MySQL分区表实战：按月分区、裁剪与归档取舍

  1.  Home
  2. Text  
    3. Share to：

一、先把问题想清楚：为什么你的 MySQL 慢

接到「网站卡」的报障，绝大多数人的第一反应是「加内存」或者「换服务器」。但 MySQL 的慢通常不是硬件不够，而是三类结构性问题：单表大到索引扛不住、写入量超过单机承受能力、查询模式本身不适合当前存储引擎。

在动手拆之前，先回答三个问题：这张表的增长速率是多少、历史数据有多大的查询价值、拆开之后应用要改多少处代码。三个问题答不出来，就不要拆——分区和分表都是「不可逆」的架构决策，做错了回滚成本极高。

本文用一个真实场景贯穿：一张访问日志表，每天新增 80 万行，一年接近 3 亿行，前端要按时间范围查、后台要按文章 ID 统计。这类表是典型的「只增不改、越老越没人查」，也是分区表最合适的场景。

二、分区、分表、归档：三条路怎么选

很多人把这两个词混着用，其实差别很大。

分区表：还是一张逻辑表，MySQL 在存储层把它切成多个物理文件。应用代码完全不用改，SQL 照旧写 SELECT ... FROM log WHERE created > ?，优化器会通过「分区裁剪」只扫相关分区。适合的场景是「按时间或范围查询、需要保留历史数据、不想改代码」。

分表：应用层或中间件把数据路由到多张物理表（log_01、log_02…），查询必须带分片键。适合的场景是「单表写入压力已经超过单机、需要水平扩展」。代价是所有跨分片查询、聚合、排序都要自己写。

归档：把冷数据迁到另一个库或文件，主表只留近期数据。这是成本最低、效果最直接的一条路，很多情况下做完这一步根本不需要分区。

选择顺序建议：先评估归档能不能解决问题（能删的历史数据有多少）→ 再好考虑分区（查询仍需要历史数据）→ 最后才考虑分表（写入压力真的到顶了）。顺序反了，等于给自己找麻烦。

三、分区表的前置条件，不满足就别试

MySQL 分区有几条硬性限制，踩一次就白干半天：

第一，分区键必须包含在所有唯一索引里。如果表有主键 id，又想按 created 分区，那么主键必须改成 (id, created) 的联合主键。这条最容易被忽略，建表时直接报错 1503。

第二，分区表不支持外键。有关系约束的表要先解掉。

第三，分区数量不要太多。一个分区在打开时需要文件句柄，几百个分区会拖慢元数据操作。按天分区一年就是 365 个，通常按周或按月更合适。

第四，InnoDB 的分区表每张表最多 8192 个分区（含子分区），实际使用建议控制在 100 以内。

四、按月分区的建表脚本

以访问日志为例，按月分区、按时间查询，最合适的形态：

CREATE TABLE access_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id INT UNSIGNED NOT NULL,
  ip VARCHAR(45) NOT NULL,
  ua VARCHAR(255) DEFAULT NULL,
  created DATETIME NOT NULL,
  PRIMARY KEY (id, created),
  KEY idx_post_created (post_id, created)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

几个要点：TO_DAYS 把日期转成整数，比直接用 RANGE COLUMNS 更兼容老版本；必须留一个 pmax 兜底，否则插入超出范围的数据直接报错；主键写成联合主键，满足「分区键在唯一索引里」的要求。

注意 RANGE COLUMNS(created) 是另一种更直观的写法，可以直接写日期值、支持按多个列分区，MySQL 5.5 之后都有。新项目优先用它。

五、需要定期做的事：加分区与删分区

分区表不是建完就完事，pmax 之后的边界必须由脚本按月推进，否则所有新数据都堆在 pmax 里，分区等于没做。

加分区：

ALTER TABLE access_log
REORGANIZE PARTITION pmax INTO (
  PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

删分区（这是分区表最大的优势，秒级完成，不产生 binlog 行级记录）：

ALTER TABLE access_log DROP PARTITION p202511;

对比一下，如果是普通大表删一年的数据：DELETE FROM log WHERE created < '2026-01-01'，会锁表、会产生巨量 binlog、会撑爆 undo log，几千万行的删除可能跑几个小时。而 DROP PARTITION 只是一次文件删除。

把「加下个月分区 + 删 13 个月前的分区」写成一个每月的 cron：

#!/bin/bash
# /root/scripts/log_partition.sh
set -euo pipefail
M=$(date -d '+1 month' +%Y%m)
NEXT=$(date -d '+2 month' +%Y-01-01)
OLD=$(date -d '-13 month' +%Y%m)

mysql -uroot -p"$DBPASS" zz1984 <<SQL
ALTER TABLE access_log REORGANIZE PARTITION pmax INTO (
  PARTITION p${M} VALUES LESS THAN (TO_DAYS('${NEXT}')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);
SQL

mysql -uroot -p"$DBPASS" zz1984 -e "ALTER TABLE access_log DROP PARTITION IF EXISTS p${OLD};"

注意这个脚本的边界处理：如果当月已经加过分区，重复执行会报「分区已存在」，所以要加判断，或者用一个记录上月执行结果的标记文件。

六、验证分区裁剪是否生效

建了分区不等于用上分区。必须用 EXPLAIN 确认优化器只扫了相关分区：

EXPLAIN SELECT COUNT(*) FROM access_log
WHERE created >= '2026-10-01' AND created < '2026-11-01';

看 partitions 列，理想输出是 p202610 一个分区，而不是 p202601,p202602,... 一串。如果扫了全部分区，说明条件被写成了函数形式，比如 WHERE DATE(created) = '2026-10-01'——函数包裹列会让裁剪失效，必须改成范围比较。

另一个陷阱是 TO_DAYS() 与 RANGE 混用。分区定义用 TO_DAYS(created)，查询条件里就不要再写 TO_DAYS(created) = xxx，直接比较日期列即可，优化器能自己转换。

七、大表直接加分区不现实：用影子表迁移

如果表已经有几千万行，直接 ALTER TABLE ... PARTITION BY 会锁表很久。可行的做法是影子表迁移：

第一步，新建一张结构相同的分区表 access_log_new。第二步，用 INSERT INTO ... SELECT 分批把老数据灌进去，每批 10 万行，避免大事务。第三步，业务低峰期做短暂切表：RENAME TABLE access_log TO access_log_old, access_log_new TO access_log，这一步是原子的，毫秒级。第四步，验证无误后删掉旧表。

切表前后要停写入或者用双写，否则会丢数据。对于日志这类「丢几条没关系」的表，直接切、接受最后几秒的损失是最经济的做法。

八、分表：什么时候真的需要

如果单表已经分区，查询仍然慢，那问题通常不在读，而在写。看三个数字：每秒写入行数、单表大小、索引维护耗时。

当单表写入超过每秒 2000 行、或者表大小超过 100GB 时，B+ 树的深度增加、buffer pool 命中率下降，插入开始变慢。这时候才需要考虑分表。

分表的关键是选对分片键。按 post_id % 16 分片能让同一文章的数据落在同一个分片，但「查最近一小时全站访问」这类查询就要扫 16 张表。按时间分片则相反。没有一种分法能同时满足所有查询，所以分表之前先想清楚「最高频的查询是什么」。

对草根站来说，更实际的选择是：日志类数据直接推到 ClickHouse 或写入按天切分的独立表（log_20261031），统计类查询走离线脚本，不用死磕 MySQL 的分表方案。

九、监控与回滚：分区改造上线后要盯的三个数

分区改造属于结构性变更，上线之后不能只看「页面能打开」。三个数字能反映它到底有没有起作用。

第一个是慢查询日志里该表的出现次数。改造前后各统计一天，如果按时间范围查询的慢 SQL 数量没有明显下降，说明分区裁剪没生效，回去用 EXPLAIN 看 partitions 列。

第二个是 information_schema.partitions 里各分区的数据量与行数。正常情况下每个月一个分区、大小均匀；如果某个分区异常膨胀，通常是 pmax 兜底分区在承接所有新数据，说明加分区的脚本没跑。

SELECT partition_name, table_rows,
       ROUND(data_length/1024/1024) AS data_mb
FROM information_schema.partitions
WHERE table_schema = 'zz1984' AND table_name = 'access_log'
ORDER BY partition_ordinal_position;

第三个是磁盘占用。分区表的优势之一是删数据不产生大量 undo，但如果历史分区一直不删，磁盘一样会被吃满。把每周的磁盘增长率和分区总大小放在同一张图上看，增长曲线出现台阶就说明该清理了。

回滚方案必须在上线前就想好。分区表回滚成普通表，做法是新建普通表、分批搬回数据、再切表，代价和正向迁移差不多，所以要尽量缩短「新旧并存」的窗口。更现实的做法是保留旧表至少一周，切表后先只读观察，确认没有问题再删旧表。对于日志类数据，甚至可以直接接受「回滚时丢掉这一段新数据」，只要业务上说得通，就能把回滚简化成一次 RENAME。

另外提醒一句：分区表的 OPTIMIZE TABLE 会对每个分区重建，几亿行的表跑起来可能是小时级，而且会锁住写入。日常维护用它意义不大，真正有效的是「删旧分区 + 加新分区」，别在 OPTIMIZE 上耗时间。

十、小结：先归档，再分区，最后才分表

回到最开始那张日志表。执行顺序应该是：先评估能删多少历史数据（往往能删掉 90%，问题直接消失）→ 剩下的需要保留，上按月分区（代码零改动，删数据秒级）→ 写入压力真的到顶，再考虑分表或换存储引擎。

三条路里，归档成本最低、分区收益最高、分表风险最大。绝大多数草根站的 MySQL 慢，做完归档加分区就够了，不需要走到分表那一步。真正需要人盯的只有一件事：分区边界脚本必须每月按时推进，否则所有新数据都会沉到 pmax 里，分区表退化成普通表，问题会在半年后以「怎么又慢了」的形式再来一次。

Last modification：October 31st, 2026 at 09:25 pm
© 允许规范转载

相关文章

Leave a Comment