1. 大查詢是否會把數(shù)據(jù)庫內存打爆?

主機內存只有 100G，現(xiàn)在要對一個 200G 的大表做全表掃描，會不會把數(shù)據(jù)庫主機的內存用光了？

1.1 全表掃描對 server 層的影響

現(xiàn)在要對一個 200G 的 InnoDB 表 db1. t，執(zhí)行一個全表掃描。當然，你要把掃描結果保存在客戶端，會使用類似這樣的命令：

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

InnoDB 的數(shù)據(jù)是保存在主鍵索引上的，所以全表掃描實際上是直接掃描表 t 的主鍵索引。
這條查詢語句由于沒有其他的判斷條件，所以查到的每一行都可以直接放到結果集里面，然后返回給客戶端。

實際上，服務端并不需要保存一個完整的結果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程是這樣的：

1. 獲取一行，寫到 net_buffer 中。這塊內存的大小是由參數(shù) net_buffer_length 定義的，默認是 16k。
2. 重復獲取行，直到 net_buffer 寫滿，調用網(wǎng)絡接口發(fā)出去。
3. 如果發(fā)送成功，就清空 net_buffer，然后繼續(xù)取下一行，并寫入 net_buffer。
4. 如果發(fā)送函數(shù)返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地網(wǎng)絡棧（socket send buffer）寫滿了，進入等待。直到網(wǎng)絡棧重新可寫，再繼續(xù)發(fā)送。

過程對應的流程圖如下所示

圖 1 查詢結果發(fā)送流程

這個流程中:

1. 一個查詢在發(fā)送過程中，占用的 MySQL 內部的內存最大就是 net_buffer_length 這么大，并不會達到 200G；
2. socket send buffer 也不可能達到 200G（默認定義 /proc/sys/net/core/wmem_default），如果 socket send buffer 被寫滿，就會暫停讀數(shù)據(jù)的流程。

也就是說，MySQL 是“邊讀邊發(fā)的”， 這就意味著，如果客戶端接收得慢，會導致 MySQL 服務端由于結果發(fā)不出去，這個事務的執(zhí)行時間變長。

比如下面這個狀態(tài)，就是故意讓客戶端不去讀 socket receive buffer 中的內容，然后在服務端 show processlist 看到的結果。

圖 2 服務端發(fā)送阻塞

看到 State 的值一直處于Sending to client就表示服務器端的網(wǎng)絡棧寫滿了。

如果客戶端使用–quick 參數(shù)，會使用 mysql_use_result 方法。這個方法是讀一行處理一行。你可以想象一下，假設有一個業(yè)務的邏輯比較復雜，每讀一行數(shù)據(jù)以后要處理的邏輯如果很慢，就會導致客戶端要過很久才會去取下一行數(shù)據(jù)，可能就會出現(xiàn)如圖 2 所示的這種情況。

因此，對于正常的線上業(yè)務來說，如果一個查詢的返回結果不會很多的話，都建議使用 mysql_store_result 這個接口，直接把查詢結果保存到本地內存。

在自己負責維護的 MySQL 里看到很多個線程都處于“Sending to client”這個狀態(tài)，就意味著要讓業(yè)務開發(fā)同學優(yōu)化查詢結果，并評估這么多的返回結果是否合理。

如果要快速減少處于這個狀態(tài)的線程的話，將 net_buffer_length 參數(shù)設置為一個更大的值是一個可選方案。

Sending data狀態(tài)
維護的實例上看到很多查詢語句的狀態(tài)是“Sending data”，但查看網(wǎng)絡也沒什么問題啊，為什么 Sending data 要這么久？
實際上，一個查詢語句的狀態(tài)變化是這樣的（注意：這里，略去了其他無關的狀態(tài)）：

• MySQL 查詢語句進入執(zhí)行階段后，首先把狀態(tài)設置成“Sending data”；
• 然后，發(fā)送執(zhí)行結果的列相關的信息（meta data) 給客戶端；
• 再繼續(xù)執(zhí)行語句的流程；
• 執(zhí)行完成后，把狀態(tài)設置成空字符串。

Sending data”并不一定是指“正在發(fā)送數(shù)據(jù)”，而可能是處于執(zhí)行器過程中的任意階段。

也就是說，僅當一個線程處于“等待客戶端接收結果”的狀態(tài)，才會顯示"Sending to client"；而如果顯示成“Sending data”，它的意思只是“正在執(zhí)行”。

查詢的結果是分段發(fā)給客戶端的，因此掃描全表，查詢返回大量的數(shù)據(jù)，并不會把內存打爆。

1.2 全表掃描對 InnoDB 的影響

InnoDB 內存的一個作用，是保存更新的結果，再配合 redo log，就避免了隨機寫盤。

內存的數(shù)據(jù)頁是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。而實際上，Buffer Pool 還有一個更重要的作用，就是加速查詢。

由于有 WAL 機制，當事務提交的時候，磁盤上的數(shù)據(jù)頁是舊的，那如果這時候馬上有一個查詢要來讀這個數(shù)據(jù)頁，是不是要馬上把 redo log 應用到數(shù)據(jù)頁呢？

不需要。因為這時候內存數(shù)據(jù)頁的結果是最新的，直接讀內存頁就可以了。這時候查詢根本不需要讀磁盤，直接從內存拿結果，速度是很快的。所以說，Buffer Pool 還有加速查詢的作用。

Buffer Pool 對查詢的加速效果，依賴于一個重要的指標，即：內存命中率

可以在 show engine innodb status 結果中，查看一個系統(tǒng)當前的 BP 命中率。一般情況下，一個穩(wěn)定服務的線上系統(tǒng)，要保證響應時間符合要求的話，內存命中率要在 99% 以上。

執(zhí)行 show engine innodb status ，可以看到“Buffer pool hit rate”字樣，顯示的就是當前的命中率。比如圖 5 這個命中率，就是 99.0%。

圖 5 show engine innodb status 顯示內存命中率

InnoDB Buffer Pool 的大小是由參數(shù) innodb_buffer_pool_size 確定的，一般建議設置成可用物理內存的 60%~80%。

Innodb_buffer_pool_size 小于磁盤的數(shù)據(jù)量是很常見的。如果一個 Buffer Pool 滿了，而又要從磁盤讀入一個數(shù)據(jù)頁，那肯定是要淘汰一個舊數(shù)據(jù)頁的。
InnoDB 內存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，這個算法的核心就是淘汰最久未使用的數(shù)據(jù)。

LRU 算法的基本模型

圖 6 基本 LRU 算法

InnoDB 管理 Buffer Pool 的 LRU 算法，是用鏈表來實現(xiàn)的。

1. 在圖 6 的狀態(tài) 1 里，鏈表頭部是 P1，表示 P1 是最近剛剛被訪問過的數(shù)據(jù)頁；假設內存里只能放下這么多數(shù)據(jù)頁；
2. 這時候有一個讀請求訪問 P3，因此變成狀態(tài) 2，P3 被移到最前面；
3. 狀態(tài) 3 表示，這次訪問的數(shù)據(jù)頁是不存在于鏈表中的，所以需要在 Buffer Pool 中新申請一個數(shù)據(jù)頁 Px，加到鏈表頭部。但是由于內存已經(jīng)滿了，不能申請新的內存。于是，會清空鏈表末尾 Pm 這個數(shù)據(jù)頁的內存，存入 Px 的內容，然后放到鏈表頭部。
4. 從效果上看，就是最久沒有被訪問的數(shù)據(jù)頁 Pm，被淘汰了。

假設按照這個算法，要掃描一個 200G 的表，而這個表是一個歷史數(shù)據(jù)表，平時沒有業(yè)務訪問它。

按照這個算法掃描的話，就會把當前的 Buffer Pool 里的數(shù)據(jù)全部淘汰掉，存入掃描過程中訪問到的數(shù)據(jù)頁的內容。也就是說 Buffer Pool 里面主要放的是這個歷史數(shù)據(jù)表的數(shù)據(jù)。對于一個正在做業(yè)務服務的庫，這可不妙?？梢钥吹?，Buffer Pool 的內存命中率急劇下降，磁盤壓力增加，SQL 語句響應變慢。

所以，InnoDB 不能直接使用這個 LRU 算法。實際上，InnoDB 對 LRU 算法做了改進。

圖 7 改進的 LRU 算法

在 InnoDB 實現(xiàn)上，按照 5:3 的比例把整個 LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域。圖中 LRU_old 指向的就是 old 區(qū)域的第一個位置，是整個鏈表的 5/8 處。也就是說，靠近鏈表頭部的 5/8 是 young 區(qū)域，靠近鏈表尾部的 3/8 是 old 區(qū)域。

改進后的 LRU 算法執(zhí)行流程:

1. 圖 7 中狀態(tài) 1，要訪問數(shù)據(jù)頁 P3，由于 P3 在 young 區(qū)域，因此和優(yōu)化前的 LRU 算法一樣，將其移到鏈表頭部，變成狀態(tài) 2。
2. 之后要訪問一個新的不存在于當前鏈表的數(shù)據(jù)頁，這時候依然是淘汰掉數(shù)據(jù)頁 Pm，但是新插入的數(shù)據(jù)頁 Px，是放在 LRU_old 處。
3. 處于 old 區(qū)域的數(shù)據(jù)頁，每次被訪問的時候都要做下面這個判斷：

• 若這個數(shù)據(jù)頁在 LRU 鏈表中存在的時間超過了 1 秒，就把它移動到鏈表頭部；
• 如果這個數(shù)據(jù)頁在 LRU 鏈表中存在的時間短于 1 秒，位置保持不變。1 秒這個時間，是由參數(shù) innodb_old_blocks_time 控制的。其默認值是 1000，單位毫秒。

這個策略，就是為了處理類似全表掃描的操作量身定制的。還是以剛剛的掃描 200G 的歷史數(shù)據(jù)表為例，看看改進后的 LRU 算法的操作邏輯：

1. 掃描過程中，需要新插入的數(shù)據(jù)頁，都被放到 old 區(qū)域 ;
2. 一個數(shù)據(jù)頁里面有多條記錄，這個數(shù)據(jù)頁會被多次訪問到，但由于是順序掃描，這個數(shù)據(jù)頁第一次被訪問和最后一次被訪問的時間間隔不會超過 1 秒，因此還是會被保留在 old 區(qū)域；
3. 再繼續(xù)掃描后續(xù)的數(shù)據(jù)，之前的這個數(shù)據(jù)頁之后也不會再被訪問到，于是始終沒有機會移到鏈表頭部（也就是 young 區(qū)域），很快就會被淘汰出去。

這個策略最大的收益，就是在掃描這個大表的過程中，雖然也用到了 Buffer Pool，但是對 young 區(qū)域完全沒有影響，從而保證了 Buffer Pool 響應正常業(yè)務的查詢命中率。

思考
如果客戶端由于壓力過大，遲遲不能接收數(shù)據(jù)，會對服務端造成什么嚴重的影響。

這個問題的核心是，造成了“長事務”。
至于長事務的影響，就要結合我們前面文章中提到的鎖、MVCC 的知識點了。

• 如果前面的語句有更新，意味著它們在占用著行鎖，會導致別的語句更新被鎖??；
• 當然讀的事務也有問題，就是會導致 undo log 不能被回收，導致回滾段空間膨脹。

2. 可不可以使用join?

于 join 語句使用的問題，一般會集中在以下兩類：

1. DBA 不讓使用 join，使用 join 有什么問題呢？
2. 如果有兩個大小不同的表做 join，應該用哪個表做驅動表呢？

創(chuàng)建兩個表 t1 和 t2

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

兩個表都有一個主鍵索引 id 和一個索引 a，字段 b 上無索引。存儲過程 idata() 往表 t2 里插入了 1000 行數(shù)據(jù)，在表 t1 里插入的是 100 行數(shù)據(jù)。

2.1 Index Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用 join 語句，MySQL 優(yōu)化器可能會選擇表 t1 或 t2 作為驅動表，這樣會影響分析 SQL 語句的執(zhí)行過程。
所以，為了便于分析執(zhí)行過程中的性能問題，我改用 straight_join 讓 MySQL 使用固定的連接方式執(zhí)行查詢，這樣優(yōu)化器只會按照指定的方式去 join。在這個語句里，t1 是驅動表，t2 是被驅動表。

explain 結果

圖 1 使用索引字段 join 的 explain 結果

可以看到，在這條語句里，被驅動表 t2 的字段 a 上有索引，join 過程用上了這個索引，因此這個語句的執(zhí)行流程是這樣的：

1. 從表 t1 中讀入一行數(shù)據(jù) R；
2. 從數(shù)據(jù)行 R 中，取出 a 字段到表 t2 里去查找；
3. 取出表 t2 中滿足條件的行，跟 R 組成一行，作為結果集的一部分；
4. 重復執(zhí)行步驟 1 到 3，直到表 t1 的末尾循環(huán)結束。

這個過程是先遍歷表 t1，然后根據(jù)從表 t1 中取出的每行數(shù)據(jù)中的 a 值，去表 t2 中查找滿足條件的記錄。
這個過程就跟寫程序時的嵌套查詢類似，并且可以用上被驅動表的索引，所以我們稱之為“Index Nested-Loop Join”，簡稱 NLJ。

流程圖如下所示：

圖 2 Index Nested-Loop Join 算法的執(zhí)行流程

在這個流程里：

1. 對驅動表 t1 做了全表掃描，這個過程需要掃描 100 行；
2. 而對于每一行 R，根據(jù) a 字段去表 t2 查找，走的是樹搜索過程。由于我們構造的數(shù)據(jù)都是一一對應的，因此每次的搜索過程都只掃描一行，也是總共掃描 100 行；
3. 所以，整個執(zhí)行流程，總掃描行數(shù)是 200。

第一個問題：能不能使用 join?
假設不使用 join，那就只能用單表查詢。

看看上面這條語句的需求，用單表查詢怎么實現(xiàn)。

1. 執(zhí)行select * from t1，查出表 t1 的所有數(shù)據(jù)，這里有 100 行；
2. 循環(huán)遍歷這 100 行數(shù)據(jù)：

• 從每一行 R 取出字段 a 的值 $R.a；
• 執(zhí)行select * from t2 where a=$R.a；
• 把返回的結果和 R 構成結果集的一行。

可以看到，在這個查詢過程，也是掃描了 200 行，但是總共執(zhí)行了 101 條語句，比直接 join 多了 100 次交互。除此之外，客戶端還要自己拼接 SQL 語句和結果。

顯然，這么做還不如直接 join 好。

第二個問題：怎么選擇驅動表？
在這個 join 語句執(zhí)行過程中，驅動表是走全表掃描，而被驅動表是走樹搜索。

假設被驅動表的行數(shù)是 M。每次在被驅動表查一行數(shù)據(jù)，要先搜索索引 a，再搜索主鍵索引。每次搜索一棵樹近似復雜度是以 2 為底的 M 的對數(shù)，記為 log2M，所以在被驅動表上查一行的時間復雜度是 2*log2M。

假設驅動表的行數(shù)是 N，執(zhí)行過程就要掃描驅動表 N 行，然后對于每一行，到被驅動表上匹配一次。

因此整個執(zhí)行過程，近似復雜度是 N + N * 2 * log2M。

顯然，N 對掃描行數(shù)的影響更大，因此應該讓小表來做驅動表。

小結一下，通過上面的分析我們得到了兩個結論：

1. 使用 join 語句，性能比強行拆成多個單表執(zhí)行 SQL 語句的性能要好；
2. 如果使用 join 語句的話，需要讓小表做驅動表。

這個結論的前提是“可以使用被驅動表的索引”

再看看被驅動表用不上索引的情況。

2.2 Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表 t2 的字段 b 上沒有索引，因此再用圖 2 的執(zhí)行流程時，每次到 t2 去匹配的時候，就要做一次全表掃描。

繼續(xù)使用圖 2 的算法，是不是可以得到正確的結果呢？如果只看結果的話，這個算法是正確的，而且這個算法也有一個名字，叫做“Simple Nested-Loop Join”。

但是，這樣算來，這個 SQL 請求就要掃描表 t2 多達 100 次，總共掃描 1001000=10 萬行。*

MySQL 也沒有使用這個 Simple Nested-Loop Join 算法，而是使用了另一個叫作“Block Nested-Loop Join”的算法，簡稱 BNL。

2.3 Block Nested-Loop Join

這時候，被驅動表上沒有可用的索引，算法的流程是這樣的：

1. 把表 t1 的數(shù)據(jù)讀入線程內存 join_buffer 中，由于我們這個語句中寫的是 select *，因此是把整個表 t1 放入了內存；
2. 掃描表 t2，把表 t2 中的每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對比，滿足 join 條件的，作為結果集的一部分返回。

流程圖如下：
圖 3 Block Nested-Loop Join 算法的執(zhí)行流程

explain 結果如下所示：

圖 4 不使用索引字段 join 的 explain 結果

在這個過程中，對表 t1 和 t2 都做了一次全表掃描，因此總的掃描行數(shù)是 1100。
由于 join_buffer 是以無序數(shù)組的方式組織的，因此對表 t2 中的每一行，都要做 100 次判斷，總共需要在內存中做的判斷次數(shù)是：100*1000=10 萬次。

前面說過，如果使用 Simple Nested-Loop Join 算法進行查詢，掃描行數(shù)也是 10 萬行。因此，從時間復雜度上來說，這兩個算法是一樣的。但是，Block Nested-Loop Join 算法的這 10 萬次判斷是內存操作，速度上會快很多，性能也更好。

假設小表的行數(shù)是 N，大表的行數(shù)是 M，那么在這個算法里：

1. 兩個表都做一次全表掃描，所以總的掃描行數(shù)是 M+N；
2. 內存中的判斷次數(shù)是 M*N。

可以看到，調換這兩個算式中的 M 和 N 沒差別，因此這時候選擇大表還是小表做驅動表，執(zhí)行耗時是一樣的。

要是表 t1 是一個大表，join_buffer 放不下怎么辦呢？join_buffer 的大小是由參數(shù) join_buffer_size 設定的，默認值是 256k。如果放不下表 t1 的所有數(shù)據(jù)話，策略很簡單，就是分段放。

把 join_buffer_size 改成 1200，再執(zhí)行：

select * from t1 straight_join t2 on (t1.a=t2.b);

執(zhí)行過程就變成了：

1. 掃描表 t1，順序讀取數(shù)據(jù)行放入 join_buffer 中，放完第 88 行 join_buffer 滿了，繼續(xù)第 2 步；
2. 掃描表 t2，把 t2 中的每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對比，滿足 join 條件的，作為結果集的一部分返回；
3. 清空 join_buffer；
4. 繼續(xù)掃描表 t1，順序讀取最后的 12 行數(shù)據(jù)放入 join_buffer 中，繼續(xù)執(zhí)行第 2 步。

執(zhí)行流程圖也就變成這樣：
圖 5 Block Nested-Loop Join – 兩段

圖中的步驟 4 和 5，表示清空 join_buffer 再復用。

可以看到，這時候由于表 t1 被分成了兩次放入 join_buffer 中，導致表 t2 會被掃描兩次。雖然分成兩次放入 join_buffer，但是判斷等值條件的次數(shù)還是不變的，依然是 (88+12)*1000=10 萬次。

驅動表的選擇問題
假設，驅動表的數(shù)據(jù)行數(shù)是 N，需要分 K 段才能完成算法流程，被驅動表的數(shù)據(jù)行數(shù)是 M。注意，這里的 K 不是常數(shù)，N 越大 K 就會越大，因此把 K 表示為λ*N，顯然λ的取值范圍是 (0,1)。

在這個算法的執(zhí)行過程中：

1. 掃描行數(shù)是 N+λNM；
2. 內存判斷 N*M 次。

顯然，內存判斷次數(shù)是不受選擇哪個表作為驅動表影響的。而考慮到掃描行數(shù)，在 M 和 N 大小確定的情況下，N 小一些，整個算式的結果會更小。

所以結論是，應該讓小表當驅動表。

在 N+λNM 這個式子里，λ才是影響掃描行數(shù)的關鍵因素，這個值越小越好。

N 越大，分段數(shù) K 越大。那么，N 固定的時候，什么參數(shù)會影響 K 的大小呢？（也就是λ的大?。┐鸢甘?join_buffer_size。
join_buffer_size 越大，一次可以放入的行越多，分成的段數(shù)也就越少，對被驅動表的全表掃描次數(shù)就越少。

再來試著回答文章開頭的兩個問題。

• 第一個問題：能不能使用 join 語句？

1. 如果可以使用 Index Nested-Loop Join 算法，也就是說可以用上被驅動表上的索引，其實是沒問題的；
2. 如果使用 Block Nested-Loop Join 算法，掃描行數(shù)就會過多。尤其是在大表上的 join 操作，這樣可能要掃描被驅動表很多次，會占用大量的系統(tǒng)資源。所以這種 join 盡量不要用。

所以在判斷要不要使用 join 語句時，就是看 explain 結果里面，Extra 字段里面有沒有出現(xiàn)“Block Nested Loop”字樣。

• 第二個問題是：如果要使用 join，應該選擇大表做驅動表還是選擇小表做驅動表？

1. 如果是 Index Nested-Loop Join 算法，應該選擇小表做驅動表；
2. 如果是 Block Nested-Loop Join 算法：

• 在 join_buffer_size 足夠大的時候，是一樣的；
• 在 join_buffer_size 不夠大的時候（這種情況更常見），應該選擇小表做驅動表。

所以，這個問題的結論就是，總是應該使用小表做驅動表。

什么叫作“小表”?
如果我在語句的 where 條件加上 t2.id<=50 這個限定條件，再來看下這兩條語句：

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

為了讓兩條語句的被驅動表都用不上索引，所以 join 字段都使用了沒有索引的字段 b。

但如果是用第二個語句的話，join_buffer 只需要放入 t2 的前 50 行，顯然是更好的。所以這里，“t2 的前 50 行”是那個相對小的表，也就是“小表”。

另外一組例子：

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

這個例子中，表 t1 和 t2 都是只有 100 行參加 join。但是，這兩條語句每次查詢放入 join_buffer 中的數(shù)據(jù)是不一樣的：

• 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，則 join_buffer 中只需要放入 b 的值；
• 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的話，就需要放入三個字段 id、a 和 b。

這里，應該選擇表 t1 作為驅動表。也就是說在這個例子里，“只需要一列參與 join 的表 t1”是那個相對小的表。

所以，更準確地說，在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過濾完成之后，計算參與 join 的各個字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個表，就是“小表”，應該作為驅動表。

2.4 Simple Nested Loop Join 的性能問題

BNL 算法和 Simple Nested Loop Join 算法都是要判斷 M*N 次（M 和 N 分別是 join 的兩個表的行數(shù)），但是 Simple Nested Loop Join 算法的每輪判斷都要走全表掃描，因此性能上 BNL 算法執(zhí)行起來會快很多。

BNL 算法的執(zhí)行邏輯是：

1. 首先，將驅動表的數(shù)據(jù)全部讀入內存 join_buffer 中，這里 join_buffer 是無序數(shù)組；
2. 然后，順序遍歷被驅動表的所有行，每一行數(shù)據(jù)都跟 join_buffer 中的數(shù)據(jù)進行匹配，匹配成功則作為結果集的一部分返回。

Simple Nested Loop Join 算法的執(zhí)行邏輯是：
順序取出驅動表中的每一行數(shù)據(jù)，到被驅動表去做全表掃描匹配，匹配成功則作為結果集的一部分返回。

Simple Nested Loop Join 算法，其實也是把數(shù)據(jù)讀到內存里，然后按照匹配條件進行判斷，為什么性能差距會這么大呢？

這個問題，需要用到MySQL 中索引結構和 Buffer Pool 的相關知識點：

1. 在對被驅動表做全表掃描的時候，如果數(shù)據(jù)沒有在 Buffer Pool 中，就需要等待這部分數(shù)據(jù)從磁盤讀入；
   從磁盤讀入數(shù)據(jù)到內存中，會影響正常業(yè)務的 Buffer Pool 命中率，而且這個算法天然會對被驅動表的數(shù)據(jù)做多次訪問，更容易將這些數(shù)據(jù)頁放到 Buffer Pool 的頭部
2. 即使被驅動表數(shù)據(jù)都在內存中，每次查找“下一個記錄的操作”，都是類似指針操作。而 join_buffer 中是數(shù)組，遍歷的成本更低。

所以說，BNL 算法的性能會更好。

小結
3. 如果可以使用被驅動表的索引，join 語句還是有其優(yōu)勢的；
4. 不能使用被驅動表的索引，只能使用 Block Nested-Loop Join 算法，這樣的語句就盡量不要使用；
5. 在使用 join 的時候，應該讓小表做驅動表。

思考
如果被驅動表是一個大表，并且是一個冷數(shù)據(jù)表，除了查詢過程中可能會導致 IO 壓力大以外，你覺得對這個 MySQL 服務還有什么更嚴重的影響嗎？

使用 BNL 算法的 join 語句，多次掃描一個冷表，而且這個語句執(zhí)行時間超過 1 秒，就會在再次掃描冷表的時候，把冷表的數(shù)據(jù)頁移到 LRU 鏈表頭部。
這種情況對應的，是冷表的數(shù)據(jù)量小于整個 Buffer Pool 的 3/8，能夠完全放入 old 區(qū)域的情況。如果這個冷表很大，就會出現(xiàn)另外一種情況：業(yè)務正常訪問的數(shù)據(jù)頁，沒有機會進入 young 區(qū)域。由于優(yōu)化機制的存在，一個正常訪問的數(shù)據(jù)頁，要進入 young 區(qū)域，需要隔 1 秒后再次被訪問到。但是，由于我們的 join 語句在循環(huán)讀磁盤和淘汰內存頁，進入 old 區(qū)域的數(shù)據(jù)頁，很可能在 1 秒之內就被淘汰了。這樣，就會導致這個 MySQL 實例的 Buffer Pool 在這段時間內，young 區(qū)域的數(shù)據(jù)頁沒有被合理地淘汰。
兩種情況都會影響 Buffer Pool 的正常運作。

3. join語句怎么優(yōu)化?

創(chuàng)建兩個表 t1、t2

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

在表 t1 里，插入了 1000 行數(shù)據(jù)，每一行的 a=1001-id 的值。
也就是說，表 t1 中字段 a 是逆序的。同時，我在表 t2 中插入了 100 萬行數(shù)據(jù)。

3.1 Multi-Range Read 優(yōu)化

Multi-Range Read 優(yōu)化 (MRR)。這個優(yōu)化的主要目的是盡量使用順序讀盤。

回表是指，InnoDB 在普通索引 a 上查到主鍵 id 的值后，再根據(jù)一個個主鍵 id 的值到主鍵索引上去查整行數(shù)據(jù)的過程。

先來看看這個問題。假設，執(zhí)行這個語句：

select * from t1 where a>=1 and a<=100;

主鍵索引是一棵 B+ 樹，在這棵樹上，每次只能根據(jù)一個主鍵 id 查到一行數(shù)據(jù)。
因此，回表肯定是一行行搜索主鍵索引的
基本流程如圖 1 所示。

如果隨著 a 的值遞增順序查詢的話，id 的值就變成隨機的，那么就會出現(xiàn)隨機訪問，性能相對較差。
雖然“按行查”這個機制不能改，但是調整查詢的順序，還是能夠加速的。

因為大多數(shù)的數(shù)據(jù)都是按照主鍵遞增順序插入得到的，所以我們可以認為，如果按照主鍵的遞增順序查詢的話，對磁盤的讀比較接近順序讀，能夠提升讀性能。這就是 MRR 優(yōu)化的設計思路。

語句的執(zhí)行流程變成了這樣：

1. 根據(jù)索引 a，定位到滿足條件的記錄，將 id 值放入 read_rnd_buffer 中 ;
2. 將 read_rnd_buffer 中的 id 進行遞增排序；
3. 排序后的 id 數(shù)組，依次到主鍵 id 索引中查記錄，并作為結果返回。

這里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 參數(shù)控制的。

如果步驟 1 中，read_rnd_buffer 放滿了，就會先執(zhí)行完步驟 2 和 3，然后清空 read_rnd_buffer。之后繼續(xù)找索引 a 的下個記錄，并繼續(xù)循環(huán)。

如果想要穩(wěn)定地使用 MRR 優(yōu)化的話，需要設置set optimizer_switch=“mrr_cost_based=off”。

官方文檔的說法，是現(xiàn)在的優(yōu)化器策略，判斷消耗的時候，會更傾向于不使用 MRR，把 mrr_cost_based 設置為 off，就是固定使用 MRR 了。

下面兩幅圖就是使用了 MRR 優(yōu)化后的執(zhí)行流程和 explain 結果:

圖 2 MRR 執(zhí)行流程

圖 3 MRR 執(zhí)行流程的 explain 結果
explain 結果中，我們可以看到 Extra 字段多了 Using MRR，表示的是用上了 MRR 優(yōu)化。

而且，由于在 read_rnd_buffer 中按照 id 做了排序，所以最后得到的結果集也是按照主鍵 id 遞增順序的，也就是與圖 1 結果集中行的順序相反。

MRR 能夠提升性能的核心在于，這條查詢語句在索引 a 上做的是一個范圍查詢（也就是說，這是一個多值查詢），可以得到足夠多的主鍵 id。這樣通過排序以后，再去主鍵索引查數(shù)據(jù)，才能體現(xiàn)出“順序性”的優(yōu)勢。

3.2 Batched Key Access

Batched Key Access(BKA) 算法。這個 BKA 算法，其實就是對 NLJ 算法的優(yōu)化。

NLJ 算法的流程圖：

圖 4 Index Nested-Loop Join 流程圖

NLJ 算法執(zhí)行的邏輯是：
從驅動表 t1，一行行地取出 a 的值，再到被驅動表 t2 去做 join。也就是說，對于表 t2 來說，每次都是匹配一個值。這時，MRR 的優(yōu)勢就用不上了。

既然如此，就把表 t1 的數(shù)據(jù)取出來一部分，先放到一個臨時內存。這個臨時內存不是別人，就是 join_buffer。

join_buffer 在 BNL 算法里的作用，是暫存驅動表的數(shù)據(jù)。但是在 NLJ 算法里并沒有用。那么，剛好就可以復用 join_buffer 到 BKA 算法中。

如圖 5 所示，是上面的 NLJ 算法優(yōu)化后的 BKA 算法的流程。

圖 5 Batched Key Access 流程

圖中，在 join_buffer 中放入的數(shù)據(jù)是 P1-P100，表示的是只會取查詢需要的字段。
當然，如果 join buffer 放不下 P1~P100 的所有數(shù)據(jù)，就會把這 100 行數(shù)據(jù)分成多段執(zhí)行上圖的流程。

• 啟用BKA 算法

需要在執(zhí)行 SQL 語句之前，先設置
set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;
其中，前兩個參數(shù)的作用是要啟用 MRR。這么做的原因是，BKA 算法的優(yōu)化要依賴于 MRR。

3.3 BNL 算法的性能問題

InnoDB 的 LRU 算法的時候提到，由于 InnoDB 對 Bufffer Pool 的 LRU 算法做了優(yōu)化，即：第一次從磁盤讀入內存的數(shù)據(jù)頁，會先放在 old 區(qū)域。
如果 1 秒之后這個數(shù)據(jù)頁不再被訪問了，就不會被移動到 LRU 鏈表頭部，這樣對 Buffer Pool 的命中率影響就不大。

但是，如果一個使用 BNL 算法的 join 語句，多次掃描一個冷表，而且這個語句執(zhí)行時間超過 1 秒，就會在再次掃描冷表的時候，把冷表的數(shù)據(jù)頁移到 LRU 鏈表頭部。

這種情況對應的，是冷表的數(shù)據(jù)量小于整個 Buffer Pool 的 3/8，能夠完全放入 old 區(qū)域的情況。

如果這個冷表很大，就會出現(xiàn)另外一種情況：業(yè)務正常訪問的數(shù)據(jù)頁，沒有機會進入 young 區(qū)域。

由于優(yōu)化機制的存在，一個正常訪問的數(shù)據(jù)頁，要進入 young 區(qū)域，需要隔 1 秒后再次被訪問到。
但是，由于我們的 join 語句在循環(huán)讀磁盤和淘汰內存頁，進入 old 區(qū)域的數(shù)據(jù)頁，很可能在 1 秒之內就被淘汰了。這樣，就會導致這個 MySQL 實例的 Buffer Pool 在這段時間內，young 區(qū)域的數(shù)據(jù)頁沒有被合理地淘汰。

大表 join 操作雖然對 IO 有影響，但是在語句執(zhí)行結束后，對 IO 的影響也就結束了。但是，對 Buffer Pool 的影響就是持續(xù)性的，需要依靠后續(xù)的查詢請求慢慢恢復內存命中率。

為了減少這種影響，可以考慮增大 join_buffer_size 的值，減少對被驅動表的掃描次數(shù)。

BNL 算法對系統(tǒng)的影響主要包括三個方面：

1. 可能會多次掃描被驅動表，占用磁盤 IO 資源；
2. 判斷 join 條件需要執(zhí)行 M*N 次對比（M、N 分別是兩張表的行數(shù)），如果是大表就會占用非常多的 CPU 資源；
3. 可能會導致 Buffer Pool 的熱數(shù)據(jù)被淘汰，影響內存命中率。

執(zhí)行語句之前，需要通過理論分析和查看 explain 結果的方式，確認是否要使用 BNL 算法。
如果確認優(yōu)化器會使用 BNL 算法，就需要做優(yōu)化。優(yōu)化的常見做法是，給被驅動表的 join 字段加上索引，把 BNL 算法轉成 BKA 算法。

這個優(yōu)化怎么做？

3.4 BNL 轉 BKA

一些情況下，可以直接在被驅動表上建索引，這時就可以直接轉成 BKA 算法了。但是，有時候確實會碰到一些不適合在被驅動表上建索引的情況。

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

表 t2 中插入了 100 萬行數(shù)據(jù)，但是經(jīng)過 where 條件過濾后，需要參與 join 的只有 2000 行數(shù)據(jù)。
如果這條語句同時是一個低頻的 SQL 語句，那么再為這個語句在表 t2 的字段 b 上創(chuàng)建一個索引就很浪費了。

如果使用 BNL 算法來 join 的話，這個語句的執(zhí)行流程是這樣的：

1. 把表 t1 的所有字段取出來，存入 join_buffer 中。這個表只有 1000 行，join_buffer_size 默認值是 256k，可以完全存入。
2. 掃描表 t2，取出每一行數(shù)據(jù)跟 join_buffer 中的數(shù)據(jù)進行對比，

• 如果不滿足 t1.b=t2.b，則跳過；
• 如果滿足 t1.b=t2.b, 再判斷其他條件，也就是是否滿足 t2.b 處于[1,2000]的條件，如果是，就作為結果集的一部分返回，否則跳過。

對于表 t2 的每一行，判斷 join 是否滿足的時候，都需要遍歷 join_buffer 中的所有行。因此判斷等值條件的次數(shù)是 1000*100 萬 =10 億次，這個判斷的工作量很大。

圖 6 explain 結果

圖 7 語句執(zhí)行時間

explain 結果里 Extra 字段顯示使用了 BNL 算法。在我的測試環(huán)境里，這條語句需要執(zhí)行 1 分 11 秒。

在表 t2 的字段 b 上創(chuàng)建索引會浪費資源，但是不創(chuàng)建索引的話這個語句的等值條件要判斷 10 億次。有沒有兩全其美的辦法呢？
這時候，可以考慮使用臨時表。使用臨時表的大致思路是：

1. 把表 t2 中滿足條件的數(shù)據(jù)放在臨時表 tmp_t 中；
2. 為了讓 join 使用 BKA 算法，給臨時表 tmp_t 的字段 b 加上索引；
3. 讓表 t1 和 tmp_t 做 join 操作。

此時，對應的 SQL 語句的寫法如下：

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

圖 8 使用臨時表的執(zhí)行效果

整個過程 3 個語句執(zhí)行時間的總和還不到 1 秒，相比于前面的 1 分 11 秒，性能得到了大幅提升。

一起看一下這個過程的消耗：

1. 執(zhí)行 insert 語句構造 temp_t 表并插入數(shù)據(jù)的過程中，對表 t2 做了全表掃描，這里掃描行數(shù)是 100 萬。之后的 join 語句，掃描表 t1，這里的掃描行數(shù)是 1000；
2. join 比較過程中，做了 1000 次帶索引的查詢。相比于優(yōu)化前的 join 語句需要做 10 億次條件判斷來說，這個優(yōu)化效果還是很明顯的。

不論是在原表上加索引，還是用有索引的臨時表，我們的思路都是讓 join 語句能夠用上被驅動表上的索引，來觸發(fā) BKA 算法，提升查詢性能。

3.5 擴展 -hash join

其實上面計算 10 億次那個操作，看上去有點兒傻。如果 join_buffer 里面維護的不是一個無序數(shù)組，而是一個哈希表的話，那么就不是 10 億次判斷，而是 100 萬次 hash 查找。這樣的話，整條語句的執(zhí)行速度就快多了

這也正是 MySQL 的優(yōu)化器和執(zhí)行器一直被詬病的一個原因：不支持哈希 join。

這個優(yōu)化思路，我們可以自己實現(xiàn)在業(yè)務端。實現(xiàn)流程大致如下：

1. select * from t1;取得表 t1 的全部 1000 行數(shù)據(jù)，在業(yè)務端存入一個 hash 結構，比如 C++ 里的 set、PHP 的數(shù)組這樣的數(shù)據(jù)結構。
2. select * from t2 where b>=1 and b<=2000; 獲取表 t2 中滿足條件的 2000 行數(shù)據(jù)。
3. 把這 2000 行數(shù)據(jù)，一行一行地取到業(yè)務端，到 hash 結構的數(shù)據(jù)表中尋找匹配的數(shù)據(jù)。滿足匹配的條件的這行數(shù)據(jù)，就作為結果集的一行。

理論上，這個過程會比臨時表方案的執(zhí)行速度還要快一些。

3.6 join寫法

1. 如果用 left join 的話，左邊的表一定是驅動表嗎？
2. 如果兩個表的 join 包含多個條件的等值匹配，是都要寫到 on 里面呢，還是只把一個條件寫到 on 里面，其他條件寫到 where 部分？

構造兩個表 a 和 b：

create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

表 a 和 b 都有兩個字段 f1 和 f2，不同的是表 a 的字段 f1 上有索引。然后，往兩個表中都插入了 6 條記錄，其中在表 a 和 b 中同時存在的數(shù)據(jù)有 4 行。

第二個問題，其實就是下面這兩種寫法的區(qū)別：

select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/

把這兩條語句分別記為 Q1 和 Q2。
這兩個 left join 語句的語義邏輯并不相同。來看一下它們的執(zhí)行結果。

圖 1 兩個 join 的查詢結果
可以看到：

• 語句 Q1 返回的數(shù)據(jù)集是 6 行，表 a 中即使沒有滿足匹配條件的記錄，查詢結果中也會返回一行，并將表 b 的各個字段值填成 NULL。
• 語句 Q2 返回的是 4 行。從邏輯上可以這么理解，最后的兩行，由于表 b 中沒有匹配的字段，結果集里面 b.f2 的值是空，不滿足 where 部分的條件判斷，因此不能作為結果集的一部分。

看看實際執(zhí)行這兩條語句時，MySQL 是怎么做的。

圖 2 Q1 的 explain 結果

可以看到，這個結果符合我們的預期：

• 驅動表是表 a，被驅動表是表 b；
• 由于表 b 的 f1 字段上沒有索引，所以使用的是 Block Nested Loop Join（簡稱 BNL） 算法。

這條語句的執(zhí)行流程其實是這樣的：

1. 把表 a 的內容讀入 join_buffer 中。因為是 select * ，所以字段 f1 和 f2 都被放入 join_buffer 了。
2. 順序掃描表 b，對于每一行數(shù)據(jù)，判斷 join 條件（也就是 (a.f1=b.f1) and (a.f1=1)）是否滿足，滿足條件的記錄, 作為結果集的一行返回。如果語句中有 where 子句，需要先判斷 where 部分滿足條件后，再返回。
3. 表 b 掃描完成后，對于沒有被匹配的表 a 的行（在這個例子中就是 (1,1)、(2,2) 這兩行），把剩余字段補上 NULL，再放入結果集中。

圖 3 left join -BNL 算法

可以看到，這條語句確實是以表 a 為驅動表，而且從執(zhí)行效果看，也和使用 straight_join 是一樣的。

語句 Q2 的 expain 結果

圖 4 Q2 的 explain 結果

這條語句是以表 b 為驅動表的。而如果一條 join 語句的 Extra 字段什么都沒寫的話，就表示使用的是 Index Nested-Loop Join（簡稱 NLJ）算法。

語句 Q2 的執(zhí)行流程是這樣的：
順序掃描表 b，每一行用 b.f1 到表 a 中去查，匹配到記錄后判斷 a.f2=b.f2 是否滿足，滿足條件的話就作為結果集的一部分返回。

為什么語句 Q1 和 Q2 這兩個查詢的執(zhí)行流程會差距這么大呢？

其實，這是因為優(yōu)化器基于 Q2 這個查詢的語義做了優(yōu)化。

在 MySQL 里，NULL 跟任何值執(zhí)行等值判斷和不等值判斷的結果，都是 NULL。這里包括， select NULL = NULL 的結果，也是返回 NULL。

因此，語句 Q2 里面 where a.f2=b.f2 就表示，查詢結果里面不會包含 b.f2 是 NULL 的行，這樣這個 left join 的語義就是“找到這兩個表里面，f1、f2 對應相同的行。對于表 a 中存在，而表 b 中匹配不到的行，就放棄”。這樣，這條語句雖然用的是 left join，但是語義跟 join 是一致的。

因此，優(yōu)化器就把這條語句的 left join 改寫成了 join，然后因為表 a 的 f1 上有索引，就把表 b 作為驅動表，這樣就可以用上 NLJ 算法。在執(zhí)行 explain 之后，你再執(zhí)行 show warnings，就能看到這個改寫的結果：
圖 5 Q2 的改寫結果

這個例子說明，即使我們在 SQL 語句中寫成 left join，執(zhí)行過程還是有可能不是從左到右連接的。也就是說，使用 left join 時，左邊的表不一定是驅動表。

如果需要 left join 的語義，就不能把被驅動表的字段放在 where 條件里面做等值判斷或不等值判斷，必須都寫在 on 里面。

再看看這兩條語句：

select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/
select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/

可以看到，這兩條語句都被改寫成：

select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

執(zhí)行計劃自然也是一模一樣的。

也就是說，在這種情況下，join 將判斷條件是否全部放在 on 部分就沒有區(qū)別了。

小結
這些優(yōu)化方法中：
4. BKA 優(yōu)化是 MySQL 已經(jīng)內置支持的，建議你默認使用；
5. BNL 算法效率低，建議你都盡量轉成 BKA 算法。優(yōu)化的方向就是給被驅動表的關聯(lián)字段加上索引；基于臨時表的改進方案，對于能夠提前過濾出小數(shù)據(jù)的 join 語句來說，效果還是很好的；MySQL 目前的版本還不支持 hash join，但你可以配合應用端自己模擬出來，理論上效果要好于臨時表的方案。

思考
在講 join 語句的這兩篇文章中，都只涉及到了兩個表的 join。那么，現(xiàn)在有一個三個表 join 的需求，假設這三個表的表結構如下：

CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

create table t2 like t1;
create table t3 like t2;
insert into ... //初始化三張表的數(shù)據(jù)

語句的需求實現(xiàn)如下的 join 邏輯：

select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;

為了得到最快的執(zhí)行速度，如果讓你來設計表 t1、t2、t3 上的索引，來支持這個 join 語句，你會加哪些索引呢？
同時，如果我希望你用 straight_join 來重寫這個語句，配合你創(chuàng)建的索引，你就需要安排連接順序，你主要考慮的因素是什么呢？

第一原則是要盡量使用 BKA 算法。
需要注意的是，使用 BKA 算法的時候，并不是“先計算兩個表 join 的結果，再跟第三個表 join”，而是直接嵌套查詢的。
具體實現(xiàn)是：在 t1.c>=X、t2.c>=Y、t3.c>=Z 這三個條件里，選擇一個經(jīng)過過濾以后，數(shù)據(jù)最少的那個表，作為第一個驅動表。
此時，可能會出現(xiàn)如下兩種情況。
第一種情況，如果選出來是表 t1 或者 t3，那剩下的部分就固定了。

1. 如果驅動表是 t1，則連接順序是 t1->t2->t3，要在被驅動表字段創(chuàng)建上索引，也就是 t2.a 和 t3.b 上創(chuàng)建索引；
2. 如果驅動表是 t3，則連接順序是 t3->t2->t1，需要在 t2.b 和 t1.a 上創(chuàng)建索引。

同時，還需要在第一個驅動表的字段 c 上創(chuàng)建索引。
第二種情況是，如果選出來的第一個驅動表是表 t2 的話，則需要評估另外兩個條件的過濾效果。

整體的思路就是，盡量讓每一次參與 join 的驅動表的數(shù)據(jù)集，越小越好，因為這樣我們的驅動表就會越小。

來自林曉斌《Mysql實戰(zhàn)45講》文章來源地址http://www.zghlxwxcb.cn/news/detail-741253.html

到了這里，關于【MySql】11- 實踐篇(九)的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！