設計MySQL的大叔為何偏愛ref？
回憶一下查詢成本
對于一個查詢來說，有時候可以通過不同的索引或者全表掃描來執(zhí)行它，MySQL優(yōu)化器會通過事先生成的統(tǒng)計數據，或者少量訪問B+樹索引的方式來分析使用各個索引時都需要掃描多少條記錄，然后計算使用不同索引的查詢成本，最后選擇成本最低的那個來執(zhí)行查詢。
小貼士：我們之前稱那種通過少量訪問B+樹索引來分析需要掃描的記錄數量的方式稱為index dive，不知道大家還有沒有印象。
一個很簡單的思想就是：使用某個索引執(zhí)行查詢時，需要掃描的記錄越少，就越可能使用這個索引來執(zhí)行查詢。
創(chuàng)建場景
假如我們現(xiàn)在有一個表t，它的表結構如下所示：
- CREATE TABLE t (
- id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- key1 VARCHAR(100),
- common_field VARCHAR(100),
- INDEX idx_key1 (key1)
- ) ENGINE=InnoDB CHARSET=utf8;
這個表包含3個列：
- id列是自增主鍵
- key1列用于存儲字符串，我們?yōu)閗ey1列建立了一個普通的二級索引
- common_field列用于存儲字符串
現(xiàn)在該表中共有10000條記錄：
- mysql> SELECT COUNT(*) FROM t;
- +----------+
- | COUNT(*) |
- +----------+
- | 10000 |
- +----------+
- 1 row in set (2.65 sec)
其中key1列為'a'的記錄有2310條：
- mysql> SELECT COUNT(*) FROM t WHERE key1 = 'a';
- +----------+
- | COUNT(*) |
- +----------+
- | 2310 |
- +----------+
- 1 row in set (0.83 sec)
key1列在'a'到'i'之間的記錄也有2310條：
- mysql> SELECT COUNT(*) FROM t WHERE key1 > 'a' AND key1 < 'i';
- +----------+
- | COUNT(*) |
- +----------+
- | 2310 |
- +----------+
- 1 row in set (1.31 sec)
現(xiàn)在我們有如下兩個查詢：
- 查詢1：SELECT * FROM t WHERE key1 = 'a';
- 查詢2：SELECT * FROM t WHERE key1 > 'a' AND key1 < 'i';
按理說上邊兩個查詢需要掃描的記錄數量是一樣的，MySQL查詢優(yōu)化器對待它們的態(tài)度也應該是一樣的，也就是要么都使用二級索引idx_key1執(zhí)行它們，要么都使用全表掃描的方式來執(zhí)行它們。不過現(xiàn)實是貌似查詢優(yōu)化器更喜歡查詢1，而比較討厭查詢2。查詢1的執(zhí)行計劃如下所示：
- # 查詢1的執(zhí)行計劃
- mysql> EXPLAIN SELECT * FROM t WHERE key1 = 'a'\G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: t
- partitions: NULL
- type: ref
- possible_keys: idx_key1
- key: idx_key1
- key_len: 303
- ref: const
- rows: 2310
- filtered: 100.00
- Extra: NULL
- 1 row in set, 1 warning (0.04 sec)
查詢2的執(zhí)行計劃如下所示：
- # 查詢2的執(zhí)行計劃
- mysql> EXPLAIN SELECT * FROM t WHERE key1 > 'a' AND key1 < 'i'\G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: t
- partitions: NULL
- type: ALL
- possible_keys: idx_key1
- key: NULL
- key_len: NULL
- ref: NULL
- rows: 9912
- filtered: 23.31
- Extra: Using where
- 1 row in set, 1 warning (0.03 sec)
很顯然，查詢優(yōu)化器決定使用idx_key1二級索引執(zhí)行查詢1，而使用全表掃描來執(zhí)行查詢2。
為什么?憑什么?同樣是掃描相同數量的記錄，憑什么我range訪問方法就要比你ref低一頭?設計MySQL的大叔，你為何這么偏心...
解密偏心原因
世界上沒有無緣無故的愛，也沒有無緣無故的恨。這事兒還得從索引結構說起。比方說idx_key1二級索引結構長這樣：
原諒我們把索引對應的B+樹結構弄了一個極度精簡版，我們忽略掉了頁的結構，只保留了葉子節(jié)點的記錄。雖然極度精簡，但是我們還是保留了一個極其重要的特性：B+樹葉子節(jié)點中的記錄是按照索引列的值從小到大排序的。對于二級索引idx_key1來說：
- 二級索引葉子節(jié)點的記錄只保留key1列和id列
- 二級索引記錄是先按照key1列的值從小到大的順序進行排序的。
- 如果key1列的值相同，則按照主鍵值，也就是id列的值從小到大的順序進行排序。
也就是說，對于所有key1值為'a'的二級索引記錄來說，它們都是按照id列的值進行排序的。對于查詢1：
- 查詢1: SELECT * FROM t WHERE key1 = 'a';
由于查詢列表是* ，也就是說我們需要通過讀取到的二級索引記錄的id值執(zhí)行回表操作，到聚簇索引中找到完整的用戶記錄(為了去獲取common_field列的值)后才可以將記錄發(fā)送到客戶端。對于所有key1列值等于'a'的二級索引記錄，由于它們是按照id列的值排序的，所以：
- 前一次回表的id值所屬的聚簇索引記錄和下一次回表的id值所屬的聚簇索引記錄很大可能在同一個數據頁中
- 即使前一次回表的id值所屬的聚簇索引記錄和下一次回表的id值所屬的聚簇索引記錄不在同一個數據頁中，由于回表的id值是遞增的，所以我們很大可能通過順序I/O的方式找到下一個數據頁，也就是說這個過程中很大可能不需要很大幅度的移動磁頭就可以找到下一個數據頁。這可以減少很多隨機I/O帶來的性能開銷。
綜上所述，執(zhí)行語句1時，回表操作帶來的性能開銷較小。
而對于查詢2來說：
- 查詢2: SELECT * FROM t WHERE key1 > 'a' AND key1 < 'i';
由于需要掃描的二級索引記錄對應的id值是無序的，所以執(zhí)行回表操作時，需要訪問的聚簇索引記錄所在的數據頁很大可能就是無序的，這樣會造成很多隨機I/O。所以如果使用idx_key1來執(zhí)行查詢1和查詢2，執(zhí)行查詢1的成本很顯然會比查詢2低，這也是設計MySQL的大叔更鐘情于ref而不是range的原因。
MySQL的內部實現(xiàn)
MySQL優(yōu)化器在計算回表的成本時，在使用二級索引執(zhí)行查詢并且需要回表的情境下，對于ref和range是很明顯的區(qū)別對待的：
- 對于range來說，需要掃描多少條二級索引記錄，就相當于需要訪問多少個頁面。每訪問一個頁面，回表的I/O成本就加1。
比方對于查詢2來說，需要回表的記錄數是2310，因為回表操作而計算的I/O成本就是2310。
- 對于ref來說，回表開銷帶來的I/O成本存在天花板，也就是定義了一個上限值：
- double worst_seeks;
這個上限值的取值是從下邊兩個值中取較小的那個：
比方對于查詢1來說，回表的記錄數是2310，按理說計算因回表操作帶來的I/O成本也應該是2310。但是由于對于ref訪問方法，計算回表操作時帶來的I/O成本時存在天花板，會從全表記錄的十分之一(也就是9912/10=991，9912為估計值)以及聚簇索引所占頁面的3倍(本例中聚簇索引占用的頁面數就是97，乘以3就是291)選擇更小的那個，本例中也就是291。
- 全表記錄數的十分之一(此處的全表記錄數屬于統(tǒng)計數據，是一個估計值)
- 聚簇索引所占頁面的3倍
小貼士：在成本分析的代碼中，range和index、all是被分到一類里的，ref是親兒子，單獨分析了一波。不過我們也可以看到，設計MySQL的大叔在計算range訪問方法的代價時，直接認為每次回表都需要進行一次頁面I/O，這是十分粗暴的，何況我們的實際聚簇索引總共才97個頁面，它卻將回表成本計算為2310，這也是很不精確的。當然，由于目前的算法無法預測哪些頁面在內存中，哪些不在，所以也就將就將就用吧～
