MySQL explain结果Extra中"Using Index"与"Using where; Using index"区别探究

问题背景

最近用explain命令分析查询sql执行计划，时而能看到Extra中显示为"Using index"或者"Using where; Using Index"，对这两者之间的明确区别产生了一些疑惑，于是通过网上搜索、自行实验探究了一番其具体区别。

测试数据准备

以下表作为测试表进行sql分析。

CREATE TABLE `test_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `f0` int(11) NOT NULL,
  `f1` varchar(50) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_f0_f1` (`f0`,`f1`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4

包含以下数据:

INSERT INTO test_table (f0, f1, f2) VALUES (1, "111", 1), (2, "222", 2), (3, "333", 3), (4, "444", 4), (5, "555", 5), (6, "666", 6);

仅有Using where含义

对于仅有Using where的情况，文档中写到：

A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

第一句话的大意是有一个where子句用于限制返回哪些匹配行到客户端或者下一个表--简单说就是有使用where条件限制要返回的select结果，从这里并没有提出Using where与是否需要回表读完整行数据有任何联系。
如下语句无where条件所以无Using where:

 EXPLAIN SELECT * FROM test_table;
 +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+

如下语句使用where子句添加限制，其Extra中有Using where结果:

EXPLAIN SELECT * FROM test_table WHERE f2=3;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

仅有Using index含义

仅有Using index的情况，文档中写道：

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

即表示where 和select中需要的字段都能够直接通过一个索引字段获取，无需再实际回表查询，当查询涉及的列都是某一单独索引的组成部分时即为此种情况，这实际上就是索引类型中覆盖索引。
如下语句所有查询列均包含在索引中，所以有Using index:

EXPLAIN SELECT f0, f1 FROM test_table WHERE f0=3;
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_table | NULL       | ref  | idx_f0_f1     | idx_f0_f1 | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

如下语句虽然where子句涉及列均包含在索引中，但是select中包含额外列，所以无Using index:

EXPLAIN SELECT f0, f1, f2 FROM test_table WHERE f0=3;
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_table | NULL       | ref  | idx_f0_f1     | idx_f0_f1 | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

同时包含两者：Using where;Using Index的情况

遍寻网上相关资料，总结下来主要有两种看法：

1. Using index表示使用索引查询初步结果集，如果+Using where表示获取初步结果集后还需进一步根据where子句条件筛选索引条件后返回最终结果集。
2. Using index表示使用索引查询初步结果集，如果+Using where表示获取初步结果集后还需要回表进一步查询数据再根据where子句条件筛选出最终结果集。

两种看法的分歧点就在于Using where是否表示需要回表查询数据，认为需要回表查数据的文档依据主要基于文档中的下述说明：

Using index
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.

其关键在于第二段中：Without Using where，优化器将可以通过只读索引而避免进一步回表读取完整行数，所以我们正常理解Using where自然就意味着需要去回表读取行数据了。
然而实际上该文档说明来源于dev版MySQL5.1的文档，在5.6及以上版本的文档中已经没有该内容，而我在存档的Mysql5.1 官方文档MySQL 5.1 Reference Manual中也没有找到对应内容，其实际来源暂不可考，这里把讨论范围限于5.6及以上版本。
于是通过上面的Using where与Using Index的官方文档说明可以得出以下三点：

1. Using where仅表示是否存在where子句限制对结果集进行筛选后返回
2. Using Index仅表示是否使用了覆盖索引，即查询涉及字段均可以从一个索引中获得，单独的Using Index表示从索引中获取返回结果集即可直接作为最终结果返回。
3. 出现Using where; Using index 表示sql使用了覆盖索引--所有字段均从一个索引中获取，同时在从索引中查询出初步结果后，还需要使用组成索引的部分字段进一步进行条件筛选，而不是说需要回表获取完整行数据--其实直觉上这种理解也更合理，因为Using index已经表示所有查询涉及字段都在索引里面包含了，压根没有什么额外字段需要再回表才能获取，那回表又有什么意义呢？
   另外，通过一番简单的查找MySQL(Mariadb 10.3)源码，发现以下代码，由于未深入探究源码流程，这里简单猜测其正是用于判定是否添加 Using where的判断代码:

explain->using_where= MY_TEST(select && select->cond);

进一步佐证Using where即表示select包含条件子句。
sample:
如下sql仅涉及索引字段，但是要在where子句中对索引字段f0进行取余计算后才能比较条件，此种情况下无法直接在第一步查找索引时即进行条件判断，只能先把索引全部取出作为初步结果集，而后再进行where子句筛选:

EXPLAIN SELECT f0, f1 FROM test_table WHERE f0%2=0;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_table | NULL       | index | NULL          | idx_f0_f1 | 8       | NULL |    6 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

如下sql则需要对索引字段f1通过LEFT函数取前缀后进行比较，同样只能先把索引全部取出作为初步结果集，而后再进行where子句筛选

EXPLAIN SELECT f0, f1 FROM test_table WHERE f0=3 AND LEFT(f1, 2)="33";
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
| id | select_type | table      | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | test_table | NULL       | ref  | idx_f0_f1     | idx_f0_f1 | 4       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+

转载请注明出处，原文地址: MySQL explain结果Extra中"Using Index"与"Using where; Using index"区别探究

参考

MySQL - "Using index condition" vs "Using where; Using index"
Whats the difference between "Using index" and "Using where; Using index" in the EXPLAIN
「Using where; Using index」和「Using index」 区别是什么
MySQL execution plan (Use where, Use index and Use index condition)

签名：拥抱开源，拥抱自由

原文地址：https://www.cnblogs.com/AcAc-t/archive/2022/02/28/mysql_explain_difference_between_using_index_and_using_where.html