MySQL的驅動表與被驅動表

驅動表與被驅動表的含義

在MySQL中進行多表聯合查詢時，MySQL會透過驅動表的結果集作為基礎資料，在被驅動表中匹配對應的資料，匹配成功合併後的臨時表再作為驅動表或被驅動表繼續與第三張表進行匹配合並，直到所有表都已匹配完畢，最後將結果返回出來。匹配演算法：Nested-Loop Join（巢狀迴圈連線），在MySQL中有三種具體的實現演算法：

• Simple Nested-Loop Join：簡單巢狀迴圈連線
• Index Nested-Loop Join：索引巢狀迴圈連結
• Block Nested-Loop Join：快取快巢狀迴圈連結

Simple Nested-Loop Join

簡單巢狀迴圈連線實際上就是簡單粗暴的巢狀迴圈，如果驅動表有100條資料，被驅動表有100條資料，那麼在匹配時會將驅動表的每一條資料作為匹配條件去被驅動表中逐個比較，實際上就要比較100*100=10000次，可以想象這種比較效率是非常低下的。

Index Nested-Loop Join

索引巢狀迴圈連線是基於被驅動表的索引進行連線的演算法，透過驅動表的匹配條件與被驅動表的索引進行匹配，避免和每條記錄比較，從而利用索引的查詢減少匹配次數，提高查詢的效能。但要注意的是被驅動表的關聯條件必須要有索引時才能用到Index Nested-Loop Join。另外由於用到索引，如果是非聚簇索引並且查詢的資料包含了被驅動表的其他欄位，則會回到被驅動表再查詢一次對應的資料，即回表，多了IO操作。

Block Nested-Loop Join

快取巢狀迴圈連線透過一次性快取多條驅動表資料、參與查詢的列到Join Buffer裡，然後拿Join Buffer裡的資料批次與被驅動表中的資料進行比較，從而減少了迴圈匹配次數。

關於Join Buffer

1. Join Buffer會快取所有參與查詢的列，而不是隻有Join的匹配列
2. 可以調整MySQL的join_buffer_size快取大小，join_buffer_size的預設值是256K,最大值在MySQL 5.1.22版本前是4G，而之後的版本才能在64位作業系統下申請大於4G的空間
3. 要使用Block Nested-Loop Join演算法需要開啟最佳化器管理配置的optimizer_switch的設定block_nested_loop為on，預設為on

當查詢最佳化器不使用Index Nested-Loop Join演算法的時候，預設使用Block Nested-Loop Join演算法。

聯合查詢的效能最佳化原則

明白聯合查詢的原理是驅動表與被驅動表透過條件巢狀迴圈連線匹配後，查詢效能最佳化的思路就是：減少迴圈比較次數。可以透過以下幾個原則來進行最佳化。

1. 以資料量小的表作為驅動表，資料量大的表作為被驅動表。

透過上面的分析可以得知，MySQL在聯合查詢中是用驅動表的資料作為篩選條件在被驅動表中進行匹配，所以假設table1作為驅動表，資料有10000條，table2作為被驅動表的資料有100條，並且被table2中有索引，那麼用Index Nested-Loop Join演算法進行匹配時要進行10000次的關聯操作。但如果反過來用table2作為驅動表，table1作為被驅動表，只需要進行100次關聯即可完成匹配，效率也會大大提高，其他的連線演算法也類似。簡單說通常情況下要用小表驅動大表。
但是這裡的小表和大表是根據查詢條件相對而言的，大小的計算是要根據查詢條件和具體的欄位進行衡量，假如查詢條件指定了table1的搜尋範圍，即table1滿足查詢條件的行數有90行，那麼計算公式為：90乘以參與關聯查詢欄位的大小總和，若結果小於table2滿足查詢條件後的行數乘以參與關聯查詢欄位的大小，則table1為小表，否則table1為大表。

2. 為匹配的條件增加索引

匹配的條件欄位列儘量使用有索引的，爭取使用Index Nested-Loop Join演算法進行關聯，減少被驅動表的迴圈次數

3. 增大join_buffer_size的大小

當使用Block Nested-Loop Join演算法時，增大join_buffer_size的大小可以使驅動表一次快取更多的資料，從而減少總體迴圈匹配的次數

4. 減少不必要的欄位查詢

• 當用到Block Nested-Loop Join演算法時，欄位越少，join Buffer所快取的資料就越多，那麼迴圈的次數就越少。
• 當用到Index Nested-Loop Join演算法時，如果可以不回表查詢，即只查詢索引列，利用覆蓋索引則可能提升匹配效率

如何確定驅動表與被驅動表

• 在使用join連線並且無where條件時：
  1. left join左邊的表為驅動表，右邊的為被驅動表
  2. right join右邊的表為驅動表，左邊的為被驅動表
  3. 使用join時，MySQL會自動判斷左右兩邊哪邊是小表，哪邊是大表。小表作為驅動表，大表作為被驅動表，小表與大表的判斷原則為上面講到的根據行數和參與關聯的欄位計算得出。
• 在使用in\exists時
  1. 使用in時，驅動表和被驅動表由MySQL的執行器根據表的大小自動選擇
  2. 使用exists時，外部表為驅動表，內部表為被驅動表。無論加什麼查詢條件都無法改變

使用join連線查詢時如果有where條件，則MySQL執行器會根據查詢條件過濾後的結果自動選擇驅動表或被驅動表。