今天我們來聊聊微信中的多表聯(lián)合查詢，應(yīng)該是小表驅(qū)動(dòng)大表還是大表驅(qū)動(dòng)小表?

1. in VS exists

在正式分析之前，我們先來看兩個(gè)關(guān)鍵字 in 和 exists。

假設(shè)我現(xiàn)在有兩張表：?jiǎn)T工表和部門表，每個(gè)員工都有一個(gè)部門，員工表中保存著部門的 id，并且該字段是索引;部門表中有部門的 id、name 等屬性，其中 id 是主鍵，name 是唯一索引。

這里我就直接使用 vhr 中的表來做試驗(yàn)，就不單獨(dú)給大家數(shù)據(jù)庫(kù)腳本了，小伙伴們可以查看 vhr 項(xiàng)目(https://github.com/lenve/vhr)獲取數(shù)據(jù)庫(kù)腳本。

假設(shè)我現(xiàn)在想查詢技術(shù)部的所有員工，我有如下兩種查詢方式：

第一種查詢方式是使用 in 關(guān)鍵字來查詢：

select * from employee e where e.departmentId in(select d.id from department d where d.name='技術(shù)部') limit 10;

這個(gè) SQL 很好理解，相信大家都能懂。查詢的時(shí)候也是先查詢里邊的子查詢(即先查詢 department 表)，然后再執(zhí)行外表的查詢，我們可以看下它的執(zhí)行計(jì)劃：

可以看到，首先查詢部門表，有索引就用索引，沒有索引就全表掃描，然后查詢員工表，也是利用索引來查詢，整體上效率比較高。

第二種是使用 exists 關(guān)鍵字來查詢：

select * from employee e where exists(select 1 from department d where d.id=e.departmentId and d.name='技術(shù)部') limit 10;

這條 SQL 的查詢結(jié)果和上面用 in 關(guān)鍵字的一樣，但是查詢過程卻不一樣，我們來看看這個(gè) SQL 的執(zhí)行計(jì)劃：

可以看到，這里先對(duì)員工表做了全表掃描，然后拿著員工表中的 departmentId 再去部門表中進(jìn)行數(shù)據(jù)比對(duì)。上面這個(gè) SQL 中，子查詢有返回值，就表示 true，沒有返回值就表示 false，如果為 true，則這個(gè)員工記錄就保留下來，如果為 false，則這個(gè)員工記錄會(huì)被拋棄掉。所以在子查詢中的可以不用 SELECT *，可以將之改為 SELECT 1 或者其他，MySQL 官方的說法是在實(shí)際執(zhí)行時(shí)會(huì)忽略SELECT 清單，因此寫啥區(qū)別不大。

對(duì)比兩個(gè)查詢計(jì)劃中的掃描行數(shù)，我們就能大致上看出差異，使用 in 的話，效率略高一些。

如果用 in 關(guān)鍵字查詢的話，先部門表再員工表，一般來說部門表的數(shù)據(jù)是要小于員工表的數(shù)據(jù)的，所以這就是小表驅(qū)動(dòng)大表，效率比較高。

如果用 exists 關(guān)鍵字查詢的話，先員工表再部門表，一般來說部門表的數(shù)據(jù)是要小于員工表的數(shù)據(jù)的，所以這就是大表驅(qū)動(dòng)小表，效率比較低。

總之，就是要小表驅(qū)動(dòng)大表效率才高，大表驅(qū)動(dòng)小表效率就會(huì)比較低。所以，假設(shè)部門表的數(shù)據(jù)量大于員工表的數(shù)據(jù)量，那么上面這兩種 SQL，使用 exists 查詢關(guān)鍵字的效率會(huì)比較高。

2. 為什么要小表驅(qū)動(dòng)大表

在 MySQL 中，這種多表聯(lián)合查詢的原理是：以驅(qū)動(dòng)表的數(shù)據(jù)為基礎(chǔ)，通過類似于我們 Java 代碼中寫的嵌套循環(huán) 的方式去跟被驅(qū)動(dòng)表記錄進(jìn)行匹配。

以第一小節(jié)的表為例，假設(shè)我們的員工表 E 表是大表，有 10000 條記錄;部門表 D 表是小表，有 100 條記錄。

假設(shè) D 驅(qū)動(dòng) E，那么執(zhí)行流程大概是這樣：

for 100 個(gè)部門{
    匹配 10000 個(gè)員工(進(jìn)行B+樹查找)
}

那么查找的總次數(shù)是 100+log10000。

假設(shè) E 驅(qū)動(dòng) D，那么執(zhí)行流程大概是這樣：

for 10000 個(gè)員工{
    匹配 100 個(gè)部門(進(jìn)行B+樹查找)
}

那么總的查找次數(shù)是 10000+log100。

從這兩個(gè)數(shù)據(jù)對(duì)比中我們就能看出來，小表驅(qū)動(dòng)大表效率要高。核心的原因在于，搜索被驅(qū)動(dòng)的表的時(shí)候，一般都是有索引的，而索引的搜索就要快很多，搜索次數(shù)也少。

3. 沒有索引咋辦?

前面第二小節(jié)我們得出的結(jié)論有一個(gè)前提，就是驅(qū)動(dòng)表和被驅(qū)動(dòng)表之間關(guān)聯(lián)的字段是有索引的，以我們前面的表為例，就是 E 表中保存了 departmentId 字段，該字段對(duì)應(yīng)了 D 表中的 id 字段，而 id 字段在 D 表中是主鍵索引，如果 id 不是主鍵索引，就是一個(gè)普通字段，那么 D 表豈不是也要做全表掃描了?那個(gè)時(shí)候 E 驅(qū)動(dòng) D 還是 D 驅(qū)動(dòng) E 差別就不大了。

對(duì)于這種被驅(qū)動(dòng)表上沒有可用索引的情況，MySQL 使用了一種名為 Block Nested-Loop Join (簡(jiǎn)稱 BNL)的算法，這種算法的步驟是這樣：

• 把 E 表的數(shù)據(jù)讀入線程內(nèi)存 join_buffer 中。
• 掃描 D 表，把 D 表中的每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對(duì)比，滿足 join 條件的，作為結(jié)果集的一部分返回。

小伙伴們來看下，如果我把 E 表中 departmentId 字段上的索引刪除，再把 D 表中的 id 字段上的主鍵索引也刪除，此時(shí)我們?cè)賮砜纯慈缦? SQL 的執(zhí)行計(jì)劃：

可以看到，此時(shí) E 表和 D 表都是全表掃描，另外需要注意，這些比對(duì)操作都是在內(nèi)存中，所以執(zhí)行效率都是 OK 的。

但是，既然把數(shù)據(jù)都讀入到內(nèi)存中，內(nèi)存中能放下嗎?內(nèi)存中放不下咋辦?我們看上面的查詢計(jì)劃，對(duì) E 表的查詢中，Extra 中還出現(xiàn)了 Using join buffer (Block Nested Loop)，Block 不就有分塊的意思嗎!所以這意思就很明確了，內(nèi)存中一次放不下，那就分塊讀取，先讀一部分到內(nèi)存中，比對(duì)完了再讀另一部分到內(nèi)存中。

通過如下指令我們可以查看 join_buffer 的大?。?/p>

262144/1024=256KB

默認(rèn)大小是 256 KB。

我現(xiàn)在把這個(gè)值改大，然后再查看新的執(zhí)行計(jì)劃，如下：

大家看到，此時(shí)已經(jīng)沒有 Using join buffer (Block Nested Loop) 提示了。

總結(jié)一下：

• 如果 join_buffer 足夠大，一次性就能讀取所有數(shù)據(jù)到內(nèi)存中，那么大表驅(qū)動(dòng)小表還是小表驅(qū)動(dòng)大表都無所謂了。
• 如果 join_buffer 大小有限，那么建議小表驅(qū)動(dòng)大表，這樣即使要分塊讀取，讀取的次數(shù)也少一些。

不過老實(shí)說，這種沒有索引的多表聯(lián)合查詢效率比較低，應(yīng)該盡量避免。

綜上所述，在多表聯(lián)合查詢的時(shí)候，建議小表驅(qū)動(dòng)大表。