聯動貼：http://blog.itpub.net/29510932/viewspace-1777673/
left join on之中出現的一些有偏差的理解，也是由於對SQL邏輯查詢順序的不瞭解導致的
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------
問題出現於生產環境，為了方便表述，在測試環境構造了類似的場景，同時部分驗證和測試例子隱去了一些生產環境的資訊
有開發人員對生產環境中出現的一些現象提出了疑問，然後仔細查閱資料，特意明確了這一個知識點，順便又挖了一個坑......_(:з」∠)_
------------------------------------------------------------------------------------------------知識點--------------------------------------------------------------------------------------------------------------
SQL虛擬碼

點選(此處)摺疊或開啟

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

SQL虛擬碼的執行順序

點選(此處)摺疊或開啟

(8) SELECT (9) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

大前提：在MySQL中，忽略ICP（Index condition Pushdown），忽略資料庫對order by，group by， limit等語法的最佳化
需要注意的是，SQL語句在執行各個步驟之後，都會把結果臨時儲存起來，姑且記為TM*
1.取出left_table和right_table，然後對兩個表的資料做笛卡爾積，得到臨時結果TM1；
2.根據ON的<join_conditionjoin_condition過濾，留下符合條件的結果，得到臨時結果TM2；
3.檢查join_type，如果是left或者right，那麼則會把left_table<left_table或者<right_tableright_table的行數補齊，得到臨時結果TM3.1；
（3.1）如果存在更多的表要進行join，則讀取下一張需要join的表的資料，重複1-3的步驟，直到得到最終的臨時結果TM3；
4.對TM3的資料，依據where_condition進行過濾，得到臨時結果TM4；
5.對TM4的資料，依據group_by_list<group_by_list進行分組操作，得到臨時結果TM5；
6.對TM5的資料，進行CUBE或者ROLLUP操作，得到臨時結果TM6；
7.對TM6的資料，依據having_condition<having_condition進行過濾，得到臨時結果TM7；
8.對TM7的資料，執行投影操作（和聚集函式計算？），得到臨時結果TM8；
9.對TM8的資料，執行去重操作，得到臨時結果TM9；
10.對TM9的資料，執行排序操作，得到臨時結果TM10；
11.對TM10的資料，執行排序操作，得到臨時結果TM11；
-------------------------------------------------------------------------------------------聯動帖的情況-----------------------------------------------------------------------------------------------------------
那麼回顧聯動帖裡面的問題，在left join on的條件中，除了常見的列關聯，還存在col>1之類的選擇條件，把整個語句代入到上面的執行邏輯裡面，
可以發現，在第2步裡面確實是把不符合col>1的列過濾掉了，但是在第3步，依據left join的特性，又把左表中的列補全了，且不符合條件的全部使用null進行填充，
所以才會出現實驗中的結果；

同樣的，換成inner join以後，得到的結果也完全可以解釋清楚，因此聯動帖中的現象可以根據這個處理邏輯來理解和判斷~
-------------------------------------------------------------------------------------------知識點的應用------------------------------------------------------------------------------------------------------------
背景：分頁查詢
於生產環境下截圖，SQL語句稍加改動，截圖隱去部分資訊
問題：根據SQL邏輯查詢順序的描述來看，分頁查詢的兩個語句應該消耗差不多的時間，
實際上分頁查詢的count（*）比分頁查詢查內容的時候，要慢了800多倍；
問題語句的復現：

點選(此處)摺疊或開啟

select count(*)
from A
left B on A.order_id= B.order_id
left C on A.apply_id= C.apply_id
WHERE
a.`shop_id` IN (3 1,2,3,4,5,6,7,8,9,10)
and a.`create_time` >= '2015-09-06 00:00:00'
------------------------------------------------------------
select A.col1, B.col1, C.col1
from A
left B on A.col1= B.col1
left C on A.col1= C.col1
WHERE
a.`shop_id` IN (1,2,3,4,5,6,7,8,9,10)
and a.`create_time` >= '2015-09-06 00:00:00'
limit 0,100

完全按照SQL邏輯執行順序來重新分析這兩個語句，這兩個語句確實是差不太多的，都會執行步驟1-4和6，下面一個語句還會在最後再執行一個limit，選出從0開始的100行資料；
那麼實際看看執行結果：
count（*）

分頁查詢

去掉limit的分頁查詢（）

其實有對比就很明顯了，這肯定是limit 0，100導致這個時間上的差距，那麼是MySQL做了什麼額外的操作使得limit的速度變得那麼快了？
看看explain：
分頁查詢

去掉limit的分頁查詢

單純在這個分頁查詢裡面，多出來了一個index condition的資訊，這是5.6新加的特性ICP（Index condition Pushdown）（又挖一個坑..._(:з」∠)_...），
在這個特性的幫助下，在步驟1就會減少讀取進來的資料量（rows也發生了變化），所以極大的降低了分頁查詢的執行時間；

去掉limit之後，count（*）和實際查詢的時間雖然還有差距，不過也是在同一個級別上了，這裡面應該還是有一些MySQL自己做的最佳化處理在裡面，這其中的東西，就不太瞭解了。
-------------------------------------------------------------------------------------------完結的分割線-----------------------------------------------------------------------------------------------------------
PS：實際上在這個過程中，還是遇到了一些其他的“奇怪”的現象，繼續摸索ing；每一個問題的背後，知識點經常是一個扣著一個，學無止境_(:з」∠)_...

</having_condition<></group_by_list<></right_table<></left_table<></join_condition<>

MySQL之SQL邏輯查詢順序

相關文章