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

ITPUB社群 發表於 2022-11-24

面試最怕遇到的問題是什麼,如何做最佳化一定當仁不讓,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 是連續自增的,這樣,我們對於主鍵索引樹的查詢,就是連續的:

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

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

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

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

所以,一個很自然的想法,就是調整主鍵 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 陣列,進行回表查詢
SQL 不知道咋最佳化?吹一手 join 語句的最佳化準沒錯

需要注意的是,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 keyname varchar, age intindex(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 的最佳化問題

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70024420/viewspace-2924971/,如需轉載,請註明出處,否則將追究法律責任。