explain | 索引优化的这把绝世好剑，你真的会用吗？

淩亂°似流年 2024-04-07 13:44 71阅读 0赞

### 前言 ###

对于互联网公司来说，随着用户量和数据量的不断增加，慢查询是无法避免的问题。一般情况下如果出现慢查询，意味着接口响应慢、接口超时等问题。如果是高并发的场景，可能会出现数据库连接被占满的情况，直接导致服务不可用。

慢查询的确会导致很多问题，我们要如何优化慢查询呢？

主要解决办法有：

*  监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql
 *  打开数据库慢查询日志功能
 *  简化业务逻辑
 *  代码重构、优化
 *  异步处理
 *  sql优化
 *  索引优化

其他的办法先不说，后面有机会再单独介绍。今天我重点说说索引优化，因为它是解决慢查询sql问题最有效的手段。

如何查看某条sql的索引执行情况呢？

没错，在sql前面加上`explain`关键字，就能够看到它的执行计划，通过执行计划，我们可以清楚的看到表和索引执行的情况，索引有没有执行、索引执行顺序和索引的类型等。

索引优化的步骤是：

1.  使用`explain`查看sql执行计划
2.  判断哪些索引使用不当
3.  优化sql，sql可能需要多次优化才能达到索引使用的最优值

既然索引优化的第一步是使用`explain`，我们先全面的了解一下它。

### explain介绍 ###

先看看mysql的官方文档是怎么描述explain的：

![4575c4dff1aa4faba0bb2a38ec2f3727.png][]

*  EXPLAIN可以使用于 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句。
 *  当EXPLAIN与可解释的语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。
 *  当EXPLAIN与非可解释的语句一起使用时，它将显示在命名连接中执行的语句的执行计划。
 *  对于SELECT语句， EXPLAIN可以显示的其他执行计划的警告信息。

### explain详解 ###

explain的语法：

{EXPLAIN | DESCRIBE | DESC}
        tbl_name [col_name | wild]
    
    {EXPLAIN | DESCRIBE | DESC}
        [explain_type]
        {explainable_stmt | FOR CONNECTION connection_id}
    
    explain_type: {
        EXTENDED
      | PARTITIONS
      | FORMAT = format_name
    }
    
    format_name: {
        TRADITIONAL
      | JSON
    }
    
    explainable_stmt: {
        SELECT statement
      | DELETE statement
      | INSERT statement
      | REPLACE statement
      | UPDATE statement
    }

用一条简单的sql看看使用`explain`关键字的效果：

explain  select * from test1;

执行结果：

![71a131553af24093887976b6c23e069e.png][]从上图中看到执行结果中会显示12列信息，每列具体信息如下： ![62acacb688c04c1480f721fcd51809c2.png][]

说白了，我们要搞懂这些列的具体含义才能正常判断索引的使用情况。

话不多说，直接开始介绍吧。

#### id列 ####

该列的值是select查询中的序号，比如：1、2、3、4等，它决定了表的执行顺序。

某条sql的执行计划中一般会出现三种情况：

1.  id相同
2.  id不同
3.  id相同和不同都有

那么这三种情况表的执行顺序是怎么样的呢？

1.id相同

执行sql如下：

explain  select * from test1 t1 inner  join test1 t2 on t1.id=t2.id

结果：

![82828ed86f394628bcd9cd16cbd93cc1.png][]我们看到执行结果中的两条数据id都是1，是相同的。

这种情况表的执行顺序是怎么样的呢？

答案：从上到下执行，先执行表t1，再执行表t2。

执行的表要怎么看呢？

答案：看table字段，这个字段后面会详细解释。

2.id不同

执行sql如下：

explain  select * from test1 t1 where t1.id = (select  id  from  test1 t2 where  t2.id=2);

结果：

![179cae12028841ae87978c7925ba408a.png][]我们看到执行结果中两条数据的id不同，第一条数据是1，第二条数据是2。

这种情况表的执行顺序是怎么样的呢？

答案：序号大的先执行，这里会从下到上执行，先执行表t2，再执行表t1。

3.id相同和不同都有

执行sql如下：

explain 
    select t1.* from test1 t1
    inner  join (select  max(id) mid  from test1 group  by  id) t2
    on t1.id=t2.mid

结果：![e9ac2b5b5de643f88f1cf54f2c0e5ba3.png][]

我们看到执行结果中三条数据，前面两条数据的的id相同，第三条数据的id跟前面的不同。

这种情况表的执行顺序又是怎么样的呢？

答案：先执行序号大的，先从下而上执行。遇到序号相同时，再从上而下执行。所以这个列子中表的顺序顺序是：test1、t1、

**也许你会在这里心生疑问：`<``derived2>` 是什么鬼？**

它表示派生表，别急后面会讲的。

**还有一个问题：id列的值允许为空吗？**

答案在后面揭晓。

#### select\_type列 ####

该列表示select的类型。具体包含了如下11种类型：![8b5659a4513149359d279f6d990f9396.png][]

但是常用的其实就是下面几个：

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单SELECT查询，不包含子查询和UNION</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>复杂查询中的最外层查询，表示主要的查询</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>SELECT或WHERE列表中包含了子查询</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>FROM列表中包含的子查询，即衍生</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>UNION关键字之后的查询</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>从UNION后的表获取结果集</td> 
  </tr> 
 </tbody> 
</table>

下面看看这些SELECT类型具体是怎么出现的：

1.  SIMPLE
    
    执行sql如下：
    
        explain select * from test1;
    
    结果：![7e8e1ee84aa9474b9f9aa5bc5c4f31da.png][]
    
    它只在简单SELECT查询中出现，不包含子查询和UNION，这种类型比较直观就不多说了。
2.  PRIMARY 和 SUBQUERY
    
    执行sql如下：
    
        explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);
    
    结果：![f87947de622041eb8729397a9304c502.png][]
    
    我们看到这条嵌套查询的sql中，最外层的t1表是PRIMARY类型，而最里面的子查询t2表是SUBQUERY类型。
3.  DERIVED
    
    执行sql如下：
    
        explain
        select t1.* from test1 t1
        inner join (select max(id) mid from test1 group by id) t2
        on t1.id=t2.mid
    
    结果：![2087fa55590641a8b2a10501f69f6cab.png][]
    
    最后一条记录就是衍生表，它一般是FROM列表中包含的子查询，这里是sql中的分组子查询。
4.  UNION 和 UNION RESULT
    
    执行sql如下：
    
        explain
        select * from test1
        union
        select* from test2
    
    结果：![2ab2efa3be684c858cee1a23864f8029.png][]

test2表是UNION关键字之后的查询，所以被标记为UNION，test1是最主要的表，被标记为PRIMARY。而<union1,2>表示id=1和id=2的表union，其结果被标记为UNION RESULT。

UNION 和 UNION RESULT一般会成对出现。

**此外，回答上面的问题：****id列的值允许为空吗？**

如果仔细看上面那张图，会发现id列是可以允许为空的，并且是在SELECT类型为： UNION RESULT的时候。

#### table列 ####

该列的值表示输出行所引用的表的名称，比如前面的：test1、test2等。

但也可以是以下值之一：

*  `<unionM,N>`：具有和id值的行的M并集N。
 *  `<derivedN>`：用于与该行的派生表结果id的值N。派生表可能来自（例如）FROM子句中的子查询 。
 *  `<subqueryN>`：子查询的结果，其id值为N

#### partitions列 ####

该列的值表示查询将从中匹配记录的分区

#### type列 ####

该列的值表示连接类型，是查看索引执行情况的一个重要指标。包含如下类型：![0907bcb7a27a43ca932af08af08ff346.png][]

执行结果从最好到最坏的的顺序是从上到下。

我们需要重点掌握的是下面几种类型：

system > const > eq\_ref > ref > range > index > ALL

在演示之前，先说明一下test2表中只有一条数据：![4c3438275ec54d2f82b6b2da615d369f.png][]

并且code字段上面建了一个普通索引：![f0e509ac059d48daa79dbcbbb12fe6e9.png][]

下面逐一看看常见的几个连接类型是怎么出现的：

1.  system
    
    这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
2.  const
    
    通过一次索引就能找到数据，一般用于主键或唯一索引作为条件的查询sql中，执行sql如下：
    
        explain select * from test2 where id=1;
    
    结果：![0bcab33dac0e4c20ad0d9c2d6f0f782e.png][]
3.  eq\_ref
    
    常用于主键或唯一索引扫描。执行sql如下：
    
        explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;
    
    结果：![b1b31d71a09940dc9d9c6c5bfe617777.png][]
    
    此时，有人可能感到不解，const和eq\_ref都是对主键或唯一索引的扫描，有什么区别？
    
    答：const只索引一次，而eq\_ref主键和主键匹配，由于表中有多条数据，一般情况下要索引多次，才能全部匹配上
4.  ref
    
    常用于非主键和唯一索引扫描。执行sql如下：
    
        explain select * from test2 where code = '001';
    
    结果：![52d91d358447429aa32572018d535586.png][]
5.  range
    
    常用于范围查询，比如：between ... and 或 In 等操作，执行sql如下：
    
        explain select * from test2 where id between 1 and 2;
    
    结果：![1d0a73e354ff403fb589c5d747d86225.png][]
6.  index
    
    全索引扫描。执行sql如下：
    
        explain select code from test2;
    
    结果：![a12567c79cd74af0801522162ef9d68a.png][]
7.  ALL
    
    全表扫描。执行sql如下：
    
        explain select *  from test2;
    
    结果：![e5466beb5bbb431eaee17de2365368d5.png][]

#### possible\_keys列 ####

该列表示可能的索引选择。

请注意，此列完全独立于表的顺序，这就意味着possible\_keys在实践中，某些键可能无法与生成的表顺序一起使用。![c3656afa7c004bbe941f727ad0848aaf.png][]

如果此列是NULL，则没有相关的索引。在这种情况下，您可以通过检查该WHERE 子句以检查它是否引用了某些适合索引的列，从而提高查询性能。

#### key列 ####

该列表示实际用到的索引。

可能会出现possible\_keys列为NULL，但是key不为NULL的情况。

演示之前，先看看test1表结构：![71496e9937684f6784f7a5fef4791447.png][]

test1表中数据：![bd33a0d72c7440908ef469628d5e967a.png][]

使用的索引：![a9777da654df4760a4fa9aee633bad49.png][]

code和name字段使用了联合索引。

执行sql如下：

explain select code  from test1;

结果：![d1da06e3c0e240e8a19bba393041cd43.png][]

这条sql预计没有使用索引，但是实际上使用了全索引扫描方式的索引。

#### key\_len列 ####

该列表示使用索引的长度。上面的key列可以看出有没有使用索引，key\_len列则可以更进一步看出索引使用是否充分。不出意外的话，它是最重要的列。![614c60cb4c16410d9d8f120d67f62659.png][]

**有个关键的问题浮出水面：key\_len是如何计算的？**

决定key\_len值的三个因素：

1.字符集

2.长度

3.是否为空

常用的字符编码占用字节数量如下：![1c742717f58b47eebe74b7b398df6547.png][]

目前我的数据库字符编码格式用的：UTF8占3个字节。

mysql常用字段占用字节数：

<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>&nbsp;n + 2</td> 
  </tr> 
  <tr> 
   <td>tinyint</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>date</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>datetime</td> 
   <td>8</td> 
  </tr> 
 </tbody> 
</table>

#### 此外，如果字段类型允许为空则加1个字节。 ####

#### 上图中的 184是怎么算的？ ####

#### 184 = 30 \* 3 + 2 + 30 \* 3 + 2 ####

再把test1表的code字段类型改成char，并且改成允许为空：![49b0d8f39bd441f7abf412099387f812.png][]

执行sql如下：

explain select code  from test1;

#### 结果：![01b767b499344b74ae51e192dbed7d72.png][] ####

#### 怎么算的？ ####

183 = 30 \* 3 + 1 + 30 \* 3 + 2

**还有一个问题：为什么这列表示索引使用是否充分呢，还有使用不充分的情况？**

执行sql如下：

explain select code  from test1 where code='001';

#### 结果：![9cdb7052dcf143cb950e0975531e074f.png][] ####

上图中使用了联合索引：idx\_code\_name，如果索引全匹配key\_len应该是183，但实际上却是92，这就说明没有使用所有的索引，索引使用不充分。

#### ref列 ####

该列表示索引命中的列或者常量。

执行sql如下：

explain select *  from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';

结果：![d34994dd32b24f9e945e82b202ef6955.png][]

我们看到表t1命中的索引是const(常量)，而t2命中的索引是列sue库的t1表的id字段。

#### rows列 ####

该列表示MySQL认为执行查询必须检查的行数。![44efde1900304a0ca8b5317668e071ab.png][]

对于InnoDB表，此数字是估计值，可能并不总是准确的。

#### filtered列 ####

该列表示按表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。![3fad2571ae4148e4afb50965f4ac5846.png][]

rows显示了检查的估计行数，rows× filtered显示了与下表连接的行数。例如，如果 rows为1000且 filtered为50.00（50％），则与下表连接的行数为1000×50％= 500。

#### Extra列 ####

该字段包含有关MySQL如何解析查询的其他信息，这列还是挺重要的，但是里面包含的值太多，就不一一介绍了，只列举几个常见的。

1.  **Impossible WHERE**
    
    表示WHERE后面的条件一直都是false，
    
    执行sql如下：
    
        explain select code  from test1 where 'a' = 'b';
    
    结果：![c1e01339cf6c43839678c2e258d3b4ee.png][]
2.  **Using filesort**
    
    表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。
    
    执行sql如下：
    
        explain select code  from test1 order by name desc;
    
    结果：![72d5894e76c94802972aec0bf8bd759e.png][]
    
    这里建立的是code和name的联合索引，顺序是code在前，name在后，这里直接按name降序，跟之前联合索引的顺序不一样。
3.  **Using index**
    
    表示是否用了覆盖索引，说白了它表示是否所有获取的列都走了索引。![7f12cca214a848b8afbd3af87dc046bd.png][]
    
    上面那个例子中其实就用到了：Using index，因为只返回一列code，它字段走了索引。
4.  **Using temporary**
    
    表示是否使用了临时表，一般多见于order by 和 group by语句。
    
    执行sql如下：
    
        explain select name  from test1 group by name;
    
    结果：![fb504b7340f048a682911cc91b7b1fa9.png][]
5.  **Using where**
    
    表示使用了where条件过滤。
6.  **Using join buffer**

表示是否使用连接缓冲。来自较早联接的表被部分读取到联接缓冲区中，然后从缓冲区中使用它们的行来与当前表执行联接。

### 索引优化的过程 ###

1.先用慢查询日志定位具体需要优化的sql

2.使用explain执行计划查看索引使用情况

3.重点关注：

key（查看有没有使用索引）

key\_len（查看索引使用是否充分）

type（查看索引类型）

Extra（查看附加信息：排序、临时表、where条件为false等）

一般情况下根据这4列就能找到索引问题。

4.根据上1步找出的索引问题优化sql

5.再回到第2步

### 最后说一句(求关注，别白嫖我) ###

[4575c4dff1aa4faba0bb2a38ec2f3727.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/9563ca44ebc048d9a257e88956a77516.png
[71a131553af24093887976b6c23e069e.png]: https://img-blog.csdnimg.cn/71a131553af24093887976b6c23e069e.png
[62acacb688c04c1480f721fcd51809c2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/92f937f6103747eeb2b4d6c8a289a30c.png
[82828ed86f394628bcd9cd16cbd93cc1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/72bfa5626cde4063b5b96a58497bc223.png
[179cae12028841ae87978c7925ba408a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/659738d7d8fb42eb8f57aef66ebefe12.png
[e9ac2b5b5de643f88f1cf54f2c0e5ba3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/14567acdc37c4981b9d5ad79aa1056c3.png
[8b5659a4513149359d279f6d990f9396.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d58a2f9580cb4028b5136a1181faaab0.png
[7e8e1ee84aa9474b9f9aa5bc5c4f31da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/9146cd13e0c7494093347cc9845d9754.png
[f87947de622041eb8729397a9304c502.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/43a2d0a0d00549009d125bebbecfa89d.png
[2087fa55590641a8b2a10501f69f6cab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/91be8acebab94a52afb76400f4a48829.png
[2ab2efa3be684c858cee1a23864f8029.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/cfb3bf112be04abbb4ff1de91c1c81dd.png
[0907bcb7a27a43ca932af08af08ff346.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d5489bfb4f8b4222bb072a4bb052ee10.png
[4c3438275ec54d2f82b6b2da615d369f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/e60fadafc6224103a2073b918e6fdf11.png
[f0e509ac059d48daa79dbcbbb12fe6e9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/f707d11857b84d24ae8f902d884bede5.png
[0bcab33dac0e4c20ad0d9c2d6f0f782e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/64262f10879c4b659460781b911a421c.png
[b1b31d71a09940dc9d9c6c5bfe617777.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/79c94d2ce8a74d13aa741596da51cd9d.png
[52d91d358447429aa32572018d535586.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/43192ee8956b4b869461bd2db4e3daa8.png
[1d0a73e354ff403fb589c5d747d86225.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/90f02672daa64bffbeab65587ed15bfe.png
[a12567c79cd74af0801522162ef9d68a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/38a2613f1d754a62b90071937ad8dfc0.png
[e5466beb5bbb431eaee17de2365368d5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/5735fe9139824f27b4e57f840f1ef55f.png
[c3656afa7c004bbe941f727ad0848aaf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/3bfebedb3e564b6c8b5c05bc3d85f862.png
[71496e9937684f6784f7a5fef4791447.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/4a5cf85a9fb847f780a32492076517b0.png
[bd33a0d72c7440908ef469628d5e967a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d2a4a5b278924189ac63d7f4d7c08222.png
[a9777da654df4760a4fa9aee633bad49.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/70fc24dbd73f456d9e6732fef57842bd.png
[d1da06e3c0e240e8a19bba393041cd43.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/b413fd9b38d94bdfa4ccb0a08b2dd504.png
[614c60cb4c16410d9d8f120d67f62659.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/253cee2d44e845cfa45ad6ee5e270c35.png
[1c742717f58b47eebe74b7b398df6547.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/1a58ef3fdf71484e82ae71ebef4b130a.png
[49b0d8f39bd441f7abf412099387f812.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/03b62bb545be4c14858a89f5dc05af31.png
[01b767b499344b74ae51e192dbed7d72.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d5d14539282b41b0ac0151e8c27c5e31.png
[9cdb7052dcf143cb950e0975531e074f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/3143eac056a54715923f38deff9f494f.png
[d34994dd32b24f9e945e82b202ef6955.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/482e6968fe70487d9183d7eb2b857fe5.png
[44efde1900304a0ca8b5317668e071ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/c4cf6c97728e4adbb3c665d4da844773.png
[3fad2571ae4148e4afb50965f4ac5846.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/9720d5d0f3ab43fb98a0ec0e000d0228.png
[c1e01339cf6c43839678c2e258d3b4ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/7fbacda7e37e47f196ae9aa09bdf44f2.png
[72d5894e76c94802972aec0bf8bd759e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/c9168215a5134082a662e266092185f4.png
[7f12cca214a848b8afbd3af87dc046bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/b272fb0bb8364f4c8caa5bff6023fd17.png
[fb504b7340f048a682911cc91b7b1fa9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/056e7105245e48a596534484230b8ba8.png