慢 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