近期剛好學習了丁奇老師的《MySQL 實戰 45 講》中的 join 優化相關知識,又剛剛好碰上了一個非常切合的 join 查詢需要優化,分析過程有些曲折,記錄下來留作筆記。
問題 SQL 描述問題 SQL 和執行計劃是這樣的:
explain SELECT t1.stru_id AS struId, ...FROM cams_stru_info t1 LEFT JOIN cams_mainframerel t2 ON t1.stru_id =t2.stru_idWHERE t1.stru_state="1";
這個 SQL 是非常簡單的,關聯條件 stru_id 在兩張表中都是主鍵或者主鍵的第一個欄位:
而把 left join 轉化成 inner join 後,SQL的效率很高:
從上述資訊來看,這個 SQL 存在的問題有:
1. 大表驅動小表,這肯定是不好的,t1表近11萬行資料,為驅動表;t2表近1.9萬行資料,為被驅動表。這主要是 left join 導致的,大部分情況下 left join 左表即驅動表,但是這裡業務需求就是如此,沒辦法改變;
2. 驅動表的篩選條件 stru_state = 1,這個欄位是一個狀態值,基數很小,不適合建索引,即使建索引也沒有用,所以驅動表一定是全表掃描。這點根據業務需求,也沒法改變,其實全表掃描對效能影響不大,後續會解釋;
3. 被驅動表關聯欄位明明有索引,但做了全表掃描(全索引掃描);
4. 優化器選擇使用的 join 演算法為 BNL(Block Nested Loop),SQL 執行是計算次數等於 11 萬 * 1.9 萬,近 20 億次計算,所以執行非常慢。
join 的兩種演算法:BNL 和 NLJ在繼續分析之前,先得介紹一下 join 的兩種演算法,方便大家理解後面我分析思路上的錯誤和心得。首先是 NLJ(Index Nested-Loop Join)演算法,以如下 SQL 為例:
select * from t1 join t2 on t1.a=t2.a
SQL 執行時內部流程是這樣的:
1. 先從 t1(假設這裡 t1 被選為驅動表)中取出一行資料 X;
2. 從 X 中取出關聯欄位 a 值,去 t2 中進行查詢,滿足條件的行取出;
3. 重複1、2步驟,直到表 t1 最後一行迴圈結束。
這就是一個巢狀迴圈的過程,注意“Index”,所以這裡前提是被驅動表的關聯欄位有索引,最明顯的特徵就是在被驅動表上查詢資料時可以使用索引,總的對比計算次數等於驅動表滿足 where 條件的行數。假設這裡 t1、t2都是1萬行,則只需要 1萬次計算。
如果 t1、t2 的 a 欄位都沒有索引,還按照上述的巢狀迴圈流程查詢資料呢?每次在被驅動表上查詢資料時都是一次全表掃描,要做1萬次全表掃描,掃描行數等於 1萬+1萬*1萬,這個效率很低,如果錶行數更多,掃描行數動輒幾百億,所以優化器肯定不會使用這樣的演算法,而是選擇 BNL 演算法,執行流程是這樣的:
1. 把 t1 表(假設這裡 t1 被選為驅動表)滿足條件的資料全部取出放到執行緒的 join buffer 中;
2. 每次取 t2 表一行資料,去 joinbuffer 中進行查詢,滿足條件的行取出,直到表 t2 最後一行迴圈結束。
這個演算法下,執行計劃的 Extra 中會出現 Using join buffer(Block Nested Loop),t1、t2 都做了一次全表掃描,總的掃描行數等於 1萬+1萬。但是由於 joinbuffer 維護的是一個無序陣列,每次在 joinbuffer 中查詢都要遍歷所有行,總的記憶體計算次數等於1萬*1萬。說句題外話,如果 joinbuffer 維護的是一個雜湊表的話,每次查詢做一次判斷就能找到資料,效率提升飛快,其實這就是 hash join 了,MySQL 8.0 已支援。另外如果 joinbuffer 不夠大放不下驅動表的資料,則要分多次執行上面的流程,會導致被驅動表也做多次全表掃描。
分析誤區回到分析過程,我一開始疑惑的點就在於:為什麼被驅動表 t2 關聯欄位有索引,卻沒有使用 NLJ 演算法,而是使用了 BNL 演算法?顯然如果使用 NLJ 演算法,總的掃描行數等於 t1 的行數即 19萬行,總的計算次數也只有19萬次,效率是很高的。
因為是剛學到 join 演算法這方面的知識,理解的不是很透徹,思路上一直糾結在演算法這裡,所以接下來我想的是禁用 BNL 演算法,搜尋了一下 hint 語法:"select /+ NO_BNL() */ t1. from ...",執行計劃的結果卻跟我預期的不一樣:
這讓我更迷惑了,明明沒有使用 BNL 演算法,為什麼被驅動表還是做了全表掃描?是演算法出了什麼問題嗎?還是 hint 產生了其他效果?
直到客戶告訴了我答案,兩表的關聯欄位字符集和校對規則不一樣...
得解釋下為什麼之前沒有想這一點,因為前面提到 inner join 執行計劃毫無問題,使用了 NLJ 演算法,優化器選了小表 t2 做驅動表,被驅動表 t1 按索引查詢,效率很高。
繼續分析得知原因後,關於演算法的疑問突然就想通了,NLJ 和 BNL 演算法的選擇根本在於關聯欄位的索引:不是取決於有沒有索引,而是被驅動表能不能使用到索引進行查詢。所以這本質上是一個索引失效問題,邏輯上其實只推進了一步,但是因為對新知識的不自信,推理能力不足(之前自認為推理能力不錯的...),這一步一直沒有走出去,這應該是我最大的收穫了。
然後還要解釋另一個疑問:既然關聯欄位字符集和校對規則不一樣,為什麼 inner join 不受影響?left join 時卻索引失效了?
來看個測試,下面是兩張表,關聯欄位的字符集不一樣:
CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` char(50) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`)) ;
CREATE TABLE `t4` ( `id` int(11) NOT NULL AUTO_INCREMENT, `b` char(50) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_b` (`b`));
分別插入了幾條資料,使用 straight_join 語法固定連線順序:
SQL1:select * from t3 straight_join t4 on t3.a=t4.b;SQL2:select * from t4 straight_join t3 on t3.a=t4.b;SQL3:select * from t3 left join t4 on t3.a=t4.b;SQL4:select * from t3 join t4 on t3.a=t4.b;
SQL1 和 SQL3 都是選擇了 t3 做驅動表,執行計劃一樣,都顯示索引失效了,使用了 BNL 演算法,被驅動表進行全表掃描:
SQL2 和 SQL4 都是選擇了 t4 做驅動表,執行計劃一樣,被驅動表按照索引查詢,使用了 NLJ 演算法:
最後,SQL 改成 inner join 後使用 NLJ 演算法的原因就很明了了:NLJ 演算法的效率顯然是高於 BNL 的,優化器做選擇時當然要選擇更高效的演算法。雖然關聯欄位字符集不一樣,但是按照小>大的順序,索引還是可以正常使用,一旦索引可以使用,選擇 NLJ 演算法就是順理成章的事了。
總結1. NLJ 和 BNL 演算法的選擇根本在於關聯欄位的索引:不是取決於有沒有索引,而是被驅動表能不能使用到索引進行查詢;
2. join 查詢關聯欄位字符集或者校對規則不一致導致的索引失效,跟關聯順序有關,當然規範一定是讓各表關聯欄位的字符集和校對規則一致;
3. join 的優化,最好的辦法就是把 BNL 轉化為 NLJ,也就是被驅動表關聯欄位加索引,並且保證其有效,更多的優化思路可以看參考資料。
另外,一個好訊息是從 MySQL8.0.18 開始已經支援 hash join 了,原本選擇 BNL 演算法的場景會直接使用 hash join,效率提升不止一點點,簡直就是 DBA 福音了。
參考資料:
https://time.geekbang.org/column/article/79700
https://time.geekbang.org/column/article/80147
https://time.geekbang.org/column/article/82865