MySQL Explain

Explain、Desc、Describe-查看表设计/SQL分析

A.Desc/Describe/Explain 可以查看表设计详情

image.png

B.Explain、Desc、Describe的SQL分析能力

image.png
从上面的执行情况可以看到Explain、Desc、Describe具有相同的分析能力。
因为在MySQL5.7版本后Desc、Describe与Explain作用效力相同，下面只对Explain作分析（其他两个命令效果分析使用等同）

Explain的作用

当Explain 与 SQL语句一起使用时，MySQL 会显示来自优化器关于SQL执行的信息。MySQL解释了它将如何处理该语句，包括如何连接表以及什么顺序连接表等。

  • 表的加载顺序
  • sql 的查询类型
  • 可能用到哪些索引，哪些索引又被实际使用
  • 表与表之间的引用关系
  • 一个表中有多少行被优化器查询 …..

    相关知识点

    不相关子查询

    如果子查询可以单独运行出结果，而不依赖于外层查询，我们把这个子查询称之为不相关子查询。

    相关子查询

    如果子查询的执行需要依赖于外层查询的值，就把这个子查询称之为相关子查询。

    子查询物化

    不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表(物化表)里。例如：
    1. SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
    假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val。子查询物化之后可以将表**s1**和子查询物化表**materialized_table**进行内连接操作，然后获取对应的查询结果。
    1. SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

    将子查询转换为semi-join

    将子查询进行物化之后再执行查询都会有建立临时表的成本，能不能不进行物化操作直接把子查询转换为连接呢？重新审视一下上边的查询语句：
    1. SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
    可以把这个查询理解成：对于**s1**表中的某条记录，如果能在**s2**表（准确的说是执行完**WHERE s2.key3 = 'a'**之后的结果集）中找到一条或多条符合**s2.common_field=s1.key1**的记录，那么该条**s1**表的记录就会被加入到最终的结果集。这个过程其实和把s1和s2两个表连接起来的效果很像：
    1. SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = 'a';
    这么做唯一的问题在于，对于s1表的某条记录来说，如果s2表中有多条记录满足s1.key1 = s2.common_field这个条件，那么该记录会被多次加入最终的结果集，因此二者不能认为是完全等价的，因此就有了semi-join(半连接)。将s1表和s2表进行半连接的意思就是：对于**s1**表的某条记录来说，只关心在**s2**表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留**s1**表的记录。当然semi-join是mysql内部机制，无法直接用在sql语句中。

    semi-join实现机制

    Table pullout （子查询中的表上拉）

    当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如这个：
    1. SELECT * FROM s1  WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');
    由于key2列是s2表的唯一二级索引列，所以可以直接把s2表上拉到外层查询的FROM子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中，实际上就是直接将子查询优化为连接查询，上拉之后的查询就是这样的：
    1. SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a';

    DuplicateWeedout execution strategy （重复值消除）

    比如下面这个查询语句：
    1. SELECT * FROM s1  WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
    转换为半连接查询后，s1表中的某条记录可能在s2表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中。为了消除重复，可以建立一个临时表，比方说这个临时表长这样：
    1. CREATE TABLE tmp (
    2. id PRIMARY KEY);
    这样在执行连接查询的过程中，每当某条s1表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表里。这种使用临时表消除semi-join结果集中的重复值的方式称之为DuplicateWeedout。

    LooseScan execution strategy （松散扫描）

    比如下面这个查询语句：
    1. SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
    在子查询中，对于s2表的访问可以使用到key1列的索引，而恰好子查询的查询列表处就是key1列，这样在将该查询转换为半连接查询后，如果将s2作为驱动表执行查询的话，那么执行过程就是这样：
    image.png
    如图所示，在s2表的idx_key1索引中，值为'aa'的二级索引记录一共有3条，那么只需要取第一条的值到s1表中查找s1.key3 = 'aa'的记录，如果能在s1表中找到对应的记录，那么就把对应的记录加入到结果集。这种虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散扫描。

    FirstMatch execution strategy （首次匹配）

    FirstMatch是一种最原始的半连接执行方式，简单来说就是说先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程。

    Explain有哪些信息

    Explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。
Column JSON Name Meaning
id select_id The SELECT identifier
select_type None The SELECT type
table table_name The table for the output row
partitions partitions The matching partitions
type access_type The join type
possible_keys possible_keys The possible indexes to choose
key key The index actually chosen
key_len key_length The length of the chosen key
ref ref The columns compared to the index
rows rows Estimate of rows to be examined
filtered filtered Percentage of rows filtered by table condition
Extra None Additional information

MySQL调优分析之Explain关键字的使用 - 图4

列名 描述
id 在一个大的查询语句中每个``SELECT关键字都对应一个唯一的id
select_type ``SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

下边会结合具体的SQL示例，详细的解读每个字段以及每个字段中不同参数的含义，以下所有示例数据库版本为 MySQL.5.7.17。

  1. mysql> select version() from dual;
  2. +------------+
  3. | version()  |
  4. +------------+
  5. | 5.7.17-log |
  6. +------------+

创建三张表 one、two、three，表之间的关系 one.two_id = two.two_id AND two.three_id = three.three_id。

Explain执行计划详解

一、id

id ：表示查询中执行select子句或者操作表的顺序，**id**的值越大，代表优先级越高，越先执行。id大致会出现 3种情况：

1、id相同

看到三条记录的id都相同，可以理解成这三个表为一组，具有同样的优先级，执行顺序由上而下，具体顺序由优化器决定。

  1. mysql> EXPLAIN SELECT * FROM one o,two t, three r WHERE o.two_id = t.two_id AND t.three_id = r.three_id;
  2. +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+
  3. | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra                                              |
  4. +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+
  5. |  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                 |    2 |      100 | NULL                                               |
  6. |  1 | SIMPLE      | t     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                 |    2 |       50 | Using where; Using join buffer (Block Nested Loop) |
  7. |  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xin-slave.t.three_id |    1 |      100 | NULL                                               |
  8. +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+

2、id不同

如果 SQL 中存在子查询，那么 id的序号会递增，id值越大优先级越高，越先被执行 。当三个表依次嵌套，发现最里层的子查询 id最大，最先执行。

  1. mysql> EXPLAIN select * from one o where o.two_id = (select t.two_id from two t where t.three_id = (select r.three_id  from three r where r.three_name='我是第三表2'));
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  5. |  1 | PRIMARY     | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |       50 | Using where |
  6. |  2 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |       50 | Using where |
  7. |  3 | SUBQUERY    | r     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |       50 | Using where |
  8. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

3、以上两种同时存在

将上边的 SQL 稍微修改一下，增加一个子查询，发现 id的以上两种同时存在。相同id划分为一组，这样就有三个组，同组的从上往下顺序执行，不同组 id值越大，优先级越高，越先执行。

  1. mysql>  EXPLAIN select * from one o where o.two_id = (select t.two_id from two t where t.three_id = (select r.three_id  from three r where r.three_name='我是第三表2')) AND o.one_id in(select one_id from one where o.one_name="我是第一表2");
  2. +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
  3. | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
  5. |  1 | PRIMARY     | o     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               |    2 |       50 | Using where |
  6. |  1 | PRIMARY     | one   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xin-slave.o.one_id |    1 |      100 | Using index |
  7. |  2 | SUBQUERY    | t     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               |    2 |       50 | Using where |
  8. |  3 | SUBQUERY    | r     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               |    2 |       50 | Using where |
  9. +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+

二、select_type

select_type：表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

select_type Value JSON Name Meaning
SIMPLE None Simple SELECT (not using UNION or subqueries)
PRIMARY None Outermost SELECT
UNION None Second or later SELECT statement in a UNION
DEPENDENT UNION dependent (true) Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT union_result Result of a UNION.
SUBQUERY None First SELECT in subquery
DEPENDENT SUBQUERY dependent (true) First SELECT in subquery, dependent on outer query
DERIVED None Derived table
MATERIALIZED materialized_from_subquery Materialized subquery
UNCACHEABLE SUBQUERY cacheable(false) A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION cacheable (false) The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

1、SIMPLE

SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union交并差集等操作。

2、PRIMARY

PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

3、SUBQUERY

SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY 。如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY

4、DERIVED

DERIVED：表示包含在from子句中的子查询的select，在 from 列表中包含的子查询会被标记为derived 。

5、UNION

UNION：如果union后边又出现的select 语句，则会被标记为union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。

6、UNION RESULT

UNION RESULT：代表从union的临时表中读取数据，而table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

  1. mysql> EXPLAIN select t.two_name, ( select one.one_id from one) o from (select two_id,two_name from two where two_name ='') t  union (select r.three_name,r.three_id from three r);
  2. +------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
  3. | id   | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
  4. +------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
  5. |    1 | PRIMARY      | two        | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |       50 | Using where     |
  6. |    2 | SUBQUERY     | one        | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |      100 | Using index     |
  7. |    4 | UNION        | r          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |      100 | NULL            |
  8. | NULL | UNION RESULT | <union1,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL | NULL     | Using temporary |
  9. +------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

7、DEPENDENT SUBQUERY

如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY

8、DEPENDENT UNION

在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。

9、DERIVED

对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED。

10、MATERIALIZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED 。

三、table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表，例如上边的DERIVED、 <union1,4>等。

四、partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

  1. +----+-------------+----------------+---------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+
  2. | id | select_type | table          | partitions                      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
  3. +----+-------------+----------------+---------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+
  4. |  1 | SIMPLE      | one            | p201801,p201802,p201803,p300012 | index | NULL          | PRIMARY | 9       | NULL |    3 |      100 | Using index |
  5. +----+-------------+----------------+---------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+

五、type

type：查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1、system

system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。

2、const

const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。

  1. mysql> EXPLAIN SELECT * from three where three_id=1;
  2. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | three | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | NULL  |
  6. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

3、eq_ref

eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。

  1. mysql> EXPLAIN select o.one_name from one o ,two t where o.one_id = t.two_id ; 
  2. +----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+-------------+
  3. | id | select_type | table | partitions | type   | possible_keys | key      | key_len | ref                | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+-------------+
  5. |  1 | SIMPLE      | o     | NULL       | index  | PRIMARY       | idx_name | 768     | NULL               |    2 |      100 | Using index |
  6. |  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY       | PRIMARY  | 4       | xin-slave.o.one_id |    1 |      100 | Using index |
  7. +----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+-------------+

4、ref

ref：区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。

  1. mysql> select o.one_id from one o where o.one_name = "xin" ; 
  2. +--------+
  3. | one_id |
  4. +--------+
  5. |      1 |
  6. |      3 |
  7. +--------+

mysql> EXPLAIN select o.one_id from one o where o.one_name = “xin” ; +——+——————-+———-+——————+———+———————-+—————+————-+———-+———+—————+——————-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——+——————-+———-+——————+———+———————-+—————+————-+———-+———+—————+——————-+ | 1 | SIMPLE | o | NULL | ref | idx_name | idx_name | 768 | const | 1 | 100 | Using index | +——+——————-+———-+——————+———+———————-+—————+————-+———-+———+—————+——————-+

  1. <a name="3j8Ki"></a>
  2. ### 5、ref_or_null
  3. `ref_or_null`：这种连接类型类似于 ref，区别在于 `MySQL`会额外搜索包含`NULL`值的行。
  4. ```sql
  5. mysql> EXPLAIN select o.one_id from one o where o.one_name = "xin" OR o.one_name IS NULL; 
  6. +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
  7. | id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
  8. +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
  9. |  1 | SIMPLE      | o     | NULL       | ref_or_null | idx_name      | idx_name | 768     | const |    3 |      100 | Using where; Using index |
  10. +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+

6、index_merge-索引合并

index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。下边示例中同时使用到主键one_id 和 字段one_name的idx_name 索引 。

  1. mysql> EXPLAIN select * from one o where o.one_id >1 and o.one_name ='xin'; 
  2. +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+
  3. | id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                          |
  4. +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+
  5. |  1 | SIMPLE      | o     | NULL       | index_merge | PRIMARY,idx_name | idx_name,PRIMARY | 772,4   | NULL |    1 |      100 | Using intersect(idx_name,PRIMARY); Using where |
  6. +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+

一般情况下，执行一个查询最多只会用到一个索引。但是在特殊情况下也可能会使用多个二级索引，使用这种方式执行的查询称为index_merge。具体的索引合并算法有下边三种。

  • Intersection合并Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：

    1. SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

  • Union合并 在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，这些不同的搜索条件之间是OR关系。比如：

    1. SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

  • Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。

  • Sort-Union合并Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：
    1. SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
    把上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

    7、unique_subquery

    类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery 。
    unique_subquery：替换下面的 IN子查询，子查询返回不重复的集合。
    1. value IN (SELECT primary_key FROM single_table WHERE some_expr)

    8、index_subquery

    index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
    1. value IN (SELECT key_column FROM single_table WHERE some_expr)

    9、range

    range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。举个栗子：three表中three_id为唯一主键，user_id普通字段未建索引。
    1. mysql> EXPLAIN SELECT * from three where three_id BETWEEN 2 AND 3;
    2. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    4. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    5. |  1 | SIMPLE      | three | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |      100 | Using where |
    6. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    从结果中看到只有对设置了索引的字段，做范围检索 type 才是 range。
    1. mysql> EXPLAIN SELECT * from three where user_id BETWEEN 2 AND 3;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    5. |  1 | SIMPLE      | three | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

    10、index

    index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。下边示例：three_id 为主键，不带 where 条件全表查询 ，type结果为index 。
    1. mysql> EXPLAIN SELECT three_id from three ;
    2. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    4. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    5. |  1 | SIMPLE      | three | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |      100 | Using index |
    6. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

    11、ALL

    ALL：将遍历全表以找到匹配的行，性能最差。
    1. mysql> EXPLAIN SELECT * from two ;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    5. |  1 | SIMPLE      | two   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |      100 | NULL  |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

    六、possible_keys

    possible_keys：表示在MySQL中通过哪些索引，能在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。具体请参考上边的例子。

    七、key

    key：区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL。具体请参考上边的例子。

    当type为index_merge时，可能会显示多个索引。

八、key_len

key_len：表示查询用到的索引长度（字节数），原则上长度越短越好 。

  • 单列索引，那么需要将整个索引长度算进去；
  • 多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

    注意：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到``key_len中。

是由这三个部分构成的：

  • 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值；对于指定字符集是变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。
  • 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。

  • 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

    九、ref

    当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的具体信息，比如只是一个常数或者是某个列。
    ref：常见的有：const，func，null，字段名。

  • 当使用常量等值查询，显示const，

  • 当关联查询时，会显示相应关联表的关联字段
  • 如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
  • 其他情况null

    十、rows

    rows：以表的统计信息和索引使用情况，估算要找到所需的记录，需要读取的行数。这是评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。
    1. mysql> EXPLAIN SELECT * from three;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    5. |  1 | SIMPLE      | three | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | NULL  |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

    十一、filtered

    filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。在MySQL.5.7版本以前想要显示filtered需要使用explain extended命令。MySQL.5.7后，默认explain直接显示partitions和filtered的信息。

    十二、Extra

    Extra ：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

    1、Using index

    Using index：在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。什么又是覆盖索引?一条 SQL只需要通过索引就可以返回，所需要查询的数据（一个或几个字段），而不必通过二级索引，查到主键之后再通过主键查询整行数据（select * ）。one_id表为主键
    1. mysql> EXPLAIN SELECT one_id from one ;
    2. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
    4. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
    5. |  1 | SIMPLE      | one   | NULL       | index | NULL          | idx_two_id | 5       | NULL |    3 |      100 | Using index |
    6. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
    注意：想要使用到覆盖索引，在 select 时只取出需要的字段，不可select *，而且该字段建了索引。
    1. mysql> EXPLAIN SELECT * from one ;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
    5. |  1 | SIMPLE      | one   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | NULL  |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

    2、Using where

    Using where：查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。下边示例create_time 并未用到索引，type 为 ALL，即MySQL通过全表扫描后再按where条件筛选数据。
    1. mysql> EXPLAIN SELECT one_name from one where create_time ='2020-05-18';
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    5. |  1 | SIMPLE      | one   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

    3、Using temporary

    Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。
    1. mysql> EXPLAIN SELECT one_name from one where one_id in (1,2) group by one_name;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    5. |  1 | SIMPLE      | one   | NULL       | range| NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using temporary; Using filesort |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

    4、Using filesort

    Using filesort：表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。
    1. mysql> EXPLAIN SELECT one_id from one  ORDER BY create_time;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    5. |  1 | SIMPLE      | one   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | Using filesort |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    如果ORDER BY字段有索引就会用到覆盖索引，相比执行速度快很多。
    1. mysql> EXPLAIN SELECT one_id from one  ORDER BY one_id;
    2. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
    4. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
    5. |  1 | SIMPLE      | one   | NULL       | index | NULL          | PRIMARY | 4       | NULL |    3 |      100 | Using index |
    6. +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

    5、Using join buffer

    Using join buffer：在联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。先看一下有索引的情况：连接条件 one_name 、two_name 都用到索引。
    1. mysql> EXPLAIN SELECT one_name from one o,two t where o.one_name = t.two_name;
    2. +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+--------------------------+
    3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref                  | rows | filtered | Extra                    |
    4. +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+--------------------------+
    5. |  1 | SIMPLE      | o     | NULL       | index | idx_name      | idx_name | 768     | NULL                 |    3 |      100 | Using where; Using index |
    6. |  1 | SIMPLE      | t     | NULL       | ref   | idx_name      | idx_name | 768     | xin-slave.o.one_name |    1 |      100 | Using index              |
    7. +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+--------------------------+
    接下来删掉 连接条件 one_name 、two_name 的字段索引。发现Extra 列变成 Using join buffer，type均为全表扫描，这也是SQL优化中需要注意的地方。
    1. mysql> EXPLAIN SELECT one_name from one o,two t where o.one_name = t.two_name;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
    5. |  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |      100 | NULL                                               |
    6. |  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
    7. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

    6、Impossible where

    Impossible where：表示在用不太正确的where语句（当查询语句中的where字句永远为false时），导致没有符合条件的行。
    1. mysql> EXPLAIN SELECT one_name from one WHERE 1=2;
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
    5. |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

    7、No tables used

    No tables used：查询语句中没有FROM子句，或者有 FROM DUAL子句。
    1. mysql> EXPLAIN select now();
    2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
    5. |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | No tables used |
    6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

    8、No matching min/max row

    当查询列表有min()或者max()聚集函数，但是没有匹配到对应的记录时会出现No matching min/max row。

    9、Using index condition

    如果查询的执行过程中使用了索引条件下推(Index Condition Pushdown)，就会出现Using index condition。

    10、Not exists

    当使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外信息。

    11、Using intersect(...)、Using union(...)和Using sort_union(...)

    如果使用了索引合并执行查询，则会出现Using intersect(...)或者Using union(...)或者Using sort_union(...)。

    12、Zero limit

    当limit子句参数为0时，就会出现Zero limit。

    13、Start temporary, End temporary

    查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，当执行策略为DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示。

    14、LooseScan

    在将In子查询转为semi-join时，如果采用的是LooseScan执行策略，则在驱动表执行计划的Extra列就是显示LooseScan提示。

    15、FirstMatch(tbl_name)

    在将In子查询转为semi-join时，如果采用的是FirstMatch执行策略，则在被驱动表执行计划的Extra列就是显示FirstMatch(tbl_name)提示。
    Extra列的信息非常非常多，详见 MySQL官方文档 ：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_index_merge
    image.png