MySQL Explain详解

阅读量：5790 次

发布时间：2019-06-18

本文共 10467 字，大约阅读时间需要 34 分钟。

MySQL Explain详解

若想查看MySQL优化器优化后的sql语句可以使用如下语句：

EXPLAIN EXTENDED sql_statement;-- show warnings紧接着上面的语句执行SHOW WARNINGS\G;

Explain输出字段解释

Explain输出字段：

Column	含义
id	查询序号
select_type	查询类型
table	表名
partitions	匹配的分区
type	join类型
prossible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引的长度
ref	与索引作比较的列
rows	要检索的行数(估算值)
filtered	查询条件过滤的行数的百分比
Extra	额外信息

id

select的标识符。整个查询语句中每个select的序列号。id越大的SELECT最先被执行，对于id相同的记录，顺序由上往下。若此行引用的是其他行UNION的结果，则id值为NULL。

select_type

查询类型，包含以下几种：

select_type	类型说明
SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	最外层的SELECT
UNION	UNION中第二个或之后的SELECT语句
DEPENDENT UNION	UNION中第二个或之后的SELECT语句取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT, 取决于外面的查询
DERIVED	衍生表(FROM子句中的子查询)
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

DEPENDENT 意味着使用了关联子查询。关于关联子查询查看：

table

输出行所引用的表。也可以为如下的值：

<unionM,N>: 引用id为M和N UNION后的结果。

<derivedN>: 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。

<subqueryN>: 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。详情查看:

partitions

此查询匹配到的分区。只有在PARTITIONS关键字被使用的时候此字段会显示。若表没有分区则值为NULL。

type

联接类型，下面详细介绍各种join类型，顺序为从最优类型到最差类型：

system

表中只有一行数据(= system table)。这是const类型的一个特例。

const

最多只有一行记录匹配，它将在查询开始时被读取。由于仅有一行记录匹配，所以此条记录的列值可被优化器视为常数。因为只读取一次，所以const表很快。

当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。在下面的查询中，tlb_name可以被用作const表：

SELECT * FROM tbl_name WHERE primary_key=1;SELECT * FROM tbl_name WHERE unique_key=1;SELECT * FROM tbl_name  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

eq_ref可用于使用'='操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。在下面的例子中，MySQL能够使用eq_ref类型来处理ref_table：

SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;

ref

对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）。

ref可用于使用'='或'<=>'操作符作比较的索引列。在下面的例子中，MySQL使用ref类型来处理ref_table:

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;

fulltext

在用到全文索引时会使用此类型。

ref_or_null

除了MySQL会额外查询包含NULL值的行，此类型跟ref一样。此类型常用在解析子查询的时候。在如下的例子中，MySQL使用ref_or_null类型：

SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;

index_merge

表示使用了索引合并优化。在这种情况下，key列包含了使用的索引的列表，key_len列包含了使用的索引的最长部分的列表。更多信息：

unique_subquery

对于如下形式的IN子查询，使用此类型替换eq_ref类型：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery 仅是一个索引查找功能，能够以更高的效率完全替换子查询。

index_subquery

跟unique_subquery类似。可以替代IN子查询，但是它适用于如下形式子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

使用索引查询记录，只取回指定范围的行。key列显示使用了哪个索引，key_len列包含了使用了此索引的长度。此类型下的ref列值为NULL。

当索引列使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()操作符与常量作比较时，会用到range类型。

SELECT * FROM tbl_name WHERE key_column = 10;SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_name WHERE key_column IN (10,20,30);SELECT * FROM tbl_name  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

除了扫描的是索引树，此类型跟ALL类型相同。

若此索引对于查询来说为，并且仅通过扫描索引树就能得到查询所需的数据。这种情况下，Extra列会显示_Using index_。仅扫描索引树比ALL类型更快的原因：索引数据通常比表的数据小。

当只查询索引中的部分字段时，MySQL可以使用此联接类型。

CREATE TABLE `store_location` (  `store_id` int(11) NOT NULL DEFAULT '0',  `store_name` varchar(30) DEFAULT NULL,  `province_id` int(11) DEFAULT NULL,  `city_id` int(11) DEFAULT NULL,  `district_id` int(11) DEFAULT NULL,  PRIMARY KEY (`store_id`),  KEY `idx_location` (`province_id`,`city_id`,`district_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1insert into store_location values(1,'adidas',110,230,560);insert into store_location values(2,'nike',111,231,561);insert into store_location values(3,'new banlace',112,232,562);insert into store_location values(4,'puma',113,233,563);mysql> explain select province_id,city_id,district_id from store_location where city_id > 231;+----+-------------+----------------+-------+---------------+--------------+---------+------+------+--------------------------+| id | select_type | table          | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |+----+-------------+----------------+-------+---------------+--------------+---------+------+------+--------------------------+|  1 | SIMPLE      | store_location | index | NULL          | idx_location | 15      | NULL |    4 | Using where; Using index |+----+-------------+----------------+-------+---------------+--------------+---------+------+------+--------------------------+1 row in set (0.00 sec)

ALL

即全表扫面，性能最差。可以通过适当添加索引来避免出现ALL。

prossible_keys

指出MySQL可以使用哪些索引从表中查询记录。这些索引没有优先级之分。

如果该列为NULL，则没有相应的索引。在这种情况下，需要检查WHERE条件引用的字段是否加上了合适的索引。如果没有建立合适的索引，创建适当的索引并使用explain来评估此查询。

可以通过 SHOW INDEX FROM table_name 查看表中的索引。

key

MySQL实际使用的索引。key的取值也可能不在prossible_keys中。

通过在语句中使用 FORCE INDEX, USE INDEX, or IGNORE INDEX 来强制MySQL使用或忽略一个索引。详情查看：

key_len

被选中的索引的长度（单位：Byte）。若key列为NULL，则key列值为NULL。注意：通过key_len的值可以确定实际使用了联合索引的哪些部分。

关于key_len值的计算，可以查看此文章：

ref

使用哪些列或常量跟key的值作比较来查询记录。若值为func，那么此值使用的是函数的结果。在EXPLAIN EXTENDED 语句之后使用 SHOW WARNINGS 可以查看使用的是哪个函数。

rows

执行查询时需要检查的行数。对于InnoDB表，这是一个估算值，结果可能并不准确。

filtered

查询条件过滤了表中多少行记录，是一个估算的百分比值。rows列显示的是行数的估算值，rows × filtered / 100 表示跟前面表作join的行数。此列只有在使用EXPLAIN EXTENDED时才会展示。

Extra

解析查询语句时的额外信息，如下：

Using filesort

MySQL需要额外的排序操作，以按顺序返回数据。详情查看:

Using index

只需从索引树中返回所需字段的信息，而不需要额外从磁盘读取实际数据。当查询仅需返回单个索引中部分字段时，Extra字段显示此信息。

Using index for group-by

跟 Using index 访问方式类似，MySQL可以从索引中取回GROUP BY或DISTINCT查询所需的数据，而不需要额外从磁盘读取实际数据。详情查看:

Using index condition

使用了Index Condition Pushdown优化，详情查看:

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

将join前面的表的一部分放到join buffer中，然后用buffer中的记录跟当前表执行join操作。(Block Nested Loop) 表示使用Block Nested-Loop算法，(Batched Key Access) 表示使用Batched Key Access算法。就是说，前面的key列中出现的字段会放到join buffer中，然后从出现 Using join buffer 的那一行table字段列出的表中分批取回匹配的行。

Using MRR

使用了Multi-Range Read优化策略，详情查看:

Using sort_union(...), Using union(...), Using intersect(...)

对于type值为index_merge类型的联接，使用了哪种索引合并算法。关于索引合并优化可以查看:

Using temporary

为了处理查询，需要创建临时表保存结果。如果查询语句包含GROUP BY和ORDER BY并且列出的字段不一样，Extra会出现此信息。

Using where

WHERE子句用于限制返回给客户端或与下一个表匹配的记录。除非你确实想要获取或检查表的所有行，否则查询会有问题若Extra不包含Using where并且联接类型为ALL或index。

Profiling的使用

SHOW PROFILE 和 SHOW PROFILES 可以查看在当前session中查询语句的资源使用情况的分析信息。 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

Profiling功能可以通过session变量来控制，默认值为0(OFF)。可以通过如下语句设置为开启状态：

mysql> SET profiling = 1;

SHOW PROFILES 语句可以查看当前session中最近执行的语句的一个列表。此列表的大小通过session变量 profiling_history_size 设置，默认值为15，最大值为100。将此变量设置为0相当于禁用Profiling。

SHOW PROFILE 语句可以查看一条sql语句的详细信息。若不包含 FOR QUERY n 子句，输出的是最近一条语句的信息。加上 FOR QUERY n 后 SHOW PROFILE 显示语句n的信息。

更多关于profiling的信息，查看MySQL官网：

如下代码为使用profiling的例子:

mysql> SET profiling = 1;Query OK, 0 rows affected (0.00 sec)mysql> select * from test_c where b = 1;+---+------+| a | b    |+---+------+| 1 |    1 |+---+------+1 row in set (0.02 sec)mysql> select * from test_d where b = 1;+---+------+------+------+| a | b    | c    | d    |+---+------+------+------+| 1 |    1 |    1 | 111  || 5 |    1 |    1 | 511  |+---+------+------+------+2 rows in set (0.03 sec)mysql> show profiles;+----------+------------+----------------------------------+| Query_ID | Duration   | Query                            |+----------+------------+----------------------------------+|        1 | 0.01119375 | select * from test_c where b = 1 ||        2 | 0.02607875 | select * from test_d where b = 1 |+----------+------------+----------------------------------+mysql> show profile for query 2;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.000121 || checking permissions | 0.000033 || Opening tables       | 0.000041 || init                 | 0.000048 || System lock          | 0.000035 || optimizing           | 0.000039 || statistics           | 0.014997 || preparing            | 0.000046 || executing            | 0.000024 || Sending data         | 0.010304 || end                  | 0.000042 || query end            | 0.000025 || closing tables       | 0.000028 || freeing items        | 0.000059 || cleaning up          | 0.000239 |+----------------------+----------+15 rows in set, 1 warning (0.01 sec)mysql> show profile cpu,block io for query 8;+----------------------+----------+----------+------------+--------------+---------------+| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting             | 0.000121 | 0.000000 |   0.000000 |            0 |             0 || checking permissions | 0.000033 | 0.000000 |   0.000000 |            0 |             0 || Opening tables       | 0.000041 | 0.000000 |   0.000000 |            0 |             0 || init                 | 0.000048 | 0.000000 |   0.000000 |            0 |             0 || System lock          | 0.000035 | 0.000000 |   0.000000 |            0 |             0 || optimizing           | 0.000039 | 0.000000 |   0.000000 |            0 |             0 || statistics           | 0.014997 | 0.000999 |   0.000000 |          128 |             0 || preparing            | 0.000046 | 0.000000 |   0.000000 |            0 |             0 || executing            | 0.000024 | 0.000000 |   0.000000 |            0 |             0 || Sending data         | 0.010304 | 0.000000 |   0.000000 |            0 |             0 || end                  | 0.000042 | 0.000000 |   0.000000 |            0 |             0 || query end            | 0.000025 | 0.000000 |   0.000000 |            0 |             0 || closing tables       | 0.000028 | 0.000000 |   0.000000 |            0 |             0 || freeing items        | 0.000059 | 0.000000 |   0.000000 |            0 |             0 || cleaning up          | 0.000239 | 0.000000 |   0.000000 |            0 |             0 |+----------------------+----------+----------+------------+--------------+---------------+15 rows in set, 1 warning (0.00 sec)-- 最后关闭profilingmysql> set profiling = 0;Query OK, 0 rows affected, 1 warning (0.01 sec)

参考：