MySQL 中的 explain 详解

选择几个重要的，个人觉得可能需要解释下的列如下

• type

  type列表示的是表格是如何"关联(join)"的。

  总的来说，MySQL 认为任何一次查询都是一次"关联"——并不仅仅是一个查询需要用到两个表的匹配才叫关联，所以在 MySQL 中，每一个查询，每一个片段(包括子查询，甚至基于单表的 SELECT ) 都可能是关联¹。

  常见的关联类型有如下几种，性能依次从最差到最优

  1. ALL: 扫描整张表以找到匹配的行
  2. index: 也是扫描全表，只不过是按照索引次序进行，而不是按照表的顺序进行。最大的有点是避免了排序；最大的缺点是要按照索引的顺序读取整张，这通常意味着按照随机的顺序读取行，开销很大。当然，在 extra 列如果展示了Using index，说明了 MySQL 正在使用覆盖索引，不需要读取到表中的行就可以得到需要的数据，开销对比按照索引读取表中的行来说小许多。
  3. range: 范围扫描就是一个有限制的索引扫描，它始于索引里的某一点，返回匹配这个值域的行。
  4. ref: 这是一种索引访问，它会返回匹配某一个值的所有行，然而它可能会找到多个多个符合条件的行，因此它是查找和扫描的混合体。此类索引访问只有当使用非唯一索引或者唯一索引索引的非唯一前缀时才会发生。把它叫做 ref 是因为索引要个某个参考值比较。
  5. eq_ref: 使用这种索引查找，MySQL 知道最多只返回一个符合条件的记录。这种访问方法可以在 MySQL 使用主键或者唯一性索引查找时看到，它会将它们与某个参考值做比较。MySQL 对于这种访问类型的优化做的非常好，因为它知道无需估计匹配行的范围或在找到匹配行后再继续查找。
  6. const, system: 当 MySQL 能对查询的某部分进行优化并将其转化成一个常量的时候，它就会使用这些访问类型。
  7. NULL: 这种访问方式意味着 MySQL 能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。距离来说从一个索引里面选取最小值就可以通过单独查找索引来完成，不需要再执行时访问表。

• ref

  ref列表示的是用于和key列中的索引对比，从表格中选择满足条件的数据的列或者常量。

  比如下面这个查询中第二行展示的ref，使用的是film表的film_id列来和film_actor中的索引idx_fk_film_id进行对比选择满足条件的行。

  mysql> explain select film.film_id, film.description, film_actor.actor_id from film left join film_actor using(film_id) where film.rating='PG' limit 10
  ;
  +----+-------------+------------+------------+------+----------------+----------------+---------+---------------------+------+----------+-------------+
  | id | select_type | table      | partitions | type | possible_keys  | key            | key_len | ref                 | rows | filtered | Extra       |
  +----+-------------+------------+------------+------+----------------+----------------+---------+---------------------+------+----------+-------------+
  |  1 | SIMPLE      | film       | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                | 1000 |    20.00 | Using where |
  |  1 | SIMPLE      | film_actor | NULL       | ref  | idx_fk_film_id | idx_fk_film_id | 2       | sakila.film.film_id |    5 |   100.00 | Using index |
  +----+-------------+------------+------------+------+----------------+----------------+---------+---------------------+------+----------+-------------+
  2 rows in set, 1 warning (0.00 sec)
  

• rows

  rows表示的执行查询的过程中需要检测的行数。对于使用了引擎 InnoDB 的表来说，这个值仅仅是一个预估的值，并不会和实际的一样。

• filtered

  filtered 表示的是预估会被表中条件筛选后的行数记录的百分比。其最大值是 100，这意味着表中的记录并不会被过滤。其值越低，意味着过滤的行数越多。rows 表示的是会被检测的行，rows * filtered表示的是接下来会被用于"关联"的行。例如，如果 rows是 1000 并且filtered是 50.0，那么接下来被用于连接的行数就是 1000 * 50% = 500。

• Extra

  这一列包含的是不适合在其他列显示的额外信息。常用的最重要的值如下：

  1. "Using index"

     此值表示 MySQL 将使用覆盖索引，以避免访问表。不要把覆盖索引和 index 访问类型混淆了。

  2. "Using where"

     这意味着 MySQL 服务器将在存储引擎检索行后再进行过滤。许多 WHERE 条件涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此并不是所有带 WHERE 子句的查询都会显示 "Using where"。这个一般发生存储引擎层返回数据后，在 MySQL 服务器进行数据的条件选择。

  3. "Using temporary"

     这意味着 MySQL 在堆查询结果排序时会使用一个临时表。

  4. "Using filesort"

     这意味着 MySQL 会对结果使用一个外部索引排序，而不是按照索引次序从表里读取行。