explain 语句的字段说明

整理使用 MySQL 的 explain 语句的用处以及其中中的字段说明，这里使用的是 MySQL 8.0.14 ，MySQL 5.7 之前的内容与之有些不同。

一、explain 语句的用处

查看 SQL 语句语句的执行计划，方便我们分析 SQL 语句或表结构的性能瓶颈，以及去调优。

下面简单展示 explain 语句： ( jobLevelId 建了索引)

 explain select * from employee where jobLevelId = 12;
 
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | jobId         | jobId | 5       | const |   50 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-------+---------+-------+------+----------+-------+

字段概要说明：

  • id : select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
  • select_type : 查询类型
  • table : 当前访问的表的表名
  • partitions : 匹配的分区
  • type (重要) : 访问类型
  • possible_keys : 显示可能应用在这张表中的索引
  • key : 实际使用到的索引，如果为NULL，则没有使用索引
  • key_len : 索引中使用的字节数
  • ref : 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
  • rows (核心指标) : 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。（越少越好）
  • filtered : 显示了通过条件过滤出的行数的百分比估计值
  • Extra : 包含不适合在其它列中显示但十分重要的额外信息

二、各字段的详细说明

1. id

多表联查时，其 id 列表示执行顺序，两条规则：

  • id 的值越大优先级越高，越先被执行
  • 其次 id 相等时是从上至下执行
explain select * from employee e,department d where e.departmentId = d.id and e.jobLevelId = (select id from joblevel where id = 9) 
# 为了方便展示 删除了最后两个字段，主要看 id
+----+-------------+----------+------------+--------+--------------------+---------+---------+----------------------+------+
| id | select_type | table    | partitions | type   | possible_keys      | key     | key_len | ref                  | rows |
+----+-------------+----------+------------+--------+--------------------+---------+---------+----------------------+------+
|  1 | PRIMARY     | e        | NULL       | ref    | departmentId,jobId | jobId   | 5       | const                |    9 |   
|  1 | PRIMARY     | d        | NULL       | eq_ref | PRIMARY            | PRIMARY | 4       | pmsys.e.departmentId |    1 |   
|  2 | SUBQUERY    | joblevel | NULL       | const  | PRIMARY            | PRIMARY | 4       | const                |    1 |   
+----+-------------+----------+------------+--------+--------------------+---------+---------+----------------------+------+

其执行顺序是： joblevel > employee > department

2. select_type

上面的例子展示了其中两种类型，一共有 6 种：

  • SIMPLE : 简单查询，不包含子查询或Union查询

  • PRIMARY : 查询中若包含任何复杂的子部分，最外层查询则被标记为主查询

  • SUBQUERY : 在 select 或 where 中包含的子查询

  • DERIVED : 在FROM列表中包含的子查询被标记为 DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

    备注：MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

    explain select * from (select Sname from students where Ssex = '男') s;
# 这里版本是 MySQL 5.1.40, 5.7+ 的显示的是 SIMPLE
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
|  2 | DERIVED     | students   | ALL    | NULL          | NULL | NULL    | NULL |    9 | Using where         |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+

  • UNION : 若第二个select出现在uion之后，则被标记为UNION

  • UNION RESULT : 从UNION表获取结果的select

    explain select * from employee where id = 1
union
select * from employee where id = 2;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | 
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------
|  1 | PRIMARY      | employee   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | 
|  2 | UNION        | employee   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | 
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL 
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------

3. table

当前访问的表的表名，参考以上例子的 table

4. partitions

匹配的分区，详细的没找到，从以上例子看出 5.7 之前没有这个字段

5. type (重要)

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

//最好到最差
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

备注：掌握以下10种常见的即可
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

一般到 eq_ref， ref 就行了

  • NULL : MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
  • system : 表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略
  • const : 表示通过索引一次就找到了，const 用于比较 primary key 或 uique 索引，因为只匹配一行数据，所以很快，如主键置于 where 列表中，MySQL就能将该查询转换为一个常量
  • eq_ref : 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
  • ref : 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体
  • ref_or_null : 类似 ref，但是可以搜索值为 NULL 的行
  • index_merge : 表示使用了索引合并的优化方法
  • range : 只检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引，一般就是在你的 where 语句中出现 between 、<> 、in 等的查询。
  • index : Full index Scan，Index 与 All 区别：index 只遍历索引树，通常比 All 快，因为索引文件通常比数据文件小，也就是虽然 All 和 index 都是读全表，但index 是从索引中读取的，而all是从硬盘读的。
  • ALL : Full Table Scan，将遍历全表以找到匹配行

6. possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

7. key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

8. key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

9. ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

10. rows (核心指标)

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

11. filtered

指返回结果的行占需要读到的行 ( rows 列的值)的百分比。按说 filtered 是个非常有用的值，因为对于 join 操作，前一个表的结果集大小直接影响了循环的次数。但是我的环境下测试的结果却是，filtered 的值一直是100%，也就是说失去了意义。

filtered 值只对 index 和 all 的扫描有效（这可以理解，其它场合，通常 rows 值就等于估算的结果集大小。)

12. Extra

性能从好到坏: useing index > usinh where > using temporary | using filesort

该列包含MySQL解决查询的详细信息,有以下部分情况（完整看官方文档）：

  • Using filesort : 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为“文件排序”

  • Using temporary : 使用了临时表保存中间结果，MySQL 在对结果排序时使用临时表，常见于排序 order by 和分组查询 group by

  • Using index : 表示相应的 select 操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错！

    如果同时出现using where，表明索引被用来执行索引键值的查找
    如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

  • Using where : SQL 使用了 where 条件过滤数据

    WHERE 子句用于限制哪些行匹配下一个表或发送给客户端。除非您专门打算从表中获取或检查所有行，那么如果这个 Extra 值不是 Using where，并且 type 是 All 或 index，则查询中可能出现错误。（机翻再修）

  • Using join buffer : 该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进

  • impossible where : where 子句的值总是 false，不能用来获取任何元组

  • distinct : 一旦 MySQL 找到了与行相联合匹配的行，就不再搜索了

  • Select tables optimized away : SELECT 操作已经优化到不能再优化了（ MySQL 根本没有遍历表或索引就返回数据了）

三、参考

一张图彻底搞懂MySQL的 explain

MySQL Explain详解

MySQL 8 官方文档 EXPLAIN Output Format 英文

posted @ 2021-04-10 21:03  东郊  阅读(335)  评论(0)  编辑  收藏  举报