快捷搜索：  汽车  科技
当前位置： 爱玩科技 > 科技 > 正文

mysqlexplain优缺点：一文学会MySQL的explain工具

威哥 239

mysqlexplain优缺点：一文学会MySQL的explain工具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 得到下面的输出：还是来个快速入门的案例比较直观，依次在mysql的命令行执行下面几条语句(建库、建表sql脚本见下面的数据准备部分)：EXPLAIN is used to obtain a query execution plan (that is an explanation of how MySQL would execute a

mysqlexplain优缺点：一文学会MySQL的explain工具(1)

mysqlexplain优缺点：一文学会MySQL的explain工具(2)

开篇说明#

(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 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)

先别急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 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 ); 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: 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)

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

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 | <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列#

相关文档：
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 | <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)


作者： 行无际

原文地址：https://www.cnblogs.com/itwild/p/13424113.html

网站首页

返回栏目

猜您喜欢：

相关文章