前言
要想优化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