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)

Tags:

VirMach 便宜 VPS

QNews

QNews