SQL JOIN 語法順序與效能之推論
2010/02/07
假設a,b表各有10萬筆資料,兩者透過共同的Id欄位要作inner join 通常sql的語法會下:
但我在想, 語法上是先過濾資料再join,還是先join再過濾?? 或許考慮下面的語法會更佳:
上述兩者差異性的語法, 哪一種inner join成本比較小呢? 為什麼書上或學校教的,總是上面比較簡短的語法呢?
我目前還沒有正確專家答案,而且SQL會caching上次查詢結果,要實地測試也不準,因此先來作思維上的推論過程: 首先,因為不同的DBMS有不一樣的處理原則,因此我們先忽略table的index設定及sql語法的優化,我們先來談一般join下,比較普遍的db共識原則。
通常,SQL語法先會執行FROM,接著是WHERE,接著是SELECT,最後才是ORDER BY. 但如今加上一個table來join的話,JOIN是放在哪裡呢? 我從SQL2008的執行計劃圖(Excution Plan)與語法上的可視度猜測,應該是這樣的順序:
我們在乎的是兩個table交叉乘積的時間成本,因此決定權在WHERE與ON語法上,這也是原始問題的差異性。我們通常用WHERE過濾a表,用ON過濾b表資料,但事實上各表的欄位可以混放。既然對先執行WHERE,因此在inner join下,資料量小的table要放左邊,就形成ON a.Id=b.Id ,但寫成相反b.Id=a.Id,會影響join效能嗎?
這句是在ON的後續其他過濾條件執行完後,作真正的交叉執行時才用到的條件,因為過濾資料動作(WHERE, ON)已經完成,因此在數學乘法上a*b = b*a是一樣的。以定位歸屬來說(ON是過濾B資料的),我比較喜歡用ON b.Id=a.Id.
但,WHERE 及 ON的過濾欄位既然能混用:
假設推論正確,再回想看原來的問題,重點在於這一行: ON b.Id=a.Id AND b.Id=1 以Syntax Parser來講,過濾資料是以AND條件的先後來執行,既然在WHERE已過濾掉a.Id=1,那麼執行b.Id=a.Id時,其實已過濾了,再多下一道b.Id=1也是多餘。
推論至此,SQL執行效能上而說,兩者應該是一樣的,甚至要說下方的SQL多此一舉,反而損失了效能。因為處於運作中的SQL的資料過濾通常只是記住cursor位置 尚未真正select出來前,不太可能另外配置空間來存放過濾後的資料。已知a.Id=1,那麼b.Id=a.Id的底層過濾算法,一定是先依a.Id過濾b的資料量,再拿a與b的剩餘量作比對的。(這世上是有正義存在的)
以上是思維推論過程,但實際狀況及效能又是如何呢?我想,除了相信世上有公理正義外,也要看db是怎麼設計及優化的,這也不是我這笨人單方面怎麼想就怎麼對的。
SELECT * FROM a INNER JOIN b ON a.Id=b.Id WHERE a.Id=1
但我在想, 語法上是先過濾資料再join,還是先join再過濾?? 或許考慮下面的語法會更佳:
SELECT * FROM a INNER JOIN b ON b.Id=a.Id AND b.Id=1 WHERE a.Id=1
上述兩者差異性的語法, 哪一種inner join成本比較小呢? 為什麼書上或學校教的,總是上面比較簡短的語法呢?
我目前還沒有正確專家答案,而且SQL會caching上次查詢結果,要實地測試也不準,因此先來作思維上的推論過程: 首先,因為不同的DBMS有不一樣的處理原則,因此我們先忽略table的index設定及sql語法的優化,我們先來談一般join下,比較普遍的db共識原則。
通常,SQL語法先會執行FROM,接著是WHERE,接著是SELECT,最後才是ORDER BY. 但如今加上一個table來join的話,JOIN是放在哪裡呢? 我從SQL2008的執行計劃圖(Excution Plan)與語法上的可視度猜測,應該是這樣的順序:
FROM a -> INNER JOIN b -> WHERE(Index效能) -> ON(Index效能) -> 交叉乘積運算-> SELECT(資料量) -> ORDER BY
我們在乎的是兩個table交叉乘積的時間成本,因此決定權在WHERE與ON語法上,這也是原始問題的差異性。我們通常用WHERE過濾a表,用ON過濾b表資料,但事實上各表的欄位可以混放。既然對先執行WHERE,因此在inner join下,資料量小的table要放左邊,就形成ON a.Id=b.Id ,但寫成相反b.Id=a.Id,會影響join效能嗎?
這句是在ON的後續其他過濾條件執行完後,作真正的交叉執行時才用到的條件,因為過濾資料動作(WHERE, ON)已經完成,因此在數學乘法上a*b = b*a是一樣的。以定位歸屬來說(ON是過濾B資料的),我比較喜歡用ON b.Id=a.Id.
但,WHERE 及 ON的過濾欄位既然能混用:
INNER JOIN b ON a.Id=b.Id AND a.C2=1 WHERE b.C1=2是否代表它們已完成inner join動作後,才作過濾呢? (這就突破我們的先期假設)我想,這事關交叉乘積的效能,DBMS經過千錘百鍊地調校過,肯定是SQL Syntax Parser提供欄位混用的便利功能,底層應該是先過濾資料再作join的。(這世上是有公理存在的)
假設推論正確,再回想看原來的問題,重點在於這一行: ON b.Id=a.Id AND b.Id=1 以Syntax Parser來講,過濾資料是以AND條件的先後來執行,既然在WHERE已過濾掉a.Id=1,那麼執行b.Id=a.Id時,其實已過濾了,再多下一道b.Id=1也是多餘。
推論至此,SQL執行效能上而說,兩者應該是一樣的,甚至要說下方的SQL多此一舉,反而損失了效能。因為處於運作中的SQL的資料過濾通常只是記住cursor位置 尚未真正select出來前,不太可能另外配置空間來存放過濾後的資料。已知a.Id=1,那麼b.Id=a.Id的底層過濾算法,一定是先依a.Id過濾b的資料量,再拿a與b的剩餘量作比對的。(這世上是有正義存在的)
以上是思維推論過程,但實際狀況及效能又是如何呢?我想,除了相信世上有公理正義外,也要看db是怎麼設計及優化的,這也不是我這笨人單方面怎麼想就怎麼對的。