如果你维护过一张体量上了亿级的订单表对“按时间分区之后查询变快”这句话一定不陌生。可我刚接手这类表的时候心里一直有个疑问明明普通表也建了索引分区表凭什么能快出一个数量级直到某次排查一条慢 SQL我在EXPLAIN的输出里看到partitions列只列出了目标月份对应的少数几个分区才真正意识到MySQL 分区裁剪 (Partition Pruning) 的价值不是“少读几张表”这么简单它在执行计划生成阶段就替我们把不需要碰的数据物理隔离掉了。这篇文章我会把这套机制从头到尾拆开先讲清楚分区裁剪到底优化了什么再看优化器内部用什么算法判断该剪哪些分区然后给出可以稳定触发裁剪的 SQL 写法和那些让裁剪失效的常见写法最后用EXPLAIN、optimizer_trace这些工具做验证并聊几个我实际踩过的坑。适合正在做分库分表设计、维护大表查询性能、或者只是被“分区表为什么比普通表快”这个问题困扰的开发与 DBA 朋友。1. 分区裁剪到底替我们省了什么从两张订单表说起1.1 一张没有分区的订单表索引救不了全表扫描先说一个我实际碰到过的场景。订单表orders有 2 亿行按时间查询某一天的订单汇总SQL 大概长这样SELECT region, COUNT(*), SUM(amount) FROM orders WHERE order_date 2024-03-15 00:00:00 AND order_date 2024-03-16 00:00:00 GROUP BY region;普通表上就算建了order_date索引这条 SQL 也只是把“全表扫描”变成“索引范围扫描 回表”最终还是要翻 2 亿行数据里属于 3 月 15 日这一整天的记录。如果这个表还同时被其他条件过滤比如status PAID而status又没索引那更是灾难——MySQL 得先按索引把当天所有订单取出来再逐行过滤状态最后分组。这时候一张按月份做 RANGE 分区的表就不一样了order_date本身就是分区键查询条件里又带上了order_date的范围优化器在执行计划生成阶段就能直接判定数据只可能在p202403这一个分区里其他 23 个分区的物理文件压根不需要打开。1.2 分区表的物理优势裁剪发生在索引之前很多人把分区裁剪和索引优化混为一谈实际上它们是两层独立的东西。如果把一张大表比作一整排档案柜普通表的情况是所有档案混放在一个大房间里就算你有一份很精确的目录本索引也得进到这个大房间里按目录翻找而分区表是把档案预先按年份、按月份分到了不同的房间每个房间外贴着标签。查询时优化器先做的一步是“看标签决定进哪个房间”这一步就是分区裁剪。进了房间之后要不要继续翻目录本是第二件事也就是索引的事情了。这个物理层面的隔离带来的收益非常直接。以 InnoDB 为例开启innodb_file_per_table后每个 InnoDB 分区都有自己独立的表空间文件。裁剪生效时数据库根本不需要打开那些不相关分区的.ibd文件文件描述符的占用、磁盘 IO、Buffer Pool 中被加载进来的 page全都在源头被掐断了。1.3 叠加使用裁剪 索引 双保险分区裁剪不是用来替代索引的它是为索引和扫描策略铺路的。正确的姿势是先用分区裁剪把搜索空间限定到少数几个分区然后在分区内部继续用二级索引或主键索引做精确定位。举个叠加使用的例子。一张订单流水表按order_time做 RANGE COLUMNS 分区每个季度一个分区同时在建表时把(order_time, order_id)做成组合主键。查询某一天的流水SELECT * FROM order_flow WHERE order_time 2024-06-10 00:00:00 AND order_time 2024-06-11 00:00:00;优化器先通过分区裁剪把访问范围锁定在p2024q2这一个分区在这个分区内部再通过order_time的索引范围访问计算精确偏移。整个查询只打开了一个分区表空间的少量 page比普通表靠全局索引扫描再回表要轻得多。正是因为这两个机制可叠加分区裁剪的价值才会在很多场景下被低估它解决的不是“某一行在哪里”的问题而是“哪些物理区域完全不值得去看”的问题。2. 优化器的裁剪算法分区边界就是一张区间集合表2.1 把查询条件变成集合把分区定义变成区间然后求交集分区裁剪的核心算法本质上是一次集合运算。MySQL 优化器在解析阶段会把WHERE条件里涉及分区键的部分转化为一组取值区间然后把每个分区定义的范围当成一个区间两者求交集。交集为空的分区直接剪掉交集非空的分区保留下来进入执行计划。以 RANGE 分区为例建表时你写的VALUES LESS THAN其实就是分区的边界映射PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300)这个表真正覆盖的区间是分区名覆盖范围p0(-∞, 100)p1[100, 200)p2[200, 300)一条WHERE part_col 150的查询等值条件被转换成单点区间 [150, 150]和 p1 的 [100, 200) 有交集于是只访问 p1。一条WHERE part_col BETWEEN 50 AND 250对应区间是 [50, 250]和 p0、p1、p2 都有交集所以三个分区都会进执行计划但不会多出任何一个不在区间内的分区。这个“求交集”的动作是在优化阶段完成的因此裁剪本身几乎不消耗执行阶段的资源。你的 SQL 条件写得越精确交集算出来保留的分区就越少。2.2 RANGE 与 RANGE COLUMNS边界判定并不完全一样RANGE 分区最常见的形态是“按整数或日期函数分区”比如PARTITION BY RANGE (YEAR(order_time))。这里要注意YEAR(order_time)本质上已经把分区键变成了一个整数年份之后查询如果直接写order_time的范围优化器是没法直接做裁剪的因为分区键是YEAR(order_time)的表达式不是order_time本身。这是很多老分区表“剪不动”的根源之一后面第 3 章我会专门展开。RANGE COLUMNS 是比 RANGE 更推荐的一种形式它允许分区键直接使用一列或一组列并且支持字符串、日期时间这些类型。比如CREATE TABLE order_flow ( id BIGINT NOT NULL, order_time DATETIME NOT NULL, region VARCHAR(20), amount DECIMAL(12,2), PRIMARY KEY (id, order_time) ) PARTITION BY RANGE COLUMNS(order_time) ( PARTITION p2024q1 VALUES LESS THAN (2024-04-01 00:00:00), PARTITION p2024q2 VALUES LESS THAN (2024-07-01 00:00:00), PARTITION p2024q3 VALUES LESS THAN (2024-10-01 00:00:00), PARTITION p2024q4 VALUES LESS THAN (2025-01-01 00:00:00) );这种情况下分区列就是order_time本身查询里直接对order_time做范围比较就能稳定裁剪。RANGE COLUMNS 还支持多列例如PARTITION BY RANGE COLUMNS(a, b)这时的边界判定类似元组比较两个边界值同时参与区间判断裁剪逻辑会更复杂但优势是你能设计出更多维度的数据分布。2.3 HASH/KEY 分区等值确定分区范围条件无能为力HASH 分区的数据分布规则很直白MOD(分区键函数, 分区数)决定行落在哪个分区。因此等值条件是最容易裁剪的PARTITION BY HASH(id) PARTITIONS 8一条WHERE id 123456优化器只需要计算123456 MOD 8就能直接定位到唯一一个分区。WHERE id IN (1, 2, 3)也能裁剪计算每个值对应的分区即可。但如果你写的是WHERE id 100优化器就没办法了——它无法根据一个范围反推出所有可能落在哪些分区这种情况下 HASH 分区的裁剪基本失效。KEY 分区类似区别在于它使用 MySQL 内部哈希函数而不是简单的MOD。KEY 分区的等值条件同样能裁剪但如果你对“最终落到哪个分区”有强迫症HASH 分区这种可预测的模运算会更好治理。这也解释了为什么按时间范围查询的业务场景最合适的永远是 RANGE/RANGE COLUMNS 分区而不是 HASH/KEY 分区。没有哪一种分区类型是万能的裁剪能力和你的查询形态是绑定的。2.4 LIST 分区枚举值直接走集合匹配LIST 分区的裁剪逻辑和 RANGE 又有不同它不是区间求交集而是枚举值集合的匹配PARTITION BY LIST(region) ( PARTITION p_west VALUES IN (west, northwest), PARTITION p_east VALUES IN (east, northeast), PARTITION p_south VALUES IN (south) );一条WHERE region east可以直接裁剪到p_eastWHERE region IN (east, south)则裁剪到p_east和p_south。但如果你写WHERE region ! east优化器很难把它转换成有效的分区集合结果往往就是所有分区都扫一遍。我把几种分区类型的裁剪能力整理成一个表方便以后设计时参考分区类型等值条件IN 条件范围条件说明RANGE支持支持支持最适合范围查询裁剪能力最强RANGE COLUMNS支持支持支持支持字符串、日期和多列HASH支持支持不支持只适合等值查询KEY支持支持不支持类似 HASH但用 MySQL 内部哈希函数LIST支持支持不支持只在枚举值匹配时能裁剪3. 同样一句 WHERE剪得动与剪不动的写法3.1 直接比较分区键规范写法的底线想让优化器稳定裁剪最基础的规范是分区键列必须直接出现在比较运算中不要被任何函数或表达式包住。可裁剪的写法包括WHERE part_col 100WHERE part_col IN (100, 200)WHERE part_col BETWEEN 100 AND 200WHERE part_col 100 AND part_col 300这些写法把分区键本身放进条件里优化器能把这些条件直接转换成取值区间、IN 集合或边界值从而映射到分区 ID。3.2 函数包裹和表达式运算优化器无法逆向推导分区键被函数包裹是让裁剪失效的头号原因也是我见过最多的问题。-- 剪不动 WHERE DATE(order_time) 2024-03-15; -- 剪得动 WHERE order_time 2024-03-15 00:00:00 AND order_time 2024-03-16 00:00:00;为什么DATE(order_time)剪不动因为优化器的范围分析处理的是原始列DATE()包裹之后优化器无法从DATE(order_time) 2024-03-15反推出order_time精确对应的连续区间。它确实知道order_time应该落在一天之内但要完成这个推断优化器需要理解DATE()函数的语义而 MySQL 的裁剪机制不会为任意函数做这种逆向推导。YEAR(order_time) 2024、MONTH(order_time) 3、order_time INTERVAL 1 DAY ...全都有同样的问题。这也是我为什么一直推荐业务表直接用 RANGE COLUMNS 按原始时间列分区而不是用PARTITION BY RANGE (YEAR(order_time))。前者查询时直接写order_time范围就能裁剪后者必须写YEAR(order_time)才能裁剪而业务 SQL 几乎不会天天带上这个函数结果就是分区表形同虚设。3.3 隐式类型转换类型不一致导致判断失真另一种隐蔽的失效是分区键列类型和查询常量的类型不一致导致隐式类型转换。比如分区键是VARCHAR查询却写WHERE region_code 101MySQL 会尝试把region_code列转成数值再比较这个转换动作本身就有可能让优化器放弃对分区 ID 的精确推导。类似的还有把DATETIME分区键和字符串常量比较时格式不统一的情况。我的建议是写 SQL 时让常量类型和列类型严格一致不要指望隐式转换帮你兜底它在旧索引优化里可能还能侥幸用上在分区裁剪这里大概率是拖后腿的。3.4 OR、IN、BETWEEN范围分析对不同条件形态的处理IN和BETWEEN是裁剪友好的老朋友了只要里面是常量列表或常量范围优化器都能做集合转换。OR的裁剪则要更谨慎一些-- 能裁剪每个 OR 分支都指向明确分区 WHERE order_time 2024-01-01 00:00:00 OR order_time 2025-01-01 00:00:00 -- 大概率剪不动第二个分支没有分区键 WHERE order_time 2024-01-01 00:00:00 OR region east第一种写法里两个分支都能被转换成分区 ID 集合优化器取并集裁剪有效。第二种写法里第二个分支region east和分区键无关理论上它可能落在任何分区优化器为了不丢数据只能保守地保留所有分区。所以业务 SQL 里如果用到OR最好确保所有分支都包含分区键条件。3.5 多列分区键的第二列想剪先给第一列RANGE COLUMNS 支持多列分区键但裁剪时候序敏感。分区定义如果是RANGE COLUMNS(a, b)那么第一列a的条件决定了大范围第二列b只在大范围确定后起细化作用。如果你只给b写条件不给a优化器很难直接定位到少数分区因为元组排序的第一步就缺了。实际经验是多列分区键设计时把最常用、过滤性最强的列放第一位。查询里至少保证第一列有等值或范围条件第二列的裁剪才有机会生效。这个和联合索引的最左前缀原则是一个道理。4. 用 EXPLAIN 和 optimizer_trace 验证裁剪是否生效4.1 partitions 列三个值得关注的状态验证裁剪是否生效最直观的工具就是EXPLAIN。MySQL 从 5.6 开始就提供了partitions列在 8.0 里同样能看到。partitions列的三种常见状态NULL说明这张表不是分区表或优化器在执行时不需要区分分区。显示p2024q1或p2024q1;p2024q2这样的列表说明裁剪生效了输出的是实际要访问的分区。显示所有分区名基本等于没有裁剪查询会把全部分区都扫一遍。看到“所有分区名都列出来”时先别急着改 SQL回头检查一下分区键条件有没有被函数包裹、类型是否匹配、OR 分支是否都包含分区键。这几个检查做完大部分裁剪失效的问题都能定位。4.2 从 FORMATTREE 到 ANALYZE实际行数最有说服力只看partitions列有时候还不足以判断裁剪的收益因为一个分区内部可能仍然有大量数据。想要更精确地理解执行代价可以看EXPLAIN FORMATTREE或直接上EXPLAIN ANALYZE。MySQL 8.0.18 以上版本支持EXPLAIN ANALYZE它是真正执行查询然后输出每个步骤的actual rows和actual time。对分区表来说这条命令能清晰展示实际扫描了多少行、花费了多少时间。比如下面这样的输出- Group aggregate: ... (actual time... rows...) - Index range scan on order_flow using PRIMARY (actual time... rows...)配合partitions列就能从“访问了哪些分区”和“实际扫了多少行”两个维度交叉验证裁剪效果是否达到预期。4.3 半小时上手建一张季度分区表跑四类查询纸上谈兵不如直接跑一遍。我建议你在一张测试表上实践一下比如建一张按季度 RANGE COLUMNS 分区的表CREATE TABLE order_flow_test ( id BIGINT NOT NULL, order_time DATETIME NOT NULL, region VARCHAR(20), amount DECIMAL(12,2), PRIMARY KEY (id, order_time) ) PARTITION BY RANGE COLUMNS(order_time) ( PARTITION p2023q4 VALUES LESS THAN (2024-01-01 00:00:00), PARTITION p2024q1 VALUES LESS THAN (2024-04-01 00:00:00), PARTITION p2024q2 VALUES LESS THAN (2024-07-01 00:00:00), PARTITION p2024q3 VALUES LESS THAN (2024-10-01 00:00:00), PARTITION p2024q4 VALUES LESS THAN (2025-01-01 00:00:00) );然后依次跑这几类查询观察EXPLAIN输出-- 1. 裁剪到一个分区 EXPLAIN SELECT * FROM order_flow_test WHERE order_time 2024-05-01 00:00:00 AND order_time 2024-06-01 00:00:00; -- 2. 裁剪到多个分区 EXPLAIN SELECT * FROM order_flow_test WHERE order_time 2024-02-01 00:00:00 AND order_time 2024-08-01 00:00:00; -- 3. 没有分区键条件无法裁剪 EXPLAIN SELECT * FROM order_flow_test WHERE region east; -- 4. 分区键被函数包裹无法裁剪 EXPLAIN SELECT * FROM order_flow_test WHERE DATE(order_time) 2024-06-15;跑完之后你会看到第 1 条 SQL 只访问p2024q2第 2 条访问p2024q1;p2024q2;p2024q3第 3、4 条则会列出所有分区或者因为表本身数据不足而没有分区列表。这个对照实验做一次你对分区裁剪的行为模式就有很直观的体感了。4.4 交叉验证information_schema.PARTITIONS 与 optimizer_trace还可以用information_schema.PARTITIONS看每个分区的统计信息辅助判断查询会“大概率”落在哪些分区SELECT PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA test AND TABLE_NAME order_flow_test;如果某个分区行数远大于其他分区而查询剪到的又恰好是这个分区那么即使裁剪生效性能也可能不如预期——因为一个分区里数据量太大。这时就需要考虑重新设计分区粒度比如从季度分区改成月份分区。想再深入一层可以开启optimizer_traceSET optimizer_trace enabledon; SELECT * FROM order_flow_test WHERE order_time 2024-05-01 00:00:00 AND order_time 2024-06-01 00:00:00; SELECT * FROM information_schema.OPTIMIZER_TRACE\G在 trace 数据里搜索“partition”相关段落能看到优化器对分区范围的判断过程。这个工具不像EXPLAIN那么直观但排查“为什么没剪到预期分区”这类问题时很有用。5. 裁剪背后那些“剪得动但依然很痛”的现实问题5.1 主键必须包含分区键全局唯一性成了奢望MySQL 分区表有一个硬性限制很多人第一次踩到时都觉得很无语分区表的主键或者唯一索引必须包含分区键的所有列。换句话说你不能建一张主键为id、按order_time分区的表。如果你强行建MySQL 会直接报错A PRIMARY KEY must include all columns in the tables partitioning function这个约束带来的连锁反应是分区表上的唯一性永远只能是“分区内唯一”不是全局唯一。比如分区键是order_time主键是(id, order_time)那么id 100可以在不同分区里各出现一次。对于需要全局唯一订单号的业务这是一个致命问题要么接受分区内唯一并调整业务逻辑要么用普通表加索引、放弃分区方案。我见过不少团队因为这个问题被迫放弃分区表转而用普通表加定期清理冷数据的方式。这个限制在设计阶段一定要想清楚别等到上线前才发现。5.2 UPDATE 分区键行从一个分区搬到另一个分区的代价裁剪对SELECT的效果立竿见影但UPDATE如果动了分区键列情况会变得很微妙。假设一张表按order_time分区你执行UPDATE order_flow SET order_time 2024-08-01 00:00:00 WHERE id 100;如果这一行原来在p2024q1现在要迁到p2024q3。InnoDB 的处理方式是在事务内部删除旧分区的行再在新分区插入新行。这个“跨分区搬行”的代价远高于普通UPDATE不仅会带来额外的页面读写还可能在事务中产生更多锁和日志。经验法则是分区键列应该设计成“业务上几乎不会被修改”的列比如订单创建时间、注册日期这类只增不改的字段。如果业务上确实需要修改分区键你要提前做好性能预案。5.3 分区数量失控裁剪收益会被元数据和文件开销吞掉分区裁剪看起来是“分区越多剪得越细”但实际不是这样。每个分区在 InnoDB 里都对应独立的表空间文件MySQL 的字典、元数据、MDL 也需要维护全部分区。分区数量一路加下去即使裁剪效果再好创建、打开、备份、统计等环节的固定成本也会逐渐吃掉收益。从我实践的经验看单表分区数量控制在几十到一两百个是比较舒服的区间。超过几百个分区很多ALTER TABLE操作会变得非常慢后台统计信息的维护也容易出现延迟。设计分区粒度时不要只想着把裁剪做到极致要结合数据留存周期和运维能力找到一个“能剪、剪得动、但不过度切碎”的平衡点。5.4 NULL 值永远留在第一个分区边界条件的坑分区表的NULL处理有特殊规则和普通表很不一样。在 RANGE 分区里NULL会被放进第一个分区——即使第一个分区的边界是VALUES LESS THAN (100)NULL也会被它兜住。这意味着查询WHERE order_time IS NULL访问的第一个分区就是兜底分区。第一个分区可能因为吸收了所有NULL列而比其他分区肥大。如果你用第一个分区做冷数据归档要格外小心NULL数据的积累。HASH 分区的规则是NULL哈希为 0进入分区 0LIST 分区则要求分区列表里显式包含NULL否则插入报错。这些边界情况不处理好分区裁剪再准确数据存储本身也会失衡。5.5 维护操作之后别忘了刷新统计信息分区裁剪是优化阶段做的事但优化器是否选对分区范围内的执行路径依赖统计信息。做过大量ALTER TABLE分区操作、TRUNCATE PARTITION、EXCHANGE PARTITION后分区的统计信息可能不够新鲜。这时候优化器可能明明裁剪到了一个小分区却因为行数估算偏差选了不佳的执行计划。我的习惯是做了分区管理操作后对相关表执行一次ANALYZE TABLE让统计信息跟上实际数据分布。这个动作成本不高但能避免不少“裁剪明明生效、查询却还是很慢”的玄学问题。5.6 8.0 不再支持子分区老教程不能照抄如果你在网上翻到 5.7 时代的老教程看到SUBPARTITION BY HASH这种写法千万别往 MySQL 8.0 里贴。MySQL 8.0 明确移除了对子分区subpartitioning的支持老脚本直接跑会报语法错误。正确的做法是重新设计分区方案用一层分区解决而不是试图在分区下面再挂一层子分区。我自己在做表结构评审时一般会把“分区键是否被函数包裹”“主键是否包含分区键”“更新是否涉及分区键”这三点列为硬性检查项。前两点决定分区裁剪能不能生效第三点决定分区表在写路径上会不会成为性能拖累。前面提到过我最推荐的做法是能直接用 RANGE COLUMNS 按原始时间列分区的就不要绕道YEAR(order_time)能保证查询里直接写分区键范围的就不要画蛇添足加函数能稳定走等值查询的再考虑 HASH 或者 KEY。分区裁剪说到底是优化器用你建表时的物理设计换来的执行效率你建表时偷的懒最后都会变成线上 SQL 的泪。每个发布到生产环境的查询都值得先跑一遍EXPLAIN看看partitions列是不是你真的想要的答案。