企业官网建设流程全解析

SQL 表分区性能优化，从定位到落地的一次完整复盘

前段时间接手了一张订单流水表，单表跑了快三年，数据量干到接近两亿行。最核心的几条查询在业务低峰期都要跑四五秒，高峰期锁等待和IO压力直接拖垮了一批报表接口。索引加了又加，慢SQL还是陆陆续续冒出来，DBA团队最后给出的方案就是表分区。当时我对分区的认知还停留在“把表拆成几张子表”的层面，等真正做完这轮优化，把表分区、分区裁剪、运维收益这几个点彻底想清楚之后，才发现这玩意儿远比网上大部分教程描述的更有价值。这篇文章我就用这次的实战经历，把SQL表分区从原理、选型到踩坑完整梳理一遍，希望能给正在被大表慢查询折腾的朋友一些参考。

这篇文章适合什么人群？一类是后端开发，刚好在维护千万级以上的大表，索引优化已经到瓶颈；另一类是初级DBA或者运维，需要了解分区表在日常运维中的使用方式；还有一类是面试前突击数据库知识的同学，看完之后至少能把分区裁剪、分区键选择、分区维护这几个点讲得跟做过项目一样。

1. 表分区到底解决了什么问题

1.1 先搞清楚分区和分库分表的边界

很多同学一提到大表优化，脑子里跳出来的第一个方案就是分库分表，比如MyCAT、ShardingSphere那套。但分库分表本质上是在应用层或者中间件层做路由，把数据分散到多个物理实例或者多张物理表中去，随之而来的是分布式事务、跨节点join、全局主键、数据迁移这一堆麻烦事。

表分区则完全不同。它是在单个实例内部、单张逻辑表内部做物理存储的拆分。一张分区表从应用的角度看还是同一张表，SQL不用改（大部分情况下），但底层的存储引擎会把数据按照分区规则放到不同的物理文件中。以MySQL的InnoDB为例，每个分区实际上对应独立的表空间文件（或者独立段），有自己独立的B+树索引结构。这就意味着，一条查询如果能够被裁剪到只访问其中某个分区，那扫描的数据量就是从“两个亿”变成“两千万”甚至“两百万”的差别。

分库分表和表分区不是非此即彼的关系。实际架构中，很多团队是先用表分区扛住单实例下的数据量增长，等连分区表都撑不住了，再考虑分库分表。分区是分库分表之前非常平滑的一级缓冲，它不需要改动应用层的数据源配置，不需要引入额外的中间件，纯粹是数据库层面的改造。

1.2 分区裁剪：性能提升的核心机制

分区的性能收益，核心就四个字：分区裁剪（Partition Pruning）。优化器在执行计划生成阶段，会分析SQL语句中的WHERE条件，判断哪些分区中包含满足条件的数据，然后只扫描这些分区对应的物理文件。其余分区的数据，就算里面有几百条符合条件的结果，优化器也根本不会去碰。

举个例子，订单表order_info按create_time做了RANGE分区，每个月一个分区。查询条件如果是WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'，优化器直接定位到p_202401这一个分区开始扫描。不分区的情况下，这个查询是全表扫描，哪怕有普通索引，也需要走索引后回表的流程，经历大量随机IO和聚簇索引查询；分区之后，整个扫描范围被物理隔离到一个小文件里，数据量缩小几十甚至上百倍，查询性能自然天差地别。

这里有一个很多人忽略的细节：分区裁剪不仅仅作用于范围查询，对于等值查询、IN查询、甚至JOIN条件，只要分区键出现在合适的条件下，优化器都有机会裁剪。比如WHERE user_id = 12345配合HASH分区，如果是按user_id做的HASH分区，优化器可以快速计算出user_id=12345落到的那个具体分区，然后只查这一个分区。这其实和分库分表中间件的路由逻辑非常像，只是它发生在数据库引擎内部，对应用透明。

1.3 运维层面的隐性收益

除了查询加速，分区表在运维上的优势才是真正让我觉得“早该做”的地方。最典型的就是数据归档和清理。

做这行的人都知道，删除海量数据是一件非常恐怖的事。如果要在两张行的订单表里删除半年前的历史数据，一条DELETE FROM order_info WHERE create_time < '2023-07-01'执行下去，InnoDB要逐行扫描，逐行加锁，逐行写undo log和redo log，产生大量的binlog，几乎没有DBA敢在生产环境直接这么干。哪怕数据量不算大，锁竞争和主从延迟也够喝一壶。

分区表的做法就优雅多了，直接ALTER TABLE order_info DROP PARTITION p_202306，这是纯元数据操作，底层相当于删一个文件，秒级完成，不产生任何行级锁和大量日志。同理，如果需要清理一个月的数据，TRUNCATE PARTITION比 DELETE 快几个数量级。

另一个隐性收益是数据冷热分离的成本降低了。可以把历史分区放到机械硬盘或者低成本存储，把热数据分区放到SSD。虽然MySQL原生不直接支持这种跨存储介质的分区方案，但在云数据库或某些涉及到表空间管理的场景下，可以手动迁移分区对应的表空间文件，SQL Server在这方面有更成熟的文件组方案，后面实操章节我再展开。

2. 分区方案怎么选：分区类型、分区键与粒度

2.1 四种分区类型的适用场景对比

选分区类型之前，先得把主要玩家盘点清楚。MySQL支持的四种分区类型是RANGE、LIST、HASH、KEY，各有各的适用场景。我把它们放到一起对比：

从我实际使用的情况来看，80%的业务场景用RANGE分区就够了，尤其是时间字段参与查询条件的情况。报表类、流水类、日志类业务，几乎清一色按时间做RANGE分区。LIST分区在按地区、按类型做数据隔离时有优势，比如一张全国销售表，按region分区，查华东区数据时直接只扫华东分区。HASH和KEY更适合完全等值查询的场景，比如按user_id查用户的操作记录，这类查询能够精确定位到某一个分区，极其高效。

还有一点要注意，不同的数据库产品对分区的支持差异非常大。上面说的是MySQL 5.7和8.0的情况。SQL Server中分区不是表级别独立设计的，而是通过分区函数 + 分区方案 + 文件组来实现，逻辑上可以做到“一张表的数据分布到不同文件组”的效果，PostgreSQL则更灵活，支持声明式分区和继承式分区。千万别把MySQL的分区语法直接套到别的数据库上。

2.2 分区键怎么选：从查询习惯反推

分区键的选择是整个分区方案设计中最关键、也最容易翻车的一步。如果分区键选得不好，分区不但没有收益，反而会引入额外的元数据开销和写入热点。我的经验是从“最频繁出现的查询条件”反推分区键，而不是从表结构本身去选。

具体来说，把这张表所有的慢查询SQL和核心业务SQL收集出来，看WHERE条件里出现频率最高的字段是哪个。比如订单表，90%的查询都带了create_time范围，那分区键就选create_time；如果多数查询是WHERE user_id = ?这种按用户查询的，那就考虑按user_id做HASH分区或KEY分区。

这里有一个非常常见的踩坑点：分区键必须包含在表的主键和唯一键中。InnoDB的约束要求，如果表有主键或者唯一索引，那么所有分区键字段都必须包含在这些唯一约束的列中。原因在于MySQL需要通过分区键来确定一条记录属于哪个分区，而唯一性校验是分区级别的，做不到跨分区的全局唯一约束。如果不遵守这条规则，建表的时候会直接报错。

举个例子，一张表主键是order_id，如果你试图按create_time做RANGE分区，就会报“A PRIMARY KEY must include all columns in the table's partitioning function”的错误。解决办法通常是两种：一是联合主键，把主键改成(order_id, create_time)；二是设计上就把create_time纳入唯一约束。这在实际项目中会让不少开发同学挠头，因为联合主键会改变已有业务代码的写法。所以，分区键的选择在项目早期就要定好，表一旦上线再改分区方案，迁移成本很高。

2.3 分区粒度怎么定：别为了分区而分区

分区粒度的把握，是个纯粹的工程经验问题。分太粗，比如一年一个分区，那数据量还是很大，裁剪效果不明显；分太细，比如一天一个分区，虽然裁剪粒度很细，但分区的管理开销、元数据膨胀、表对象数量过多的问题会浮现出来。

MySQL官方建议一张表的分区数量上限是8192（MySQL 8.0），但实际上超过几百个分区之后，部分场景下的表现就开始下降。尤其是涉及全局索引扫描、统计信息收集、DDL操作（如ALTER TABLE重建表）时，分区数量过多会让成本线性上升。我个人的习惯是：按自然月做RANGE分区，保留最近24到36个分区，历史分区定期归档删除。这个粒度对于绝大多数订单、流水类业务都够用了，既能保证单分区数据量在一个可控范围内（月流水千万级），又不会让分区数量膨胀。

还有一点是关于写入热点的。如果按时间做RANGE分区，写入永远集中在最新一个分区上，这其实是正常的，因为业务的热数据本来就在最近时间段。但如果是按HASH分区，要注意HASH分区数的设置。HASH分区的取模逻辑对分区键的数值分布非常敏感，如果分区数选得不合理（比如不是2的幂次），或者分区键本身分布不均匀，容易出现某个分区数据量暴涨的情况。设计阶段可以用SELECT user_id % 8, COUNT(*) FROM order_info GROUP BY user_id % 8这样的语句去校验分布是否均匀，再决定分区数。

3. 实操过程与核心环节实现

3.1 MySQL分区表建表实操

这次的订单表优化，我选的是RANGE按月分区。下面用一个简化版本展示建表过程，关键参数和坑点都会标注清楚。

CREATE TABLE `order_info` ( `order_id` bigint NOT NULL, `user_id` bigint NOT NULL, `create_time` datetime NOT NULL, `amount` decimal(12,2) DEFAULT NULL, `status` tinyint DEFAULT NULL, PRIMARY KEY (`order_id`, `create_time`), KEY `idx_user_id` (`user_id`), KEY `idx_create_time` (`create_time`) ) ENGINE=InnoDB PARTITION BY RANGE (YEAR(create_time) * 100 + MONTH(create_time)) ( PARTITION p_202301 VALUES LESS THAN (202302), PARTITION p_202302 VALUES LESS THAN (202303), PARTITION p_202303 VALUES LESS THAN (202304), PARTITION p_future VALUES LESS THAN MAXVALUE );

这里有两个关键点要解释一下。

第一，PARTITION BY RANGE (YEAR(create_time) * 100 + MONTH(create_time))这种写法有点绕，为什么要用这个表达式而不是直接用TO_DAYS(create_time)？直观原因是后续的分区维护SQL可读性更高，VALUES LESS THAN (202302)一眼就能看出是2023年2月的数据。当然也可以用TO_DAYS('2023-02-01')这种方式，结果一样，只是看分区定义的时候没那么直观。要注意MySQL的RANGE分区只支持整数作为分区边界（8.0仍如此），所以日期必须转换成整数，RANGE COLUMNS也可以直接用datetime类型的列做分区，语法更简洁，不过部分版本和备份恢复工具兼容性有差异。

第二，主键变成了PRIMARY KEY (order_id, create_time)。这是前面提到的强制规则，不这么写，MySQL会直接拒绝建表。如果业务上确实需要单独用order_id查询，应用层可以改成“先用order_id查到create_time，再带上create_time查一次”的方式，或者干脆接受联合主键对写入性能的微小影响。

另外，预先建了一个p_future分区，对应MAXVALUE，这是为了防止后续忘记加分区导致数据插入报错。等新月份到来之前，我把p_future这个分区拆分成新的月份分区和新的p_future。这种“预留一个未来分区”的做法，是我强烈推荐的，很多线上事故就是忘了提前建下一个月的分区导致的。当然，更好的做法是写定时任务，每月自动检测并新增分区，后面会展开讲。

3.2 分区维护：新增、删除、拆分

建表只是开始，分区表的日常维护才是重头戏。我把经常用到的分区维护操作整理成了一份速查表：

我需要重点强调两个操作背后的逻辑。

一是REORGANIZE PARTITION的使用。ADD PARTITION只能往RANGE分区链的末端追加，但如果你用VALUES LESS THAN MAXVALUE兜底，那么MAXVALUE分区总是处于最末端，这时候想插入一个中间月份的分区，就必须用REORGANIZE PARTITION p_future INTO (...)来拆分。这个操作在MySQL中是允许的，原理是把MAXVALUE分区的数据重写到新的分区集合中。由于MAXVALUE分区里正常情况下没有业务数据（它只兜底未来数据），这个REORGANIZE操作很快。这也是为什么我建议保留MAXVALUE分区的目的。如果你用的工具如pt-online-schema-change在线变更，对分区表支持可能有限，操作时要特别小心。

二是DROP PARTITION的代价。很多人潜意识里觉得分区表删数据应该很快，这个判断没错，但有前提。如果分区表上有全局二级索引（MySQL 8.0虽然支持，但使用场景有限），DROP PARTITION的时候要同步维护全局索引，代价比纯本地索引高很多。MySQL 8.0之前的版本不支持全局索引，每个分区的二级索引实际上是独立的分区索引，这导致按非分区键查询时，需要逐个分区扫描索引，性能反而不如普通表。这是分区表一个很久远的痛，8.0才逐步缓解。

另外补充一个批量维护的思路：既然每月都要手动新增分区，不如把新增分区的SQL写进定时任务里自动执行。比如每个月的25号自动创建下一个月的分区，这样即使开发同学休个假，也不用担心月底线上数据写不进去。不管是Shell脚本配合crontab，还是直接写在业务系统的调度任务里，核心逻辑都一样，就是先查INFORMATION_SCHEMA确认目标分区不存在，然后执行REORGANIZE。我遇到过不少因为忘记建分区导致的“表无法写入”事故，基本都是凌晨0点整准时报出来，那滋味体会一次就够了。

3.3 查询代码要不要改？分区裁剪的隐性前提

分区表最大的卖点就是“应用透明”，但这个透明是有前提的：查询条件必须包含分区键，并且分区键不能被表达式或函数包裹。如果业务代码里查询压根没带分区键，优化器无从裁剪，结果就是扫描所有分区，性能可能比普通表更差。

比如这次项目里，订单查询接口的入参经常是status + create_time，但有一部分老接口只传order_id或者只传user_id，这类查询在分区表上就没法利用分区裁剪。解决方案是业务层改造，在SQL生成时强制拼接create_time条件；无法拼接的地方，就老老实实用二级索引，接受性能瓶颈。

还有一个更隐蔽的坑：隐式类型转换会让分区裁剪失效。比如分区键是整数表达式，但查询条件里传的是字符串。MySQL在比较时会把字符串转成数字，分区裁剪在部分版本上会“认不出来”，直接退化成全分区扫描。排查这类问题，最好在SQL前面加EXPLAIN查看partitions列，看它是否精确指向了几个分区，还是指向了所有分区。

我把常用的分区表查询验证方式总结一下：

-- 查看执行计划和实际访问的分区 EXPLAIN SELECT * FROM order_info WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'; -- 结果中的 partitions 列会显示 p_202401，如果显示所有分区名，说明裁剪失败

很多开发同学平时看EXPLAIN只看type、key、rows这三个字段，忽略了partitions列，这是不对的。分区表优化前后的对比，最直观的体现就在这一列。如果这一列显示p_202401，那说明优化器精准地砍掉了99%的数据范围；如果显示p_202301,p_202302,...,p_future这样一长串，那这个查询等于零收益，甚至还会因为分区元数据的开销变得更慢。

3.4 SQL Server和PostgreSQL的分区差异（快速认知）

既然热词里出现了很多SQL Server相关的内容，这里简单对比一下，避免看完MySQL的教程就以为天下大同。

SQL Server实现分区的方式和MySQL完全不同：先创建分区函数定义边界值，再创建分区方案把分区映射到文件组，最后建表时通过ON 分区方案(分区列)把表关联到分区方案上。因为是文件组级别的隔离，SQL Server可以把不同分区放到不同的物理磁盘，真正实现冷热数据分层存储，这是MySQL原生做不到的。SQL Server的分区表也要求唯一索引包含分区列，这和MySQL思路一致。

PostgreSQL从10开始支持声明式分区，语法上更现代一些，比如PARTITION BY RANGE (create_time)，子表通过FOR VALUES FROM ... TO ...指定范围。PostgreSQL的分区表查询时，优化器通过约束排除（Constraint Exclusion）实现类似分区裁剪的效果。但PostgreSQL分区表的维护比MySQL更灵活，可以直接对子表做VACUUM、ANALYZE、索引重建等操作。如果项目是PostgreSQL，维护分区表反而比MySQL更容易做精细化管理。

不同数据库的分区实现细节差异很大，但设计思路是相通的：都是“数据物理隔离 + 优化器裁剪 + 运维解耦”。理解了这个本质，换任何数据库都能快速上手。

4. 常见问题与排查技巧实录

4.1 分区没生效？从EXPLAIN开始排查

如果优化之后发现查询还是慢，第一件事就是检查分区裁剪是否真的发生了。前面说了，EXPLAIN的partitions列就是最直接的证据。我见过太多人建完分区表后以为万事大吉，结果一查EXPLAIN，partitions列显示的是全部30多个分区，等于一张普通表。

分区裁剪失效的原因，我整理了最常见的几个：

• WHERE条件里没有分区键，优化器无从裁剪。
• 分区键被函数包裹，比如WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2024-01-01'，这种情况下MySQL大概率无法裁剪，正确写法是WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。
• 配置了optimizer_switch中某些开关被关闭，比如derived_merge、condition_fanout_filter等情况影响优化器决策，这类问题相对罕见，但遇到时可以检查。
• 查询使用了JOIN，且关联条件没有把分区键传递到下推的表中，导致被驱动表没法裁剪。
• 分区表达式太复杂，优化器无法在编译阶段计算边界匹配。比如RANGE分区用了自定义函数，优化器为了保险起见直接放弃裁剪，全分区扫描。

排查技巧就一句话：任何一次慢查询排查，都先在EXPLAIN里看partitions列。这一步能帮你节省至少一半的时间。

4.2 数据倾斜：分区大小严重不均怎么办

分区表最常见的性能隐患之一就是数据倾斜。RANGE分区容易出现一种情况，比如月初某天搞了个大促，数据量翻了十倍，那个月的分区体积明显膨胀，查询落在那个分区上依然很慢。这时候单纯靠分区已经救不了了，得靠二级索引、并行查询等手段配合。

更麻烦的是HASH分区的倾斜。HASH分区看起来是均匀取模，但如果分区键本身的分布就有问题，比如某个热点用户产生了海量数据，那么他所在的HASH桶就是热点桶，很多查询集中打到一个分区上。解决思路是换更均匀的分区键，或者增加分区数，让单个热点用户的数据尽量被分散到多个分区。不过HASH分区是严格按分区键取值取模的，单个用户的数据只会落在固定的一个分区里，换键或增加分区数只是让“哪个分区更热”发生变化，真正彻底解决热点还是要靠业务层缓存或者对热点用户做单独的路由策略。

对于RANGE分区的时间倾斜，我的经验是**可以用“按周分区”替代“按月分区”**来缓解短时间数据洪峰。比如大促那周单独做一个周分区，用REORGANIZE把那一周拆出去，既有独立分区的可管理性，又不至于让一个月的数据量过于庞大。

4.3 统计信息不更新引发的执行计划问题

分区表加了分区之后，统计信息的收集策略要不要改？这个问题的答案是：要。

MySQL里，ANALYZE TABLE默认会收集所有分区的统计信息，如果分区数量多，数据量大，这个操作本身就比较耗时。而如果长期不收集统计信息，优化器又可能基于过期的统计信息做出错误的裁剪决策，包括错误地评估某个分区的数据量，导致选择错误的执行计划。

我遇到过一个实际问题：某个分区表一个月没跑ANALYZE，某天一个带分区键的查询，优化器估算出来的结果集大小严重偏离实际，最终选择了全分区扫描而不是只扫一个分区，查询耗时从200毫秒变成8秒。手工ANALYZE之后恢复正常。解决办法是给这类大分区表配置周期性的统计信息收集任务，频率一般和写入量相关，数据变化快就每天一次，变化慢可以每周一次。SQL Server中是通过UPDATE STATISTICS配合WITH FULLSCAN或抽样比例来做，思路相同。

4.4 全局二级索引问题

MySQL 8.0之前，分区表的二级索引都是分区内的局部索引。这意味着按非分区键做等值查询时，如果有索引，需要把每个分区都扫描一遍，每个分区的二级索引指向各自分区内的数据。索引的层数没有变少，查询性能基本等于把多个小索引全部查一遍再合并结果。

MySQL 8.0引入了全局二级索引的概念，解决了这个痛点，但引入全局索引也有代价，每次写入都要修改全局索引，写入放大的问题需要权衡。如果你的数据库版本还在5.7，遇到非分区键查询的慢SQL，建议不要在分区表上建太多二级索引，反而要考虑把这类查询改造成“带上分区键条件”的写法。比如业务层先查一次索引表拿到create_time，再带上create_time去查分区表。

这个“先用辅助索引定位，再带分区键二次查询”的思路，弥补了老版本MySQL分区表局部索引的缺陷。听起来多查了一步，实际上因为主查询能精确裁剪到一个分区，整体性能反而比全分区扫索引更好。

4.5 分区表DDL的锁与在线变更

对分区表做ALTER TABLE操作（尤其是增加、删除、重组分区），MySQL 8.0之前大部分会锁表。虽然DROP PARTITION和TRUNCATE PARTITION在很多场景下是元数据操作，速度很快，但REORGANIZE PARTITION在数据量较大时耗时较长，期间对表的读写可能受影响。

生产环境的分区维护时间窗口，我通常放在凌晨低峰期，并且用脚本检测运维操作是否完成。如果实在不能接受停机，可以考虑业务双写方案，或者利用pt-online-schema-change把分区变更转为触发器方式在后台慢慢跑，但工具对分区表的支持不够完善，操作前一定先在测试环境完整演练一遍。

MySQL 8.0的原子DDL特性把很多ALTER操作改成了原子的，但REORGANIZE PARTITION的锁行为依然要测试验证。别轻信网上说“分区操作不锁表”的说法，版本、数据量、是否有全局索引都会影响实际表现。

4.6 常见问题速查表

最后把分区表日常运维中比较典型的场景整理成一张速查表，方便直接抄作业：

5. 这次优化做完后的客观效果

分区方案上线一个月，我把几项核心指标做了对比，这是最有说服力的部分。

订单列表查询场景，原来无论怎么走索引，都要在近两亿行数据的B+树里来回折腾，平均耗时4.2秒。分区后，按月份裁剪到单个月的数据量，实测200毫秒到400毫秒之间，提升了一个数量级以上。报表类的统计SQL，原来扫描全表跑十几秒，现在只扫对应分区，3秒内出结果。这是数据量物理范围缩小带来的最直接红利。

还有一个容易被忽视的收益：备份和恢复效率大幅提升。用物理备份工具对分区表做备份时，可以只备份最近几个热分区，不再需要每次都全量备份几亿行数据。某个历史分区发生数据损坏时，也只需要恢复对应的分区文件即可，不用整表恢复，RTO从小时级降到分钟级。

不过也要说句公道话，分区不是银弹。它解决的是“数据量持续增长带来的扫描范围过大”问题，解决不了所有慢SQL。比如SQL本身写法很烂，或者索引设计不合理、大量回表、锁竞争，这些靠分区是治不了的。做性能优化，老老实实从慢查询日志分析、执行计划阅读、索引设计做起，分区只是工具箱里的一件强力武器。

6. 关于表分区，我自己的一些体会

最后聊一点主观经验。最开始我以为分区就是照葫芦画瓢建个表，真正深入之后才发现，分区方案的成败，几乎全在事前设计阶段：分区键选什么、分区粒度多细、历史数据怎么归档、定时维护脚本怎么写、老查询要不要改、缓存策略要不要调，这些细节决定了上线后是“真香”还是“踩雷”。

我的建议是，如果你手上有千万级以上数据量的表，并且查询模式相对固定，可以尽早考虑分区。但是不要在项目上线前临时抱佛脚做分区改造，尽量在设计阶段就把分区键纳入表结构设计，尤其是主键和唯一索引的设计。因为表一旦跑起来，再改分区方案涉及数据迁移、业务代码改造，成本会成倍增加。

另外一个实用技巧是，在做分区方案前，先对目标表进行一次数据分布摸底，看看时间字段的分布、热点查询的模式、数据增长的速率，这些数据能帮你更理性地决定分区类型和分区粒度，而不是拍脑袋定“按周”还是“按月”。

分区这件事，知道它存在的人很多，真正在合适的场景下用好它的人不算多。希望这篇复盘，能帮你少走一些我走过的弯路。