MySQL 数据分区

简介

MySQL分区是一种数据库优化的技术，它允许将一个大的表、索引或其子集分割成多个较小的、更易于管理的片段，这些片段称为“分区”。虽然在逻辑上表依然是一个整体，但物理上数据被分割到不同的分区中。每个分区都可以独立于其他分区进行存储、备份、索引和其他操作。这种技术主要是为了改善大型数据库表的查询性能、维护的方便性以及数据管理效率。

分区表的查询、插入和删除操作只会影响相关的分区，而不涉及整个表，从而减少了系统的资源消耗，提升了操作效率。

原理

InnoDB逻辑存储结构

InnoDB存储引擎的逻辑结构是一个层次化的体系，主要由表空间、段、区和页构成。

表空间：是InnoDB数据的最高层容器，所有数据都逻辑地存储在这里。
段（Segment）：是表空间的重要组成部分，根据用途可分为数据段、索引段和回滚段等。InnoDB引擎负责管理这些段，确保数据的完整性和高效访问。
区（Extent）：由连续的页组成，每个区默认大小为1MB，不论页的大小如何变化。为保证页的连续性，InnoDB会一次性从磁盘申请多个区。每个区包含64个连续的页，当默认页大小为16KB时。在段开始时，InnoDB会先使用32个碎片页存储数据，以优化小表或特定段的空间利用率。
页（Page）：是InnoDB磁盘管理的最小单元，也被称为块。其默认大小为16KB，但可通过配置参数进行调整。页的类型多样，包括数据页、undo页、系统页等，每种页都有其特定的功能和结构。

根据主键筛选需要的区

逻辑与物理分离：分区表在逻辑上表现为单一表，但数据物理上按分区规则存储在不同文件中（如每个分区对应独立的.ibd文件）。
分区键（Partition Key）：根据指定的列（如日期、ID等）决定数据分布到哪个分区。分区键必须是主键或唯一键的一部分，以确保数据唯一性。

分区类型

范围分区（RANGE Partitioning）

RANGE 分区是按数值范围将数据划分为不同的分区。例如，可以根据日期、ID 范围等字段将数据分片。（YEAR(date) < 2020）

sql

CREATE TABLE sales_range (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2011),
    PARTITION p2 VALUES LESS THAN (2012),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

数据根据 sale_date 按年份划分到不同的分区，查询某一年的数据时只会访问相应的分区。

列表分区（LIST Partitioning）

LIST 分区按指定的值列表将数据划分为不同的分区，适合按非连续的值进行分区的场景。例如，按地区或用户类型划分分区。

sql

CREATE TABLE sales_list (
    id INT NOT NULL,
    region ENUM('North', 'South', 'East', 'West') NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
) PARTITION BY LIST COLUMNS(region) (
    PARTITION pNorth VALUES IN('North'),
    PARTITION pSouth VALUES IN('South'),
    PARTITION pEast VALUES IN('East'),
    PARTITION pWest VALUES IN('West')
);

哈希分区（HASH Partitioning）

HASH 分区使用哈希算法对数据进行均匀分布，适合将数据随机均匀地划分到多个分区中，尤其在没有明显的划分规律时使用。

sql

CREATE TABLE orders (
    id INT,
    order_date DATE
)
PARTITION BY HASH(id)
PARTITIONS 4;

根据订单 ID 进行哈希分区，将数据均匀分布到 4 个分区中。

键分区（KEY Partitioning）

KEY 分区是 MySQL 提供的类似于哈希分区的机制，但哈希计算由 MySQL 内部处理。它比 HASH 分区更加灵活，可以用于任意数据类型的列。

sql

CREATE TABLE sales_key (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id, sale_date)
) PARTITION BY KEY(id) PARTITIONS 4;

使用场景

按时间增量的场景可以使用RANGE分区，将数据按照时间进行分区，例如按年、月、日进行分区，使用户关注的新数据不会受到历史数据沉重而影响到查询速度。常用于日志、订单等场景。

修改分区表

添加分区

对于 RANGE 或 LIST 分区，可以使用 ALTER TABLE 语句添加分区：

sql

ALTER TABLE sales_range ADD PARTITION (PARTITION p4 VALUES LESS THAN (2013));

对于 HASH 或 KEY 分区，由于它们是基于哈希函数进行分区的，因此不能直接添加分区，但可以通过重新创建表或调整分区数量来间接实现。

删除分区

可以使用 ALTER TABLE 语句删除分区：

sql

ALTER TABLE sales_range DROP PARTITION p0;

这将删除名为 p0 的分区及其包含的所有数据。

优化分区

当从分区中删除了大量数据，或者对包含可变长度字段（如VARCHAR或TEXT类型列）的分区进行了多次修改后，优化分区可以回收未使用的空间并整理数据碎片。

sql

# 语法
# ALTER TABLE tbl_name OPTIMIZE PARTITION partition_name_list;

ALTER TABLE tbl_users OPTIMIZE PARTITION p2, p3;

注意事项和限制

在实施MySQL分区时，需要注意以下事项和限制：

分区键选择：选择合适的分区键至关重要。确保分区键能够均匀地分布数据，并且与查询条件相匹配，以提高查询性能。
分区数量限制：MySQL对单个表的分区数量有限制（通常为1024个分区）。在设计分区策略时要考虑这个限制。
全局唯一索引限制：在分区表上创建全局唯一索引时存在限制。确保了解这些限制，并根据需要进行调整。
性能和资源消耗：虽然分区可以提高性能，但在某些情况下，过多的分区可能导致额外的性能和资源消耗。因此，要合理设计分区策略以平衡性能和资源消耗。
兼容性和迁移：在迁移现有表到分区表之前，要确保备份原始数据并测试迁移过程的正确性。此外，要了解不同MySQL版本之间对分区功能的支持和兼容性差异。

MySQL 数据分区 ​

简介 ​

原理 ​

InnoDB逻辑存储结构 ​

根据主键筛选需要的区 ​

分区类型 ​

范围分区（RANGE Partitioning） ​

列表分区（LIST Partitioning） ​

哈希分区（HASH Partitioning） ​

键分区（KEY Partitioning） ​

使用场景 ​

修改分区表 ​

添加分区 ​

删除分区 ​

优化分区 ​

注意事项和限制 ​