本文詳細介紹了 MySQL 參數 join_buffer_size 在 INNER JOIN 場景的使用，OUTER JOIN 不包含。在討論這個 BUFFER 之前，我們先了解下 MySQL 的 INNER JOIN 分類。

如果按照檢索的性能方式來細分，那么無論是兩表 INNER JOIN 還是多表 INNER JOIN，都大致可以分為以下幾類：

1. JOIN KEY 有索引，主鍵2. JOIN KEY 有索引，二級索引3. JOIN KEY 無索引

今天主要針對第三種場景來分析，也是就全表掃的場景。

回過頭來看看什么是 join_buffer_size？

JOIN BUFFER 是 MySQL 用來緩存以上第二、第三這兩類 JOIN 檢索的一個 BUFFER 內存區域塊。一般建議設置一個很小的 GLOBAL 值，完了在 SESSION 或者 QUERY 的基礎上來做一個合適的調整。

比如，默認的值為 512K， 想要臨時調整為 1G。那么，mysql>set?session?join_buffer_size?=?1024?*?1024?*?1024;mysql>select?*?from?...;mysql>set?session?join_buffer_size=default;或者mysql>select?/*+??set_var(join_buffer_size=1G)?*/?*?from?...;

接下來詳細看下 JOIN BUFFER 的用法。

那么 MySQL 里針對 INNER JOIN 大致有以下幾種算法，

1. Nested-Loop Join 翻譯過來就是嵌套循環連接，簡稱 NLJ。

這種是 MySQL 里最簡單、最容易理解的表關聯算法。

比如，拿語句select * from p1 join p2 using(r1)來說，

先從表 p1 里拿出來一條記錄 ROW1，完了再用 ROW1 遍歷表 p2 里的每一條記錄，并且字段 r1 來做匹配是否相同，以便輸出；再次循環剛才的過程，直到兩表的記錄數對比完成為止。

那看下實際 SQL 的執行計劃，mysql>?explain?format=json?select?*?from?p1?inner?join?p2?as?b?using(r1)\G***************************?1.?row?***************************EXPLAIN:?{?"query_block":?{???"select_id":?1,???"cost_info":?{?????"query_cost":?"1003179606.87"???},???"nested_loop":?[?????{???????"table":?{?????????"table_name":?"b",?????????"access_type":?"ALL",?????????"rows_examined_per_scan":?1000,?????????"rows_produced_per_join":?1000,?????????"filtered":?"100.00",?????????"cost_info":?{???????????"read_cost":?"1.00",???????????"eval_cost":?"100.00",???????????"prefix_cost":?"101.00",???????????"data_read_per_join":?"15K"?????????},?????????"used_columns":?[???????????"id",???????????"r1",???????????"r2"?????????]???????}?????},?????{???????"table":?{?????????"table_name":?"p1",?????????"access_type":?"ALL",?????????"rows_examined_per_scan":?9979810,?????????"rows_produced_per_join":?997981014,?????????"filtered":?"10.00",?????????"cost_info":?{???????????"read_cost":?"5198505.87",???????????"eval_cost":?"99798101.49",???????????"prefix_cost":?"1003179606.87",???????????"data_read_per_join":?"14G"?????????},?????????"used_columns":?[???????????"id",???????????"r1",???????????"r2"?????????],?????????"attached_condition":?"(`ytt_new`.`p1`.`r1`?=?`ytt_new`.`b`.`r1`)"???????}?????}???]?}}1?row?in?set,?1?warning?(0.00?sec)

從上面的執行計劃來看，表 p2 為第一張表(驅動表或者叫外表)，第二張表為 p1，那 p2 需要遍歷的記錄數為 1000，同時 p1 需要遍歷的記錄數大概 1000W 條，那這條 SQL 要執行完成，就得對表 p1(內表)匹配 1000 次，對應的 read_cost 為 5198505.87。那如何才能減少表 p1 的匹配次數呢？那這個時候 JOIN BUFFER 就派上用處了

2. Block Nested-Loop Join ，塊嵌套循環，簡稱 BNLJ

那 BNLJ 比 NLJ 來說，中間多了一塊 BUFFER 來緩存外表的對應記錄從而減少了外表的循環次數，也就減少了內表的匹配次數。還是那上面的例子來說，假設 join_buffer_size 剛好能容納外表的對應 JOIN KEY 記錄，那對表 p2 匹配次數就由 1000 次減少到 1 次，性能直接提升了 1000 倍。我們看下用到 BNLJ 的執行計劃，mysql>?explain?format=json?select?*?from?p1?inner?join?p2?as?b?using(r1)\G***************************?1.?row?***************************EXPLAIN:?{?"query_block":?{???"select_id":?1,???"cost_info":?{?????"query_cost":?"997986300.01"???},???"nested_loop":?[?????{???????"table":?{?????????"table_name":?"b",?????????"access_type":?"ALL",?????????"rows_examined_per_scan":?1000,?????????"rows_produced_per_join":?1000,?????????"filtered":?"100.00",?????????"cost_info":?{???????????"read_cost":?"1.00",???????????"eval_cost":?"100.00",???????????"prefix_cost":?"101.00",???????????"data_read_per_join":?"15K"?????????},?????????"used_columns":?[???????????"id",???????????"r1",???????????"r2"?????????]???????}?????},?????{???????"table":?{?????????"table_name":?"p1",?????????"access_type":?"ALL",?????????"rows_examined_per_scan":?9979810,?????????"rows_produced_per_join":?997981014,?????????"filtered":?"10.00",?????????"using_join_buffer":?"Block?Nested?Loop",?????????"cost_info":?{???????????"read_cost":?"5199.01",???????????"eval_cost":?"99798101.49",???????????"prefix_cost":?"997986300.01",???????????"data_read_per_join":?"14G"?????????},?????????"used_columns":?[???????????"id",???????????"r1",???????????"r2"?????????],?????????"attached_condition":?"(`ytt_new`.`p1`.`r1`?=?`ytt_new`.`b`.`r1`)"???????}?????}???]?}}1?row?in?set,?1?warning?(0.00?sec)

上面的執行計劃有兩點信息，第一：多了一條 "using_join_buffer": "Block Nested Loop"第二：read_cost 這塊由之前的 5198505.87 減少到 5199.01

3. 最近 MySQL 8.0.18 發布，終于推出了新的 JOIN 算法 — HASH JOIN。

MySQL 的 HASH JOIN 也是用了 JOIN BUFFER 來做緩存，但是和 BNLJ 不同的是，它在 JOIN BUFFER 中以外表為基礎建立一張哈希表，內表通過哈希算法來跟哈希表進行匹配，hash join 也就是進一步減少內表的匹配次數。當然官方并沒有說明詳細的算法描述，以上僅代表個人臆想。那還是針對以上的 SQL，我們來看下執行計劃。mysql> explain format=tree select * from p1 inner join p2 as b using(r1)\G

*************************** 1. row ***************************

EXPLAIN: -> Inner hash join (p1.r1 = b.r1) ?(cost=997986300.01 rows=997981015)

-> Table scan on p1 ?(cost=105.00 rows=9979810)

-> Hash

-> Table scan on b ?(cost=101.00 rows=1000)

1 row in set (0.00 sec)

通過上面的執行計劃看到，針對表 p2 建立一張哈希表，然后針對表 p1 來做哈希匹配。目前僅僅支持簡單查看是否用了 HASH JOIN，而沒有其他更多的信息展示。總結下，本文主要討論 MySQL 的內表關聯在沒有任何索引的低效場景。其他的場景另外開篇。

總結

以上是生活随笔為你收集整理的mysql内连接简写_技术分享 | MySQL 的 join_buffer_size 在内连接上的应用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。