开篇说明#
(1) 本文将细致介绍MySQL的explain工具，是下一篇《一文读懂MySQL的索引机制及查询优化》的准备篇。
(2) 本文主要基于MySQL5.7版本(https://dev.mysql.com/doc/refman/5.7/en/)，MySQL8.x版本可另行翻阅对应版本文档(https://dev.mysql.com/doc/refman/8.0/en/)。
(3) 演示过程中的建库、建表、建索引等语句仅为了测试explain工具的使用，并未考虑实际应用场景的合理性。
explain工具介绍#
相关文档：
https://dev.mysql.com/doc/refman/5.7/en/explain.html
https://dev.mysql.com/doc/refman/5.7/en/using-explain.html
EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).
简单翻译一下，就是explain用于获取查询执行计划(即MySQL是如何执行一个查询的)。
工作中，我们会遇到慢查询，这个时候我们就可以在select语句之前增加explain关键字，模拟MySQL优化器执行SQL语句，从而分析该SQL语句有没有用上索引、是否全表扫描、能否进一步优化等。
还是来个快速入门的案例比较直观，依次在mysql的命令行执行下面几条语句(建库、建表sql脚本见下面的数据准备部分)：
Copymysql> use `explain_test`;mysql> select * from tb_hero where hero_name = '李寻欢' and book_id = 1;mysql> explain select * from tb_hero where hero_name = '李寻欢' and book_id = 1;mysql> show warnings G得到下面的输出：
Copymysql> use `explain_test`;Database changedmysql> select * from tb_hero where hero_name = '李寻欢' and book_id = 1;+---------+-----------+--------------+---------+| hero_id | hero_name | skill | book_id |+---------+-----------+--------------+---------+| 1 | 李寻欢 | 小李飞刀 | 1 |+---------+-----------+--------------+---------+1 row in set (0.00 sec)mysql> explain select * from tb_hero where hero_name = '李寻欢' and book_id = 1;+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+| 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)mysql> show warnings G*************************** 1. row *************************** Level: Note Code: 1003Message: /* select#1 */ select `explain_test`.`tb_hero`.`hero_id` AS `hero_id`,`explain_test`.`tb_hero`.`hero_name` AS `hero_name`,`explain_test`.`tb_hero`.`skill` AS `skill`,`explain_test`.`tb_hero`.`book_id` AS `book_id` from `explain_test`.`tb_hero` where ((`explain_test`.`tb_hero`.`book_id` = 1) and (`explain_test`.`tb_hero`.`hero_name` = '李寻欢'))1 row in set (0.00 sec)先别急explain语句输出结果每一列表示什么意思(后面会具体描述)，用show warnings命令可以得到优化后的查询语句大致长什么样子。
补充：
- 有关show warnings更详细的使用见https://dev.mysql.com/doc/refman/5.7/en/show-warnings.html
- 有关获取explain额外的输出信息见https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html
原SQL语句：
Copyselect * from tb_hero where hero_name = '李寻欢' and book_id = 1;优化后的SQL语句：
Copyselect `explain_test`.`tb_hero`.`hero_id` AS `hero_id`, `explain_test`.`tb_hero`.`hero_name` AS `hero_name`, `explain_test`.`tb_hero`.`skill` AS `skill`, `explain_test`.`tb_hero`.`book_id` AS `book_id`from `explain_test`.`tb_hero`where ((`explain_test`.`tb_hero`.`book_id` = 1) and (`explain_test`.`tb_hero`.`hero_name` = '李寻欢'))可以看出，MySQL优化器把*优化成具体的列名，另外把我where中的两个过滤条件hero_name、book_id先后顺序调换了一下，这种顺序调换是概率性事件还是另有文章？
(哈哈哈，(●´ω｀●)留个悬念，本篇仅介绍explain工具，读了下篇《一文读懂MySQL的索引机制及查询优化》后自然豁然开朗)
数据准备#
为了方便演示explain工具的使用以及输出结果的含义，准备了一些测试数据，初始化sql脚本如下:
Copy-- ------------------------------ create database-- ----------------------------DROP database IF EXISTS `explain_test`;create database `explain_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;-- switch databaseuse `explain_test`;-- ------------------------------ table structure for `tb_book`-- ----------------------------DROP TABLE IF EXISTS `tb_book`;CREATE TABLE `tb_book` ( `book_id` int(11) NOT NULL, `book_name` varchar(64) DEFAULT NULL, `author` varchar(32) DEFAULT NULL, PRIMARY KEY (`book_id`), UNIQUE KEY `uk_book_name` (`book_name`) USING BTREE, INDEX `idx_author` (`author`) USING BTREE);BEGIN;INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (1, '多情剑客无情剑', '古龙');INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (2, '笑傲江湖', '金庸');INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (3, '倚天屠龙记', '金庸');INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (4, '射雕英雄传', '金庸');INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (5, '绝代双骄', '古龙');COMMIT;-- ------------------------------ table structure for `tb_hero`-- ----------------------------DROP TABLE IF EXISTS `tb_hero`;CREATE TABLE `tb_hero` ( `hero_id` int(11) NOT NULL, `hero_name` varchar(32) DEFAULT NULL, `skill` varchar(64) DEFAULT NULL, `book_id` int(11) DEFAULT NULL, PRIMARY KEY (`hero_id`), INDEX `idx_book_id_hero_name`(`book_id`, `hero_name`) USING BTREE);BEGIN;INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (1, '李寻欢', '小李飞刀', 1);INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (2, '令狐冲', '独孤九剑', 2);INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (3, '张无忌', '九阳神功', 3);INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (4, '郭靖', '降龙十八掌', 4);INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (5, '花无缺', '移花接玉', 5);INSERT INTO `tb_hero`(`hero_id`, `hero_name`, `skill`, `book_id`) VALUES (6, '任我行', '吸星大法', 2);COMMIT;-- ------------------------------ Table structure for `tb_book_hero`-- ----------------------------DROP TABLE IF EXISTS `tb_book_hero`;CREATE TABLE `tb_book_hero` ( `book_id` int(11) NOT NULL, `hero_id` int(11) NOT NULL, `user_comment` varchar(255) DEFAULT NULL, PRIMARY KEY (`book_id`, `hero_id`) USING BTREE) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;BEGIN;INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (1, 1, '小李飞刀，例无虚发，夺魂索命，弹指之间');INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (2, 2, '令狐少侠留步!');INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (3, 3, '尝遍世间善恶，归来仍是少年');INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (4, 4, '我只要我的靖哥哥!');INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (5, 5, '风采儒雅亦坦荡，武艺精深兼明智。');INSERT INTO `tb_book_hero`(`book_id`, `hero_id`, `user_comment`) VALUES (2, 6, '有人就有恩怨，有恩怨就有江湖，人心即是江湖，你如何退出！');COMMIT;explain的输出结果#
相关文档：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
看一下官方文档显示的关于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';详情见：
https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html
https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html
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 | | 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 | | 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列是格式，表示当前查询依赖id=N的查询，需先执行id=N的查询。上面含select_type为DERIVED的查询就是这种情况，这里不再重复举例。
type列#
相关文档：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
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 | | 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 =, <>, >, >=, , 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)
作者： 行无际
233
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
