join buffer
Join Buffer 可被用于聯(lián)接是ALL撩独、index、和range的類型;每次聯(lián)接使用一個(gè)Join Buffer账月，因此多表的聯(lián)接可以使用多個(gè)Join Buffer综膀；Join Buffer在聯(lián)接發(fā)生之前進(jìn)行分配，在SQL語句執(zhí)行完后進(jìn)行釋放局齿；Join Buffer只存儲(chǔ)要進(jìn)行查詢操作的相關(guān)列數(shù)據(jù)剧劝，而不是整行的記錄。
1抓歼、Using join buffer (Block Nested Loop) mysql5.7
join buffer 使用在 BNLJ中
我們可以增加join buffer的值來優(yōu)化join查詢讥此，因?yàn)樵黾拥膉oin buffer可以容納下更多的外表join字段記錄。這樣就可以批讓內(nèi)部表批量的匹配更多個(gè)外部表字段谣妻，相對(duì)來說就減少了匹配次數(shù)萄喳，提高了join查詢效率。
2蹋半、Using join buffer (hash join) mysql 8.0
join buffer使用在 HASH JOIN中他巨，用作緩存外表的關(guān)聯(lián)字段hash值
調(diào)整Join_buffer_size變量
1、配置默認(rèn)值
推薦設(shè)置：16M
系統(tǒng)默認(rèn)大小為：512k减江，mac下默認(rèn)大小為：256k染突，針對(duì)128GB，1萬并發(fā)的mysql我推薦給到的值為：8~16M辈灼。對(duì)于JOIN KEY 有索引和二級(jí)索引份企，JOIN KEY 無索引mysql會(huì)使用到j(luò)oin_buffer_size，一般建議設(shè)置一個(gè)很小的 GLOBAL 值巡莹，完了在 SESSION 或者 QUERY 的基礎(chǔ)上來做一個(gè)合適的調(diào)整司志。適當(dāng)?shù)娜ジ淖兯_實(shí)可以帶來一定的提速，但并不是說很多值越大越好降宅，為什么我們?cè)O(shè)置成4m呢俐芯？我們假設(shè)我們的mysql所在的vm是128gb，一根這樣的join（如果被用到）是4M钉鸯，1萬個(gè)也不過用掉40G,而根據(jù)官方說法吧史，total加在一起產(chǎn)生的join_buffer_size不要超過你所在系統(tǒng)的50%.默認(rèn)512k肯定是小了點(diǎn)，我們可以適當(dāng)放寬，比如說：2M贸营，在實(shí)際使用場(chǎng)景時(shí)我們發(fā)覺有這樣的高頻操作（要看高頻出現(xiàn)的有意義的sql的執(zhí)行計(jì)劃吨述，并確認(rèn)該計(jì)劃的：執(zhí)行cost如："query_cost": "1003179606.87"，它產(chǎn)生的cost為：0.93個(gè)G,如果它真的很高頻出現(xiàn)在調(diào)優(yōu)sql到無法調(diào)優(yōu)的程度钞脂，我們會(huì)去做set session join_buffer_size = 1024 * 1024 * 1024;這樣的操作揣云。而不是在一開始的my.cnf中去分配一個(gè)暴大的值，我們這邊基于128gb冰啃，1萬connection的并發(fā)來說邓夕，你給個(gè)16M不算小也不算多，我推薦給到8~16M間（這是指在一開始）阎毅。
如果不配的后果：默認(rèn)的為256k
配置實(shí)例：
[mysqld]
join_buffer_size = 16M
2焚刚、配置語句或會(huì)話級(jí)別的值
變量join_buffer_size用來控制Join Buffer的大小，調(diào)大后可以避免多次的內(nèi)表掃描扇调，從而提高性能矿咕。也就是說，當(dāng)MySQL的Join有使用到Block Nested-Loop Join狼钮，那么調(diào)大變量join_buffer_size才是有意義的碳柱。而前面的Index Nested-Loop Join如果僅使用索引進(jìn)行Join，那么調(diào)大這個(gè)變量則毫無意義熬芜。變量join_buffer_size的默認(rèn)值是256K莲镣，顯然對(duì)于稍復(fù)雜的SQL是不夠用的。好在這個(gè)是會(huì)話級(jí)別的變量涎拉，可以在執(zhí)行前進(jìn)行擴(kuò)展剥悟。建議在會(huì)話級(jí)別進(jìn)行設(shè)置，而不是全局設(shè)置曼库，因?yàn)楹茈y給一個(gè)通用值去衡量区岗。另外，這個(gè)內(nèi)存是會(huì)話級(jí)別分配的毁枯，如果設(shè)置不好容易導(dǎo)致因無法分配內(nèi)存而導(dǎo)致的宕機(jī)問題慈缔。
一般建議設(shè)置一個(gè)很小的 GLOBAL 值，完了在 SESSION 或者 QUERY 的基礎(chǔ)上來做一個(gè)合適的調(diào)整种玛。比如 默認(rèn)的值為 512K藐鹤， 想要臨時(shí)調(diào)整為 1G應(yīng)該如下操作：
set session join_buffer_size = 1024 * 1024 * 1024;
select * from ...;
set session join_buffer_size=default;
或者
mysql>select /*+ set_var(join_buffer_size=1G) */ * from ...;
SHOW VARIABLES LIKE '%join_buffer_size%' --默認(rèn)262144(0.25M)
關(guān)于調(diào)整Join_buffer_size的測(cè)試
通過我的測(cè)試join_buffer_size參數(shù)對(duì)hash join查詢性能的幫助比較少。一幫默認(rèn)的0.25M就夠用了赂韵。我的實(shí)驗(yàn)中user表640000條數(shù)據(jù)娱节，book 762474條數(shù)據(jù)。進(jìn)行l(wèi)eft join 查詢：
EXPLAIN SELECT * FROM `user` a LEFT JOIN book b IGNORE index(index_user_id) ON a.id=b.user_id;
user.id 是bigint類型的祭示，那就是640000*8 字節(jié) 4.8MB超過了0.25MB 肄满。實(shí)驗(yàn)結(jié)果是如下，每次查詢都是重啟數(shù)據(jù)庫的，防止緩存影響稠歉。
join_buffer_size = 1 字節(jié)掰担，13秒
join_buffer_size = 0.25M 默認(rèn) 6.3秒
join_buffer_size = 1G 5.7秒
join_buffer_size設(shè)置過小會(huì)讓hash join變慢很多，但是設(shè)置過大也沒多大性能提升怒炸！同時(shí)我還做了使用索引的INLJ 關(guān)聯(lián)查詢對(duì)比带饱，第一次查詢竟然耗費(fèi)了33秒的時(shí)間，沒重啟mysql查詢第二次變成了8秒阅羹∩滋郏看來這種時(shí)候HASH JOIN的性能要優(yōu)于通過索引的BNLJ，mysql8還是相當(dāng)給力的捏鱼！
Join Buffer緩存對(duì)象有哪些执庐？
join buffer 緩存外表的在sql查詢中設(shè)計(jì)的字段
另外，Join Buffer緩存的對(duì)象是什么穷躁，這個(gè)問題相當(dāng)關(guān)鍵和重要耕肩。然在MySQL的官方手冊(cè)中是這樣記錄的：Only columns of interest to the join are stored in the join buffer, not whole rows.
可以發(fā)現(xiàn)Join Buffer不是緩存外表的整行記錄因妇，而是緩存“columns of interest”问潭，具體指所有參與查詢的列都會(huì)保存到Join Buffer，而不是只有Join的列婚被。比如下面的SQL語句狡忙，假設(shè)沒有索引，需要使用到Join Buffer進(jìn)行鏈接：
SELECT a.col3
FROM a,
b
WHERE a.col1 = b.col2
AND a.col2 > ….
AND b.col2 = …
假設(shè)上述SQL語句的外表是a址芯，內(nèi)表是b灾茁，那么存放在Join Buffer中的列是所有參與查詢的列，在這里就是（a.col1谷炸，a.col2北专，a.col3）。
通過上面的介紹旬陡，我們現(xiàn)在可以得到內(nèi)表的掃描次數(shù)為：Scaninner_table = (RN * used_column_size) / join_buffer_size + 1
對(duì)于有經(jīng)驗(yàn)的DBA就可以預(yù)估需要分配的Join Buffer大小拓颓，然后盡量使得內(nèi)表的掃描次數(shù)盡可能的少，最優(yōu)的情況是只掃描內(nèi)表一次描孟。
