Day17｜MySQL大数据大表治理的一些经验

今天是写博客的第十七天。前面分享了不少关于高并发和 Kafka 的内容，今天想聊一聊最近自己在 MySQL 大数据、大表治理上的一些心得。
这些不仅是我查资料总结的，还有我在实战中踩过的坑、挨过的训，都是货真价实的经验。

遥想当初无脑加索引的行为，真的有点很像新手哈哈哈哈。搞得那几张表大的跟个内存表一样，现在都在吐槽那段屎山查询，又迟迟不敢动哈哈哈哈哈 这边还是建议每一次查询，最好是要去遵循现有的索引，能走现有索引就走现有索引，非必要不自己另加索引。

---

1. 为什么要治理大表？

我们知道，一个表的数据量如果持续增长到千万甚至上亿行级别，不光影响查询性能，还会拖慢 DML（INSERT、UPDATE、DELETE）的速度，甚至连索引维护、备份恢复都会越来越慢。

我在项目中就遇到过这种情况——某个用户行为记录表，从上线之初到后来增长到了几千万条数据，而且业务每天都在疯狂新增记录。
刚开始的时候还没意识到问题，直到一次线上查询跑了 几十秒，而且 SQL 扫描了上千万行，把数据库 CPU 干到 100%，应用直接卡死。那次事故之后，我才真正开始学着去治理这些“大胖子”表。

---

2. 我做过的几种治理方案

2.1 按日期 Range 分区

第一次做治理的时候，我采用的是 MySQL 原生分区（Partition），按日期做 RANGE 分区，比如按照 YYYYMM 来给数据分片。

示例：

sql

ALTER TABLE user_action PARTITION BY RANGE (YEAR(action_time)*100 + MONTH(action_time)) ( PARTITION p202301 VALUES LESS THAN (202302), PARTITION p202302 VALUES LESS THAN (202303), PARTITION pmax VALUES LESS THAN MAXVALUE );

这样做的好处：

• 管理方便，查询某个月的数据，可以直接命中对应分区而不用扫全表。
• 老数据删除也很快，直接 DROP PARTITION p202201 就可以，避免了大批量 DELETE 带来的事务开销。

但我也踩过坑：

• 有些业务 SQL 没加日期条件时，会打跨分区的全表扫描，一样慢。
• 分区列必须包含在 主键 或 所有唯一索引 里，否则会报错。
• 改分区方案比改索引还要“重”，一旦定了最好不要频繁变更。

---

2.2 分库分表

后来我们面对更夸张的日志数据量，直接上了 分库分表。这次不是用 MySQL 分区，而是物理上拆成多个库 / 表，比如按 user_id % N 拆成 N 张表。

方案选型：

• 框架层面用了 ShardingSphere，这样应用侧改动比较少。
• 分片规则按用户 ID 做 hash，能比较均匀地分摊压力。

它的优势：

• 单表数据量显著下降，所有查询都能落到单张小表，性能提升很大。
• 备份、恢复、索引维护都更轻松。

缺点：

• 跨分片查询变麻烦了，要么应用端自己聚合，要么中间层合并结果。
• 分片规则一旦定下，很难改。
• 双写/迁移的时候成本很高。

---

2.3 History 表归档

还有一种相对“温和”的方式，就是做一张 history 表，把历史数据归档过去，然后必要的时候通过 UNION 查询最新表与历史表。

比如用户交易记录：

• 当前交易表保存最近 3 个月的数据
• 超过 3 个月的数据搬到 history 表
• 查询时，如果需要查历史，就执行：

sql

SELECT * FROM trade_current WHERE create_time >= '2025-05-01' UNION ALL SELECT * FROM trade_history WHERE create_time < '2025-05-01';

好处：

• 不影响当前活跃业务的数据读写速度
• 历史数据单独存储，不会拖慢核心链路
• 回溯时仍可查询

我自己觉得这个办法适合对历史数据访问很少，但又不能完全删掉的场景。而且实现起来比分库分表要简单不少，风险也小。

---

3. 索引优化：别光想着“多多益善”

刚开始接触索引的时候，我也犯过“无脑加索引”的毛病。每次遇到慢 SQL 就想着加一个索引解决，结果后来发现问题更多了：

• 索引多了会导致 写入性能下降，因为每次 INSERT/UPDATE 都要维护所有相关索引。
• 过多的索引占据大量磁盘和内存缓存空间（InnoDB Buffer Pool 会被挤爆）。
• 有些场景下 MySQL 优化器可能会错误选择不合适的索引，反而让 SQL 更慢。

我印象最深的一次，是因为频繁加索引，导致某张表的热点查询结果直接落到了 临时内存表（内存不够就落到磁盘临时表），性能一下子雪崩，被 DBA 当场训了一顿。当时才明白，加索引不是越多越好，一定要针对业务场景分析：

加索引前需要考虑：

1. 查询条件是否高选择性？如果过滤效果差，加索引没太大意义。
2. 是否覆盖最常用的查询模式（覆盖索引＞普通索引）。
3. 联合索引的顺序是否符合最常见的 where 条件使用习惯。
4. 是否和已有索引重复或冗余。

---

4. 一些具体的小技巧

除了上述的大方案，还有一些我实际总结的小技巧：

1. 控制单表行数 + 周期归档
   控制在 500w~1000w 行以内是比较稳妥的，超过这个区间性能明显会下降。

2. 读写分离
   大表在高并发情况下尤其需要主从架构，把查询分流到从库，可以显著降低主库压力。

3. **尽量避免 SELECT ***
   在大表面前，“我要这么多字段干嘛”一定要问自己，不必要的字段不要查。

4. 统计数据单独维护
   有些分页统计类功能可以提前计算好存在独立的小表里，而不是实时 COUNT(*) 整个大表。

5. 批量操作代替单条循环
   DELETE/UPDATE 大数据时尽量用 limit 分批提交，而不是一口气搞掉几百万行。

6. 冷热数据分离
   像用户活跃度、访问日志这种冷热差异明显的数据，可以单独放不同表甚至不同库里。

---

5. 我的反思

现在回想起自己第一次对大表下手，那种一看到慢 SQL 就“加索引”、看到数据量大会慌、直接想全量删除再导出的心态，其实就是典型的新手操作。经过一次次线上事件、优化和被训，我才慢慢学会冷静分析问题、权衡利弊。

我觉得，大数据表的治理，本质上是在时间成本、业务风险和性能之间找平衡点。
而且没有一种方案是万能的，你得结合业务需求、访问模式、团队能力去选择。比如范围分区可能更适合报表类业务，分库分表更适合强实时高并发写入，而 history 表归档则特别适合 “写多读少 + 历史可查” 的场景。

---

6. 总结

我的建议：

1. 别等到线上爆慢 SQL 才考虑拆表、归档；
2. 永远先分析再动手，不要盲目加索引；
3. 慢 SQL 日志开着，每周巡检一次；