失眠网 > 【MySQL附录】A4：MySQL中执行计划explain详解

【MySQL附录】A4：MySQL中执行计划explain详解

时间：2021-02-27 18:52:21

MySql使用explain关键字可以模拟优化器执行sql语句，我们就能够知道MySql会如何处理咱们的sql，

可以根据explain的分析结果和MySql底层数据结构优化sql。文章内容基于MySql 8.0.18分析，

不同MySql版本可能有差别。如果用的是MySQL5.6.x和MySQL5.7.x，差别应该不会很大。

MySql索引底层数据结构和算法：/yhl_jxy/article/details/88392411

explain详解

数据脚本准备，有一个teacher（教师表），class（班级表），class_teacher（班级与对应上课老师关系表）。

这里不涉及到实际业务，只是通过实例分析explain相关内容。

# 教师DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`id` int(11) NOT NULL comment '教师编号',`teacher_name` varchar(45) DEFAULT NULL comment '教师姓名',`entry_time` date DEFAULT NULL comment '入职时间',PRIMARY KEY (`id`)) ENGINE=InnoDB;INSERT INTO `teacher` (`id`, `teacher_name`, `entry_time`)VALUES (1,'a','-09-22'), (2,'b','-10-26'), (3,'c','-12-25');# 班级DROP TABLE IF EXISTS `class`;CREATE TABLE `class` (`id` int(11) NOT NULL AUTO_INCREMENT comment '班级编号',`class_name` varchar(10) DEFAULT NULL comment '班级名称',PRIMARY KEY (`id`),KEY `idx_class_name` (`class_name`)) ENGINE=InnoDB;INSERT INTO `class` (`id`, `class_name`)VALUES (1,'class1'),(2,'class2'),(3,'class3');# 班级教师关系表DROP TABLE IF EXISTS `class_teacher`;CREATE TABLE `class_teacher` (`id` int(11) NOT NULL comment '主键',`class_id` int(11) NOT NULL comment '班级编号',`teacher_id` int(11) NOT NULL comment '教师编号',`remark` varchar(255) DEFAULT NULL comment '备注',PRIMARY KEY (`id`),KEY `idx_class_teacher_id` (`class_id`,`teacher_id`)) ENGINE=InnoDB ;INSERT INTO `class_teacher` (`id`, `class_id`, `teacher_id`, `remark`)VALUES (1, 1, 1, '一班的1号教师'), (2, 1, 2, '一班的2号教师'), (3, 2, 1, '二班的1号教师');

先用explain关键字执行下，看看能看到啥？

mysql> explain select * from teacher \G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: teacherpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 3filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)mysql> show warnings \G;*************************** 1. row ***************************Level: NoteCode: 1003Message: /* select#1 */ select `test`.`teacher`.`id` AS `id`,`test`.`teacher`.`teacher_name` AS `teacher_name`,`test`.`teacher`.`entry_time` AS `entry_time` from `test`.`teacher`1 row in set (0.00 sec)ERROR: No query specified

id ：编号

select_type ：查询类型

table ：表名

partitions ：分区

type ：类型

possible_keys ：显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的

key ：实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引

key_len ：表示索引中使用的字节数,而通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即,key_len是根据表定义计算而得么不是通过表内检索出的。

ref ：显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值

rows ：根据表统计信息及索引选用情况,大只估算出找到所需的记录所需要读取的行数

filtered ：过滤

Extra ：额外的信息

id列（重要）

id列的编号一般情况下是select的序列号，一般有几个select就有几个id，并且id的顺序按select出现的顺序增长。

id值越大执行优先级越高，id值相同则从上往下执行，id相同由上至下，id为null最后执行。

mysql> explain select (select id from teacher limit 1) from class \G;*************************** 1. row ***************************id: 1select_type: PRIMARYtable: classpartitions: NULLtype: indexpossible_keys: NULLkey: idx_class_namekey_len: 43ref: NULLrows: 3filtered: 100.00Extra: Using index*************************** 2. row ***************************id: 2select_type: SUBQUERYtable: teacherpartitions: NULLtype: indexpossible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 3filtered: 100.00Extra: Using index2 rows in set, 1 warning (0.00 sec)

select_type列

simple:简单的select查询,查询中不包含子查询或union查询

primary:查询中若包含任何复杂的子部分,最外层查询则被标记为primary，相对于子查询。

subquery 在select 或where 列表中包含了子查询

derived 在from列表中包含的子查询被标记为derived,mysql会递归这些子查询,把结果放在临时表里

union 做第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived

union result 从union表获取结果的select

table列

table为访问的表名。根据上面id或select_type实例观察，总结表名的规则。

1）from子查询时，table为子表名或<derivenN>格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

mysql> explain select * from (select 1) tmp;+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+| 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| NULL | NULL | NULL | NULL| NULL | NULL | NULL | NULL |NULL | No tables used |+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+2 rows in set, 1 warning (0.00 sec)

2）当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

mysql> explain select 1 union select 2;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table| partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| 1 | PRIMARY| NULL | NULL | NULL | NULL| NULL | NULL | NULL | NULL |NULL | No tables used || 2 | UNION | NULL | NULL | NULL | NULL| NULL | NULL | NULL | NULL |NULL | No tables used || NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL| NULL | NULL | NULL | NULL |NULL | Using temporary |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+3 rows in set, 1 warning (0.00 sec)

partitions列

如果查询基于分区表，将会显示访问的是哪个区。

type列（重要）

type列表示关联类型或访问类型，MySql决定如何查找表中的行，以及查询表数据行记录的大概范围。

type常见类型从最优到最差：system > const > eq_ref > ref > range > index > ALL，

system:表只有一行记录,这是const类型的特例,平时不会出现const:表示通过索引一次就找到了,const即常量,它用于比较primary key或unique索引,因为只匹配一行数据,所以效率很快,如将主键置于where条件中,mysql就能将该查询转换为一个常量eq_ref:使用唯一性索引扫描,对于每个索引键,表中最多只有一条记录与之匹配,常见于primary key 或 unique key索引扫描。与const区别：ef_ref出现多表关联情况。ref: 使用非唯一性索引或者唯一索引的部分前缀查找，与某个单独值匹配，它可能会找到多个符合条件的结果行。range:从索引中检索给定范围的行,使用一个索引来选择行,如where语句中出现了between,<,>,in等查询,这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。index: index遍历索引树,这通常比All快,因为索引文件通常比数据文件小,index是从索引中读取（select的结果集要得数据从索引中能都找到，不用全表扫描）,all从硬盘中读取ALL:全表扫描,是最差的一种查询类型

分别举例如下：

mysql> explain select * from (select * from class where id = 1) tmp;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE| class | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from class_teacher left join class on class_teacher.class_id = class.id;+----+-------------+---------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra |+----+-------------+---------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------+| 1 | SIMPLE| class_teacher | NULL | ALL | NULL| NULL | NULL | NULL | 3 | 100.00 | NULL || 1 | SIMPLE| class | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.class_teacher.class_id | 1 | 100.00 | NULL |+----+-------------+---------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> explain select * from class where class_name = 'class1';+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+| 1 | SIMPLE| class | NULL | ref | idx_class_name | idx_class_name | 43| const | 1 | 100.00 | Using index |+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from teacher where id between 1 and 5;+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | SIMPLE| teacher | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from class;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+| 1 | SIMPLE| class | NULL | index | NULL| idx_class_name | 43| NULL | 3 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from teacher;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE| teacher | NULL | ALL | NULL| NULL | NULL | NULL | 3 | 100.00 | NULL |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

如果为：nullMySql优化器能够在优化阶段分解查询语句，在执行阶段就不用访问表或索引

一般得保证查询时能达到range级别，如果能做到ref级别，就更好了。

possible_keys列

possible_keys显示可能用到的索引，该列值可能为空，也可能不为空。

1）不为null时

当不为null时，会显示可能用到的索引。

2）为null时

当该列值为null时，表示不会用到索引。当出现这种情况时，看下where条件是否用到了索引字段，

key列（重要）

key列值显示MySql实际使用到的索引。如果没有使用索引，该列值为null。

key_len列

MySql索引使用的字节数，通过该值计算出使用了索引中的哪些列。

比如class_teacher表中，有联合索引idx_class_teacher_id，由class_id和teacher_id字段组成。

使用一个索引字段或使用两个索引字段来观察ken_len的值。

从上面sql可以看到，key_len值分别为4和8，这个值是int=4个字节算出来的，

因为我们的class_id和teacher_id都是int类型，所以key_len存的是使用到的索引字段类型字节长度。

第一个查询sql只用class_id索引字段，所以key_len为4，第二个查询sql用到了class_id和teacher_id，

所以key_len计算为8，但是如果字段允许为null，则需要加1。key_len的计算规则如下：

key_len计算规则如下：

1）字符串

char(n)：n字节长度；

varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2；

2）数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

3）时间类型

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL。（这是为什么会比正常计算多1的原因）。

索引最大长度是768字节，当字符串过长时，MySql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，一般比较常见为const或字段名称。

rows列

MySql大致估计sql查询结果的数据条数（不是很精准）

filtered列

filtered的值指返回结果的行占需要读到的行(rows列的值)的百分比。

mysql> select teacher_name from teacher;+--------------+| teacher_name |+--------------+| a || b || c |+--------------+3 rows in set (0.00 sec)mysql> explain select teacher_name from teacher where teacher_name = 'a';+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE| teacher | NULL | ALL | NULL| NULL | NULL | NULL | 3 | 33.33 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

但是我换一个条件查询：

这个filtered是100，因为where 条件 id是主键，或者别的索引列，它直接根据索引查找。所以读到的行，不是全表数据总量。

如果索引上查找某个值，它认为你只会去读取一条数据，并且你也只能查询出一条数据，所以是100。

Extra列（重要）

Extra显示额外信息，就像你做excel一样，最后搞个备注列。备注很多时候很重要，

Extra列也很重要，通常有如下几类重要信息需要关注。

1）Using index

Extra显示Using Index，说明用到了索引，是性能高的表现。一般出现在查询的列被索引列覆盖，

并且where筛选条件是索引的前导列

2）Using where

Extra显示Using where，表示没有用到索引，查询的列未被索引列覆盖，where筛选条件非索引的前导列。

3）Using where Using index

Extra显示Using whre Using index，表示查询的列被索引列覆盖，并且where筛选条件是索引列之一但

不是索引的前导列，说明无法直接通过索引查找查询到符合条件的数据。

4）NULL

Extra显示null，表示查询的列未被索引列覆盖，并且where筛选条件是索引的前导列，说明用到了索引，

但是部分字段未被索引列覆盖，必须通过“回表”来实现，所以不是纯粹地用到了索引，也不是完全没用到索引。

5）Using index condition

Extra显示Using index condition与Using where类似，查询的列不完全被索引列覆盖，where条件中是一个前导列的范围。

6）Using temporary

Extra显示Using temporaty表示MySql需要创建一张临时表来处理查询。

中间MySql处理过程需要多处理一个临时表，一般这种情况是需要优化处理的。

优化处理一般使用索引优化手段会多些。

在teacher.teacher_name没有索引，如果对该字段去重处理distinct，则MySql会把查询结果集建临时表，然后再去重处理，中间多了创建临时表的过程，效率低；

咱们在开始建表的时候，做了一个class.class_name建立了idx_class_name索引，通过class的class_name建立索引类比teacher上没有建立索引的效果。

从比较结果知道，name上建立索引的distinct会走索引去重，而不会创建临时表，索引效率高。

7）Using filesort

Extra显示Using filesort表示MySql会对查询结果使用外部索引排序，

而没有按索引次序从表里读取行，没有用索引。此时MySql会根据联接类型扫描所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。MySql绕着弯干这么多事，效率很低，如果你的order by出现Using filesort，一般需要优化了。

在teacher.teacher_name未创建索引，会扫描teacher整个表，保存排序关键字teacher_name和对应的id，然后排序teacher_name并检索行记录。

在class.class_name建立了idx_class_name索引，此时查询时Extra是Using index，使用了索引列排序，效率高效。

EXPLAIN FORMAT=TREE

在explain 后面增加format=tree参数，可以按树形方式展示执行计划，并且信息内容比上面的丰富。例如：

| -> Nested loop inner join-> Filter: ((employees.id > 500) and (employees.fname is not null)) (cost=16251.35 rows=53335)-> Table scan on employees (cost=16251.35 rows=160021)-> Single-row index lookup on <subquery2> using <auto_distinct_key> (fname=employees.fname)-> Materialize with deduplication-> Filter: (employees.fname is not null) (cost=16251.35 rows=53335)-> Filter: (employees.id < 100) (cost=16251.35 rows=53335)-> Table scan on employees (cost=16251.35 rows=160021)|

EXPLAIN ANALYZE

MySQL 8.0.18引入EXPLAIN ANALYZE，该语句实际运行语句并生成EXPLAIN输出，以及有关优化器的期望如何与实际执行相匹配的时间以及基于迭代器的其他信息。对于每个迭代器，提供以下信息：

预估cost

预估结果集rows条数

返回第一行的耗时（以毫秒为单位）

返回全部行的耗时（以毫秒为单位）

实际结果集rows条数

mysql> explain analyze select * from class order by class_name;+------------------------------------------------------------------------------------------------------------+| EXPLAIN|+------------------------------------------------------------------------------------------------------------+| -> Index scan on class using idx_class_name (cost=0.55 rows=3) (actual time=0.018..0.022 rows=3 loops=1)|+------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

这个工具刚刚推出，其可靠性和可用性，还需要进一步验证。

最后，建议实际使用时，这三个命令结合起来一起使用。

如果觉得《【MySQL附录】A4：MySQL中执行计划explain详解》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。