分析SQL执行效率（一）

定位慢 SQL

定位慢 SQL 的两种方案

• 查看慢查询日志确定已经执行完的慢查询

• show processlist 查看正在执行的慢查询

通过慢查询日志

MySQL 的慢查询日志用来记录在 MySQL 中响应时间超过参数 long_query_time（单位秒，默认值 10）设置的值并且扫描记录数不小于 min_examined_row_limit（默认值 0）的语句

使用慢查询日志，一般分为四步：开启慢查询日志、设置慢查询阀值、确定慢查询日志路径、确定慢查询日志的文件名。

首先开启慢查询日志，由参数 slow_query_log 决定是否开启，默认环境下，慢查询日志是关闭的

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

设置慢查询时间阀值

mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

慢查询日志的路径默认是 MySQL 的数据目录

mysql> show global variables like "datadir";
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| datadir       | /data/3306/ |
+---------------+-------------+
1 row in set (0.00 sec)

确定慢查询日志的文件名

mysql> show global variables like "slow_query_log_file";
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log_file | /data/3306/mysql_01-slow.log |
+---------------------+------------------------------+
1 row in set (0.01 sec)

根据上面的查询结果，可以直接查看 /data/3306/mysql_01-slow.log 文件获取已经执行完的慢查询

[root@mysql_01 ~]# tail -n5 /data/3306/mysql_01-slow.log 
# Time: 2020-06-17T03:47:28.224746Z
# User@Host: root[root] @ localhost []  Id:     5
# Query_time: 10.003112  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1592365648;
select sleep(10);
[root@mysql_01 ~]# 

tail -n5 ：只查看慢查询文件的最后 5 行

Time：慢查询发生的时间

User@Host：客户端用户和 IP

Query_time：查询时间

Lock_time：等待表锁的时间

Rows_sent：语句返回的行数

Rows_examined：语句执行期间从存储引擎读取的行数

通过 show processlist

有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS 权限，则可以看到所有线程。否则，只能看到当前会话的线程。

mysql> show processlistG
*************************** 1. row ***************************
     Id: 8
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
1 row in set (0.00 sec)

Time：表示执行时间

Info：表示 SQL 语句

可以通过它的执行时间（Time）来判断是否是慢 SQL

使用 explain 分析慢查询

explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。使用方法：在查询语句前面加上 explain 运行就可以了

为了便于理解，先创建两张测试表

create database muke;
use muke;

CREATE TABLE `t1` (
	`id` int(11) NOT NULL auto_increment,
	`a` int(11) DEFAULT NULL,
	`b` int(11) DEFAULT NULL,
	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
	`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
	PRIMARY KEY (`id`),
	KEY `idx_a` (`a`),
	KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

delimiter $
create procedure insert_t1()	/* 创建存储过程insert_t1 */
begin
    declare i int;				/* 声明变量i */
    set i=1;				/* 设置i的初始值为1 */
    while(i<=1000) do			/* 对满足i<=1000的值进行while循环 */
	insert into t1(a, b) values(i, i); 		/* 写入表t1中a、b两个字段，值都为i当前的值 */
	set i=i+1;				/* 将i加1 */
    end while;
end $
delimiter ;
call insert_t1();				/* 运行存储过程insert_t1 */

create table t2 like t1;			/* 创建表t2，表结构与t1一致 */
insert into t2 select * from t1;		/* 将表t1的数据导入到t2 */

下面尝试使用 explain 分析一条 SQL，例子如下：

mysql> explain select * from t1 where b=100;

列名	解释
id	查询编号
select_type	查询类型：显示本行是简单还是复杂查询
table	涉及到的表
partitions	匹配的分区：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。
type	本次查询的表连接类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	被选择的索引长度：一般用于判断联合索引有多少列被选择了
ref	与索引比较的列
rows	预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
filtered	按条件筛选的行的百分比
Extra	附加信息

select_type

select_type 的值	解释
SIMPLE	简单查询 (不使用关联查询或子查询)
PRIMARY	如果包含关联查询或者子查询，则最外层的查询部分标记为 primary
UNION	联合查询中第二个及后面的查询
DEPENDENT UNION	满足依赖外部的关联查询中第二个及以后的查询
UNION RESULT	联合查询的结果
SUBQUERY	子查询中的第一个查询
DEPENDENT SUBQUERY	子查询中的第一个查询，并且依赖外部查询
DERIVED	用到派生表的查询
MATERIALIZED	被物化的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外层查询的每一行
UNCACHEABLE UNION	关联查询第二个或后面的语句属于不可缓存的子查询

type

type 的值	解释
system	查询对象表只有一行数据，且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况
const	基于主键或唯一索引查询，最多返回一条结果
eq_ref	表连接时基于主键或非 NULL 的唯一索引完成扫描
ref	基于普通索引的等值查询，或者表间等值连接
fulltext	全文检索
ref_or_null	表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	利用索引进行范围查询
index	全索引扫描
ALL	全表扫描

上表的这些情况，查询性能从上到下依次是最好到最差。

extra

Extra 常见的值	解释	例子
Using filesort	将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序	explain select * from t1 order by create_time;
Using temporary	需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时	explain select * from t1 group by create_time;
Using index	使用覆盖索引	explain select a from t1 where a=111;
Using where	使用 where 语句来处理结果	explain select * from t1 where create_time=‘2019-06-18 14:38:24’;
Impossible WHERE	对 where 子句判断的结果总是 false 而不能选择任何数据	explain select * from t1 where 1<0;
Using join buffer (Block Nested Loop)	关联查询中，被驱动表的关联字段没索引	explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);
Using index condition	先条件过滤索引，再查数据	explain select * from t1 where a >900 and a like “%9”;
Select tables optimized away	使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是	explain select max(a) from t1;