前阵子帮一个业务线排查慢查询凌晨四点收到报警一条统计订单的SQL跑了快四秒。我打开建表语句一看订单表已经按月份做了RANGE分区单表四个多亿行按理说这种结构应该不至于这么慢。可问题出在查询SQL上WHERE条件里全是商户ID和状态码压根没带分区键优化器只能傻乎乎地把所有分区从头到尾扫一遍。后来我把SQL改成先用order_time框定时间范围同样一条统计耗时直接掉到两百毫秒以内。这个差距就是MySQL分区裁剪Partition Pruning在起作用。这篇文章我想把这个功能彻底讲透。分区裁剪说穿了就是MySQL优化器在执行计划生成之前根据WHERE条件里分区键的约束提前排除掉那些没必要扫描的分区只留下可能包含目标数据的分区去执行。它是分区表性能好坏的分水岭同样一张表能用好裁剪和用不好裁剪查询性能可以是两个世界。适合谁看正在用分区表但总感觉性能不对劲的开发以及被“带分区键但还是扫全表”这种问题困扰的DBA都可以从里面找到答案。1. 分区裁剪的运行机制与原理解读1.1 分区表为什么必须依赖裁剪很多同学对分区表的理解停留在“分而治之”觉得数据拆到不同分区里查询速度自然就快了。这个直觉只对了一半。分区确实把物理存储拆开了但如果你查询的时候没有把分区键作为筛选条件MySQL不知道目标数据在哪个分区它就必须把所有分区都访问一遍把结果汇总之后再做过滤。这种“扫描之后再丢弃”的行为和全表扫描没什么本质区别甚至更糟因为分区表比普通表多了分区信息管理、文件句柄占用这些额外开销。用一个生活里的类比分区表就像一个大仓库被隔成了几十个小房间分区裁剪相当于你进仓库之前先在门禁系统里查清楚“目标物资只在三号房间”于是你只开三号房间的门。没有裁剪的话你就得把所有房间的门全部打开翻一遍再关回去。所以分区裁剪才是分区表真正省时间的核心机制它解决的核心问题就是让查询只触碰该触碰的数据。1.2 优化器在哪个阶段完成了裁剪我见过不少开发同学以为分区裁剪是InnoDB存储引擎在执行过程中做的其实不是。它发生在更靠前的阶段也就是MySQL优化器在生成执行计划之前会根据解析后的WHERE条件决定一份“待访问分区列表”这份列表随后被固化到执行计划里。官方把这个过程称为分区修剪实现上优化器会分析分区键上的条件然后调用针对不同分区类型的分区裁剪算法对于RANGE和RANGE COLUMNS分区优化器会把分区键的条件和每个分区的边界值做比较把边界完全在条件范围之外的分区直接排除掉。对于LIST分区优化器会把分区键的枚举值映射到分区号能通过等值条件快速定位到少数几个分区。对于HASH和KEY分区优化器会对分区键做哈希计算通过取模或者哈希值确定目标分区编号。拿RANGE COLUMNS分区来举例分区边界就是一组有序的时间点优化器要做的事情就是判断条件区间落在哪个分区区间里。这本质上是集合运算把“命中分区集合”从“全部分区集合”里筛出来效率非常高几乎可以忽略不计。1.3 裁剪到底省下了哪些成本判断一个优化手段值不值得关注得看它省了什么。分区裁剪省掉的是你最在意的那三块成本第一是磁盘IO。每个分区在InnoDB里都有独立的数据文件开启innodb_file_per_table后尤其明显扫描全部分区等于把整张表的物理文件都要读一遍。裁剪后只读取目标分区文件IO量从“全表”变成“单区”。第二是CPU和内存。分区多的时候每扫描一个分区都要初始化分区上下文、遍历分区内的索引和行数据这些都要消耗CPU和缓冲池内存。裁剪掉大部分分区之后这些开销同步消失。第三是回表次数和随机IO。很多人忽略这个如果分区键上连着二级索引扫描全部分区意味着每个分区的二级索引都要走一遍回表跨度巨大。裁剪后回表只在命中的分区里发生随机IO的规模被大幅压缩。我用一个实际数字来说明假设一张表按月份分了24个分区某条查询只需要最近一个月的数据理论上裁剪能让你扫描的数据量从24份变成1份。当然实际执行中因为缓冲池、索引顺序等原因不会严格变成1/24但数量级上的差距是绝对的。这也是为什么本文开头那条SQL能从将近四秒优化到两百毫秒的原因。2. 分区策略选型不同分区方式能裁剪到什么程度2.1 RANGE与RANGE COLUMNS时间范围查询的黄金组合RANGE分区是应用最广泛的一种尤其适合按时间维度拆分的流水型业务。RANGE COLUMNS是RANGE分区的一种进阶写法它允许你直接用DATETIME、字符串甚至多个列来做分区边界比早期用函数表达式比如TO_DAYS(order_time)要直观得多。我建议新业务优先使用RANGE COLUMNS因为分区表达式越简单裁剪判断越准确。一个典型的按月分区建表语句长这样CREATE TABLE order_log ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, order_time DATETIME NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(12,2) NOT NULL DEFAULT 0, PRIMARY KEY (id, order_time), KEY idx_user_time (user_id, order_time) ) ENGINEInnoDB PARTITION BY RANGE COLUMNS(order_time) ( PARTITION p202401 VALUES LESS THAN (2024-02-01), PARTITION p202402 VALUES LESS THAN (2024-03-01), PARTITION p202403 VALUES LESS THAN (2024-04-01), PARTITION p_max VALUES LESS THAN MAXVALUE );注意上面的表里主键用了(id, order_time)这不是多此一举。MySQL强制要求分区表的所有唯一索引包括主键必须包含分区键的列。如果你直接写PRIMARY KEY (id)建表就会报错A PRIMARY KEY must include all columns in the tables partitioning function。在裁剪方面RANGE COLUMNS分区对上界和下界条件都相当友好。WHERE order_time 2024-01-01 AND order_time 2024-02-01能精准裁剪到p202401分区如果是BETWEEN 2024-01-15 AND 2024-02-15这种跨越两个月边界的条件优化器也能把涉及的p202401和p202402都识别出来。要注意的是如果你不写分区键条件比如只写WHERE status 1那这个表的所有分区还是会被全扫。2.2 LIST分区按枚举值精确命中LIST分区适合分区键取值有限且相对固定的场景比如按省份、业务类型、订单状态来拆。它的裁剪逻辑也很直接等值条件能精确映射到分区编号IN列表也能展开成多个分区。举一个简单例子CREATE TABLE user_region_log ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, region_id INT NOT NULL, log_time DATETIME NOT NULL, PRIMARY KEY (id, region_id) ) ENGINEInnoDB PARTITION BY LIST COLUMNS(region_id) ( PARTITION p_north VALUES IN (1, 2, 3), PARTITION p_south VALUES IN (4, 5, 6), PARTITION p_other VALUES IN (7, 8, 9, 10) );如果应用层经常按照“华北地区”这种维度查询LIST分区能把数据物理隔离好裁剪也精准。但它也有软肋当业务枚举值扩展时你得记得提前维护分区定义往LIST里新增枚举值否则插入数据时会直接报“表没有对应分区”的错误。相比之下RANGE分区自动走MAXVALUE兜底分区要省心一些。2.3 HASH与KEY分区等值查询的快速定位HASH分区是按照分区键的哈希值对分区数取模KEY分区则使用MySQL内部哈希函数。它们最大的特点是分区键的等值查询可以非常快速地定位到唯一一个分区因为WHERE id 100这种条件下100的哈希取模结果可以直接算出来。CREATE TABLE user_session ( user_id BIGINT NOT NULL, session_id VARCHAR(64) NOT NULL, login_time DATETIME NOT NULL, PRIMARY KEY (user_id, session_id) ) ENGINEInnoDB PARTITION BY HASH(user_id) PARTITIONS 16;这里用user_id做HASH分区只要查询条件带上user_id比如WHERE user_id 12345 AND login_time 2024-01-01优化器能马上算出该用户只可能在某个分区里其他15个分区直接被跳过。但HASH分区有个天然的局限性范围查询的裁剪能力弱。比如WHERE login_time 2024-01-01这种不带user_id的时间范围查询优化器无法用范围推导出该访问哪几个分区因为HASH值和时间值根本不对应只能全分区扫描。2.4 分区键选择的几条准则我踩过不少坑之后把分区键的选择标准总结成下面几条你照着做基本不会错第一分区键必须出现在高频查询的WHERE条件里。选分区键之前先统计业务SQL里最常被过滤的字段是哪个。如果业务查询经常“查最近一个月某个用户的数据”那分区键选order_time二级索引里再带上user_id裁剪索引双管齐下效果是最好的。第二分区键必须属于所有唯一索引。这是MySQL的硬性规定也是很多开发第一次建分区表就报错的原因。简单理解为了让某个唯一索引在全表范围成立数据库必须能在单分区内校验唯一性所以分区键必须成为每个唯一索引的组成部分。第三分区键上的条件要能保持“裸列”形态。不要写成YEAR(order_time) 2024、DATE_FORMAT(order_time, %Y-%m) 2024-01这种函数包裹形态否则优化器很难判断哪个分区需要保留裁剪大概率失效。第四分区粒度要跟业务查询粒度匹配。比如业务最常查近一个月就按月分区如果经常查近一年就按季度甚至按年分区。分区数不是越多越好MySQL 5.7之前单表最多1024个分区8.0放宽了限制但分区过多会带来文件句柄压力、DDL变更复杂、优化器处理分区元数据的成本上升实际生产经验建议分区数控制在200个以内比较稳妥。3. 裁剪生效的判断方法与实践验证3.1 用EXPLAIN看partitions列一眼识别是否裁剪验证分区裁剪最直接的办法就是看执行计划。在MySQL 5.7里用EXPLAIN PARTITIONS SELECT ...在MySQL 8.0里EXPLAIN PARTITIONS这种写法已经逐步退出历史舞台直接执行EXPLAIN SELECT ...结果里就会带上partitions列显示这条SQL实际会访问哪些分区。模拟一个实测场景执行EXPLAIN SELECT COUNT(*) FROM order_log WHERE order_time 2024-01-01 AND order_time 2024-02-01;执行计划里partitions列显示为p202401说明优化器已经剪掉了其他所有分区。如果同一个表跑EXPLAIN SELECT COUNT(*) FROM order_log WHERE user_id 10086;执行计划里partitions列显示的可能是p202401,p202402,p202403,p_max这就是典型的分区裁剪没生效所有分区都要扫。看到这种结果第一反应应该是WHERE条件里没带分区键或者分区键条件被某种形式“遮住”了。3.2 用EXPLAIN FORMATJSON查看裁掉了多少分区想看更详细的信息可以用JSON格式的执行计划。在MySQL 8.0里执行EXPLAIN FORMATJSON SELECT COUNT(*) FROM order_log WHERE order_time 2024-01-01 AND order_time 2024-02-01;输出的JSON里有一个关键字段partitions_pruned它直接告诉你这个查询从全部N个分区里去掉了几个。比如显示partitions_pruned: 3 of 4意思就是总共4个分区裁掉了3个只留下1个。配合attached_partitions字段能清楚看到执行计划实际保留的分区列表。这里分享一个实用习惯在慢查询治理的时候把线上慢SQL的执行计划JSON抓出来重点看两部分内容一是rows_examined_per_scan是否异常大二是partitions_pruned是不是0。如果partitions_pruned长期是0那说明这个SQL压根没有走分区裁剪的逻辑优化方向就很明确了。3.3 常见失效场景排查清单分区裁剪失效的情况五花八门但核心原因翻来覆去就那几类我都列出来供你对照排查第一类是分区键根本没出现在WHERE条件里。这是最常见的情况SQL里全是非分区键的条件优化器无米下锅只能扫全部分区。解决方式就是结合业务语义主动把分区键条件补上去。第二类是分区键被函数包裹。比如WHERE DATE_FORMAT(order_time, %Y-%m) 2024-01或者WHERE YEAR(order_time) 2024。优化器虽然可能基于一定规则尝试做等价推导但大多数时候并不买账尤其是自定义函数和嵌套表达式裁剪基本失效。改成范围写法就好很多WHERE order_time 2024-01-01 AND order_time 2025-01-01。第三类是字段类型不匹配。分区键是DATETIME类型但应用层传入的是字符串直接比还行如果隐式转换发生在索引列上情况就会变复杂。比如分区键是整型order_id查询条件却写WHERE order_id 123456字符串转整型倒还能正常裁剪反过来把字符串字段和数值类型比较就可能出问题。最稳妥的做法是保证查询条件与分区键类型严格一致。第四类是使用NOT IN、、!这类否定条件。分区裁剪擅长处理确定区间一旦条件变成“不等于某值”“排除某些值”优化器很难从否定条件里倒推出“该扫描哪些分区”绝大多数情况下会保守地扫描全部或大部分分区。业务上能改写为IN列表或范围条件的尽量改写。第五类是JOIN查询里分区键推导不出来。两个表做关联时如果驱动表传过去的关联字段不能推导成本分区表的分区键常量优化器就没办法在分区表上做裁剪。此时可以试试调整连接顺序或者把分区表的分区键条件显式写在WHERE里。3.4 一句口诀帮你快速判断我总结了一句很实用的口诀团队里的同学照着念就不会写错分区键裸用在条件上别套函数别转换写范围写等值都行IN列表也可以否定条件绕着走。口诀展开解释就是尽量让分区键以原始列名形态出现在WHERE条件中函数、类型转换、表达式运算都会增加优化器的推导难度等值、范围、IN列表是裁剪最容易识别的三类条件反过来NOT IN、!这类带否定语义的条件大概率会把分区裁剪变成“全分区扫描”。4. 一个真实业务场景订单流水按月分区裁剪优化实录4.1 建表与分区设计实操我接手这个业务时表已经建好并积累了几个月的数据但设计上有个很大的问题分区键order_time没有进入主键导致建表时被迫改成了PRIMARY KEY (id, order_time)。这个改动看起来微小实际影响不小因为所有二级索引也都必须包含order_time索引长度增加了不少。这里提醒新同学建分区表之前先把唯一索引的约束想清楚不要等到上线后再改改主键的成本非常高。最终的简化建表语句如下CREATE TABLE order_log ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, order_time DATETIME NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(12,2) NOT NULL DEFAULT 0, PRIMARY KEY (id, order_time), KEY idx_user_time (user_id, order_time), KEY idx_status_time (status, order_time) ) ENGINEInnoDB PARTITION BY RANGE COLUMNS(order_time) ( PARTITION p202401 VALUES LESS THAN (2024-02-01), PARTITION p202402 VALUES LESS THAN (2024-03-01), PARTITION p202403 VALUES LESS THAN (2024-04-01), PARTITION p_max VALUES LESS THAN MAXVALUE );这里我把两个最常查询的组合都建成了复合索引(user_id, order_time)覆盖“查某个用户的某个时间段”(status, order_time)覆盖“查某个状态在某个时间段的数据”。分区裁剪负责把扫描范围缩小到单个月份分区索引负责在分区内部快速定位两者是接力关系不是替代关系。4.2 优化前后的SQL变化与执行计划对比这个业务有个高频查询是统计某天零点到当前时间某个商户成功状态的订单金额。最初线上写的SQL是这样的SELECT SUM(amount) FROM order_log WHERE status 1 AND order_time 2024-01-10 00:00:00 AND order_time 2024-01-11 00:00:00;注意这条SQL其实是带有分区键条件的但问题出在二级索引选择上。优化器在idx_status_time和全分区扫描之间权衡时因为status的区分度不高最终选择了走idx_status_time。由于复合索引顺序是status, order_time虽然能定位到当天数据但它必须先扫完所有分区里status1的索引项再在索引内部继续过滤order_time依然会访问全部4个分区。优化思路是既然order_time本身就是分区键干脆让优化器直接做分区裁剪。我改写成了等价写法SELECT SUM(amount) FROM order_log WHERE order_time 2024-01-10 00:00:00 AND order_time 2024-01-11 00:00:00 AND status 1;条件顺序调整后优化器优先用分区键做裁剪partitions列从p202401,p202402,p202403,p_max缩到了p202401扫描行数少了90%以上。实测结果查询耗时从3.71秒降到0.19秒那个凌晨报警再也沒出现过。我把优化前后结果整理成下表对比项优化前优化后访问分区全部分区p202401扫描行数约3500万约90万耗时3.71秒0.19秒主要代价全分区IO单分区索引定位这里有个很关键的实操心得条件顺序本身的调整只是表象真正起作用的是优化器在“走二级索引”和“先分区裁剪再扫描分区”两个计划之间做代价估算时结果变了。所以生产环境遇到这种情况我会建议你用EXPLAIN FORMATJSON验证一下如果partitions_pruned从0变成非0就说明改造成功。4.3 分区表附带的能力归档与清理用好分区裁剪后我还想提一个非常实用的副产品分区表在数据清理上的天然优势。以前清理历史数据用DELETE FROM order_log WHERE order_time 2023-01-01一次删几千万行锁范围大回滚日志也大搞不好把从库拖垮。换成分区表之后清理一个月的数据就是一条命令ALTER TABLE order_log DROP PARTITION p202301;这个操作是数据定义级别的底层直接删除对应分区文件速度极快也不会产生大量binlog回放压力。归档场景也可以配合ALTER TABLE ... EXCHANGE PARTITION把某个分区快速交换成一个独立表再备份这个表比传统的SELECT ... INTO OUTFILE或pt-archiver效率高很多。4.4 不要忽略分区表自身的代价分区裁剪好用但分区表不是银弹我见过有人把系统里所有大表一概分区结果反而更慢。原因通常是分区键选得不对比如按照status这种低区分度字段做HASH分区业务查询却都带user_id分区键和查询条件对不上裁剪永远失效。还有一点容易忽略分区表在DDL变更、数据导入、主从复制上的开销比普通表要高。每增加一个分区复制线程都要额外处理分区事件做一次全表ALTER TABLE可能需要重建全部分区。所以分区表的定位应该是“业务查询模式稳定、数据量大且有明显时间维度或枚举维度的大表”而不是所有表的默认选项。5. 常见问题与排查技巧实录5.1 问题速查表我在处理分区裁剪相关的线上问题时发现很多问题都反复出现整理成一张速查表方便你遇到同类问题快速定位现象可能原因解决建议EXPLAIN显示扫描全部分区WHERE条件没有分区键结合业务语义补上分区键条件有分区键条件但裁剪仍失效分区键被函数包裹或隐式类型转换改成范围或等值裸列条件JOIN查询完全不裁剪关联条件无法推导出分区键常量调整驱动表顺序或显式加分区键条件裁剪生效但查询依然很慢分区内数据量仍然过大检查分区粒度配合二级索引优化INSERT时很慢分区数过多或索引过多控制分区数量裁剪不必要的索引执行计划的partitions_pruned一直为0统计信息缺失或SQL书写习惯问题收集统计信息改写SQL核对索引5.2 一个容易被忽略的坑统计信息与执行计划抖动分区裁剪虽然很多情况下是根据条件静态判断的但优化器是否选择“裁剪后再走索引”这个路径会受统计信息影响。分区表的数据分布如果长期不做更新information_schema里的分区统计可能严重失真导致优化器选了坏的执行计划。我遇到过一种情况某个分区新增了大量数据之后没做ANALYZE TABLE同一条SQL前一天还走分区裁剪加索引后一天执行计划突然变成全分区扫描。排查到最后发现是直方图统计过期优化器认为某个非分区键过滤条件更“划算”。处理方式就是定期对大分区表执行ANALYZE TABLE order_log;不过它开销也不小建议放在业务低峰期或者由运维平台每周自动调度一次。5.3 用慢查询日志和行扫描数验证优化效果如果你不确定一次优化到底有没有生效最扎实的办法是看行扫描数。MySQL慢查询日志里有两个字段很关键Rows_examined和Rows_sent。一个健康的裁剪查询Rows_examined应该接近实际命中的行数如果Rows_examined动辄几千万而Rows_sent只有几百行说明裁剪极可能失效了。开启慢查询记录SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;优化完之后拿同样的业务场景跑一遍对比Rows_examined的变化。我通常要求团队同学把改动前后的Rows_examined贴到工单里一眼就能看出裁剪有没有真正落地。5.4 独家经验分区键条件加在哪里有讲究写到这里再分享一个实操里很容易忽略的细节。对于同一个分区键条件放在WHERE里和放在JOIN的ON里效果可能完全不同。分区表参与JOIN时如果分区键条件写在ON子句里优化器可能在关联阶段拿到驱动表的值之后再去裁剪裁剪时效和要求更高而把分区键条件直接写在WHERE子句里往往能更早地触发裁剪。比如SELECT a.id, b.order_no FROM merchant a JOIN order_log b ON a.id b.merchant_id WHERE b.order_time 2024-01-01 AND b.order_time 2024-02-01;这种情况下MySQL有机会在扫描order_log之前先用order_time的范围完成分区裁剪。如果你把order_time条件只写在ON后面部分版本下的优化器不一定能把它提前推导到分区表访问之前执行计划可能就完全不同。我的习惯是分区表作为被驱动表时分区键条件强制写在WHERE里减少执行计划的不确定性。如果你也在维护一张几亿行的大表我的建议是先别急着上分布式方案先看看你的分区表有没有真正利用好分区裁剪。它的实现原理并不复杂但价值极大前提是你把分区键选对、把SQL写对、把执行计划看对。我个人踩过最大的坑就是一开始只关注“表有没有分区”忽略了“查询有没有触发裁剪”结果分区表反而成了拖累。现在每次排查分区表性能问题我第一个动作永远是打开EXPLAIN看partitions列和partitions_pruned先确认裁剪是否生效再谈索引和SQL改写。最后再分享一个小技巧如果你的业务经常是“某个时间段某个业务实体”的组合查询建二级索引时记得把分区键放在索引的最后一位比如(user_id, order_time)这样分区裁剪先缩小到单分区索引再在分区内部快速定位两层优化叠加性能表现会稳得多。