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支持四种分区类型，我用一个表格对比它们的语法和适用场景：

实际业务里，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`) ) ENGINE=InnoDB 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 table's partitioning function。你可以要么把分区键加进主键，要么把主键去掉改成普通索引（但一般不建议去掉主键），有些场景使用无主键表配合分区也能跑，但复制和高可用架构下会有很多麻烦，不太推荐。

第二，用TO_DAYS()还是YEAR()。按天/按月分区时，很多教程会用TO_DAYS()或TO_SECONDS()将日期转换为整数。好处是RANGE分区按整数值比较，效率更高。直接用created_at字段本身做RANGE，MySQL也允许（从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_max：

ALTER 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-15'，MySQL没法直接推断出分区范围，可能全分区扫描。正确写法是：

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`) ) ENGINE=InnoDB 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 明明建了分区，查询却全分区扫描

这是分区表最头疼的问题。排查思路按步骤来：

1. EXPLAIN看partitions列。如果显示所有分区（比如p0到p10都出现了），说明分区裁剪没生效。
2. 检查WHERE条件是否有分区键。没有分区键，MySQL必然全分区扫描。
3. 检查分区键是否被函数包裹。比如WHERE TO_DAYS(created_at) = TO_DAYS('2024-01-15')，这种写法不是不能用，但优化器有时算不出来精确范围。改成直接比较时间字段的范围。
4. 检查字段类型和查询参数类型的隐式转换。分区键是VARCHAR，查询条件是数字，很可能无法精确匹配分区。
5. 检查分区表达式是否足够简单。越复杂的表达式，优化器越难推断边界。

我习惯性地在任何分区表的慢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参数，比如--where="created_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_format=STATEMENT模式下，某些DDL或DML可能导致复制不一致。生产环境最好使用binlog_format=ROW，这也是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知道“你要的数据大概在哪个篮子里”。把边界划好，把篮子管理好，大表也就没那么可怕了。