在開發中往往會出現查詢多表聯查的情況，那麼就會用到 join 查詢。
Join查詢種類
為了方便說明，先定義一個統一的表，下面再做例子。
CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB;
表 t1、t2 結構相等，t2 1000條記錄， t1 100 條記錄，t1 的資料在 t2 上都有。
Index Nested-Loop Join（NLJ）
關於 NLJ 可以從名字上直接看出，這是一個使用到索引的 join 查詢，正因為使用到索引，所以不需要用到臨時表。
例子
select * from t1 straight_join t2 on (t1.a=t2.a);
straight_join ：相當於強制版的 inner join。因為在使用 inner join 或 join 連線時，優化器預設使用的都是小表作為驅動表，但是如果排序或篩選條件列是被驅動表，那麼該列的索引就會用不到，比如 select * from a inner join b where a.id=b.aid order by a.id，如果a的集合比b大，那麼mysql就會以b為驅動表，這個時候如果a.id有索引的話，那麼這個索引在 order by 排序時是不起效的（on 篩選時可以用到，排序只能通過驅動表排序）。那麼就需要額外排序，甚至用到臨時表，非常消耗效能，而 straight_join 的作用就是強制讓左表作為驅動表，當然使用前提是在事先知道優化器選擇的驅動表效率較低，然後才可以考慮使用 straight_join 。這裡就是為了防止優化器選擇錯誤的驅動表，當然，這裡使用 inner join 也是會以 t1 小表作為基礎表。
執行過程：
1、從表 t1 中讀入一行資料 R；
2、從資料行 R 中，取出 a 欄位到表 t2 裡去查詢；
3、取出表 t2 中滿足條件的行，跟 R 組成一行，作為結果集的一部分；
4、重複執行步驟 1 到 3，直到表 t1 的末尾迴圈結束。
整個過程步驟1 會遍歷 t1 所有的行，所以一共會掃描100行。 而步驟2 因為會用到索引，所以每次掃描一次，一共掃描 100 行。最後拼接在一起，所以一共掃描 200 行。
公式：
假設被驅動表的行數是 M。每次在被驅動表查一行資料，要先搜尋索引 a，再搜尋主鍵索引。每次搜尋一棵樹近似複雜度是以 2 為底的 M 的對數，記為 log2M，所以在被驅動表上查一行的時間複雜度是 2*log2M。
假設驅動表的行數是 N，執行過程就要掃描驅動表 N 行，然後對於每一行，到被驅動表上匹配一次。因此整個執行過程，近似複雜度是 N + N*2*log2M。
由此看來，不考慮其他因素的影響（比如上面straight_join 說到的情況），NLJ 方式以小表作為驅動表的效率會更高。
Simple Nested-Loop Join
沒有用到索引。
例子
select * from t1 straight_join t2 on (t1.a=t2.b);
執行過程：
首先因為 straight_join 的作用，還是以 t1 為驅動表。執行時還是先從 t1 上取一條記錄取 t2 上尋找對應的記錄，但是因為 t2 的 b 列上沒有索引，所以在 t2 上執行的是全表掃描。所以掃描行數為 100+100*1000。所以 Simple Nested-Loop Join 的效率很低，這種方式也是沒有被 MySQL 採用。
Block Nested-Loop Join（BNJ）
這種方式也是沒有用到索引，但是和上面一種的區別是在記憶體中實現的。主要思路是將驅動表載入到一個記憶體空間 join_buffer 中，然後從被驅動表上每次拿出一條記錄到 join_buffer 中找到符合條件的記錄，然後返回。join_buffer 大小可由引數 join_buffer_size 設定，預設值是 256k。在 explain 中出現 Block Nested Loop 說明使用的是 BNJ 演算法，BNJ 執行效率比較低，所以應該避免。
例子
select * from t1 straight_join t2 on (t1.a=t2.b);
執行過程：
1、如果 join_buffer 空間足夠儲存 t1 所有記錄：
1）把表 t1 的資料讀入執行緒記憶體 join_buffer 中，由於我們這個語句中寫的是 select *，因此是把整個表 t1 放入了記憶體；
2）掃描表 t2，把表 t2 中的每一行取出來，跟 join_buffer 中的資料做對比，滿足 join 條件的，作為結果集的一部分返回。需要注意的是，因為 join_buffer 是無序陣列，所以雖然 t1 的a 列有索引，在這一步尋找時也不會用到索引。
2、如果 join_buffer 空間不能儲存 t1 的所有記錄。那麼就會分批來處理。
1）掃描表 t1，順序讀取資料行放入 join_buffer 中，放完第 88 行 join_buffer 滿了，繼續第 2 步；
2）掃描表 t2，把 t2 中的每一行取出來，跟 join_buffer 中的資料做對比，滿足 join 條件的，作為結果集的一部分返回；
3）清空 join_buffer；
4）繼續掃描表 t1，順序讀取最後的 12 行資料放入 join_buffer 中，繼續執行第 2 步。
結論：以上這兩種掃描的總行數都是一樣的。 1、將 t1掃描進 join_buffer 100行；2、t2 每行去 joiin_buffer 上進行全表掃描 100*t2總行數1000。所以總行數為 100 +100*1000。和上面的 Simple Nested-Loop Join 方式掃描的行數一樣，但是因為使用的 join_buffer 是在記憶體中的，所以執行的速度會比 Simple Nested-Loop Join 快得多。
公式：
假設，驅動表的資料行數是 N，需要分 K 段才能完成演算法流程，被驅動表的資料行數是 M。注意，這裡的 K 不是常數，N 越大 K 就會越大，因此把 K 表示為λ*N，顯然λ的取值範圍是 (0,1)。那麼掃描行數就是 N+λ*N*M。
由此可以看出，驅動表參與篩選的記錄數越少，掃描的行數就越少，效率也就越高。也就是在不考慮其他因素的影響下，以小表為驅動表可以提高 BNJ方式的執行效率。
優化
Index Nested-Loop Join（NLJ）
NLJ 查詢過程中會用到索引，所以查詢的效率會很快，但是其還是有優化空間的，那就是 MySQL 5.6引入的 Batched Key Access(BKA) 演算法。其原理就是通過 MRR 實現順序讀，因為之前的 NLJ 過程是每次拿一條記錄去匹配，然後得到對應的一條記錄，這樣每次獲取的記錄主鍵很有可能不是按順序去查詢的，同時多次的查詢使得執行效率比較低（每次都需要從 B+樹的根節點開始查詢匹配）。
MRR
MRR 會先將要查詢的記錄主鍵新增到 read_rnd_buffer中（如果放不下就分多次進行），對 read_rnd_buffer 中的 id 進行遞增排序，然後再依次按 id 去查詢，經過 MRR 優化的執行就會走 B+ 樹的葉子節點，所以查詢效率提高。下面以 sql：select * from t1 where a>=1 and a<=100 為例，其中 a 列有索引，看一下開啟 MRR 執行的流程圖：
開啟：
設定：SET @@optimizer_switch='mrr=on,mrr_cost_based=on';
相關引數：
當mrr=on,mrr_cost_based=on，則表示cost base的方式還選擇啟用MRR優化,當發現優化後的代價過高時就會不使用該項優化
當mrr=on,mrr_cost_based=off（官方文件的說法，是現在的優化器策略，判斷消耗的時候，會更傾向於不使用 MRR，把 mrr_cost_based 設定為 off，就是固定使用 MRR 了。），則表示總是開啟MRR優化。
如果查詢使用了 MRR 優化，那麼使用 explain 解析就會出現 Using MRR 的提示
BKA
使用 BKA 的過程：
還是以上面的 select * from t1 straight_join t2 on (t1.a=t2.b); 為例
首先將 t1 的篩選欄位存入 join_buffer（如果存不下就分多次執行），然後將儲存的欄位值批量去 t2 上匹配，得到匹配的主鍵，然後進行主鍵排序，再去依次查詢對應的記錄返回。
Block Nested-Loop Join（BNJ）
BNJ 造成效能損失很高，主要原因有以下三個方面：
1、可能會多次掃描被驅動表，佔用磁碟 IO 資源；
2、判斷 join 條件需要執行 M*N 次對比（M、N 分別是兩張表的行數），如果是大表就會佔用非常多的 CPU 資源；
3、可能會導致 Buffer Pool 的熱資料被淘汰，影響記憶體命中率（影響嚴重）。通過 InnoDB 中的緩衝池(Buffer Pool) 可以知道緩衝池是使用了 LRU 演算法來對熱點資料進行了優化的，但是在某些情況下還是會出現熱點資料被擠掉的場景，使用 BNJ 進行多次的查詢就是其中一種，因為 BNJ 操作如果涉及的表資料量比較大，那麼用到的資料也很多，那麼如果在使用到後面某一時刻某個會話也查詢了某個冷門資料，那麼因為之前 BNJ 也查詢了，並且中間的時間間隔達到了最大老年時間，所以這個冷門資料就會進入老年代頭部，擠掉其他熱點資料。大表 join 操作雖然對 IO 有影響，但是在語句執行結束後，對 IO 的影響也就結束了。但是，對 Buffer Pool 的影響就是持續性的，需要依靠後續的查詢請求慢慢恢復記憶體命中率。
優化思路1：減少 BNJ 的迴圈次數，上面說到，多次的掃描被驅動表會長時間佔用磁碟 IO 資源，造成系統整體效能下降。
方法：增大 join_buffer_size 的值，減少對被驅動表的掃描次數。
優化思路2：將 BNJ 優化成 NLJ。
方法1：在篩選條件欄位使用率比較高時，可以考慮為其建立一個索引，這樣在執行時因為有索引就會變成 NLJ 了。
方法2：如果篩選欄位使用率很低，為其建立索引會提高維護的成本，做到得不償失，那麼該如何優化？答案是可以使用臨時表，從 MySQL 中的臨時表 可以知道，臨時表會隨著會話的結束而自動銷燬，省去了維護的成本；同時不同會話可以建立同名的臨時表，不會產生衝突。這使得臨時表成為優化篩選欄位使用率低的 BNJ 查詢的絕佳方法。
例：假設有表 t1、t2，表 t1 裡，插入了 1000 行資料， t2 中插入了 100 萬行資料 。執行 select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000; b 列使用率很低
未優化前執行：
1、把表 t1 的所有欄位取出來，存入 join_buffer 中。這個表只有 1000 行，join_buffer_size 預設值是 256k，可以完全存入。
2、掃描表 t2，取出每一行資料跟 join_buffer 中的資料進行對比，
1）如果不滿足 t1.b=t2.b，則跳過；
2）如果滿足 t1.b=t2.b, 再判斷其他條件，也就是是否滿足 t2.b 處於[1,2000]的條件，如果是，就作為結果集的一部分返回，否則跳過。整個篩選過程一共掃描了 1000*1000000 = 10億行。
優化思路：
1、把表 t2 中滿足條件的資料放在臨時表 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; select * from t1 join temp_t on (t1.b=temp_t.b);
過程消耗：
1、執行 insert 語句構造 temp_t 表並插入資料的過程中，對錶 t2 做了全表掃描，這裡掃描行數是 100 萬。
2、之後的 join 語句，掃描表 t1，這裡的掃描行數是 1000；join 比較過程中，做了 1000 次帶索引的查詢（因為t1 1000行，作為驅動表，t2作為被驅動表）。相比於優化前的 join 語句需要做 10 億次條件判斷來說，這個優化效果還是很明顯的。
進一步優化：
臨時表又分為磁碟臨時表和記憶體臨時表，使用記憶體臨時表效率比磁碟臨時表高，上面的引擎是 innodb，也就是磁碟臨時表，如果換成 Memory 引擎就是記憶體臨時表。但是相對的記憶體臨時表只能儲存2000行資料，所以在資料量特別大時還是應該使用磁碟臨時表。
三張表優化
表結構：
CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; create table t2 like t1; create table t3 like t2; insert into ... //初始化三張表的資料
如何優化語句：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;
答：首先根據where 三個條件來判斷哪個表符合條件的返回的欄位長度最小，將最小的作為驅動表。
1、第一種情況，如果選出來是表 t1 或者 t3，那剩下的部分就固定了。（因為 join 順序是 t1、t2、t3，確定小表直接向另一個方向驅動就可以了）
1）如果驅動表是 t1，則連線順序是 t1->t2->t3，要在被驅動表欄位建立上索引，也就是 t2.a 和 t3.b 上建立索引；
2）如果驅動表是 t3，則連線順序是 t3->t2->t1，需要在 t2.b 和 t1.a 上建立索引。
同時，我們還需要在第一個驅動表的欄位 c 上建立索引。
2、第二種情況是，如果選出來的第一個驅動表是表 t2 的話，則需要評估另外兩個條件的過濾效果。
總之，整體的思路就是，儘量讓每一次參與 join 的驅動表的資料集，越小越好，因為這樣我們的驅動表就會越小。
總結
NLJ 原本是不需要用到 join_buffer 的，但是可以通過 BKA 優化使用 join_buffer ，此時方向是使用在 join_buffer 中的驅動表資料去被驅動表上匹配，然後得到主鍵，排序、回表返回結果，如果 read_rnd_buffer 或者 join_buffer 空間不夠就分多次進行。
BNL 原本沒有用到索引，所以必須使用 join_buffer 來幫助查詢，方向是被驅動表到 join_buffer 上的驅動表資料進行匹配，優化後變成 BKA 演算法的 NLJ，所以方向也就變成了使用在 join_buffer 中的驅動表資料去被驅動表上匹配。所以在 BNL 優化前的思路就是減少被驅動表的遍歷次數，也就是增大 join_buffer 的大小；而優化後就需要在被驅動表上建立索引，來優化查詢。
join 的 on 條件與 where 條件的關聯
表結構：
create table a(f1 int, f2 int, index(f1))engine=innodb; create table b(f1 int, f2 int)engine=innodb; insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6); insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
on 條件寫在 where 中可能會使外連線失效
以上面的表結構，執行：
select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q1*/ select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q2*/
執行結果：
分析
Q1：
解析：
因為是以 a 作為驅動表，而 a 的 f1有索引，f2沒有索引，所以會用到臨時表來篩選，也就出現 using join buffer（Block hested Loop）
過程：
1、把表 a 的內容讀入 join_buffer 中。因為是 select * ，所以欄位 f1 和 f2 都被放入 join_buffer 了。
2、順序掃描表 b，對於每一行資料，判斷 join 條件（也就是 (a.f1=b.f1) and (a.f1=1)）是否滿足，滿足條件的記錄, 作為結果集的一行返回。如果語句中有 where 子句，需要先判斷 where 部分滿足條件後，再返回。
3、表 b 掃描完成後，對於沒有被匹配的表 a 的行（在這個例子中就是 (1,1)、(2,2) 這兩行），把剩餘欄位補上 NULL，再放入結果集中。
Q2：
解析：
為什麼Q2執行會把 null 值部分過濾掉了？
這是因為在 where 條件中，NULL 跟任何值執行等值判斷和不等值判斷的結果，都是 NULL。這裡包括， select NULL = NULL 的結果，也是返回 NULL。所以在篩選時，先通過 on 判斷帶 null 的記錄，但是因為 where 條件的作用，會篩掉其中為 null 的記錄，導致 left join 失效，所以優化器在實際執行時會將這條語句優化成 inner join，並把篩選條件移到 where 條件後面。整個語句就會被優化成下面的語句執行：
也就是 select a.f1, a.f2, b.f1, b.f2 from a join b where a.f1 = b.f1 and a.f2=b.f2
所以過程就變成：
順序掃描表 b，每一行用 b.f1 到表 a 中去查，匹配到記錄後判斷 a.f2=b.f2 是否滿足（索引下推），滿足條件的話就作為結果集的一部分返回。
所以，如果想要執行外連線查詢，篩選條件就不能寫在 where 中。
內連線可能會將on條件優化成 where 條件
執行：
select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/ select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);/*Q4*/
解析：
可以看到兩條語句解析結果是一模一樣的，並且執行語句也與這兩條語句都不一樣，都被優化成 select * from a join b where a.f1=b.f1 and a.f2=b.f2 。執行過程和上面的 Q2 一樣。這是因為如果放在 on 中就會用到臨時表，效率會低一些，所以優化器直接優化放在 where 中配合索引下推通過索引一併完成判斷。