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

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

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

驅(qū)動(dòng)表與被驅(qū)動(dòng)表

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

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

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

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

下面我們分析一下，不同join類型下，驅(qū)動(dòng)表和被驅(qū)動(dòng)表所做的操作的具體內(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) // 也可以使用存儲(chǔ)過程來實(shí)現(xiàn)大批量數(shù)據(jù)的插入
 
create table table2 like table1;
insert into t2 (select * from t2 where id <= 100)

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

基于索引的join

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

如下面這個(gè)語句：

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

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

因?yàn)樵诒或?qū)動(dòng)表 table1上有索引a字段，在join的時(shí)候，會(huì)使用到這個(gè)索引，具體可以通過查看上面sql的執(zhí)行計(jì)劃：

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

執(zhí)行計(jì)劃圖：

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

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

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

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

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

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

在整個(gè)過程中：

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

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

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

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

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

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

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

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

當(dāng) table1作為驅(qū)動(dòng)表，table2作為被驅(qū)動(dòng)表時(shí)：

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

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

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

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

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

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

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

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

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

如果不使用join的情況下，要想實(shí)現(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的方式，也會(huì)掃描200行數(shù)據(jù)，只不過要執(zhí)行的sql語句會(huì)有101條，而使用join語句的情況下，卻只有1條。相比使用join，不使用join，會(huì)增加100次與mysql的交互過程，整體的執(zhí)行效率相比使用join反而更低。

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

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

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

最新評(píng)論