MYSQL執行流程的簡單探討

周末糾結了一個關於mysql執行流程的問題,現在有點感悟,寫下這篇blog,以做記錄! 現在網絡上有很多文章,都極力推薦在mysql的多表查詢中使用聯表式,而反對子查詢式.但大多文章都是只說其然,而不說其所以然.

說到mysql的執行,就不得不說它的執行流程.而它的執行流程又分為標准執行流程和優化後的執行流程.

標准流程
標准流程是SQL執行的標准流程,幾乎所有的SQL數據庫都是以這個流程作為基礎的.那麼在聯表的時候,他的流程是怎麼樣的呢?
這裡會帶入兩個專業的名詞,笛卡爾積,虛擬表(Virtual Table 簡稱VT);
笛卡爾積這個說明的篇幅太長,大家可以先google一下,這裡就不說明了,而且一般有學過集合的同學,都知道這麼一個東西
VT就是虛擬的表,在mysql處理某個問題的時候,它需要一個容器存放內容,那麼這個容器就是VT.

以下是標准流程的舉例說明
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = ‘name’ LIMIT 5;

這是一個很常見的SQL語句.那它在標准流程中是怎麼執行的呢?
1.T1和T2進行笛卡爾積的計算,形成以個新的集合,放在一個VT內.我們稱這個VT為VT1;
2.對VT1進行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT2;
3.對VT2進行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT3;
4.對VT3進行LIMIT字句處理,取出前5條數據,形成VT4;
5.返回VT4;

這就是一個SQL的標准執行流程,由上面的流程可以看出,每兩表相聯的時候,都會先整理出一個笛卡爾集.這是非常消耗資源的.

這裡我們再看一個子查詢的處理過程.
SELECT * FROM (SELECT * FROM T1 WHERE T1.name = ‘name’) as TMP INNER JOIN T2 ON TMP.id = T2.t1_id LIMIT 5;

如果按照標准的執行流程.這裡的處理流程是
1.對T1進行WHERE字句處理,得到一個臨時表TMP;
2.TMP和T2進行笛卡爾積的計算,形成以個新的集合,形成VT1;
3.對VT1進行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT2;
4.對VT2進行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT3;
5.對VT3進行LIMIT字句處理,取出前5條數據,形成VT4;
6.返回VT4;

對比之下,子查詢比INNER JOIN查詢多了一步的操作,就是先執行WHERE字句,過濾一遍T1,形成一個臨時表.這樣,使用TMP表和T2進行笛卡爾積計算的時候,因為TMP的數據比T1減少了很多,所以大大地提高了兩表連接的效率.雖然說因為子查詢而形成一個臨時表,
增加了開銷,但是卻能很大程度地減少笛卡爾積的體積,這個犧牲是可接受的.

如果是這樣的執行流程,子查詢肯定會比INNER JOIN快.那為什麼那麼多人推薦INNER JOIN呢?終究其原因就是,MYSQL優化器.
在MYSQL的語句執行之前,都會經過優化器,優化器對SQL進行一系列的處理,編程它自己認為效率最高的方式(但也有失誤的時候),然後再執行;

優化流程
以下是同一語句,經過MYSQL優化器處理之後的簡述.MYSQL優化器做的事很多,這裡只是簡述.
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T1.name = ‘name’ LIMIT 5;

1.發現T1是主表,而且WHERE字句中使用的是T1中的name字段作為條件,所以優先排除T1.name != ‘name’的記錄.形成VT1
2.TMP和T2進行笛卡爾積的計算,形成以個新的集合,形成VT2;
3.對VT2進行ON條件的處理,找出VT1中符合T1.id = T2.t1_id條件的記錄,形成VT3;
4.對VT3進行WHERE字句處理,找出VT2中符合T1.name = ‘name’條件的記錄,形成VT4;
5.對VT4進行LIMIT字句處理,取出前5條數據,形成VT5;
6.返回VT5;

優化器自行優先執行了WEHRE字句的內容,不用通過子查詢來排除記錄,這樣既可以減少笛卡爾積的體積,同時也不會因為子查詢而產生了一個臨時表.
故得出,如果可以盡量使用聯表查詢的結論

題外拓展
很多時候,你自己認為的主表,並不是真正的主表.例如
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.t1_id WHERE T2.name = ‘name’ LIMIT 5;

這條SQL中,用T2表中的name作為條件來查詢,當優化器察覺到這個問題的時候,它就會選擇T2作為主表,然後處理WHERE子句之後,再對T1進行聯接
雖然出來的結果是一樣的,但是他們的處理過程卻不一定是你所想象的
當然,這個還跟WEHRE子句中所用到到的索引有關系,總之優化器會選擇它認為最優的辦法來執行.但是,優化器認為是最優的,事實上並不一定是,所以我們要知道它的執行流程和規律,讓它在優化的時候,符合我們所想得.L