如何定位并优化慢SQL查询

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

使用SQL命令查询慢日志设置

1
SHOW VARIABLES LIKE '%QUERY%';
截屏2021-01-05 下午4.17.37

最后两个变量slow_query_log和slow_query_log_file即和慢日志有关，第一个变量控制是否开启慢日志，第二个变量定义慢日志存储位置。

超过long_query_time时间等查询会被记录在慢日志中。

开启慢查询命令

打开慢查询：

1
SET GLOBAL slow_query_log = ON;

设置慢查询时间：

1
SET GLOBAL long_query_time = 1;

直接使用命令设置数据库重启后会还原，想要永久需要到my.cnf配置文件中去设置。

使用sql命令查询慢日志状态

1
SHOW STATUS like '%slow_queries%';
截屏2021-01-05 下午4.21.27

Slow_queries显示慢查询记录条数。

2. 使用explain等工具分析sql

在命令前加explian即可分析

例如：

1
EXPLAIN SELECT `name` FROM person_info_large ORDER BY name desc;
截屏2021-01-05 下午5.08.34

其中id表示执行顺序，对于复合查询来说id越大越先执行。

  1. type表示mysql找到需要的数据行的方式，方式性能从最优到最差如图所示：

    截屏2021-01-05 下午4.49.55

    index和all表明走的是全表扫描，故非常慢。

  2. Extra：

    截屏2021-01-05 下午4.52.49

3. 修改sql或者尽量让sql走索引

改sql

例如命令：

1
SELECT `name` FROM person_info_large ORDER BY name desc;

由于name没有走索引，故可以把name改成count等有索引的项目

截屏2021-01-05 下午5.12.03

加索引

1
alter table person_info_large add index idx_name(name);