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的情况

在互联网上搜索相关信息，主要有两种观点：

两种看法的分歧点就在于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的官方文档说明可以得出以下三点：

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 |
+----+-------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+

Original: https://www.cnblogs.com/AcAc-t/p/mysql_explain_difference_between_using_index_and_using_where.html
Author: 及时
Title: MySQL explain结果Extra中”Using Index”与”Using where; Using index”区别探究