MySQL一次Join表的性能優化分析，性能相差上千倍

背景

在某技術群裡有人發了兩條sql，問為什麼性能有這麼大的差距，下面是當時的問題

線索

SQL 1

SELECT * FROM T1 LEFT JOIN T2 ON T1.f1 = T2.f1 AND T2.f2 IS NULL WHERE T1.f1 = '2875205';      

explain

SQL 2

SELECT * FROM T1 LEFT JOIN T2 ON T1.f1 = T2.f1 WHERE T1.f1 = '2875205' AND T2.f2 IS NULL;      

提問：T1.f1，T2.f1，T2.f2都有索引，T1表大約3萬行，T2表大約100萬行（T2.f2字段90%是null），這兩條sql的結果集是一模一樣的，為什麼第一條sql要3秒，第二條sql隻有1毫秒？

分析

性能分析很多情況下看explain能看出很多問題，我們首先看一下兩條sql的explain，首先都是使用T1.f1的idx_f1索引，type是ref，row是1·，可以先忽略，着重看第二行

sql1：

因為join條件裡有T2.f2 is null，是以選擇了T2.f2的索引idx_f2，掃描行數是1+1*90w，就是說T1每比對一行，都需要掃描T2的90%的行，雖然被驅動表走了索引，但又不完全走，約等于BNL算法被驅動表全表掃描

sql2：

T1.f1 = T2.f1選擇了T2.f1的索引idx_f1，掃描行數是1+n（因為群友沒提供滿足條件說裡面有多少行滿足，但必定遠小于90w）且是索引查詢

擴充知識

MySQL JOIN算法

Nested-Loop Join Algorithm（NLJ）

• 從驅動表取符合條件的一行
• 根據驅動表的資料查詢被驅動表
• 傳回符合條件的資料為結果集

整個過程類似于嵌套循環

https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html#nested-loop-join-algorithm

Block Nested-Loop Join Algorithm（BNL）

• 查詢驅動表的資料寫到join_buffer（ join_buffer_size 确定大小），寫不下就分批執行以下操作
• 查詢被驅動表的資料比對join_buffer

https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html#block-nested-loop-join-algorithm

Hash Join Optimization

MySQL 8.0.18新增的算法，具體類似BNL，并且删除了BNL算法，隻是會将join_buffer的資料轉成散清單存儲

• 确定大小）存儲成散清單，寫不下就分批執行以下操作
• 查詢被驅動表的資料比對join_buffer，因為是散清單，是以時間複雜度是O(1)

https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html