一文学会MySQL的explain工具

日行四善 07-01 116

前言：

而今各位老铁们对“mysqlexplainref”大体比较注意，各位老铁们都想要学习一些“mysqlexplainref”的相关知识。那么小编在网络上收集了一些对于“mysqlexplainref””的相关文章，希望朋友们能喜欢，你们一起来了解一下吧！

开篇说明#

(1) 本文将细致介绍MySQL的explain工具，是下一篇《一文读懂MySQL的索引机制及查询优化》的准备篇。

(2) 本文主要基于MySQL5.7版本()，MySQL8.x版本可另行翻阅对应版本文档()。

(3) 演示过程中的建库、建表、建索引等语句仅为了测试explain工具的使用，并未考虑实际应用场景的合理性。

explain工具介绍#

相关文档：

看一下官方文档显示的关于explain输出结果列(explain output columns)的含义：

ColumnJSON NameMeaningidselect_idThe SELECT identifierselect_typeNoneThe SELECT typetabletable_nameThe table for the output rowpartitionspartitionsThe matching partitionstypeaccess_typeThe join typepossible_keyspossible_keysThe possible indexes to choosekeykeyThe index actually chosenkey_lenkey_lengthThe length of the chosen keyrefrefThe columns compared to the indexrowsrowsEstimate of rows to be examinedfilteredfilteredPercentage of rows filtered by table conditionExtraNoneAdditional information

其中JSON Name指的是当设定FORMAT=JSON时，列名在json中显示的name，见下面的演示就明白了

Copymysql> explain select * from tb_book \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tb_book   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 5     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.00 sec)mysql> explain FORMAT=JSON select * from tb_book \G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "2.00"    },    "table": {      "table_name": "tb_book",      "access_type": "ALL",      "rows_examined_per_scan": 5,      "rows_produced_per_join": 5,      "filtered": "100.00",      "cost_info": {        "read_cost": "1.00",        "eval_cost": "1.00",        "prefix_cost": "2.00",        "data_read_per_join": "1K"      },      "used_columns": [        "book_id",        "book_name",        "author"      ]    }  }}1 row in set, 1 warning (0.00 sec)

下面重点看一下比较重要的几个字段。

id列#

id是select的唯一标识，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的，id值越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

为了验证上面的结论，临时关闭mysql5.7对子查询(sub queries)产生的衍生表(derived tables)的合并优化

Copyset session optimizer_switch='derived_merge=off';

详情见：

Copymysql> set session optimizer_switch='derived_merge=off';Query OK, 0 rows affected (0.00 sec)mysql> select (select count(1) from tb_book) as book_count, (select count(1) from tb_hero) as hero_count from (select * from tb_book_hero) as book_hero;+------------+------------+| book_count | hero_count |+------------+------------+|          5 |          6 ||          5 |          6 ||          5 |          6 ||          5 |          6 ||          5 |          6 ||          5 |          6 |+------------+------------+6 rows in set (0.00 sec)mysql> explain select (select count(1) from tb_book) as book_count, (select count(1) from tb_hero) as hero_count from (select * from tb_book_hero) as book_hero;+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+| id | select_type | table        | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+|  1 | PRIMARY     | <derived4>   | NULL       | ALL   | NULL          | NULL                  | NULL    | NULL |    6 |   100.00 | NULL        ||  4 | DERIVED     | tb_book_hero | NULL       | ALL   | NULL          | NULL                  | NULL    | NULL |    6 |   100.00 | NULL        ||  3 | SUBQUERY    | tb_hero      | NULL       | index | NULL          | idx_book_id_hero_name | 136     | NULL |    6 |   100.00 | Using index ||  2 | SUBQUERY    | tb_book      | NULL       | index | NULL          | uk_book_name          | 259     | NULL |    5 |   100.00 | Using index |+----+-------------+--------------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+4 rows in set, 1 warning (0.00 sec)mysql> set session optimizer_switch='derived_merge=on';Query OK, 0 rows affected (0.00 sec)

可见，查询语句中有4个select，先执行的是select * from tb_book_hero，然后执行select count(1) from tb_hero，再执行select count(1) from tb_book，最后执行select book_count, hero_count from book_hero

select_type列#

select_type表示的是查询类型，常见的包括SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION

(1) SIMPLE：简单查询(不包含子查询和UNION查询)

Copymysql> explain select * from tb_book where book_id = 1;+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | tb_book | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

(2) PRIMARY：复杂查询中最外层的查询

(3) SUBQUERY：包含在select中的子查询(不在from子句中)

(4) DERIVED：包含在from子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表(derived tables)

这3种select_type见下面的例子

Copymysql> set session optimizer_switch='derived_merge=off';Query OK, 0 rows affected (0.00 sec)mysql> explain select (select count(1) from tb_book) as book_count from (select * from tb_book_hero) as book_hero;+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+| id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+|  1 | PRIMARY     | <derived3>   | NULL       | ALL   | NULL          | NULL         | NULL    | NULL |    6 |   100.00 | NULL        ||  3 | DERIVED     | tb_book_hero | NULL       | ALL   | NULL          | NULL         | NULL    | NULL |    6 |   100.00 | NULL        ||  2 | SUBQUERY    | tb_book      | NULL       | index | NULL          | uk_book_name | 259     | NULL |    5 |   100.00 | Using index |+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+3 rows in set, 1 warning (0.00 sec)mysql> set session optimizer_switch='derived_merge=on';Query OK, 0 rows affected (0.00 sec)

(5) UNION：在UNION中的第二个和随后的select

Copymysql> select * from tb_book where book_id = 1 union all select * from tb_book where book_name = '笑傲江湖';+---------+-----------------------+--------+| book_id | book_name             | author |+---------+-----------------------+--------+|       1 | 多情剑客无情剑        | 古龙   ||       2 | 笑傲江湖              | 金庸   |+---------+-----------------------+--------+2 rows in set (0.00 sec)mysql> explain select * from tb_book where book_id = 1 union all select * from tb_book where book_name = '笑傲江湖';+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+|  1 | PRIMARY     | tb_book | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL  ||  2 | UNION       | tb_book | NULL       | const | uk_book_name  | uk_book_name | 259     | const |    1 |   100.00 | NULL  |+----+-------------+---------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)

table列#

table表示查询涉及的表或衍生表。

常见table列是<derivenN>格式，表示当前查询依赖id=N的查询，需先执行id=N的查询。上面含select_type为DERIVED的查询就是这种情况，这里不再重复举例。

type列#

相关文档：

type列是判断查询是否高效的重要依据，我们可以通过type字段的值，判断此次查询是全表扫描还是索引扫描等，进而进一步优化查询。

一般来说表示查询性能最优到最差依次为：NULL > system > const > eq_ref > ref > range > index > ALL

前面的几种类型都是利用到了索引来查询数据, 因此可以过滤部分或大部分数据, 查询效率自然就比较高了。

而后面的index类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比ALL类型稍快。

所以，应当尽可能地保证查询达到range级别，最好达到ref。

(0) NULL: 不用访问表或者索引，直接就能得到结果，如：在索引列中选取最大值，执行时不需要再访问表

Copymysql> explain select max(book_id) from tb_book;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+1 row in set, 1 warning (0.00 sec)

(1) system：The table has only one row. This is a special case of the const join type.

当查询的表只有一行的情况下，system是const类型的特例，

(2) const：It is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values.

针对主键或唯一索引的等值查询扫描, 最多只返回一行数据。const查询速度非常快, 因为它仅仅读取一次即可。

关于type列为system、const的情况，见下面的示例：

Copymysql> set session optimizer_switch='derived_merge=off';Query OK, 0 rows affected (0.00 sec)mysql> explain select * from (select * from tb_book where book_id = 5) as book;+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  ||  2 | DERIVED     | tb_book    | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> set session optimizer_switch='derived_merge=on';Query OK, 0 rows affected (0.00 sec)

(3) eq_ref：It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

此类型通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是=，查询效率较高。

Copymysql> select tb_hero.*, tb_book_hero.user_comment from tb_book_hero, tb_hero where tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+| hero_id | hero_name | skill        | book_id | user_comment                                                                         |+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+|       2 | 令狐冲    | 独孤九剑     |       2 | 令狐少侠留步!                                                                        ||       6 | 任我行    | 吸星大法     |       2 | 有人就有恩怨，有恩怨就有江湖，人心即是江湖，你如何退出！                             |+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> explain select tb_hero.*, tb_book_hero.user_comment from tb_book_hero, tb_hero where tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                               | rows | filtered | Extra |+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+|  1 | SIMPLE      | tb_book_hero | NULL       | ref    | PRIMARY       | PRIMARY | 4       | const                             |    2 |   100.00 | NULL  ||  1 | SIMPLE      | tb_hero      | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | explain_test.tb_book_hero.hero_id |    1 |   100.00 | NULL  |+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> select tb_hero.*, tb_book_hero.user_comment from tb_book_hero join tb_hero on tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+| hero_id | hero_name | skill        | book_id | user_comment                                                                         |+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+|       2 | 令狐冲    | 独孤九剑     |       2 | 令狐少侠留步!                                                                        ||       6 | 任我行    | 吸星大法     |       2 | 有人就有恩怨，有恩怨就有江湖，人心即是江湖，你如何退出！                             |+---------+-----------+--------------+---------+--------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> explain select tb_hero.*, tb_book_hero.user_comment from tb_book_hero join tb_hero on tb_book_hero.book_id = 2 and tb_book_hero.hero_id = tb_hero.hero_id;+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                               | rows | filtered | Extra |+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+|  1 | SIMPLE      | tb_book_hero | NULL       | ref    | PRIMARY       | PRIMARY | 4       | const                             |    2 |   100.00 | NULL  ||  1 | SIMPLE      | tb_hero      | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | explain_test.tb_book_hero.hero_id |    1 |   100.00 | NULL  |+----+-------------+--------------+------------+--------+---------------+---------+---------+-----------------------------------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)

(4) ref: It is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value).

相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的最左前缀，可能会找到多个符合条件的行。

简单的select查询，author列上建有普通索引（非唯一索引）

Copymysql> explain select * from tb_book where author = '古龙';+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | tb_book | NULL       | ref  | idx_author    | idx_author | 131     | const |    2 |   100.00 | NULL  |+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

关联表查询，tb_book_hero表使用了联合主键PRIMARY KEY (book_id, hero_id)，这里使用到了左边前缀book_id进行过滤。

Copymysql> explain select * from tb_book_hero where book_id = 3;+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | tb_book_hero | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

(5) range: It can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators

扫描部分索引(范围扫描)，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>、in等查询

Copymysql> explain select * from tb_book where book_id > 3;+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|  1 | SIMPLE      | tb_book | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

(6) index：the index tree is scanned, MySQL can use this type when the query uses only columns that are part of a single index.

表示全索引扫描(full index scan), 和ALL类型类似, 只不过ALL类型是全表扫描, 而index类型则仅仅扫描所有的索引, 而不扫描数据.

Copymysql> explain select book_name from tb_book;+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | tb_book | NULL       | index | NULL          | uk_book_name | 259     | NULL |    5 |   100.00 | Using index |+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

上面的例子中, 我们查询的book_name字段上恰好有索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据。因此这样的情况下, type的值是index, 并且Extra的值大多是Using index。

(7) ALL: A full table scan is done

表示全表扫描, 这个类型的查询是性能最差的查询之一。通常来说, 我们的查询不应该出现ALL类型的查询, 因为这样的查询在数据量大的情况下, 严重降低数据库的性能。如果一个查询是ALL类型查询, 那么大多可以对相应的字段添加索引来避免。

Copymysql> explain select * from tb_hero where hero_name = '令狐冲';+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | tb_hero | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

possible_keys列#

表示MySQL在查询时, 能够使用到的索引。注意, 即使有些索引在possible_keys中出现, 但是并不表示此索引会真正地被MySQL使用到。MySQL在查询时具体使用了哪些索引, 由key字段决定。

key列#

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL。

key_len列#

表示查询优化器使用了索引的字节数，这个字段可以评估联合索引是否完全被使用, 或只有最左部分字段被使用到。

举例来说，tb_hero表的联合索引idx_book_id_hero_name由book_id和hero_name两个列组成，int类型占4字节，另外如果字段允许为NULL，需要1字节记录是否为NULL，通过结果中的key_len=5(tb_hero.book_id允许为NULL)可推断出查询使用了第一个列book_id列来执行索引查找；再拿tb_book_hero表联合主键PRIMARY KEY (book_id, hero_id)举例，通过key_len=4(tb_book_hero.book_id不允许为NULL)可推断出查询使用了第一个列book_id列来执行索引查找

Copymysql> explain select * from tb_hero where book_id = 2;+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | tb_hero | NULL       | ref  | idx_book_id_hero_name | idx_book_id_hero_name | 5       | const |    2 |   100.00 | NULL  |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from tb_book_hero where book_id = 2;+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+|  1 | SIMPLE      | tb_book_hero | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    2 |   100.00 | NULL  |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)

key_len的计算规则如下:

字符串:char(n): n字节长度varchar(n): 如果是utf8编码, 则是3n + 2字节; 如果是utf8mb4编码, 则是4n + 2字节.数值类型:TINYINT: 1字节SMALLINT: 2字节MEDIUMINT: 3字节INT: 4字节BIGINT: 8字节时间类型DATE: 3字节TIMESTAMP: 4字节DATETIME: 8字节字段属性:NULL属性占用一个字节如果一个字段是NOT NULL的, 则没有此属性

再看下面的计算:

4 [book_id是int类型] + 1 [book_id允许为NULL] + (4 * 32 + 2) [hero_name是varchar32,且用的是utf8mb4编码] + 1 [hero_name允许为NULL] = 136

Copymysql> explain select * from tb_hero where book_id = 2 and hero_name = '令狐冲';+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+|  1 | SIMPLE      | tb_hero | NULL       | ref  | idx_book_id_hero_name | idx_book_id_hero_name | 136     | const,const |    1 |   100.00 | NULL  |+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

ref列#

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

显示的是哪个字段或常数与key一起被使用

rows列#

MySQL查询优化器根据统计信息, 估算SQL要查找到结果集需要扫描读取的数据行数，注意这个不是结果集里的行数。这个值非常直观显示SQL的效率好坏, 原则上rows越少越好。

Extra列#

这一列展示的是额外信息。常见的重要值如下:

(1) Using index

表示查询在索引树中就可查到所需数据, 不用扫描表数据文件

Copymysql> explain select hero_id from tb_book_hero where book_id = 2;+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table        | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | tb_book_hero | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    2 |   100.00 | Using index |+----+-------------+--------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)mysql> explain select book_id  from tb_book where author = '金庸';+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | tb_book | NULL       | ref  | idx_author    | idx_author | 131     | const |    3 |   100.00 | Using index |+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

(2) Using where

查询的列没有全部被索引覆盖

Copymysql> explain select book_id, book_name from tb_book where author = '金庸';+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | tb_book | NULL       | ALL  | idx_author    | NULL | NULL    | NULL |    5 |    60.00 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

(3) Using temporary

查询有使用临时表，一般出现于排序、分组、多表join、distinct查询等等。

举例子如下：tb_book表对book_name字段建立了唯一性索引，这时候distinct查询Extra列为Using index; tb_hero表的skill字段上没有任何索引，这时候distinct查询Extra列为Using temporary

Copymysql> select distinct book_name from tb_book;+-----------------------+| book_name             |+-----------------------+| 倚天屠龙记            || 多情剑客无情剑        || 射雕英雄传            || 笑傲江湖              || 绝代双骄              |+-----------------------+5 rows in set (0.00 sec)mysql> explain select distinct book_name from tb_book;+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | tb_book | NULL       | index | uk_book_name  | uk_book_name | 259     | NULL |    5 |   100.00 | Using index |+----+-------------+---------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> select distinct skill from tb_hero;+-----------------+| skill           |+-----------------+| 小李飞刀        || 独孤九剑        || 九阳神功        || 降龙十八掌      || 移花接玉        || 吸星大法        |+-----------------+6 rows in set (0.00 sec)mysql> explain select distinct skill from tb_hero;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+|  1 | SIMPLE      | tb_hero | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+1 row in set, 1 warning (0.00 sec)

(4) Using filesort

表示MySQL不能通过索引顺序达到排序效果，需额外的排序操作，数据较小时在内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

Copymysql> explain select book_id, hero_name from tb_hero order by hero_name;+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                       |+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+|  1 | SIMPLE      | tb_hero | NULL       | index | NULL          | idx_book_id_hero_name | 136     | NULL |    6 |   100.00 | Using index; Using filesort |+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-----------------------------+1 row in set, 1 warning (0.00 sec)mysql> explain select book_id, hero_name from tb_hero order by book_id, hero_name;+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | tb_hero | NULL       | index | NULL          | idx_book_id_hero_name | 136     | NULL |    6 |   100.00 | Using index |+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

tb_hero表上有联合索引INDEX idx_book_id_hero_name(book_id, hero_name) USING BTREE

但是order by hero_name, 不能使用索引进行优化(下一篇博客会介绍联合索引的结构), 进而会产生Using filesort

如果将排序依据改为order by book_id, hero_name, 就不会出现Using filesort了。

(5) Select tables optimized away

比如下面的例子：

Copymysql> explain select min(book_id), max(book_id) from tb_book;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+1 row in set, 1 warning (0.00 sec)

作者：行无际

原文地址：

本文地址：http://www.longkongtuishu.com/cae34BARsBFEBD1ZT.html

标签： #mysqlexplainref #mysql中switch #mysqlsub