MySQL 真的是一门“老少皆宜”的数据库，用得久了就知道，它最大的优点不是性能，而是皮实耐用。但有时候，这种“皮实”也可能给你带来灾难——尤其当你面对的是一个上亿条数据的大表。
前段时间，我们项目中的一个 MySQL 实例每天凌晨都会报 SLA 告警，说主从延迟严重。查了一圈，发现罪魁祸首竟然是一个自动清理历史数据的定时任务。而这个任务每天都在硬刚上亿条数据的 arrival_record 表，搞得 MySQL 主从都喘不过气。
你没听错，每晚执行的 delete 操作让我们的数据库差点“原地升天”。
慢查询里的“罪魁祸首”
用 pt-query-digest 把一周的 mysql-slow.log 啪一扔，输出结果让我有点上头。慢查询总耗时 25403 秒，最慢的 SQL 执行了 266 秒，平均每条慢查询执行了 5 秒，还平均扫描了 1766 万行数据。
其中，select 和 delete 操作针对的都是一个叫 arrival_record 的表。
我们抓了其中最频繁的 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;
但别被这“平平无奇”的 SQL 语句骗了，它单次扫描的数据有时候能飙到 5600 万行，堪比扫一遍某些大厂的员工花名册。
执行计划一看，嘿，好家伙：
id: 1
select_type: SIMPLE
table: arrival_record
type: ref
key: IXFK_arrival_record
rows: 32261320
filtered: 3.70
Extra: Using index condition; Using where
扫了三千多万行！
那这个表长什么样？我也去看了眼表结构，果然没让我失望：
KEY IXFK_arrival_record (product_id, station_no, sequence, receive_time, arrival_time)
一个五字段的复合索引，但 select 的条件只有 product_id 和 receive_time，连 station_no 都没带，那复合索引自然也就派不上太大用场了。
而且 product_id 这字段选择性巨差，基数低得可怜，唯一能救命的 receive_time 又是排在索引的第四位，你说它能快到哪里去？
不甘心就只能背锅，我们决定“动刀”
既然复合索引没用上，我们直接重新设计索引结构。
抓了一波 select 语句的真实 where 条件，发现很多都是这样：
where sequence='2019-03-27 08:40'
and product_id=24
and station_no='V4205'
OK，这说明 sequence 和 station_no 是高频字段，而且这些字段组合起来的过滤能力还不错。于是我们决定重建复合索引，从原来的：
(product_id, station_no, sequence, receive_time, arrival_time)
变成：
idx_product_id_sequence_station_no (product_id, sequence, station_no)
并且给 receive_time 单独建一个索引：
idx_receive_time (receive_time)
重建索引之前当然不能贸然直接上生产，搞坏了领导第一个找你喝茶。
我们先用 mydumper 把生产表备份出来，压缩之后从 48G 缩到了 1.2G，我真是头一次佩服这工具的压缩能力。然后在测试环境还原、删除旧索引、建新索引，一顿操作猛如虎。
使用 online ddl 和 pt-osc 分别测试重建索引，最终发现 online ddl 更快，比 pt-osc 节省了将近一半时间，给我们上线打了个强心针。
再看执行计划，一秒钟感受到优化的魅力
加了新的索引之后，我立刻跑了同样的 SQL：
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;
结果：
key: idx_receive_time
rows: 291448
Extra: Using index condition; Using where
扫描行数直接从三千万降到二十九万！
这优化效果，简直是脱胎换骨，比中年男人做完体检后突然决定每天跑五公里还要励志。
delete 语句的另一条命：小批量删除
虽然 select 优化了，但 delete 依旧像个憨憨，每晚那几十秒的大删除一来，主从立马开始喘气。我们尝试给 receive_time 加上索引，效果确实好了一些，但单次删除的记录数一多，执行时间还是猛涨。
后来痛定思痛，我们决定彻底改变删除策略，用“小步快跑”的方式解决大问题。
从之前：
delete from arrival_record where receive_time '2019-03-01';
变成现在：
SELECT MAX(id) INTO @need_delete_max_id
FROM arrival_record
WHERE receive_time '2019-03-01';
DELETE FROM arrival_record
WHERE id < @need_delete_max_id
LIMIT
20000;
每次删 20000 条，删完等 0.5 秒，然后继续删下一批。应用层判断 row_count 是否为 0，是就停，不是就继续删。现在的效果是：
这才是对大表 delete 最优雅的打开方式。
一点体会
整个过程干下来，我最大的体会就是：MySQL 的问题，索引八成能解决，剩下两成交给“削峰填谷”策略。
而作为开发，我们不能一味依赖 DBA 和运维，尤其是像 Spring Boot + MyBatis Plus 这种 ORM 框架，很容易一不小心就把全表扫给写进代码里。
记得我改完那段 SQL 后，专门在代码里加了点注释，提醒后人一句：
// ❗❗❗ 慎重修改该 SQL，数据量非常大，依赖 idx_receive_time 索引！
// 改了别忘去 explain 一下！！！
写代码就像打牌，能赢的前提是你知道手里的牌是什么，哪些好打，哪些别碰。MySQL 里也是一样，写 SQL 之前最好心里清楚索引长什么样。
再啰嗦两句
如果你也是在维护一个数据量爆炸的系统，建议记住这几条：
- 慢查询日志一定要开，
pt-query-digest 会是你最好的朋友。 - 复合索引不是越多越好，别让框架自动建了你就用，得看条件能不能覆盖。
-
delete 别一次性梭哈，你不是在双十一抢手机，数据库也需要喘气。
- 主从延迟=命门，主库出事切换到从库，延迟一大，切换失败，掉线出事故分分钟。
写到这里我都忍不住给自己倒杯咖啡，毕竟这场“大表优化之战”，赢得实在不容易。
