Mysql中解释Explain 详解

超、凢脫俗 2022-02-03 10:29 383阅读 0赞

**前言**

Github：[https://github.com/HealerJean][https_github.com_HealerJean]

博客：[http://blog.healerjean.com][http_blog.healerjean.com]

> 数据准备
> 
> **mysql优化器在数据量不同的情况下，也会到结果产生影响**

create table `user`
    (
        `id`    bigint(20) unsigned not null auto_increment,
        `name`  varchar(64)         default null,
        `age`   bigint(20) unsigned default '0',
        `param` varchar(32)         default null,
        `a`     int(11)             default '0',
        `b`     int(11)             default '0',
        `c`     int(11)             default '0',
        primary key (`id`),
        key `idx_a_b_c_d` (`a`, `b`, `c`),
        key `idx_age` (`age`),
        key `idx_name` (`name`)
    );
    
    
    
    create table `order_info`
    (
        `id`          bigint(16) unsigned not null auto_increment,
        `ref_user_id` bigint(20) unsigned not null,
        `serial_no`   varchar(32)         not null,
        primary key (`id`),
        key `uk_user_id` (`ref_user_id`)
    );
    
    
    
    
    INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (1, 'xiaoming', 11, 'a', 1, 2, 3);
    INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (2, 'xiaohong', 23, 'b', 21, 21, 21);
    INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (3, 'liuqiangdong', 45, 'c', 56, 23, 23);
    INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (4, 'mayun', 123, 'd', 45, 12, 3);
    INSERT INTO hlj_sql.user (id, name, age, param, a, b, c) VALUES (5, 'leijun', 5, 'e', 12, 322, 1);
    
    
    INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (1, 1, '2');
    INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (2, 2, '3');
    INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (3, 3, '5');
    INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (4, 2, '3');
    INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (5, 4, '3');
    INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (6, 5, '6');
    INSERT INTO hlj_sql.order_info (id, ref_user_id, serial_no) VALUES (7, 4, '3');

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>查询的标识符</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>SELECT 查询的类型.</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>查询的是哪个表</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>判断是什么扫描查询 比如：ALL,Index，Rank</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>可能选用的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>确切使用到的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>索引长度（<strong>通过观察这个可以判断联合索引使用了几列，很有用</strong>）</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>哪个字段或常数与 key 一起被使用</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>

# 1、`select_type` #

<table> 
 <thead> 
  <tr> 
   <th>select_type</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>SIMPLE</code></td> 
   <td>简单查询</td> 
  </tr> 
  <tr> 
   <td><code>UNION</code></td> 
   <td>联合查询</td> 
  </tr> 
  <tr> 
   <td><code>SUBQUERY</code></td> 
   <td>子查询</td> 
  </tr> 
  <tr> 
   <td><code>UNION RESULT</code></td> 
   <td>联合查询的结果</td> 
  </tr> 
  <tr> 
   <td><code>PRIMARY</code></td> 
   <td>最外层查询</td> 
  </tr> 
 </tbody> 
</table>

## 1.1、`SIMPLE` 简单查询 ##

> ##### **解释：此查询不包含 UNION 查询或子查询** #####

explain  select * from user ;

## 1.2、`UNION` 联合查询 ##

> ##### **解释：表示此查询是 UNION 的第二或随后的查询** #####

explain  select * from user union  select * from user ;
    
    id = 1  PRIMARY 外层查询    
    id = 2  UNION 联合查询
    id = 3  UNION RESULT 很明显为联合查询的结果

<table> 
 <thead> 
  <tr> 
   <th align="left">id</th> 
   <th align="left">select_type</th> 
   <th align="left">table</th> 
   <th align="left">partitions</th> 
   <th align="left">type</th> 
   <th align="left">possible_keys</th> 
   <th align="left">key</th> 
   <th align="left">key_len</th> 
   <th align="left">ref</th> 
   <th align="left">rows</th> 
   <th align="left">filtered</th> 
   <th align="left">Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">user</td> 
   <td align="left">NULL</td> 
   <td align="left">ALL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">5</td> 
   <td align="left">100</td> 
   <td align="left">NULL</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">UNION</td> 
   <td align="left">user</td> 
   <td align="left">NULL</td> 
   <td align="left">ALL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">5</td> 
   <td align="left">100</td> 
   <td align="left">NULL</td> 
  </tr> 
  <tr> 
   <td align="left">NULL</td> 
   <td align="left">UNION RESULT</td> 
   <td align="left">&lt;union1,2&gt;</td> 
   <td align="left">NULL</td> 
   <td align="left">ALL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">Using temporary</td> 
  </tr> 
 </tbody> 
</table>

## 1.3、`SUBQUERY` 子查询 ##

explain
    select *
    from order_info o
    where id > (select b.id from user b where b.id = 1); 
            
            
    1、第一个 select 为 PRIMARY   最外层查询      
    2、第二个 select 为 SUBQUERY  子查询

<table> 
 <thead> 
  <tr> 
   <th align="left">id</th> 
   <th align="left">select_type</th> 
   <th align="left">table</th> 
   <th align="left">partitions</th> 
   <th align="left">type</th> 
   <th align="left">possible_keys</th> 
   <th align="left">key</th> 
   <th align="left">key_len</th> 
   <th align="left">ref</th> 
   <th align="left">rows</th> 
   <th align="left">filtered</th> 
   <th align="left">Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">o</td> 
   <td align="left">NULL</td> 
   <td align="left">ALL</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">7</td> 
   <td align="left">85.71</td> 
   <td align="left">Using where</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">SUBQUERY</td> 
   <td align="left">b</td> 
   <td align="left">NULL</td> 
   <td align="left">const</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">8</td> 
   <td align="left">const</td> 
   <td align="left">1</td> 
   <td align="left">100</td> 
   <td align="left">Using index</td> 
  </tr> 
 </tbody> 
</table>

## 1.4、`UNION RESULT` 联合查询的结果 ##

> **解释：在 `1.2` 中介绍过了**

## 1.5、`PRIMARY` 最外层查询 ##

> **解释：在 `1.2`和 `1.3`中介绍过**

# 2、type #

> **解释：它提供了判断查询是否高效的重要依据依据. 通过 `type` 字段, 我们判断此次查询是 `全表扫描` 还是 `索引扫描` 等，要和Extra同时观察会更好**
> 
> 性能：**ALL < index < range ~ index\_merge < ref < eq\_ref < const < system**

## 2.1、`ALL` ##

> **解释：全表扫描**

explain  select * from user ;

## 2.2、`index` ##

> **解释：表示全索引扫描 （索引覆盖）和ALL类似**
> 
> 1、`index`: 表示全索引扫描， 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, **而 index 类型则仅仅扫描所有的索引, 而不扫描数据**. 其实就是讲 查询条件 写上索引的字段
> 
> 2、`index` 类型通常出现在: **所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据.** **当是这种情况时, Extra 字段 会显示 `Using index`**
> 
> 3、`index` 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.

explain   select name from user ;

## 2.3、`range` ##

> **解释：索引范围内查询** ，通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中

explain  select * from user  where  id > 2;

## 2.4、`INDEX_MERGE` ##

> **解释：合并索引，使用多个单列索引搜索**

explain   select id from user  where  id = 2 or  name = 'xiaoming';

## 2.5、`REF` ##

> **解释：根据索引查找一个或多个值**

explain   select id from user  where  name = 'xiaoming';

## 2.6、`eq_ref` ##

> **解释：连接join查询时，使用primary key 或 unique类型，其实就是说索引唯一的关联查询**

explain
    select *
    from order_info o
             join user u on u.id = o.ref_user_id;

<table> 
 <thead> 
  <tr> 
   <th align="left">id</th> 
   <th align="left">select_type</th> 
   <th align="left">table</th> 
   <th align="left">partitions</th> 
   <th align="left">type</th> 
   <th align="left">possible_keys</th> 
   <th align="left">key</th> 
   <th align="left">key_len</th> 
   <th align="left">ref</th> 
   <th align="left">rows</th> 
   <th align="left">filtered</th> 
   <th align="left">Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">SIMPLE</td> 
   <td align="left">o</td> 
   <td align="left">NULL</td> 
   <td align="left">ALL</td> 
   <td align="left">uk_user_id</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">NULL</td> 
   <td align="left">7</td> 
   <td align="left">100</td> 
   <td align="left">NULL</td> 
  </tr> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">SIMPLE</td> 
   <td align="left">u</td> 
   <td align="left">NULL</td> 
   <td align="left">eq_ref</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">PRIMARY</td> 
   <td align="left">8</td> 
   <td align="left">hlj_sql.o.ref_user_id</td> 
   <td align="left">1</td> 
   <td align="left">100</td> 
   <td align="left">NULL</td> 
  </tr> 
 </tbody> 
</table>

## 2.7、`const` ##

> 解释：针对主键或唯一索引的等值查询扫描，只有一行

explain   select id from user  where  id = 1;

## 2.8、`system` ##

> 解释：表中仅仅有一条数据，这个是特殊的const查询

# 3、`possible_keys` #

> 可能用到的索引，看 4

# 4、`key` #

> \*\*解释：表示 MySQL 在查询时, 真实使用到的索引， \*\*
> 
> **即使有些索引在 `possible_keys` 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 `key` 字段决定…**

下面这个条件中使用了 索引id 和 联合索引 ref_user_id   
    实际上我们只使用了 索引id 进行查询，所以 key是id ，possible_keys 是id和 ref_user_id
    
    explain   select id from order_info  where  id = 1 and ref_user_id = 1;

# 5、`key_len` #

> **解释： 使用索引字节长度，这个字段可以评估联合索引是否完全被使用**

## 5.1、字符串 ##

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>索引长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char(n)</td> 
   <td>n</td> 
  </tr> 
  <tr> 
   <td>varchar(n)</td> 
   <td>如果是 utf8，3 n + 2</td> 
  </tr> 
  <tr> 
   <td>varchar(n)</td> 
   <td>如果是 utf8mb4 ，则是 4 n + 2 字节.</td> 
  </tr> 
 </tbody> 
</table>

## 5.2、数值类型: ##

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>索引长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>float</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>decimal</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

## 5.3、时间类型 ##

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>year</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>date</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>datetime</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>4</td> 
  </tr> 
 </tbody> 
</table>

# 6、`rows` #

> 显示此查询一共扫描了多少行. 这个是一个估计值.

# 7、Extra #

> ##### 解释 ： 额外信息，优化器会在索引存在的情况下，通过符合 RANGE 范围的条数和总数的比例来选择是使用索引还是进行全表遍历， #####
> 
> ##### 具体案例例具体分析，不要把这里想复杂了，就是一个额外的信息而已 #####

**名词解释：**

**回表：表示即使使用索引筛选了，但是查询的字段不是全部都是索引列**

<table> 
 <thead> 
  <tr> 
   <th>Extra</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NULL</td> 
   <td>查询的不全都是索引</td> 
  </tr> 
  <tr> 
   <td>using index</td> 
   <td>使用覆盖索引的时候就会出现</td> 
  </tr> 
  <tr> 
   <td>using index condition</td> 
   <td>查询条件是索引的一个范围</td> 
  </tr> 
  <tr> 
   <td>using where</td> 
   <td>查询条件包含普通的条件</td> 
  </tr> 
  <tr> 
   <td>Using filesort</td> 
   <td>排序 不能通过索引达到排序效果</td> 
  </tr> 
 </tbody> 
</table>

`using index` > `using where` > `using index condition` ，如果不需要回表查询数据，效率上应该比较快的

![ContactAuthor][]

[https_github.com_HealerJean]: https://github.com/HealerJean
[http_blog.healerjean.com]: http://HealerJean.github.io
[ContactAuthor]: /images/20220203/12d1ffcb93cf4b19a48412b3835c237e.png