本文轉(zhuǎn)載自微信公眾號(hào)「飛天小牛肉」，作者小牛肉 。轉(zhuǎn)載本文請(qǐng)聯(lián)系飛天小牛肉公眾號(hào)。

面試最怕遇到的問題是什么，如何做優(yōu)化一定當(dāng)仁不讓，SQL 優(yōu)化更是首當(dāng)其沖，這里先跟大家分享一個(gè)比較容易理解的 join 語句的優(yōu)化~

前文提到過，當(dāng)能夠用上被驅(qū)動(dòng)表的索引的時(shí)候，使用的是 Index Nested-Loop Join 算法，這時(shí)性能還是很好的;但是，用不上被驅(qū)動(dòng)表的索引的時(shí)候，使用的 Block Nested-Loop Join 算法性能就差多了，非常消耗資源。

針對(duì) join 語句的這兩種情況，其實(shí)都還是存在繼續(xù)優(yōu)化的空間的

老規(guī)矩，背誦版在文末。點(diǎn)擊閱讀原文可以直達(dá)我收錄整理的各大廠面試真題

Multi-Range Read 優(yōu)化

我們先來回顧一下 “回表” 這個(gè)概念?；乇硎侵?，InnoDB 在普通索引上查到主鍵 id 的值后，再根據(jù)主鍵 id 的值到主鍵索引樹上去查詢整行記錄的過程。

那么，思考一個(gè)問題，回表的過程是一行行地查數(shù)據(jù)，還是批量地查數(shù)據(jù)?

顯然是一行行地。

因?yàn)榛乇聿樵兊谋举|(zhì)就是查詢 B+ 樹，在這棵樹上，每次只能根據(jù)一個(gè)主鍵 id 查到一行數(shù)據(jù)。

看下面這條語句，從 user 表中獲取 80 歲以上用戶的信息：

select * from user where age >= 80; 

假設(shè)，age 對(duì)應(yīng)的 id 是連續(xù)自增的，這樣，我們對(duì)于主鍵索引樹的查詢，就是連續(xù)的：

當(dāng)然，這是理想情況，如果 age 對(duì)應(yīng)的 id 值不是順序的話，那當(dāng)我們順序取 age 的時(shí)候，id 的獲取就是亂序隨機(jī)的了，性能就會(huì)比較差。解釋下為什么這里亂序查詢的性能就比較差：

首先，我們都知道，索引文件其實(shí)就是一個(gè)磁盤文件，盡管有內(nèi)存中 Buffer Pool 的存在可以減少訪問磁盤的次數(shù)，但是并不能完全避開對(duì)磁盤的訪問。而對(duì)于磁盤來說，一個(gè)磁盤從內(nèi)到外有許多磁道，一個(gè)磁道又被劃分成多個(gè)相同的扇區(qū)，隨機(jī)讀取性能較差的原因就是每次都需要花費(fèi)時(shí)間去尋找磁道，找到磁道之后又要去尋找合適的扇區(qū)，從而耗費(fèi)大量時(shí)間。所以順序讀取比隨機(jī)讀取快很多。

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

那么，具體該如何調(diào)整主鍵 id 查詢的順序呢?

因?yàn)榇蠖鄶?shù)的數(shù)據(jù)都是按照主鍵 id 遞增順序插入的，對(duì)吧，所以我們可以簡(jiǎn)單的認(rèn)為，如果按照主鍵 id 的遞增順序查詢的話，對(duì)磁盤的讀取會(huì)比較接近順序讀取，從而提升讀性能。這就是 Multi-Range Read (MRR) 優(yōu)化的思想。

而將主鍵 id 進(jìn)行升序排序的過程，是在內(nèi)存中的隨機(jī)讀取緩沖區(qū) read_rnd_buffer 中進(jìn)行的。

我們可以設(shè)置 set optimizer_switch="mrr_cost_based=off" 來開啟 MRR 優(yōu)化，這樣，語句的執(zhí)行流程就是下面這個(gè)樣子：

• 根據(jù)普通索引 age，找到滿足條件的主鍵 id，然后將 id 值放入 read_rnd_buffer 中
• 將 read_rnd_buffer 中的 id 進(jìn)行遞增排序;
• 根據(jù)排序后的 id 數(shù)組，進(jìn)行回表查詢

需要注意的是，read_rnd_buffer 的大小是由 read_rnd_buffer_size 參數(shù)控制的。如果發(fā)現(xiàn) read_rnd_buffer 放滿了，那么 MySQL 就會(huì)先執(zhí)行完步驟 2 和 3，然后清空 read_rnd_buffer，之后再繼續(xù)循環(huán)。

可以看出來，使用 MRR 提升性能主要適用于范圍查詢，這樣可以得到足夠多的主鍵 id，通過排序以后，再去主鍵索引查數(shù)據(jù)，從而體現(xiàn)出順序讀取的優(yōu)勢(shì)。

MRR 這種開辟一個(gè)內(nèi)存空間對(duì)主鍵 id 進(jìn)行排序的思想呢，應(yīng)用到 join 語句的優(yōu)化層面上來，就是 MySQL 在 5.6 版本后引入的 Batched Key Access 算法(BKA)，下面我們來解析下這個(gè)算法以及如何使用這個(gè)算法對(duì) Index Nested-Loop Join 和 Block Nested-Loop Join 兩種情況進(jìn)行優(yōu)化。

優(yōu)化 Index Nested-Loop Join

假設(shè)我們已經(jīng)在 age 字段上建立了索引，那么下面這條 sql 語句用到的就是 Index Nested-Loop Join 算法，回顧下具體的執(zhí)行邏輯：

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

從 table1 表中讀入一行數(shù)據(jù) R

從數(shù)據(jù)行 R 中，取出 age 字段到表 table2 的 age 索引樹上去找并取得對(duì)應(yīng)的主鍵

根據(jù)主鍵回表查詢，取出 table2 表中滿足條件的行，然后跟 R 組成一行，作為結(jié)果集的一部分

也就是說，對(duì)于表 table2 來說，每次都是只匹配一個(gè)值。這時(shí)，MRR 的優(yōu)勢(shì)就用不上了。

所以，如果想要享受到 MRR 帶來的優(yōu)化，就必須在被驅(qū)動(dòng)表 table2 上使用范圍匹配，換句話說，我們需要一次性地多傳些值給表 table2。那么具體該怎么做呢?

方法就是，從表 table1 中一次性地多拿些行出來，先放到一個(gè)臨時(shí)內(nèi)存中，然后再一起傳給表 table2。而這個(gè)臨時(shí)內(nèi)存不是別人，就是 join_buffer!

之前我們分析過 Block Nested-Loop Join 算法中用到了 join_buffer，而 Index Nested-Loop Join 并沒有用到，這不，在優(yōu)化這里派上用場(chǎng)了。

這就是 BKA 算法對(duì) Index Nested-Loop Join 的優(yōu)化，可以通過下面這行命令啟用 BKA 優(yōu)化算法

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

前兩個(gè)參數(shù)的作用是啟用 MRR，因?yàn)?BKA 算法的優(yōu)化依賴于 MRR。

優(yōu)化 Block Nested-Loop Join

那如果用不上被驅(qū)動(dòng)表索引的話，使用的 BNL 算法性能是比較低的，所以常見的優(yōu)化方法就是給被驅(qū)動(dòng)表的 join 字段加上索引。

但是，如果這條 SQL 語句的使用頻率比較低并且數(shù)據(jù)量不大的話，建立索引其實(shí)就比較浪費(fèi)資源了。

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

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

把表 table2 中滿足條件的數(shù)據(jù)放在臨時(shí)表 temp_table2 中

給臨時(shí)表 temp_table2 的字段 age 加上索引

讓表 table1 和 temp_table2 做 join 操作

這樣，一個(gè) BNL 算法的優(yōu)化問題，就被我們轉(zhuǎn)換成了 Index-Nested Loop Join 的優(yōu)化問題了，按照上述所說的，可以使用 BKA 進(jìn)行優(yōu)化。

具體的 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); 

總的來說，優(yōu)化 Block Nested-Loop Join 的思路就是使用有索引的臨時(shí)表，讓 join 語句能夠用上被驅(qū)動(dòng)表上的索引，從而轉(zhuǎn)換為 Index Nested-Loop Join 然后觸發(fā) BKA 算法，提升查詢性能。

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

面試官：SQL 優(yōu)化了解過嗎?

小牛肉：先說 join 語句的優(yōu)化

join 語句分為兩種情況，一種是能夠用上被驅(qū)動(dòng)表的索引，這個(gè)時(shí)候使用的算法是 Index Nested-Loop，另一種是用不上，這個(gè)時(shí)候使用的算法是 Block Nested-Loop

• 對(duì)于 Index Nested-Loop 來說，具體步驟其實(shí)就是一個(gè)嵌套查詢，首先，遍歷驅(qū)動(dòng)表，然后，對(duì)這每一行都去被驅(qū)動(dòng)表中根據(jù) on 條件字段進(jìn)行搜索，由于被驅(qū)動(dòng)表上建立了條件字段的索引，所以每次搜索只需要在輔助索引樹上掃描一行就行了，性能比較高
• 對(duì)于 Block Nested-Loop 來說，MySQL 首先把驅(qū)動(dòng)表中的數(shù)據(jù)讀入線程內(nèi)存 join_buffer 中;然后掃描被驅(qū)動(dòng)表，把被驅(qū)動(dòng)表中的每一行依次取出來，跟 join_buffer 中的數(shù)據(jù)做對(duì)比，滿足 on 條件的，就作為結(jié)果集的一部分返回。BNL 算法的性能比較差，因?yàn)槲覀冃枰啻伪闅v被驅(qū)動(dòng)表。那么對(duì)于 BNL 算法來說，一個(gè)很常見的優(yōu)化思路就是對(duì)被驅(qū)動(dòng)表的條件字段建立索引，從而轉(zhuǎn)換成 Index Nested-Loop 算法。

對(duì)于上面這兩種 join 情況來說，如果繼續(xù)添加一個(gè)范圍查詢的 where 條件的話，其實(shí)還存在優(yōu)化空間。

其核心做法其實(shí)就是針對(duì)范圍查詢的優(yōu)化，也稱為 Multi-Range Read 算法

具體來說，因?yàn)榇蠖鄶?shù)的數(shù)據(jù)都是按照主鍵 id 遞增順序插入的嘛，所以我們可以簡(jiǎn)單的認(rèn)為，如果按照主鍵 id 的遞增順序進(jìn)行查詢的話，對(duì)磁盤的讀取會(huì)比較接近順序讀取，這樣相比于亂序讀取的話減少了尋道時(shí)間，從而提升讀性能。

而將主鍵 id 進(jìn)行升序排序的過程，是在內(nèi)存中的隨機(jī)讀取緩沖區(qū) read_rnd_buffer 中進(jìn)行的。就是先把在輔助索引樹上查找的滿足條件的主鍵 id 存到 read_rnd_buffer 中，然后對(duì)這些 id 進(jìn)行遞增排序，根據(jù)排序后的 id 數(shù)組，進(jìn)行回表查詢。

MRR 的思想應(yīng)用到 join 語句的優(yōu)化層面上來，就是 MySQL 在 5.6 版本后引入的 Batched Key Access，BKA 算法

• 對(duì)于 Index Nested-Loop 來說，就是一次性地從驅(qū)動(dòng)表中取出很多個(gè)行記錄出來，先放到臨時(shí)內(nèi)存 join_buffer 中，然后再一起傳給被驅(qū)動(dòng)表
• 對(duì)于 Block Nested-Loop 來說，就是對(duì)被驅(qū)動(dòng)表建立一個(gè)臨時(shí)表，并且對(duì)條件字段建立索引，然后把之前兩張表的 join 操作轉(zhuǎn)換成驅(qū)動(dòng)表和臨時(shí)表的 join 操作，從而轉(zhuǎn)換成對(duì) Index Nested-Loop 的優(yōu)化問題

balabala.......(后續(xù)其他 SQL 優(yōu)化會(huì)慢慢更新的~)