慢 SQL 定位与优化
- MySQL
- 2022-07-14
- 153热度
- 0评论
导航
0 基本思路
基本思路如下:
- 根据慢日志定位慢查询 sql
- 使用 explain 等工具分析 sql
- 修改 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