MySQL explain常见场景分析

最新推荐文章于 2025-02-06 16:31:01 发布

蜡笔没了小新git

最新推荐文章于 2025-02-06 16:31:01 发布

阅读量452

点赞数

CC 4.0 BY-SA版权

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/ljcgit/article/details/118600853

本文通过对MySQL中Explain输出的不同场景分析，探讨了回表现象、覆盖索引的优势以及Using index condition和Using where; Using index的区别。通过实例解析了查询过程中索引的使用策略，帮助理解如何优化SQL查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实际场景分析：

建表语句：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uindex` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

插入数据：

insert into t(a,b,c,d) values(1,12,100,"a");

insert into t(a,b,c,d) values(2,13,100,"b");

insert into t(a,b,c,d) values(3,12,100,"c");

insert into t(a,b,c,d) values(4,11,100,"d");

insert into t(a,b,c,d) values(5,14,100,"a");

insert into t(a,b,c,d) values(6,13,100,"b");

insert into t(a,b,c,d) values(2,12,100,"c");

insert into t(a,b,c,d) values(1,11,100,"d");

insert into t(a,b,c,d) values(3,11,100,"a");

insert into t(a,b,c,d) values(1,10,100,"b");

insert into t(a,b,c,d) values(1,10,100,"c");

表中存在一个主键id，以及一个联合索引**（a,b,c)**

这里创建的表结构和数据量可能不是很好，模拟的出来的效果可能会不一致。

1. 场景1

explain select * from t where a = 1 and b = 10 and c = 100;

在这里插入图片描述

这里extra字段值为空（或为null），代表发生了回表。

回表：InnoDB引擎中索引树大致可以分为两种类型——聚集索引和非聚集索引，两种结构遍历过程和存储结构都是相同的，主要就是叶子节点存放的数据以及各节点关键字的类型不同。聚集索引叶子节点存放的是具体的数据（所有行数据），而非聚集索引存放的是主键的值）。当通过主键ID进行搜索时只需要遍历聚集索引B+树，拿到叶子节点中的数据即可，而通过普通索引进行查询，需要遍历非聚集索引B+树拿到叶子节点中主键的值（不是地址），再去遍历聚集索引B+树拿需要的数据。这个通过先拿到主键的值，在通过主键进行查询的过程就是回表。

2. 场景2

将语句1的返回字段改为只返回a,b,c字段（也可以加上id）

explain select a,b,c from t where a = 1 and b = 10 and c = 100;

在这里插入图片描述

Using Index：覆盖索引，所需要的列数据在索引树中都已存在，直接返回索引树中的数据即可。由于无需进行回表，执行时间相对语句1要来的短。

3.场景3

将语句1的字段c的比较改为!=

EXPLAIN select * from t where a = 1 and b = 10 and c != 100;

返回：
在这里插入图片描述

Using index condition ：

Using index condition从字面上看就是使用索引作为条件，个人认为就是充分利用索引来提高效率。按照联合索引的最左前缀匹配规则，c是无法使用索引的，但是MySQL认为可以直接在索引树中按照c的条件直接筛选出所需要的主键信息，这时再利用所有匹配的主键去聚集索引中查询。

4. 场景4

将语句2中的字段c的比较改为!=

EXPLAIN select  a,b,c from t where a = 1 and b = 10 and c != 100;

在这里插入图片描述

Using where; Using index：

可以认为先通过字段a和b在索引树中找到了对应的所有行，因为这里只需要索引树包含的信息，所以直接返回该树中的数据，然后Sql 的Server层再进行c字段的过滤。也可以认为是using index和using where的结合体，在using index覆盖索引的结果上进行了条件判断过滤。

5.场景5

explain select a,b,c from t where b > 10;

在这里插入图片描述

可以看到条件中只有一个b字段，肯定是不满足最左前缀匹配的规则，但是在key字段中还是显示了使用了uindex联合索引，并且extra中的值和上一条语句一样。

其实执行逻辑上也和语句4基本一样。当前语句中需要返回的字段刚好在联合索引树中都有，所有直接扫描索引树中的所有数据（语句4通过联合索引进行了筛选），然后进行条件判断（type为index代表扫描索引树中的所有数据，效果会略好于全表扫描，key_len为15）。

Using index condition 和 Using where; Using index的区别？

这两个值经常容易混淆。个人是这样理解的：两者主要就是处理无法正常走索引树遍历流程的字段的时间不同，Using index condition可以认为是在索引树遍历返回数据前，Using where; Using index是在索引树返回数据后。还有Using index condition一般都需要回表操作，Using where; Using index能直接从普通索引中获取所有需要的数据。

type为range和ref有何不同？

range可以认为是查询的某个范围，而ref是具体匹配到的多个值。