join 是進行兩個或多個數(shù)據(jù)表進行關(guān)聯(lián)查詢的過程中，經(jīng)常使用的一種查詢手段。提到j(luò)oin，你一定會想到"笛卡爾積",當數(shù)據(jù)量很大的時候，"笛卡爾積"運算量會成倍的增加，在我們的印象中，join是一種運算效率不高的查詢語句。

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

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

驅(qū)動表與被驅(qū)動表

進行join操作的兩個表，分別稱為驅(qū)動表和被驅(qū)動表，到底哪個是驅(qū)動表，哪個是被驅(qū)動表是不確定的，這個是mysql優(yōu)化器來決定，和sql語句中兩個表的位置沒有關(guān)系。

如果我們想要強制指定兩個表的對應關(guān)系，可以將sql中的join替換成 straight_join，替換后，在straight_join前的表稱為驅(qū)動表，在straight_join后的表，稱為被驅(qū)動表。

驅(qū)動表和被驅(qū)動表有什么差異

在join語句執(zhí)行的過程中，驅(qū)動表和被驅(qū)動表所執(zhí)行的操作是不同的。同是驅(qū)動表或被驅(qū)動表，在不同的join類型中，所執(zhí)行操作也是不同的。

下面我們分析一下，不同join類型下，驅(qū)動表和被驅(qū)動表所做的操作的具體內(nèi)容。

為了方便下面問題的討論，我們建立如下的表結(jié)構(gòu):

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) // 也可以使用存儲過程來實現(xiàn)大批量數(shù)據(jù)的插入
 
create table table2 like table1;
insert into t2 (select * from t2 where id <= 100)

建立表結(jié)構(gòu)完全相同的兩個表table1和table2，共有三個字段：id為主鍵字段，索引字段a和普通字段b。向table1中插入了1000行自增的數(shù)據(jù)，將table1中的前100行數(shù)據(jù)插入到table2中。

基于索引的join

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

如下面這個語句：

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

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

因為在被驅(qū)動表 table1上有索引a字段，在join的時候，會使用到這個索引，具體可以通過查看上面sql的執(zhí)行計劃：

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

執(zhí)行計劃圖：

該條語句的執(zhí)行過程如下：

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

2.從該數(shù)據(jù)行R中取出字段a，到table1中去查找滿足a=$R.a的數(shù)據(jù)行，因為在table1表中，字段a上有索引，所以這個查詢效率很高。

3.將從2中查詢返回的結(jié)果和R，構(gòu)成結(jié)果集中一行。

4.重復步驟1到3，直到遍歷完table2中的所有數(shù)據(jù)行。

這個過程遍歷 table2中的所有數(shù)據(jù)行，取出每一行中的a值，然后去table1中查找滿足條件的數(shù)據(jù)行，將table1中滿足條件的數(shù)據(jù)和table2中遍歷到的數(shù)據(jù)，組合成結(jié)果集中的數(shù)據(jù)。

在整個過程中：

驅(qū)動表table2所做的操作：被逐行遍歷，也就是進行全表掃描，該過程要掃描100行數(shù)據(jù)。

被驅(qū)動表table1所做的操作：基于索引字段進行數(shù)據(jù)查詢，因為table1中，沒有a值相同的兩行數(shù)據(jù)，所以每次搜索過程只會掃描一行數(shù)據(jù)。因為table2中有100行數(shù)，所以在table1中要執(zhí)行100次搜索過程，也就是在table1中，也要掃描100行數(shù)據(jù)。

所以這個join語句整個執(zhí)行下來 要掃描200行數(shù)。

如果讓 table1作為驅(qū)動表，table2作為被驅(qū)動表的話，執(zhí)行語句如下：

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

和前者有和區(qū)別呢？

根據(jù)上面的分析，驅(qū)動表需要進行全表掃描，被驅(qū)動表基于索引字段進行數(shù)據(jù)搜索。

table1作為驅(qū)動表時，sql語句執(zhí)行計劃如下圖：

當 table1作為驅(qū)動表，table2作為被驅(qū)動表時：

驅(qū)動表table1需要被掃描 1000行。被驅(qū)動表table2需要進行 1000次搜索，但是最終只能成功搜索到100行數(shù)據(jù)。總的所有數(shù)據(jù)行數(shù)1100行。

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

join查詢中如何選擇驅(qū)動表

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

我們假設(shè)驅(qū)動表中的數(shù)據(jù)行數(shù)是N，被驅(qū)動表中的數(shù)據(jù)行數(shù)為M，因為在被驅(qū)動表中查詢一行數(shù)據(jù)，要先搜索普通索引a，然后再回表到主鍵索引，才能獲取完整的一行數(shù)據(jù)。

表中數(shù)據(jù)行數(shù)為M，通過主鍵索引樹和普通索引樹查找一行數(shù)據(jù)的時間復雜度都是log2M，所以查找一行數(shù)據(jù)的時間復雜度為2*log2M。驅(qū)動表中有N行數(shù)，因此驅(qū)動表要掃描N行，驅(qū)動表中的每行數(shù)據(jù)都要到被驅(qū)動表中進行一次搜索。所以當驅(qū)動表數(shù)據(jù)行數(shù)為N，被驅(qū)動表數(shù)據(jù)行數(shù)為M的情況下，一次基于索引的join查詢的近似時間復雜度為 O = N + N*2*log2M。

整個join語句的時間復雜度，與驅(qū)動表中行數(shù)的關(guān)系為： O = (1+2*log2M)*N ，是線性關(guān)系。和被驅(qū)動表中行數(shù)的關(guān)系為：O = N*2*log2M +N 是對數(shù)函數(shù)關(guān)系。

基于數(shù)學知識，我們知道 "驅(qū)動表中行數(shù)"對整個sql執(zhí)行時間復雜度的影響 要比"被驅(qū)動表中行數(shù)" 影響要大。因此在 基于索引的join(NLJ)中，我們應該盡量使用 數(shù)據(jù)量小的表作為驅(qū)動表。這樣可以減少掃描的行數(shù)，以及整體的時間復雜度。

不使用join，執(zhí)行效率是否會更高

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

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

我們需要把 table2中的數(shù)據(jù)全部取出來，

select * from table2; // 掃描100行數(shù)據(jù)

共100行數(shù)據(jù)，然后循環(huán)遍歷這100行數(shù)據(jù)，取出每行數(shù)據(jù)中的a值$R.a，去執(zhí)行

select * from table1 where a = $R.a // 掃描1行數(shù)據(jù)

把該條語句返回的結(jié)果 和R拼接在一起，構(gòu)成結(jié)果集中的一行數(shù)據(jù)。

這種不使用join的方式，也會掃描200行數(shù)據(jù)，只不過要執(zhí)行的sql語句會有101條，而使用join語句的情況下，卻只有1條。相比使用join，不使用join，會增加100次與mysql的交互過程，整體的執(zhí)行效率相比使用join反而更低。

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

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

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論