MySQL高阶实战分区表Partitioning Table从原理到避坑做MySQL开发或者DBA的谁没有被大表折磨过。单表几千万行查询能跑但慢索引越来越大维护要半夜搞删除旧数据直接DELETE能把binlog撑爆。我早年接手过一个运营系统日志表三个月就上亿了日常查询和归档都痛苦到怀疑人生。后来把核心表改成分区表Partitioning Table问题一下子清晰了很多。这篇就把我实践中对MySQL分区表的理解、操作步骤和踩过的坑从头到尾梳理一遍适合数据量开始涨、正在纠结要不要上分区的朋友参考。分区表不是“分库分表”它是在同一个逻辑表内部按一定规则把数据物理拆分成多个分区存储。对业务层来说你仍然查这一张表但MySQL内部可以只扫描命中的分区这种能力叫“分区裁剪”Partition Pruning也是分区表最核心的价值。下面内容我会把为什么用、怎么用、什么时候千万别用以及我踩过的各种坑全部拆开讲。1. 分区表的适用场景与核心设计思路1.1 分区表到底解决什么问题先想清楚分区表不是银弹。它对下面几类问题帮助最大第一数据归档和删除的代价。这是最直接的应用。普通表删除几千万行要么一条一条删产生海量binlog要么DROP表重建但锁表时间太长。有了分区可以直接ALTER TABLE ... DROP PARTITION这相当于删除一个独立文件或独立表空间秒级完成且不产生大量binlog。很多业务会把一年前的数据直接丢弃用RANGE分区按月切分一次删一个月的数据就是一条DDL的事。第二查询性能提升。注意这是有条件的。只有WHERE条件里包含分区键时优化器才能做分区裁剪只扫描相关的几个分区减少IO和缓冲池的压力。比如订单表按order_date分区查某一天的单子只需要扫那个分区。但如果查询条件不带分区键MySQL会扫描所有分区性能反而可能比普通表更差。第三减轻索引维护压力。大表的二级索引会随着数据增长变得非常大每次插入都要随机更新多棵B树。分区后每个分区有独立的索引树整体索引的高度和写入的随机性都会下降。这在大量并发插入的场景下体感很明显。第四数据管理的灵活性。你可以只对热数据所在的几个分区做优化或备份冷数据分区甚至可以放到省钱的低速存储上。比如一年内的订单放SSD更早的分区挪到普通机械盘这在MySQL里可以通过不同分区的表空间配置实现。1.2 什么时候不建议用分区这一点我必须放在前面说因为它比“怎么建分区”更重要。分区表有几个硬伤分区键必须包含在主键或唯一键中。如果你的主键是id又想按create_time分区对不起MySQL直接报错。这是很多人第一次建分区表最大的拦路虎。分区表跨分区查询、跨分区JOIN、带UNIQUE KEY的操作性能可能不尽如人意。如果你的业务有大量关联查询而且关联字段不是分区键分区表帮不上忙反而可能拖慢。分区数量有限制MySQL 5.7及以前单表最多8192个分区8.0沿用了这个限制。别觉得很多按天分区两年就是700多个了按小时分区一个月就爆炸。业务设计时要估算清楚。分区无法完全替代索引。分区是一种粗粒度的数据组织方式它减少的是“扫描范围”不等于你不用建索引。真正定位到具体行还是要靠索引。所以分区表的正确打开方式是数据量确实大到单表索引和IO已经吃力并且查询模式里有稳定、高频、区分度好的分区键。2. 核心机制与分区类型选型2.1 分区键与分区裁剪原理分区裁剪是理解分区表的钥匙。MySQL优化器在解析SQL时如果发现WHERE条件里有分区键的取值范围就会提前排除掉不匹配的分区只扫描满足条件的分区。类似地INSERT的时候MySQL根据插入值的分区键直接定位到对应分区不需要全表扫描找位置。这意味着一个关键点分区键的选型直接决定分区表有没有效果。你不用分区键查询优化器就只能全分区扫描而且每个分区都有一份索引等于把所有分区都摸一遍性能比普通表更糟。我见过有同事把分区键建了但所有业务SQL都没有带这个字段结果上线后慢查询翻了几倍最后只能回滚。所以设计阶段就要想清楚哪个字段是所有高频查询都会带上的条件如果是时间字段是created_at还是order_date如果是区域字段是region_id还是city_id这个字段的值分布是否均匀想清楚了再选分区类型。2.2 四种分区类型怎么选MySQL支持四种分区类型我用一个表格对比它们的语法和适用场景分区类型关键语法适用场景注意事项RANGEPARTITION p0 VALUES LESS THAN (...)按时间、按数值区间最常用必须定义上限新增分区要手动或定时LISTPARTITION p0 VALUES IN (...)按枚举值如省份、类型无法覆盖的值会报错需提前规划全量值HASHPARTITION BY HASH(expr) PARTITIONS N数据分布均匀、无自然分界数量固定无法直接删除单分区数据KEYPARTITION BY KEY(col) PARTITIONS N类似HASH但使用MySQL内部哈希函数只允许使用列不能是表达式实际业务里RANGE分区占了90%以上的场景。为什么因为大多数需要分区的表都有时间维度的访问特征最近的数据访问最频繁老数据逐渐变冷最后被归档和删除。RANGE分区天然匹配这种“冷热分层”的需求。LIST分区适合分区键是固定枚举值的情况比如按省份、按业务类型。但要注意LIST分区必须列出所有可能的值如果插入一个不在列表里的值MySQL会直接报错ERROR 1526。所以如果枚举值可能扩展LIST分区会很难维护要提前评估。HASH和KEY分区适合那种“没有明显区间但希望数据均匀分散”的场景。它们的共同缺点是分区数量建好后一般不做增减删除单分区数据的意义不大旧数据归档也很别扭。而且HASH分区的表达式最好返回整数用日期函数需要确保函数能生成均匀分布的值。我个人的建议是——除非非常明确要均匀分布否则优先考虑RANGE。2.3 分区与索引如何配合分区表和索引是两套独立的工具它们的工作方式很多人没理解透。首先MySQL分区表不支持全局唯一索引也就是说如果分区键不是主键或唯一键的一部分你就不能在该表上建唯一索引。这是很多业务从普通表改成表时发现索引报错的原因。因为每个分区内的索引是局部的唯一性只能在一个分区内保证跨分区无法校验。其次分区表上的索引本质上是每个分区各有一棵B树。所以如果你给表建了二级索引那么有多少个分区就有多少棵索引树。查询时如果分区裁剪生效只需要查命中的那几棵索引树如果没命中就要把每棵索引树都查一遍这比普通表还慢。所以关于索引的实操建议是分区键字段一定要建索引。虽然分区列表本身就有一种“索引”的效果但二级索引的建立还是必要的特别是高基数的非分区字段。联合索引尽量把分区键放在最前面。这样索引的B树可以直接利用分区裁剪的优势减少扫描量。不要滥用索引。分区表多一层结构每个分区的索引都要维护索引越多写入成本越高。只给高频查询的字段建索引。在8.0版本中MySQL对分区表的索引支持也做了优化支持在分区表上使用INVISIBLE INDEX等特性但核心设计思路没有变分区减少扫描范围索引负责精确定位两个配合才能发挥最大价值。3. 实操过程从建表到分区管理3.1 创建分区表的完整步骤以最常见的按月RANGE分区为例。先看一个完整的建表语句CREATE TABLE order_log ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL DEFAULT 0.00, created_at datetime NOT NULL, PRIMARY KEY (id, created_at) ) ENGINEInnoDB PARTITION BY RANGE (TO_DAYS(created_at)) ( 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)), PARTITION p202404 VALUES LESS THAN (TO_DAYS(2024-05-01)), PARTITION p202405 VALUES LESS THAN (TO_DAYS(2024-06-01)), PARTITION p202406 VALUES LESS THAN (TO_DAYS(2024-07-01)), PARTITION p_max VALUES LESS THAN MAXVALUE );这里有几个关键点要注意第一主键必须包含分区键。我上面把created_at放进了联合主键(id, created_at)这是MySQL的硬性规则。如果主键只有id建表直接报错A PRIMARY KEY must include all columns in the tables partitioning function。你可以要么把分区键加进主键要么把主键去掉改成普通索引但一般不建议去掉主键有些场景使用无主键表配合分区也能跑但复制和高可用架构下会有很多麻烦不太推荐。第二用TO_DAYS()还是YEAR()。按天/按月分区时很多教程会用TO_DAYS()或TO_SECONDS()将日期转换为整数。好处是RANGE分区按整数值比较效率更高。直接用created_at字段本身做RANGEMySQL也允许从5.7开始支持函数表达式但要注意表达式的确定性和性能。我个人习惯用TO_DAYS()因为这个函数返回的整数B树和分区比较都不存在隐式转换问题。第三最后一个分区必须用MAXVALUE兜底。如果你定义的分区上限只到6月业务一插入7月的数据就直接报错Table has no partition for value from column_list。我吃过这个亏——上线前忘了加后补分区某天突然告警所有插入全挂。所以我把p_max VALUES LESS THAN MAXVALUE作为最后一个保险分区。查某些日期很Old的归档数据不会报错但要注意p_max会持续膨胀需要定期拆分或归档。如果你是在已有的大表上加分区MySQL 8.0支持ALTER TABLE ... PARTITION BY ...直接转换但这会重建整张表锁表时间很长生产环境务必用pt-online-schema-change这类工具或者计划维护窗口操作。3.2 分区的日常管理拆分、添加、删除、合并建好分区表只是开始真正的功力在于后续的运维管理。RANGE分区的核心操作有几种我一个个说。添加新分区ALTER TABLE order_log ADD PARTITION ( PARTITION p202407 VALUES LESS THAN (TO_DAYS(2024-08-01)) );但注意如果你的表已经有MAXVALUE分区这个时候直接ADD PARTITION会报错VALUES LESS THAN MAXVALUE must be the last partition。你必须先把MAXVALUE分区拆分或删除才能加新分区。正确的做法是使用REORGANIZE PARTITION把p_max拆成新分区和新的p_maxALTER TABLE order_log REORGANIZE PARTITION p_max INTO ( PARTITION p202407 VALUES LESS THAN (TO_DAYS(2024-08-01)), PARTITION p202408 VALUES LESS THAN (TO_DAYS(2024-09-01)), PARTITION p_max VALUES LESS THAN MAXVALUE );删除旧分区ALTER TABLE order_log DROP PARTITION p202401;这一句执行完2024年1月的数据就物理删除了而且性能极快不会产生逐行删除的binlog。这也是分区表在数据归档上最酣畅淋漓的优势。记得删除前先确认数据不需要保留最好SELECT COUNT(*) FROM order_log PARTITION(p202401)看一眼数据量。合并分区ALTER TABLE order_log REORGANIZE PARTITION p202401, p202402 INTO ( PARTITION p202401_02 VALUES LESS THAN (TO_DAYS(2024-03-01)) );合并会重建分区如果有大量数据会比较耗资源尽量在业务低峰期做。去掉分区如果某天业务不需要分区了想恢复成普通表MySQL 5.7及以后可以使用ALTER TABLE order_log REMOVE PARTITIONING;这一步相当于重建表大表要谨慎测试环境提前演练锁表时间。查看分区信息SELECT PARTITION_NAME, PARTITION_METHOD, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA DATABASE() AND TABLE_NAME order_log;这条SQL是运维日常必须掌握的可以快速确认各分区的数据行数和状态排查有没有数据落到MAXVALUE之外的异常分区。3.3 常见坑主键约束、NULL值处理、查询失效分区表的坑很多是“CODE里跑着跑着突然炸了”的类型。我把最常见的几类罗列一下。坑一NULL值去哪了不同分区类型对NULL值的默认处理不一样非常容易踩。RANGE分区NULL会被放到最小的分区。是的你没看错VALUES LESS THAN (xxx)不包含NULL的大小判断MySQL把NULL当作比任何值都小直接塞进第一个分区。LIST分区如果分区的VALUES IN里没有明确包含NULL插入NULL会直接报错。坑不坑必须把NULL显式写进某分区的值列表里。HASH/KEY分区NULL会被当作0处理所以会落到PARTITION p0。这就导致一个结果如果你的RANGE分区表第一个分区是按月切的p202401所有created_at IS NULL的数据都会积压到p202401里。查询WHERE created_at IS NULL时优化器可能不按预期裁剪整个分区都要扫。我的经验是业务表字段尽量都设为NOT NULL或者在建表时给默认值避免NULL值分区的“隐藏聚集”问题。坑二自定义函数做分区表达式要谨慎。MySQL官方限制分区表达式必须是确定的。像NOW()就不行因为每次调用返回不同值。如果用YEAR(created_at)或TO_DAYS(created_at)没问题因为给定字段值结果固定。但如果你用了DATE_FORMAT(created_at, %Y%m)这种返回字符串的分区表达式性能会比较差分区裁剪也可能失效尽量不要用。坑三WHERE条件带函数分区裁剪失效。比如分区键是created_at你查WHERE DATE(created_at) 2024-01-15MySQL没法直接推断出分区范围可能全分区扫描。正确写法是WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00这样优化器能明确算到p202401的范围。这是一个非常容易忽略、但影响巨大的细节。坑四分区键类型不一致。比如分区键是VARCHAR但你查询时传入数字MySQL的隐式转换可能导致无法匹配分区键裁剪失效全表扫描走一遍。这也是慢查询排查时经常遇到的问题。最好保持分区键字段类型和应用程序传入参数类型一致尽量别依赖MySQL的隐式转换。4. 真实场景一张千万级日志表的改造4.1 需求与分析我参与过的一个真实项目有一张用户操作日志表user_operation_log每天新增100万行一个月就3000万行保留6个月。当时的痛点是单表数据量很快过亿查询慢索引维护成本高。每天凌晨有清理任务把3个月前的数据DELETE掉每天跑40多分钟还容易拖慢主库。后台管理需要按用户ID和时间范围查询操作日志大部分场景都限制在当天或近7天。分析下来这就是非常典型的分区表适用场景数据有明确的时间属性日常查询集中在近期旧数据需要定期删除。分区键选择operation_time按天分区保留6个月就是180个分区远低于8192上限。当时有一个争议要不要把user_id也加入分区规则比如先按user_id做HASH分区再按时间做子分区。我的结论是不搞子分区。原因有三一是子分区会让物理文件数量翻倍运维变复杂二是我们的查询基本都带时间范围条件RANGE分区的裁剪已经足够三是子分区的管理操作特别是ALTER TABLE更复杂容易出错。能用简单方案就不用复杂的。4.2 建表方案与效果统计具体建表语句大致如下CREATE TABLE user_operation_log ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, operation_type varchar(32) NOT NULL, operation_desc varchar(255) DEFAULT NULL, operation_time datetime NOT NULL, PRIMARY KEY (id, operation_time), KEY idx_user_time (user_id, operation_time) ) ENGINEInnoDB PARTITION BY RANGE (TO_DAYS(operation_time)) ( PARTITION p20240101 VALUES LESS THAN (TO_DAYS(2024-01-02)), PARTITION p20240102 VALUES LESS THAN (TO_DAYS(2024-01-03)), PARTITION p20240103 VALUES LESS THAN (TO_DAYS(2024-01-04)) -- 后续分区由定时任务自动添加 );上线后的变化非常直观删除旧数据的任务从40分钟变成秒级每天凌晨定时执行ALTER TABLE user_operation_log DROP PARTITION pXXX即可。按时间范围查询原来在亿级大表上要走索引回表平均要几百毫秒分区裁剪生效后命中单天分区查询稳定在几十毫秒。插入性能也有一定提升因为每个分区的索引结构更独立锁竞争更小。不过坦白讲如果主键不带分区键这个效果会打折扣因为我们这里把operation_time放进了联合主键插入时B树定位的范围本身就小了一些。这里要提醒的是分区表不等于不需要分区管理自动化。我另外写了一个定时任务每天提前创建未来30天的分区避免某天午夜因为新数据来了但分区不存在而导致插入失败。这个定时任务可以用MySQL自带的事件调度器Event Scheduler实现也可以由应用层的定时脚本执行看你们团队的运维习惯。脚本核心就一句ALTER TABLE user_operation_log REORGANIZE PARTITION p_max INTO ( PARTITION p2024xxxx VALUES LESS THAN (TO_DAYS(2024-xx-yy)), PARTITION p_max VALUES LESS THAN MAXVALUE );4.3 和Oracle分区表的对比因为热词里出现了“oracle 分区表在线改为普通表”我顺便提一句。Oracle的分区表功能非常成熟比如支持二级分区、自动分区、在线重定义分区表为普通表DBMS_REDEFINITION在线改分区也基本不锁业务。而MySQL的分区表起步较晚能力上确实差一截不能在线把分区表改成普通表大表ALTER TABLE REMOVE PARTITIONING会锁很长没有全局二级索引分区裁剪算法也不如Oracle灵活。如果你的架构是Oracle背景迁到MySQL对分区表的预期要调低一些很多Oracle里能做的骚操作在MySQL里只能绕道。不过反过来说MySQL分区表在“按时间归档”这个最常见的场景里已经足够好用了这也是它虽然限制多、但依然被广泛使用的原因。5. 常见问题与排查技巧实录5.1 明明建了分区查询却全分区扫描这是分区表最头疼的问题。排查思路按步骤来EXPLAIN看partitions列。如果显示所有分区比如p0到p10都出现了说明分区裁剪没生效。检查WHERE条件是否有分区键。没有分区键MySQL必然全分区扫描。检查分区键是否被函数包裹。比如WHERE TO_DAYS(created_at) TO_DAYS(2024-01-15)这种写法不是不能用但优化器有时算不出来精确范围。改成直接比较时间字段的范围。检查字段类型和查询参数类型的隐式转换。分区键是VARCHAR查询条件是数字很可能无法精确匹配分区。检查分区表达式是否足够简单。越复杂的表达式优化器越难推断边界。我习惯性地在任何分区表的慢SQL排查之前先跑一下EXPLAIN确认partitions列基本上80%的问题都能在这一步定位。5.2 查询变慢是不是分区数量太多导致的有朋友问我分区数量是不是越多越好不是。分区过多会导致优化器和存储引擎在打开和扫描文件时开销变大特别是几十个甚至几百个分区时INFORMATION_SCHEMA的统计信息维护、O_DIRECT文件打开、查询计划生成都会变慢。经验值上RANGE按天分保留180天没问题。按小时分一个月720个分区已经开始吃力。超过2000个分区日常DDL和统计更新都会明显变慢。如果你的分区数量已经比较大建议把PARTITION数量的设计从“按小粒度”改成“按业务生命周期”。比如日志类数据按天分但保留周期控制在6个月以内历史归档数据按周或按月分降低分区总数。另外分区表上的ANALYZE TABLE和OPTIMIZE TABLE等操作会遍历所有分区大表上会很慢。建议在低峰期执行并且评估好对主库的影响。5.3 分区表的备份与恢复分区表的备份和普通表没什么本质区别主要用mysqldump或xtrabackup。但有几个实操细节使用mysqldump时默认导出整个表恢复时保持分区结构不变。如果你只想备份某几个分区可以用--where参数比如--wherecreated_at 2024-01-01 AND created_at 2024-02-01但这容易踩坑不建议在生产环境这么干。使用xtrabackup做物理备份时分区表会对应多个.ibd文件备份和恢复逻辑跟普通表一致。恢复时要特别注意版本兼容最好同大版本之间恢复。如果你想单独恢复某个分区到一个临时表可以CREATE TABLE temp LIKE order_log; ALTER TABLE temp REMOVE PARTITIONING; ALTER TABLE temp IMPORT TABLESPACE——但这种方法比较复杂我建议只在紧急恢复时用正常情况下还是全库或全表备份。注意ALTER TABLE ... TRUNCATE PARTITION可以用来清空特定分区的数据速度和删除分区差不多但保留了分区结构。如果只想清数据不想删分区这个命令比DELETE高效太多。5.4 分区表与主从复制分区表在开启了binlog的主从架构下有一些需要注意的点。首先DROP PARTITION这类DDL会作为语句写入binlog从库重放的时候同样是秒级删除不会像DELETE那样逐行重放所以从库的复制延迟也会大幅降低。这算分区表在复制架构里的一大优点。但要注意每个分区的AUTO_INCREMENT计数器在MySQL重启后可能不是全局连续的。这在分区表上更明显因为每个分区有独立的索引结构但AUTO_INCREMENT是表的全局属性所以一般不会因为分区而出现主键重复。不过如果主键是(id, created_at)这种复合主键应用层生成ID的逻辑要确保全局唯一别依赖分区字段做唯一性判断。还有一点MySQL 8.0前分区表在binlog_formatSTATEMENT模式下某些DDL或DML可能导致复制不一致。生产环境最好使用binlog_formatROW这也是8.0以后的默认值用ROW格式不会有这种问题。5.5 分区键选错怎么办有时候上线后才发现分区键选得不对比如按user_id做了HASH分区但业务查询几乎都是按时间范围分区裁剪完全用不上。这时候怎么办只能重建表。如果表数据量不大可以直接CREATE TABLE new_table LIKE old_table; ALTER TABLE new_table REMOVE PARTITIONING; ALTER TABLE new_table PARTITION BY RANGE (...); INSERT INTO new_table SELECT * FROM old_table; RENAME TABLE old_table TO old_table_bak, new_table TO old_table;如果表数据量大强烈建议用pt-online-schema-change或者gh-ost这类工具来操作尽量减少对线上业务的影响。这里也提醒一句在设计阶段就要把查询模式摸清楚再选分区键分区表“上线后改”的成本比普通表高得多。我自己在处理这类问题时会先拉出线上近一周的慢查询日志统计高频查询的WHERE条件看字段出现频次再选分区键。这个习惯后来救过我好几次强烈推荐大家也这么做。写在最后的几个小经验分区表这个东西文档一页纸能写完但真正用好需要很多现场的判断。我这几年用下来的私人经验浓缩成几条第一能用普通表就别用分区表。数据量没到千万级别、没有明显归档需求、查询模式也不稳定的情况上分区表等于自己给自己加维护成本。分区表是工具不是装饰品。第二分区键和主键的设计要一起想。先定分区键再定主键两者必须兼容。不要先建完主键再想着加分区那样大概率报错。第三自动化管理分区是生产环境必需。手动添加分区撑不过一个月。用Event Scheduler或定时任务提前创建分区、定时删除过期分区让它完全自动化运转这才是分区表让人省心的状态。第四监控MAXVALUE分区。凡是RANGE分区带MAXVALUE兜底的都要定期监控它的数据量。如果有一天发现它突然快速增长说明前面的分区边界设置出了问题或者有异常数据比如未来时间戳插进来了。第五在8.0上优先使用TO_DAYS()之类的整数表达式并配合EXPLAIN查看partitions列养成每次写SQL都确认分区裁剪的习惯。这个习惯能帮你提前发现很多性能问题而不是等线上告警了再排查。分区表不是什么高深魔法它就是给数据加了一层物理边界让MySQL知道“你要的数据大概在哪个篮子里”。把边界划好把篮子管理好大表也就没那么可怕了。 MySQL分区表实战：从原理到避坑，解决大表查询与归档难题
MySQL高阶实战分区表Partitioning Table从原理到避坑做MySQL开发或者DBA的谁没有被大表折磨过。单表几千万行查询能跑但慢索引越来越大维护要半夜搞删除旧数据直接DELETE能把binlog撑爆。我早年接手过一个运营系统日志表三个月就上亿了日常查询和归档都痛苦到怀疑人生。后来把核心表改成分区表Partitioning Table问题一下子清晰了很多。这篇就把我实践中对MySQL分区表的理解、操作步骤和踩过的坑从头到尾梳理一遍适合数据量开始涨、正在纠结要不要上分区的朋友参考。分区表不是“分库分表”它是在同一个逻辑表内部按一定规则把数据物理拆分成多个分区存储。对业务层来说你仍然查这一张表但MySQL内部可以只扫描命中的分区这种能力叫“分区裁剪”Partition Pruning也是分区表最核心的价值。下面内容我会把为什么用、怎么用、什么时候千万别用以及我踩过的各种坑全部拆开讲。1. 分区表的适用场景与核心设计思路1.1 分区表到底解决什么问题先想清楚分区表不是银弹。它对下面几类问题帮助最大第一数据归档和删除的代价。这是最直接的应用。普通表删除几千万行要么一条一条删产生海量binlog要么DROP表重建但锁表时间太长。有了分区可以直接ALTER TABLE ... DROP PARTITION这相当于删除一个独立文件或独立表空间秒级完成且不产生大量binlog。很多业务会把一年前的数据直接丢弃用RANGE分区按月切分一次删一个月的数据就是一条DDL的事。第二查询性能提升。注意这是有条件的。只有WHERE条件里包含分区键时优化器才能做分区裁剪只扫描相关的几个分区减少IO和缓冲池的压力。比如订单表按order_date分区查某一天的单子只需要扫那个分区。但如果查询条件不带分区键MySQL会扫描所有分区性能反而可能比普通表更差。第三减轻索引维护压力。大表的二级索引会随着数据增长变得非常大每次插入都要随机更新多棵B树。分区后每个分区有独立的索引树整体索引的高度和写入的随机性都会下降。这在大量并发插入的场景下体感很明显。第四数据管理的灵活性。你可以只对热数据所在的几个分区做优化或备份冷数据分区甚至可以放到省钱的低速存储上。比如一年内的订单放SSD更早的分区挪到普通机械盘这在MySQL里可以通过不同分区的表空间配置实现。1.2 什么时候不建议用分区这一点我必须放在前面说因为它比“怎么建分区”更重要。分区表有几个硬伤分区键必须包含在主键或唯一键中。如果你的主键是id又想按create_time分区对不起MySQL直接报错。这是很多人第一次建分区表最大的拦路虎。分区表跨分区查询、跨分区JOIN、带UNIQUE KEY的操作性能可能不尽如人意。如果你的业务有大量关联查询而且关联字段不是分区键分区表帮不上忙反而可能拖慢。分区数量有限制MySQL 5.7及以前单表最多8192个分区8.0沿用了这个限制。别觉得很多按天分区两年就是700多个了按小时分区一个月就爆炸。业务设计时要估算清楚。分区无法完全替代索引。分区是一种粗粒度的数据组织方式它减少的是“扫描范围”不等于你不用建索引。真正定位到具体行还是要靠索引。所以分区表的正确打开方式是数据量确实大到单表索引和IO已经吃力并且查询模式里有稳定、高频、区分度好的分区键。2. 核心机制与分区类型选型2.1 分区键与分区裁剪原理分区裁剪是理解分区表的钥匙。MySQL优化器在解析SQL时如果发现WHERE条件里有分区键的取值范围就会提前排除掉不匹配的分区只扫描满足条件的分区。类似地INSERT的时候MySQL根据插入值的分区键直接定位到对应分区不需要全表扫描找位置。这意味着一个关键点分区键的选型直接决定分区表有没有效果。你不用分区键查询优化器就只能全分区扫描而且每个分区都有一份索引等于把所有分区都摸一遍性能比普通表更糟。我见过有同事把分区键建了但所有业务SQL都没有带这个字段结果上线后慢查询翻了几倍最后只能回滚。所以设计阶段就要想清楚哪个字段是所有高频查询都会带上的条件如果是时间字段是created_at还是order_date如果是区域字段是region_id还是city_id这个字段的值分布是否均匀想清楚了再选分区类型。2.2 四种分区类型怎么选MySQL支持四种分区类型我用一个表格对比它们的语法和适用场景分区类型关键语法适用场景注意事项RANGEPARTITION p0 VALUES LESS THAN (...)按时间、按数值区间最常用必须定义上限新增分区要手动或定时LISTPARTITION p0 VALUES IN (...)按枚举值如省份、类型无法覆盖的值会报错需提前规划全量值HASHPARTITION BY HASH(expr) PARTITIONS N数据分布均匀、无自然分界数量固定无法直接删除单分区数据KEYPARTITION BY KEY(col) PARTITIONS N类似HASH但使用MySQL内部哈希函数只允许使用列不能是表达式实际业务里RANGE分区占了90%以上的场景。为什么因为大多数需要分区的表都有时间维度的访问特征最近的数据访问最频繁老数据逐渐变冷最后被归档和删除。RANGE分区天然匹配这种“冷热分层”的需求。LIST分区适合分区键是固定枚举值的情况比如按省份、按业务类型。但要注意LIST分区必须列出所有可能的值如果插入一个不在列表里的值MySQL会直接报错ERROR 1526。所以如果枚举值可能扩展LIST分区会很难维护要提前评估。HASH和KEY分区适合那种“没有明显区间但希望数据均匀分散”的场景。它们的共同缺点是分区数量建好后一般不做增减删除单分区数据的意义不大旧数据归档也很别扭。而且HASH分区的表达式最好返回整数用日期函数需要确保函数能生成均匀分布的值。我个人的建议是——除非非常明确要均匀分布否则优先考虑RANGE。2.3 分区与索引如何配合分区表和索引是两套独立的工具它们的工作方式很多人没理解透。首先MySQL分区表不支持全局唯一索引也就是说如果分区键不是主键或唯一键的一部分你就不能在该表上建唯一索引。这是很多业务从普通表改成表时发现索引报错的原因。因为每个分区内的索引是局部的唯一性只能在一个分区内保证跨分区无法校验。其次分区表上的索引本质上是每个分区各有一棵B树。所以如果你给表建了二级索引那么有多少个分区就有多少棵索引树。查询时如果分区裁剪生效只需要查命中的那几棵索引树如果没命中就要把每棵索引树都查一遍这比普通表还慢。所以关于索引的实操建议是分区键字段一定要建索引。虽然分区列表本身就有一种“索引”的效果但二级索引的建立还是必要的特别是高基数的非分区字段。联合索引尽量把分区键放在最前面。这样索引的B树可以直接利用分区裁剪的优势减少扫描量。不要滥用索引。分区表多一层结构每个分区的索引都要维护索引越多写入成本越高。只给高频查询的字段建索引。在8.0版本中MySQL对分区表的索引支持也做了优化支持在分区表上使用INVISIBLE INDEX等特性但核心设计思路没有变分区减少扫描范围索引负责精确定位两个配合才能发挥最大价值。3. 实操过程从建表到分区管理3.1 创建分区表的完整步骤以最常见的按月RANGE分区为例。先看一个完整的建表语句CREATE TABLE order_log ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL DEFAULT 0.00, created_at datetime NOT NULL, PRIMARY KEY (id, created_at) ) ENGINEInnoDB PARTITION BY RANGE (TO_DAYS(created_at)) ( 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)), PARTITION p202404 VALUES LESS THAN (TO_DAYS(2024-05-01)), PARTITION p202405 VALUES LESS THAN (TO_DAYS(2024-06-01)), PARTITION p202406 VALUES LESS THAN (TO_DAYS(2024-07-01)), PARTITION p_max VALUES LESS THAN MAXVALUE );这里有几个关键点要注意第一主键必须包含分区键。我上面把created_at放进了联合主键(id, created_at)这是MySQL的硬性规则。如果主键只有id建表直接报错A PRIMARY KEY must include all columns in the tables partitioning function。你可以要么把分区键加进主键要么把主键去掉改成普通索引但一般不建议去掉主键有些场景使用无主键表配合分区也能跑但复制和高可用架构下会有很多麻烦不太推荐。第二用TO_DAYS()还是YEAR()。按天/按月分区时很多教程会用TO_DAYS()或TO_SECONDS()将日期转换为整数。好处是RANGE分区按整数值比较效率更高。直接用created_at字段本身做RANGEMySQL也允许从5.7开始支持函数表达式但要注意表达式的确定性和性能。我个人习惯用TO_DAYS()因为这个函数返回的整数B树和分区比较都不存在隐式转换问题。第三最后一个分区必须用MAXVALUE兜底。如果你定义的分区上限只到6月业务一插入7月的数据就直接报错Table has no partition for value from column_list。我吃过这个亏——上线前忘了加后补分区某天突然告警所有插入全挂。所以我把p_max VALUES LESS THAN MAXVALUE作为最后一个保险分区。查某些日期很Old的归档数据不会报错但要注意p_max会持续膨胀需要定期拆分或归档。如果你是在已有的大表上加分区MySQL 8.0支持ALTER TABLE ... PARTITION BY ...直接转换但这会重建整张表锁表时间很长生产环境务必用pt-online-schema-change这类工具或者计划维护窗口操作。3.2 分区的日常管理拆分、添加、删除、合并建好分区表只是开始真正的功力在于后续的运维管理。RANGE分区的核心操作有几种我一个个说。添加新分区ALTER TABLE order_log ADD PARTITION ( PARTITION p202407 VALUES LESS THAN (TO_DAYS(2024-08-01)) );但注意如果你的表已经有MAXVALUE分区这个时候直接ADD PARTITION会报错VALUES LESS THAN MAXVALUE must be the last partition。你必须先把MAXVALUE分区拆分或删除才能加新分区。正确的做法是使用REORGANIZE PARTITION把p_max拆成新分区和新的p_maxALTER TABLE order_log REORGANIZE PARTITION p_max INTO ( PARTITION p202407 VALUES LESS THAN (TO_DAYS(2024-08-01)), PARTITION p202408 VALUES LESS THAN (TO_DAYS(2024-09-01)), PARTITION p_max VALUES LESS THAN MAXVALUE );删除旧分区ALTER TABLE order_log DROP PARTITION p202401;这一句执行完2024年1月的数据就物理删除了而且性能极快不会产生逐行删除的binlog。这也是分区表在数据归档上最酣畅淋漓的优势。记得删除前先确认数据不需要保留最好SELECT COUNT(*) FROM order_log PARTITION(p202401)看一眼数据量。合并分区ALTER TABLE order_log REORGANIZE PARTITION p202401, p202402 INTO ( PARTITION p202401_02 VALUES LESS THAN (TO_DAYS(2024-03-01)) );合并会重建分区如果有大量数据会比较耗资源尽量在业务低峰期做。去掉分区如果某天业务不需要分区了想恢复成普通表MySQL 5.7及以后可以使用ALTER TABLE order_log REMOVE PARTITIONING;这一步相当于重建表大表要谨慎测试环境提前演练锁表时间。查看分区信息SELECT PARTITION_NAME, PARTITION_METHOD, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA DATABASE() AND TABLE_NAME order_log;这条SQL是运维日常必须掌握的可以快速确认各分区的数据行数和状态排查有没有数据落到MAXVALUE之外的异常分区。3.3 常见坑主键约束、NULL值处理、查询失效分区表的坑很多是“CODE里跑着跑着突然炸了”的类型。我把最常见的几类罗列一下。坑一NULL值去哪了不同分区类型对NULL值的默认处理不一样非常容易踩。RANGE分区NULL会被放到最小的分区。是的你没看错VALUES LESS THAN (xxx)不包含NULL的大小判断MySQL把NULL当作比任何值都小直接塞进第一个分区。LIST分区如果分区的VALUES IN里没有明确包含NULL插入NULL会直接报错。坑不坑必须把NULL显式写进某分区的值列表里。HASH/KEY分区NULL会被当作0处理所以会落到PARTITION p0。这就导致一个结果如果你的RANGE分区表第一个分区是按月切的p202401所有created_at IS NULL的数据都会积压到p202401里。查询WHERE created_at IS NULL时优化器可能不按预期裁剪整个分区都要扫。我的经验是业务表字段尽量都设为NOT NULL或者在建表时给默认值避免NULL值分区的“隐藏聚集”问题。坑二自定义函数做分区表达式要谨慎。MySQL官方限制分区表达式必须是确定的。像NOW()就不行因为每次调用返回不同值。如果用YEAR(created_at)或TO_DAYS(created_at)没问题因为给定字段值结果固定。但如果你用了DATE_FORMAT(created_at, %Y%m)这种返回字符串的分区表达式性能会比较差分区裁剪也可能失效尽量不要用。坑三WHERE条件带函数分区裁剪失效。比如分区键是created_at你查WHERE DATE(created_at) 2024-01-15MySQL没法直接推断出分区范围可能全分区扫描。正确写法是WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00这样优化器能明确算到p202401的范围。这是一个非常容易忽略、但影响巨大的细节。坑四分区键类型不一致。比如分区键是VARCHAR但你查询时传入数字MySQL的隐式转换可能导致无法匹配分区键裁剪失效全表扫描走一遍。这也是慢查询排查时经常遇到的问题。最好保持分区键字段类型和应用程序传入参数类型一致尽量别依赖MySQL的隐式转换。4. 真实场景一张千万级日志表的改造4.1 需求与分析我参与过的一个真实项目有一张用户操作日志表user_operation_log每天新增100万行一个月就3000万行保留6个月。当时的痛点是单表数据量很快过亿查询慢索引维护成本高。每天凌晨有清理任务把3个月前的数据DELETE掉每天跑40多分钟还容易拖慢主库。后台管理需要按用户ID和时间范围查询操作日志大部分场景都限制在当天或近7天。分析下来这就是非常典型的分区表适用场景数据有明确的时间属性日常查询集中在近期旧数据需要定期删除。分区键选择operation_time按天分区保留6个月就是180个分区远低于8192上限。当时有一个争议要不要把user_id也加入分区规则比如先按user_id做HASH分区再按时间做子分区。我的结论是不搞子分区。原因有三一是子分区会让物理文件数量翻倍运维变复杂二是我们的查询基本都带时间范围条件RANGE分区的裁剪已经足够三是子分区的管理操作特别是ALTER TABLE更复杂容易出错。能用简单方案就不用复杂的。4.2 建表方案与效果统计具体建表语句大致如下CREATE TABLE user_operation_log ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, operation_type varchar(32) NOT NULL, operation_desc varchar(255) DEFAULT NULL, operation_time datetime NOT NULL, PRIMARY KEY (id, operation_time), KEY idx_user_time (user_id, operation_time) ) ENGINEInnoDB PARTITION BY RANGE (TO_DAYS(operation_time)) ( PARTITION p20240101 VALUES LESS THAN (TO_DAYS(2024-01-02)), PARTITION p20240102 VALUES LESS THAN (TO_DAYS(2024-01-03)), PARTITION p20240103 VALUES LESS THAN (TO_DAYS(2024-01-04)) -- 后续分区由定时任务自动添加 );上线后的变化非常直观删除旧数据的任务从40分钟变成秒级每天凌晨定时执行ALTER TABLE user_operation_log DROP PARTITION pXXX即可。按时间范围查询原来在亿级大表上要走索引回表平均要几百毫秒分区裁剪生效后命中单天分区查询稳定在几十毫秒。插入性能也有一定提升因为每个分区的索引结构更独立锁竞争更小。不过坦白讲如果主键不带分区键这个效果会打折扣因为我们这里把operation_time放进了联合主键插入时B树定位的范围本身就小了一些。这里要提醒的是分区表不等于不需要分区管理自动化。我另外写了一个定时任务每天提前创建未来30天的分区避免某天午夜因为新数据来了但分区不存在而导致插入失败。这个定时任务可以用MySQL自带的事件调度器Event Scheduler实现也可以由应用层的定时脚本执行看你们团队的运维习惯。脚本核心就一句ALTER TABLE user_operation_log REORGANIZE PARTITION p_max INTO ( PARTITION p2024xxxx VALUES LESS THAN (TO_DAYS(2024-xx-yy)), PARTITION p_max VALUES LESS THAN MAXVALUE );4.3 和Oracle分区表的对比因为热词里出现了“oracle 分区表在线改为普通表”我顺便提一句。Oracle的分区表功能非常成熟比如支持二级分区、自动分区、在线重定义分区表为普通表DBMS_REDEFINITION在线改分区也基本不锁业务。而MySQL的分区表起步较晚能力上确实差一截不能在线把分区表改成普通表大表ALTER TABLE REMOVE PARTITIONING会锁很长没有全局二级索引分区裁剪算法也不如Oracle灵活。如果你的架构是Oracle背景迁到MySQL对分区表的预期要调低一些很多Oracle里能做的骚操作在MySQL里只能绕道。不过反过来说MySQL分区表在“按时间归档”这个最常见的场景里已经足够好用了这也是它虽然限制多、但依然被广泛使用的原因。5. 常见问题与排查技巧实录5.1 明明建了分区查询却全分区扫描这是分区表最头疼的问题。排查思路按步骤来EXPLAIN看partitions列。如果显示所有分区比如p0到p10都出现了说明分区裁剪没生效。检查WHERE条件是否有分区键。没有分区键MySQL必然全分区扫描。检查分区键是否被函数包裹。比如WHERE TO_DAYS(created_at) TO_DAYS(2024-01-15)这种写法不是不能用但优化器有时算不出来精确范围。改成直接比较时间字段的范围。检查字段类型和查询参数类型的隐式转换。分区键是VARCHAR查询条件是数字很可能无法精确匹配分区。检查分区表达式是否足够简单。越复杂的表达式优化器越难推断边界。我习惯性地在任何分区表的慢SQL排查之前先跑一下EXPLAIN确认partitions列基本上80%的问题都能在这一步定位。5.2 查询变慢是不是分区数量太多导致的有朋友问我分区数量是不是越多越好不是。分区过多会导致优化器和存储引擎在打开和扫描文件时开销变大特别是几十个甚至几百个分区时INFORMATION_SCHEMA的统计信息维护、O_DIRECT文件打开、查询计划生成都会变慢。经验值上RANGE按天分保留180天没问题。按小时分一个月720个分区已经开始吃力。超过2000个分区日常DDL和统计更新都会明显变慢。如果你的分区数量已经比较大建议把PARTITION数量的设计从“按小粒度”改成“按业务生命周期”。比如日志类数据按天分但保留周期控制在6个月以内历史归档数据按周或按月分降低分区总数。另外分区表上的ANALYZE TABLE和OPTIMIZE TABLE等操作会遍历所有分区大表上会很慢。建议在低峰期执行并且评估好对主库的影响。5.3 分区表的备份与恢复分区表的备份和普通表没什么本质区别主要用mysqldump或xtrabackup。但有几个实操细节使用mysqldump时默认导出整个表恢复时保持分区结构不变。如果你只想备份某几个分区可以用--where参数比如--wherecreated_at 2024-01-01 AND created_at 2024-02-01但这容易踩坑不建议在生产环境这么干。使用xtrabackup做物理备份时分区表会对应多个.ibd文件备份和恢复逻辑跟普通表一致。恢复时要特别注意版本兼容最好同大版本之间恢复。如果你想单独恢复某个分区到一个临时表可以CREATE TABLE temp LIKE order_log; ALTER TABLE temp REMOVE PARTITIONING; ALTER TABLE temp IMPORT TABLESPACE——但这种方法比较复杂我建议只在紧急恢复时用正常情况下还是全库或全表备份。注意ALTER TABLE ... TRUNCATE PARTITION可以用来清空特定分区的数据速度和删除分区差不多但保留了分区结构。如果只想清数据不想删分区这个命令比DELETE高效太多。5.4 分区表与主从复制分区表在开启了binlog的主从架构下有一些需要注意的点。首先DROP PARTITION这类DDL会作为语句写入binlog从库重放的时候同样是秒级删除不会像DELETE那样逐行重放所以从库的复制延迟也会大幅降低。这算分区表在复制架构里的一大优点。但要注意每个分区的AUTO_INCREMENT计数器在MySQL重启后可能不是全局连续的。这在分区表上更明显因为每个分区有独立的索引结构但AUTO_INCREMENT是表的全局属性所以一般不会因为分区而出现主键重复。不过如果主键是(id, created_at)这种复合主键应用层生成ID的逻辑要确保全局唯一别依赖分区字段做唯一性判断。还有一点MySQL 8.0前分区表在binlog_formatSTATEMENT模式下某些DDL或DML可能导致复制不一致。生产环境最好使用binlog_formatROW这也是8.0以后的默认值用ROW格式不会有这种问题。5.5 分区键选错怎么办有时候上线后才发现分区键选得不对比如按user_id做了HASH分区但业务查询几乎都是按时间范围分区裁剪完全用不上。这时候怎么办只能重建表。如果表数据量不大可以直接CREATE TABLE new_table LIKE old_table; ALTER TABLE new_table REMOVE PARTITIONING; ALTER TABLE new_table PARTITION BY RANGE (...); INSERT INTO new_table SELECT * FROM old_table; RENAME TABLE old_table TO old_table_bak, new_table TO old_table;如果表数据量大强烈建议用pt-online-schema-change或者gh-ost这类工具来操作尽量减少对线上业务的影响。这里也提醒一句在设计阶段就要把查询模式摸清楚再选分区键分区表“上线后改”的成本比普通表高得多。我自己在处理这类问题时会先拉出线上近一周的慢查询日志统计高频查询的WHERE条件看字段出现频次再选分区键。这个习惯后来救过我好几次强烈推荐大家也这么做。写在最后的几个小经验分区表这个东西文档一页纸能写完但真正用好需要很多现场的判断。我这几年用下来的私人经验浓缩成几条第一能用普通表就别用分区表。数据量没到千万级别、没有明显归档需求、查询模式也不稳定的情况上分区表等于自己给自己加维护成本。分区表是工具不是装饰品。第二分区键和主键的设计要一起想。先定分区键再定主键两者必须兼容。不要先建完主键再想着加分区那样大概率报错。第三自动化管理分区是生产环境必需。手动添加分区撑不过一个月。用Event Scheduler或定时任务提前创建分区、定时删除过期分区让它完全自动化运转这才是分区表让人省心的状态。第四监控MAXVALUE分区。凡是RANGE分区带MAXVALUE兜底的都要定期监控它的数据量。如果有一天发现它突然快速增长说明前面的分区边界设置出了问题或者有异常数据比如未来时间戳插进来了。第五在8.0上优先使用TO_DAYS()之类的整数表达式并配合EXPLAIN查看partitions列养成每次写SQL都确认分区裁剪的习惯。这个习惯能帮你提前发现很多性能问题而不是等线上告警了再排查。分区表不是什么高深魔法它就是给数据加了一层物理边界让MySQL知道“你要的数据大概在哪个篮子里”。把边界划好把篮子管理好大表也就没那么可怕了。