江苏龙网

  1. 首页 > 人文 > >

「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！


背景 XX实例（一主一从）xxx告警中每天凌晨在报SLA报警 ， 该报警的意思是存在一定的主从延迟（若在此时发生主从切换 ， 需要长时间才可以完成切换 ， 要追延迟来保证主从数据的一致性）
XX实例的慢查询数量最多（执行时间超过1s的sql会被记录） ， XX应用那方每天晚上在做删除一个月前数据的任务
分析 使用pt-query-digest工具分析最近一周的mysql-slow.logpt-query-digest --since=148h mysql-slow.log | less结果第一部分
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
最近一个星期内 ， 总共记录的慢查询执行花费时间为25403s ， 最大的慢sql执行时间为266s ， 平均每个慢sql执行时间5s ， 平均扫描的行数为1766万
结果第二部分
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
select arrival_record操作记录的慢查询数量最多有4万多次 ， 平均响应时间为4s ， 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
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
select arrival_record 语句在mysql中最多扫描的行数为5600万、平均扫描的行数为172万 ， 推断由于扫描的行数多导致的执行时间长
查看执行计划
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
用到了索引IXFK_arrival_record ， 但预计扫描的行数很多有3000多w行

「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
现在已经知道了在慢查询中记录的select arrival_record where语句传入的参数字段有 product_id ， receive_time ， receive_spend_ms ， 还想知道对该表的访问有没有通过其它字段来过滤了？
神器tcpdump出场的时候到了
使用tcpdump抓包一段时间对该表的select语句
tcpdump -i bond0 -s 0 -l -w - dst port 3316 | strings | grep select | egrep -i 'arrival_record' >/tmp/select_arri.log 获取select 语句中from 后面的where条件语句
IFS_OLD=$IFSIFS=$'\n'for i in `cat /tmp/select_arri.log `;do echo ${i#*'from'}; done | lessIFS=$IFS_OLD
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
综上所示 ， 优化方法为 ， 删除复合索引IXFK_arrival_record ， 建立复合索引idx_sequence_station_no_product_id ， 并建立单独索引indx_receive_time
delete xxx_record语句
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！
本文插图
该delete操作平均扫描行数为1.1亿行 ， 平均执行时间是262s
delete语句如下所示 ， 每次记录的慢查询传入的参数值不一样
delete from arrival_record where receive_time < STR_TO_DATE('2019-02-23', '%Y-%m-%d')\G
执行计划
「MySQL」一开工，就遇到上亿(MySQL)大表的优化，我的天啊！


推荐阅读


上一篇：『长安奔奔』重庆:2020长安出行新车型即将上线

下一篇：我的第一部5G手机@外媒公布手机性能排行榜，三星勇夺第一，华为成最大输家