MySQL 分区表这功能早年在社区里的评价一直两极分化。一边是一张大表拆成小表查询快到飞起的爽文故事另一边是分区表性能还不如普通表、坑多得防不胜防的劝退帖。两种说法我都见过自己也踩过不少。做了这么多年数据库优化我的看法是分区表本身没问题问题出在大多数人对它的理解停留在拆表这个表象上既不清楚分区表到底优化了什么也不知道它在什么场景下才是正确答案。这篇文章我会把分区表的原理、选型、建表实操、日常维护和常见坑一次讲透适合正在评估要不要上分区表、或者已经在用但对性能心存疑虑的同学参考。1. 分区表的核心价值与适用边界1.1 一张大表的三个痛点在聊分区表之前先回顾一下我们为什么会被大表逼到考虑分区。一张表的数据量一旦到了千万甚至亿级压力通常来自三个方向。第一个是查询性能。绝大多数业务查询并不会因为表大了就变得复杂反而是因为表大了一条本该走索引的SQL多扫了很多无关数据。尤其是那种按时间范围查最近一个月数据的语句如果表里有三年历史数据每次查询都在几千万行里找几百行即使索引命中B树的高度和索引页的缓存命中率也会明显劣化。第二个是写入性能。大表的B树索引也是巨大的每插入一条记录都要维护主键索引和所有二级索引。数据量越大索引页分裂和刷盘压力越大单条写入的延迟和抖动就越明显。第三个是维护成本这个是很多团队真正动手做分区的导火索。比如你要删除三个月前的历史数据一条DELETE FROM big_table WHERE create_time ...在亿级表上可能跑十几分钟还可能因为锁竞争把线上业务拖垮。又比如你要升级表结构、做一次大表重整整个过程可能把你的运维窗口耗尽。分区表本质上就是把一张逻辑表拆成多个物理存储分区每个分区就像独立的小表。它能直击上述痛点但它绝不是万能的很多人在这一步就走偏了。1.2 分区裁剪是怎么省时间的理解分区表性能的关键是弄明白一个词分区裁剪Partition Pruning。这是一个执行计划层面的优化动作。当你的SQL查询条件里带有分区键时优化器会在真正的数据扫描之前先把不相关的分区直接排除掉。举个例子。我维护过一张订单流水表按月份做了RANGE分区。查询最近三天的订单时SQL里的create_time条件落在三个分区范围内优化器只需要扫描那三个分区其他三十几个分区连碰都不会碰。每个分区如果过滤后还有几千条数据最终扫描量就是几千乘以三而不是整张表上亿行。这里有个关键点容易被人忽略分区裁剪省的是物理IO和扫描范围而不是索引失效。分区表在每个分区内部都有自己的索引结构查询先裁剪分区再在每个保留的分区内走索引两者是叠加关系。如果你建了分区表但是查询条件不带分区键那优化器只能把全部分区都扫一遍性能会比普通表更差——因为你的索引被拆分到多个小树里扫描效率反而更零散。1.3 什么时候不适合分区表我见过不少团队把分区表当成性能银弹结果上线后效果平平甚至更差。这里把不适合的场景说清楚帮你少走弯路。首先如果你表里的数据量还没到千万级别分区表带来的收益通常撑不起它的复杂度。分区表和普通表相比查询扫描的差距在数据量小的时候微乎其微但你在DDL管理上的成本却实实在在增加了。其次如果你的核心查询条件里完全没有分区键那分区的意义就很小。比如你按create_time分了区但业务查询主要按user_id和order_id走优化器无法裁剪只能全分区扫描。这种情况下分区不但没有帮你缩小扫描范围反而让每个分区的索引碎片化得不偿失。再次如果你的数据不需要淘汰归档也就是没有删历史数据这类需求分区表最大的运维红利就吃不到了。分区表最适合的场景永远是那种数据持续涨、历史数据定期清理、查询总是带同一个筛选维度的数据管道类业务。最后提一个很多人忽略的问题分区表记录数的统计。COUNT(*)在分区表上的优化是分区级聚合遇到大分区数量多的情况统计性能也挺难受而且原子性、外键等约束在分区表上都有额外限制。这些我会在后面常见问题部分详细展开。2. 四种分区类型怎么选MySQL的分区类型有RANGE、LIST、HASH、KEY四种很多同学在建表时就纠结了。其实它们的适用场景非常清晰选错才是灾难的开始。2.1 RANGE时间序列表单的首选RANGE分区按连续区间划分数据是使用率最高的一种尤其适合时间字段。你可以按天、按月、按年来定义分区的上界MySQL会根据分区键的值自动落到对应分区。它的优势在于和业务的时间局部性天然匹配。日志表、流水表、订单表几乎都逃不过查最近一段时间的诉求而且数据淘汰也是按时间维度。RANGE分区的另一个好处是它支持后续平滑地增加分区比如每个月月底预创建下个月的分区运维节奏很稳定。创建RANGE分区表的典型SQLCREATE TABLE order_log ( id bigint NOT NULL, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, amount decimal(12,2) DEFAULT NULL, create_time datetime NOT NULL, PRIMARY KEY (id, create_time) ) ENGINEInnoDB PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026) );一个必须提前想清楚的问题是RANGE分区只定义了上界数据超过最大上界时插入会直接报错。业务里很多表突然写不进去的事故都是因为忘记预创建分区。你可以用MAXVALUE兜底但那样后续增加分区就得拆分麻烦不少我更建议你用定时任务提前建分区。2.2 LIST枚举值的精准归位LIST分区按照枚举值列表把数据映射到指定分区适合那种分区键是有限离散值的场景。比如订单状态待支付、已支付、已发货、已关闭、业务类型、区域ID等。它的用法是把每个分区映射到一组具体的值不同分区的值不能重复CREATE TABLE order_info ( id bigint NOT NULL, order_no varchar(64) NOT NULL, status tinyint NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id, status) ) ENGINEInnoDB PARTITION BY LIST (status) ( PARTITION p_pending VALUES IN (1), PARTITION p_processing VALUES IN (2, 3), PARTITION p_finished VALUES IN (4), PARTITION p_other VALUES IN (5, 6, 7) );LIST分区的典型收益是按状态分组统计这类查询。比如运营要拉一个待支付订单清单优化器能直接只扫p_pending一个分区。不过说实话LIST分区的使用频率远低于RANGE因为大部分业务的分区键更倾向于时间和ID而枚举值的分布往往不均匀容易产生热分区。2.3 HASH与KEY数据打散的背后差异HASH分区和KEY分区都是为了让数据更均匀地分布到各个分区适合那种没有明确的时间偏好、分区键只是用来分散压力的场景。比如按用户ID分区把写入压力平均分摊到多个分区。HASH分区需要分区键支持整数运算你可以直接使用整数列或者用函数表达式转换。如果某个列的取值随机且均匀HASH分区效果就不错。KEY分区和HASH分区的区别在于KEY分区使用MySQL内部的哈希函数对分区键的类型限制更宽字符串类型也能直接用而且是默认分区类型。两者的创建示例如下-- HASH分区按业务ID散列到4个分区 CREATE TABLE user_action_log ( id bigint NOT NULL, biz_id bigint NOT NULL, action varchar(32) DEFAULT NULL, create_time datetime NOT NULL, PRIMARY KEY (id, biz_id) ) ENGINEInnoDB PARTITION BY HASH (biz_id) PARTITIONS 4; -- KEY分区按字符串列散列到4个分区 CREATE TABLE access_record ( id bigint NOT NULL, token varchar(64) NOT NULL, path varchar(128) DEFAULT NULL, create_time datetime NOT NULL, PRIMARY KEY (id, token) ) ENGINEInnoDB PARTITION BY KEY (token) PARTITIONS 4;HASH和KEY分区有个共同的痛点如果业务查询的筛选条件不是分区键分区裁剪完全发挥不出来。它们只解决写入分散问题不解决查询缩小范围问题。这一点务必牢记。2.4 类型选型对照表把四种分区类型放在一起对比选型逻辑会更清晰分区类型适用字段特征典型场景核心优势主要局限RANGE连续取值时间、ID范围日志/流水/订单按时间归档范围查询裁剪效果好分区可平滑增加分区不均匀时容易产生热点LIST离散枚举值状态、类型、地区分布精确匹配枚举值裁剪精准新增枚举需要重建分区值分布不均易热点HASH可整数的散列字段用户ID、订单ID打散写入分散均匀范围查询无法裁剪KEY任意类型内部哈希字符串标识、UUID场景类型限制少写入分散均匀同样依赖等值查询裁剪从我的经验看90%的生产分区表都落在RANGE分区上因为时间范围查询历史数据归档的组合实在太常见。LIST在某些状态统计类业务里也有不错的效果但需要评估好枚举值的分布。HASH和KEY一般来说只在单纯想分散写入压力时考虑。3. 实操建表与验证3.1 设计分区键时的硬约束所谓硬约束就是MySQL的语法规则不满足直接报错那种。最典型的就是主键约束如果表有主键或者唯一索引那么分区键必须包含在主键和每一个唯一索引里。原因很好理解分区表本质上还是要求每一条记录都能唯一定位到某个分区。如果分区键不在主键里那么主键索引只能定位到记录在哪个分区里无法定位到具体记录这破坏了索引唯一性。实际报错长这样ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitioning function这意味着你设计主键时就得预留分区键的位置。最常见的做法是把create_time这种分区键放进联合主键或者干脆用自增ID和create_time组成联合主键。如果业务上确实不想把时间字段放进主键那就要重新评估分区键换成业务ID或干脆放弃分区表。还有一个隐性约束是分区键必须是表中真实的列或者是在该列上的表达式。你没法用不存在于表中的值做分区。同时表达式的返回值必须是整数像YEAR(create_time)返回年份整数没问题TO_DAYS(create_time)也可以但如果用DATE_FORMAT(create_time, %Y-%m)返回字符串就会报错。3.2 建表SQL与参数说明我们以一个真实的模拟项目“某跨平台系统”的订单流水表为例完整演示RANGE分区表的建表过程。业务需求是这样的单日订单量在百万级需要保留最近36个月的数据查询集中在最近一周、最近一个月、指定某天这类时间范围内同时大数据团队经常按天抽取前一天的增量数据。基于这些约束我设计按月RANGE分区分区键用create_time主键用id create_timeCREATE TABLE order_flow ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, channel varchar(16) DEFAULT NULL, amount decimal(12,2) DEFAULT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id, create_time), KEY idx_user_create (user_id, create_time), KEY idx_order_no (order_no) ) ENGINEInnoDB PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)) );注意几个细节。第一我用了TO_DAYS(create_time)而不是YEAR(create_time)。TO_DAYS能把日期转换成与天粒度一致的整数支持更细粒度的时间范围裁剪。比如查询2024年2月20日下午3点到晚上9点的数据按天粒度的分区可以精确裁剪到2月20日所在的分区然后在这个分区内走索引继续过滤。如果按YEAR分区时间跨度跨年的查询就会把多个年份分区都扫一遍裁剪精度差很多。第二二级索引idx_user_create和idx_order_no都是普通索引但它们在每个分区内部是独立B树。你可能会问order_no的索引既然只在本分区内有效全局查询会怎样这个问题下面会单独讲。第三为了让查询尽量稳定走分区裁剪我在user_id和create_time上建了联合索引。业务场景里最常见的查询是某个用户某段时间的订单WHERE user_id ? AND create_time BETWEEN ? AND ?这个条件既触发了分区裁剪又能走联合索引是效果最好的组合。3.3 EXPLAIN验证分区裁剪建完分区表第一件事是验证SQL到底有没有触发分区裁剪。不同版本和客户端下方式略有差异我这里说最通用的方法。在MySQL 5.7里可以直接用EXPLAIN PARTITIONS显示分区命中信息在MySQL 8.0里这个语法被移除了取而代之的是EXPLAIN FORMATTREE或者EXPLAIN ANALYZE。在8.0上一条带分区键的查询执行计划大概是EXPLAIN FORMATTREE SELECT * FROM order_flow WHERE create_time 2024-02-01 00:00:00 AND create_time 2024-03-01 00:00:00;输出里会明确出现partition (p202402)标识这就表示优化器只扫描了2月这一个分区。如果条件跨月输出会带多个分区比如partition (p202401,p202402)。看到这样的结果说明你的分区裁剪生效了。最怕看到的是这样两种现象。第一种是执行计划里出现partitions为空或扫描全部分区第二种是明明写了分区键条件但计划显示的扫描范围完全没有收窄。这两种情况通常指向同一个根因你的查询写法让优化器无法对分区键做裁剪。常见原因有几种分区键被包在函数里比如WHERE YEAR(create_time) 2024而不是WHERE create_time 2024-01-01、SQL里的时间字段类型不匹配、或者NULL值处理逻辑破坏了裁剪判断。具体排查方法放在第5章详述。4. 日常运维全记录4.1 增删分区的标准姿势分区表上线只是开始真正考验人的是长期运维。RANGE分区最常见的运维操作就是提前建分区、到点删分区。预创建下个月分区ALTER TABLE order_flow ADD PARTITION ( PARTITION p202404 VALUES LESS THAN (TO_DAYS(2024-05-01)) );删除三个月前的旧分区ALTER TABLE order_flow DROP PARTITION p202401;这里有个非常重要的运维认知DROP PARTITION删的是整个分区的数据文件和索引文件速度极快几乎不产生大量的逐行删除日志和回滚段开销。对比一下业务上的DELETE FROM order_flow WHERE create_time 2024-01-01在几千万行数据上可能要跑几十分钟DROP PARTITION则能在秒级完成。这是分区表在数据归档场景里最值钱的能力。如果你不想删分区只想清空数据并保留分区定义用TRUNCATE PARTITIONALTER TABLE order_flow TRUNCATE PARTITION p202403;如果你是HASH或KEY分区增加分区数不能用ADD PARTITION要用COALESCE PARTITION减少数量或者对HASH分区修改分区总数使数量翻倍。说实话HASH/KEY分区的数量调整会触发整个表的数据重新分布代价很大所以这类分区在设计之初就要把分区数量定好后续尽量别动。4.2 查看分区数据与使用率运维过程中你需要随时知道每个分区里到底有多少数据、有没有分区异常膨胀。直接查information_schema.PARTITIONS是标准姿势SELECT PARTITION_NAME AS pname, PARTITION_METHOD AS pmethod, PARTITION_EXPRESSION AS pexpr, TABLE_ROWS AS rowcount, DATA_LENGTH / 1024 / 1024 AS data_mb FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA your_db AND TABLE_NAME order_flow ORDER BY PARTITION_ORDINAL_POSITION;这个查询有两个实用点。第一TABLE_ROWS数据能帮你判断数据分布是否均匀。如果某个月的分区数据量比其他月份大一个数量级说明业务存在明显的热点月你要评估这个月份的查询性能是否需要单独优化。第二留意分区数量和实际数据容量的关系。分区分得太细比如按天分区一年就要365个分区管理成本高且每个分区的数据量过小分区太粗比如按年分区删除旧数据的粒度又太粗无法实现只保留最近36个月这类精细要求。我的习惯是日数据量在几十万到几百万之间的流水表按月分区最均衡。4.3 分区交换归档大法再分享一个我经常用到的归档方案分区交换Partition Exchange。它可以把某个分区的数据几乎瞬时地迁移到一张独立表里非常适合做冷热数据分离。假设要把p202401分区的全部数据迁移到归档表order_flow_archive_202401且归档表的表结构和原表完全一致ALTER TABLE order_flow EXCHANGE PARTITION p202401 WITH TABLE order_flow_archive_202401;执行完之后原表的p202401分区变成空分区也可以选择WITHOUT VALIDATION跳过行校验但我不建议省略校验归档表里则带着原分区的全部数据。接下来你可以把这个分区DROPartition掉归档表单独存储甚至可以备份后从主库移除。这个方案比普通的INSERT INTO archive SELECT ... DELETE FROM ...高效得多而且整个过程对线上业务的影响非常小。唯一需要注意的就是归档表必须是独立表不能存在外键关联并且表的引擎和字符集要和原表一致。MySQL 8.0对交换时校验的限制比较多实操前最好先在测试环境过一遍。5. 高频问题与踩坑实录5.1 NULL值到底进了哪个分区分区键是NULL时的行为不同分区类型的差别很大这也是最容易踩的坑之一。很多同学以为分区表会自动把NULL归类结果发现数据进了完全意想不到的分区。RANGE分区对NULL的处理是永远将NULL放入最小的分区。也就是说一个按TO_DAYS(create_time)分的RANGE表如果create_time为NULL数据会被塞进最早的那个分区。这很危险因为最早的分区往往是即将被删除的历史分区一旦你执行DROP PARTITION归档旧数据这些NULL数据会被一并清掉。LIST分区则相反它不会自动处理NULL。如果你在VALUES IN列表里没有显式包含NULL那么插入NULL分区的记录会直接报错ERROR 1526 (HY000): Table has no partition for value NULL所以面部场景如果允许NULL要么在表结构里用NOT NULL约束强制业务必填要么在建表时显式加一个PARTITION p_null VALUES IN (NULL)分区兜底。HASH和KEY分区对NULL的支持相对宽松会把NULL值当作某个固定值分配到分区里。但方便归方便这种隐式规则同样容易让数据分布失控最好的做法还是从源头禁止NULL。5.2 明明有WHERE却全分区扫描这是我被问得最多的一个现象查询里明明带了分区键条件执行计划还是扫描了全部分区。除了第3章提到的函数包裹问题还有几个常见原因值得自查。第一个原因是隐式类型转换。比如分区键是整型但查询条件里写的是字符串MySQL可能会把整型列转成字符串再比较导致无法按原值做范围裁剪。第二个原因是查询条件里对分区键用了表达式比如WHERE create_time INTERVAL 1 DAY 2024-02-01。哪怕表达式的结果和直接比较等价优化器也没法反转这个表达式来做分区裁剪。第三个原因比较隐蔽如果查询条件里分区键的筛选度太低比如WHERE user_type IN (a,b,c,...)覆盖了大部分分区值优化器算下来觉得全扫和裁剪到大多数分区的代价差不多甚至全扫更优它就会选择全分区扫描。排查这类问题我建议按这个顺序来先看执行计划是否列出了所有分区再看SQL里分区键有没有被函数或表达式包裹接着确认字段类型和条件值类型是否匹配最后用ANALYZE TABLE刷新统计信息避免优化器基于过期统计信息做错误判断。5.3 分区数量失控的隐患分区数量越多越好吗绝对不是。MySQL在早期版本文档里对InnoDB表有8192个分区的上限理论上好像很宽松但实际生产环境里超过几百个分区就是一个巨大的运维负担。分区数量失控带来的问题有几个层面。第一每个分区都是独立的B树结构分区太多意味着你的InnoDB缓冲池被大量分区索引页占据缓存命中率下降。第二DDL操作比如ALTER TABLE增加列、重建表需要遍历所有分区分区数量多DDL时间成倍增长。第三备份和恢复的复杂度上升每个分区都要单独处理。我的经验值是单表单分区数量最好控制在100以内超过这个数就要考虑按月还是按其他更粗的粒度。另外还要留意自动建分区的脚本别把未来十年的分区一次性全部创建出来。有些团队习惯预创建一年的分区结果分区表数据没多少空分区倒是占了一堆反而拖垮了统计信息和缓冲池。5.4 分区表与分库分表的取舍最后一个高频问题什么时候分区表就够了什么时候必须上分库分表这是一个架构层面的选择题。分区表解决的是单表内数据量过大的问题它把一张物理大表拆成多个物理小分区但所有分区仍然在同一个个数据库实例上。分库分表比如中间件方案解决的是单库或单实例压力过大的问题它把数据横向拆分到多个数据库实例读写压力也随之分散。从这个角度看分区表适合数据库实例本身压力不大、瓶颈在于单表扫描量过大和归档困难的场景。如果你的业务已经出现单实例CPU和IO长期打满或者单库连接数吃紧那不是分区表能解决的你需要的是分库分表。我的建议是先评估数据量和压力来源再决定技术方案。数据量几千万到几亿、实例硬件资源还行、查询大多带时间维度优先考虑分区表。数据量到了几十亿级别或者并发写入高到单实例扛不住别犹豫直接往分库分表方向设计。两者不是互斥的某些超大规模场景里分库分表后每个分片再引入时间分区也是常见组合。最后分享一点实操体会分区表这块我踩过的最大一次教训是上线前没有把分区键和业务查询的真实路径对齐。当时项目组为了看起来优雅按某个业务类型字段做了LIST分区结果线上查询几乎都是按用户和时间维度分区裁剪完全没生效性能甚至低于改造前。后来改成按时间RANGE分区才真正看到查询扫描量下降一个数量级。所以不管文档怎么写最终一定要用你线上最核心的SQL去建表、去验证执行计划而不是用理想化的查询做验收。其次就是运维脚本要做好预创建分区的监控哪天新增分区失败数据写入会瞬间失败这种事故一旦发生比SQL慢几个数量级都难处理。分区表本身不复杂复杂的是对数据特征和访问模式的判断这个想透了方案基本就对了。