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