SQL在使用JOIN時的一些原理

以前寫SQL用到INNER JOIN時,都自以為SQL會依照順序用等號左邊的資料表(驅動表)欄位去找等號右邊的資料表(被驅動表)欄位的資料

所以應該用資料量較少的Table去當驅動表去JOIN資料量較多的被驅動表,這樣速度會比較快

後來查資料才知道,我只對一半,用資料量少的資料表去找資料量較多的資料表這觀念是沒問題的。但並不是依照SQL語法由上到下的順序來判定JOIN時的驅動表和被驅動表。誰是驅動誰是被動還是要看執行計畫比較準確,MSSQL預設把有加索引的表當被驅動表,未加索引的表則為驅動表,在不同JOIN時會有不同的結果。

ps:個人猜測如果驅動跟被動表都使用到索引的話,應該是以資料筆數較少的表當做驅動表,但這只是個人猜測,還沒做過驗證。

  • INNER JOIN
--Order => 838710 rows
--Invoice_Dtl =>256890 rows

SELECT *FROM Order o 
INNER JOIN Invoice_Dtl id on o.Order_No = id.Order_No

SELECT *FROM Invoice_Dtl id
INNER JOIN Order o  on id.Order_No = o.Order_No

上面兩段SQL,如果以我舊的錯誤觀念來看的話,我會覺得寫法1比較慢,因為Order總共有80幾萬筆資料,但最後執行結果是一樣的。透過執行計畫可以看到MSSQL把有使用到索引(索引搜尋)的Order當作被驅動表,沒使用到索引(索引掃描)的Invoice_Dtl當驅動表,透過巢狀迴圈去取得對應資料(Invoice_Dtl為外迴圈, Order為內迴圈),也因為是透過巢狀迴圈方是去被驅動表找到對應資料,所以在增加索引時,應該把索引增加在被驅動表,當然最好的狀況下是驅動&被驅動表都有索引

 

可以再看一個例子,User表使用到了索引搜尋,所以被當成驅動表。Apply_List使用索引掃描,所以被當成了驅動表。User資料筆數大概是500多筆,Apply_List有10幾萬筆資料,由此可知,並不是資料筆數少的就會被當成驅動表。

;with 
List_a as(
	select *from Apply_List
	WHERE Apply_Type='Stock Out' and Status  in ('D-Waiting DOC','D-waiting (Finished Goods)','Closed') and available='Y' 
),
List_b as (
	select *from Apply_List
	WHERE Apply_Type='Stock In' and Status not in ('Closed','Repairable','Scrap','Temporary Location') and substring(Apply_ID,2,1)='R' and available='Y'
),
Users as (
	select *from User where Available ='Y' and User_Type in ('RMS','RMS Admin','RTS','RTS Admin')
)

select b.RMA_No,b.PartNumber,b.Qty,b.Apply_Date,b.User_id,a.Due_Date,a.Purpose from Users u
inner join List_a a on u.User_ID = a.User_id
inner join List_b b on u.User_ID = b.User_id and a.Purpose= b.Purpose AND a.RMA_NO=b.RMA_NO and a.PartNumber=b.PartNumber and a.Qty=b.Qty
  • LEFT(RIGHT) JOIN

外部連結的話就比較單純了,以LEFT JOIN來說的話,不管資料表的大小,又表為驅動表,左表則為被驅動表,在下ON條件時,被驅動表最好使用索引。另外一提,執行計畫顯示出來的結果,上下順序跟驅動表還是被驅動表示沒有關係的,不是在上面的就是驅動表,在下面的就是被驅動表,可參考下圖,Order為驅動表,但在執行計劃裡還是在Invoice_Dtl的下面!

 

簡單總結:

1.使用join時先確定好驅動跟被驅動表

2.索引應該建立在被驅動表

 

Ref:

1.mysql驱动表与被驱动表及join优化 - 一支会记忆的笔 - 博客园

2.mysql inner join原理_由一个场景分析Mysql的join原理_weixin_39653448的博客-CSDN博客