mysql show 和explain_MySQL优化：explain、show profile和show processlist

最新推荐文章于 2023-06-28 10:57:38 发布

艺术留学女流氓

最新推荐文章于 2023-06-28 10:57:38 发布

阅读量380

点赞数

文章标签： mysql show 和explain

本文链接：https://blog.csdn.net/weixin_28748867/article/details/113455663

版权

前言

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

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------+

1 row in set (0.01 sec)

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

ColumnJSON NameMeaning

select_id

The SELECT identifier

select_type

None

The SELECT type

table

table_name

The table for the output row

partitions

The matching partitions

type

access_type

The join type

possible_keys

The possible indexes to choose

key

The index actually chosen

key_len

key_length

The length of the chosen key

ref

The columns compared to the index

rows

Estimate of rows to be examined

filtered

Percentage of rows filtered by table condition

Extra

None

Additional information

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

id相同时，从上往下执行

id不同时，数值越大，优先级越高，越先执行

select_type

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

select_type ValueMeaning解释例子

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的结果集。

：是指ID值为M和N的行的并集。

：引用ID值为N的行的派生表结果。派生表可能来自例如FROM子句中的子查询。

：引用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类型的全索引扫描

实用范围 =, <>, >, >=, , 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;

+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+

+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+

+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+

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;

+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+

+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+

+----+-------------+-------+-------+----------------------------------+-----------------+---------+------+------+------------------------------------+

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`;

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+

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';

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+

| 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

执行结果：

+----------------------+----------+----------+------------+--------------+---------------+

+----------------------+----------+----------+------------+--------------+---------------+

| 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;

+----+------+----------------+------+---------+------+-------+------------------+

+----+------+----------------+------+---------+------+-------+------------------+

+----+------+----------------+------+---------+------+-------+------------------+

2 rows in set (0.00 sec)

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

字段解释

连接标识符

User

当前用户

Host

操作的主机，指客户端

默认数据库(如果已选择)；否则为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

艺术留学女流氓

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql show 和explain_MySQL优化：explain、show profile和show processlist

前言要想优化SQL语句，首先得知道SQL语句有什么问题，哪里需要被优化。这样就需要一个SQL语句的监控与量度指标，本文讲述的explain和show profile就是这样两个量度SQL语句的命令。本文主要基于MySQL5.6讲解其用法，因为之后的MySQL版本会去掉show profile功能。SQL脚本本篇使用的表结构以及数据如下/*Table structure for table `dep...
复制链接

扫一扫