MySQL慢查询原因 ｜ 青训营笔记

这是我参与「第五届青训营 」伴学笔记创作活动的第 10 天

今天学习了mysql在慢查询的排查流程以及具体的常见慢查询的种类

• 慢查询优化思路

  • 慢查询日志记录慢SQL
  • explain分析SQL的执行计划
  • profile 分析执行耗时
  • Optimizer Trace分析详情
  • 确定问题并采用相应的措施

• type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
  • const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
  • eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
  • ref : 常用于非主键和唯一索引扫描。
  • ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
  • index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
  • unique_subquery：类似于eq_ref，条件用了in子查询
  • index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
  • range：常用于范围查询，比如：between ... and 或 In 等操作
  • index：全索引扫描
  • ALL：全表扫描

• 常见慢查询的12个原因

  • 隐式转换

    • 使用了字符串类型的索引，但是传递的是数值，则系统将数值类型的数看做是隐式转换不走索引

  • 不符合最左匹配原则

  • 深分页问题

    • 由于limit的使用不当导致检索了大量信息
    • 可以使用标签记录法和延迟关联法解决

  • 使用查询的in中的元素过多

  • order by 走文件排序导致速度过慢（其实就是查询的暂存量过大导致需要借助磁盘，解决方法如下）

    • 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化order by语句。
    • 我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化；

  • 索引字段上使用（！= 或者 < >），索引可能失效

  • 索引字段上使用is null， is not null，索引可能失效

  • 当左右的数据使用的编码集不同时，该使用索引的反而不使用索引

  • 使用groupby不当导致占用了大量内存

    • group by使用不当，很容易就会产生慢SQL问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

      • 如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。
      • 如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间

  • delete + in子查询不走索引

• 原文链接：SQL优化思路+经典案例分析 - 掘金 (juejin.cn)

• 慢查询更慢的12个原因：盘点MySQL慢查询的12个原因 (qq.com)