如何理解MYSQL中的type:index 和 Extra:Using

今天就跟大家聊聊有關如何理解MYSQL中的type:index 和 Extra:Using，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

考慮下面執行計劃中的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 方式進行掃描，同樣他也是為了避免排序而大量使用的。

看完上述內容，你們對如何理解MYSQL中的type:index 和 Extra:Using有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。