轻松掌握mysql慢查询定位与优化知识点

在这里插入图片描述

1、利用工具定位慢sql

1、运维工具Skywalking

1、定位到慢接口

2、追踪慢sql的执行情况

2、利用MySQL的日志定位慢sql

在调式阶段才开启慢日志的查询，因为会损耗一些性能。

3、分析是否正确使用了索引

当我们已经定位到具体哪个sql较慢时，就可以开始分析导致sql慢的原因了。可以通过sql执行计划分析、

1、通过执行计划分析索引使用情况

通过执行计划可分析聚合查询、多表查询、表数据量过大查询。

重点关注如下参数：

• possible_keys可能使用到的索引

• key实际命中的索引

• key_len实际命中的索引占用的大小

• Extra 额外的优化建议

• type 表示sql的连接类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all。

  • NULL：表示这条sql执行中没有用到表，可以不用太关注
  • system：查询mysql内置的表，在开发中也用的不多
  • const: 根据主键查询的表
  • eq_ref：根据主键索引查询或者唯一索引查询，所以只能返回一条数据
  • ref：根据索引查询，可能是非唯一索引可返回多条数据。
  • range: 根据索引查询的，但是条件是范围查询。
  • index：走的是全索引查询，会去遍历整个索引树，在去检索数据，效率不高。
  • all：不走索引，全盘扫描数据，效率不高

1、是否命中索引判断

通过key实际命中的索引、key_len实际命中的索引占用的大小两者即可判断出是否命中索引。

2、使用了索引但有优化空间

Extra 额外的优化建议，如果出现了回表查询也就是Using index condition，则还有继续优化的空间

3、根据sql连接类型判断

如果连接类型为如下index和all，通常需要优化

index：走的是全索引查询，会去遍历整个索引树，在去检索数据，效率不高。

all：不走索引，全盘扫描数据，效率不高

2、索引失效情况分析解决

通过执行计划分析后，如果存在索引失效情况。则需要分析为何索引失效了。

1、违反最佳左前缀法则

如下：我们在tb_seller表建立了一个复合索引，字段顺序name、status、address;

如下查询时需要按顺序查询，都命中了索引；

如下违反了左前缀法则，导致没有命中索引

2、范围查询右边的列

如下图第二个sql，status字段使用了范围查询，导致address不走索引了，所以key_len长度值只用309。只有name和status才走了索引。

3、索引列使用函数

如下图：索引列使用了substring导致索引失效了。

4、索引列使用类型转换

5、模糊查询的%在前面

如下图：如果模糊查询时%放在前面，会导致索引失效。

4、分析超大数据分页问题优化

1、通过覆盖索引优化

先通过表中的id进行分页查询，就能筛选出id集合。在通过id集合和原来的表做关联查询。即可得到结果。

提升了4秒。

5、分析表设计方面优化

6、分析使用union all 代替union

union会将重复的数据去重，union all 不会。所以union all 少了过滤操作，自然效率就高。

7、分析使用内连代替左连和右连

内连inner join会自动对两个表进行优化。

8、分析使用读写分离