Explain分析sql语句执行效率

一、Explain介绍及使用

在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN去完成。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。

explain的使用很简单，在select语句前加上explain就ok了。

我在user中创建了一个一个索引idx_name_age_phone,这是一个组合索引，包含三个字段。

重要参数释义

先来看下explain解释执行的主要参数的含义：

1. id: 包含一组数字，表示查询中执行select子句或操作表的顺序,id相同，顺序执行由上到下。如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。id如果相同，可认为是一组，执行顺序由上至下，在所有组中，id越大，执行优先级越高。

2. select_type:表示每个select子句的类型

   • SIMPLE:查询中不包含子查询或union
   • PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为primary。
   • SUBQUERY:在select或where列表中包含了子查询，该子查询被标记为subquery。
   • DERIVED:在FROM列表中包含的子查询被标记为DERIVED（衍生），用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
   • 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
   • 从UNION表获取结果的SELECT被标记为：UNION RESULT
   • SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。DEPENDENT意味着select依赖于外层查询中发现的数据。UNCACHEABLE意味着select中的某些特性阻止结果被缓存于一个item_cache中。

3. type:表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下:
   
   ALL, index, range, ref, eq_ref, const, system, NULL
   从左到右，性能从最差到最好。

   • All:Full table scan，全表扫描，Mysql将遍历全表以找到匹配的行。
   • index：full index scan，index类型只遍历索引树。
   • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
   • ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
   • eq_ref:类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
   • const,system:当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,注：system是const类型的特例，当查询的表只有一行的情况下，使用system
   • null：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4. possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

5. key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

6. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

7. ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

8. rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

9. Extra： 包含不适合在其他列中显示但十分重要的额外信息

10. filtered：　在mysql5.1里新加的，在使用explain extended时出现。它显示的是针对表里符合条件的记录数的百分比所做的一个悲观估算值。filtered 列给出了一个百分比的值，这个百分比值和rows 列的值相乘，可以估计出那些将要和QEP 中的前一个表进行连接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

二、索引失效的几种情况

下面就看一下这个组合索引在不同select语句中的执行情况。现在分情况验证下索引失效的问题：

1. 查询条件中字段顺序不遵循最左前缀集合

• 首个查询字段不是组合索引首位

  `explain select * from user where age = 20 and phone = '17311111111'`
  

  
  可以看出这里是用了全表扫描。

• 查询字段中断

  `explain select * from user where age = 20 and phone = '17311111111'`
  

  
  只是用了name，没有使用phone，因为age中断了。

2. 范围条件(between,>,<,in等）右边的字段不能继续使用索引。
   explain select * from user where name = '张三' and age > 20 and phone = '17311111111'

   
   只用了name和age的索引，age之后的就断了。

3. 使用like迷糊查询
   

   • 使用like模糊查询也可能会出现索引失效，但是这个是分情况的，有的是全部失效，有的是部分失效。
     explain select * from user where name like '%张%' and age = 20 and phone = '17311111111'
     
     使用了%开头的这种查询，都会进行全表扫描，导致索引失效。

   • 结尾使用“%”

     explain select * from user where name like '张三%' and age = 20 and phone = '17311111111'
     
     这种情况不会全表扫描，变为range范围查找，但是与>,<,between不同的是，不会导致范围右边的索引失效。

4. 如果索引字段是字符串，查询时没有使用单引号，也会转为全表扫描，使用or也会是索引失效，转向全表扫描