當被驅動表是一張非常大的冷表，且沒有命中索引時。我們該如何做優化呢？
表t2的c2欄位是沒有索引的，且t2表是一張超級大的冷表，join語句如下：
select * from t1 straight_join t2 on (t1.c1=t2.c2);
其執行過程如下圖：
圖片
其中對t2表做多次全表掃描會帶來什麼問題呢？
innoDB儲存引擎中有一片區域稱為Buffer Pool，用來做查詢快取的。其使用的演算法為LRU演算法：：第一次從磁碟讀入記憶體的資料頁，會先放在 old 區域。如果 1 秒之後這個資料頁不再被訪問了，就不會被移動到 LRU 連結串列頭部。
但是由於t2表資料量比較大，每一次全表掃描t2表必然超過來1秒，那麼再次全表掃描t2表時，冷表的資料頁移到 LRU 連結串列頭部。這樣帶來的後果是正常的業務查詢資料無法進入連結串列頭部。導致join語句開始執行到執行完成後的一定時間內連結串列頭部的資料頁沒有被合理地淘汰。
所以，這種情況不僅這條語句本身執行效率慢，且同時影響了正常業務查詢的效率。而且對正常業務查詢的影響還是持續性的，得依靠後續的查詢邏輯恢復Buffer Pool。
這種情況有兩種優化方式：
增加join_buffer_size，減少t2表全表掃描的次數。
把t1表需要查詢的欄位讀入記憶體臨時表中。（起到一個大的join_buffer的作用）對應的執行語句如下：
// 建立臨時表
create temporary table temp_t1(
id int primary key,
c1 int,
c2 int,
index(c1)
)engine=innodb;
// 插入t1資料
insert into temp_t1 select * from t1;
//使用臨時表做join查詢
select * from temp_t1 join t2 on (t2.c2=temp_t1.c1);
join查詢時，條件是放在on裡還是where裡？
如下兩條語句，你會做如何選擇呢？
// sql1
select * from t1 left join t2 on t1.c1=t2.c1 where t2.c2>100;
//sql2
select * from t1 left join t2 on t1.c1=t2.c1 and t2.c2>100;
我們來看下sql1、sql2的執行過程：
圖片
t1表讀取一行L，到t2表根據on條件到t2表查詢一行資料R（找不到填充null）
L與R的結果存放到臨時結果集
讀取t1表下一行
重複1、2、3步，直到t1表掃描完成
在臨時結果集中根據where條件過濾得到結果集
sql1和sql2區別在於第五步，where條件的執行在on條件查詢的結果後，所以儘量把可能多的條件放在on後，減少where的執行。
本作品採用《CC 協議》，轉載必須註明作者和本文連結