MySQL的explain命令详解

朱雀 2022-07-15 02:29 130阅读 0赞

在MySQL中，当需要对某条SQL查询语句进行分析时，我们经常会使用 explain 命令 或 desc 命令进行操作，分析SQL语句时，explain 和 desc 的作用是一样的。

使用 explain 命令可以分析出SQL查询语句中索引的使用情况、扫描的行数、扫描的类型等等，以便帮助我们对索引和SQL语句进行优化。

使用方法：在SQL查询语句前面，加上 explain 或 desc 即可。

为了便于演示，现在我创建了一张people表：

create table people ( id int unsigned not null auto_increment primary key comment '主键id', last_name varchar(20) not null default '' comment '姓', first_name varchar(20) not null default '' comment '名', birthday date not null default '1970-01-01' comment '出生日期', gender tinyint unsigned not null default 3 comment '性别：1男，2女，3未知', key(last_name, first_name, birthday) ) engine=innodb default charset=utf8;

可以看出，people表中有一个主键索引（id）和一个复合索引 key(last\_name, first\_name, birthday)。

people表中也已经插入了如下一些数据：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>last_name</th> 
   <th>first_name</th> 
   <th>birthday</th> 
   <th>gender</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Clinton</td> 
   <td>Bill</td> 
   <td>1970-01-01</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Allen</td> 
   <td>Cuba</td> 
   <td>1960-01-01</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Bush</td> 
   <td>George</td> 
   <td>1970-01-01</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Smith</td> 
   <td>Kim</td> 
   <td>1970-01-01</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Allen</td> 
   <td>Cally</td> 
   <td>1989-06-08</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

然后，执行下面的语句：

explain select id,first_name from people where id=3 \G

结果如下：

*************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: people
     type: const
    possible_keys: PRIMARY
     key: PRIMARY
     key_len: 4
     ref: const
     rows: 1
     Extra:
    1 row in set (0.00 sec)

下面，对explain分析结果中的各个参数进行详细说明。

#### **1. id** ####

explain 得到的结果集的id编号，不重要。

#### **2. select\_type** ####

select\_type，即SQL查询语句的类型，准确地说，应该是当前的select语句操作table的方式。常见的值有以下几种：

*  SIMPLE 它表示简单的单表查询，没有union和子查询。
 *  PRIMARY 它表示主表（也就是最外层的表）查询。这个类型通常可以在DERIVED和UNION时见到。
 *  DERIVED 它表示派生表查询，派生表本身不是一个物理表。
 *  DEPENDENT SUBQUERY 它表示子查询。
 *  UNION 它表示 union 语句中的查询。

#### **3. table** ####

它表示和当前的输出结果相关的表。

#### **4. type** ####

type 是一个非常重要的参数，也较为复杂，它表示了当前的查询所使用的查找数据行的扫描方式或访问类型。访问类型有很多种，比如：全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用扫描等等。这里列的这些，速度是从慢到快，扫描的行数也是从多到少。现在，我们就根据这个顺序介绍几个常见的扫描方式：

*  ALL 全表扫描，表示需要扫描整张表，才能获取到需要的数据。不会使用到索引，一般来说，它的性能最差。如：
    
        desc select id,first_name from people where gender=2;
 *  index 索引扫描，表示仅从索引中扫描获取数据，index 和 all 差不多，都需要扫描全部的记录，只不过 index 是从索引中扫描全部记录获取需要的数据。虽然从索引中扫描获取了数据，但实际上并没有用到索引加快查找速度的功能。也就是说索引失效了。性能也较差。如：
    
        explain select id,last_name,first_name,birthday from people where first_name='Cuba';
 *  range 范围扫描，表示在给定的范围内进行扫描，会使用到索引，性能较好。如：
    
        explain select id,first_name from people where id < 3;
        或
        explain select id,first_name from people where last_name BETWEEN 'Allen' And 'Clinton';
 *  ref 常规索引匹配扫描，表示用到的索引是常规索引（也叫普通索引），性能较好。需要注意的是，复合索引中的列顺序，要想使索引生效，需遵循最左前缀匹配法则。如：
    
        explain select id,first_name from people where last_name='Allen' and first_name='Cuba';
 *  eq\_ref 索引关联扫描，联表查询时，如果关联的键是主键，就会出现这个值。性能较好。
 *  const 常量引用扫描，表示用到的索引是主键或唯一索引，索引会等值匹配一个定值。性能最好。如：
    
        explain select id,first_name from people where id=3;

#### **5. possible\_keys** ####

表示当前查询可能用到的索引，这个值不太重要。

#### **6. key** ####

表示当前查询实际用到的索引。

#### **7. key\_len** ####

表示当前查询用到的索引的长度（字节数）。

#### **8. ref** ####

表示索引的哪一列被用到了，如果是等值匹配索引，则是一个常数 const。

#### **9. rows** ####

表示当前查询实际扫描的行数。值越小越好。这个参数非常重要。

#### **10. Extra** ####

表示当前查询的额外信息，比如是根据什么方式排序的，获取数据的方式等。下面列举几个Extra参数常见的值：

*  using filesort 表示当前查询做了额外的步骤将结果集进行文件排序。如果看到这个值，就需要进行优化了。
 *  using temporary 表示当前查询使用了一个临时表来存储结果。如果看到这个值，也需要进行优化。
 *  using index 表示只使用索引就可以满足全部的查询需求，不需要再回表查询数据行，通常称之为索引覆盖查询。

通常，如果我们明确知道了哪条SQL查询语句效率很差，就可以用 explain 或 desc 来分析该SQL语句。但是，大多数情况下，我们并不知道是哪些SQL查询语句有问题，一般都是开启MySQL的慢查询日志，通过慢查询日志来确定查询效率较慢的问题SQL，然后再对这些问题SQL进行分析和优化。