MYSQL中的type:index 和 Extra:Using index

原創水平有限，如有錯誤請指出


考慮下面執行計劃中的TYPE和Extra

+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | index | NULL          | id2  | 10      | NULL |    3 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+

type:index 不使用索引B+樹結構,只使用索引葉子結點連結串列結構進行掃描，我們知道在索引的葉子結點有一個葉子結點之間的雙向指標，
           並且葉子結點的資料是排序好的。他和ALL的方式類似，訪問效率並不高，其主要的應用場景為用於避免order by使用using filesort
           也就是避免排序。他是一種訪問資料的方式，和const、ref、eq_ref等一樣
Extra:Using index  當二級索引包含了所有的查詢需要的所有欄位的時候，select查詢只需要透過索引及可以
                   獲得全部的資料，那麼就不需要回表了。注意這裡全部資料是條件謂詞和查詢欄位的全部
                   總和比如
                   select id1 from test where id2=1；
                   這個索引必須包含id1和id2，這裡有種特殊的情況叫做Index Extensions在後面說明
                   它可以考慮B+樹結構如使用type:ref也可以不考慮使用type:index
                   一般來說索引的大小要遠遠小於表的大小，不管從回表還是讀取物理檔案的大小來說，使用
                   Using index 都可以提高查詢效能。也叫索引覆蓋掃描

這兩個地方是讓人經常容易混淆的，並且它們並不是總是一起出現(雖然可能性不小)，實際上他們沒有必然的聯絡
下面是我的測試表結構
mysql> show create table testud;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                        |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testud | CREATE TABLE `testud` (
  `id1` int(11) NOT NULL,
  `id2` int(11) DEFAULT NULL,
  `id3` int(11) DEFAULT NULL,
  `id4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`,`id3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

1、可以單獨的出現type:index
mysql> explain select * from testud force index(id2) order by id2;
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | testud | NULL       | index | NULL          | id2  | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

這裡只是代表type=index避免的排序，但是需要從頭到尾使用雙向連結串列來訪問整個葉子結點
2、可以單獨出現Extra:Using index
mysql> explain select id2 from testud where id2=1;
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | ref  | id2           | id2  | 5       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
這裡type為ref,代表透過一個非唯一的索引進行了單個值的掃描 id2=1,也就是這裡的(id2,id3)是非唯一索引,而1是單個值，他考慮了索引
的B+樹的結構也就是不僅僅考慮了葉子結點，需要從根結點到分支節點(如果有)，再到葉子結點來完成id2=1這種條件的過濾
而因為id2包含在索引(id2,id3)中當然也就使用Using index 就可以了。
從上面兩種情況來看type:index和Extra:Using index並沒有必然的聯絡。他們各自代表值的意思

3、共同出現這個就很簡單了。
mysql> explain select id2 from testud;
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | index | NULL          | id2  | 10      | NULL |    3 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


需要從頭到尾使用雙向連結串列來訪問整個葉子結點，而索引id2包含了全部的需要的資料。


這裡還需要提高Using index的一種特殊場景，也是很多人問過的。官方文件叫做
9.2.1.7 Use of Index Extensions
簡單來說比如上面的KEY `id2` (`id2`,`id3`)，我們知道葉子結點除了索引自己的資料實際上還有主鍵的資料在末尾，這個我在前面
已經做過驗證，參考：
http://blog.itpub.net/7728585/viewspace-2128817/
這個時候實際上索引id2 包含了 id2 id3 id1 這樣排列的資料如果id2相等按照id3排序如果id3相等按照id1排序的這樣一種結構，那麼
我們的using index就擴大了範圍比如下的語句：
mysql> explain select id1,id2,id3 from testud where id2=1;
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | ref  | id2           | id2  | 5       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


我們可以看到Using index是生效的。具體可以參考官方文件

最後我們來簡單說明一下ORACLE中的索引覆蓋掃描
ORACLE中分為2種
index fast full scan:主要按照磁碟物理順序進行掃描，我們知道連結串列之所以叫做連結串列是因為它有指向前或者後的指標比如C語言中經常用
                     *next *pr 來表示前後，既然是指向關係在物理上不一定是有序的。但是這種方式更快，可以使用物理上的多塊讀取
                     但是其返回資料並不有序，仔細考慮實際上MYSQL中沒有這種方式
index full scan：這種訪問返回就是有序的，他有點像MYSQL中的index+Using index 方式進行掃描，同樣他也是為了避免排序而大量使用
                 的。


作者微信：