博客

MySQL优化：explain、show profile和show processlist

  • Post author:
  • Post category:mysql




前言

要想优化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);

ALTER TABLE emp ADD INDEX idx_emp_ename(`ename`);
ALTER TABLE emp ADD INDEX idx_emp_deptno(`deptno`);



使用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


版权声明：本文为Baisitao_原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: