MySQL 分区表治理实战：从分区裁剪失效到在线滚动维护

dbaai

MySQL 分区表治理实战：从分区裁剪失效到在线滚动维护

一、具体的问题

某业务库的日志表 t_log 涨到 4.2 亿行、单表 316GB。当初为了"方便清理历史数据"，一开始就按天做了分区，共 480 个分区。上线半年后陆续暴露三个问题：

• 报表按时间范围查一周，P95 从 900ms 涨到 3.4s。看执行计划，partitions 列把 480 个分区全列了出来——分区裁剪根本没生效，等于在扫全表；
  
• 运维想清理两年前的数据，只能用 DELETE 分批删，删掉 2000 万行花了 4 小时 12 分钟，顺带写了 2.4GB binlog、制造了 38 分钟主从延迟；
  
• 想改用 DROP PARTITION，又发现分区数太多，服务器 Open_files 长期在 80% 以上告警，不敢再加。
  

这三个问题其实是同一件事的三个面：分区表用对了是几乎免维护的归档利器，用错了就是一个慢查询加一个文件句柄炸弹。

二、核心原理

先把三件事说清楚。

第一，分区裁剪（partition pruning）是优化阶段的静态判断，不是运行时的智能筛选。 优化器只认"分区列与常量直接比较"这种形式。一旦写成 DATE(create_time) >= '2026-01-01'，分区列外面套了一层函数，优化器无法把函数结果映射回分区边界，只能把所有分区列进待扫描清单。这不是缺陷，是明确的设计边界。

第二，分区的空间回收走的是元数据路径，跟 DELETE 完全是两回事。 DROP PARTITION 只改数据字典、删掉分区对应的 .ibd 文件，不产生行级 undo，也不产生行级 binlog。DELETE 2000 万行则要写 2000 万条 undo 与 binlog，还要等 purge 线程回收。同一件事，前者亚秒级完成，后者按小时计。

第三，分区不是越多越好。 InnoDB 每个分区一个独立表空间文件，分区数会直接乘进 Open_files、表缓存和优化器的分区评估成本。按天分区一年 365 个、十年 3650 个，光评估阶段就要遍历几千个分区边界。比较稳妥的经验区间是按月 30～120 个分区。

三、实例参考（动手步骤）

步骤 0：确认现状基线

2. SELECT table_name, COUNT(*) AS parts,
3.        ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) AS gb
4. FROM information_schema.PARTITIONS
5. WHERE table_schema = 'biz' AND partition_name IS NOT NULL
6. GROUP BY table_name ORDER BY gb DESC;
8. SELECT partition_name, partition_ordinal_position, partition_description,
9.        table_rows, data_length
10. FROM information_schema.PARTITIONS
11. WHERE table_schema='biz' AND table_name='t_log'
12. ORDER BY partition_ordinal_position LIMIT 5;
14. SHOW GLOBAL STATUS LIKE 'Open_files';
15. SHOW VARIABLES LIKE 'open_files_limit';
16. SHOW VARIABLES LIKE 'table_open_cache';

复制代码

步骤 1：复现裁剪失效

2. -- 正常：只列出需要的几个分区
3. EXPLAIN SELECT COUNT(*) FROM t_log
4. WHERE create_time >= '2026-10-01 00:00:00';
6. -- 反例一：分区列上套函数，partitions 全列出
7. EXPLAIN SELECT COUNT(*) FROM t_log
8. WHERE DATE(create_time) >= '2026-10-01';
10. -- 反例二：列是 datetime，字面量没带时分秒，同样全列出
11. EXPLAIN SELECT COUNT(*) FROM t_log
12. WHERE create_time >= '2026-10-01';

复制代码

普通 EXPLAIN 的 partitions 列就是最直接的证据：列了几个分区，实际就扫了几个分区，不用猜。

步骤 2：把函数从分区列上摘下来

2. -- 反例
3. WHERE DATE(create_time) = '2026-10-01'
5. -- 正例：改写成半开区间
6. WHERE create_time >= '2026-10-01 00:00:00'
7.   AND create_time <  '2026-10-02 00:00:00'

复制代码

如果是 MySQL 8.0，建议把老式的 RANGE(TO_DAYS(create_time)) 换成 RANGE COLUMNS(create_time)，条件里可以直接写日期字面量，不再依赖函数白名单。

步骤 3：分区键与唯一索引的硬关系

MySQL 有一条硬限制：唯一索引必须包含分区函数的全部列。所以按 create_time 分区时，主键不能只有 id：

2. CREATE TABLE t_log (
3.   id          BIGINT NOT NULL AUTO_INCREMENT,
4.   tenant_id   INT NOT NULL,
5.   create_time DATETIME NOT NULL,
6.   level       TINYINT NOT NULL,
7.   msg         VARCHAR(512),
8.   PRIMARY KEY (id, create_time),
9.   KEY idx_tenant_time (tenant_id, create_time)
10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
11. PARTITION BY RANGE COLUMNS(create_time) (
12.   PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
13.   PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
14.   PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
15.   PARTITION pmax    VALUES LESS THAN (MAXVALUE)
16. );

复制代码

若写成 PRIMARY KEY (id) 再按 create_time 分区，建表直接报 1503：A UNIQUE INDEX must include all columns in the table's partitioning function。这一条卡住了很多"事后才想加分区"的表。

步骤 4：普通表转分区表

2. ALTER TABLE t_log PARTITION BY RANGE COLUMNS(create_time) (
3.   PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
4.   PARTITION pmax    VALUES LESS THAN (MAXVALUE)
5. ) ALGORITHM=INPLACE, LOCK=NONE;

复制代码

先跑这一句当 dry-run：不支持时它会立刻报错，代价只是解析一次 DDL，不会锁表。若不支持，退回影子表方案——用 gh-ost 按带分区定义的建表语句建影子表，追平后 cut-over，业务无感。不要在业务高峰直接跑 ALGORITHM=COPY 的整表重建。

步骤 5：滚动维护，每月加新分区、删老分区

因为 pmax 必须排在最后，加分区不能直接 ADD，要 REORGANIZE 最后一个分区：

2. ALTER TABLE t_log REORGANIZE PARTITION pmax INTO (
3.   PARTITION p202611 VALUES LESS THAN ('2026-12-01'),
4.   PARTITION pmax    VALUES LESS THAN (MAXVALUE)
5. );
7. -- 清理两年前的分区
8. ALTER TABLE t_log DROP PARTITION p202408;

复制代码

想先留档再删，用 EXCHANGE PARTITION 把分区整体搬到一张普通表，再导出：

2. CREATE TABLE t_log_202408 LIKE t_log;   -- 结构须一致，且不能带外键
3. ALTER TABLE t_log EXCHANGE PARTITION p202408 WITH TABLE t_log_202408;
4. SELECT * FROM t_log_202408 INTO OUTFILE '/tmp/t_log_202408.csv'
5.   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

复制代码

步骤 6：确认空间与句柄真的降下来了

2. du -sh /var/lib/mysql/biz/t_log#P#p202408.ibd
3. SHOW VARIABLES LIKE 'innodb_file_per_table';   -- 必须为 ON
4. SHOW GLOBAL STATUS LIKE 'Open_files';

复制代码

DROP PARTITION 后 .ibd 的物理删除是后台异步的，磁盘空间不会立刻释放；若 innodb_file_per_table=OFF，数据在共享表空间里，删分区永远不还盘。

四、实操检查清单

• 用 information_schema.PARTITIONS 统计每个分区表的分区数，超过 200 个的列为重点整改对象
  
• 对每个分区表跑一次 EXPLAIN，确认 partitions 列不等于全部子分区
  
• 逐条排查 WHERE：分区列上不得套 DATE()、SUBSTRING()、CAST 等函数；日期字面量必须与列类型和精度完全一致
  
• 建表语句里确认唯一索引/主键包含全部分区函数列，避免 1503
  
• 确认 innodb_file_per_table=ON，否则 DROP 分区无法回收磁盘
  
• 检查 Open_files 使用率，超过 70% 就规划下调分区数
  
• 分区定义必须保留 MAXVALUE 兜底分区，避免插入时报 1526
  
• 加分区统一用 REORGANIZE pmax，清理统一用 DROP PARTITION，两条语句写进月度维护脚本
  
• 删除前先用 EXCHANGE PARTITION 落档，核对导出文件行数与大小再 DROP
  
• 分区 DDL 与全量备份窗口错开，避免备份期间数据字典变更
  
• 监控里补两条指标：分区数环比增长、最老分区年龄，提前一个季度预警
  

五、几个容易踩的坑

坑一：以为分区能救所有慢查询。 分区只对"能裁剪到少数分区"的范围查询有效。如果业务主要拿 tenant_id 点查、时间条件不固定，分区帮不上忙，该加的复合索引还得加。

坑二：唯一索引限制让分区方案中途夭折。 分区要在设计阶段就定，别等表大了才想加——事后改分区键等于重建表。

坑三：DELETE 和 DROP PARTITION 混着用。 有人图省事用 DELETE 清分区内数据，结果留下空分区，分区数照涨，句柄问题一点没解决。

坑四：DROP 完没验证就以为空间回来了。 必须用 du 或 information_schema.PARTITIONS 的 data_length 复核。

坑五：把分区当分库分表的替代品。 单机分区解决的是管理粒度和范围扫描量，解决不了单表写入热点和连接数上限。

六、治理前后对比

指标	治理前	治理后
单日范围查询 P95	3.4s	140ms
命中分区数	480 / 480（全分区）	1 / 62
实际扫描行数	421 万	21.7 万
清理 2000 万行历史数据	DELETE 4h12m	DROP PARTITION 0.8s
binlog 增量	2.4GB	约 40KB（仅 DDL）
主从延迟峰值	38min	0
Open_files 使用率	83%	19%
月度维护工时	6h（人工分批 DELETE）	5min（脚本执行两条 DDL）

结论：分区表的价值不在"让查询变快"，而在把数据生命周期管理从行级操作降级为元数据操作。但前提有两个——条件能被裁剪、分区数可控。这两条不成立时，分区表就只是一张更复杂、更容易出事的普通表。