在数据库领域讲到SQL性能优化表分区是绕不开的一个方案。前几天我帮一个朋友看慢查询订单流水表3亿行按user_id查最近订单索引建了还是慢。执行计划倒是走了索引但回表太频繁热数据散落在几个亿的页面里Buffer Pool几乎每次都要去磁盘捞。后来没有继续加索引而是把表改成按时间分区同时把SQL补上order_time范围条件问题才算真正解决。这篇文章就把表分区的原理、选型、SQL写法和常见坑讲清楚适合正在做慢SQL优化、数据库设计或者数据归档的同学。1. 表分区的性能红利到底在哪一张表拆开查询少干活1.1 分区裁剪优化器帮你“扔掉”无关数据分区表在逻辑上还是一张表但物理上会被拆成多个独立的存储段。以MySQL InnoDB为例每个分区有独立的数据文件和索引组织结构查询时优化器会根据WHERE条件只扫描命中的分区其他分区直接跳过这个机制叫分区裁剪。比如订单表按order_time做RANGE分区查询条件写成WHERE order_time 2025-01-01 AND order_time 2025-02-01优化器能推导出只需要扫描1月份这一个分区几亿行表瞬间变成几百万行分区。这是表分区最核心的性能来源也是很多慢SQL被救活的根本原因。所以判断一张表要不要分区第一件事不是看数据量而是看高频SQL里能不能带出分区键的过滤条件。1.2 存储、维护和并行容易被忽略的三重收益分区不只是让查询变快。数据清理场景里普通表DELETE一年前的数据可能要在凌晨跑几个小时还会产生海量undo日志和binlog。分区表直接DROP PARTITION秒级完成因为这个操作是元数据级别的不逐行走存储引擎。另外老数据归档也能用EXCHANGE PARTITION把某个分区快速变成一张独立表整个过程对在线业务影响很小。备份也可以按分区做哪天某个分区数据坏了恢复范围会比整表小很多。Oracle和PostgreSQL在分区级别还能做并行扫描MySQL 8.0虽然并行能力有限但多个查询并发访问不同分区时InnoDB的并发粒度也会好一些。1.3 什么时候分区反而没意义如果一张表本身只有几百万行一个普通二级索引就能扛住分区只会增加建表、维护、统计信息采集的复杂度。如果业务SQL写得很随意条件里从来不碰分区键分区表不会带来裁剪效果反而可能因为每个分区都有独立的B树让某些查询比普通表更慢。分区不是银弹。建分区之前先想清楚你到底想让优化器帮你扔掉哪部分数据。想不明白就不要先动手ALTER TABLE。2. 四种分区策略怎么选按范围、按列表还是按哈希2.1 RANGE分区时间序列表的标准答案RANGE分区按连续区间切分最常见的是按日期。MySQL建表示例CREATE TABLE order_p ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(12,2), order_time DATETIME NOT NULL, PRIMARY KEY (id, order_time) ) PARTITION BY RANGE (TO_DAYS(order_time)) ( PARTITION p202501 VALUES LESS THAN (TO_DAYS(2025-02-01)), PARTITION p202502 VALUES LESS THAN (TO_DAYS(2025-03-01)), PARTITION p202503 VALUES LESS THAN (TO_DAYS(2025-04-01)) );RANGE分区的边界是“前闭后开”LESS THAN后面的值不包含在分区内。这种分区的好处非常直接时间范围查询能裁剪到指定分区新数据永远进最新分区过期数据可以直接按分区清理。需要注意如果分区键值超过了最大边界MySQL会直接报错所以未来分区必须提前建。2.2 LIST分区枚举值明确的业务维度LIST分区按显式枚举值切分适合业务上能把数据归属到确定类别的场景。比如按地区、业务线、订单状态PARTITION BY LIST (region) ( PARTITION p_east VALUES IN (shanghai, hangzhou), PARTITION p_north VALUES IN (beijing, tianjin), PARTITION p_other VALUES IN (guangzhou, shenzhen) )LIST的好处是热点维度可以单独放一个分区比如状态为“待支付”的高频查询数据量不大放进单独分区后裁剪效果非常好。缺点也很明显枚举值需要稳定如果业务新加了一个城市而分区定义里没有插入就会失败。枚举值变化频繁的表用LIST要做好分区维护的持久准备。2.3 HASH/KEY分区没有自然分区键时的均衡方案很多表没有天然的时间范围高频查询是user_id等值查询找不到一个“从哪到哪”的区间。这种情况下可以用HASH分区把数据按哈希值均匀打散到固定数量的分区里。CREATE TABLE user_action_p ( action_id BIGINT NOT NULL, user_id BIGINT NOT NULL, action_time DATETIME, PRIMARY KEY (action_id, user_id) ) PARTITION BY HASH(user_id) PARTITIONS 16;HASH分区适合等值查询。WHERE user_id 123可以直接算出该去哪号分区O(1)定位。但如果是范围查询比如user_id 1000优化器无法精确定位必须扫描全部分区性能反而更差。MySQL还支持KEY分区区别是KEY使用MySQL内置哈希函数允许指定多个列不需要分区键必须是整数。选HASH还是KEY核心取决于查询条件能不能稳定命中分区键。2.4 主流数据库分区能力的一个横向对照不同数据库的分区实现差异很大设计时一定要查对应的官方文档。MySQL、PostgreSQL、Oracle、SQL Server的语法和细节如下表所示数据库原生分区类型分区裁剪全局索引注意事项MySQLRANGE、LIST、HASH、KEY支持不支持主键和唯一键必须包含分区键PostgreSQLRANGE、LIST、HASH声明式分区支持分区级索引支持分区表唯一约束分区裁剪成熟维护文档多OracleRANGE、LIST、HASH及复合分区支持支持全局索引和分区索引功能最完整但License成本高SQL Server主要基于分区函数和方案实现RANGE支持支持对齐/非对齐索引不建议照搬MySQL语法这个对比表不是让大家马上迁移数据库而是提醒一点网上抄了一段“分区表建表语句”不代表所有数据库通用。MySQL的分区表设计拿到PostgreSQL里经常要重写。3. 分区键怎么定先回答三个问题再写建表语句3.1 问题一你的高频SQL都在过滤哪一列这是决定分区键最关键的问题。把慢查询日志和业务核心SQL拉出来统计WHERE和JOIN条件里每个列出现的次数。分区键最好就是高频条件列。如果过滤条件是范围比如时间区间、金额区间优先RANGE如果过滤条件是等值比如user_id、order_id优先HASH或LIST。比如一张订单表业务上“按时间统计”和“按时间段跑报表”是核心那order_time就应该当分区键。如果核心是“只查某个人的订单”SQL却总是只传user_id不带时间那按order_time分区就是给自己挖坑。3.2 问题二数据能不能均匀落在各个分区HASH分区要求分区键的基数足够高、分布足够均匀。user_id、device_id这类字段做HASH分区通常没问题但status这种只有几个枚举值的字段就不适合因为哈希后数据仍然会堆到几个热点分区里。RANGE分区也要看数据分布。按月份分区平时一个月几百万行双十一一个月几千万行热点月份分区体积比别人大十倍。这种情况下分区裁剪确实能减少扫描量但落到热点分区时依然慢。解决思路是把热点月份再拆分或者在这个分区内配合二级索引继续收窄查询范围。3.3 问题三数据生命周期里有没有“删除/归档”需求分区表一个很大的价值是快速清理和归档旧数据。如果业务规定订单数据只保留一年按月RANGE分区就是最佳组合新数据进当前月分区超过一年的分区直接删除或归档。这个过程如果不用分区表就需要“分批DELETE”写坏很多DBA的头发。但如果你的表是永久数据从不删除分区的生命周期管理收益就不存在这时候更要把重点放在查询裁剪上。分区键如果只为了“存着好看”而选那还不如不做。3.4 一个订单表的分区键取舍案例假设业务有三种高频查询查某个用户最新订单、按时间段统计订单量、按订单状态查列表。多数情况下我会选order_time做RANGE分区因为“按时间统计”和“定期清理”是强需求。同时用(user_id, order_time)联合索引兜住“查某个用户”的场景。但注意光有联合索引还不够SQL里最好带上时间范围SELECT * FROM order_p WHERE user_id 123456 AND order_time 2025-01-01 AND order_time 2025-07-01;这样优化器把扫描范围限制在6个分区内同时联合索引还能继续把范围缩小。如果SQL只写user_id 123456没有时间条件那MySQL只能全分区扫描索引即使存在也要在几十个分区里分别查一遍。分区键的取舍本质上就是访问模式和数据生命周期的权衡。3.5 分区键选型的检查清单是否出现在60%以上核心SQL的WHERE条件里条件类型是等值还是范围范围用RANGE等值用HASH/LIST数据按这个键拆分后是否均匀这个键在业务上更新频率是否极低是否天然支持定期删除/归档在MySQL里分区键是否能放进所有主键和唯一键。六条里至少满足四条才值得动手做分区。4. 日常SQL怎么写才能吃满分区能力4.1 让分区键出现在条件里且不要被函数包裹分区裁剪依赖优化器从SQL条件里推导分区范围。最常见的错误是给分区键套函数比如WHERE DATE(order_time) 2025-01-01分区键被DATE()包住之后优化器无法把条件反向换算成分区范围只能全分区扫描。正确写法是WHERE order_time 2025-01-01 AND order_time 2025-01-02同样的问题还有隐式类型转换。分区键是字符串类型却用数字去比较分区键是datetime却用一个不带时间的字符串去比较都可能让优化器放弃裁剪。检查SQL时先看分区键有没有被“污染”。4.2 参数化查询和ORM生成的SQL要格外小心用Prepared Statement或ORM框架时SQL通常长这样SELECT * FROM order_p WHERE user_id ?如果业务没有主动加order_time条件这个查询在分区表上等于每个分区都扫一遍。我见过不少项目代码里模型写得很干净但分区表上线后性能反而下降最后发现是ORM通用查询方法把所有字段的过滤条件都去掉了。稳妥的做法是在应用层强制传入“默认时间范围”比如最近30天。对大多数业务来说查一个用户最近30天的订单结果集和体验并不会差但数据库扫描的分区数从几十个降到了1到2个。这是成本最低、收益最明显的修复。4.3 JOIN时让两边的分区键对齐如果你的事实表和维表都按user_id做HASH分区JOIN条件写成user_id user_idOracle会做partition-wise join只在对应分区内做连接。MySQL目前没有这个能力但两边都带分区键过滤时扫描的分区数量也会减少。反过来如果A表按时间分区B表按用户分区JOIN条件两边没有共同的分区键关联优化器就得交叉访问更多分区性能很容易恶化。所以在数据建模阶段尽量把查询里最核心的关联键和分区键设计成同一个字段后面写SQL会省很多事。4.4 用执行计划验证分区裁剪效果MySQL可以执行EXPLAIN SELECT ...查看partitions列它显示SQL实际命中的分区列表。如果显示ALL或者列出一大堆分区说明裁剪没生效。PostgreSQL的EXPLAIN会显示Append节点下扫描了哪些分区Oracle计划里会出现PARTITION RANGE SINGLE或PARTITION RANGE ALL。这个验证动作应该写进SQL评审规范而不是只在建表时测一次。业务SQL是持续迭代的今天能裁剪明天别人加个条件可能就不能了。5. 分区表的索引怎么配不是加了索引就万事大吉5.1 为什么分区表上的二级索引默认是“本地索引”InnoDB里每个分区是一棵独立的B树主键索引和二级索引都只在各自分区内部存在。这种“本地索引”对带分区键的查询没有影响但对不带分区键的查询就是灾难优化器必须逐个分区扫描索引每个分区都要做一次B树查找N个分区就是N次很多时候比普通表单棵大B树还慢。比如(user_id)索引在普通表上一次索引查找能找到目标在12个分区的表上如果没带order_time就必须把12个分区的本地索引各查一遍。所以分区表上的二级索引重点不是建多少个而是它到底在服务哪一类SQL。如果核心SQL不带分区键这个表的分区策略基本是错的。5.2 全局索引跨分区查询的速效救心丸Oracle支持全局索引索引覆盖全部分区不带分区键的等值查询也能快速定位。但MySQL不支持全局索引分区表上的所有索引都是本地索引。所以MySQL里遇到跨分区查询不要指望“建个全局索引”来救优先改SQL让它带上分区键或者重新评估是不是真的需要分区。PostgreSQL声明式分区会在父表上创建索引同时自动为每个分区创建同样的索引类似本地索引的效果。PG 11之后支持分区表上的唯一约束但每一条数据仍然是在分区内部做约束校验。设计时要注意如果你需要跨分区的全局唯一PostgreSQL也不是完全无感。5.3 唯一键、主键与分区键必须绑定的MySQL规则MySQL要求分区表的每个主键和唯一键都必须包含分区键列。因为MySQL需要保证唯一约束能在单个分区内完成判断如果唯一键不是分区键MySQL就无法确定两条重复数据是否在同一边所以只能强制绑定。这意味着自增id做主键的表想按order_time分区必须把order_time也加进主键PRIMARY KEY (id, order_time)这会让按id查询时无法用主键直接定位因为主键顺序变成了先order_time后id。很多人第一次建分区表就是在这里踩坑。如果项目里大量代码按id查单条记录这个设计一定要提前和业务确认。5.4 分区数太多时索引维护开销会反噬性能分区不是越多越好。按日分区跑上三年就是1000多个分区每个分区都有独立的B树打开文件数、元数据管理、统计信息采集都会变成压力。每次查询如果跨几十个分区光打开分区就有固定开销。实践经验是单表分区数控制在几十到几百个。按时间字段做RANGE分区时优先按月而不是按天除非单个分区的数据量实在大到必须再拆。维护脚本也要把“提前创建未来分区”和“定期DROP历史分区”做成自动化不能靠人肉。6. 分区维护和时间窗口生产环境最需要谨慎的地方6.1 未来分区没建凌晨0点插入报了“分区不存在”RANGE分区如果设置了最大边界数据一旦超过边界就会插入报错。最常见的场景是按月分区结果没有提前建下个月的分区某天凌晨0点一过新订单全部落库失败。我见过因为这个事故被叫起来处理的DBA不在少数。运维脚本必须预建未来N个分区比如每个月1号自动把下下个月的分区建好留出容错窗口。同时加监控把“分区数量少于预期”设置成告警别等到爆了才发现。6.2 DROP/TRUNCATE分区比DELETE高效几个数量级的清理这是分区表最有价值的操作之一ALTER TABLE order_p DROP PARTITION p202401; ALTER TABLE order_p TRUNCATE PARTITION p202401;DROP PARTITION是直接删除整个分区的数据和索引TRUNCATE PARTITION是清空分区数据但保留分区定义。两者的性能比DELETE好太多因为它们不做逐行删除也不产生大量undo。但要注意操作是不可逆的。DROP之前一定要确认数据已经备份、归档或确认可以永久删除。生产环境别贪图快先做一次SELECT确认边界数据量。6.3 拆分、合并与EXCHANGE动态调整分区形态RANGE分区要拆分时可以用REORGANIZE PARTITION把一个原来的分区拆成多个子分区LIST分区要增加新的枚举值时也需要REORGANIZE。如果数据量膨胀把一个月分区拆成两个更细的分区查询裁剪会更精准但DDL执行期间可能有锁影响。EXCHANGE PARTITION适合做冷数据归档把某个分区和一张结构相同但独立的普通表交换数据瞬间变成独立表之后可以继续保留也可以DROP。整个过程几乎不复制数据速度快但要求两张表结构完全一致。这类高级操作用在前先在小表上演练一遍别直接在核心表上试。6.4 DDL锁和低峰期分区操作不是零成本MySQL执行ADD/DROP/REORGANIZE PARTITION时需要获取表的元数据锁执行过程中可能阻塞表上的读写。8.0的部分分区操作支持ALGORITHMINPLACE但依然要评估锁影响。Oracle的分区DDL相对轻量但也不要傻到在业务高峰去重建全局索引。我的习惯是所有分区维护都放进低峰期脚本里加检查点执行完自动记录分区列表。如果分区维护脚本和应用写入正好撞车宁可让脚本失败重试也不要让报错扩散到业务侧。7. 分区表性能不升反降的排查复盘7.1 场景还原加了分区之后查询慢了30%朋友的项目把一张1亿行的订单表按order_time做了月度RANGE分区结果上线后部分接口反而慢了30%。一开始都怀疑是缓存问题后来我用EXPLAIN把核心SQL拉了一遍问题马上浮出水面。7.2 第一步EXPLAIN看是否全分区扫描执行计划显示partitions列为ALL也就是全分区扫描。业务里的查询模板基本都是SELECT * FROM order_p WHERE user_id ?没有order_time条件。user_id虽然建了索引但每个索引都是分区本地的优化器不知道去哪个月份找只能把1月份的到12月份的分区全部扫一遍。这就是分区键和实际访问模式错配的典型表现。7.3 第二步统计信息和数据倾斜执行ANALYZE TABLE之后发现12个月份的数据量并不均匀。大促那个月有5000万行平时一个月几百行。即使SQL带了时间条件只要落在这个热点分区扫描成本依然高。数据倾斜让RANGE分区的“平均分配”假设失效。7.4 第三步二级索引在分区表上的真实表现执行计划确实走了(user_id)索引但走了12个分区的本地索引。InnoDB里每个分区的索引都是独立B树需要做12次索引查找和多次回表。看起来走了索引总成本反而比普通表单棵大B树更高。这是MySQL分区表最迷惑人的地方。最后解决方案是给所有后台查询强制加上最近3个月的order_time范围同时把原先的普通索引改成(user_id, order_time)联合索引。上线后分区裁剪生效查询扫描的分区从12个变成最多3个响应时间比分区前快了一倍多。7.5 复盘结论不要先分区再改SQL先理清查询再分区这次排查绕了一大圈根子还是分区键和SQL访问模式错配。如果一开始把慢查询日志里真正高频的执行模式列出来让分区键覆盖80%以上SQL的过滤条件就不会白白折腾一轮。表分区能放大优化器裁剪数据的能力但无法替代SQL设计。如果一个系统里的查询从来不带分区键分区只是给表增加了“更多数据段”性能不降反升才是奇怪。最后分享一个我自己的习惯决定分区前先把生产慢查询日志拉出来统计WHERE条件里每个列出现的次数。如果一个列出现在60%以上的查询里再谈分区否则先优化索引和SQL。分区上线之后也要在测试环境用核心SQL跑一遍EXPLAIN确认每个SQL的扫描分区数都在预期内。分区不是银弹但用对了之后那种把几亿行表切到几十个分区再查询的体感确实比加几十个索引更有用。希望你的表也能尽早睡个好觉。