企业官网建设流程全解析

前阵子帮一个业务线排查慢查询，凌晨四点收到报警，一条统计订单的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) ) ENGINE=InnoDB 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 table's 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) ) ENGINE=InnoDB 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) ) ENGINE=InnoDB 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 FORMAT=JSON查看裁掉了多少分区

想看更详细的信息，可以用JSON格式的执行计划。在MySQL 8.0里执行：

EXPLAIN FORMAT=JSON 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) ) ENGINE=InnoDB 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，虽然能定位到当天数据，但它必须先扫完所有分区里status=1的索引项，再在索引内部继续过滤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秒，那个凌晨报警再也沒出现过。

我把优化前后结果整理成下表：

这里有个很关键的实操心得：条件顺序本身的调整只是表象，真正起作用的，是优化器在“走二级索引”和“先分区裁剪再扫描分区”两个计划之间做代价估算时，结果变了。所以生产环境遇到这种情况，我会建议你用EXPLAIN FORMAT=JSON验证一下，如果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 问题速查表

我在处理分区裁剪相关的线上问题时，发现很多问题都反复出现，整理成一张速查表，方便你遇到同类问题快速定位：

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)，这样分区裁剪先缩小到单分区，索引再在分区内部快速定位，两层优化叠加，性能表现会稳得多。