MySQL的explain工具
explain工具介绍
简单翻译一下，就是explain用于获取查询执行计划（即MySQL是如何执行一个查询的）。
工作中，我们会遇到慢查询，这个时候我们就可以在select语句之前增加explain关键字，模拟MySQL优化器执行SQL语句，从而分析该SQL语句有没有用上索引、是否全表扫描、能否进一步优化等。
还是来个快速入门的案例比较直观，依次在mysql的命令行执行下面几条语句(建库、建表sql脚本见下面的数据准备部分)：
mysql> 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
得到下面的输出
mysql> use `explain_test`; Database changed mysql> 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: 1003 Message: /* 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)
原SQL语句：
select * from tb_hero where hero_name = '李寻欢' and book_id = 1;
优化后的SQL语句：
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` = '李寻欢'))
可以看出，MySQL优化器把*优化成具体的列名，另外把我where中的两个过滤条件hero_name、book_id先后顺序调换了一下，这种顺序调换是概率性事件还是另有文章
数据准备
为了方便演示explain工具的使用以及输出结果的含义，准备了一些测试数据，初始化sql脚本如下:
-- ---------------------------- -- create database -- ---------------------------- DROP database IF EXISTS `explain_test`; create database `explain_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- switch database use `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 ) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 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 ) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 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的输出结果
其中JSON Name指的是当设定FORMAT=JSON时，列名在json中显示的name，见下面的演示就明白了
mysql> explain select * from tb_book \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb_book partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 100.00 Extra: NULL 1 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)
select_type列
select_type表示的是查询类型，常见的包括SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION
(1) SIMPLE：简单查询(不包含子查询和UNION查询)
mysql> 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见下面的例子
mysql> 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
mysql> 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)
type列
type列是判断查询是否高效的重要依据，我们可以通过type字段的值，判断此次查询是全表扫描还是索引扫描等，进而进一步优化查询。
一般来说表示查询性能最优到最差依次为：NULL > system > const > eq_ref > ref > range > index > ALL
前面的几种类型都是利用到了索引来查询数据, 因此可以过滤部分或大部分数据, 查询效率自然就比较高了。
而后面的index类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比ALL类型稍快。
所以，应当尽可能地保证查询达到range级别，最好达到ref。
(0) NULL: 不用访问表或者索引，直接就能得到结果，如：在索引列中选取最大值，执行时不需要再访问表
mysql> 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的情况，见下面的示例：
mysql> 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查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是=，查询效率较高。
mysql> 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列上建有普通索引（非唯一索引）
mysql> 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进行过滤。
mysql> 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等查询
mysql> 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类型则仅仅扫描所有的索引, 而不扫描数据.
mysql> 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类型查询, 那么大多可以对相应的字段添加索引来避免。
mysql> 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字段决定。
Extra列
这一列展示的是额外信息。常见的重要值如下:
(1) Using index
表示查询在索引树中就可查到所需数据, 不用扫描表数据文件
mysql> 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
查询的列没有全部被索引覆盖
mysql> 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
mysql> 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)
