MySQL EXPLAIN详解，left join和in优化详解，以及optimizer_trace跟踪语句使用

阳光穿透心脏的1/2处 2023-02-18 06:27 165阅读 0赞

最近优化mybaits的sql语句性能，网上查了查mysql的explain用法，这里记录一下，希望对大家有帮助。

# EXPLAIN简述: #

explain为mysql提供语句的执行计划信息。可以应用在select、delete、insert、update和place语句上。explain的执行计划，只是作为语句执行过程的一个参考，实际执行的过程不一定和计划完全一致，但是执行计划中透露出的讯息却可以帮助选择更好的索引和写出更优化的查询语句。

## EXPLAIN输出项（来源于mysql5.7文档） ##

备注:当使用FORMAT=JSON， 返回的数据为json结构时，JSON Name为null的不显示。

<table> 
 <thead> 
  <tr> 
   <th>Column</th> 
   <th>JSON Name</th> 
   <th>Meaning</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>select_id</td> 
   <td>The SELECT identifier</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>None</td> 
   <td>The SELECT type</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>table_name</td> 
   <td>The table for the output row</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>partitions</td> 
   <td>The matching partitions</td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;"><strong>type</strong></span></td> 
   <td><span style="color:#f33b45;"><strong>access_type</strong></span></td> 
   <td><span style="color:#f33b45;"><strong>The join type</strong></span></td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>possible_keys</td> 
   <td>The possible indexes to choose</td> 
  </tr> 
  <tr> 
   <td><span style="color:#f33b45;"><strong>key</strong></span></td> 
   <td><span style="color:#f33b45;"><strong>key</strong></span></td> 
   <td><span style="color:#f33b45;"><strong>The index actually chosen</strong></span></td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>key_length</td> 
   <td>The length of the chosen key</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>ref</td> 
   <td>The columns compared to the index</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>rows</td> 
   <td>Estimate of rows to be examined</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>filtered</td> 
   <td>Percentage of rows filtered by table condition</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>None</td> 
   <td>Additional information</td> 
  </tr> 
 </tbody> 
</table>

注意：

1.  在5.7以前的版本中，想要显示partitions需要使用explain partitions命令；
2.  想要显示filtered需要使用explain extended命令。在5.7版本后，
3.  默认explain直接显示partitions和filtered中的信息。

下面说明一下各列含义及可能值：

## 1、id的含义 ##

The SELECT identifier. This is the sequential number of the SELECT within the query. The value can be NULL if the row refers to the union result of other rows. In this case, the table column shows a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N.  
翻译：id为SELECT的标识符。它是在SELECT查询中的顺序编号。**如果这一行表示其他行的union结果，这个值可以为空**。在这种情况下，table列会显示为形如<union M,N>，表示它是id为M和N的查询行的联合结果。

**id是SQL执行的顺序的标识,SQL从大到小的执行**

1.  id相同时，执行顺序由上至下
2.  如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3.  id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

注意：id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

## 2、select\_type可能出现的情况（来源于Mysql5.7文档） ##

<table> 
 <thead> 
  <tr> 
   <th>select_type Value</th> 
   <th>JSON Name</th> 
   <th>Meaning</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>None</td> 
   <td> <p>Simple SELECT (not using UNION or subqueries)</p> <p>简单SELECT, 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。</p> </td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>None</td> 
   <td> <p>Outermost SELECT</p> <p>查询中若包含任何复杂的子部分, 最外层的select被标记为PRIMARY</p> <p>一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个。</p> </td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>None</td> 
   <td> <p>Second or later SELECT statement in a UNION</p> <p>UNION中的第二个或后面的SELECT语句</p> </td> 
  </tr> 
  <tr> 
   <td>DEPENDENT UNION</td> 
   <td>dependent (true)</td> 
   <td> <p>Second or later SELECT statement in a UNION, dependent on outer query</p> <p>UNION中的第二个或后面的SELECT语句，取决于外面的查询</p> </td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>union_result</td> 
   <td> <p>Result of a UNION.</p> <p>UNION的结果</p> </td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>None</td> 
   <td> <p>First SELECT in subquery</p> <p>子查询中的第一个SELECT</p> </td> 
  </tr> 
  <tr> 
   <td>DEPENDENT SUBQUERY</td> 
   <td>dependent (true)</td> 
   <td> <p>First SELECT in subquery, dependent on outer query</p> <p>子查询中的第一个SELECT，取决于外面的查询</p> </td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>None</td> 
   <td> <p>Derived table SELECT (subquery in FROM clause)</p> <p>派生表的SELECT, FROM子句的子查询</p> </td> 
  </tr> 
  <tr> 
   <td>MATERIALIZED</td> 
   <td>materialized_from_subquery</td> 
   <td> <p>Materialized subquery</p> <p>被物化的子查询</p> </td> 
  </tr> 
  <tr> 
   <td>UNCACHEABLE SUBQUERY</td> 
   <td>cacheable (false)</td> 
   <td> <p>A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query</p> <p>一个子查询的结果不能被缓存，必须重新评估外链接的第一行</p> <p>对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）</p> </td> 
  </tr> 
  <tr> 
   <td>UNCACHEABLE UNION</td> 
   <td>cacheable (false)</td> 
   <td> <p>The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)</p> <p>UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）</p> </td> 
  </tr> 
 </tbody> 
</table>

各项内容含义说明：

A：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。  
**B：primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select\_type即为primary。且只有一个**。  
C：union：union连接的select查询，除了第一个表外，第二个及以后的表select\_type都是union。  
D：dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响  
E：union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null  
F：subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery  
G：dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响  
H：derived：from字句中出现的子查询。  
I：materialized：被物化的子查询  
J：UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）  
K：UNCACHEABLE UNION：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

## 3、table ##

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。如果是尖括号括起来的<subquery N>，这个表示子查询结果被物化，之后子查询结果可以被复用（个人理解）。

## 4、type ##

**依次从好到差**：**system，const，eq\_ref，ref，fulltext，ref\_or\_null，index\_merge，unique\_subquery，index\_subquery，range，index，ALL**，除了all之外，其他的type都可以使用到索引，除了index\_merge之外，其他的type只可以用到一个索引

A：system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index  
**B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描**  
**C：eq\_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq\_ref  
D：ref：不像eq\_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。**  
E：fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引  
F：ref\_or\_null：与ref方法类似，只是增加了null值的比较。实际用的不多。  
例如：  
SELECT \* FROM ref\_table  
WHERE key\_column=expr OR key\_column IS NULL;  
G：index\_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref\_or\_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range  
H：unique\_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值  
I：index\_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。  
J：range：索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。  
K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。按照官方文档的说法：

> ● If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extracolumn says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.  
> ● A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

以上说的是索引扫描的两种情况，一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示Using index，反之，如果在索引上进行全表扫描，没有Using index的提示。  
**L：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。(说明语句写的很失败)**

**说明：const、system、NULL指查询优化到常量级别, 甚至不需要查找时间.**

## 5、partitions ##

版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

## 6、possible\_keys ##

**注意: 系统估计可能用的几个索引,但最终,只能用1个.**

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

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible\_keys中的某些键实际上不能按生成的表次序使用。  
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

## 7、key ##

查询真正使用到的索引，select\_type为index\_merge时，这里可能出现两个以上的索引，其他的select\_type这里只会出现一个。

**key列显示MySQL实际决定使用的键（索引）**

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible\_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

## 8、key\_len ##

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

**不损失精确性的情况下，长度越短越好**

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key\_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key\_len中。

key\_len 的计算规则如下:

*  字符串
    
     *  char(n): n 字节长度
     *  varchar(n): 如果是 utf8 编码, 则是 3 *n + 2字节; 如果是 utf8mb4 编码, 则是 4 *n + 2 字节.
 *  数值类型:
    
     *  TINYINT: 1字节
     *  SMALLINT: 2字节
     *  MEDIUMINT: 3字节
     *  INT: 4字节
     *  BIGINT: 8字节
 *  时间类型
    
     *  DATE: 3字节
     *  TIMESTAMP: 4字节
     *  DATETIME: 8字节
 *  字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。key\_len的计算法方法：

<table> 
 <thead> 
  <tr> 
   <th>列类型</th> 
   <th>KEY_LEN</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id int</td> 
   <td>key_len = 4+1</td> 
   <td>int为4bytes,允许为NULL,加1byte</td> 
  </tr> 
  <tr> 
   <td>id bigint not null</td> 
   <td>key_len=8</td> 
   <td>bigint为8bytes</td> 
  </tr> 
  <tr> 
   <td>user char(30) utf8</td> 
   <td>key_len=30*3+1</td> 
   <td>utf8每个字符为3bytes,允许为NULL,加1byte</td> 
  </tr> 
  <tr> 
   <td>user varchar(30) not null utf8</td> 
   <td>key_len=30*3+2</td> 
   <td>utf8每个字符为3bytes,变长数据类型,加2bytes</td> 
  </tr> 
  <tr> 
   <td>user varchar(30) utf8</td> 
   <td>key_len=30*3+2+1</td> 
   <td>utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes</td> 
  </tr> 
  <tr> 
   <td>detail text(10) utf8</td> 
   <td>key_len=30*3+2+1</td> 
   <td>TEXT截取部分,被视为动态列类型。</td> 
  </tr> 
 </tbody> 
</table>

key\_len只指示了where中用于条件过滤时被选中的索引列，是不包含`order by`或`group by`这一部分被选中的索引列

## 9、ref ##

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

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

## 10、rows ##

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

这里是执行计划中估算的扫描行数，不是精确值

## 11、extra ##

**性能从好到坏: using index>using where > using temporary | using filesort**

对于extra列，官网上有这样一段话：

> If you want to make your queries as fast as possible, look out for Extra column values of Using filesort and Using temporary, or, in JSON-formatted EXPLAINoutput, for using\_filesort and using\_temporary\_table properties equal to true.

大概的意思就是说，如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

这个列可以显示的信息非常多，有几十种，常用的有：  
A：distinct：在select部分使用了distinc关键字  
B：no tables used：不带from字句的查询或者From dual查询  
C：使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。  
D：using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中  
**E：using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。**  
F：using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。  
G：using sort\_union，using\_union，using intersect，using sort\_intersection：  
using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集  
using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集  
using sort\_union和using sort\_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。  
H：using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used\_tmp\_table，used\_tmp\_disk\_table才能看出来。  
**I：using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤**。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，**可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量**。extra列显示using index condition  
J：firstmatch(tb\_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个  
K：loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息

## 12、filtered ##

使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

# 二、SQL优化之多表join #

## 关于Join ##

![format_png][]

如上图所示，left join和inner join的取值范围还是有很明显的不同的，看网上不少文章优化left join也不交代上下文环境，直接就是教唆把left join换成inner join，只能说是章口就莱，文体两开花...

## 关于笛卡尔积 ##

简言之就是强拼，不带条件的硬拼2（多）张表，结果就是直接做乘法

SELECT \* FROM a CROSS JOIN b;

SELECT \* FROM a INNER JOIN b;

SELECT \* FROM a,b;

SELECT \* FROM a NATURE JOIN b;

SELECT \* FROM a NATURA join b;

## 如何判断驱动表 ##

如果没有where条件

**左连接，如果没有where条件，则左表为驱动表**

![format_png 1][]

**右连接，如果没有where条件，右表为驱动表**

**如果有where条件**

**（参照《数据库索引设计与优化》第8章内容对外表的判定应该是下面写的这个样子）**

（参考读书笔记：[索引读书笔记][Link 1]）

![format_png 2][]

![format_png 3][]

**但是实际操作中有本地谓词的表并不一定百分百的是外表**，而是由优化器来判断的

## \#个人实际优化过程中用的最多的还是下面这条 ##

***explain规则：explain语句结果中，第一行为驱动表（该定律适用于join；子查询的话要分情况）***

--------------------

## 性能优化相关 ##

**①选谁做驱动表**

引用一个举烂了的例子，在没有过滤条件的情况下，**外表有多少行就会被加载多少次**；参考《索引设计与优化》这本书的说法，**每次加载相当于一次随机读**；

假设A表10000行，B表100行；也就是说如果用A表做驱动表会伴随着10000次随机读，而如果是B表作为驱动表，会伴随着100次随机读。

**      所以普遍的优化方案是小表作为外表（驱动表），用小表去驱动大表。**

**②创建合理的索引**

**      选择小表驱动大表，然后在内表（被驱动的大表）的连接谓词上建立索引，使得对内表的查询走索引提高效率。这种做法最高效的就是外表是一张小表，而内表的连接谓词刚好是内表的主键。**

在对这种没有查询过滤条件的语句执行explain的时候会非常不爽，因为驱动表通常是走的全表扫描；在我的测试中，如果外表上没有查询过滤条件（也就是本地谓词）的话，**无论查询项是否是索引项，都是走全表**。（换句话说，也就是在没有过滤条件的情况下，外表其实有没有索引对于当前语句的优化意义都不太明显（因为会走全表嘛），如果专门因为某个语句去对外表建一大堆索引的可以省省了）

**\#用下面的例子来解释上面2句话**

![format_png 4][]

m表和t表都是25W行左右，大小差距不大；根据开篇说的驱动表确立规则，无查询过滤条件的左连接，左表为驱动表；蓝框表明了这一点。

**绿框的t.charge\_no是t表的连接谓词，在这一列上建有一个选择性不错的索引**，因此在explain的第二行可以看到内表的查询走了索引，type为ref，效率还算不错。（如果这个连接谓词是t表的主键，效率会更高）

红框处可以看到，对m表取的查询值，我们仅仅取了m.charge\_no这一列，而且m.charge\_no上是建有索引的，但explain的第一行粉框的ALL表明m表的查询仍然走了全表（即使是我们m表的查询列只取了建有索引的列）。

**\#如何让外表走索引？**

**方法：对外表增加过滤条件（本地谓词），并且在本地谓词上创建索引**

![format_png 5][]

查询过滤条件中只有t表的本地谓词，因此无论左连接还是右连接，t表都是驱动表

由于**过滤条件t.partner\_id和t.trade\_date上建有索引，因此外表t不再走全表扫描**

注意2处蓝框是有关联的，右下角的那个大蓝框里面是没有temporary的，这与接下来讨论的有关

**③合理的排序**

前面2张图里面的语句是我在项目中遇到的一个慢查询，本体并非前面2张图贴出的那样，区别在于order by后面跟的字段不同，原语句order by后面跟着的是一个内表字段

![format_png 6][]

多表join执行时，优化器内部会先找出外表，之后会对外表进行排序；如果order by后面跟的是外表字段，则排序会在这时完成。但如果order by后面的字段是内表字段，则在所有结果集选出之后，再进行一次排序，这也就是执行计划里面Using temporary的由来了。

项目中的原语句一来没有本地谓词，二来还用内表进行排序，导致最后的执行时间达到8s之久；与研发沟通之后将语句改成用外表字段排序，执行时间所短到0.3s。

**④强行指定驱动表**

左右连接都可以指定驱动表，但（inner） join无法强行指定，通常来说都是通过系统自动去判定

**      强行指定方式：straight\_join**，这个参数会强行指定左表是外表

例子就截个别人的图吧

![format_png 7][]

--------------------

# 多表join优化案例 #

eg.

![format_png 8][]

红框处是可能需要加索引的各种连接谓词和本地谓词，蓝框标示了执行顺序扫描方式等帮助信息

**\#对执行计划进行分析**

在执行计划的第5行可以发现pcb表走了全表扫描，扫描行数达到95W行；通过执行计划的第4行可以发现这里有一个派生表，这个派生表是由pcb和ppv做inner join形成的，这里很明显pcb成了这个连接查询的外表，查询ppv表，发现ppv表仅仅只有19行。这是典型的大表驱动小表

查询pcb表的索引，发现pcb表的连接谓词和本地谓词上面都没有建索引！而ppv.id是ppv表的主键（mysql优化器大概就是因为这个原因让ppv表做了内表吧...）

把pcb表的连接谓词和本地谓词做了一个联合索引之后，扫描行数从95W缩减至5.4W，优化器很识趣的将ppv选做外表了

消灭了95W行的扫描之后，盘面上最刺眼的就是第二行那个2871了，对pc表的索引进行查询发现pc.client\_no列上也没有索引，由于pc.client\_no列是一个本地谓词，在添加索引之后，pc表直接跳到了explain的第一行。优化后的执行计划如下图

![format_png 9][]

对比pp表和pc表，pp表有16行数据，pc表有3000行数据，都不算太大，无论谁作为驱动表都不会有太大的性能提升了，优化也到此结束；执行时间由1s缩减到0.29s

# 其他优化点 #

需要优化的查询：使用explain

1.  **出现了Using temporary；**
2.  有分页时出现了Using filesort则表示使用不了索引，需要根据下面的技巧来调整语句
3.  **rows过多，或者几乎是全表的记录数；**
4.  **key 是 (NULL)；**
5.  **possible\_keys 出现过多（待选）索引。**

## 1.使用explain语法，对SQL进行解释，根据其结果进行调优： ##

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果：

a.EXPLAIN 结果中，第一行出现的表就是驱动表

b.**对驱动表可以直接排序**，对非驱动表（的字段排序）需要对循环查询的**合并结果（临时表）进行排序（Important!），即using temporary;**

c. \[驱动表\] 的定义为：**1）指定了联接条件时，满足查询条件的记录行数少的表为\[驱动表\]；2）未指定联接条件时，行数少的表为\[驱动表\]（Important!）。**

d.**优化的目标**是尽可能减少JOIN中Nested Loop的循环次数，以此保证：**永远用小结果集驱动大结果集**（Important!）！：A JOIN B，A为驱动，A中每一行和B进行循环JOIN，看是否满足条件，所以当A为小结果集时，越快。

e.NestedLoopJoin实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，**则再通过前两个表的Join结果集作为循环基础数据**，再一次通过循环查询条件到第三个表中查询数据，如此往复

## 2.两表JOIN优化： ##

a.当无order by条件时，根据实际情况，使用left/right/inner join即可，根据explain优化 ；

b.当有order by条件时，如select \* from a inner join b where 1=1 and other condition **order by a.col**；使用explain解释语句；

1）如果第一行的驱动表为a，则效率会非常高，无需优化；

2）否则，因为只能对驱动表字段直接排序的缘故，**会出现using temporary**，所以此时需要使用**STRAIGHT\_JOIN**明确a为驱动表，来达到使用a.col上index的优化目的；**或者使用left join且Where条件中不含b的过滤条件**，此时的结果集为a的全集，**而STRAIGHT\_JOIN为inner join且使用a作为驱动表**

## 3.多表JOIN优化： ##

a.无order by条件时，根据实际情况，使用left/right/inner join即可，根据explain优化；

b.有order by a.col条件时，所有join必须为left join，且每个join字段都创建索引，同时where条件中只能有a表的条件，即将其它表的数据关联到a中形成一张大表，再对a的全集进行过滤；

如果不能全使用left join，则需灵活使用**STRAIGHT\_JOIN及其它技巧，以时间排序为例：**

**   ** 1)数据入库按照平台时间入库，自然a的数据都按时间有序；

SELECT c.\*, r.HYPERVISOR\_HOST\_NAME hostname, r.HOST\_IP FROM trust\_monitor c **STRAIGHT\_JOIN** res\_node r ON c.res\_node\_id = r.ID **STRAIGHT\_JOIN** am\_assets a ON r.ASSET\_ID = a.ID AND a.status = 58 **STRAIGHT\_JOIN** se\_role s ON a.DEPT\_FLAG = s.ROLE\_ORG AND s.ROLE\_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed\_type = 79 limit 1,10;

SELECT c.\*, r.HYPERVISOR\_HOST\_NAME hostname, r.HOST\_IP FROM trust\_monitor c **inner JOIN** res\_node r ON c.res\_node\_id = r.ID **INNER JOIN** am\_assets a ON r.ASSET\_ID = a.ID AND a.status = 58 **INNER JOIN** se\_role s ON a.DEPT\_FLAG = s.ROLE\_ORG AND s.ROLE\_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed\_type = 79 **order by c.changed\_time** limit 1,10;

**两者结果一致**

4.误区：

a.视图只是屏蔽或者高效集合多表数据的一种方法，视图与表JOIN，不会起到任何效果

# optimizer\_trace表跟踪语句生成的执行计划的具体步骤 #

MySQL5.6在information\_schema中增加了optimizer\_trace表，用于跟踪语句生成的执行计划的具体步骤，包含各种关键的优化步骤。

可查看介绍：[http://blog.itpub.net/28218939/viewspace-2658978/][http_blog.itpub.net_28218939_viewspace-2658978]

### **opitimizer\_trace的使用：** ###

\# 开启跟踪,默认是关闭的

**SET optimizer\_trace="enabled=on";      **

\#执行你的sql语句

select ....

\#查看trace信息

SELECT \* FROM INFORMATION\_SCHEMA.OPTIMIZER\_TRACE;

\# 执行完你需要的sql语句就可以关闭trace了

**SET optimizer\_trace="enabled=off";**

### **支持的trace语句有：** ###

1.  SELECT;
2.  INSERT or REPLACE (with VALUES or SELECT);
3.  UPDATE/DELETE and their multi-table variants;
4.  all the previous ones prefixed by EXPLAIN;
5.  SET (unless it manipulates the optimizer\_trace system variable);
6.  DO;
7.  DECLARE/CASE/IF/RETURN (stored routines language elements);
8.  CALL.

### 这里比较in和between的区别 ###

使用时候可能与目前数据库数据有关，参考：[https://www.jianshu.com/p/20361fe96f0a][https_www.jianshu.com_p_20361fe96f0a]

分别看下三种不同语句的执行代价：

**1. SELECT \* FROM tin where c1 >= 100 and c1 <= 104;**

“chosen\_range\_access\_summary”: \{  
                    “range\_access\_plan”: \{  
                      “type”: “range\_scan”,  
                      “index”: “PRIMARY”,  
                      “rows”: 5,  
                      “ranges”: \[  
                        “100 <= c1 <= 104″  
                      \]  
                    \},  
                    “rows\_for\_plan”: 5,  
                    “cost\_for\_plan”: 2.0188,  
                    “chosen”: true  
                    \}

**2. SELECT \* FROM tin where c1 in (100, 101, 102, 103, 104);**

“chosen\_range\_access\_summary”: \{  
                    “range\_access\_plan”: \{  
                      “type”: “range\_scan”,  
                      “index”: “PRIMARY”,  
                      “rows”: 5,  
                      “ranges”: \[  
                        “100 <= c1 <= 100″,  
                        “101 <= c1 <= 101″,  
                        “102 <= c1 <= 102″,  
                        “103 <= c1 <= 103″,  
                        “104 <= c1 <= 104″  
                      \]  
                    \},  
                    “rows\_for\_plan”: 5,  
                    “cost\_for\_plan”: 6.0188,  
                    “chosen”: true  
                  \}

**3. SELECT \* FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 = 104;**

# 参考： #

[https://www.jianshu.com/p/73f2c8448722][https_www.jianshu.com_p_73f2c8448722]

[https://www.jianshu.com/p/8b98a204378b][https_www.jianshu.com_p_8b98a204378b]

[https://www.cnblogs.com/uttu/p/6384541.html][https_www.cnblogs.com_uttu_p_6384541.html]

[https://www.cnblogs.com/katec/p/9239985.html][https_www.cnblogs.com_katec_p_9239985.html]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTdiYjM4M2IyMGMwODM1YWMucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvNTY5L2Zvcm1hdC93ZWJw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTcwZThkMGJmNTIzYjEzYjAucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvMTIwMC9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[Link 1]: https://www.jianshu.com/p/d5ffffecc399
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTc3YmZiZjgwNzE3NTljOWEucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvNjAxL2Zvcm1hdC93ZWJw?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTI3YWZlMWY0YWRhOTczYWIucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvMTIwMC9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTdmOGM1MGEyZjVlNWY5YTQucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvOTA4L2Zvcm1hdC93ZWJw?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTRlMjc3NjRjY2E1ZTQ3OWYucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvMTIwMC9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LWIzMDE0ZWQ2N2Y3NTZkNGIucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvMTIwMC9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTkwOTg1NGE1NTVkNzRiZTgucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvNzQzL2Zvcm1hdC93ZWJw?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTExNDZjMDU5NWJlYTVjYTYucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvMTIwMC9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy85Mjg5ODE2LTgyODg5OWM5NjBjNmE1ZWYucG5nP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXB8aW1hZ2VWaWV3Mi8yL3cvMTIwMC9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[http_blog.itpub.net_28218939_viewspace-2658978]: http://blog.itpub.net/28218939/viewspace-2658978/
[https_www.jianshu.com_p_20361fe96f0a]: https://www.jianshu.com/p/20361fe96f0a
[https_www.jianshu.com_p_73f2c8448722]: https://www.jianshu.com/p/73f2c8448722
[https_www.jianshu.com_p_8b98a204378b]: https://www.jianshu.com/p/8b98a204378b
[https_www.cnblogs.com_uttu_p_6384541.html]: https://www.cnblogs.com/uttu/p/6384541.html
[https_www.cnblogs.com_katec_p_9239985.html]: https://www.cnblogs.com/katec/p/9239985.html