join執行邏輯

Index Nested_Loop Join（NLJ）

Index Nested_Loop Join：被驅動表上有索引，查的就快一點

// 背景條件：t1表N條數據，t2表M條，t2表在a上有索引，N << M
select * from t1 join t2 on (t1.a=t2.a);

驅動表：t1 (小表)

被驅動表：t2，t2上有a索引，走索引再回表查詢

圖片來自極客時間 丁奇 MySQL實戰45講

時間復雜度：N + N * 2 * log2M

N（t1全表掃描） + （t2表要查N次）N * 2（a索引上搜索一次+回表搜索一次） * log2M(樹查找)

MMR(Mutli-Range Read) 優化

正常回表都是一個一個回表查，但是如果我們是范圍查，可以一組查詢按主鍵id排序后再查（主鍵ID表上是有序的）就更快

在read_rnd_buffer中做排序

|

圖片來自極客時間 丁奇 MySQL實戰45講

BKA(Batched Key Access)算法

按照MMR的思路，NLJ本來是從t1一條一條取數據去t2 a索引上找的，我們可以每次多取點（看join buffer的大小）到join buffer上排個序再一起MRR，去a索引上找。

Simple Nested_Loop Join

如果被驅動表上沒有索引，那t1、t2都全表掃描

時間復雜度：N + N * M

Block Nested-Loop Join（BLJ）

如果被驅動表上沒有索引，做點優化：join_buffer 把驅動表存到內存里，這樣對比的時候快點

原先是從磁盤上一行一行的讀t1，拿到a的值再去t2表上查；現在把t1整個存在內存join buffer中，在一行一行的拿t2和join buffer中的數據做比較

時間復雜度：N + M，內存判斷次數：N * M

join buffer 一次放不下 驅動表

分段放，每部分都執行上面的步驟。

時間復雜度：N + K * M // K就是分成了多少段，內存判斷次數：N * M

join buffer優化的影響：主要影響緩存命中率

大表join會導致冷數據進入內存緩沖區，影響正常業務緩存命中率。由于join buffer優化，導致被驅動表被多次掃描，就算lru 有young區、old區，熱數據也有被頂掉的風險。所以慎用BLJ，最好在被驅動表上建索引，如果僅臨時操作一次，建索引比較浪費，可以考慮使用臨時表

臨時表

臨時表的特點：每個事務獨立有的，會話結束時自動銷毀，show tables訪問不到；對于同名表和臨時表，臨時表優先級高于同名表；

對于偶爾join大表，可以考慮使用臨時表

選用t1還是t2做驅動表

選按照各自條件過濾完后，數據較少的表做驅動表。

從上面的時間復雜度可以看到：join buffer能裝下，選誰都行；其他情況：N的影響大于M，所以N越小越好

全表掃描性能優化

對sever層的影響

圖片來自極客時間 丁奇 MySQL實戰45講

服務端并不需要保存完整的結果集，數據是一段一段傳給客戶端的：

1. 先取一行寫道net buffer pool中。這個內存的大小由參數net_buffer_length定義，默認16KB
2. 重復，直至net buffer pool寫慢，調用網絡接口發出去
3. 成功，就清空net buffer，重復
4. 直至發送失敗，socket send buffer寫慢了，進入等待；等能寫了再發

對innodb的影響：主要影響緩存命中率

buffer pool結構

buffer pool使用lru算法，對最近最少使用的數據進行淘汰。全表掃描會導致短時間內大量冷數據進入buffer pool，影響正常業務的緩存命中率。

針對全表掃描的buffer pool優化

• 若此時訪問P3，由于P3在young區，所以使用之前的lru算法，移動到鏈表首部
• 若此時要訪問一個不存在在buffer pool的數據頁，依舊淘汰隊尾Pm，但新插入的元素放在old區隊首Px位置（// 先觀察一下）
• old區的數據，每次訪問前都要做判斷：
  • 在鏈表存在時間超過1s，ok，可以移動到young隊首
  • 沒到1s，位置不變。innodb_old_blocks_time = 1000ms

增加old區，因為全表掃描的冷數據不會變頻繁訪問，所以一般就在old區，對young區正常業務的緩存影響減小。