MySQL 单表一亿行之后我踩过的坑：分区表、分库分表、归档到底该先上哪个

📅 ✍️ 📂 数据库 👁️ 公开 📝 ID: 2203

🔑 关键词：MySQL大表优化,MySQL分区表,分库分表,数据归档,深分页

📖 摘要：订单表跑到 1.2 亿行，把分区表、ShardingSphere 分库分表、冷热归档三种方案都实际压了一遍，记录具体参数、SQL、耗时数字和几个网上很少有人提的坑。

先把场景交代清楚，不然聊方案都是空谈

图片

表是订单表 orders，1.2 亿行，MySQL 8.0.32，16C64G，SSD 云盘，单表文件大概 47GB（含索引）。业务侧统计了一下，92% 的查询都带 user_id，只有 3% 是运营拉的全表范围报表，剩下 5% 是定时任务扫。这个分布很重要，因为后面选方案基本就是看这个比例。

还有个背景：这张表不是一开始就这么大的。2021 年上线的时候一天才几万单，2023 年做活动那天冲到了单日 180 万单，从那之后就开始有人反馈【订单列表加载慢】。真正让我下决心动的，是有个周三凌晨两点，监控告警说接口 P99 从 180ms 涨到了 4.2s，我爬起来看慢查询日志，一条 SELECT ... ORDER BY id DESC LIMIT 1000000, 20 跑了 6.8 秒。

那个被传烂的「2000 万行」说法，我认真算过一遍

这个数字的来源是 InnoDB 三层 B+ 树的容量推算：假设主键 bigint 8 字节加页指针 6 字节共 14 字节，16KB 的页大约能放 1170 个键值；叶子节点按每行 1KB 算，一页 16 行。三层就是 1170 × 1170 × 16 ≈ 2190 万。逻辑没错，但前提是【每行 1KB】。

我们这张表实际平均行长 380 字节左右，叶子页能放 40 行上下，理论上撑到 5000 万行才会掉到四层。所以把 2000 万当成硬红线是偷懒。我后来复盘，真正让查询变慢的不是行数，是两件事：一是二级索引回表次数太多，二是深分页要扫描并丢弃前 100 万行。行数只是把这两个问题放大了。

图片

我实测的几组数字，环境就是上面那台机器，数据量 1.2 亿：

  • LIMIT 1000000, 20：冷缓存 4.7s，热缓存 1.9s
  • 改成游标分页 WHERE id < 1234567 ORDER BY id DESC LIMIT 20：0.02s
  • SELECT count(*) FROM orders WHERE status = 3：8.3s（status 基数只有 5，走索引还不如全表）
  • 走覆盖索引 SELECT id FROM orders WHERE user_id = 8821 ORDER BY id DESC LIMIT 20：0.008s

最后一条能跑这么快，是因为 (user_id, id) 这个联合索引本身就带主键，不用回表。这个索引我加完之后，订单列表接口 P99 直接从 4.2s 掉到 90ms，比后面做的任何架构改造都立竿见影。

方案一：分区表，改造成本最低但有两个硬限制

分区表的 SQL 长这样，按季度 RANGE 分区：

图片

ALTER TABLE orders
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p2023q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
  PARTITION p2023q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
  PARTITION p2023q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
  PARTITION p2023q4 VALUES LESS THAN (TO_DAYS('2024-01-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

第一个坑：必须把 created_at 加进主键，改成 PRIMARY KEY (id, created_at)，否则直接报 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function。这个改动会连带影响所有用 id 当外键引用的地方，我们当时有 3 个下游服务要跟着改。

第二个坑是查询必须带分区键才能剪枝。你有 8 个分区，但查询写的是 WHERE user_id = 8821，MySQL 会老老实实扫全部 8 个分区，性能反而比不分区还差一点点（要合并结果集）。运营那 3% 的报表带时间范围，能剪枝，效果很好；用户端那 92% 的查询完全用不上分区。

不过分区表在删数据这件事上真的很爽。删掉 2023Q1 那 1200 万行，ALTER TABLE orders DROP PARTITION p2023q1 耗时 0.3 秒。同样的数据用 DELETE FROM orders WHERE created_at < '2023-04-01' 删，跑了 41 分钟，binlog 写了 8.2GB，中间还差点把从库延迟拉到 20 分钟。

方案二：ShardingSphere 分 16 库 64 表，能力最强但代价也最大

图片

版本用的 ShardingSphere-JDBC 5.3.2，分片键 user_id 取模，16 个库每个库 4 张表。配置大概 80 行 YAML，加上自定义的雪花算法 ID 生成器。

改造完之后单条按 user_id 查的 SQL 稳定在 15ms 左右，写入也均匀了，单库 QPS 从 3200 降到 200 出头。这些都是好的。

问题是这些：

  1. 跨片查询聚合。运营要查【某个商品最近 7 天销量】，分片键是 user_id，这条 SQL 得广播到 64 张表再归并。我们压测过一次，同样的 SQL 在单表 1.2 亿时跑 6s，分片后跑 11s。你得分片键和查询模式对得上才有收益。
  2. 分布式事务。原来一个 @Transactional 能搞定的事，现在得引 Seata，或者改成最终一致。我们有两个业务场景妥协了，改成 TCC 补偿。
  3. 运维复杂度陡增。备份从【mysqldump 一张表】变成【16 个实例的调度】；建索引要写脚本遍历 64 张表；有一张表因为历史数据倾斜，数据量是其他表的 3 倍。

我不后悔做分库分表，但我后悔的是把它排在第二位做。

图片

方案三：冷热归档，被严重低估的一招

最后落地的其实是这个，而且效果最好。思路很简单：只有最近 90 天的订单会被高频访问，更早的数据改到普通用户查不到、运营要用时走离线库。

热表只留 90 天，大概 900 万行，表文件 3.8GB，全量能塞进 buffer pool，普通查询几乎不落盘。冷表用 ROW_FORMAT=COMPRESSED，配合 innodb_compression_level=6，实测压缩比 3.2:1，1.1 亿行压到 14GB。

归档脚本我写的是分批删，每批 5000 行，中间 sleep 50ms，避免长事务：

INSERT INTO orders_cold SELECT * FROM orders WHERE created_at < ? LIMIT 5000;
DELETE FROM orders WHERE created_at < ? LIMIT 5000;

图片

跑了一晚上，凌晨 1 点到早上 7 点，搬走 1.1 亿行，主库延迟峰值 1.2 秒。

如果让我重排一次顺序

先归档，再考虑分区，最后才是分库分表。理由很直接：归档的改动量是 3 天，分区表是 2 周（还要改主键和下游），分库分表是 2 个月起步而且会永久抬高团队的运维门槛。绝大多数业务，把 90 天以前的数据挪走之后，热表根本撑不到需要分片的程度。

唯一让我觉得该早点做的是那个 (user_id, id) 联合索引。它在归档、分区、分片三个方案里都是必要的，而且成本最低，加索引那 20 分钟比后面所有事情加起来都值。

最后吐槽一句：我当初花了两周研究分片算法里的雪花 ID 时钟回拨怎么处理，结果发现真正的瓶颈是运营同事写的一句 SELECT * FROM orders WHERE remark LIKE '%退款%'。技术方案再漂亮，也架不住这种 SQL。

🏷️ 标签：

📚 随机推荐（数据库）