在 MySQL 的執行計劃中，Using join buffer (Block Nested Loop) 表示在執行嵌套循環連接（Nested Loop Join）時，MySQL 使用了一種稱為塊嵌套循環（Block Nested Loop）的優化策略，這種策略會用到連接緩沖區（join buffer）。

具體解釋

1. 驅動表和被驅動表

   • 驅動表：在嵌套循環中，首先處理的表。
   • 被驅動表：驅動表中的每一行都會和它進行匹配查詢的表。

2. 為什么需要 Join Buffer？
   當 MySQL 執行兩表連接時，如果被驅動表沒有合適的索引（例如 ON 條件中未能使用索引），MySQL 無法直接通過索引快速找到匹配的記錄。這種情況下，需要對被驅動表做全表掃描。

   為了減少與被驅動表之間的交互次數，MySQL 會將驅動表的部分數據加載到連接緩沖區（join buffer）中，然后對被驅動表逐行掃描，嘗試匹配連接條件。

3. Block Nested Loop 的工作方式
   塊嵌套循環優化了傳統的嵌套循環算法，通過減少驅動表與被驅動表交互的次數提高性能。執行過程如下：

   • Step 1：讀取驅動表的一部分數據（多個數據塊）放入 join buffer。
   • Step 2：對被驅動表進行全表掃描，將被驅動表中的每一行與 join buffer 中的數據進行比較，匹配連接條件。
   • Step 3：匹配的結果保留，不匹配的丟棄。
   • Step 4：如果驅動表尚未讀取完畢，則繼續讀取下一部分數據，重復上述步驟。

4. 性能影響

   • 如果驅動表的數據量較大，join buffer 可能會頻繁寫入和讀取，從而造成性能瓶頸。
   • 如果被驅動表的數據量較大（并且沒有索引），需要頻繁掃描，也會導致較大的 I/O 開銷。

5. 改進方法

   • 給被驅動表加索引：索引能夠顯著提高連接查詢的效率，避免全表掃描。
   • 優化 SQL 語句的執行計劃：通過調整表的連接順序，讓更小的數據表作為驅動表。
   • 增加 join_buffer_size：如果無法加索引，可以通過增大 join_buffer_size（默認為 262144 字節，256KB），減少緩沖區寫入和讀取的次數。

示例

假設有兩個表：A 和 B，需要執行以下查詢：

SELECT * 
FROM A 
JOIN B 
ON A.col1 = B.col2;

情況 1：B.col2 上有索引

MySQL 會直接使用索引查找匹配的 B 表記錄，執行效率高。

情況 2：B.col2 上沒有索引

• MySQL 將讀取 A 表的部分數據塊放入 join buffer。
• 然后對 B 表逐行掃描，嘗試匹配 join buffer 中的數據。
• 執行計劃中會顯示：Using join buffer (Block Nested Loop)。

Using join buffer (Block Nested Loop)** 是一種優化策略，用于在連接查詢時處理缺乏索引的被驅動表。盡管它優化了傳統的嵌套循環，但性能仍然不及直接使用索引。優化這類查詢的最佳方法是為連接條件字段建立合適的索引。