共計 3628 個字符，預計需要花費 10 分鐘才能閱讀完成。
這篇文章主要介紹“MySQL 的 ref 有什么用”，在日常操作中，相信很多人在 MySQL 的 ref 有什么用問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL 的 ref 有什么用”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！
回憶一下查詢成本
對于一個查詢來說，有時候可以通過不同的索引或者全表掃描來執行它，MySQL 優化器會通過事先生成的統計數據，或者少量訪問 B + 樹索引的方式來分析使用各個索引時都需要掃描多少條記錄，然后計算使用不同索引的查詢成本，最后選擇成本最低的那個來執行查詢。
創建場景
假如我們現在有一個表 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 列用于存儲字符串，我們為 key1 列建立了一個普通的二級索引
common_field 列用于存儲字符串
現在該表中共有 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)現在我們有如下兩個查詢：
查詢 1：SELECT * FROM t WHERE key1 = a 查詢 2：SELECT * FROM t WHERE key1 a AND key1 i按理說上邊兩個查詢需要掃描的記錄數量是一樣的，MySQL 查詢優化器對待它們的態度也應該是一樣的，也就是要么都使用二級索引 idx_key1 執行它們，要么都使用全表掃描的方式來執行它們。不過現實是貌似查詢優化器更喜歡查詢 1，而比較討厭查詢 2。查詢 1 的執行計劃如下所示：
# 查詢 1 的執行計劃 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 的執行計劃如下所示：
# 查詢 2 的執行計劃 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)很顯然，查詢優化器決定使用 idx_key1 二級索引執行查詢 1，而使用全表掃描來執行查詢 2。
為什么? 憑什么? 同樣是掃描相同數量的記錄，憑什么我 range 訪問方法就要比你 ref 低一頭? 設計 MySQL 的大叔，你為何這么偏心 …
解密偏心原因
世界上沒有無緣無故的愛，也沒有無緣無故的恨。這事兒還得從索引結構說起。比方說 idx_key1 二級索引結構長這樣：
原諒我們把索引對應的 B + 樹結構弄了一個極度精簡版，我們忽略掉了頁的結構，只保留了葉子節點的記錄。雖然極度精簡，但是我們還是保留了一個極其重要的特性：B+ 樹葉子節點中的記錄是按照索引列的值從小到大排序的。對于二級索引 idx_key1 來說：
二級索引葉子節點的記錄只保留 key1 列和 id 列
二級索引記錄是先按照 key1 列的值從小到大的順序進行排序的。
如果 key1 列的值相同，則按照主鍵值，也就是 id 列的值從小到大的順序進行排序。
也就是說，對于所有 key1 值為 a 的二級索引記錄來說，它們都是按照 id 列的值進行排序的。對于查詢 1：
查詢 1: SELECT * FROM t WHERE key1 = a由于查詢列表是 * ，也就是說我們需要通過讀取到的二級索引記錄的 id 值執行回表操作，到聚簇索引中找到完整的用戶記錄 (為了去獲取 common_field 列的值) 后才可以將記錄發送到客戶端。對于所有 key1 列值等于 a 的二級索引記錄，由于它們是按照 id 列的值排序的，所以：
前一次回表的 id 值所屬的聚簇索引記錄和下一次回表的 id 值所屬的聚簇索引記錄很大可能在同一個數據頁中
即使前一次回表的 id 值所屬的聚簇索引記錄和下一次回表的 id 值所屬的聚簇索引記錄不在同一個數據頁中，由于回表的 id 值是遞增的，所以我們很大可能通過順序 I / O 的方式找到下一個數據頁，也就是說這個過程中很大可能不需要很大幅度的移動磁頭就可以找到下一個數據頁。這可以減少很多隨機 I / O 帶來的性能開銷。
綜上所述，執行語句 1 時，回表操作帶來的性能開銷較小。
而對于查詢 2 來說：
查詢 2: SELECT * FROM t WHERE key1 a AND key1 i由于需要掃描的二級索引記錄對應的 id 值是無序的，所以執行回表操作時，需要訪問的聚簇索引記錄所在的數據頁很大可能就是無序的，這樣會造成很多隨機 I /O。所以如果使用 idx_key1 來執行查詢 1 和查詢 2，執行查詢 1 的成本很顯然會比查詢 2 低，這也是設計 MySQL 的大叔更鐘情于 ref 而不是 range 的原因。
MySQL 的內部實現
MySQL 優化器在計算回表的成本時，在使用二級索引執行查詢并且需要回表的情境下，對于 ref 和 range 是很明顯的區別對待的：
對于 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。
全表記錄數的十分之一(此處的全表記錄數屬于統計數據，是一個估計值)
聚簇索引所占頁面的 3 倍
小貼士：在成本分析的代碼中，range 和 index、all 是被分到一類里的，ref 是親兒子，單獨分析了一波。不過我們也可以看到，設計 MySQL 的大叔在計算 range 訪問方法的代價時，直接認為每次回表都需要進行一次頁面 I /O，這是十分粗暴的，何況我們的實際聚簇索引總共才 97 個頁面，它卻將回表成本計算為 2310，這也是很不精確的。
到此，關于“MySQL 的 ref 有什么用”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！
