用 explain 来优化查询

explain 语句能够提供 MySQL 执行语句的信息，即语句的执行计划。

explain 支持的语句有：

• select
• delete
• insert
• replace
• update

explain 对 select 语句的输出格式：（官网： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html ）

• id -- 查询的标识符（the select identifier）
• select_type -- 查询的类型（the select type）
• table -- 表名（the table for output row）
• partitions -- 匹配的分区（the matching partitions）
• type -- 连接类型（the join type）
• possible_keys -- 可能选择的索引（the possible indexes to choose）
• key -- 实际选择的索引（the index actually chosen）
• key_len -- 实际选择的索引的长度（the length of the chosen key）
• ref -- 与索引比较的列（the columns compared to the index）
• rows -- 要扫描的估计的行数（estimate of rows to be examined）
• filtered -- 被表条件过滤的表的估计百分比（percentage of rows filtered by table condition）
• Extra -- 额外的信息（additional information）

详解 explain 输出的信息

id： 查询的标识符，即查询语句中每个 select 的顺序号。值可以为null，当前行引用的是其它行的并集（union）结果。

select_type： select的类型

• simple
  • 简单的 select，即没有用 union 或子查询
  • simple select,not using union or subqueries
• primary
  • 外层 select
  • outermost select
• union
  • 在一个 union 的第二或后面的 select
  • second or later select statement in a union
• dependent union
  • 依靠外层查询的第二或后面的 select 在一个 union
  • second or later select statement in a union,dependent on outer query
• union result
  • 一个 union 的结果
  • result of a union
• subquery
  • 子查询的第一个 select
  • first select in subquery
• dependent subquery
  • 依靠外层查询的子查询的第一个 select
  • first select in subquery,dependent on outer query
• derived
  • 派生表
  • derived table
• materialized
  • 物化子查询
  • materialized subquery
• uncacheable subquery
  • 一个子查询，即结果不能被缓存且要外层查询要重新查询
  • a subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
• uncacheable union
  • 在一个 union 的第二或后面的 select，属于一个不能缓存的子查询，跟 uncacheable query 一样
  • the second or later select in a union that belongs to an uncacheable subquery(see uncacheable subquery)

table： 表名，即 explain 输出的行所引用的表名，也可以是下面的值：

• <unionM,N>： 即 id 值为 M 和 N 的 union
• <derivedN>： 即 id 值为 N 的派生表
• <subqueryN>： 即 id 值为 N 的物化子查询

partitions: 匹配的分区，即被查询匹配上的记录所在的分区，表没有分区的话就是 null

type: 连接类型（官网： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types ）

• system: 只有一条记录的表，这是一种特殊情况
• const: 在查询开始时读取，表中最多有一个匹配的行记录，即只读一次，所以非常快
  • 用了主键索引或唯一索引的列为查询条件，而它的值是常量时，会用到 const
    • 如果其它的查询条件匹配失败，type 则为null，Extra 列中会有： Impossible WHERE noticed after reading const tables
• eq_ref：
  • 在联表查询中，对上一个表每一行的组合，从当前表中读取一行，而关联条件的字段用的是主键索引或唯一索引的列，它的值可以为常量，也可以为关联表的字段。
• ref：
  • 查询中，用了主键索引或唯一索引的列的前缀（就是匹配到的一止一行记录），或者用的是主键索引及唯一索引之外的索引。
• fulltext
  • 联接用的是全文索引执行的
• ref or null
  • 与 ref 类似，但是mysql会额外扫描包含空值的行。比如：

    select * from ref_table where key_column=exepr or key_column is null;
    

• index merge
  • 执行计划用了索引合并优化
    • key 的那一列会展示用到的索引；
    • key_len 的那一列会展示的长度是最长长度的索引的。
      --> 合并的索引比较多，会影响性能，应该考虑加一个联合索引
• unique subquery
  • 被用 in () 括起来的子查询，子查询是 eq_ref 的 type，其 type 会被替换成 unique subquery
• index subquery
  • 与 unique subquery 类型，是被用 in () 括起来的子查询，但不同的是子查询中用的索引不是唯一索引
• range
  • 仅查询给定范围的行记录，且使用了索引；
    • 给定范围的方式包括用： =, <>, >, >=, <, <=, is null, <=>, between, like, in()等。
  • key 的那一列会展示用到的索引；
  • key_len 的那一列会展示的长度是最长长度的索引的。
• index
  • 跟全表扫描类型，不过是在索引树上扫描，比全表扫描要快。
    • 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。
      • Extra 列会展示： Using index；
    • 使用从索引中读取的数据执行全表扫描，以按索引顺序查找数据行。
• all
  • 即全表扫描查询。

小结：

1、就查效率来说：system > const > eq_ref > ref > range > index > all
2、index merge 是个坑，因为加太多索引的情况，全并索引的结果会开销比较大，还不如全表扫描。