聽說mysql中的join很慢?是你用的姿勢不對吧

2022-09-28 14:01:14

join 是進行兩個或多個資料表進行關聯查詢的過程中，經常使用的一種查詢手段。提到join，你一定會想到"笛卡爾積",當資料量很大的時候，"笛卡爾積"運算量會成倍的增加，在我們的印象中，join是一種運算效率不高的查詢語句。

除了定性的判斷join慢之外，你能定量的判斷join的執行效率嗎？

經過下面對join執行效率定量分析後，可能你會改變對join的認識，不在想當然的認為join就一定很慢了。

驅動表與被驅動表

進行join操作的兩個表，分別稱為驅動表和被驅動表，到底哪個是驅動表，哪個是被驅動表是不確定的，這個是mysql優化器來決定，和sql語句中兩個表的位置沒有關係。

如果我們想要強制指定兩個表的對應關係，可以將sql中的join替換成 straight_join，替換後，在straight_join前的表稱為驅動表，在straight_join後的表，稱為被驅動表。

驅動表和被驅動表有什麼差異

在join語句執行的過程中，驅動表和被驅動表所執行的操作是不同的。同是驅動表或被驅動表，在不同的join型別中，所執行操作也是不同的。

下面我們分析一下，不同join型別下，驅動表和被驅動表所做的操作的具體內容。

為了方便下面問題的討論，我們建立如下的表結構:

create table 'table1' (
'id' int(11) NOT NULL,
'a' int(11) DEFAULT NULL,
'b' int(11) DEFAULT NULL,
PRIMARY KEY ('id'),
KEY 'a' ('a')
) engine = Innodb;
 
insert into table1 values(1,1,1)
insert into table1 values(2,2,2)
...
insert into table1 values(1000,1000,1000) // 也可以使用儲存過程來實現大批次資料的插入
 
create table table2 like table1;
insert into t2 (select * from t2 where id <= 100)

建立表結構完全相同的兩個表table1和table2，共有三個欄位：id為主鍵欄位，索引欄位a和普通欄位b。向table1中插入了1000行自增的資料，將table1中的前100行資料插入到table2中。

基於索引的join

如果在join過程中，使用到了索引，這種join又被稱為 Index Nested-Loop Join(NLJ)。

如下面這個語句：

select * from table2  straight_join table1 on table2.a = table1.a;

為了便於明確驅動表和被驅動表，我們使用 straight_join 代替 join，這樣就可以明確 table2 為驅動表，table1為被驅動表。

因為在被驅動表 table1上有索引a欄位，在join的時候，會使用到這個索引，具體可以通過檢視上面sql的執行計劃：

explain select * from table2  straight_join table1 on table2.a = table1.a;

執行計劃圖：

該條語句的執行過程如下：

1.從table2中，讀入一行R。

2.從該資料行R中取出欄位a，到table1中去查詢滿足a=$R.a的資料行，因為在table1表中，欄位a上有索引，所以這個查詢效率很高。

3.將從2中查詢返回的結果和R，構成結果集中一行。

4.重複步驟1到3，直到遍歷完table2中的所有資料行。

這個過程遍歷 table2中的所有資料行，取出每一行中的a值，然後去table1中查詢滿足條件的資料行，將table1中滿足條件的資料和table2中遍歷到的資料，組合成結果集中的資料。

在整個過程中：

驅動表table2所做的操作：被逐行遍歷，也就是進行全表掃描，該過程要掃描100行資料。

被驅動表table1所做的操作：基於索引欄位進行資料查詢，因為table1中，沒有a值相同的兩行資料，所以每次搜尋過程只會掃描一行資料。因為table2中有100行數，所以在table1中要執行100次搜尋過程，也就是在table1中，也要掃描100行資料。

所以這個join語句整個執行下來要掃描200行數。

如果讓 table1作為驅動表，table2作為被驅動表的話，執行語句如下：

select * from table1  straight_join table2 on table2.a = table1.a;

和前者有和區別呢？

根據上面的分析，驅動表需要進行全表掃描，被驅動表基於索引欄位進行資料搜尋。

table1作為驅動表時，sql語句執行計劃如下圖：

當 table1作為驅動表，table2作為被驅動表時：

驅動表table1需要被掃描 1000行。被驅動表table2需要進行 1000次搜尋，但是最終只能成功搜尋到100行資料。總的所有資料行數1100行。

這樣對比下來，table2作為驅動表，table1作為被驅動表執行的效率，要比table1作為驅動表，table2作為被驅動表的執行效率要高一些。

join查詢中如何選擇驅動表

除了分析掃描行數，我們可以對NLJ執行過程中，總的時間複雜度計算一下，看一下哪個因素對join查詢效率影響比較大，進而來對我們選擇驅動表提供參考。

我們假設驅動表中的資料行數是N，被驅動表中的資料行數為M，因為在被驅動表中查詢一行資料，要先搜尋普通索引a，然後再回表到主鍵索引，才能獲取完整的一行資料。

表中資料行數為M，通過主鍵索引樹和普通索引樹查詢一行資料的時間複雜度都是log2M，所以查詢一行資料的時間複雜度為2*log2M。驅動表中有N行數，因此驅動表要掃描N行，驅動表中的每行資料都要到被驅動表中進行一次搜尋。所以當驅動表資料行數為N，被驅動表資料行數為M的情況下，一次基於索引的join查詢的近似時間複雜度為 O = N + N*2*log2M。

整個join語句的時間複雜度，與驅動表中行數的關係為： O = (1+2*log2M)*N ，是線性關係。和被驅動表中行數的關係為：O = N*2*log2M +N 是對數函數關係。

基於數學知識，我們知道 "驅動表中行數"對整個sql執行時間複雜度的影響要比"被驅動表中行數" 影響要大。因此在基於索引的join(NLJ)中，我們應該儘量使用資料量小的表作為驅動表。這樣可以減少掃描的行數，以及整體的時間複雜度。

不使用join，執行效率是否會更高

如果不使用join的情況下，要想實現下圖類似功能，

select * from table2  join table1 on table2.a = table1.a;

我們需要把 table2中的資料全部取出來，

select * from table2; // 掃描100行資料

共100行資料，然後迴圈遍歷這100行資料，取出每行資料中的a值$R.a，去執行

select * from table1 where a = $R.a // 掃描1行資料

把該條語句返回的結果和R拼接在一起，構成結果集中的一行資料。

這種不使用join的方式，也會掃描200行資料，只不過要執行的sql語句會有101條，而使用join語句的情況下，卻只有1條。相比使用join，不使用join，會增加100次與mysql的互動過程，整體的執行效率相比使用join反而更低。

由此可見，在被驅動表上可以使用到索引的情況下，join操作的效率還是比較高的。讀到這裡，你是否會改變對join的認識呢？還會想當然的認為join執行效率很低嗎？

可能你會問，如果join的過程中，被驅動表上沒有索引呢？的確，當被驅動表上沒有索引的情況下，join的執行效率會變慢很多，顯然，"join執行的效率低"這個認知，不是空穴來風，但是變慢的原因是什麼呢？感興趣的老鐵可以看一下，本篇文章。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。