记一次Mysql慢SQL优化过程 3周前

缘起

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

select
    *
from
    test_table1 e
join test_table2 ebp on
    ebp.event_id = e.id
    and 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
        *
    from
        test_table3 p
    left join test_table4 a on
        p.attr_id = a.id
    where
        p.is_deleted = 'N'
        and a.is_deleted = 'N' ) tcp on
    ebp.buried_point_type = tcp.buried_point_type

过程

先使用EXPLAN看下: 记一次Mysql慢SQL优化过程 我们来回顾下这几个字段代表的意义:

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却没有索引 记一次Mysql慢SQL优化过程 这还怎么玩?没有索引大概就是全表扫描的原因了。再看另一个typeALL的表,是一样的,也是只有一个主键建了索引,p.attr_id = a.id中的attr_id根本没索引。把上面两个字段加上索引试下。 记一次Mysql慢SQL优化过程 可以看到ebp这个表的type已经不是ALL了,但是p还是ALL,p是ALL的原因应该是attr_id虽然加了索引,但是走这个索引的话要全量回表去取所有字段,就不如直接全量扫描更快了,起码省略了扫描索引并回表操作了。如果取得不是全部字段,可以考虑使用覆盖索引优化

总结

讲道理的话,连结查询的字段一定是要加索引的,这个表因为历史原因没有加,最开始我感觉这玩意儿正常人都会加吧,结果就是没有,可能当时设计人员认为没有很多数据吧。历史问题就不追究了,主要是要知道怎么去定位问题和解决问题。

一只糖糖糖糖
好好学习,天天向上。
5
发布数
2
关注者
6259
累计阅读

热门教程文档

C++
73小节
C
14小节
Gin
17小节
Rust
84小节
10.x
88小节