mysql|MySQL性能分析工具——如何快速定位SQL执行慢的原因（） explain|慢SQL|mysql

本文目录

一、前言
二、慢查询日志定位执行慢的 SQL
- 2.1.开启慢查询日志
- 2.2.设置慢查询的时间阈值
- 2.3. 慢查询日志介绍
- - 模拟慢查询
  - 慢查询日志
- 2.4.日志分析工具mysqldumpslow
三、查询优化神器EXPLAIN命令
- 3.1. id字段
- 3.2. select_type字段
- 3.3. table字段
- 3.4. partitions字段
- 3.5. type字段
- 3.6. possible_keys字段
- 3.7. key字段
- 3.8. rows字段
- 3.9. key_len字段
- 3.10. Extra字段
- 总结
四、SQL性能分析show profile命令
五、总结

一、前言我们在做SQL优化的时候，应该从哪几方面定位SQL执行慢的原因呢？是索引设计的问题？参数配置的问题？还是需要扩容了呢？
本文介绍了比较常用的三种工具来定位和分析慢SQL，本文主要内容如下：

通过慢查询日志定位执行慢的 SQL；
使用 EXPLAIN 分析该 SQL 语句是否使用到了索引，以及具体的数据表访问方式；
使用SHOW PROFILE 进一步分析SQL的每一步执行时间以及CPU、IO等资源使用情况。

二、慢查询日志定位执行慢的 SQL 慢查询日志是MySQL提供的一种日志记录，它用来记录所有执行时间超过long_query_time参数值的SQL。long_query_time的默认值为10秒，默认情况下执行超过10s的SQL语句，会被记录到慢查询日志中。
2.1.开启慢查询日志在使用前，我们需要先看下慢查询是否已经开启，使用下面这条命令查看是否开启：

show variables like '%slow_query_log%';

文章图片

slow_query_log=OFF，表示未开启。 slow_query_log=ON表示慢查询已经开启，如果未开启，通过以下命令开启：

set global slow_query_log='ON';

开启后，使用show variables like '%slow_query_log%'再来看下是否开启

文章图片

【mysql|MySQL性能分析工具——如何快速定位SQL执行慢的原因（）】slow_query_log_file 是慢查询日志文件目录。
Mysql支持文件和数据库表两种日志存储方式，默认将日志存储在文件中，使用下面这条命令查看存储方式：

show variables like '%log_output%';

文章图片

可以修改为存储到数据库表，这样日志信息就会被写入到mysql.slow_log表中

set global log_output='TABLE';

文章图片

MySQL数据库同时支持两种日志存储方式，配置的时候以逗号隔开即可

set global log_output='TABLE,FILE';

日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
2.2.设置慢查询的时间阈值 long_query_time的默认值为10秒，通过以下命令可以查看慢查询的时间阈值

show variables like '%long_query_time%';

文章图片

如果我们想修改long_query_time参数值，通过以下命令即可，假如我们把long_query_time值设置为1。

set global long_query_time = 1;

注意：修改完再次执行show variables like ‘%long_query_time%’，发现还是默认的10S，是不是没有修改成功呢？其实已经修改成功了，此时只需要关闭当前连接，再重新连接就可以了。

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，需要修改my.cnf配置文件，配置文件如下：

[mysqld] slow_query_log = 1# 开启慢查询 slow_query_log_file = /data/mysql/logs/slow.log# 慢查询日期路径 long_query_time = 1# 慢查询时间阈值 log_timestamps = SYSTEM log_output = FILE

2.3. 慢查询日志介绍模拟慢查询
如果自己mysql上没有慢查询，可以通过sleep(N)函数来模拟慢查询操作，比如下面这条模拟慢查询的语句

SELECT *, sleep(3) FROM tb_user WHERE id<4

注意：sleep(N)函数表示，每返回一行数据经过WHERE条件判断后，都会触发Sleep函数，比如上面的SQL语句，返回3条数据，每条阻塞3秒，查询时间总共是9秒。

文章图片

慢查询日志
有了慢查询日志，可以使用以下命令获取有多少条日志

show global status like '%Slow_queries%';

文章图片

可以看到现在有两条慢查询，接下来我们来看下慢查询日志具体记录哪些内容

# Time: 2022-01-22T12:56:00.070149Z # User@Host: root[root] @ localhost []Id:8 # Query_time: 4.006927Lock_time: 0.000074 Rows_sent: 4Rows_examined: 4 SET timestamp=1642856160; SELECT *, sleep(1) FROM tb_user WHERE id<5;

第一行记录的是该条 SQL 语句执行的时间；
第二行记录的是执行该SQL语句的用户和 IP 以及链接 id；
第三行的几个字段解释如下：

Query_time: duration 语句执行时间，以秒为单位。
Lock_time: duration 获取锁的时间(以秒为单位)。
Rows_sent: 发送给 Client 端的行数。
Rows_examined: 服务器层检查的行数(不计算存储引擎内部的任何处理)

第四行记录是此SQL语句执行时候的时间戳；
第五行就是具体的慢SQL。也是我们需要优化的SQL。
2.4.日志分析工具mysqldumpslow MySQL 提供了 mysqldumpslow 工具统计慢查询日志。mysqldumpslow 命令的参数如下：

-s：采用 order 排序的方式，排序方式可以有以下几种。分别是 c（访问次数）、t（查询时间）、l（锁定时间）、r（返回记录）、ac（平均查询次数）、al（平均锁定时间）、ar（平均返回记录数）和 at（平均查询时间）。其中 at 为默认排序方式。
-t：返回前 N 条数据。
-g：后面可以是正则表达式，对大小写不敏感。

比如我们想要按照查询时间排序，查看前3条 SQL 语句，这样写即可：

mysqldumpslow -s r -t 3 /var/lib/mysql/a7bb95cee15a-slow.log

得到访问次数最多的5个SQL

mysqldumpslow -s c -t 5/var/lib/mysql/a7bb95cee15a-slow.log

得到按照时间排序的前5条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join”/var/lib/mysql/a7bb95cee15a-slow.log

另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 5/var/lib/mysql/a7bb95cee15a-slow.log | more

三、查询优化神器EXPLAIN命令 MySQL 提供的 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，开发人员可以根据输出的信息对SQL进行有针对性的优化。
EXPLAIN 可以帮助我们了解数据表的读取4,l; ’]m np[ohfg]'顺序、SELECT 子句的类型、数据表的访问类型、可使用的索引、实际使用的索引、使用的索引长度、上一个表的连接匹配条件、被优化器查询的行的数量olhb.; /及额外的信息（比如是否使用了外部排序，是否使用了临时表等）等。
EXPLAIN使用方式如下，EXPLAIN 命令的输出内容如下：

文章图片

下面对EXPLAIN 命令的输出各个字段进行说明
3.1. id字段 select查询的序列号，表示查询中执行select子句或操作表的顺序。
SQL 执行的顺序是根据 id 从大到小执行的，也就是 id 越大越先执行，当 id 相同时，从上到下执行。
比如下面这个SQL语句id一样，执行顺序按照从上到下执行。

EXPLAIN SELECT u.name,o.title FROMtb_user u JOIN tb_order o ON u.id=o.user_id

文章图片

下面的这条SQL语句，可以看到子查询的id为2，所以这条语句先执行是子查询

EXPLAIN SELECT * FROM tb_order WHERE user_id = (SELECT id FROM tb_user WHERE id=1)

文章图片

3.2. select_type字段表示查询的类型。常用的值如下表：

select_type字段值	说明
SIMPLE	表示查询语句不包含子查询或union
PRIMARY	表示此查询是最外层的查询
UNION	表示此查询是UNION的第二个或后续的查询
DEPENDENT UNION	UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT	UNION的结果
SUBQUERY	SELECT子查询语句

平时我们最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。
3.3. table字段输出行所引用的表的名称。
3.4. partitions字段该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
3.5. type字段 type字段表示存储引擎查询数据时采用的方式。这个也是我们平时做SQL优化重点关注的信息，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。type 常用属性值如下表所示：

type字段值	说明
`ALL`	全表扫描，性能最差。
`index`	表示基于索引的全表扫描，先扫描索引再扫描全表数据。
`range`	表示使用索引范围查询。使用>、>=、<、<=、in等等。
`ref`	表示采用了非唯一索引，或者是唯一索引的非唯一性前缀
`eq_ref`	一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
`const`	表最多有一个匹配行，因为只有一行，所以这一行中列的值可以被优化器视为常量
`system`	system 类型一般用于 `MyISAM` 或 `Memory` 表，属于 `const` 类型的特例

上面的这些type字段值，执行效率从上至下依次增强。效率从低到高依次为 all < index < range < index_merge < ref < eq_ref < const/system。
下面对type字段常用的类型做详细的说明

all 是最坏的情况，因为采用了全表扫描的方式。
index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。如果我们在 extra 列中看到 Using index，说明采用了索引覆盖，也就是索引可以覆盖所需的 SELECT 字段，就不需要进行回表，这样就减少了数据查找的开销。
下面通过一个例子，来对index进行说明：
比如我们有个tb_user表，对name和phone字段建立联合索引：

CREATE INDEX name_phone_index on tb_user(`name`,phone)

然后对数据表中的 name、phone 字段进行查询，EXPLAIN 执行计划如下：

文章图片

从EXPLAIN执行结果中可以看到，这条SQL使用了index的方式，key列采用了联合索引（name_phone_index），进行了全表扫描。Extral 列为 Using index，告诉我们索引可以覆盖 SELECT 中的字段，也就不需要回表查询了。

range 表示采用了索引范围扫描。比如下面这条SQL语句

select * from tb_user where id>=1 and id<=5;

文章图片

从range这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀。
比如我们对tb_order表user_id字段创建索引

CREATE INDEX user_id_index on tb_order(user_id)

然后查询user_id等于1的订单信息，EXPLAIN 执行计划如下：

EXPLAIN SELECT title,order_datetime FROM tb_order WHERE user_id=1

文章图片

这里 user_id 为普通索引，因此采用的访问类型是 ref，同时在 ref 列中显示 const，表示连接匹配条件是常量，用于索引列的查找。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。
比如我们对tb_order表和tb_user进行join关联查询

EXPLAIN SELECT o.title,o.order_datetime,u.name FROM tb_order o JOIN tb_useru ON o.user_id=u.id

EXPLAIN 执行计划如下：

文章图片

const类型表示我们使用了主键或者唯一索引（所有的部分）与常量值进行比较。
比如根据主键id查找某一个用户

文章图片

在做优化的时候，最好可以使用到 range 这一级别及以上的 type 访问方式，如果只使用到了 all 或者 index 这一级别的访问方式，我们可以从 SQL 语句和索引设计的角度上进行改进。
3.6. possible_keys字段表示查询时可能能使用到的索引。实际并不一定会真正使用。
3.7. key字段表示查询时真正使用到的索引，显示的是索引名称。
3.8. rows字段 MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
3.9. key_len字段表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
key_len的计算规则如下：

字符串类型
字符串长度跟字符集有关，常见编码长度：gbk=2、utf8=3、utf8mb4=4
char(n)：n*字符集长度
varchar(n)：n * 字符集长度 + 2字节
数值类型
TINYINT：1个字节
SMALLINT：2个字节
MEDIUMINT：3个字节
INT、FLOAT：4个字节
BIGINT、DOUBLE：8个字节
时间类型
DATE：3个字节
TIMESTAMP：4个字节
DATETIME：8个字节

3.10. Extra字段 Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

Using where 表示查询需要通过索引回表查询数据。
Using index 表示查询需要通过索引，索引就可以满足所需数据。
Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。
Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作。

总结在使用EXPLAIN分析慢SQL的时候，我们主要关注type字段，最好可以使用到 range 这一级别及以上的 type 访问方式，如果只使用到了 all 或者 index 这一级别的访问方式，我们可以从 SQL 语句和索引设计的角度上进行改进。
其次关注rows字段，绝大部分rows小的语句执行一定很快，所以优化语句基本上都是在优化rows。
四、SQL性能分析show profile命令 show profile 相比 EXPLAIN 能看到更进一步的执行解析，包括 SQL 都做了什么、所花费的时间等。
默认情况下，show profile 是关闭的，使用下面的命令查看状态

mysql> show variables like 'profiling';

文章图片

通过设置 profiling='ON’来开启 show profile：

mysql > set profiling = 'ON';

文章图片

show profile命令只是在本会话内起作用，即无法分析本会话外的语句。开启分析功能后，所有本会话中的语句都被分析（甚至包括执行错误的语句），除了SHOW PROFILE和SHOW PROFILES两句本身。

查看当前会话都有哪些 profiles，使用下面这条命令：

mysql > show profiles;

文章图片

使用下面的命令我们可以查看上一个查询的开销：

mysql > show profile;

文章图片

可以给show profile 指定一个 for query id 来查看指定 id 的语句，比如查看Query_ID为4的SQL信息

mysql> show profile for query 4;

还可以给输出添加新的列，取值范围可以如下：

ALL 显示所有性能信息
BLOCK IO 显示块IO操作的次数
CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动
CPU 显示用户CPU时间、系统CPU时间
IPC 显示发送和接收的消息数量
PAGE FAULTS 显示页错误数量
SOURCE 显示源码中的函数名称与位置
SWAPS 显示SWAP的次数

比如：

mysql> show profile cpu,block io,swaps for query 4;

文章图片

通过上面的结果，我们可以弄清楚每一步骤的耗时，以及在不同部分，比如 CPU、block io 的执行时间，这样我们就可以判断出来 SQL 到底慢在哪里。
五、总结本文介绍了MySQL性能分析常用的三种工具（还有很多性能分析工具），通过慢查询日志定位执行慢的 SQL，然后通过 EXPLAIN 分析该 SQL 语句是否使用到了索引和具体的数据表访问方式是怎样的。如果有需要最后再使用 SHOW PROFILE 进一步了解 SQL 每一步的执行时间，包括CPU 、I/O 等资源的使用情况。
通过上面介绍的这三种工具我们可以快速的定位和分析慢SQL，然后做出相应的优化。
参考文档：
《高性能MySQL》