NLJ 效果不錯，BNL 大表 join 性能差，消耗 CPU 資源。都可繼續(xù)優(yōu)化
?t1 1000 行， a=1001-id 的值。a逆序。 t2 插入了 100 萬行數(shù)據(jù)。
一、Multi-Range Read 優(yōu)化
Read 優(yōu)化 (MRR)，順序讀盤。
第 4 篇文章“回表”普通索引 a 上查到主鍵 id ，根據(jù)主鍵 id 去查整行數(shù)據(jù)的過程。
select * from t1? where a>=1 and a<=100;
主鍵索引 B+ 樹，每次只能根據(jù)一個主鍵 id 查一行。一行行搜主鍵索引
a 遞增，id 變隨機，隨機訪問，性能差。雖按行查不能改，調(diào)整順序加速。
MRR思路：按主鍵遞增，磁盤讀接近順序讀，提升讀性能。
1.? 索引 a，定位滿足條件記錄，id 值放read_rnd_buffer 中;
2.? read_rnd_buffer 中 id 進行遞增排序；
3.? ?id 查記錄返回
read_rnd_buffer_size 控制大小。read_rnd_buffer放滿，清空繼續(xù)循環(huán)。
穩(wěn)定用 MRR 優(yōu)化設(shè)置setoptimizer_switch="mrr_cost_based=off"。（優(yōu)化器策略，判斷消耗時，傾向不使用 MRR，就是固定用 MRR ）
?Extra 多 Using MRR。read_rnd_buffer 按id 排序，結(jié)果集中行順序相反。
提升性能核心“順序性”：a 上查 id。排序，id查數(shù)據(jù)，
二、Batched Key Access
NLJ 的優(yōu)化（放內(nèi)存）
表 t2 每次都匹配一個值。MRR 用不上。
t1 多拿行一起傳，先放臨時內(nèi)存join_buffer。暫存驅(qū)動表數(shù)據(jù)
放入 P1~P100，只會取查詢需要字段。如放不下所有，分多段
啟用先設(shè)置set optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';? 啟用 MRR。BKA 依賴于 MRR。
三、BNL 算法性能問題
被驅(qū)動表做多次掃描。大冷數(shù)據(jù)表，除導(dǎo)致 IO 壓力大外，還有什么影響？
LRU 優(yōu)化， old 區(qū)域。對 Buffer Pool 的命中率影響就不大。
冷表很大，出現(xiàn)另外一種情況：業(yè)務(wù)正常訪問數(shù)據(jù)頁，沒有機會進入 young 區(qū)域（沒有被合理淘汰）。
兩種情況都影響 Buffer Pool 正常運作。
大表 join 操作雖然對 IO 有影響，語句執(zhí)行結(jié)束后，對 IO 的影響結(jié)束。Buffer Pool 影響持續(xù)性，后續(xù)的查詢慢慢恢復(fù)內(nèi)存命中率。
增大join_buffer_size 的值，減少被驅(qū)動表掃描次數(shù)。BNL 影響：
1.? 占用磁盤 IO 資源；
2.? 判斷 join 條件需M*N 次對比（M、N 分別是兩張表的行數(shù)），占用 CPU 資源；
3.? Buffer Pool 熱數(shù)據(jù)淘汰，影響內(nèi)存命中率。
優(yōu)化：被驅(qū)動表 join 字段加索引，BNL 轉(zhuǎn)BKA 算法。
四、BNL 轉(zhuǎn)BKA
被驅(qū)動表建索引，直接轉(zhuǎn)BKA ；不適合在被驅(qū)動表上建索引如：
select * from t1? join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000
t2 插 100 萬，where后只有 2000 。同時低頻的 SQL ，建索引就浪費。
1.? t1 所有字段取出來，存入 join_buffer 。1000行 完全存入 join_buffer_size（默認 256k）
2.? 掃描t2，跟 join_buffer 對比，不滿足 t1.b=t2.b跳過； [1,2000] 返回，否則跳過。
對于表 t2 每行，判斷 join 是否滿足的時候，遍歷 join_buffer 所有行。判斷等值條件的次數(shù)是 1000*100 萬 =10 億次，工作量大。
Extra 顯示用 BNL 算法。執(zhí)行 1 分 11 秒。
?b 上創(chuàng)建索引浪費資源，不創(chuàng)建判斷 10 億次，浪費。兩全其美辦法：臨時表：
1.? t2 滿足條件數(shù)據(jù)放臨時表 tmp_t 中；
2.? 讓 join 用 BKA 算法，給臨時表 tmp_t 字段 b 加索引；
3.? t1 和 tmp_t 做 join 操作。
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;//100 萬
select * from t1? join temp_t on (t1.b=temp_t.b); //1000次索引查詢
原表上加索引，還是索引臨時表，join 用被驅(qū)動表上索引，觸發(fā) BKA 算法，提升性能。
五、擴展-hash join
上面計算 10 億次點兒傻。如 join_buffer 不是無序數(shù)組，是哈希表話，不是 10 億判斷，而是 100 萬次 hash 查找?？於嗔耍?b>比臨時表快）
MySQL 優(yōu)化器和執(zhí)行器詬?。翰恢С止?join。優(yōu)化思路自己實現(xiàn)：
1. select * from t1; t1 全部 1000 行數(shù)據(jù)，存 hash 結(jié)構(gòu)，如 C++ 里的 set、PHP 的數(shù)組
2.? select * from t2 where b>=1 and b<=2000; t2 中滿足條件2000 行
3.? 2000 行數(shù)據(jù)，一行行取到業(yè)務(wù)端hash 結(jié)構(gòu)數(shù)據(jù)表中尋找匹配數(shù)據(jù)。作為結(jié)果集
小結(jié)
Index Nested-Loop Join（NLJ）和 Block Nested-Loop Join（BNL）的優(yōu)化方法。
1.? BKA 優(yōu)化是 MySQL 已經(jīng)內(nèi)置支持，默認用；
2.? BNL 效率低，建議你都盡量轉(zhuǎn)成 BKA 算法。給被驅(qū)動表關(guān)聯(lián)字段加上索引；
3.? 臨時表改進方案，提前過濾出小數(shù)據(jù) join ，效果好；
4.? MySQL 不支持 hash join，自己模擬，好于臨時表。
思考題
三個表 join 需求：
改寫成straight_join，怎么指定連接順序，怎么給三個表創(chuàng)建索引。
第一原則： BKA 算法，直接嵌套查詢。不是“先計算兩個表 join 結(jié)果，再跟第三個join”，
實現(xiàn)：t1.c>=X、t2.c>=Y、t3.c>=Z 三個條件，數(shù)據(jù)最少第一個驅(qū)動表。
第一種情況
選出表 t1 或者 t3，剩下部分就固定。
1. 驅(qū)動表t1，連接順序t1->t2->t3，被驅(qū)動表（t2.a 和 t3.b?）字段建索引，?
2.? 驅(qū)動表t3，連接順序t3->t2->t1，t2.b 和 t1.a 上創(chuàng)建索引。
驅(qū)動表 c 創(chuàng)建索引。
第二種情況
第一個驅(qū)動表t2，評估另外兩個條件過濾效果。
評論1
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;
快速定位t1、t2和t3的c字段建索引。join優(yōu)先選擇數(shù)量比較少兩張表，t1.a=t2.a的行數(shù)小于滿足t2.b=t3.b的行數(shù)，t1和t2join，行數(shù)少驅(qū)動表，被驅(qū)動表a上建立索引，結(jié)果放入join_buffer與t3進行join，被驅(qū)動表的t3的b上建索引。t1.c>=X ，t2.c>=Y ，t3.c>=Z
為什么不用一個索引，要分別建3個?
評論2
我還有個問題，開篇sql select * from t1 where a>=1 and a<=100;
a是索引列，有order by a，不使用MRR 優(yōu)化，查詢出來就是按a排序的，使用了mrr優(yōu)化，是不是要額外排序？
答：用order by就不用MRR
ps： 固態(tài)硬盤順序?qū)?/b>比隨機寫快
