SQL 不知道咋最佳化？吹一手 join 語句的最佳化準沒錯

面試最怕遇到的問題是什麼，如何做最佳化一定當仁不讓，SQL 最佳化更是首當其衝，這裡先跟大家分享一個比較容易理解的 join 語句的最佳化~

前文提到過，當能夠用上被驅動表的索引的時候，使用的是 Index Nested-Loop Join 演算法，這時效能還是很好的；但是，用不上被驅動表的索引的時候，使用的 Block Nested-Loop Join 演算法效能就差多了，非常消耗資源。

針對 join 語句的這兩種情況，其實都還是存在繼續最佳化的空間的

Multi-Range Read 最佳化

我們先來回顧一下 “回表” 這個概念。回表是指，InnoDB 在普通索引上查到主鍵 id 的值後，再根據主鍵 id 的值到主鍵索引樹上去查詢整行記錄的過程。

那麼，思考一個問題，回表的過程是一行行地查資料，還是批次地查資料？

顯然是一行行地。

因為回表查詢的本質就是查詢 B+ 樹，在這棵樹上，每次只能根據一個主鍵 id 查到一行資料。

看下面這條語句，從 user 表中獲取 80 歲以上使用者的資訊：

select * from user where age >= 80;

假設，age 對應的 id 是連續自增的，這樣，我們對於主鍵索引樹的查詢，就是連續的：

當然，這是理想情況，如果 age 對應的 id 值不是順序的話，那當我們順序取 age 的時候，id 的獲取就是亂序隨機的了，效能就會比較差。解釋下為什麼這裡亂序查詢的效能就比較差：

首先，我們都知道，索引檔案其實就是一個磁碟檔案，儘管有記憶體中 Buffer Pool 的存在可以減少訪問磁碟的次數，但是並不能完全避開對磁碟的訪問。而對於磁碟來說，一個磁碟從內到外有許多磁軌，一個磁軌又被劃分成多個相同的扇區，隨機讀取效能較差的原因就是每次都需要花費時間去尋找磁軌，找到磁軌之後又要去尋找合適的扇區，從而耗費大量時間。所以順序讀取比隨機讀取快很多。

所以，一個很自然的想法，就是調整主鍵 id 查詢的順序，使其接近順序讀取，從而達到加速的目的。

那麼，具體該如何調整主鍵 id 查詢的順序呢？

因為大多數的資料都是按照主鍵 id 遞增順序插入的，對吧，所以我們可以簡單的認為，如果按照主鍵 id 的遞增順序查詢的話，對磁碟的讀取會比較接近順序讀取，從而提升讀效能。這就是 Multi-Range Read (MRR) 最佳化的思想。

而將主鍵 id 進行升序排序的過程，是在記憶體中的隨機讀取緩衝區 read_rnd_buffer 中進行的。

我們可以設定 set optimizer_switch="mrr_cost_based=off" 來開啟 MRR 最佳化，這樣，語句的執行流程就是下面這個樣子：

1. 根據普通索引 age，找到滿足條件的主鍵 id，然後將 id 值放入 read_rnd_buffer 中
2. 將 read_rnd_buffer 中的 id 進行遞增排序；
3. 根據排序後的 id 陣列，進行回表查詢

需要注意的是，read_rnd_buffer 的大小是由 read_rnd_buffer_size 引數控制的。如果發現 read_rnd_buffer 放滿了，那麼 MySQL 就會先執行完步驟 2 和 3，然後清空 read_rnd_buffer，之後再繼續迴圈。

可以看出來，使用 MRR 提升效能主要適用於範圍查詢，這樣可以得到足夠多的主鍵 id，透過排序以後，再去主鍵索引查資料，從而體現出順序讀取的優勢。

MRR 這種開闢一個記憶體空間對主鍵 id 進行排序的思想呢，應用到 join 語句的最佳化層面上來，就是 MySQL 在 5.6 版本後引入的 Batched Key Access 演算法（BKA），下面我們來解析下這個演算法以及如何使用這個演算法對 Index Nested-Loop Join 和 Block Nested-Loop Join 兩種情況進行最佳化。

最佳化 Index Nested-Loop Join

假設我們已經在 age 欄位上建立了索引，那麼下面這條 sql 語句用到的就是 Index Nested-Loop Join 演算法，回顧下具體的執行邏輯：

select * from table1 join table2 on table1.age = table2.age where table2.age >= 80;

1. 從 table1 表中讀入一行資料 R
2. 從資料行 R 中，取出 age 欄位到表 table2 的 age 索引樹上去找並取得對應的主鍵
3. 根據主鍵回表查詢，取出 table2 表中滿足條件的行，然後跟 R 組成一行，作為結果集的一部分

也就是說，對於表 table2 來說，每次都是隻匹配一個值。這時，MRR 的優勢就用不上了。

所以，如果想要享受到 MRR 帶來的最佳化，就必須在被驅動表 table2 上使用範圍匹配，換句話說，我們需要一次性地多傳些值給表 table2。那麼具體該怎麼做呢？

方法就是，從表 table1 中一次性地多拿些行出來，先放到一個臨時記憶體中，然後再一起傳給表 table2。而這個臨時記憶體不是別人，就是 join_buffer！

之前我們分析過 Block Nested-Loop Join 演算法中用到了 join_buffer，而 Index Nested-Loop Join 並沒有用到，這不，在最佳化這裡派上用場了。

這就是 BKA 演算法對 Index Nested-Loop Join 的最佳化，可以透過下面這行命令啟用 BKA 最佳化演算法

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

前兩個引數的作用是啟用 MRR，因為 BKA 演算法的最佳化依賴於 MRR。

最佳化 Block Nested-Loop Join

那如果用不上被驅動表索引的話，使用的 BNL 演算法效能是比較低的，所以常見的最佳化方法就是給被驅動表的 join 欄位加上索引。

但是，如果這條 SQL 語句的使用頻率比較低並且資料量不大的話，建立索引其實就比較浪費資源了。

所以，有沒有一種兩全其美的辦法呢？

這時候，我們可以考慮使用臨時表。使用臨時表的大致思路是：

1. 把表 table2 中滿足條件的資料放在臨時表 temp_table2 中
2. 給臨時表 temp_table2 的欄位 age 加上索引
3. 讓表 table1 和 temp_table2 做 join 操作

這樣，一個 BNL 演算法的最佳化問題，就被我們轉換成了 Index-Nested Loop Join 的最佳化問題了，按照上述所說的，可以使用 BKA 進行最佳化。

具體的 SQL 語句如下：

# select * from table1 join table2 on table1.age = table2.age where table2.age >= 80;
create temporary table temp_table2 (id int primary key, name varchar, age int, index(age)) engine=innodb;
insert into temp_table2  select * from table1 where age >= 80;
select * from table1 join temp_table2  on (table1.b=temp_table2 .b);

總的來說，最佳化 Block Nested-Loop Join 的思路就是使用有索引的臨時表，讓 join 語句能夠用上被驅動表上的索引，從而轉換為 Index Nested-Loop Join 然後觸發 BKA 演算法，提升查詢效能。

最後放上這道題的背誦版：

? 面試官：SQL 最佳化了解過嗎？

? 小牛肉：先說 join 語句的最佳化

join 語句分為兩種情況，一種是能夠用上被驅動表的索引，這個時候使用的演算法是 Index Nested-Loop，另一種是用不上，這個時候使用的演算法是 Block Nested-Loop

• 對於 Index Nested-Loop 來說，具體步驟其實就是一個巢狀查詢，首先，遍歷驅動表，然後，對這每一行都去被驅動表中根據 on 條件欄位進行搜尋，由於被驅動表上建立了條件欄位的索引，所以每次搜尋只需要在輔助索引樹上掃描一行就行了，效能比較高
• 對於 Block Nested-Loop 來說，MySQL 首先把驅動表中的資料讀入執行緒記憶體 join_buffer 中；然後掃描被驅動表，把被驅動表中的每一行依次取出來，跟 join_buffer 中的資料做對比，滿足 on 條件的，就作為結果集的一部分返回。BNL 演算法的效能比較差，因為我們需要多次遍歷被驅動表。那麼對於 BNL 演算法來說，一個很常見的最佳化思路就是對被驅動表的條件欄位建立索引，從而轉換成 Index Nested-Loop 演算法。

對於上面這兩種 join 情況來說，如果繼續新增一個範圍查詢的 where 條件的話，其實還存在最佳化空間。

其核心做法其實就是針對範圍查詢的最佳化，也稱為 Multi-Range Read 演算法

具體來說，因為大多數的資料都是按照主鍵 id 遞增順序插入的嘛，所以我們可以簡單的認為，如果按照主鍵 id 的遞增順序進行查詢的話，對磁碟的讀取會比較接近順序讀取，這樣相比於亂序讀取的話減少了尋道時間，從而提升讀效能。

而將主鍵 id 進行升序排序的過程，是在記憶體中的隨機讀取緩衝區 read_rnd_buffer 中進行的。就是先把在輔助索引樹上查詢的滿足條件的主鍵 id 存到 read_rnd_buffer 中，然後對這些 id 進行遞增排序，根據排序後的 id 陣列，進行回表查詢。

MRR 的思想應用到 join 語句的最佳化層面上來，就是 MySQL 在 5.6 版本後引入的 Batched Key Access，BKA 演算法

• 對於 Index Nested-Loop 來說，就是一次性地從驅動表中取出很多個行記錄出來，先放到臨時記憶體 join_buffer 中，然後再一起傳給被驅動表
• 對於 Block Nested-Loop 來說，就是對被驅動表建立一個臨時表，並且對條件欄位建立索引，然後把之前兩張表的 join 操作轉換成驅動表和臨時表的 join 操作，從而轉換成對 Index Nested-Loop 的最佳化問題