Explain-SQL语句分析

explain

mysql数据库下，为了判断一条sql是如何执行的，我们需要explain命令。 explain命令并不会真正去执行sql语句，而是对语句做一个分析。

explain 可以告诉我们什么

sql 如何使用索引

虽然我们在业务开发中会增加一些Key，但是我们并不知道这些key是否被mysql优化器所选用，所以我们需要用explain 执行一下，告诉我们索引是否真的被命中
可以查看关联表的执行顺序（join 操作）

Mysql在的优化器会分析我们输入的sql语句，优化表的关联顺序，比如我们的Sql是 A join B , 但是经过Mysql优化后，可能会变成 B joinA
需要扫描的行数

用法

explain 加上我们平时写的Sql

mysql> explain select * from a where id=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)


#以json形式返回
explain format=json select * from a where id=1

expain 返回结果解释

字段名	json格式名称	解释
`id`	`select_id`	返回结果标识符
`select_type`	None	查询类型
`table`	`table_name`	作用的表名称
`partitions`	`partitions`	匹配的分区，Mysql在创建InnoDB 表的时候是可以分区的
`type`	`access_type`	表的连接类型
`possible_keys`	`possible_keys`	可能用到的索引
`key`	`key`	真正选择的索引
`key_len`	`key_length`	索引的长度，理论上索引的长度
`ref`	`ref`	可用用到此值去查询索引列
`rows`	`rows`	抽样统计的扫描行数
`filtered`	`filtered`	返回行数和扫描行数之间的一个比值，抽样统计数据，作为参考
`Extra`	None	额外比较重要的信息

id 选择标志符 , id 有两种可能

一种是一组数字（比如id 1,2,3,4,5）

如果这组Id值相同，表示的执行计划的顺序是由上而下

如果这组Id值不同，数字越大，表示越优先被执行

#创建测试表语句
create table a(id int primary key auto_increment, f varchar(1))Engine=InnoDB default charset=utf8;
insert into a values(null, '1');
create table b select * from a;

# id相同的情况
mysql> explain select * from a join b on a.id = b.id \G;
*************************** 1. row ***************************
           id: 1
           ... 此处省略很多行
*************************** 2. row ***************************
           id: 1
		   ... 此处省略很多行
2 rows in set, 1 warning (0.00 sec)

如果Id为空，表示两位个sql语句Union后的结果集


# 最后一行 id 为null的情况
mysql> explain select * from a union select * from b \G;
... 省略两行
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary
3 rows in set, 1 warning (0.00 sec)

select_type

包含 SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY,、DERIVED、DEPENDENT DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION

SIMPLE

不包含子查询或者union操作

mysql> explain select * from a \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
  此处省略好多字符...
1 row in set, 1 warning (0.00 sec)

PRIMARY

查询中如果包含子查询，那么最外层的查询有可能被标记为 PRIMARY

mysql> explain select * from a where f in (select max(f) from b ) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
  此处省略好多字符...
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
  此处省略好多字符...

UNION

union 中的第二个或者随后的select 查询，不依赖于外部查询结果集

mysql> explain select * from a union all select * from b \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: index
possible_keys: NULL
          key: f
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

DEPENDENT UNION

union 中的第二个或者随后的select查询，依赖外部的查询结果集

#默认 a，b 表已在前面的示例中创建
create table c select * from b;

explain select * from a where f in (select f from b union all select f from c);

#返回结果中的 id =3 的才是 select * from c 这条语句的分析， 而这条语句也是 跟随的第二个或者随后的select语句
mysql> explain select * from a where f in (select f from b union all select f from c) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
      此处省略多行...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
          此处省略多行...
*************************** 3. row ***************************
           id: 3
  select_type: DEPENDENT UNION
         此处省略多行...
3 rows in set, 1 warning (0.00 sec)

#注意上面的sql语句 隐含了一些查询条件， 可以用 以下命令，看看mysql的优化器对此作出的解释
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test2`.`a`.`id` AS `id`,`test2`.`a`.`f` AS `f` from `test2`.`a` where <in_optimizer>(`test2`.`a`.`f`,<exists>(/* select#2 */ select `test2`.`b`.`f` from `test2`.`b` where (<cache>(`test2`.`a`.`f`) = `test2`.`b`.`f`) union all /* select#3 */ select `test2`.`c`.`f` from `test2`.`c` where (<cache>(`test2`.`a`.`f`) = `test2`.`c`.`f`)))
1 row in set (0.00 sec)
#我们可以看到 优化器自动加上了 查询条件

UNION RESULT

union 查询多张表返回组合的结果集

mysql> explain select * from a union select * from b \G
*************************** 1. row ***************************

  此处省略多行...
*************************** 2. row ***************************
此处省略多行...
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
  此处省略多行...

SUBQUERY

子查询中第一个select查询，不依赖外部查询结果集

mysql> explain select * from a where id = (select id from a limit 1) \G
*************************** 1. row ***************************
   此处省略多行...
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
  此处省略多行...

DEPENDENT SUBQUERY

子查询中第一个select查询，依赖外部查询结果集

#返回结果中的 id =3 的才是 select * from c 这条语句的分析， 而这条语句也是 跟随的第二个或者随后的select语句
mysql> explain select * from a where f in (select f from b union all select f from c) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
      此处省略多行...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
          此处省略多行...
*************************** 3. row ***************************
           id: 3
  select_type: DEPENDENT UNION
         此处省略多行...
3 rows in set, 1 warning (0.00 sec)

DERIVED

作用域 from 子句有子查询的情况， Mysql 会递归这些子查询，此结果放在临时表中。

mysql> explain select * from (select * from a union select * from b ) as c \G
*************************** 1. row ***************************
此处省略很多行
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
此处省略很多行
*************************** 3. row ***************************
此处省略很多行
*************************** 4. row ***************************
       此处省略很多行
4 rows in set, 1 warning (0.00 sec)

DEPENDENT DERIVED

from 中的子查询依赖外部查询结果集, 这个我没有在 mysql 8.0 上复现处理，貌似很多种情况，mysql 8.0 都做了优化
MATERIALIZED

物化子查询， Mysql 会在第一次使用到子查询的时候，将子查询内容转成临时表中

mysql> explain select count(1), f from a as a1 where f in (select f from a where f in ('h','a','n')) \G
*************************** 1. row ***************************
     此处省略很多行
*************************** 2. row ***************************
    此处省略很多行
*************************** 3. row ***************************
        id: 2
select_type: MATERIALIZED

UNCACHEABLE SUBQUERY

子查询不可被物化，每次都需要拿外表的每一行，对应子查询都会执行一次
UNCACHEABLE UNION

UNINO操作中，第二个或者以后的子句不能被物化操作

table

显示涉及到的某张表，也可以是几个表的集合，如下:
- <union*M*,*N*>: 由M和N查询union 后产生的结果集
- <derivedN>、<subquery*N`*>: 由Id为 N的查询产生的结果
partitions

Mysql InnoDB 存储引擎是可以分区的，如果是分区表会显示对应的分区号，不是分区表显示为null，以下网址是对应的分区表介绍的网址:

https://dev.mysql.com/doc/refman/8.0/en/partitioning-info.html
type（重要）

表的访问类型
- system
  
  表中只有一行数据的时候,显示此类型， Mysql 8.0 和以往数据库的优化策略不太相同，这个没有找到相应的例子
- conts
  
  确定只匹配一行的时候，显示此值
```
mysql> explain select * from a where id=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
```
- eq_ref
  
  唯一或者主键查找，对于每个索引键，表中只有一条记录与之匹配
- ref
  
  非唯一索引查找，返回匹配某个值的所有行
```
mysql> explain select * from a where f='a' \G
*************************** 1. row ***************************
 此处省略很多行
         type: ref
 此处省略很多行
```
- fulltext
  
  如果字段使用了全文索引，可能会显示的连接类型
- ref or null
  
  类似ref查找，但是附加了对null值列的查询
- index merge
  
  此连接类型使用了索引合并的优化方法
- range
  
  索引范围扫描，常见于between, > , < 这样的查询条件
- index
  
  Full Index Scan 全索引扫描，同all的区别是，遍历的是索引树
- all
  
  Full table Scan 全表扫描，这是效率最差的连接方式
索引的效率有高到底 system> const > eq_ref > ref > fulltext > ref or null > range > index > all
possible_keys

Mysql 查询过程中可能会用到的索引，查询所涉及到的索引都有可能被列出来，但不一定会被使用到
key

实际查询过程中会被使用到的索引，如果没有使用索引，此字段显示null，如果查询使用到了覆盖索引，可能这个字段只出现在key中，并不会出现在 possible_keys 中
key_len

表示， Mysql 使用到的字段最大可能的长度，如果使用联合索引，可能使用到的索引长度小于联合索引的总长度
ref

表示使用的哪些列或者常量与key字段一块，从数据库中选择出具体的行
row

统计查询需要扫描的行数， row 的大小是一个抽象统计结果，结果其实并不十分准确
filtered

返回结果的行数占需要读取行数的百分比，此行也依赖统计信息，所以并不十分准确，一般认为，值越大，效率越高，越说明实际返回的行数和扫描行数是相近的
Extra

表示其他列以外的额外信息，通常来说也很重要，可能的情况有很多，详细的情况可以查阅mysql的官方文档, 文档地址:

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information
- Distinct
  
  优化Distint 操作，在找到第一个匹配的元组后即停止找同样值的操作
- Using index
  
  使用覆盖索引进行查询，覆盖索引是一种非常高效的查询方式，他表示需要查询到的数据都可以通过索引数据结构返回，而不用回表操作
- Using temporary
  
  表示mysql需要临时表来存储结果集，常用于排序和分组查询
- Using filesort
  
  Mysql 无法利用索引直接完成排序，即排序字段并不是索引字段，这种情况有可能用到缓冲空间来进行排序
- Using Where 需要在Mysql服务器层使用where过滤数据

参考资料:

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html 【mysql 输出解释】

https://www.cnblogs.com/xuanzhi201111/p/4175635.html

https://www.cnblogs.com/micrari/p/6583482.html 【mysql执行计划总结】

https://blog.ops-coffee.cn/s/p5ukuh1yy3p4zrozvbmy1w 【MySQL EXPLAIN结果集分析 - 附带大量案例】

https://www.modb.pro/db/26155 【MySQL8.0 AntiJoin了解&分析性能方法揭秘】

https://docs.gitcode.net/mysql/guide/optimization/subquery-materialization.html 【使用物化优化子查询】

posted on 2022-09-03 22:28 指尖，写不尽阅读(136) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

指尖，写不尽