MySQL索引优化——Explain的应用

一、什么是 Explain

  使用 Explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的，分析你的查询语句或是表结构的性能瓶颈。

二、 Explain 的简单使用

1、如何使用 Explain

  语法

Explain + SQL 语句

2、使用 Explain 显示的信息

  在 5.7 以前的版本中，想要显示 partitions 需要使用 explain partitions 命令，想要显示 filtered 需要使用 explain extended 命令。
  在 5.7 版本后，默认 explain 直接显示 partitions 和 filtered 中的信息。
  执行一条简单的 SQL 语句。

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3、Explain 的功能

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

三、Explain 的信息详解

  其中重要的信息有：id、type、key、rows、Extra。

1、id

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

① id 相同

  执行顺序由上至下。

explain select * 
from t1,t2,t3 
where t1.id=t3.id and t1.id=t2.id and t1.age=2;

+----+-------------+-------+------------+--------+----------------------+--------------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys        | key          | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+--------+----------------------+--------------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref    | PRIMARY,index_t1_age | index_t1_age | 5       | const     |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY              | PRIMARY      | 4       | zyx.t1.id |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | PRIMARY              | PRIMARY      | 4       | zyx.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+----------------------+--------------+---------+-----------+------+----------+-------+

② id 不同

  如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。

explain select t2.* 
from t2 
where id=(select id 
	  from t1 
	  where id=(select id 
		    from t3 
		    where title="123"));

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
|  3 | SUBQUERY    | t3    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |    50.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

③ id 相同不同，同时存在。

  id 如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id 值越大，优先级越高，越先执行。

explain select t2.*
from t2,t5
where t2.id=(select id 
	  from t1 
	  where id=2);

+----+-------------+-------+------------+-------+---------------+---------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t2    | NULL       | const | PRIMARY       | PRIMARY                   | 4       | const |    1 |   100.00 | NULL        |
|  1 | PRIMARY     | t5    | NULL       | index | NULL          | user_product_detail_index | 254     | NULL  |    9 |   100.00 | Using index |
|  2 | SUBQUERY    | t1    | NULL       | const | PRIMARY       | PRIMARY                   | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------------------+---------+-------+------+----------+-------------+

2、select_type

  查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

常见类型的作用：

• SIMPLE：简单的 select 查询，查询中不包含子查询或者 UNION。
• PRIMARY：查询中若包含在任何负责的子部分，最外层查询则被标记为。
• SUBQUERY：在 select 或 where 列表中包含的子查询。
• DERIVED：派生表——该临时表是从子查询派生出来的，位于 form 中的子查询。
• UNION：位于 union 中第二个及其以后的子查询被标记为 union，第一个就被标记为 primary 如果是 union 位于 from 中则标记为 derived。
• UNION RESULT：从临时表里检索结果的 select。
• dependent union：首先需要满足 UNION 的条件，及 UNION 中第二个以及后面的 SELECT 语句，同时该语句依赖外部的查询。
• dependent subquery 和 DEPENDENT UNION 相对 UNION 一样。

3、table

  显示这一行的数据是关于哪一张表。关联优化器会为查询选择关联顺序，左侧深度优先。当 from 中有子查询的时候，表名是 derivedN 的形式，N 指向子查询，也就是 explain 结果中的下一列。

4、partitions

  显示查询将访问的分区，如果你的查询是基于分区表。

5、type

  type 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
  访问类型从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL。一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

常见类型的作用：

• All：最坏的情况,全表扫描。
• index：和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。也就是说虽然 All 和 index 都是读取全部，但是 index 是从索引中读取的，而 all 是从硬盘中读取的。
• range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现了 between 、< 、> 、in 等的查询，这种范围扫描索引扫描比全表扫描要好。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，他可能会找到多个复合条件的行，所以他应该属于查找和扫描的混合体。
• eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
• const：当确定最多只会有一行匹配的时候，MySQL 优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入 where 子句时，MySQL 把这个查询转为一个常量（高效）。
• system：这是 const 连接类型的一种特例，表仅有一行满足条件，平时不会出现。
• Null：意味说 MySQL 能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）。

6、possible_keys

  显示可能应用在这张表中的索引，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定查询实际使用。

7、key

  实际使用的索引，如果为 null 则没有使用索引，查询中若使用了覆盖索引，则该索引仅出现在 key 列表中。

8、key_len

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

9、ref

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

10、row

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

11、filtered

  表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

12、Extra

  包含不适合在其他列表中显示但又十分重要的额外的信息。

常见信息的作用：

• Using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该提示信息表示排序没有用到索引最好优化，如果排序使用索引会大大提升性能。
• Using temporary：使用了临时表保存了中间结果，MySQL 在对查询结果排序时使用了临时表，常见于排序 order by 和分组查询 group by。表示创建了临时表后再删除，效率还没第一个好。
• Not exists：MySQL 优化了 LEFT JOIN，一旦它找到了匹配 LEFT JOIN 标准的行， 就不再搜索了。
• Using index：说明查询是覆盖索引，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现 using where，表明索引被用来执行索引键值的查找，没有 using where，表明索引用来读取数据而非执行查找动作。这是 MySQL 服务层完成的，但无需再回表查询记录。
• Using index condition：表示会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。Index Condition Pushdown (ICP)是MySQL 5.6 以上版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。
• Using where：表示 MySQL 服务器将存储引擎返回服务层以后再应用 WHERE 条件过滤。
• Using join buffer：使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接。
• impossible where：where 子句的值总是 false，不能用来获取任何元组。
• select tables optimized away：在没有 GROUP BY 子句的情况下，基于索引优化 MIN/MAX 操作，或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
• distinct：优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的动作

什么是覆盖索引

  就是 select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件。