explain-in-detail.adoc

MySQL explain 详解

1. explain 语法

2. explain 输出

   1. id

   2. select_type

      1. SIMPLE

      2. PRIMARY

      3. UNION

      4. DEPENDENT UNION

      5. UNION RESULT

      6. SUBQUERY

      7. DEPENDENT SUBQUERY

      8. DERIVED

      9. MATERIALIZED

      10. UNCACHEABLE SUBQUERY

      11. UNCACHEABLE UNION

   3. table

   4. partitions

   5. type

      1. system

      2. const

      3. eq_ref

      4. ref

      5. fulltext

      6. ref_or_null

      7. index_merge

      8. unique_subquery

      9. index_subquery

      10. range

      11. index

      12. ALL

   6. possible_keys

   7. key

   8. key_len

   9. ref

   10. rows

   11. filtered

   12. extra

       1. Child of 'table' pushed join@1

       2. const row not found

       3. Deleting all rows

       4. Distinct

       5. FirstMatch

       6. Full scan on NULL key

       7. Impossible HAVING

       8. Impossible WHERE

       9. Impossible WHERE noticed after reading const tables

       10. LooseScan(m..n)

       11. No matching min/max row

       12. no matching row in const table

       13. No matching rows after partition pruning

       14. No tables used

       15. Not exists

       16. Plan isn’t ready yet

       17. Range checked for each record (index map: N)

       18. Scanned N databases

       19. Select tables optimized away

       20. Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table

           1. Skip_open_table

           2. Open_frm_only

           3. Open_trigger_only

           4. Open_full_table

       21. Start temporary, End temporary

       22. unique row not found

       23. Using filesort

       24. Using index

       25. Using index condition

       26. Using index for group-by

       27. Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

       28. Using MRR

       29. Using sort_union(…​), Using union(…​), Using intersect(…​)

       30. Using temporary

       31. Using where

       32. Using where with pushed condition

       33. Zero limit

EXPLAIN 语法

DESCRIBE 和 EXPLAIN 是同义词。在实践中，DESCRIBE 多用于显示表结构，而 EXPLAIN 多用于显示 SQL 语句的执行计划。

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

EXPLAIN FORMAT = JSON SELECT DISTINCT (m.user_id)
FROM user_extdata m
WHERE m.city_id IN (1, 2, 3, 4) ;

获取表结构

DESCRIBE 是 SHOW COLUMNS 的简写形式。

explain 输出

select_type

SIMPLE

简单SELECT,不使用UNION或子查询等

PRIMARY

查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY

UNION

UNION中的第二个或后面的SELECT语句

DEPENDENT UNION

UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT

UNION的结果

SUBQUERY

子查询中的第一个SELECT

DEPENDENT SUBQUERY

子查询中的第一个SELECT，取决于外面的查询

DERIVED

派生表的SELECT, FROM子句的子查询

MATERIALIZED

UNCACHEABLE SUBQUERY

一个子查询的结果不能被缓存，必须重新评估外链接的第一行

UNCACHEABLE UNION

==== table

partitions

type

system

当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

const

eq_ref

类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

ref

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

fulltext

ref_or_null

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

index_merge

unique_subquery

index_subquery

range

只检索给定范围的行，使用一个索引来选择行

index

Full Index Scan，index与ALL区别为index类型只遍历索引树

ALL

Full Table Scan， MySQL将遍历全表以找到匹配的行

possible_keys

key

key_len

ref

rows

filtered

extra