周末糾結(jié)了一個(gè)關(guān)于mysql執(zhí)行流程的問(wèn)題,現(xiàn)在有點(diǎn)感悟,寫下這篇blog,以做記錄! 現(xiàn)在網(wǎng)絡(luò)上有很多文章,都極力推薦在mysql的多表查詢中使用聯(lián)表式,而反對(duì)子查詢式.但大多文章都是只說(shuō)其然,而不說(shuō)其所以然.

說(shuō)到mysql的執(zhí)行,就不得不說(shuō)它的執(zhí)行流程.而它的執(zhí)行流程又分為標(biāo)準(zhǔn)執(zhí)行流程和優(yōu)化后的執(zhí)行流程.

標(biāo)準(zhǔn)流程:標(biāo)準(zhǔn)流程是SQL執(zhí)行的標(biāo)準(zhǔn)流程,幾乎所有的SQL數(shù)據(jù)庫(kù)都是以這個(gè)流程作為基礎(chǔ)的.那么在聯(lián)表的時(shí)候,他的流程是怎么樣的呢?

這里會(huì)帶入兩個(gè)專業(yè)的名詞,笛卡爾積,虛擬表(Virtual Table 簡(jiǎn)稱VT);

笛卡爾積這個(gè)說(shuō)明的篇幅太長(zhǎng),大家可以先google一下,這里就不說(shuō)明了,而且一般有學(xué)過(guò)集合的同學(xué),都知道這么一個(gè)東西

VT就是虛擬的表,在mysql處理某個(gè)問(wèn)題的時(shí)候,它需要一個(gè)容器存放內(nèi)容,那么這個(gè)容器就是VT.

以下是標(biāo)準(zhǔn)流程的舉例說(shuō)明:

SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = ‘name’ LIMIT 5;

這是一個(gè)很常見(jiàn)的SQL語(yǔ)句.那它在標(biāo)準(zhǔn)流程中是怎么執(zhí)行的呢?

1.T1和T2進(jìn)行笛卡爾積的計(jì)算,形成以個(gè)新的集合,放在一個(gè)VT內(nèi).我們稱這個(gè)VT為VT1;

2.對(duì)VT1進(jìn)行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT2;

3.對(duì)VT2進(jìn)行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT3;

4.對(duì)VT3進(jìn)行LIMIT字句處理,取出前5條數(shù)據(jù),形成VT4;

5.返回VT4;

這就是一個(gè)SQL的標(biāo)準(zhǔn)執(zhí)行流程,由上面的流程可以看出,每?jī)杀硐嗦?lián)的時(shí)候,都會(huì)先整理出一個(gè)笛卡爾集.這是非常消耗資源的.

這里我們?cè)倏匆粋€(gè)子查詢的處理過(guò)程.

SELECT * FROM (SELECT * FROM T1 WHERE T1.name = ‘name’) as TMP INNER JOIN T2 ON TMP.id = T2.t1_id LIMIT 5;

如果按照標(biāo)準(zhǔn)的執(zhí)行流程.這里的處理流程是:

1.對(duì)T1進(jìn)行WHERE字句處理,得到一個(gè)臨時(shí)表TMP;

2.TMP和T2進(jìn)行笛卡爾積的計(jì)算,形成以個(gè)新的集合,形成VT1;

3.對(duì)VT1進(jìn)行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT2;

4.對(duì)VT2進(jìn)行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT3;

5.對(duì)VT3進(jìn)行LIMIT字句處理,取出前5條數(shù)據(jù),形成VT4;

6.返回VT4;

對(duì)比之下,子查詢比INNER JOIN查詢多了一步的操作,就是先執(zhí)行WHERE字句,過(guò)濾一遍T1,形成一個(gè)臨時(shí)表.這樣,使用TMP表和T2進(jìn)行笛卡爾積計(jì)算的時(shí)候,因?yàn)門MP的數(shù)據(jù)比T1減少了很多,所以大大地提高了兩表連接的效率.雖然說(shuō)因?yàn)樽硬樵兌纬梢粋€(gè)臨時(shí)表,

增加了開(kāi)銷,但是卻能很大程度地減少笛卡爾積的體積,這個(gè)犧牲是可接受的.

如果是這樣的執(zhí)行流程,子查詢肯定會(huì)比INNER JOIN快.那為什么那么多人推薦INNER JOIN呢?終究其原因就是,MYSQL優(yōu)化器.

在MYSQL的語(yǔ)句執(zhí)行之前,都會(huì)經(jīng)過(guò)優(yōu)化器,優(yōu)化器對(duì)SQL進(jìn)行一系列的處理,編程它自己認(rèn)為效率最高的方式(但也有失誤的時(shí)候),然后再執(zhí)行;

優(yōu)化流程

以下是同一語(yǔ)句,經(jīng)過(guò)MYSQL優(yōu)化器處理之后的簡(jiǎn)述.MYSQL優(yōu)化器做的事很多,這里只是簡(jiǎn)述.

SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = ‘name’ LIMIT 5;

1.發(fā)現(xiàn)T1是主表,而且WHERE字句中使用的是T1中的name字段作為條件,所以優(yōu)先排除T1.name != ‘name’的記錄.形成VT1

2.TMP和T2進(jìn)行笛卡爾積的計(jì)算,形成以個(gè)新的集合,形成VT2;

3.對(duì)VT2進(jìn)行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT3;

4.對(duì)VT3進(jìn)行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT4;

5.對(duì)VT4進(jìn)行LIMIT字句處理,取出前5條數(shù)據(jù),形成VT5;

6.返回VT5;

優(yōu)化器自行優(yōu)先執(zhí)行了WEHRE字句的內(nèi)容,不用通過(guò)子查詢來(lái)排除記錄,這樣既可以減少笛卡爾積的體積,同時(shí)也不會(huì)因?yàn)樽硬樵兌a(chǎn)生了一個(gè)臨時(shí)表.

故得出,如果可以盡量使用聯(lián)表查詢的結(jié)論

題外拓展:很多時(shí)候,你自己認(rèn)為的主表,并不是真正的主表.例如

SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T2.name = ‘name’ LIMIT 5;

這條SQL中,用T2表中的name作為條件來(lái)查詢,當(dāng)優(yōu)化器察覺(jué)到這個(gè)問(wèn)題的時(shí)候,它就會(huì)選擇T2作為主表,然后處理WHERE子句之后,再對(duì)T1進(jìn)行聯(lián)接

雖然出來(lái)的結(jié)果是一樣的,但是他們的處理過(guò)程卻不一定是你所想象的,當(dāng)然,這個(gè)還跟WEHRE子句中所用到到的索引有關(guān)系,總之優(yōu)化器會(huì)選擇它認(rèn)為最優(yōu)的辦法來(lái)執(zhí)行.但是,優(yōu)化器認(rèn)為是最優(yōu)的,事實(shí)上并不一定是,所以我們要知道它的執(zhí)行流程和規(guī)律,讓它在優(yōu)化的時(shí)候,符合我們所想得.