MySQL 上亿数据表优化后，接口不卡了，系统也稳了

MySQL 上亿大表的优化，真不是一句“加个索引”就能搞定的事。

前段时间我刚好遇到一个项目，数据库每天都在凌晨哀嚎，报警一条接一条。一查日志，发现是主从延迟搞的鬼，而且每次报警的时间点都非常稳定——凌晨删数据那一波刚好开始，数据库扛不住。

说实话，这种情况你让我不重视都不行。尤其是这个库的数据表还特大，arrival_record 表的数据量已经突破 1 亿条了，表本身 48G，大表中的战斗机，操作起来要是没点章法，分分钟给你来个慢查询 Top1。

慢查询全靠“硬查”，看完我沉默了

为了弄清楚是哪条 SQL 把库干趴了，我先用了pt-query-digest把一周内的慢查询日志过了一遍。结果直接让我沉默。

慢 SQL 总共跑了2.5 万秒，平均每条慢查询要跑5 秒。最猛的那条跑了 266 秒，光是看着这个数字，我的 CPU 都开始冒烟了。

其中出现次数最多的，是一条 select 查询：

select count(*) from arrival_record

where product_id=26

and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00'

and receive_spend_ms>=0;

看着挺正常是不是？但问题来了，这张 arrival_record 表的索引设计有点一言难尽——

KEY IXFK_arrival_record (product_id,station_no,sequence,receive_time,arrival_time)

表面看起来五个字段的复合索引，但你再看一下那条慢 SQL：人家只用了product_id和receive_time，压根没 station_no 和 sequence，你这个复合索引用得上吗？

答案是：用不上。

按照最左前缀匹配原则，这条 SQL 只命中了索引的第一列product_id。可问题是，product_id 这个字段的基数太小，选择性极差，结果就导致 MySQL 必须去扫描几千万条数据才能给我答案。最惨的一次，直接扫了 5600 万行，效率感人。

explain 一下，说人话就是“扫爆内存”

为了更直观，我跑了下执行计划：

explain select count(*) from arrival_record

where product_id=26

and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00'

and receive_spend_ms>=0;

结果一出来，我真想帮 MySQL 按个暂停键。

rows: 32261320

filtered: 3.70

Extra: Using index condition; Using where

大哥你扫三千万行，然后还只留下不到 4%，你是来健身的吗？

tcpdump 抓包一看：更惨的还在后面

我不死心，干脆上了个狠活，直接用tcpdump抓了一波访问 arrival_record 表的 SQL 包。结果让我感受到了数据库的“社畜心酸”。

通过分析 where 条件，我发现大部分的 select 查询其实是：

where sequence='xxxx'

and product_id=xx

and station_no='xxx'

也就是说，经常查询的是 sequence、station_no 和 product_id，但索引里它们的顺序却是product_id,station_no,sequence。

这意味着什么？意味着实际使用中，索引根本就没命中顺序，MySQL 只能靠体力活顶着。

delete 一条老命，delete 一片天荒地老

select 已经够惨了，delete 更是要命。每晚凌晨要执行一个 delete 操作：

delete from arrival_record where receive_time < '2019-02-23'

说实话，这种“直接删历史”的操作我一看就不对劲。你连个 receive_time 索引都没有，就敢全表删？

我随手跑了个 explain：

type: ALL

key: NULL

rows: 109501508

Extra: Using where

全表扫描，上亿条数据，说真的，如果数据库能说话，它一定已经报警了：“我真不行了。”

这条 delete 的平均执行时间达到了262 秒，而且严重拖慢了主从同步，导致 Sentry 连续报 SLA 延迟警告。

真正的优化，从删掉那个索引开始

问题基本查清了，那就开始优化吧。

首先我把原来的复合索引IXFK_arrival_record给拆了，直接换成两个：

-- 查询用的

create index idx_product_id_sequence_station_no on arrival_record(product_id, sequence, station_no);

-- delete 用的

create index idx_receive_time on arrival_record(receive_time);

为什么这么建？

因为前面的tcpdump抓包里发现，实际查询更依赖于sequence和station_no，我们就要把它们提前放进索引；而 delete 是按 receive_time 来筛的，那就必须给 receive_time 单独建个索引。

另外，原来那破索引还跟外键绑定在一起了，不删掉外键压根删不掉索引，我是哭着执行drop foreign key的。

优化效果立竿见影

改完索引后我重新跑了一下那两条经典 SQL 的执行计划：

-- delete 计划

explain select count(*) from arrival_record where receive_time < '2019-03-10';

-- select 计划

explain select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00' and receive_spend_ms>=0;

这次终于用上了idx_receive_time索引，扫描行数瞬间从几千万下降到了 20 万以内，速度那是杠杠的！

原来 delete 一次得跑 260 多秒，优化完只要 77 秒，关键是主从同步再也没报警了，兄弟们都松了口气。

最后一击：小批量 delete

为了彻底解决 delete 带来的压力，我们把 delete 也做了分页优化：

-- 拿到要删除的最大主键 ID

SELECT MAX(id) INTO @need_delete_max_id

FROM arrival_record

WHERE receive_time < '2019-03-01';

-- 每次只删 20000 条

DELETE FROM arrival_record

WHERE id < @need_delete_max_id

LIMIT 20000;

然后在应用层做个循环，每次删完睡 0.5 秒，看row_count()是不是为 0，为 0 就退出循环。

说实话，这种小批量删除法，不仅减轻了主库压力，还让从库同步更顺畅，凌晨 Sentry 报警直接“消失”。

大表优化，不能只靠运气

到这一步，这个千万级大表终于安生了。

整个优化过程，其实就两件事：

看懂实际 SQL 的使用方式，不要盲目信任开发写的索引；

别让大操作一次全表扫描，小批量做才稳当。

MySQL 真不是说“我加个索引”就完事的活。我们是 Java 开发没错，但面对大表这种级别的问题，要是还指望 DBA 一键解决，那可真的是想太多了。

毕竟，代码写得再好，SQL 跑不动，也救不了你那凌晨狂响的报警器。

最后，我为大家打造了一份deepseek的入门到精通教程，完全免费：https://www.songshuhezi.com/deepseek