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)

標籤：其他

上一篇：oracle洗掉超過N天資料腳本

下一篇：如果鍵的一部分包含在另一個鍵中，則字典中的值總和