這篇文章主要講解了“如何理解MySQL的join功能”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“如何理解MySQL的join功能”吧！

成都創(chuàng)新互聯(lián)公司是一家專注于成都網(wǎng)站制作、做網(wǎng)站與策劃設(shè)計(jì),額爾古納網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:額爾古納等地區(qū)。額爾古納做網(wǎng)站價(jià)格咨詢:18980820575

正文

在日常數(shù)據(jù)庫(kù)查詢時(shí)，我們經(jīng)常要對(duì)多表進(jìn)行連表操作來(lái)一次性獲得多個(gè)表合并后的數(shù)據(jù)，這是就要使用到數(shù)據(jù)庫(kù)的 join 語(yǔ)法。join  是在數(shù)據(jù)領(lǐng)域中十分常見(jiàn)的將兩個(gè)數(shù)據(jù)集進(jìn)行合并的操作，如果大家了解的多的話，會(huì)發(fā)現(xiàn) MySQL，Oracle，PostgreSQL 和 Spark  都支持該操作。本篇文章的主角是 MySQL，下文沒(méi)有特別說(shuō)明的話，就是以 MySQL 的 join 為主語(yǔ)。而 Oracle ，PostgreSQL 和  Spark 則可以算做將其吊打的大boss，其對(duì) join 的算法優(yōu)化和實(shí)現(xiàn)方式都要優(yōu)于 MySQL。

MySQL 的 join 有諸多規(guī)則，可能稍有不慎，可能一個(gè)不好的 join  語(yǔ)句不僅會(huì)導(dǎo)致對(duì)某一張表的全表查詢，還有可能會(huì)影響數(shù)據(jù)庫(kù)的緩存，導(dǎo)致大部分熱點(diǎn)數(shù)據(jù)都被替換出去，拖累整個(gè)數(shù)據(jù)庫(kù)性能。

所以，業(yè)界針對(duì) MySQL 的 join 總結(jié)了很多規(guī)范或者原則，比如說(shuō)小表驅(qū)動(dòng)大表和禁止三張表以上的 join 操作。下面我們會(huì)依次介紹 MySQL  join 的算法，和 Oracle 和 Spark 的 join 實(shí)現(xiàn)對(duì)比，并在其中穿插解答為什么會(huì)形成上述的規(guī)范或者原則。

對(duì)于 join 操作的實(shí)現(xiàn)，大概有 Nested Loop Join (循環(huán)嵌套連接)，Hash Join(散列連接) 和 Sort Merge  Join(排序歸并連接) 三種較為常見(jiàn)的算法，它們各有優(yōu)缺點(diǎn)和適用條件，接下來(lái)我們會(huì)依次來(lái)介紹。

MySQL 中的 Nested Loop Join 實(shí)現(xiàn)

Nested Loop Join 是掃描驅(qū)動(dòng)表，每讀出一條記錄，就根據(jù) join  的關(guān)聯(lián)字段上的索引去被驅(qū)動(dòng)表中查詢對(duì)應(yīng)數(shù)據(jù)。它適用于被連接的數(shù)據(jù)子集較小的場(chǎng)景，它也是 MySQL join  的唯一算法實(shí)現(xiàn)，關(guān)于它的細(xì)節(jié)我們接下來(lái)會(huì)詳細(xì)講解。

MySQL 中有兩個(gè) Nested Loop Join 算法的變種，分別是 Index Nested-Loop Join 和 Block  Nested-Loop Join。

Index Nested-Loop Join 算法

下面，我們先來(lái)初始化一下相關(guān)的表結(jié)構(gòu)和數(shù)據(jù)

CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB;  delimiter ;; # 定義存儲(chǔ)過(guò)程來(lái)初始化t1 create procedure init_data() begin declare i int; set i=1; while(i<=10000)do   insert into t1 values(i, i, i);   set i=i+1; end while; end;; delimiter ; # 調(diào)用存儲(chǔ)過(guò)來(lái)來(lái)初始化t1 call init_data(); # 創(chuàng)建并初始化t2 create table t2 like t1; insert into t2 (select * from t1 where id<=500)

有上述命令可知，這兩個(gè)表都有一個(gè)主鍵索引 id 和一個(gè)索引 a，字段 b 上無(wú)索引。存儲(chǔ)過(guò)程 init_data 往表 t1 里插入了 10000  行數(shù)據(jù)，在表 t2 里插入的是 500 行數(shù)據(jù)。

為了避免 MySQL 優(yōu)化器會(huì)自行選擇表作為驅(qū)動(dòng)表，影響分析 SQL 語(yǔ)句的執(zhí)行過(guò)程，我們直接使用 straight_join 來(lái)讓 MySQL  使用固定的連接表順序進(jìn)行查詢，如下語(yǔ)句中，t1是驅(qū)動(dòng)表，t2是被驅(qū)動(dòng)表。

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

使用我們之前文章介紹的 explain 命令查看一下該語(yǔ)句的執(zhí)行計(jì)劃。

從上圖可以看到，t1 表上的 a 字段是由索引的，join 過(guò)程中使用了該索引，因此該 SQL 語(yǔ)句的執(zhí)行流程如下：

• 從 t2 表中讀取一行數(shù)據(jù) L1;

• 使用L1 的 a 字段，去 t1 表中作為條件進(jìn)行查詢;

• 取出 t1 中滿足條件的行， 跟 L1組成相應(yīng)的行，成為結(jié)果集的一部分;

• 重復(fù)執(zhí)行，直到掃描完 t2 表。

這個(gè)流程我們就稱之為 Index Nested-Loop Join，簡(jiǎn)稱 NLJ，它對(duì)應(yīng)的流程圖如下所示。

需要注意的是，在第二步中，根據(jù) a  字段去表t1中查詢時(shí)，使用了索引，所以每次掃描只會(huì)掃描一行(從explain結(jié)果得出，根據(jù)不同的案例場(chǎng)景而變化)。

假設(shè)驅(qū)動(dòng)表的行數(shù)是N，被驅(qū)動(dòng)表的行數(shù)是 M。因?yàn)樵谶@個(gè) join  語(yǔ)句執(zhí)行過(guò)程中，驅(qū)動(dòng)表是走全表掃描，而被驅(qū)動(dòng)表則使用了索引，并且驅(qū)動(dòng)表中的每一行數(shù)據(jù)都要去被驅(qū)動(dòng)表中進(jìn)行索引查詢，所以整個(gè) join 過(guò)程的近似復(fù)雜度是  N2log2M。顯然，N 對(duì)掃描行數(shù)的影響更大，因此這種情況下應(yīng)該讓小表來(lái)做驅(qū)動(dòng)表。

當(dāng)然，這一切的前提是 join 的關(guān)聯(lián)字段是 a，并且 t1 表的 a 字段上有索引。

如果沒(méi)有索引時(shí)，再用上圖的執(zhí)行流程時(shí)，每次到 t1 去匹配的時(shí)候，就要做一次全表掃描。這也導(dǎo)致整個(gè)過(guò)程的時(shí)間復(fù)雜度編程了 N *  M，這是不可接受的。所以，當(dāng)沒(méi)有索引時(shí)，MySQL 使用 Block Nested-Loop Join 算法。

Block Nested-Loop Join

Block Nested-Loop Join的算法，簡(jiǎn)稱 BNL，它是 MySQL 在被驅(qū)動(dòng)表上無(wú)可用索引時(shí)使用的 join  算法，其具體流程如下所示：

• 把表 t2 的數(shù)據(jù)讀取當(dāng)前線程的 join_buffer 中，在本篇文章的示例 SQL 沒(méi)有在 t2 上做任何條件過(guò)濾，所以就是講 t2 整張表  放入內(nèi)存中;

• 掃描表 t1，每取出一行數(shù)據(jù)，就跟 join_buffer 中的數(shù)據(jù)進(jìn)行對(duì)比，滿足 join 條件的，則放入結(jié)果集。

比如下面這條 SQL

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

這條語(yǔ)句的 explain 結(jié)果如下所示。可以看出

可以看出，這次 join 過(guò)程對(duì) t1 和 t2 都做了一次全表掃描，并且將表 t2 中的 500 條數(shù)據(jù)全部放入內(nèi)存 join_buffer  中，并且對(duì)于表 t1 中的每一行數(shù)據(jù)，都要去 join_buffer 中遍歷一遍，都要做 500 次對(duì)比，所以一共要進(jìn)行 500 * 10000  次內(nèi)存對(duì)比操作，具體流程如下圖所示。

主要注意的是，第一步中，并不是將表 t2 中的所有數(shù)據(jù)都放入 join_buffer，而是根據(jù)具體的 SQL  語(yǔ)句，而放入不同行的數(shù)據(jù)和不同的字段。比如下面這條 join 語(yǔ)句則只會(huì)將表 t2 中符合 b >= 100 的數(shù)據(jù)的 b 字段存入  join_buffer。

select t2.b,t1.b from t2 straight_join t1 on (t2.b=t1.b) where t2.b >= 100;

join_buffer 并不是無(wú)限大的，由 join_buffer_size 控制，默認(rèn)值為  256K。當(dāng)要存入的數(shù)據(jù)過(guò)大時(shí)，就只有分段存儲(chǔ)了，整個(gè)執(zhí)行過(guò)程就變成了：

• 掃描表 t2，將符合條件的數(shù)據(jù)行存入 join_buffer，因?yàn)槠浯笮∮邢?，存?00行時(shí)滿了，則執(zhí)行第二步;

• 掃描表 t1，每取出一行數(shù)據(jù)，就跟 join_buffer 中的數(shù)據(jù)進(jìn)行對(duì)比，滿足 join 條件的，則放入結(jié)果集;

• 清空 join_buffer;

• 再次執(zhí)行第一步，直到全部數(shù)據(jù)被掃描完，由于 t2 表中有 500行數(shù)據(jù)，所以一共重復(fù)了 5次

這個(gè)流程體現(xiàn)了該算法名稱中 Block 的由來(lái)，分塊去執(zhí)行 join 操作。因?yàn)楸?t2 的數(shù)據(jù)被分成了 5 次存入 join_buffer，導(dǎo)致表 t1  要被全表掃描 5次。

如上所示，和表數(shù)據(jù)可以全部存入 join_buffer 相比，內(nèi)存判斷的次數(shù)沒(méi)有變化，都是兩張表行數(shù)的乘積，也就是 10000 *  500，但是被驅(qū)動(dòng)表會(huì)被多次掃描，每多存入一次，被驅(qū)動(dòng)表就要掃描一遍，影響了最終的執(zhí)行效率。

基于上述兩種算法，我們可以得出下面的結(jié)論，這也是網(wǎng)上大多數(shù)對(duì) MySQL join 語(yǔ)句的規(guī)范。

• 被驅(qū)動(dòng)表上有索引，也就是可以使用Index Nested-Loop Join 算法時(shí)，可以使用 join 操作。

• 無(wú)論是Index Nested-Loop Join 算法或者 Block Nested-Loop Join 都要使用小表做驅(qū)動(dòng)表。

因?yàn)樯鲜鰞蓚€(gè) join 算法的時(shí)間復(fù)雜度至少也和涉及表的行數(shù)成一階關(guān)系，并且要花費(fèi)大量的內(nèi)存空間，所以阿里開(kāi)發(fā)者規(guī)范所說(shuō)的嚴(yán)格禁止三張表以上的 join  操作也是可以理解的了。

但是上述這兩個(gè)算法只是 join 的算法之一，還有更加高效的 join 算法，比如 Hash Join 和 Sorted Merged  join?？上н@兩個(gè)算法 MySQL 的主流版本中目前都不提供，而 Oracle ，PostgreSQL 和 Spark 則都支持，這也是網(wǎng)上吐槽 MySQL  弱爆了的原因(MySQL 8.0 版本支持了 Hash join，但是8.0目前還不是主流版本)。

其實(shí)阿里開(kāi)發(fā)者規(guī)范也是在從 Oracle 遷移到 MySQL 時(shí)，因?yàn)?MySQL 的 join 操作性能太差而定下的禁止三張表以上的 join  操作規(guī)定的 。

Hash Join 算法

Hash Join 是掃描驅(qū)動(dòng)表，利用 join  的關(guān)聯(lián)字段在內(nèi)存中建立散列表，然后掃描被驅(qū)動(dòng)表，每讀出一行數(shù)據(jù)，并從散列表中找到與之對(duì)應(yīng)數(shù)據(jù)。它是大數(shù)據(jù)集連接操時(shí)的常用方式，適用于驅(qū)動(dòng)表的數(shù)據(jù)量較小，可以放入內(nèi)存的場(chǎng)景，它對(duì)于沒(méi)有索引的大表和并行查詢的場(chǎng)景下能夠提供最好的性能?？上贿m用于等值連接的場(chǎng)景，比如  on a.id = where b.a_id。

還是上述兩張表 join 的語(yǔ)句，其執(zhí)行過(guò)程如下

• 將驅(qū)動(dòng)表 t2 中符合條件的數(shù)據(jù)取出，對(duì)其每行的 join 字段值進(jìn)行 hash 操作，然后存入內(nèi)存中的散列表中;

• 遍歷被驅(qū)動(dòng)表 t1，每取出一行符合條件的數(shù)據(jù)，也對(duì)其 join 字段值進(jìn)行 hash  操作，拿結(jié)果到內(nèi)存的散列表中查找匹配，如果找到，則成為結(jié)果集的一部分。

可以看出，該算法和 Block Nested-Loop Join 有類似之處，只不過(guò)是將無(wú)序的 Join Buffer 改為了散列表 hash  table，從而讓數(shù)據(jù)匹配不再需要將 join buffer 中的數(shù)據(jù)全部遍歷一遍，而是直接通過(guò) hash，以接近 O(1)  的時(shí)間復(fù)雜度獲得匹配的行，這極大地提高了兩張表的 join 速度。

不過(guò)由于 hash 的特性，該算法只能適用于等值連接的場(chǎng)景，其他的連接場(chǎng)景均無(wú)法使用該算法。

Sorted Merge Join 算法

Sort Merge Join 則是先根據(jù) join  的關(guān)聯(lián)字段將兩張表排序(如果已經(jīng)排序好了，比如字段上有索引則不需要再排序)，然后在對(duì)兩張表進(jìn)行一次歸并操作。如果兩表已經(jīng)被排過(guò)序，在執(zhí)行排序合并連接時(shí)不需要再排序了，這時(shí)Merge  Join的性能會(huì)優(yōu)于Hash Join。Merge  Join可適于于非等值Join(>，<，>=，<=，但是不包含!=，也即<>)。

需要注意的是，如果連接的字段已經(jīng)有索引，也就說(shuō)已經(jīng)排好序的話，可以直接進(jìn)行歸并操作，但是如果連接的字段沒(méi)有索引的話，則它的執(zhí)行過(guò)程如下圖所示。

• 遍歷表 t2，將符合條件的數(shù)據(jù)讀取出來(lái)，按照連接字段 a 的值進(jìn)行排序;

• 遍歷表 t1，將符合條件的數(shù)據(jù)讀取出來(lái)，也按照連接字段 a 的值進(jìn)行排序;

• 將兩個(gè)排序好的數(shù)據(jù)進(jìn)行歸并操作，得出結(jié)果集。

Sorted Merge Join 算法的主要時(shí)間消耗在于對(duì)兩個(gè)表的排序操作，所以如果兩個(gè)表已經(jīng)按照連接字段排序過(guò)了，該算法甚至比 Hash Join  算法還要快。在一邊情況下，該算法是比 Nested Loop Join 算法要快的。

下面，我們來(lái)總結(jié)一下上述三種算法的區(qū)別和優(yōu)缺點(diǎn)。

連接條件 適用于任何條件 只適用于等值連接(=) 等值或非等值連接(>，<，=，>=，<=)，‘<>’除外

主要消耗資源 CPU、磁盤I/O 內(nèi)存、臨時(shí)空間 內(nèi)存、臨時(shí)空間

特點(diǎn) 當(dāng)有高選擇性索引或進(jìn)行限制性搜索時(shí)效率比較高，能夠快速返回第一次的搜索結(jié)果 當(dāng)缺乏索引或者索引條件模糊時(shí)，Hash Join 比 Nested  Loop 有效。通常比 Merge Join 快。在數(shù)據(jù)倉(cāng)庫(kù)環(huán)境下，如果表的紀(jì)錄數(shù)多，效率高 當(dāng)缺乏索引或者索引條件模糊時(shí)，Sort Merge Join 比  Nested Loop 有效。當(dāng)連接字段有索引或者提前排好序時(shí)，比 hash join 快，并且支持更多的連接條件

缺點(diǎn) 無(wú)索引或者表記錄多時(shí)效率低 建立哈希表需要大量?jī)?nèi)存，第一次的結(jié)果返回較慢  所有的表都需要排序。它為最優(yōu)化的吞吐量而設(shè)計(jì)，并且在結(jié)果沒(méi)有全部找到前不返回?cái)?shù)據(jù)

需要索引 是(沒(méi)有索引效率太差) 否 否

對(duì)于 Join 操作的理解

講完了 Join 相關(guān)的算法，我們這里也聊一聊對(duì)于 join 操作的業(yè)務(wù)理解。

在業(yè)務(wù)不復(fù)雜的情況下，大多數(shù)join并不是無(wú)可替代。比如訂單記錄里一般只有訂單用戶的  user_id，返回信息時(shí)需要取得用戶姓名，可能的實(shí)現(xiàn)方案有如下幾種：

• 一次數(shù)據(jù)庫(kù)操作，使用 join 操作，訂單表和用戶表進(jìn)行 join，連同用戶名一起返回;

• 兩次數(shù)據(jù)庫(kù)操作，分兩次查詢，第一次獲得訂單信息和 user_id，第二次根據(jù) user_id 取姓名，使用代碼程序進(jìn)行信息合并;

• 使用冗余用戶名稱或者從 ES 等非關(guān)系數(shù)據(jù)庫(kù)中讀取。

上述方案都能解決數(shù)據(jù)聚合的問(wèn)題，而且基于程序代碼來(lái)處理，比數(shù)據(jù)庫(kù) join 更容易調(diào)試和優(yōu)化，比如取用戶姓名不從數(shù)據(jù)庫(kù)中取，而是先從緩存中查找。

當(dāng)然， join  操作也不是一無(wú)是處，所以技術(shù)都有其使用場(chǎng)景，上邊這些方案或者規(guī)則都是互聯(lián)網(wǎng)開(kāi)發(fā)團(tuán)隊(duì)總結(jié)出來(lái)的，適用于高并發(fā)、輕寫重讀、分布式、業(yè)務(wù)邏輯簡(jiǎn)單的情況，這些場(chǎng)景一般對(duì)數(shù)據(jù)的一致性要求都不高，甚至允許臟讀。

但是，在金融銀行或者財(cái)務(wù)等企業(yè)應(yīng)用場(chǎng)景，join  操作則是不可或缺的，這些應(yīng)用一般都是低并發(fā)、頻繁復(fù)雜數(shù)據(jù)寫入、CPU密集而非IO密集，主要業(yè)務(wù)邏輯通過(guò)數(shù)據(jù)庫(kù)處理甚至包含大量存儲(chǔ)過(guò)程、對(duì)一致性與完整性要求很高的系統(tǒng)。

感謝各位的閱讀，以上就是“如何理解MySQL的join功能”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)如何理解MySQL的join功能這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！