MySQL EXPLAIN 系列（一）using index、using where 区别

引言

开发中，为了查看 SQL 的执行计划，通常都会 Explain 一下，对 SQL 进行分析，以查看索引的使用情况。Extra 内容比较多，本文着重介绍一下 Using index、Using where Using index、Using index condition、Using where 的几种情况。

准备工作

我们以一个简单的学生表为例进行说明（MySQL 的版本为 8.0.31）。先看一下表结构：

CREATE TABLE `student_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` int NOT NULL COMMENT '学生编号',
  `username` varchar(8) COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '姓名',
  `class_id` int NOT NULL COMMENT '班级编号',
  `age` int NOT NULL COMMENT '年龄',
  `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci

为了进行演示，我们创建一个辅助索引：

create index union_idx on student_info(user_id,username,class_id)

批量生成测试数据：

-- 开启允许创建函数
SET GLOBAL log_bin_trust_function_creators = 1;
DROP FUNCTION IF EXISTS rand_name;    
-- 创建随机生成姓名函数 rand_name
delimiter $$

CREATE FUNCTION rand_name(n int) RETURNS varchar(16)
BEGIN
    DECLARE family_str varchar (128) default '张王孙李周吴赵董郑尹刘孟';
    DECLARE name_str varchar (128) default '平书丽颜莹佳若山向秋凡白斌绮源鹏雯奇润语芯烟蕾天曼绮彤文宇紫星飞玉凡梅敏';
    DECLARE i int default 0;
    DECLARE full_name varchar(16) default '';
    DECLARE rand_num int DEFAULT 0;

    WHILE i < n DO
        SET full_name = IF(i > 0, concat(full_name, ','), full_name);
        SET full_name = concat(full_name, SUBSTR(family_str, floor(1+rand()*16), 1));
        SET full_name = concat(full_name, SUBSTR(name_str, floor(1+rand()*16), 1));
        SET rand_num = rand()*10;
        SET full_name = IF(rand_num > 5, concat(full_name, SUBSTR(name_str, floor(1+rand()*16), 1)), full_name);
        SET i = i + 1;
    END WHILE;

    RETURN full_name;

END$$ 
delimiter ; 

-- 创建存储过程
DROP PROCEDURE IF EXISTS insert_student;
delimiter $$
CREATE PROCEDURE insert_student(in num int)
BEGIN
    -- 记录循环次数
    DECLARE i int default 0;
    -- 关闭自动提交
    SET autocommit = 0;
    -- 循环 insert
    WHILE i < num do
        INSERT INTO student_info(username, class_id, user_id, age) VALUES 
        (rand_name(1), floor(15 + rand()*10), floor(rand()*10000 + rand()*1000), floor(10 + rand()*10));
        SET i = i + 1;
    END WHILE;
    COMMIT;

END$$
delimiter ;

-- 生成测试数据
CALL insert_student(1000000);

-- 对 user_id 去重
DELETE 
FROM
    student_info
WHERE
    id NOT IN ( SELECT tmp.minid FROM ( SELECT MIN( id ) AS minid FROM student_info GROUP BY user_id ) tmp )

Extra

我们以一个简单的 SQL 开始：

EXPLAIN SELECT * FROM student_info WHERE id = 1;

id 为表的自增主键，也是 Innodb 的聚簇索引，所以通过 id 进行查询，它的 Explain 如上图所示，其中我们需要关注以下几个字段：

• type
  type 表示了当前的扫描类型，按照执行效率从低到高按照如下顺序排列：
  • ALL（全表扫描）
  • index（全索引扫描）
  • range（索引范围扫描）
  • ref（非唯一索引扫描）
  • eq_ref（唯一索引扫描）
  • const（结果只有一条的主键或唯一索引扫描）
• possible_keys
  可能用到的索引 Key
• Key
  实际用到的索引 Key
• key_len
  实际用到的索引 Key 长度
• ref
  表示索引的哪一列被使用了，如果可能的话是一个常数
• rows
  表示要获取所需记录大概要读取的行数
• filtered
  表示返回结果的行占需要读到的行(rows 列的值)的百分比
• extra
  此时为 Null，虽然用到了索引（主键索引），但是要查询数据需要回表。此外，它还有以下几种状态：
  • using index：表示目前使用了覆盖索引查询
  • using where：表示使用了where子句查询，通常表示没使用索引
  • using index condition：表示查询条件使用到了联合索引的前面几个字段
  • using temporary：表示使用了临时表处理查询结果
  • using filesort：表示以索引字段之外的方式进行排序，效率较低
  • select tables optimized away：表示在索引字段上使用了聚合函数

查询列未被索引覆盖

为了便于分析，我们把查询的列根据是否被索引覆盖分开。首先分析查询得列未被覆盖的场景。

文章开始时，我们为表 studen_info 中的字段 user_id、username、class_id 创建了一个联合索引，那么对应的相当于产生了以下三个索引：（user_id）、（user_id，username）、（user_id，username，class_id），也就是前导列。

匹配前导列（精确查询）

EXPLAIN SELECT * FROM student_info WHERE user_id = '5630';

由于使用 user_id 前导列进行查询，得到的 type 为 ref，也就是采用了非唯一索引扫描，key_len 为 4 字节（因为 user_id 采用 int 类型），Extra 为 NULL。此时 MySQL 会回表查询额外的字段。

---

EXPLAIN SELECT * FROM student_info WHERE user_id = 1277 AND username = '山' 

---

EXPLAIN SELECT * FROM student_info WHERE user_id = 1277 AND username = '山' AND class_id = 121

💡 总结

通过以上看出，当索引是前导列时，它们的检索 type 是 ref，filtered 为 100 说明能根据索引命中数据。唯一有变化的就是 key_len，当前导列为三个索引时，ken_len 为 42。int 类型的 user_id、class_id 各占 4B，username 类型为 varchar(8) ，而字符集是 utf8mb4，所以它占用 8 * 4 + 2（变长类型需要额外的 2B 表示长度）。

匹配前导列（范围/模糊）

EXPLAIN SELECT * FROM student_info WHERE user_id > 1277

如果我们对 user_id 采用范围查询，发现 MySQL 进行全表扫描，即 type 为 ALL，而 Extra 为 Using where 说明应用层做了条件过滤。

---

EXPLAIN SELECT * FROM student_info WHERE user_id = 1277 AND username like '山' 

如果 user_id 做精确匹配，然后 username 做模糊查询，得到的 type 为 range，说明 MySQL 进行了 索引范围扫描。Extra 为 Using index condition 说明查询条件中出现了索引列，但是有部分条件无法使用索引，MySQL 会根据能用的索引条件先搜索一遍数据再匹配无法使用索引的条件。该过程是在引擎层完成的，所以属于新版本的优化。

前导列不匹配

如果前导列不匹配那么会发生什么情况呢？从前边例子中我们发现，如果 user_id 为范围查找时，就会出现前导列不匹配的一种情况，MySQL 会进行全表扫描，然后使用服务层的条件筛选，即 Using where。那么其他的前导列不匹配的场景是不是也一样呢？

精确查询

EXPLAIN SELECT * FROM student_info WHERE username = '山'

从图中我们看到，MySQL 进行了全表扫描，即 type 为 ALL
。然后采用服务层过滤，即 Extra 为 Using where。

模糊查询

EXPLAIN SELECT * FROM student_info where username like 'test' AND class_id > 100

与精确查询一样，也进行了全表扫描，同时服务层做筛选。

非索引筛选

如果我们使用非索引进行筛选过滤又会发生什么呢？

EXPLAIN SELECT * FROM student_info where age > 10

MySQL 仍然进行了全表扫描。

💡 总结

通过以上的例子，我们可以看出，如果匹配了前导列，那么 MySQL 会进行非唯一索引扫描定位到具体的数据，然后回表查询非索引的字段。如果匹配前导列，但是前导列有部分索引未能生效（如模糊查询），那么会进行全索引扫描，然后利用引擎过滤出未能匹配的数据。如果前导列不匹配或者非索引列筛选，那么行为是一致的，都会进行全表扫描，然后利用服务层过滤数据。总之，只要查询列不命中索引，势必会进行回表查询的。

查询列被索引覆盖

我们分析了查询列未命中索引的场景，接下来我们看看如果命中索引时，会出现什么情况。

前导列命中

EXPLAIN SELECT username, class_id, user_id FROM student_info WHERE user_id = 100

username、class_id、user_id 都是联合索引里的成员，而筛选条件也是匹配前导列，所以我们看到 Extra 为 Using index，rows 为 1，ref 为 const 说明 MySQL 直接使用二级索引就完成数据的查询与过滤，并不需要回表。

前导列范围查询

EXPLAIN SELECT username, class_id, user_id FROM student_info WHERE user_id > 100

我们发现 Extra 为 Using where; Using index，type 为 rang 说明进行了索引范围的扫描，意味着无法直接通过索引查找来查询到符合条件的数据。

非前导列筛选

EXPLAIN SELECT username, class_id, user_id FROM student_info WHERE create_date > '2021-12-11 10:22:11'

毫无疑问，肯定是需要回表的。

💡 总结

通过以上几个例子，我们可以得出以下结论，如果查询字段被索引命中，如果是前导列匹配，则直接通过索引就可以获取结果，不需要回表。如果前导列不匹配，依然不用回表，只不过会进行全索引扫描筛选。如果筛选条件是非索引字段，那么需要回表进行查询。

索引组合查询

如果把两个不同的索引组合一下查询会发生什么呢？

CREATE INDEX age ON student_info (age)

我们为 age 单独创建一个索引，然后查询一下看看：

EXPLAIN SELECT username, class_id, user_id, age FROM student_info WHERE user_id = 100

我们发现 MySQL 回表查询了。

EXPLAIN SELECT username, class_id, user_id, age FROM student_info WHERE age = 12

💡 总结

如果组合两个不同的索引进行过滤或者查询，会发现 MySQL 虽然不会进行全表扫描，但是仍然会回表查询数据。也就是说多个单列索引不如一个多列索引。

索引下推

什么是索引下推呢？还记得 Extra 为 Using index condition 这种情况吗？就是当查询字段未覆盖索引，筛选条件中的前导列部分索引不匹配，MySQL 会在索引扫描部分数据之后，让引擎再根据未匹配的索引过滤。将过滤好的数据交给服务层，依次来减少 IO 操作。如以下例子：

EXPLAIN SELECT age FROM student_info WHERE user_id = 100 AND class_id > 11

前提是前导列中有索引可以匹配上，而部分索引不匹配（由范围、模糊查询、顺序等原因造成的）。如果仅有的索引列不匹配，那么将进行全表扫描，如图：

索引下推是 Mysql 5.6 推出的功能，我们看下，如果我们关闭这个功能，MySQL 的行为是怎样的：

SET optimizer_switch="index_condition_pushdown=off";

type 为 ref 说明进行了非唯一索引扫描，Extra 为 Using where 说明进行了服务层的过滤。这是符合预期的，因为索引下推就是利用存储引擎将我们想要的数据进行过滤，减少与服务层的 IO 交互次数。

---

尾声

本文通过多个简单的例子介绍了索引在不同场景下表现出来的行为，当然实际的情况要远比这个复杂，但是只要我们能够理解它，举一发三，所有问题我们都能迎刃而解。