MySQL之Join語句執(zhí)行流程是什么

本文小編為大家詳細(xì)介紹“MySQL之Join語句執(zhí)行流程是什么”，內(nèi)容詳細(xì)，步驟清晰，細(xì)節(jié)處理妥當(dāng)，希望這篇“MySQL之Join語句執(zhí)行流程是什么”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學(xué)習(xí)新知識(shí)吧。

Join語句執(zhí)行流程

在實(shí)際生產(chǎn)中，關(guān)于 join 語句使用的問題，一般會(huì)集中在以下兩類：

• 不讓使用 join，使用 join 有什么問題呢？

• 如果有兩個(gè)大小不同的表做 join，應(yīng)該用哪個(gè)表做驅(qū)動(dòng)表呢？

創(chuàng)建兩個(gè)表 t1 和 t2 來說明。

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

可以看到，這兩個(gè)表都有一個(gè)主鍵索引 id 和一個(gè)索引 a，字段 b 上無索引。

存儲(chǔ)過程 idata() 往表 t2 里插入了 1000 行數(shù)據(jù)，在表 t1 里插入的是 100 行數(shù)據(jù)。

一、Index Nested-Loop Join

如果直接使用 join 語句，MySQL 優(yōu)化器可能會(huì)選擇表 t1 或 t2 作為驅(qū)動(dòng)表，這樣會(huì)影響分析 SQL 語句的執(zhí)行過程。

所以，為了便于分析執(zhí)行過程中的性能問題，改用 straight_join 讓 MySQL 使用固定的連接方式執(zhí)行查詢，這樣優(yōu)化器只會(huì)按照指定的方式去 join。

來看一下這個(gè)語句：

select * from t1 straight_join t2 on (t1.a=t2.a);

在這個(gè)語句里，t1 是驅(qū)動(dòng)表，t2 是被驅(qū)動(dòng)表。

現(xiàn)在，來看一下這條語句的 explain 結(jié)果。

可以看到，在這條語句里，被驅(qū)動(dòng)表 t2 的字段 a 上有索引，join 過程用上了這個(gè)索引，因此這個(gè)語句的執(zhí)行流程是這樣的：

從表 t1 中讀入一行數(shù)據(jù) R；從數(shù)據(jù)行 R 中，取出 a 字段到表 t2 里去查找；取出表 t2 中滿足條件的行，跟 R 組成一行，作為結(jié)果集的一部分；重復(fù)執(zhí)行步驟 1 到 3，直到表 t1 的末尾循環(huán)結(jié)束。

這個(gè)過程是先遍歷表 t1，然后根據(jù)從表 t1 中取出的每行數(shù)據(jù)中的 a 值，去表 t2 中查找滿足條件的記錄。

在形式上，這個(gè)過程就跟寫程序時(shí)的嵌套查詢類似，并且可以用上被驅(qū)動(dòng)表的索引，所以稱之為“Index Nested-Loop Join”，簡(jiǎn)稱 NLJ。它對(duì)應(yīng)的流程圖如下所示：

在這個(gè)流程里：

對(duì)驅(qū)動(dòng)表 t1 做了全表掃描，這個(gè)過程需要掃描 100 行；而對(duì)于每一行 R，根據(jù) a 字段去表 t2 查找，走的是樹搜索過程。由于構(gòu)造的數(shù)據(jù)都是一一對(duì)應(yīng)的，因此每次的搜索過程都只掃描一行，也是總共掃描 100 行；所以，整個(gè)執(zhí)行流程，總掃描行數(shù)是 200。

能不能使用 join?

假設(shè)不使用 join，那就只能用單表查詢。

看看上面這條語句的需求，用單表查詢?cè)趺磳?shí)現(xiàn)。

• 執(zhí)行select * from t1，查出表 t1 的所有數(shù)據(jù)，這里有 100 行；

• 循環(huán)遍歷這 100 行數(shù)據(jù)：

• 從每一行 R 取出字段 a 的值 $R.a；

• 執(zhí)行select * from t2 where a=$R.a；

• 把返回的結(jié)果和 R 構(gòu)成結(jié)果集的一行。

可以看到，在這個(gè)查詢過程，也是掃描了 200 行，但是總共執(zhí)行了 101 條語句，比直接 join 多了 100 次交互。

除此之外，客戶端還要自己拼接 SQL 語句和結(jié)果。

顯然，這么做還不如直接 join 好。

怎么選擇驅(qū)動(dòng)表？

在這個(gè) join 語句執(zhí)行過程中，==驅(qū)動(dòng)表是走全表掃描，而被驅(qū)動(dòng)表是走樹搜索。==假設(shè)被驅(qū)動(dòng)表的行數(shù)是 M。

每次在被驅(qū)動(dòng)表查一行數(shù)據(jù)，要先搜索索引 a，再搜索主鍵索引。

每次搜索一棵樹近似復(fù)雜度是以 2 為底的 M 的對(duì)數(shù)，記為 log2M，所以在被驅(qū)動(dòng)表上查一行的時(shí)間復(fù)雜度是 2*log2M。

假設(shè)驅(qū)動(dòng)表的行數(shù)是 N，執(zhí)行過程就要掃描驅(qū)動(dòng)表 N 行，然后對(duì)于每一行，到被驅(qū)動(dòng)表上匹配一次。

因此整個(gè)執(zhí)行過程，近似復(fù)雜度是 N + N*2*log2M。

顯然，N 對(duì)掃描行數(shù)的影響更大，因此應(yīng)該讓小表來做驅(qū)動(dòng)表。

如果沒覺得這個(gè)影響有那么“顯然”， 可以這么理解：
N 擴(kuò)大 1000 倍的話，掃描行數(shù)就會(huì)擴(kuò)大 1000 倍；
而 M 擴(kuò)大 1000 倍，掃描行數(shù)擴(kuò)大不到 10 倍。

小結(jié)一下，通過上面的分析得到了兩個(gè)結(jié)論：

• 使用 join 語句，性能比強(qiáng)行拆成多個(gè)單表執(zhí)行 SQL 語句的性能要好；

• 如果使用 join 語句的話，需要讓小表做驅(qū)動(dòng)表。

但是，需要注意，這個(gè)結(jié)論的前提是“可以使用被驅(qū)動(dòng)表的索引”。

二、Simple Nested-Loop Join

再看看被驅(qū)動(dòng)表用不上索引的情況。

現(xiàn)在，把 SQL 語句改成這樣：

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表 t2 的字段 b 上沒有索引，因此再用圖 2 的執(zhí)行流程時(shí)，每次到 t2 去匹配的時(shí)候，就要做一次全表掃描。

你可以先設(shè)想一下這個(gè)問題，繼續(xù)使用圖 2 的算法，是不是可以得到正確的結(jié)果呢？

如果只看結(jié)果的話，這個(gè)算法是正確的，而且這個(gè)算法也有一個(gè)名字，叫做“Simple Nested-Loop Join”。

但是，這樣算來，這個(gè) SQL 請(qǐng)求就要掃描表 t2 多達(dá) 100 次，總共掃描 100*1000=10 萬行。

這還只是兩個(gè)小表，如果 t1 和 t2 都是 10 萬行的表（當(dāng)然了，這也還是屬于小表的范圍），就要掃描 100 億行，這個(gè)算法看上去太“笨重”了。

三、Block Nested-Loop Join

當(dāng)然，MySQL 也沒有使用這個(gè) Simple Nested-Loop Join 算法，而是使用了另一個(gè)叫作“Block Nested-Loop Join”的算法，簡(jiǎn)稱 BNL。

這時(shí)候，被驅(qū)動(dòng)表上沒有可用的索引，算法的流程是這樣的：

• 把表 t1 的數(shù)據(jù)讀入線程內(nèi)存 join_buffer 中，由于我們這個(gè)語句中寫的是 select *，因此是把整個(gè)表 t1 放入了內(nèi)存；

• 掃描表 t2，把表 t2 中的每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對(duì)比，滿足 join 條件的，作為結(jié)果集的一部分返回。

這個(gè)過程的流程圖如下：

對(duì)應(yīng)地，這條 SQL 語句的 explain 結(jié)果如下所示：

可以看到，在這個(gè)過程中，對(duì)表 t1 和 t2 都做了一次全表掃描，因此總的掃描行數(shù)是 1100。

由于 join_buffer 是以無序數(shù)組的方式組織的，因此對(duì)表 t2 中的每一行，都要做 100 次判斷，總共需要在內(nèi)存中做的判斷次數(shù)是：100*1000=10 萬次。

前面我們說過，如果使用 Simple Nested-Loop Join 算法進(jìn)行查詢，掃描行數(shù)也是 10 萬行。因此，從時(shí)間復(fù)雜度上來說，這兩個(gè)算法是一樣的。但是，Block Nested-Loop Join 算法的這 10 萬次判斷是內(nèi)存操作，速度上會(huì)快很多，性能也更好。

在這種情況下，應(yīng)該選擇哪個(gè)表做驅(qū)動(dòng)表。

假設(shè)小表的行數(shù)是 N，大表的行數(shù)是 M，那么在這個(gè)算法里：

• 兩個(gè)表都做一次全表掃描，所以總的掃描行數(shù)是 M+N；

• 內(nèi)存中的判斷次數(shù)是 M*N。

可以看到，調(diào)換這兩個(gè)算式中的 M 和 N 沒差別，因此這時(shí)候選擇大表還是小表做驅(qū)動(dòng)表，執(zhí)行耗時(shí)是一樣的。

這個(gè)例子里表 t1 才 100 行，要是表 t1 是一個(gè)大表，join_buffer 放不下怎么辦呢？

join_buffer 的大小是由參數(shù) join_buffer_size 設(shè)定的，默認(rèn)值是 256k。如果放不下表 t1 的所有數(shù)據(jù)話，策略很簡(jiǎn)單，就是分段放。

join_buffer_size 改成 1200，再執(zhí)行：

select * from t1 straight_join t2 on (t1.a=t2.b);

執(zhí)行過程就變成了：

• 掃描表 t1，順序讀取數(shù)據(jù)行放入 join_buffer 中，放完第 88 行 join_buffer 滿了，繼續(xù)第 2 步；

• 掃描表 t2，把 t2 中的每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對(duì)比，滿足 join 條件的，作為結(jié)果集的一部分返回；

• 清空 join_buffer；

• 繼續(xù)掃描表 t1，順序讀取最后的 12 行數(shù)據(jù)放入 join_buffer 中，繼續(xù)執(zhí)行第 2 步。

執(zhí)行流程圖也就變成這樣：

圖中的步驟 4 和 5，表示清空 join_buffer 再復(fù)用。

這個(gè)流程才體現(xiàn)出了這個(gè)算法名字中“Block”的由來，表示“分塊去 join”。

可以看到，這時(shí)候由于表 t1 被分成了兩次放入 join_buffer 中，導(dǎo)致表 t2 會(huì)被掃描兩次。

雖然分成兩次放入 join_buffer，但是判斷等值條件的次數(shù)還是不變的，依然是 (88+12)*1000=10 萬次。

在這種情況下驅(qū)動(dòng)表的選擇問題。

假設(shè)，驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 N，需要分 K 段才能完成算法流程，被驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 M。

注意，這里的 K 不是常數(shù)，N 越大 K 就會(huì)越大，因此把 K 表示為λ*N，顯然λ的取值范圍是 (0,1)。

所以，在這個(gè)算法的執(zhí)行過程中：

• 掃描行數(shù)是 N+λNM；

• 內(nèi)存判斷 N*M 次。

顯然，內(nèi)存判斷次數(shù)是不受選擇哪個(gè)表作為驅(qū)動(dòng)表影響的。

而考慮到掃描行數(shù)，在 M 和 N 大小確定的情況下，N 小一些，整個(gè)算式的結(jié)果會(huì)更小。所以結(jié)論是，應(yīng)該讓小表當(dāng)驅(qū)動(dòng)表。

在 N+λNM 這個(gè)式子里，λ才是影響掃描行數(shù)的關(guān)鍵因素，這個(gè)值越小越好。

剛剛我們說了 N 越大，分段數(shù) K 越大。那么，N 固定的時(shí)候，什么參數(shù)會(huì)影響 K 的大小呢？（也就是λ的大?。┐鸢甘?join_buffer_size。

join_buffer_size 越大，一次可以放入的行越多，分成的段數(shù)也就越少，對(duì)被驅(qū)動(dòng)表的全表掃描次數(shù)就越少。

如果你的 join 語句很慢，就把 join_buffer_size 改大。

第一個(gè)問題：能不能使用 join 語句？

• 如果可以使用 Index Nested-Loop Join 算法，也就是說可以用上被驅(qū)動(dòng)表上的索引，其實(shí)是沒問題的；

• 如果使用 Block Nested-Loop Join 算法，掃描行數(shù)就會(huì)過多。尤其是在大表上的 join 操作，這樣可能要掃描被驅(qū)動(dòng)表很多次，會(huì)占用大量的系統(tǒng)資源。所以這種 join 盡量不要用。

所以在判斷要不要使用 join 語句時(shí)，就是看 explain 結(jié)果里面，Extra 字段里面有沒有出現(xiàn)“Block Nested Loop”字樣。

第二個(gè)問題是：如果要使用 join，應(yīng)該選擇大表做驅(qū)動(dòng)表還是選擇小表做驅(qū)動(dòng)表？

• 如果是 Index Nested-Loop Join 算法，應(yīng)該選擇小表做驅(qū)動(dòng)表；

• 如果是 Block Nested-Loop Join 算法：

• 在 join_buffer_size 足夠大的時(shí)候，是一樣的；

• 在 join_buffer_size 不夠大的時(shí)候（這種情況更常見），應(yīng)該選擇小表做驅(qū)動(dòng)表。

所以，這個(gè)問題的結(jié)論就是，總是應(yīng)該使用小表做驅(qū)動(dòng)表。

當(dāng)然了，這里我需要說明下，什么叫作“小表”。

如果我在語句的 where 條件加上 t2.id<=50 這個(gè)限定條件，再來看下這兩條語句：

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

注意，為了讓兩條語句的被驅(qū)動(dòng)表都用不上索引，所以 join 字段都使用了沒有索引的字段 b。

但如果是用第二個(gè)語句的話，join_buffer 只需要放入 t2 的前 50 行，顯然是更好的。

所以這里，“t2 的前 50 行”是那個(gè)相對(duì)小的表，也就是“小表”。

再來看另外一組例子：

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

這個(gè)例子里，表 t1 和 t2 都是只有 100 行參加 join。

但是，這兩條語句每次查詢放入 join_buffer 中的數(shù)據(jù)是不一樣的：

• 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，則 join_buffer 中只需要放入 b 的值；

• 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的話，就需要放入三個(gè)字段 id、a 和 b。

應(yīng)該選擇表 t1 作為驅(qū)動(dòng)表。也就是說在這個(gè)例子里，“只需要一列參與 join 的表 t1”是那個(gè)相對(duì)小的表。

所以，更準(zhǔn)確地說，在決定哪個(gè)表做驅(qū)動(dòng)表的時(shí)候，應(yīng)該是兩個(gè)表按照各自的條件過濾，過濾完成之后，計(jì)算參與 join 的各個(gè)字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個(gè)表，就是“小表”，應(yīng)該作為驅(qū)動(dòng)表。

讀到這里，這篇“MySQL之Join語句執(zhí)行流程是什么”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注億速云行業(yè)資訊頻道。