获课：shanxueit.com/7182/

# 实战复盘：MySQL大表优化方案落地全过程经验分享

做后端开发的人，早晚都会遇到一张表大到撑不住的那一天。这张表可能是一张订单表、一张操作日志表，或者一张用户行为流水表。几百GB的数据塞在里面，业务查询慢得像蜗牛爬，索引加了又加，效果越来越不明显。年初我刚好经历了一次订单主表的大表优化，从问题暴露、方案选型到最终落地，前后折腾了将近一个月。这篇文章不讲代码，只还原当时做决策的过程和思考。

## 问题是怎么暴露的

说起来事情来得不突然，但一直没下决心处理。那张订单表用了三年多，数据量接近1.5亿行，磁盘占用超过200GB。平时的简单查询还能扛，问题出在每个月月初做财务报表的时候——需要扫描近三个月的历史订单做汇总，一条SQL跑十几分钟是常态，最夸张的一次跑了四十多分钟，直接导致业务侧的定时任务超时告警。

更麻烦的是，这张表有几个常用查询字段的组合索引已经加到了极限，再往上加索引，写入性能肉眼可见地下降。每天晚上做备份的时候，备份窗口越来越长，开始挤压正常业务时间。站在这个节点回头看，已经是在"还能用但很不舒服"的状态里拖了太久，再不动就真要出生产事故了。

## 方案选型时的纠结和权衡

大表优化无外乎几条路：垂直拆分、水平分表、冷热数据分离、归档历史数据、或者换分布式数据库。每条路看着都能走，但每条路都有代价，当时团队内部吵了好几轮。

**垂直拆分被最早否决**，因为这张订单表本身字段不算特别多，而且几乎所有字段都有被查询的可能，强行拆表反而会引入跨表join的复杂度和性能损耗。

**水平分表讨论了很久**。按订单ID取模分库分表是最规范的方案，但问题是现有业务代码里大量查询不带上订单ID，是按用户ID、按商户ID、按时间范围查的。如果分表键选订单ID，那这些查询就得改底层逻辑，甚至要引入额外索引表来维护映射关系，改造成本超出了我们当时能承受的范围。分库分表是终极方案没错，但在这个阶段，它太重了。

**冷热数据分离**是反复讨论后确定的方向。订单有一个天然的业务属性：绝大多数的查询需求集中在最近三个月的订单上，超过半年的历史订单除了出财报和审计的时候几乎没人碰。既然访问模式如此鲜明，把热数据和冷数据物理分开，是一个性价比极高的方案。

## 落地过程里那些没踩但差点踩的坑

方向定了，执行依然不轻松。我挑几个印象最深的事情说。

第一个是**归档窗口的选择**。我们最初想在业务低峰期的凌晨两点做数据迁移，把超过六个月的历史订单迁到归档表里。结果第一次试跑的时候发现，扫描六个月的订单需要加一个范围锁，虽然我们用了分批小事务的方式，但还是对主表的写入产生了轻微影响。后来调整了策略，把历史订单按照月份分批归档，每个月的数据单独作为一个批次迁移，每个批次用独立的小事务提交。这样单个事务的持锁时间控制在毫秒级别，业务侧完全无感。

第二个是**索引的取舍**。迁移数据到归档表之后，一开始我们给归档表建了和主表完全一样的索引，结果发现归档表的写入速度慢得离谱，因为每次迁移一批数据都要维护七八个索引。后来我们把归档表的索引精简到只保留查询必用的两个核心索引，其他全部砍掉。毕竟归档表是只读的，查询频次低，不需要用写入性能去交换查询效率。

第三个是**应用层的路由适配**。最头疼的部分其实是应用代码怎么知道一笔订单该去主表查还是归档表查。最初的方案是在业务层根据订单的创建时间做判断，但发现很多查询跨越了新旧边界。比如统计过去一年的订单总额，如果应用层强制路由，就必须做两次查询再汇总，代码改动量大不说，还容易出bug。最终的做法是在数据库层做了一个视图，视图里用union all合并了主表和归档表，应用层完全不知道底层分了两张表，所有查询还是走原来的表名，改造成本降到最低。

## 效果和一点反思

最终落地后的效果：主表数据从1.5亿行降到约3000万行，占用的存储空间下降了75%。主表的查询响应时间从平均几秒降到了毫秒级别，月初的财务报表任务从四十多分钟压缩到了三分钟以内。归档表虽然查询慢一些，但半年才有人查一次，完全在可接受范围内。

回头看这次大表优化，我最深的感触是：**不要觉得分库分表才是终极方案，冷热分离在很多业务场景里性价比其实更高。** 它不需要改动业务架构，不需要重写数据访问层，核心就是做一次数据搬家的事情。当然，这个方案的前提是你的业务确实存在明显的时间冷热特征，这个判断需要提前做好。

还有一个感悟是：大表优化这件事，拖得越久代价越大。那张订单表一年前就超过五千万行，那时候做归档的话，各方面压力都会小很多。拖到1.5亿才动手，光是在生产环境做数据迁移的时间窗口就比预想的长了一倍。如果你手头也有一张正在变大的表，别等到它疼得你睡不着觉再动手，那时候代价已经翻了好几倍了。