记一次Mysql慢SQL优化过程

缘起

最近有个同事让我看看一个测试环境的SQL，因为这个SQL执行了几十秒，导致接口超时了。
sql为(里面表名已经使用test_table开头的表名脱敏，返回的字段使用*脱敏，别名未修改)：

select*
fromtest_table1 e
join test_table2 ebp onebp.event_id = e.idand ebp.is_deleted = 'N'and e.id in (1260,1294,1297,1300,1520,1727,1730,1731,1820,1897,1898,1899,1900,2542,2543,2920,3137,3140,4843,4858,4942,5014,5077,5324,5337,5536,5550,5556,5557,5558,5560,5563,5564,5567,5568,5797,5798,5845,6645)and e.is_deleted = 'N'and e.is_display = '1'
join (select*fromtest_table3 pleft join test_table4 a onp.attr_id = a.idwherep.is_deleted = 'N'and a.is_deleted = 'N' ) tcp onebp.buried_point_type = tcp.buried_point_type

过程

先使用EXPLAN看下：
在这里插入图片描述
我们来回顾下这几个字段代表的意义：

type 访问类型以下类型性能从差到优：
all：全表扫描，这个代价是最大的，性能最差的，有很大的优化空间
index：全索引扫描，扫描所有索引，比全表扫描快一点
range:：范围查询，这个范围一定是用了索引的范围，大多出现在>,<,>=,<=,BWTWEEN这种带范围的查询
ref：结果是使用了索引的（非主键索引，非唯一索引），我们知道索引是有序的，即使是这个索引有重复字段，也会在一个小范围内并且连续，不会全表扫描
ref_eq：使用了索引结果只有一个，出现在查询用到了唯一索引或主键索引的场景
const：当前查询使用了主键索引

Extra 额外访问类型：
using where
使用where条件进行了数据过滤，一般遇到这个很难判定性能好坏，需要和type一起判断是否需要优化
using index
使用了索引，一般来讲结果都在一颗索引树上查到（无需回表），性能较好，一般不需要优化
using index condition
使用了索引，但是需要回表，此时优化一般是使用覆盖索引
using filesort
使用了临时文件进行排序，一般出现在order by的排序中由于排序字段没加索引导致全部数据排序，并且没法再内存中完成排序，性能较差，一般会在排序字段上加索引，避免全部排序
using temporary
使用了临时表，性能较差，一般出现在``group by和order by`一起的时候，分组字段和排序字段不一致导致需要中渐变暂存结果，优化方案一般是加索引
using join buffer (Block Nested Loop)
需要循环计算，一般出现在两个表join操作但是join的字段没有加索引导致，一般优化方案是在join的字段上加索引

possible keys 可能使用的索引

key 实际使用到的索引

ref 查询中使用到的与其它表的关联字段，外键等

以上引用来自我的其它博客 mysql慢日志分析，执行计划

可以看到type有两个ALL，这个大概就是最慢的两个点了，去看下为啥会全表扫描，发现这个表只有一个主键id是主键索引，但是表连接ebp.event_id = e.id中的ebp.event_id却没有索引
在这里插入图片描述
这还怎么玩？没有索引大概就是全表扫描的原因了。再看另一个type是ALL的表，是一样的，也是只有一个主键建了索引，p.attr_id = a.id中的attr_id根本没索引。把上面两个字段加上索引试下。

可以看到ebp这个表的type已经不是ALL了，但是p还是ALL，p是ALL的原因应该是attr_id虽然加了索引，但是走这个索引的话要全量回表去取所有字段，就不如直接全量扫描更快了，起码省略了扫描索引并回表操作了。如果取得不是全部字段，可以考虑使用覆盖索引优化。