Explain执行计划优化SQL的性能？

系统管理员 2024-03-17 22:26 92阅读 0赞

#### Explain执行计划优化SQL的性能？ ####

我们可以使用EXPLAIN命令来分析SQL的**执行计划。执行计划是指一条SQL语句在经过MySQL查询优化器**的优化之后，具体的执行方式。

`EXPLAIN`并不会真的去执行相关的语句，而是通过**查询优化器**对语句进行分析，找出最优的查询方案，并显示对应的信息。

`EXPLAIN`适用于select，delete，insert，replace，和update语句，我们一般分析select查询较多。

`EXPLAIN`的输出格式如下：

各个字段的含义如下：

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>select查询的序列标识符</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>select关键字对应的查询类型</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>用到的表名</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区，对于未分区的表，值为null</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>表的访问方法</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>可能用到的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际用到的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>所选索引的长度</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>当使用索引等值查询时，与索引做比较的列或常量</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>预计要读取的行数</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>按表条件过滤后，留存的记录数的百分比。</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>附加信息</td> 
  </tr> 
 </tbody> 
</table>

##### 如何分析EXPLAIN结果？ #####

为了分析EXPLAIN语句的执行结果，我们需要搞懂执行计划中的重要字段。

**id**

select标识符，是查询中select的序号，用来标识整个查询中select语句的顺序。

id如果相同，从上往下依次执行。id不同，id值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为NULL。

**select\_type**

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

*  SIMPLE：简单查询，不包含UNION或者子查询。
 *  PRIMARY：查询中如果包含子查询或者其他部分，外层的select将被标记为PRIMARY。
 *  SUBQUERY：子查询中的第一个select。
 *  UNION：在UNION语句中，UNION之后出现的select。
 *  DERIVED：在FROM中出现的子查询将被标记为DERIVED。
 *  UNION RESULT：UNION查询的结果。

**table**

查询用到的额表名，每行都有对应的表名，表明除了正常的表之外，也可能是以下列出的值：

*  <unionM,N>：本行引用了id为M和N的行的UNION结果；
 *  ：本行引用了id为N的表所产生的派生表结果。派生表有可能产生自FROM语句中的子查询。-：本行引用了id为N的表所产生的物化子查询结果。

**type（重要）**

查询执行的类型，描述了查询时如何执行的。所有的值顺序从最优到最差的排序为：system>const>eq\_ref>ref>fulltext>ref\_or\_null>index\_merge>unique\_subquery>index\_subquery>range>index>ALL

常见的几种类型具体含义如下：

*  system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法时system，是const的一种特例。
 *  const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
 *  eq\_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了system与const之外的最好的join方式，常用于使用主键或唯一索引的所有字段作为连表条件。
 *  ref：使用的普通索引作为查询条件，查询结果可能找到多个复合条件的行。
 *  index\_merge：当查询条件使用了多个索引时，表示开启了Index Merge优化，次吃执行计划中的key列列除了使用到的索引。
 *  range：对索引列进行范围查询，执行计划中的key列表示哪个索引被使用了。
 *  Index：查询遍历了整颗索引树，与ALL类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
 *  ALL：全表扫描。

**possible\_keys**

possible\_keys列表示MySQL执行查询时可能用到的索引。如果这一列为null，则表示没有可能用到索引；这种情况下，需要检查where语句中所使用的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

**key（重要）**

key列表式MySQL实际使用到的索引。如果为null，则表示未用到索引。

**key\_len**

key\_ken列表式MySQL实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果key列显示null，则key\_len列也显示null。

**rows**

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

**Extra（重要）**

这列包含了MySQL解析查询的额外信息，通过这些信息，可以更准确的理解MySQL到底是如何执行查询的。常见的值如下：

*  Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
 *  Using temporary：MySQL需要创建临时表来存储查询的结果，常见于ORDER BY和GROUP BY。
 *  Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
 *  Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
 *  Using where：表明查询使用了WHERE子句进行条件过滤。一般在没有使用到索引的时候会出现。
 *  Using join buffer（Block Nested Loop）：连表查询的方式，表示当被驱动表没有使用索引的时候，MySQL会先将驱动表读出来放到join buffer中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当Extra列包含Using filesort或Using temporary时，MySQL的性能可能会存在问题，需要尽可能避免。