一入职！就遇到MySQL亿级大表优化....

前段时间刚入职一家公司，入职就遇到了 MySQL 亿级大表优化这事!

图片来自 Pexels

背景

XX 实例(一主一从)xxx 告警中每天凌晨在报 SLA 报警，遇到L亿该报警的表优意思是存在一定的主从延迟。(若在此时发生主从切换，入职需要长时间才可以完成切换，遇到L亿要追延迟来保证主从数据的表优一致性)

XX 实例的慢查询数量最多(执行时间超过 1s 的 SQL 会被记录)，XX 应用那方每天晚上在做删除一个月前数据的入职任务。

分析

使用 pt-query-digest 工具分析最近一周的源码库遇到L亿 mysql-slow.log：

pt-query-digest --since=148h mysql-slow.log | less 

结果第一部分：

最近一个星期内，总共记录的表优慢查询执行花费时间为 25403s，最大的入职慢 SQL 执行时间为 266s，平均每个慢 SQL 执行时间 5s，遇到L亿平均扫描的表优行数为 1766 万。

结果第二部分：

select arrival_record 操作记录的入职慢查询数量最多有 4 万多次，平均响应时间为 4s，遇到L亿delete arrival_record 记录了 6 次，表优平均响应时间 258s。

select xxx_record 语句

select arrival_record 慢查询语句都类似于如下所示，香港云服务器where 语句中的参数字段是一样的，传入的参数值不一样：

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\G

select arrival_record 语句在 MySQL 中最多扫描的行数为 5600 万、平均扫描的行数为 172 万，推断由于扫描的行数多导致的执行时间长。

查看执行计划：

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\G;