為什么不讓用join？《死磕MySQL系列 十六》

大家好，我是咔咔 不期速成，日拱一卒

在平時(shí)開發(fā)工作中join的使用頻率是非常高的，很多SQL優(yōu)化博文也讓把子查詢改為join從而提升性能，但部分公司的DBA又不讓用，那么使用join到底有什么問題呢？

一、什么是Nested-Loop Join

在MySQL中，使用Nested-Loop Join的算法進(jìn)行優(yōu)化join的使用，此算法翻譯過來為嵌套循環(huán)連接，并且使用了三種算法來實(shí)現(xiàn)。

Index Nested-Loop Join ：簡(jiǎn)稱NLJ

Block Nested-Loop Join ：簡(jiǎn)稱BNLJ

Simple Nested-Loop Join ：簡(jiǎn)稱 BNL

這幾種算法大致意思為索引嵌套循環(huán)連接、緩存塊嵌套循環(huán)連接、粗暴嵌套循環(huán)連接，你現(xiàn)在看的順序就是MySQL選擇join算法的優(yōu)先級(jí)。

從名字上給人感覺Simple Nested-Loop Join算法是非常簡(jiǎn)單同樣也是最快的，但實(shí)際情況是MySQL并沒有使用這種算法而是優(yōu)化成使用Block Nested-Loop Join，帶著各種疑問一起來探索其中的奧秘。

都看到這里了，是不是對(duì)嵌套循環(huán)連接的意思不太明白？其實(shí)是非常簡(jiǎn)單的，一個(gè)簡(jiǎn)單的案例你就能明白什么是嵌套循環(huán)連接。

假設(shè)現(xiàn)在有一張文章表article，一張文章評(píng)論表article_detail，需求是查詢文章的id查詢出所有的評(píng)論現(xiàn)在的首頁，那么SQL就會(huì)是以下的樣子

select * from article a left join article_detail b on a.id = b.article_id

1

若使用代碼來描述這段SQL的實(shí)現(xiàn)原理大致如下，這段代碼使用切片和雙層循環(huán)實(shí)現(xiàn)冒泡排序，這段代碼就能非常代表SQL中join的實(shí)現(xiàn)原理，第一層for即為驅(qū)動(dòng)表，第二層for則為被驅(qū)動(dòng)表。

func bubble_sort(arr []int) { a := 0 for j := 0; j < len(arr)-1; j++ { for i := 0; i < len(arr)-1-j; i++ { if arr[i] > arr[i+1] { a = arr[i] arr[i] = arr[i+1] arr[i+1] = a } } } }

1

2

3

4

5

6

7

8

9

10

11

12

好了，現(xiàn)在你知道了什么是Nested-Loop Join，也知道了實(shí)現(xiàn)Nested-Loop Join的三種算法，接下來咱們就圍繞這三種算法來進(jìn)行討論，為什么不讓用join。

二、Index Nested-Loop Join

為了防止優(yōu)化器對(duì)SQL進(jìn)行粗暴優(yōu)化，接下來會(huì)使用STRAIGHT_JOIN來進(jìn)行查詢操作。

為什么會(huì)需要STRAIGHT_JOIN，在開發(fā)過程中有沒有遇到明明是驅(qū)動(dòng)表的卻莫名其妙的成為了被驅(qū)動(dòng)表，在MySQL中驅(qū)動(dòng)表的概念是當(dāng)指定了連接條件時(shí)，滿足條件并記錄行數(shù)少的表為驅(qū)動(dòng)表。當(dāng)沒有指定查詢條件時(shí)，則掃描行數(shù)少的為驅(qū)動(dòng)表，優(yōu)化器總是以小表驅(qū)動(dòng)大表的方式來決定執(zhí)行順序的。

索引嵌套循環(huán)連接是基于索引進(jìn)行連接的算法，索引是基于被驅(qū)動(dòng)表的，通過驅(qū)動(dòng)表查詢條件直接與被驅(qū)動(dòng)表索引進(jìn)行匹配，防止跟被驅(qū)動(dòng)表的每條記錄進(jìn)行比較，利用索引的查詢減少了對(duì)被驅(qū)動(dòng)表的匹配次數(shù)，從而提升join的性能。

使用前提

使用索引嵌套查詢的前提是驅(qū)動(dòng)表與被驅(qū)動(dòng)表關(guān)聯(lián)字段上有設(shè)置索引。

接下來使用一個(gè)案例來詳細(xì)解析索引嵌套查詢的具體執(zhí)行流程，以下SQL是所有的表和數(shù)據(jù)，直接復(fù)制就可以用

CREATE TABLE `article` (`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'ID',`author_id` INT (11) NOT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci COMMENT='文章表'; CREATE PROCEDURE idata () BEGIN DECLARE i INT; SET i=1; WHILE (i<=1000) DO INSERT INTO article VALUES (i,i); SET i=i+1; END WHILE; END; call idata(); CREATE TABLE `article_comment` (`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'ID',`article_id` INT (11) NOT NULL COMMENT '文章ID',`user_id` INT (11) NOT NULL COMMENT '用戶ID',PRIMARY KEY (`id`),INDEX `idx_article_id` (`article_id`)) ENGINE=INNODB CHARSET=utf8mb4 COLLATE utf8mb4_german2_ci COMMENT='用戶評(píng)論表'; DROP PROCEDURE idata; CREATE PROCEDURE idata () BEGIN DECLARE i INT; SET i=1; WHILE (i<=1000) DO INSERT INTO article_comment VALUES (i,i,i); SET i=i+1; END WHILE; END; CALL idata ();

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

可以看到，此時(shí)article表和article_comment，數(shù)據(jù)都是1000行

需求是查看文章的所有評(píng)論信息，執(zhí)行SQL如下

SELECT*FROM article STRAIGHT_JOIN article_comment ON article.id=article_comment.article_id;

1

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

可以看到，在這條語句中，被驅(qū)動(dòng)表article_comment的字段article_id使用了索引，因此這個(gè)語句的執(zhí)行流程是這樣的

從article表讀取一行數(shù)據(jù)R

從R中去除id字段到表article_comment去查找

取出article_comment中滿足條件的行，跟R組成一行

重復(fù)前三個(gè)步驟，直到表article滿足條件的數(shù)據(jù)掃描結(jié)束

在這個(gè)流程中我們簡(jiǎn)單的梳理一下掃描行數(shù)

對(duì)article表需要做全表掃描，掃描行數(shù)為1000

沒行R數(shù)據(jù)，根據(jù)article表的id去表article_comment查找，走的是樹搜索，因此每次的搜索的結(jié)果都是一一對(duì)應(yīng)的，也就是說每次只會(huì)掃描到一行數(shù)據(jù)，共需要掃描1000

所以，這個(gè)執(zhí)行流程，總掃描行數(shù)為2000行

若在代碼中如何實(shí)現(xiàn)

全表掃描article數(shù)據(jù)，這里是1000行

循環(huán)這1000行數(shù)據(jù)

使用article的id作為條件，在循環(huán)中進(jìn)行查詢

執(zhí)行過程掃描行數(shù)也是2000行，先不涉及這樣寫性能如何，光與MySQL進(jìn)交互就進(jìn)行了1001次。

結(jié)論

顯然這么做還不如直接使用join好

三、Simple Nested-Loop Join

簡(jiǎn)單嵌套循環(huán)連接查詢是表連接使用不上索引，然后就粗暴的使用嵌套循環(huán)，article、article_comment表都有1000行數(shù)據(jù)，那么掃描數(shù)據(jù)的行數(shù)就是1000*1000=1千萬，這種查詢效率可想而知是怎么樣的。

執(zhí)行SQL如下

SELECT * FROM article STRAIGHT_JOIN article_comment ON article.author_id=author_id.user_id;

1

在這個(gè)流程里：

對(duì)驅(qū)動(dòng)表article做了全表掃描，這個(gè)過程需要掃描1000行

從驅(qū)動(dòng)表讀取一行數(shù)據(jù)都需要在article_comment表中進(jìn)行全表掃描，沒有使用索引就需要全表掃描

因此，每次都需要全表掃描被驅(qū)動(dòng)表的數(shù)據(jù)

這還是兩個(gè)非常小的表，在生產(chǎn)環(huán)境的表動(dòng)輒就是上千萬，如果使用這種算法估計(jì)MySQL就沒有現(xiàn)在的盛況

當(dāng)然了，MySQL也沒有使用這種算法，而是用了分塊嵌套查詢的算法，這種思想在MySQL中很多地方都在使用

擴(kuò)展

例如，索引是存儲(chǔ)在磁盤中的，每次使用索引進(jìn)行檢索數(shù)據(jù)時(shí)會(huì)把數(shù)據(jù)從磁盤讀入內(nèi)存中，讀取的方式也是分塊讀取，并不是一次讀取完。

假設(shè)現(xiàn)在操作系統(tǒng)需在磁盤中讀取1kb的數(shù)據(jù)，實(shí)際上會(huì)操作系統(tǒng)讀取到4kb的數(shù)據(jù)，在操作系統(tǒng)中一頁的數(shù)據(jù)是4kb，在innodb存儲(chǔ)引擎中默認(rèn)一頁的數(shù)據(jù)是16kb。

為什么MySQL會(huì)采用分塊來讀取數(shù)據(jù)，是因?yàn)閿?shù)據(jù)的局部性原理，數(shù)據(jù)和程序都有聚集成群的傾向，在訪問到一行數(shù)據(jù)后，在之后有極大的可能性會(huì)再次訪問這條數(shù)據(jù)和這條數(shù)據(jù)相鄰的數(shù)據(jù)。

四、Block Nested-Loop Join

使用簡(jiǎn)單嵌套查詢的方式經(jīng)過上文的分析肯定是不可取的，而是選擇了分塊的思想進(jìn)行處理。

這時(shí)，執(zhí)行流程是這樣的

從驅(qū)動(dòng)表article中讀取數(shù)據(jù)存放在join_buffer中，由于是使用的沒有條件的select ，因此會(huì)把a(bǔ)rticle全表數(shù)據(jù)放入內(nèi)存

拿著join_buffer中的數(shù)據(jù)跟article_comment中的數(shù)據(jù)進(jìn)行逐行對(duì)比

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

為了復(fù)現(xiàn)Block Nested Loop，咔咔裝了三個(gè)版本的MySQL，分別為MySQL8，MySQL5.5，MySQL5.7在后兩個(gè)版本中都使用的是Block Nested Loop，但在MySQL8中卻發(fā)生了變化。

對(duì)于hash join 下期會(huì)聊到，在這個(gè)查詢過程中，對(duì)表article、article_comment都做了一次全表掃描，因此掃描行數(shù)是2000。

把a(bǔ)rticle中的數(shù)據(jù)讀取到j(luò)oin_buffer中是以無序數(shù)組的方式存儲(chǔ)的，對(duì)于article_comment表中的每一行，都需要做1000次判斷，那么就需要判斷的次數(shù)就是1000*1000=1000萬次。

這時(shí)你發(fā)現(xiàn)使用分塊嵌套循環(huán)跟簡(jiǎn)單嵌套查詢掃描行數(shù)是一樣的，但Block Nested Loop算法應(yīng)用了join_buffer的這么一個(gè)內(nèi)存空間，因此速度上肯定會(huì)比Simple快很多。

五、總結(jié)

本期我們用三個(gè)問題來總結(jié)全文，以幫助你更好的理解。

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

通過三個(gè)演示案例，現(xiàn)在你應(yīng)該知道當(dāng)關(guān)聯(lián)條件的列是被驅(qū)動(dòng)表的索引時(shí)，是完全沒有問題的，也就是說當(dāng)使用索引嵌套查詢時(shí)，是可以使用join的。

但當(dāng)使用的是分塊嵌套查詢，這種方式掃描行數(shù)為兩張表行數(shù)的乘，掃描行數(shù)會(huì)非常的大，會(huì)占用大量的系統(tǒng)資源，所以這種算法的join是非常不建議使用的。

因此當(dāng)使用join時(shí)，最大可能的讓關(guān)聯(lián)查詢的列為被驅(qū)動(dòng)表的索引列，若不能達(dá)到這個(gè)條件則可以考慮表結(jié)構(gòu)設(shè)計(jì)是否合理

第二個(gè)問題：如果使用join，選擇大表還是小表作為驅(qū)動(dòng)表？

好的習(xí)慣都是慢慢養(yǎng)成的，因此你要記住無論在什么情況下都用小表驅(qū)動(dòng)大表，先記住這個(gè)結(jié)論。

如果是Nested-Loop Join算法，應(yīng)該選擇小表作為驅(qū)動(dòng)表。

如果是Block Nested-Loop Join，當(dāng)join_buffer足夠大的時(shí)候，使用大表還是小表作為驅(qū)動(dòng)表都是一樣的，但是當(dāng)join_buffer沒有手動(dòng)設(shè)置更大的值時(shí)，還是應(yīng)該選擇小表作為驅(qū)動(dòng)表。

這里還需要知道一點(diǎn)join_buffer的默認(rèn)值為在MySQL8.0位256kb。

第三個(gè)問題：什么樣的表是小表？

這里的小表不是數(shù)據(jù)量非常小的表，這點(diǎn)一定不能搞錯(cuò)，在所有的SQL查詢中絕大多數(shù)情況是有條件進(jìn)行篩選的。

看是否為小表是根據(jù)同一條件下兩張表那個(gè)檢索的數(shù)據(jù)量小，那張表就是小表。

推薦閱讀

死磕MySQL系列總目錄

打開order by的大門，一探究竟《死磕MySQL系列 十二》

重重封鎖，讓你一條數(shù)據(jù)都拿不到《死磕MySQL系列 十三》

闖禍了，生成環(huán)境執(zhí)行了DDL操作《死磕MySQL系列 十四》

聊聊MySQL的加鎖規(guī)則《死磕MySQL系列 十五》

堅(jiān)持學(xué)習(xí)、堅(jiān)持寫作、堅(jiān)持分享是咔咔從業(yè)以來所秉持的信念。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來一點(diǎn)幫助，我是咔咔，下期見。

MySQL SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。