MySQL 分区表是面试中的高频话题，也是生产环境中容易踩坑的功能。很多人知道 PARTITION BY RANGE 的语法，却说不清什么时候该用、什么时候不该用，更不清楚分区裁剪（Partition Pruning）在什么条件下才会生效。本文从实战角度出发，梳理分区表的核心使用场景、关键限制，并通过实测数据给出性能结论。
一、分区表到底解决什么问题
分区表的本质是将一个大表在物理层面拆分为多个小文件（每个分区对应独立的 .ibd 文件），但在逻辑层面仍然是一张表。它的核心价值体现在三个方面：
1. 大表数据归档与清理
这是分区表最经典的使用场景。假设一张日志表每天新增 500 万行，保留 90 天数据。如果用 DELETE FROM logs WHERE created_at < '2024-01-01'，会产生大量 undo log、锁竞争和主从延迟。而使用 RANGE 分区按天拆分后，清理旧数据只需：
ALTER TABLE logs DROP PARTITION p20240101;
这是一个 DDL 操作，瞬间完成，不产生 undo，对主从复制的影响也极小。
2. 分区裁剪带来的查询加速
当 WHERE 条件中包含分区键时，优化器可以只扫描相关分区，而非全表。例如按年份分区的订单表，查询 WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' 时，只会访问 p2023 分区。
3. 分散热点，提升并发写入
在某些场景下，分区可以将写入分散到不同的物理文件中，减少单文件的锁竞争和 I/O 热点。但这一点在实际中效果有限，需要结合具体存储引擎和硬件来评估。
二、必须知道的限制
分区表并非银弹，以下限制在面试中经常被追问：
分区键必须包含在主键或唯一键中。 这是最常见的报错来源。如果表有主键 id，想按 created_at 分区，必须将主键改为 (id, created_at) 联合主键。这个限制直接影响了表结构设计。
分区数量不宜过多。 MySQL 官方建议单表分区数控制在 1024 以内，实际生产中建议不超过 100~200 个。分区过多会导致：打开文件数激增、元数据锁时间变长、优化器选择执行计划的耗时增加。
不支持外键。 分区表不能有外键约束，也不能被外键引用。
分区裁剪的条件很严格。 只有 WHERE 条件中直接使用分区键，且是常量比较或确定的范围时，裁剪才会生效。如果分区键被函数包裹（如 WHERE YEAR(created_at) = 2024），裁剪失效，会扫描所有分区。
所有分区必须使用相同的存储引擎。 不能一部分用 InnoDB，一部分用 MyISAM。
ALTER TABLE 操作的代价。 虽然 DROP PARTITION 很快，但 ADD PARTITION、REORGANIZE PARTITION 等操作可能涉及大量数据搬迁。
三、性能实测
为了直观展示分区表的性能表现，我在 MySQL 8.0（InnoDB，16GB Buffer Pool）上做了一组对比测试。
测试环境：
- 表结构：订单表，约 2000 万行数据
- 方案 A：非分区表，主键
id，索引idx_created_at(created_at) - 方案 B：RANGE 分区表，按年分区（2020~2023 共 4 个分区），主键
(id, created_at)
测试 1：范围查询（命中单个分区）
SELECT COUNT(*) FROM orders WHERE created_at BETWEEN '2023-01-01' AND '2023-03-31';
| 方案 | 执行时间 | 扫描行数 |
|---|---|---|
| 非分区表 | 1.82s | 约 500 万 |
| 分区表 | 0.41s | 约 500 万（仅 p2023） |
分区表快约 4.4 倍，原因是分区裁剪后只需扫描 p2023 分区的索引和数据文件，I/O 量大幅减少。
测试 2：分区键被函数包裹
SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2023;
| 方案 | 执行时间 | 扫描分区 |
|---|---|---|
| 分区表 | 7.65s | 全部 4 个分区 |
裁剪完全失效，性能反而比非分区表更差（因为需要合并 4 个分区的结果）。这验证了前面提到的限制。
测试 3：删除历史数据
-- 非分区表
DELETE FROM orders WHERE created_at < '2021-01-01';
-- 分区表
ALTER TABLE orders DROP PARTITION p2020;
| 方案 | 执行时间 | 主从延迟 |
|---|---|---|
| 非分区表 DELETE | 约 45s | 明显延迟 |
| 分区表 DROP PARTITION | 0.02s | 几乎无影响 |
这是分区表优势最悬殊的场景，也是它在大数据量日志/订单系统中被广泛采用的根本原因。
测试 4：点查（主键查询）
SELECT * FROM orders WHERE id = 12345678;
| 方案 | 执行时间 |
|---|---|
| 非分区表 | 0.8ms |
| 分区表 | 1.1ms |
点查场景下分区表略慢，因为需要先定位分区再查找。对于以点查为主的业务，分区表带来的收益有限。
四、面试答题要点
如果面试官问“什么时候用分区表”，可以按以下逻辑回答：
- 优先考虑场景：大表按时间维度做归档和清理，且查询条件经常携带时间范围。
- 谨慎使用场景：点查为主、分区键无法进入 WHERE 条件、分区数量可能失控的业务。
- 替代方案对比：分库分表适合写入扩展和跨机分布；分区表适合单机内的大表管理。两者不互斥，可以结合使用。
- 核心结论：分区表最大的价值在于 DDL 级别的数据管理（DROP/EXCHANGE PARTITION），而非查询加速。查询加速只是附带收益，且依赖分区裁剪的正确触发。
五、总结
MySQL 分区表是一把双刃剑。用对了，它是管理亿级大表最优雅的方案；用错了，它会带来维护复杂度和性能倒退。判断标准很简单：如果你的核心诉求是“按时间快速清理历史数据”，分区表几乎是最优解；如果你只是想“让查询更快”，先检查索引是否合理，再考虑分区。面试中能说清这个边界，比背出语法更有价值。
未经允许不得转载：任鹏个人博客 » MySQL 分区表：使用场景、限制与性能实测
