6.读懂mysql执行计划

分手后的思念是犯贱 2021-12-20 08:53 274阅读 0赞

### 文章目录 ###

*  1.执行计划概念和语法
 *   *  1.执行计划的概念
     *  2.执行计划的语法
     *   *  1.常规执行计划语法
         *  2.扩展执行计划的语法
         *  3.分区表的执行计划语法
 *  2.执行计划包含的信息
 *   *  1.id：查询的顺序
     *  2.select\_type：查询类型
     *  3.table：查询涉及到的表
     *  4.type：访问类型
     *  5.possible\_keys：可能使用的索引
     *  6.key：实际使用的索引
     *  7.key\_length：索引长度
     *  8.ref：连接匹配条件
     *  9.rows：估算的结果集数量
     *  10.extra：额外信息
     *  11.参考文档

# 1.执行计划概念和语法 #

## 1.执行计划的概念 ##

我们都知道mysql对于一条sql在执行过程中会对它进行优化，而对于查询语句的来说最好的优化方案就是使用索引。而执行计划就是显示mysql执行sql时的详细执行情况。其中包含了是否使用索引，使用了那些索引…

## 2.执行计划的语法 ##

> 下面的示例均以一下用户信息表(user)和用户地址信息表(address)为例。  
> user 表：

CREATE TABLE `user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `username` varchar(100) DEFAULT NULL,
      `password` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name` (`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

address表：

CREATE TABLE `address` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `address` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

表数据如下：  
user表：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>username</th> 
   <th>password</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>p1</td> 
   <td>123</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>p2</td> 
   <td>456</td> 
  </tr> 
 </tbody> 
</table>

address表：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>user_id</th> 
   <th>address</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>邯郸</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>德州</td> 
  </tr> 
 </tbody> 
</table>

### 1.常规执行计划语法 ###

explain select * from user;

### 2.扩展执行计划的语法 ###

explain 的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过mysql的show warnings命令得到。

explain extended select * from user;

### 3.分区表的执行计划语法 ###

explain partitions select * from user;

# 2.执行计划包含的信息 #

不同版本的Mysql和不同的存储引擎执行计划不完全相同，但基本信息都差不多。mysql执行计划主要包含以下信息:

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>user</td> 
   <td>ALL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>1</td> 
   <td>NULL</td> 
  </tr> 
 </tbody> 
</table>

## 1.id：查询的顺序 ##

由一组数字组成，表示一个查询中各个子查询的执行顺序。

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>id不同情况</th> 
   <th>执行顺序</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>id相同</td> 
   <td>执行顺序由上至下</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>id不同</td> 
   <td>值越大优先级越高，越先被执行</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>id为null</td> 
   <td>表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中</td> 
  </tr> 
 </tbody> 
</table>

## 2.select\_type：查询类型 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">id</th> 
   <th align="left">select_type</th> 
   <th align="left">description</th> 
   <th>举例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">SIMPLE</td> 
   <td align="left">不包含任何子查询或union等查询</td> 
   <td>EXPLAIN SELECT * FROM user WHERE id=1;</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">包含子查询最外层查询就显示为 <code>PRIMARY</code></td> 
   <td>EXPLAIN SELECT * FROM <code>user</code> WHERE id=(SELECT user_id FROM address WHERE id=1);</td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left">SUBQUERY</td> 
   <td align="left">在<code>select</code>或 <code>where</code>字句中包含的查询</td> 
   <td>EXPLAIN SELECT * FROM <code>user</code> WHERE id=(SELECT user_id FROM address WHERE id=1);</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">DERIVED</td> 
   <td align="left"><code>from</code>字句中包含的查询（衍生查询）</td> 
   <td>EXPLAIN SELECT * FROM (SELECT * FROM user WHERE id&gt;1) t;</td> 
  </tr> 
  <tr> 
   <td align="left">5</td> 
   <td align="left">UNION</td> 
   <td align="left">出现在<code>union</code>后的查询语句中（当前执行计划的<code>中间记录</code>就是 UNION RESULT））</td> 
   <td>EXPLAIN SELECT * FROM user WHERE id=1 UNION SELECT * FROM user WHERE id=2;</td> 
  </tr> 
  <tr> 
   <td align="left">6</td> 
   <td align="left">UNION RESULT</td> 
   <td align="left">从UNION中获取结果集当前执行计划的<code>最后一条记录</code>就是 UNION RESULT）</td> 
   <td>EXPLAIN SELECT * FROM user WHERE id=1 UNION SELECT * FROM user WHERE id=2;（</td> 
  </tr> 
 </tbody> 
</table>

## 3.table：查询涉及到的表 ##

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

## 4.type：访问类型 ##

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>type</th> 
   <th>含义</th> 
   <th>例子</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>ALL</td> 
   <td>全表扫描（没有使用到索引）</td> 
   <td>EXPLAIN SELECT * FROM user WHERE PASSWORD=‘1’;</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>index</td> 
   <td>遍历索引（只查询索引列）</td> 
   <td>EXPLAIN SELECT id FROM user ;</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>range</td> 
   <td>索引范围查找(在索引列添加范围查询)</td> 
   <td>EXPLAIN SELECT id FROM user where id&gt;1;</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>index_subquery</td> 
   <td>在子查询中使用 ref</td> 
   <td>暂时没有找到例子</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>unique_subquery</td> 
   <td>在子查询中使用 eq_ref</td> 
   <td>暂时没有合适例子</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>ref_or_null</td> 
   <td>对Null进行索引的优化的 ref</td> 
   <td>暂时没有合适例子</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>fulltext</td> 
   <td>使用全文索引</td> 
   <td>暂时没有合适例子</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>ref</td> 
   <td>使用非唯一索引查找数据</td> 
   <td>EXPLAIN SELECT id FROM <code>user</code> WHERE username=‘p1’;</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>eq_ref</td> 
   <td>在join查询中使用 PRIMARY KEY or UNIQUE NOT NULL索引关联 (这里需要把address的user_id设置为unique类型的索引)</td> 
   <td>EXPLAIN SELECT * FROM user u left JOIN address a on u.id=a.user_id WHERE a.id&gt;1;</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>const</td> 
   <td>使用主键或者唯一索引，且匹配的结果只有一条记录</td> 
   <td>EXPLAIN SELECT * FROM <code>user</code> WHERE id=1;</td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td>system const</td> 
   <td>连接类型的特例，查询的表为系统表</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

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

**所以，如果通过执行计划发现某张表的查询语句的type显示为ALL，那就要考虑添加索引，或者更换查询方式，使用索引进行查询。**

## 5.possible\_keys：可能使用的索引 ##

注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 `NULL`时就要考虑当前的`SQL`是否需要优化了。

## 6.key：实际使用的索引 ##

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

TIPS:查询中若使用了覆盖索引（覆盖索引：索引的数据覆盖了需要查询的所有数据），则该索引仅出现在key列表中。select\_type为index\_merge时，这里可能出现两个以上的索引，其他的select\_type这里只会出现一个。

## 7.key\_length：索引长度 ##

char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

## 8.ref：连接匹配条件 ##

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了**内部隐式转换**，这里可能**显示为func**。

## 9.rows：估算的结果集数量 ##

返回估算的结果集数目，注意这并**不是一个准确值**。

## 10.extra：额外信息 ##

extra的信息非常丰富，常见的有如下4种类型：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>extra</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Using index</td> 
   <td>使用覆盖索引</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Using where</td> 
   <td>使用了用where子句来过滤结果集</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Using filesort</td> 
   <td>使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Using temporary</td> 
   <td>使用了临时表</td> 
  </tr> 
 </tbody> 
</table>

## 11.参考文档 ##

读懂执行计划： http://blog.jobbole.com/112877/
    
    会写mysql索引： http://blog.jobbole.com/113142/
    
    mysql 查询优化： https://www.cnblogs.com/danhuangpai/p/8475458.html
    
    mysql优化： https://www.cnblogs.com/danhuangpai/category/1142859.html