MySQL(十六)-Explain用法说明

参考文献

`MySQL Explain`简介

EXPLAIN:查看SQL语句的执行计划

EXPLAIN命令可以帮助我们深入了解MySQL基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用,在优化慢查询时非常有用;

执行explain之后结果集包含如下信息

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | innodb1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.08 sec)

`Explain`结果字段说明

`id`

SELELCT查询的序列号,包含一组数字,表示查询中执行SELELCT子句或者操作表的顺序
id号分为三种情况：
1. 如果id相同,那么执行顺序从上到下
2. 如果id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
3. id相同和不同的,同时存在：相同的可以认为是一组,从上往下顺序执行,在所有组中,id值越大,优先级越高,越先执行.

mysql> explain select * from t21 join (select id from t21 order by name limit 10000,5) t on t21.id=t.id;

+----+-------------+------------+------------+--------+---------------+-------------------------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key                     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+-------------------------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL                    | NULL    | NULL | 10005 |   100.00 | NULL        |
|  1 | PRIMARY     | t21        | NULL       | eq_ref | PRIMARY       | PRIMARY                 | 4       | t.id |     1 |   100.00 | NULL        |
|  2 | DERIVED     | t21        | NULL       | index  | NULL          | idx_name_age_face_value | 93      | NULL | 10005 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-------------------------+---------+------+-------+----------+-------------+
3 rows in set (0.01 sec)

`select_type`

主要用来分辨查询的类型,是普通查询还是联合查询还是子查询

`select_type` Value	JSON Name	Meaning
`SIMPLE`	None	Simple `SELECT` (not using `UNION` or subqueries)
`PRIMARY`	None	Outermost `SELECT`
`UNION`	None	Second or later `SELECT` statement in a `UNION`
`DEPENDENT UNION`	`dependent` (`true`)	Second or later `SELECT` statement in a `UNION`, dependent on outer query
`UNION RESULT`	`union_result`	Result of a `UNION`.
`SUBQUERY`	None	First `SELECT` in subquery
`DEPENDENT SUBQUERY`	`dependent` (`true`)	First `SELECT` in subquery, dependent on outer query
`DERIVED`	None	Derived table
`DEPENDENT DERIVED`	`dependent` (`true`)	Derived table dependent on another table
`MATERIALIZED`	`materialized_from_subquery`	Materialized subquery
`UNCACHEABLE SUBQUERY`	`cacheable` (`false`)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
`UNCACHEABLE UNION`	`cacheable` (`false`)	The second or later select in a `UNION` that belongs to an uncacheable subquery (see `UNCACHEABLE SUBQUERY`)

上表源于:https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

select_type表示查询使用的类型,有下面几种

SIMPLE: 简单的select查询,不包含子查询和其他复杂语法的简单查询.

mysql> explain select * from innodb1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | innodb1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.08 sec)

PRIMARY: 查询中若包含任何复杂的子查询,最外层查询则被标记为PRIMARY

mysql> explain select * from (select * from test where id = 1000) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | test       | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

UNION: 若第二个select出现在union之后,则被标记为UNION

mysql> explain select * from test where id = 1000 union all select * from test2 ;
+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows  | Extra           |
+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+
|  1 | PRIMARY      | test       | const | PRIMARY       | PRIMARY | 8       | const |     1 | NULL            |
|  2 | UNION        | test2      | ALL   | NULL          | NULL    | NULL    | NULL  | 67993 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  |  NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+

DEPENDENT UNION : 跟union类似,此处的DEPENDENT表示union或union all联合而成的结果会受外部表影响

mysql> explain select * from test where id in (select id  from test where id = 1000 union all select id from test2) ;
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref   | rows  | Extra           |
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
|  1 | PRIMARY            | test       | ALL    | NULL          | NULL    | NULL    | NULL  | 68505 | Using where     |
|  2 | DEPENDENT SUBQUERY | test       | const  | PRIMARY       | PRIMARY | 8       | const |     1 | Using index     |
|  3 | DEPENDENT UNION    | test2      | eq_ref | PRIMARY       | PRIMARY | 8       | func  |     1 | Using index     |
| NULL | UNION RESULT       | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL  |  NULL | Using temporary |
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+

SUBQUERY: 子查询中的第一个select查询,不依赖与外部查询的结果集

mysql> explain select * from test where id = (select id from test where id = 1000);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL        |
|  2 | SUBQUERY    | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

DEPENDENT SUBQUERY: 子查询中的第一个select查询,依赖于外部查询的结果集

mysql> explain select * from test where id in (select id  from test where id = 1000 union all select id from test2) ;
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref   | rows  | Extra           |
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
|  1 | PRIMARY            | test       | ALL    | NULL          | NULL    | NULL    | NULL  | 68505 | Using where     |
|  2 | DEPENDENT SUBQUERY | test       | const  | PRIMARY       | PRIMARY | 8       | const |     1 | Using index     |
|  3 | DEPENDENT UNION    | test2      | eq_ref | PRIMARY       | PRIMARY | 8       | func  |     1 | Using index     |
| NULL | UNION RESULT       | <union2,3> | ALL    | NULL          | NULL    | NULL    | NULL  |  NULL | Using temporary |
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+

DERIVED:

DERIVED是一个关键词,用于表示派生表或子查询。当使用派生表或子查询时,可以将其结果称为"派生",并通过使用关键词DERIVED来引用。

通常,派生表或子查询是在查询语句中通过嵌套SELECT语句来创建的。它们可以作为临时的表来存储中间结果,供查询语句后续使用。

当一个表不是物理表时,那么被叫做DERIVED
用于FROM子句中有子查询的情况,MySQL会递归执行这些子查询,此结果集放在临时表中

mysql> explain select * from (select * from test2 where id = 1000)a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | test2      | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

`table`

table用来表示输出行所引用的表名

对应行正在访问哪一个表,表名或者别名,可能是临时表或者union合并结果集
1. 如果是具体的表名,则表明从实际的物理表中获取数据,当然也可以是表的别名
2. 表名是<derivedN>的形式,表示使用了id为N的查询产生的衍生表
3. 当有union result的时候,表名是union n1,n2等的形式,n1,n2表示参与union的id

`type`

type表示访问类型,下面依次解释各种类型,类型顺序从最好到最差排列

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

system: 表仅有一行,是const类型的一个特例,因为子查询只有一行数据,模拟了单表只有一行数据,此时type为system

mysql> explain select * from (select * from test2 where id = 1000)a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | test2      | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

const: 针对主键或唯一索引的等值查询扫描,确定只有一行匹配的时候,MySQL优化器会在查询前读取它并且只读取一次,速度非常快

mysql> explain select * from test where id =1 ;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

eq_ref: 此类型通常出现在多表的join查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高,常用在一个索引是UNIQUE KEY或者PRIMARY KEY

mysql> explain select * from test,test2 where test.com_key=test2.com_key;
+----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
| id | select_type | table | type   | possible_keys | key          | key_len | ref                | rows  | Extra |
+----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
|  1 | SIMPLE      | test2 | ALL    | IDX(com_key)  | NULL         | NULL    | NULL               | 67993 | NULL  |
|  1 | SIMPLE      | test  | eq_ref | IDX(com_key)  | IDX(com_key) | 194     | test.test2.com_key |     1 | NULL  |
+----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+

ref:

此类型通常出现在多表的join查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
对于来自前面的表的行组合,所有有匹配索引值的行都从这张表中读取,
如果联接只使用键的最左前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref;
ref可以用于使用=或<=>操作符的带索引的列

mysql> explain select * from test ,test2 where test.bnet_id=test2.aid;
+----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref               | rows  | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL    | NULL    | NULL              | 68505 | Using where           |
|  1 | SIMPLE      | test2 | ref  | idx_aid       | idx_aid | 5       | test.test.bnet_id | 34266 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+

ref_or_null: 类似ref,但是添加了可以专门搜索null值的行

mysql> explain select * from test where bnet_id=1 or bnet_id is null;
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type        | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref_or_null | idx_bnet      | idx_bnet | 9       | const |    2 | Using index condition |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+

index_merge: 该访问类型使用了索引合并优化方法,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素

mysql> explain select * from test where id  = 1 or bnet_id = 1;
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
| id | select_type | table | type        | possible_keys    | key              | key_len | ref  | rows | Extra                                      |
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
|  1 | SIMPLE      | test  | index_merge | PRIMARY,idx_bnet | PRIMARY,idx_bnet | 8,9     | NULL |    2 | Using union(PRIMARY,idx_bnet); Using where |
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+

range:

表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录.
key列显示使用了哪个索引.key_len包含所使用索引的最长关键元素.在该类型中ref列为NULL
当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,使用range;

mysql> explain select * from test where bnet_id > 1000 and  bnet_id < 10000;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | idx_bnet      | idx_bnet | 9       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

index:

在进行统计时非常常见,此联接类型实际上会扫描索引树
表示全索引扫描(full index scan), 和ALL类型类似, 只不过ALL类型是全表扫描, 而index`类型则仅仅扫描所有的索引, 而不扫描数据.
此类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra字段会显示 Using index

mysql> explain select count(*) from test;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_bnet | 9       | NULL | 68505 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+

ALL:

对于每个来自于先前的表的行组合,进行完整的表扫描,通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出
表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys和key字段都是NULL, 表示没有使用到索引, 并且rows十分巨大, 因此整个查询效率是十分低下的.

mysql> explain select *  from test where create_time = '0000-00-00 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 68505 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

`type` 类型的性能比较

通常来说, 不同的 type 类型的性能关系如下:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

`possible_keys`

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

`key`

key列显示的是当前表实际使用的索引,如果没有选择索引,则此列为null,要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

`key_len`

key_len列显示MySQL决定使用的键长度.如果KEY键是NULL,则长度为NULL.在不损失精确性的情况下,长度越短越好

key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节.key_len的计算法方法:

列类型	`key_len`	备注
`id int`	`key_len` = 4+1	int为4bytes,允许为NULL,加1byte
`id bigint not null`	`key_len`=8	bigint为8bytes
`user char(30) utf8`	`key_len`=30*3+1	utf8每个字符为3bytes,允许为NULL,加1byte
`user varchar(30) not null utf8`	`key_len`=30*3+2	utf8每个字符为3bytes,变长数据类型,加2bytes
`user varchar(30) utf8`	`key_len`=30*3+2+1	utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
`detail text(10) utf8`	`key_len`=30*3+2+1	TEXT截取部分,被视为动态列类型.

key_len只指示了WHERE中用于条件过滤时被选中的索引列,是不包含order by或group by这一部分被选中的索引列

`ref`

ref列用来显示使用哪个列或常数与key一起从表中选择相应的行.它显示的列的名字(或const),此列多数时候为null

`rows`

rows列显示的是MySQL解析器认为执行此SQL时必须扫描的行数.此数值为一个预估值,不是具体值,通常比实际值小

`filtered`

此参数为**MySQL 5.7 新加参数**,指的是返回结果的行数所占需要读到的行(rows的值)的比例对于使用join时,前一个表的结果集大小直接影响了循环的行数

`Extra`

extra表示不在其他列并且也很重要的额外信息

Using index: 该值表示这个SQL语句使用了覆盖索引(覆盖索引是指可以直接在索引列中得到想要的结果,而不用去回表),此时效率最高

mysql> explain select id from test;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_bnet | 9       | NULL | 68505 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+

这个例子中id字段为主键,但是key那里显示走的并不是主键索引,这个是因为MySQL的所有二级索引中都会包含所有的主键信息,而MySQL没有单独的存储主键索引,所以扫描二级索引的开销比全表扫描更快

Using where: 表示存储引擎搜到记录后进行了后过滤(POST-FILTER),如果查询未能使用索引,using where的作用只是提醒我们MySQL要用WHERE条件过滤结果集

mysql> explain select * from test where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 8       | NULL | 34252 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

Using temporary 表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询

mysql> explain select * from test where id in (1,2) group by bnet_id;
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys                           | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | test  | range | PRIMARY,IDX(event_key-bnet_Id),idx_bnet | PRIMARY | 8       | NULL |    2 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+

Using filesort: 是指MySQL无法利用索引直接完成排序(排序的字段不是索引字段),此时会用到缓冲空间来进行排序

mysql> explain select * from test order by bnet_id;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 68505 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

Using join buffer: 强调在获取连接条件时没有用到索引,并且需要连接缓冲区来存储中间结果.(性能可以通过添加索引或者修改连接字段改进)

mysql> explain select * from test left join test2 on test.create_time = test2.create_time;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 959692 |   100.00 | NULL                                               |
|  1 | SIMPLE      | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 958353 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Block Nested Loop是指Block Nested-Loop Join算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数.

Impossible where: 表示WHERE条件导致没有返回的行,意味着查询优化器判断基于给定的查询条件,无法选择合适的索引或使用索引进行有效的数据过滤

mysql> explain select * from test where id is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

Using index condition: 是MySQL 5.6 之后新加的特性,结合MySQL的ICP(Index Condition Pushdown 索引下推)特性使用.主要是优化了可以在索引(仅限二级索引)上进行`like查找

`explain format=tree`,`explain format=json`,`explain analyze`

MySQL 8.0.16 引入一个实验特性:explain format=tree ,树状的输出执行过程,以及预估成本和预估返回行数.在 MySQL 8.0.18 又引入了 EXPLAIN ANALYZE,在 format=tree 基础上,使用时,会执行 SQL ,并输出迭代器(感觉这里用“算子”更容易理解)相关的实际信息,比如执行成本、返回行数、执行时间,循环次数.

mysql> explain format=tree SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
*************************** 1. row ***************************
  -> Nested loop inner join  (cost=4.95 rows=9)
    -> Filter: (`<subquery2>`.b is not null)  (cost=2.83..1.80 rows=9)
        -> Table scan on <subquery2>  (cost=0.29..2.61 rows=9)
            -> Materialize with deduplication  (cost=3.25..5.58 rows=9)
                -> Filter: (t2.b is not null)  (cost=2.06 rows=9)
                    -> Filter: (t2.id < 10)  (cost=2.06 rows=9)
                        -> Index range scan on t2 using PRIMARY  (cost=2.06 rows=9)
    -> Index lookup on t1 using a (a=`<subquery2>`.b)  (cost=2.35 rows=1)
1 row in set (0.01 sec)

mysql> explain analyze SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10)\G
*************************** 1. row ***************************
  -> Nested loop inner join  (cost=4.95 rows=9) (actual time=0.153..0.200 rows=9 loops=1)
    -> Filter: (`<subquery2>`.b is not null)  (cost=2.83..1.80 rows=9) (actual time=0.097..0.100 rows=9 loops=1)
        -> Table scan on <subquery2>  (cost=0.29..2.61 rows=9) (actual time=0.001..0.002 rows=9 loops=1)
            -> Materialize with deduplication  (cost=3.25..5.58 rows=9) (actual time=0.090..0.092 rows=9 loops=1)
                -> Filter: (t2.b is not null)  (cost=2.06 rows=9) (actual time=0.037..0.042 rows=9 loops=1)
                    -> Filter: (t2.id < 10)  (cost=2.06 rows=9) (actual time=0.036..0.040 rows=9 loops=1)
                        -> Index range scan on t2 using PRIMARY  (cost=2.06 rows=9) (actual time=0.035..0.038 rows=9 loops=1)
    -> Index lookup on t1 using a (a=`<subquery2>`.b)  (cost=2.35 rows=1) (actual time=0.010..0.010 rows=1 loops=9)
1 row in set (0.01 sec)

从右到左: 没有遇到并列的迭代器之前,都是从右边开始执行；
从上到下: 遇到并列的迭代器,都是上边的先开始执行;

`SHOW STAUS`

通过SHOW STATUS命令可以查看实际的行操作

在查询计划前后查询Handler_%的状态可以检查是否使用了索引:show status like 'Handler_%;'

mysql> show status like 'Handler_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

Handler_read_rnd_next代表从datafile中读取下一个值的频繁程度.过高的值一般代表了全表扫描.
Handler_read_key表示读取索引的请求数目.正常情况下该值相对于Handler_read_rnd_next不应该很低,相差很多则表示大部分行的读取没有使用索引.
Handler_commit表示事务提交的次数
Handler_read_first表示读取索引中第一项的次数,Handler_read_first,表明请求服务器读取索引中第一条记录,这可以当做全索引扫描的标志.

重置这些变量:flush status

`ANALYZE TABLE` 分析表结构

注: PROCEDURE ANALYZE() 语句在 MySQL 8.0 及以上版本中已被弃用，应使用 ANALYZE TABLE 语句来替代
MySQL8.0之前
- PROCEDURE ANALYZE() 是MySQL中的一个语句，用于获取表中各列值的分布信息.它分析表的内容，并返回有关每个列的统计信息，包括最小和最大值、值的平均值和标准差、不同值的数量以及值在不同值范围内的分布情况.
- PROCEDURE ANALYZE() 语句需要传入一个表名和一个可选的列名列表作为参数，并返回一个结果集，包括以下列:
  - Column_name: 分析的列名
  - Min_value: 列中的最小值
  - Max_value: 列中的最大值
  - Avg_value: 列中的平均值
  - Std_value: 列中的标准差值
- 假设有一个名为 users 的表，包含 id、name 和 age 三个列，可以使用 PROCEDURE ANALYZE() 语句来分析这个表中 age 列的分布情况.示例代码如下:
  1
  2
  # PROCEDURE ANALYZE(table_name, column_name);
  PROCEDURE ANALYZE(users, age);

MySQL8.0之后

# 语法结构
ANALYZE TABLE table_name [PARTITION(partition_name[, partition_name] ...)] [COLUMNS(col_name[, col_name] ...)]

ANALYZE TABLE table_name;

参考文献

MySQL Explain简介

Explain结果字段说明

id

select_type

table

type

type 类型的性能比较

possible_keys

key

key_len

ref

rows

filtered

Extra

explain format=tree,explain format=json,explain analyze

SHOW STAUS

ANALYZE TABLE 分析表结构