mysql5.7 中Nested Loop Join和 Block Nested-Loop Join對比
在mysql5.7之前兩個表join的方式只有迭代循環，即Nested loop join，在實際實現中有3中算法
1. Simple Nested loop join :SNLP 簡單嵌套循環
2. Index Nested loop join :INLP 簡單嵌套循環
3. Block Nested-Loop Join:BNLJ 緩存塊嵌套循環連接
在嵌套循環中，一般將小表作為外層表，也成驅動表，大表作為內部表，或被驅動表，在實際工作中就是用小表的數據去大表中進行查詢並返回所需數據。
Simple Nested loop join :SNLP 簡單嵌套循環
該嵌套查詢類似於for循環，簡單粗暴，將兩個表的數據進行笛卡爾即操作，假設兩個表各100條記錄，那需要100*100=10000次對比，效率非常地下，MySQL在該算法基礎上繼續優化出 INLP和BNLP, 下圖是 SNLP的說明，我們看到table1是外表（驅動表），我們先從table1中獲取記錄r1，然後再到tables2中全表變量去匹配，目前mysql中已不再使用該低效方法
Index Nested-LoopJoin（減少內層表數據的匹配次數）
我們在SNLP基礎上進行優化，如果在table2上添加一個索引，那麼從table1中來的數據到table2進行匹配的時候就不需要全表掃描了，只需要走索引獲取相應的匹配即可，這就大大提高了迭代效率，我們看下圖
INLP 使用前提為內表關聯字段上有索引
但是我們看上圖有個書籤跳轉的情況，即從table2 index 獲取相應的行之後還要跳轉到聚集索引（主鍵）上去獲取其他列數據，這樣就會導致了額外的io，這種情況我們可以通過創建覆蓋索引進行解決，即table2 index 包含了所需所有數據，這樣上面的圖就變成了
Block Nested-Loop Join 減少內層表數據的循環次數
正如我們上面所說的當內表中關聯鍵有索引情況下mysql會使用INLJ，但如果沒有索引呢？ 這裡mysql會使用BNLI，它會把外表放到緩存中，然後將數據分批到內部表中去匹配，而不是每次一條條的去匹配，這樣就建設了匹配次數，也可成為減少了內部表的邏輯讀次數，那放到緩存中那個地方呢？ 就是所謂的join buffer中去了，當然mysql的join buffer默認並不是很大，它是session級別的設置，我們可以同步下面指令進行查看
show variables like '%join_buffer_size%'
默認大小為256K。BNLI的說明圖如下
如何優化sql
1. 用小結果集驅動大結果集，減少外層循環的數據量：
2. 如果小結果集和大結果集連接的列都是索引列，mysql在內連接時也會選擇用小結果集驅動大結果集，因為索引查詢的成本是比較固定的，這時候外層的循環越少，join的速度便越快。
3. 為匹配的條件增加索引：爭取使用INLJ，減少內層表的循環次數
4. 增大join buffer size的大小：當使用BNLJ時，一次緩存的數據越多，那麼外層表循環的次數就越少
5. 減少不必要的字段查詢：
（1）當用到BNLJ時，字段越少，join buffer 所緩存的數據就越多，外層表的循環次數就越少；
（2）當用到INLJ時，如果可以不回表查詢，即利用到覆蓋索引，則可能可以提示速度。（未經驗證，只是一個推論）
閱讀更多 大數據和數據庫學習 的文章
