目錄
- 要不要用join
- Join驅動表選擇
- Multi-Range Read優化
- Batched Key Access （BKA）對NLJ進行優化
- BNL算法性能問題
- BNL轉BKA
要不要用join
1、如果使用的是Index Nested-Loop Join算法，即可以用上被驅動表的索引，可以用
2、如果使用的是Block Nested-Loop Join算法。掃描行數過多，尤其是大表join會導致掃描多次被驅動表，會占用大量系統資源，這種Join盡量不要用
Join驅動表選擇
1、如果是Index Nested-Loop Join算法，使用小表做驅動表
2、如果是Block Nested-Loop Join算法，在 join_buffer_size 足夠大，大表小表一樣，當 join_buffer_size 不夠大時，選擇小表做驅動表
注意，在決定哪個表做驅動表時，應該是兩個表按照各自條件過濾完成之后，計算參與join的各個字段的總數據量，數據量小的表，那就是小表。
Multi-Range Read優化
若有這樣查詢語句：
select * from t1 where a>=1 and a<=100;
a值是遞增的，但是回表后的id并非如此，而是隨機的，會帶來性能損失。
大多數數據按照主鍵遞增順序插入得到，所以我們可以認為如果按照主鍵的遞增順序查找的話，對磁盤的讀比較接近順序讀，從而可以提升讀性能。
1、根據索引a，定位到滿足條件的記錄，將id值放入read_rnd_buffer中；
2、將read_rnd_buffer中的id進行遞增排序；
3、排序后的id數組，依次到主鍵id索引中查找記錄，并作為結果返回
總的來說就是：**先將索引數據緩存，查到id之后，排序之后再回表 **
用法：
設置：
set optimizer_switch="mrr_cost_based=off
現在的優化器在判斷消耗時，更傾向于不使用MRR，所以需要設置為off后，就會固定使用MRR
Batched Key Access （BKA）對NLJ進行優化
Index Nested-Loop Join執行邏輯是：從驅動表t1，一行行取出a值，再到驅動表t2去做join。對于表t2來說，每次都是匹配一個值，MMR優勢用不上。
既然這樣，將表t1的數據取出來一部分，先放到一個臨時內存里：join_buffer.
然后在此基礎上復用MRR即可。
使用方法：
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
BNL算法性能問題
之前提到過InnoDB的LRU優化：第一次從磁盤讀入內存的數據頁，會先放到old區域，如果1s后這個數據頁不再被訪問，就不會移動到LRU鏈表頭部，這樣對Buffer Pool命中率影響就不大了。
如果使用了BNL的join語句，多次掃描一個冷表，并且這個語句執行時間超過1s，就會在再次掃描冷表時，把冷表的數據頁移動到LRU鏈表頭部。
如果冷表數據很大， 會一直占據old區，正常頁無法進入，無法更新young區
tips: 冷表，指表中數據還沒有加載到bufferpool中，需要先從盤里讀出來的表
又因為優化機制，一個正常訪問的數據頁要進入young區域，需要隔1s再次被訪問到。由于join’語句在循環都磁盤和淘汰內存頁，進入old區域的數據頁很可能在1s之內就被淘汰了。
大表join后對于Buffer Pool的影響是持續性的，需要依靠后續的查詢請求慢慢恢復內存命中率。
總結，BNL對于系統的影響：
1、可能多次掃描被驅動表，占用磁盤IO資源
2、判斷join條件執行M * N次，占用CPU資源
3、可能導致Buffer Pool的熱數據被淘汰，影響內存命中率
所以我們需要優化BNL，通過給驅動表的join字段加索引的方式，將BNL轉換為BKA
BNL轉BKA
對于一些不常執行大表join的sql，不在被驅動表上創建索引的情況，可以創建一個臨時表 create templete table在這個臨時表上創建索引，然后讓驅動表與臨時表做join操作。 為什么不在被驅動表上創建索引，是因為這塊sql功能不常用，創建索引浪費空間，并且可能觸發這塊的join sql 也不經常調用。
創建臨時表以及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;
select * from t1 join temp_t on (t1.b=temp_t.b);
