你了解MySQL分区表吗？知道哪些情况不适用分区表吗？
一、分区表的使用
简而言之，在数据库设计中分区表是指将具有相同物理结构的多张数据表通过特定算法组合而成的一张逻辑性较强的大型表格。这种组织方法所依据的核心技术即为分区函数（Partitioning Function），而MySQL数据库系统目前主要支持以下几种典型的实现方式：包括按范围键值分组（RANGE）、基于列表键值排序（LIST）、基于哈希值计算（HASH）、基于主键索引（KEY）以及基于列式索引（COLUMNS）。
在MySQL中使用任何分片函数时
CREATE TABLE t (
a INT,
b INT,
c DATETIME(6),
d VARCHAR(32),
e INT,
PRIMARY KEY (a,b)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).在创建过程中，我们建立了表 t，并确定其主键为复合主键（由列 a 和 b 组成）。该表旨在通过基于时间列 c 的分区策略来组织数据存储。
从中获取：在错误提示中可以看出，在构建分区表时需要特别注意主键字段是否包含了作为分区函数使用的列。由此可见，在基于列c进行数据分片的情况下，默认情况下主键必须包含该列。例如以下建表语句：
代码块
解释
CREATE TABLE t (
a INT,
b INT,
c DATETIME,
d VARCHAR(32),
e INT,
PRIMARY KEY (a,b,c),
KEY idx_e (e)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);完成表创建后，在物理存储上可以看到四个分区所对应的ibd文件，并解释道：按照时间列c将数据存储至相应的四个文件中。
t#p#p0000.ibd t#p#p2019.ibd t#p#p2020.ibd t#p#p9999.ibd需要特别注意的是，在MySQL数据库系统中，默认情况下会将创建的大主表按照一定的策略自动划分为多个高可用性的小子表（即分区表），每个子表都独立地包含自己的索引信息。从逻辑结构上类似于一张虚拟的大主表，在实际运行时则由多个独立的子实体共同完成数据存储与管理功能；这些子实体在物理层面上分别存储在不同的文件中以实现高可用性和数据冗余的效果
二、分区表的注意事项：唯一索引
MySQL数据库环境中进行处理
ALTER TABLE t ADD UNIQUE KEY idx_d(d);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).然而由于将唯一索引设计为包含分区列的原因 后者导致它仅具有该分区范围内的唯一性 而不再是全局范围内的唯一性了 那么对于表 t 允许插入以下两条记录
INSERT INTO t VALUES
(1,1,'2023-01-01','aaa',1),
(1,1,'2024-01-01','aaa',1);
SELECT * FROM t;
+---+---+---------------------+------+------+
|a|b|c|d|e|
+---+---+---------------------+------+------+
|1|1|2023-01-01 00:00:00|aaa|1|
|1|1|2024-01-01 00:00:00|aaa|1|
+---+---+---------------------+------+------+观察到列 d 中的元素均为字符串‘aaa’；然而该操作仍可成功插入。此操作会导致列 d 并非单一值，并需通过当前分区实现全局唯一性。
那如何实现全局唯一索引呢？ 和之前表结构设计类似，在唯一索引中采用类似于 UUID 的全球唯一标识符（如类似的随机字符串），从而消除局部独特性的潜在问题。
三、分区表的误区：性能提升
将一张大的数据表分割成多张较小的数据表是MySQL数据库性能优化的重要手段。这一观点存在严重问题！如果试图通过这种方法来提升数据库性能，则不建议采用这种方法，因为这种优化效果无法实现。
分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理 。
我们曾探讨了B+树与其所支持的数据量之间的关系。当B+树的高度达到4时，在该结构下可支持存储10^9条记录。这种情况下实现的一次查询仅需4次IO操作即可完成，并运行得非常迅速。然而，在引入分区表后虽降低了B+树的高度，在实际应用中对性能的提升却有限
此外，在这种情况下（即非分区表中的普通列数据检索），尽管主键或外键字段可能已经在相应的区域中有了优化的支持（如果有的话），但在其他字段上仍然会面临类似的挑战：由于它们依赖于各个区域内的独立局部存储方案（即每个区域都有自己的局部指针式存储结构），因此必须逐个访问每个区域的数据块才能完成查询操作
此外，在这种情况下（即非分区表中的普通列数据检索），尽管主键或外键字段可能已经在相应的区域中有了优化的支持（如果有的话），但在其他字段上仍然会面临类似的挑战：由于它们依赖于各个区域内的独立局部存储方案（即每个区域都有自己的局部指针式存储结构），因此必须逐个访问每个区域的数据块才能完成查询操作
比如如下SQL及其执行计划：
SELECT * FROM t WHERE d = 'aaa'
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
partitions: p0000,p2019,p2020,p9999
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 50.00
Extra: Using where通过执行相应的计划方案时**：我们能够预见到**：该 SQL 查询将跨越四个存储区域。其中每个存储区域预计会经历三次I/O操作，则总共将经历12次I/O操作。然而**：相比之下**：如果采用传统表结构，则即使数据量再增加也不会超过每次查询仅需4次I/O操作的时间。
所以，在设计分区表时应当确保每个查询条件均包含对应的分区字段；如果不带，则会导致系统必须遍历所有分区的数据或索引信息以完成查询操作。因此，在某种程度上说这种设计方法并未改善数据库性能；其主要功能是用于数据迁移及备份操作
四、分区表业务实战
考虑到电商行业的普遍性，在类似淘宝这样的电商平台中，
Orders 表的数据量会非常巨大，
假设一天会产生500万笔订单，
那么一年该表 Orders 就会积累近18亿笔订单数据。
考虑到订单表的特点，在数据库中通常只存储最近一年甚至更短时长的数据；而历史订单数据会被归档。除非存在超过1年未退款的订单，则大部分已完成的订单则从业务角度来看就不再具有价值。
如果希望高效管理订单表的数据，则可以通过将表 Orders 按年份建立分区索引表来实现
CREATE TABLE `orders` (
`o_orderkey` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_CLERK` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`o_orderkey`,`O_ORDERDATE`),
KEY `orders_fk1` (`O_CUSTKEY`),
KEY `idx_orderdate` (`O_ORDERDATE`)
)
PARTITION BY RANGE COLUMNS(o_orderdate)
(
PARTITION p0000 VALUES LESS THAN ('1992-01-01') ENGINE = InnoDB,
PARTITION p1992 VALUES LESS THAN ('1993-01-01') ENGINE = InnoDB,
PARTITION p1993 VALUES LESS THAN ('1994-01-01') ENGINE = InnoDB,
PARTITION p1994 VALUES LESS THAN ('1995-01-01') ENGINE = InnoDB,
PARTITION p1995 VALUES LESS THAN ('1996-01-01') ENGINE = InnoDB,
PARTITION p1996 VALUES LESS THAN ('1997-01-01') ENGINE = InnoDB,
PARTITION p1997 VALUES LESS THAN ('1998-01-01') ENGINE = InnoDB,
PARTITION p1998 VALUES LESS THAN ('1999-01-01') ENGINE = InnoDB,
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
）可以看到，在这种情况下，“Orders表”的主键被修改为(o_orderkey, O_ORDERDATE)这一组合字段，并根据年度对数据进行分区存储。那么如果我们想要删除一年前的数据——例如删除1998年的数据——之前需要用以下SQL语句：
DELETE FROM Orders
WHERE o_orderdate >= '1998-01-01'
AND o_orderdate < '1999-01-01'可这条 SQL 的执行效率较低，在运行过程中会产生大量的二进制日志记录，在生产环境中也会导致数据库主从系统的延迟问题。而采用分区表策略时，则会使得数据管理更加简便：直接执行清空分区的操作即可：
ALTER TABLE orders_par
TRUNCATE PARTITION p1998该 SQL 的执行效率显著较高, 其运作机制主要涉及将分区表进行删除与重建操作, 从而避免了主从复制造成的潜在延迟问题. 此外仅生成一条 DDL 日志记录, 这一特点进一步保证了系统的高效运行.
文章将持续更新，欢迎关注公众号：服务端技术精选。欢迎点赞、关注、转发 。