MySQL优化：explain、show profile和show processlist

前言

要想优化SQL语句，首先得知道SQL语句有什么问题，哪里需要被优化。这样就需要一个SQL语句的监控与量度指标，本文讲述的explain和show profile就是这样两个量度SQL语句的命令。

本文主要基于MySQL5.6讲解其用法，因为之后的MySQL版本会去掉show profile功能。

SQL脚本

本篇使用的表结构以及数据如下

/*Table structure for table `dept` */
CREATE TABLE `dept` (
  `deptno` int(2) NOT NULL,
  `dname` varchar(15) DEFAULT NULL,
  `loc` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE,
  UNIQUE KEY `index_dept_dname` (`dname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

/*Data for the table `dept` */
insert  into `dept`(`deptno`,`dname`,`loc`) values 
(10,'ACCOUNTING','NewYork'),
(20,'RESEARCH','Dallas'),
(30,'SALES','Chicago'),
(40,'OPERATIONS','Boston');

/*Table structure for table `emp` */
CREATE TABLE `emp` (
  `empno` int(4) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(10) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,0) DEFAULT NULL,
  `comm` decimal(7,0) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`) USING BTREE,
  KEY `index_emp_ename` (`ename`),
  KEY `index_emp_deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

/*Data for the table `emp` */
insert  into `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) values 
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

使用explain

explain关键字用于获取SQL语句的执行计划，描述的是SQL将以何种方式去执行，用法非常简单，就是直接加在SQL之前。

explain select * from emp

执行结果

mysql> explain select * from emp;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)

执行结果中的各个字段代表的含义如下（下文详解各个字段的意思）

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

id

一系列数字，表示SQL语句执行的序列号，代表了操作的顺序。具体原则分为以下两点

id相同时，从上往下执行
id不同时，数值越大，优先级越高，越先执行

select_type

主要是用来区分查询的类型，是普通查询、连接查询、还是子查询。值对应的解释如下

select_type Value	Meaning	解释	例子
SIMPLE	Simple SELECT (not using UNION or subqueries)	不包含`UNION`或子查询	`EXPLAIN SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno`
PRIMARY	Outermost SELECT	查询中包含子查询，最外层的查询会被标记成`PRIMARY`	`EXPLAIN SELECT * FROM emp e WHERE e.deptno = (SELECT d.deptno FROM dept d WHERE d.dname = 'SALES')`
UNION	Second or later SELECT statement in a UNION	出现在`UNION`之后的语句会被标记成`UNION`	`EXPLAIN SELECT * FROM emp WHERE empno = 7369 UNION SELECT * FROM emp WHERE empno = 7499 UNION SELECT * FROM emp WHERE empno = 7521`
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query	和`UNION`类似，但是结果取决于外部查询	`EXPLAIN SELECT * FROM emp e WHERE e.empno IN ( SELECT empno FROM emp WHERE deptno = 10 UNION SELECT empno FROM emp WHERE sal >2000)`
UNION RESULT	Result of a UNION.	`UNION`的结果	同·`UNION`·
SUBQUERY	First SELECT in subquery	子查询中的第一个`SELECT`子句	同`PRIMARY`
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query	和`SUBQUERY`类似，但是结果取决于外部查询	同`DEPENDENT UNION`
DERIVED	Derived table	`FROM`语句中出现的子查询，也叫派生表	`EXPLAIN SELECT * FROM (SELECT e.empno, e.ename, e.deptno FROM emp e) t`
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query	/	/
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)	/	/

table

表示正在访问哪一张表，是表名或者别名。也有可能是临时表或者union的结果集。

<unionM,N>：是指ID值为M和N的行的并集。
<derivedN>：引用ID值为N的行的派生表结果。派生表可能来自例如FROM子句中的子查询。
<subqueryN>：引用ID值为N的行的实例化子查询的结果

type

描述如何联接表，表示SQL语句以何种方式去访问表，找到对应的数据。访问类型有很多，效率从高到低，分别为

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况下，得保证查询至少达到range级别，最好能达到ref

type	描述	例子
ALL	全表扫描，通常利用索引来避免	`explain select * from emp`
index	全索引扫描，效率比`ALL`高，通常包括两种情况当前查询用到了索引覆盖，所需的数据可以在索引中直接获取当前查询利用了索引进行排序，这样就可以避免数据的重新排序	`EXPLAIN SELECT e.ename FROM emp e`
range	使用索引的时候限制了范围，避免了`index`类型的全索引扫描实用范围 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN()	`EXPLAIN SELECT * FROM emp e WHERE e.ename LIKE 'SMITH%'`
index_subquery	利用索引来关联子查询	/
unique_subquery	类似`index_subquery`，但是使用的是唯一索引	/
index_merge	需要多个索引组合使用	/
ref_or_null	对某个查询条件既需要关联条件，又需要null	`EXPLAIN SELECT * FROM emp e WHERE e.deptno = 20 OR e.deptno IS NULL`
fulltext	使用`FULLTEXT`索引执行连接	/
ref	使用非唯一的索引进行查找，和`ref_or_null`类似，但是不需要null	`EXPLAIN SELECT * FROM emp e WHERE e.deptno = 20`
eq_ref	使用`PRIMARY KEY`或者`UNIQUE NOT NULL`索引进行连接查询	`EXPLAIN SELECT * FROM emp e1 LEFT JOIN emp e2 ON e1.empno = e2.empno`
const	查询最多能匹配一条记录	`EXPLAIN SELECT * FROM emp WHERE empno = 7369`
system	表只有一行记录，`const`的特例	/

possible_keys

显示一个或者多个可能用于该SQL的索引。

EXPLAIN SELECT * FROM emp e WHERE e.`ename` LIKE 'SIM%' AND e.`deptno` = 10

执行结果

mysql> EXPLAIN SELECT * FROM emp e WHERE e.`ename` LIKE 'SIM%' AND e.`deptno` = 10;
+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+
| id | select_type | table | type  | possible_keys                    | key             | key_len | ref  | rows | Extra                              |    
+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+    
|  1 | SIMPLE      | e     | range | index_emp_ename,index_emp_deptno | index_emp_ename | 33      | NULL |    1 | Using index condition; Using where |
+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+    
1 row in set (0.00 sec)

key

实际使用到的索引，如果NULL表示没有使用索引

EXPLAIN SELECT * FROM emp e WHERE e.`ename` LIKE 'SIM%' AND e.`deptno` = 10

执行结果

mysql> EXPLAIN SELECT * FROM emp e WHERE e.`ename` LIKE 'SIM%' AND e.`deptno` = 10;
+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+
| id | select_type | table | type  | possible_keys                    | key             | key_len | ref  | rows | Extra                              |    
+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+    
|  1 | SIMPLE      | e     | range | index_emp_ename,index_emp_deptno | index_emp_ename | 33      | NULL |    1 | Using index condition; Using where |
+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+    
1 row in set (0.00 sec)

key_len

表示索引中使用的字节数，在满足需求的情况下，值越小越好。

ref

表示将哪些列或常量与索引进行比较，以从表中选择记录。

列名

EXPLAIN SELECT * FROM emp e1 LEFT JOIN emp e2 ON e1.`empno` = e2.`empno`

执行结果

mysql> EXPLAIN SELECT * FROM emp e1 LEFT JOIN emp e2 ON e1.`empno` = e2.`empno`;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
|  1 | SIMPLE      | e1    | ALL    | NULL          | NULL    | NULL    | NULL          |   14 | NULL  |
|  1 | SIMPLE      | e2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.e1.empno |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
2 rows in set (0.00 sec)

ref列中test.e1.empno分别表示数据库名、表（别）名，字段名。

常量

EXPLAIN SELECT * FROM emp WHERE ename = 'CLERK'

执行结果

mysql> EXPLAIN SELECT * FROM emp WHERE ename = 'CLERK';
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | emp   | ref  | index_emp_ename | index_emp_ename | 33      | const |    1 | Using index condition |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

如果该值为func，则使用的值是某些函数的结果。

rows

该SQL语句需要访问的大致行数，是一个估计值。但是这个值非常重要，在满足需求的情况下，越小越好。

extra

额外信息。其中常见比较重要的几种如下

	解释	例子
using filesort	无法仅利用索引就完成排序，只能利用排序算法进行排序，会消耗额外的空间	`EXPLAIN SELECT * FROM emp ORDER BY hiredate ASC`
using temporary	建立临时表来保存中间结果，查询完成之后把临时表删除	`EXPLAIN SELECT ename ,COUNT(*) FROM emp WHERE deptno = 10 GROUP BY ename`
using index	表示当前查询满足索引覆盖	`EXPLAIN SELECT ename FROM emp`
using where	使用`where`条件进行过滤	`EXPLAIN SELECT ename FROM emp WHERE ename = 'SMITH'`
impossible where	`where`条件的结果总是`false`	`EXPLAIN SELECT * FROM emp WHERE 1 = 2`

以上便是explain关键字的使用方式以及含义，这个关键字的作用主要用来分析索引使用情况。

需要了解的是：使用explain关键字进行分析时，SQL语句并不会执行。只是模拟MySQL优化器的执行过程，所以用explain查看的结果是叫执行计划。

使用show profile

explain关键字主要用来定性分析索引的使用情况，以及SQL语句的优劣，但是无法知道SQL语句的实际执行情况。
而show profile命令可以做到定量分析SQL语句的执行情况。即使用者可以明确知道一条SQL到底执行了多久。

想要使用这个命令，主要步骤可以分为四步：

首先设置属性，
```
set profileing=1;
```
开启了这个属性后，再执行SQL语句，就会记录SQL语句执行各个步骤耗时
接着执行多条SQL语句
```
select * from emp;
select * from dept;
```
执行结果不重要，主要关注各个SQL语句的执行时间
接下来再执行如下语句，显示统计成功的SQL语句
```
show profiles;
```

执行结果

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00065025 | select * from emp  |
|        2 | 0.00626150 | select * from dept |
+----------+------------+--------------------+
2 rows in set, 1 warning (0.00 sec)

可以看到MySQL已经统计了上面执行的两条SQL语句

如果想具体查看SQL语句各个步骤的详细耗时，接着执行如下SQL语句

## 查看第二条SQL语句执行耗时的详细信息
show profile for query 2

执行结果

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000164 |
| checking permissions | 0.000054 |
| Opening tables       | 0.004434 |
| init                 | 0.000037 |
| System lock          | 0.000013 |
| optimizing           | 0.000007 |
| statistics           | 0.000013 |
| preparing            | 0.000014 |
| executing            | 0.000004 |
| Sending data         | 0.001350 |
| end                  | 0.000013 |
| query end            | 0.000007 |
| closing tables       | 0.000012 |
| freeing items        | 0.000123 |
| cleaning up          | 0.000018 |
+----------------------+----------+
15 rows in set, 1 warning (0.03 sec)

执行结果展示个各个步骤以及持续的时间。

show profile语法

show profile完整的语法如下：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

各个type对应的信息如下

type	解释
ALL	显示所有性能信息
BLOCK IO	显示块io的次数
CONTEXT SWITCHES	显示上下文切换的次数，包括主动和被动
CPU	显示系统和用户CPU使用时间
IPC	显示发送和接收消息的次数
MEMORY	暂未实现
PAGE FAULTS	显示页面错误的数量
SOURCE	显示源代码中的函数名称以及该函数所在文件的名称和行号
SWAPS	显示swap的次数

也就是说除了各个步骤持续的时间，还可以看到BLOCK IO、CPU等信息，具体用法如下：

show profile block io, cpu for query 2

执行结果：

+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000164 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions | 0.000054 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables       | 0.004434 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                 | 0.000037 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock          | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing           | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics           | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing            | 0.000014 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing            | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data         | 0.001350 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                  | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end            | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables       | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items        | 0.000123 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up          | 0.000018 | 0.000000 |   0.000000 |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

补充

需要注意的是，show profile方式将从5.6.7开始不推荐使用，并且在以后的版本中会删除，改用Performance Schema

使用show processlist

show processlist命令可以查看当前MySQL实例的连接情况，用于观察是否有大量的连接处于非正常状态。用法非常简单，直接使用就行

show processlist

执行结果

mysql> show processlist;
+----+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host           | db   | Command | Time | State | Info             |
+----+------+----------------+------+---------+------+-------+------------------+
|  7 | root | localhost:2353 | test | Sleep   |   57 |       | NULL             |
|  8 | root | localhost:3811 | NULL | Query   |    0 | init  | show processlist |
+----+------+----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

可以看到我的MySQL实例当前有2个连接。其中各个字段的含义如下

字段	解释
Id	连接标识符
User	当前用户
Host	操作的主机，指客户端
db	默认数据库（如果已选择）；否则为NULL
Command	线程正在执行的命令类型
Time	线程处于其当前状态的持续时间（以秒为单位）
State	指示线程正在执行的操作，事件或状态
Info	线程正在执行的语句，如果未执行任何语句，则为NULL。该语句可能是发送到服务器的那条语句，或者是最内部的语句（如果该语句执行其他语句，比如存储过程中的select语句）

对于Command字段，对应的状态如下：

sleep：正在等待客户端发送新的请求
query：正在执行查询或者正在将结果发送给客户端
locked：在MySQL服务层，线程正在等待表锁
analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中
sorting result：正在对结果集进行排序
sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据

show processlist命令默认Info字段最多显示每条语句的前100个字符，如果想完全显示，可以使用show full processlist

总结

学会了explain和show profile这两个命令，足以应用于一些比较简单的性能分析场景。分析出SQL语句存在的问题，从而写出更优质的SQL语句。

show processlist命令则是用来管理MySQL实例的连接情况，如果收到类似too many connections的错误，使用此命令将非常有用。

参考

https://dev.mysql.com/doc/refman/5.6/en/explain-output.html
https://dev.mysql.com/doc/refman/5.6/en/show-profile.html
https://dev.mysql.com/doc/refman/5.6/en/show-processlist.html

posted @ 2020-07-09 16:19 一个人的孤独自白阅读(616) 评论(0) 编辑收藏举报

刷新页面返回顶部

MySQL优化：explain、show profile和show processlist

前言

SQL脚本

使用explain

id

select_type

table

type

possible_keys

key

key_len

ref

rows

extra

使用show profile

show profile语法

补充

使用show processlist

总结

参考

公告