MySQL的ref有什么用

這篇文章主要介紹“MySQL的ref有什么用”，在日常操作中，相信很多人在MySQL的ref有什么用問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL的ref有什么用”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

 回憶一下查詢成本

對于一個查詢來說，有時候可以通過不同的索引或者全表掃描來執(zhí)行它，MySQL優(yōu)化器會通過事先生成的統(tǒng)計數(shù)據(jù)，或者少量訪問B+樹索引的方式來分析使用各個索引時都需要掃描多少條記錄，然后計算使用不同索引的查詢成本，最后選擇成本最低的那個來執(zhí)行查詢。

創(chuàng)建場景

假如我們現(xiàn)在有一個表t，它的表結(jié)構(gòu)如下所示：

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';

按理說上邊兩個查詢需要掃描的記錄數(shù)量是一樣的，MySQL查詢優(yōu)化器對待它們的態(tài)度也應(yīng)該是一樣的，也就是要么都使用二級索引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。

為什么?憑什么?同樣是掃描相同數(shù)量的記錄，憑什么我range訪問方法就要比你ref低一頭?設(shè)計MySQL的大叔，你為何這么偏心...

解密偏心原因

世界上沒有無緣無故的愛，也沒有無緣無故的恨。這事兒還得從索引結(jié)構(gòu)說起。比方說idx_key1二級索引結(jié)構(gòu)長這樣：

原諒我們把索引對應(yīng)的B+樹結(jié)構(gòu)弄了一個極度精簡版，我們忽略掉了頁的結(jié)構(gòu)，只保留了葉子節(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值所屬的聚簇索引記錄很大可能在同一個數(shù)據(jù)頁中

• 即使前一次回表的id值所屬的聚簇索引記錄和下一次回表的id值所屬的聚簇索引記錄不在同一個數(shù)據(jù)頁中，由于回表的id值是遞增的，所以我們很大可能通過順序I/O的方式找到下一個數(shù)據(jù)頁，也就是說這個過程中很大可能不需要很大幅度的移動磁頭就可以找到下一個數(shù)據(jù)頁。這可以減少很多隨機I/O帶來的性能開銷。

綜上所述，執(zhí)行語句1時，回表操作帶來的性能開銷較小。

而對于查詢2來說：

查詢2: SELECT * FROM t WHERE key1 > 'a' AND key1 < 'i';

由于需要掃描的二級索引記錄對應(yīng)的id值是無序的，所以執(zhí)行回表操作時，需要訪問的聚簇索引記錄所在的數(shù)據(jù)頁很大可能就是無序的，這樣會造成很多隨機I/O。所以如果使用idx_key1來執(zhí)行查詢1和查詢2，執(zhí)行查詢1的成本很顯然會比查詢2低，這也是設(shè)計MySQL的大叔更鐘情于ref而不是range的原因。

MySQL的內(nèi)部實現(xiàn)

MySQL優(yōu)化器在計算回表的成本時，在使用二級索引執(zhí)行查詢并且需要回表的情境下，對于ref和range是很明顯的區(qū)別對待的：

• 對于range來說，需要掃描多少條二級索引記錄，就相當(dāng)于需要訪問多少個頁面。每訪問一個頁面，回表的I/O成本就加1。

比方對于查詢2來說，需要回表的記錄數(shù)是2310，因為回表操作而計算的I/O成本就是2310。

• 對于ref來說，回表開銷帶來的I/O成本存在天花板，也就是定義了一個上限值：

double worst_seeks;

這個上限值的取值是從下邊兩個值中取較小的那個：

比方對于查詢1來說，回表的記錄數(shù)是2310，按理說計算因回表操作帶來的I/O成本也應(yīng)該是2310。但是由于對于ref訪問方法，計算回表操作時帶來的I/O成本時存在天花板，會從全表記錄的十分之一(也就是9912/10=991，9912為估計值)以及聚簇索引所占頁面的3倍(本例中聚簇索引占用的頁面數(shù)就是97，乘以3就是291)選擇更小的那個，本例中也就是291。

• 全表記錄數(shù)的十分之一(此處的全表記錄數(shù)屬于統(tǒng)計數(shù)據(jù)，是一個估計值)

• 聚簇索引所占頁面的3倍

小貼士：在成本分析的代碼中，range和index、all是被分到一類里的，ref是親兒子，單獨分析了一波。不過我們也可以看到，設(shè)計MySQL的大叔在計算range訪問方法的代價時，直接認(rèn)為每次回表都需要進行一次頁面I/O，這是十分粗暴的，何況我們的實際聚簇索引總共才97個頁面，它卻將回表成本計算為2310，這也是很不精確的。

到此，關(guān)于“MySQL的ref有什么用”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>