慢 SQL 定位与优化

0 基本思路

基本思路如下:

  1. 根据慢日志定位慢查询 sql
  2. 使用 explain 等工具分析 sql
  3. 修改 sql,尽量让 sql 走索引

下面依次展开。

1 根据慢日志定位慢查询 sql

先来认识一下 mysql 中的几个变量:

  • long_query_time:定义了查询时间超过多少秒,即认为属于慢 sql
  • slow_query_log:一个开关,控制是否记录慢日志
  • slow_query_log_file:慢日志文件的具体路径

这几个变量可以用命令 show variables like '%query%' 查看,且都可以在 mysql.ini 中进行配置。

我们需要把 show_query_log 设置为 ON,long_query_time 根据需要设定(例如 1s)。

(另外,show status like '%slow_queries%' 可以看到慢查询的数量。)

前面的变量配置好之后,当我们执行慢 sql 时,就会被记录下来。至此,完成慢 sql 的定位。

2 使用 explain 分析慢 sql

接下来,使用 explain 对查询语句进行分析。使用方式,就是在原来的 sql 前面,加上 explain 关键字。

例如:

其中,有两列非常重要:

  • type:代表 MySQL 找到数据行的方式。执行效率从高到低的排序为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。其中,排在最后的 index 和 all,都是全表扫描,一个走索引,一个没有走索引。
  • Extra:当出现以下两项时,意味着 MySQL 根本不能使用索引,效率会受到重大影响,应尽可能优化。
    • Using filesort。表示 MySQL 会对结果使用一个外部排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作成为“文件排序”。
    • Using temporary。表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

3 修改 sql,尽量走索引

有两个思路。一是修改 sql 本身,让它使用索引字段。二是不修改 sql,而是给相关字段添加索引。

继续刚才这个例子,我们给 name 字段加上索引:

alter table person_info_large add index idx_name(name);

加完索引之后,explain 的结果如下:

到此,优化完成。


参考资料:

https://coding.imooc.com/lesson/303.html