Explain详解与索引优化

1. EXPLAIN

1.1 概述

使用EXPLAIN可以分析查询语句

用法

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

1.2 基本用法

EXPLAN可以查看语句的执行计划，执行EXPLAN时并没有真正的执行后面的语句。
EXPLAIN语句输出的各个列的作用如下：

（1）table

不论查询语句有多复杂，包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

（2）id

查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列。

• id如果相同，可以认为是一组，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• 关注点：id号每个号码，表示一趟独立的查询,一个sql的查询趟数越少越好

（3）select_type

MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色

（4）partitions

代表分区表中的命中情况，非分区表，该项为NULL。一般情况下查询语句的执行计划的partitions列的值都是NULL

（5）type

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称"访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。

完整的访问方法如下，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system
  当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system
• const
  当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const
• eq_ref
  在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）。则对该被驱动表的访问方法就是eq_ref
• ref
  当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
• fulltext 全文索引
• ref_or_null
  当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null
• index_merge
  一般情况下对于某个表的查询只能使用到一个索引，但单表访问方法时在某些场景下可以使用Interseation、union、Sort-Union这三种索引合并的方式来执行查询。
• unique_subquery
  类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery
• index_subquery
  index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引
• range
  如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法
• index
  当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index
• ALL
  全表扫描

（6）possible_keys和key

在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个列执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

（7）key_len

实际使用到的索引长度 (即：字节数)

检查是否充分的利用了索引，值越大越好（相对于自身），主要针对于联合索引，有一定的参考意义。

（8）ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

（9）rows

预估的需要读取的记录条数，条目数越小越好。这是因为值越小，加载I/O的页数就越少

（10）filtered

某个表经过搜索条件过滤后剩余记录条数的百分比。如果进行单表扫描时使用了索引，那么在计算时需要估算出除了符合索引条件外，满足其他搜索条件的记录数量。

（11）Extra

Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息可以更准确的理解MySQL到底将如何执行给定的查询语句。