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 返回结果解释

• 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

  1. SIMPLE

     不包含子查询或者union操作

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

  2. 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
       此处省略好多字符...
     
     

  3. 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)
     

  4. 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)
     #我们可以看到 优化器自动加上了 查询条件 
     

  5. 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
       此处省略多行...
     

  6. 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
       此处省略多行...
     
     

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

  8. 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)
     

  9. DEPENDENT DERIVED

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

  10. 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
  
  

  11. UNCACHEABLE SUBQUERY

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

  12. 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 【使用物化优化子查询】