MySQL（四） Explain分析器

4. Explain 分析器

1. explain + 查询语句

该EXPLAIN语句提供有关MySQL如何执行语句的信息。 EXPLAIN作品有 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句。

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

2. id 参数( 表的读取顺序 )

id为select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

id值存在三种情况：

1. id相同，执行顺序由上至下。

   

   MySQL执行的顺序是 t1,t3,t2.

2. id不同，如果是子查询，id序号会递增，id值越大，优先级越高，越先被执行

   

   MySQL执行的顺序是 t3 , t1 , t2.

3. 如果同时存在id相同和不同的情况，那么id相同的会被认为是一组，从上到下的顺序执行；在所有组中，id值越大的，优先级越高，越先执行.

   

   MySQL执行的顺序是 t3, <derived2>, t2

3. select_type 参数

• SUBQUERY： 在select 或者 where 列表中包含子查询

• DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放到临时表中。

• UNION：若第二个 SELECT 出现在 UNION 之后，则标记为 UNION；若UNION包含在FROM子句的子查询中，外层SELECT将标记为 DERIVED。

• UNION RESULT 从UNION表中获取的结果。

• <union*M,N*>：该行指的是具有和id值的行 的 *M*并集 N。
• <derived*N*>：该行是指用于与该行的派生表结果id的值 N。派生表可能来自（例如）FROM子句中的子查询 。
• <subquery*N>：该行是指该行的物化子查询的结果，其id 值为N*。

4. type 参数

type参数为访问类型访问，其中包括这些内容 ，

其中它们的查询性能由左到右，性能由差到好。表优化时，一般时百万级别的数据量才优化。 而一般情况下，我们==只需要优化到ref或者range==即可。

• System: 表中只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现。
• const： 表示通过索引一次就找到，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。 如将主键置于 where 列表中， MySQL就能将该查询转换为一个常量。

首先我们得分析id值，id值越大得越先执行，当id值相同的话，则由上到下执行。所以先执行id值为2的语句，即先访问t1表， 因为 在select * from t1 where id = 1 中，id作为主键存在，通过索引即可找到，所以访问类型为 const。 而 id值为2的表时通过 子查询获得的，且子表中只有一行数据，所以type 查询类型为 system。

• eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描。
• ref： 非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独的行，然而，它可能会找到符合某个条件的行，所以他应该属于查找和扫描的混合体。

在t1表中对 col1 和 col2 字段创建一个符合索引，

• range： 只检索给定范围的行，使用一个索引来选择行，key 列显示使用哪个索引，一般就是在你的where 语句中出现 between、< 、 in 等查询语句。这种范围索引扫描比全表扫描要好，因为它只需要索引开始于某一点，结束于某一点，不同全表扫描。

• index： index 与 All 的区别在于 index 类型只遍历 索引树。这通常比 All 要快，因为索引文件通常要比数据文件要小（也就是说all 和index 都是读全表，但 index 是从索引中读取的，而all是从硬盘中读取的。）

• all： 将全表进行扫描

5. possible_key 和 key

• possible_key: 显示可能应用在表中的索引，一个或者多个。查询涉及到的字段，若存在索引，则会被列出，但不一定在实际查询中使用
• key： 实际使用的索引，如果为null，则没有使用索引。查询中若使用覆盖索引，则该索引仅出现在key列表中。

如果建立索引，但在这并没使用，这就是索引失效。

当中存在三种情况： 1. 理论上需要使用，实际上使用；2. 理论上不使用，实际上使用；3. 理论上不使用，实际上也没使用。

6. key_len 参数

表示索引中使用的字节数。可通过该列计算查询中使用的索引长度，在不损失精度的情况下，长度越短越好。Key_len显示的值为索引字段的最大可能的长度，并非实际长度，即key_len是根据表定义计算而得来的，而不是通过表内检索获得的。

col1 和 col2 在 t1 表中是一对复合索引。

当查询条件只有一个时：

当查询条件为两个时：

7. ref 参数

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引上的值。

实例一：

分析： 因为id 相同，则从上到下开始执行， t1.other_colum=‘ ’，所以第一行的ref为 const。第二次执行的为 t1.id = t3.id 因为数据库会自适应，把它转换成 t3.id = t1.id, 所以扫描 t3 表时， 查询的是 test.t1.ID, 意思是 test数据库中的t1表中的ID字段。

示例二：

分析：

首先查看id值，id值相同，从上到下开始执行。首先加载的是t2表，没有使用任何索引，并且进行全表扫描。

第2行时，扫描的是t1表，使用的索引是idx_col1_col2，关联的数据是 share数据库下的t2表中 的col1 字段 ， 以及 一个常量即为“ac”。

8. rows 关键字

根据表统计信息及索引选用情况，大致估算找到所需记录所需读取的行数。该值越小越好。

示例1：

建立索引之后，扫描的行数由641 减少到了 199 行， 大大提高了查找的效率。