MySQL5.6之use_index_extensions優化

InnoDB的二級索引（Secondary Index）除了儲存索引列key值，還儲存著主鍵值(而不是指向主鍵的指標)。為什麼這樣做？因為InnoDB是以聚集索引方式組織資料的儲存，即主鍵值相鄰的資料行緊湊的儲存在一起（索引組織表）。當資料行移動或者發生頁分裂的時候，可以減少大量的二級索引維護工作。InnoDB移動行時，無需更新二級索引。

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

資料表t1的schema設計如上面所示。二級索引k_d（d）的元組在InnoDB內部實際被擴充套件成（d,i1,i2），即包含主鍵值。因此在設計主鍵的時候，常見的一條設計原則是要求主鍵欄位儘量簡單（整型數值，自增），以避免二級索引過大。在MySQL5.6.9之前，優化器在決定是否使用一個索引或者怎樣使用一個索引的時候，並不考慮索引中擴充套件的主鍵列這一部分。而從MySQL5.6.9開始，優化器開始考慮使用擴充套件的主鍵列，這樣可以產生更高效的執行計劃和更好的效能。
優化器可以把擴充套件的二級索引用於ref，range，index_merge索引訪問、鬆散索引掃描、連線和排序優化、min()和max()優化。
在5.6中，可以通過優化器開關optimizer_switch(Golbal,Session:Dynamic)來開啟或者關閉use_index_extensions優化。
set[global|session] optimizer_switch="use_index_extensions=off|on";

下面通過Handler_read_key狀態資訊和explain執行計劃兩方面的資訊來觀察use_index_extensions對mysql執行效率和效能的影響。先往t1中插入一些資料，方便觀察status和explain輸出的資訊。
INSERT INTO t1 VALUES 

(1, 1, '1998-01-01'), (1, 2, '1999-01-01'), 
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'), 
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'), 
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'), 
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'), 
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'), 
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'), 
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), 
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'), 
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'), 
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'), 
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'), 
(5, 5, '2002-01-01'); 

關閉use_index_extensions,觀察explain輸出的資訊。

開啟use_index_extensions,再觀察explain輸出結果。

當use_index_extensions=off的時候，僅使用索引k_d中d列的資料，忽略了擴充套件的主鍵列的資料。而use_index_extensions=on時，使用了k_d索引中（i1,i2,d）三列的資料。可以從上面兩種情況下的explain輸出結果中資訊得以驗證。
key_len：由4變到8，說明不僅僅使用了d列上的索引，而且使用了擴充套件的主鍵i1列的資料。
ref：有const變為”const,const”, 使用了索引的兩部分。
rows:從5變為1，只需要檢查更少的資料行就可以產生結果集。
Extra：”using index,Using where” 變為”Using index”，通過索引覆蓋就完成資料查詢，而不需要讀取任何的資料行。

另外，從status資訊中“Handler_read_%”相關狀態值可以觀察實際執行過程中索引和資料行的訪問統計。flush table關閉已開啟的資料表，並清除快取（表快取和查詢快取）。flush status把status計數器清零。
關閉use_index_extensions情況下，status的統計資訊。

開啟use_index_extensions情況下，status的統計資訊。

Handler_read_next的值從5變為1，索引的訪問效率更高了，減少了資料行的讀取次數。

Handler_read_first 代表讀取索引頭的次數，如果這個值很高，說明全索引掃描很多。
Handler_read_key代表一個索引被使用的次數，如果我們新增加一個索引，可以檢視Handler_read_key是否有增加，如果有增加，說明sql用到索引。
Handler_read_next 代表讀取索引的下列，一般發生range scan。
Handler_read_prev 代表讀取索引的上列，一般發生在ORDER BY … DESC。
Handler_read_rnd 代表在固定位置讀取行，如果這個值很高，說明對大量結果集進行了排序、進行了全表掃描、關聯查詢沒有用到合適的KEY。
Handler_read_rnd_next 代表進行了很多表掃描，查詢效能低下。

參考資料：

Use of Index Extensions

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN