INNER JOIN與LEFT JOIN在SQL Server的效能

標籤：

我建立了INNER JOIN 9桌，反正需要很長的（超過五分鐘）。所以，我的民歌改變INNER JOIN來LEFT JOIN LEFT JOIN的效能較好，在首次儘管我所知道的。之後我變了，查詢的速度顯著提高。 我想知道為什麼LEFT JOIN的速度比INNER JOIN？ 我的樣子如下：SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D因此沒有 更新： 這是我的簡單架構的。

FROM sidisaleshdrmly a -- NOT HAVE PK AND FK INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK  ON a.CompanyCd = b.CompanyCd    AND a.SPRNo = b.SPRNo    AND a.SuffixNo = b.SuffixNo    AND a.dnno = b.dnno INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine  ON a.CompanyCd = h.CompanyCd   AND a.sprno = h.AcctSPRNo INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix  ON c.CompanyCd = h.CompanyCd   AND c.FSlipNo = h.FSlipNo    AND c.FSlipSuffix = h.FSlipSuffix  INNER JOIN coMappingExpParty d -- NO PK AND FK  ON c.CompanyCd = d.CompanyCd   AND c.CountryCd = d.CountryCd  INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd  ON b.CompanyCd = e.CompanyCd   AND b.ProductSalesCd = e.ProductSalesCd  LEFT JOIN coUOM i -- PK = UOMId  ON h.UOMId = i.UOMId  INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd  ON a.CompanyCd = j.CompanyCd   AND b.BFStatus = j.BFStatus   AND b.ProductSalesCd = j.ProductSalesCd INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd  ON e.ProductGroup1Cd = g1.ProductGroup1Cd INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd  ON e.ProductGroup1Cd = g2.ProductGroup1Cd

本文地址 ：CodeGo.net/150174/ 
------------------------------------------------------------------------------------------------------------------------- 
1. 一LEFT JOIN比絕對不是更快INNER JOIN。事實上，它的速度較慢，根據定義，一個外部聯結（LEFT JOIN或RIGHT JOIN）所要做的所有工作INNER JOIN空擴充結果的額外工作。它也將有望重回更多的行，進一步增加了總的執行簡單地歸結到結果集的規模越大。 （而且，即使一LEFT JOIN是在特定的情況下更快的原因有多種難想象合流，它不是在函數上等同於INNER JOINCodeGo.net，所以你不能簡單地去替換一個與其他的所有執行個體！） 最有可能的效能問題在其他地方，如沒有一個候選索引鍵或適當的索引外鍵。 9表格是相當多的，能夠加入這樣的放緩可以從字面上幾乎任何地方。如果您發布您的模式，我們也許能夠提供更多的細節。 編輯： 進一步反映在此，我能想到的下一個plotLEFT JOIN可能比一個快INNER JOIN，那就是當： 的表是非常小的（例如，在10行）; 該表不具有足夠的索引來覆蓋查詢。 考慮這個例子：

CREATE TABLE #Test1( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL)INSERT #Test1 (ID, Name) VALUES (1, ‘One‘)INSERT #Test1 (ID, Name) VALUES (2, ‘Two‘)INSERT #Test1 (ID, Name) VALUES (3, ‘Three‘)INSERT #Test1 (ID, Name) VALUES (4, ‘Four‘)INSERT #Test1 (ID, Name) VALUES (5, ‘Five‘)CREATE TABLE #Test2( ID int NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL)INSERT #Test2 (ID, Name) VALUES (1, ‘One‘)INSERT #Test2 (ID, Name) VALUES (2, ‘Two‘)INSERT #Test2 (ID, Name) VALUES (3, ‘Three‘)INSERT #Test2 (ID, Name) VALUES (4, ‘Four‘)INSERT #Test2 (ID, Name) VALUES (5, ‘Five‘)SELECT *FROM #Test1 t1INNER JOIN #Test2 t2ON t2.Name = t1.NameSELECT *FROM #Test1 t1LEFT JOIN #Test2 t2ON t2.Name = t1.NameDROP TABLE #Test1DROP TABLE #Test2

如果你運行這個並查看執行計畫，你會看到INNER JOIN查詢確實成本比LEFT JOIN，它滿足上述兩個條件。這是SQL Server的想要做一個雜湊匹配的INNER JOIN，但不嵌套迴圈的LEFT JOIN;在通常快得多，但由於行數是如此的渺小和沒有索引的雜湊運算結果是查詢的最昂貴的部分。 您可以通過編寫一個程式在您最喜愛的程式設計語言與5對5的大小的雜湊表進行列表上大量尋找的看到效果，雜湊表的版本實際上是慢的。但它增加到50或5000和列表的版本慢如蝸牛，它的O（N）與O（1）的雜湊表。 但是，改變這種查詢是對ID列，而不是Name你會看到一個完全不同的故事。在這種情況下，它嵌套迴圈兩個查詢，但INNER JOIN版本能夠取代索引掃描與尋求之一-這意味著這簡直就一個數量級更快了大量的行。 所以或多或少在什麼之上幾個段落，這是幾乎可以肯定的索引或索引覆蓋問題，與一個或多個非常小的表。這些是僅有的情況下使用的SQL Server可能會選擇一個更壞的執行計畫INNER JOIN比LEFT JOIN。 
2. 有可能導致外部聯結比內部更快地聯結尚未又一個重要的情境。 外串連，最佳化一直是free的，從執行計畫下降外串連表，如果串連列是外部表格中的PK，並沒有列是從外部表格中選擇。例如SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY和B.KEY是PK的B.兩個甲骨文（我相信我釋放10）和SQL Server（2008 R2）從執行計畫trimB表。 的未必是真實的內部聯結：SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY可能會或可能不會要求乙方在執行計畫根據什麼constraints存在。 如果A.KEY是空的外鍵引用B.KEY，那麼最佳化器不能從它必須確認A B行存在，每一個行的計劃落乙。 如果A.KEY是一個強制性的外鍵引用B.KEY，那麼最佳化器可以自由地從該計劃的constraints保證該行的存在降乙。但是，僅僅最佳化器可以從該計劃刪除表，沒有按‘會的。 SQL Server 2008的R2不會從該計劃跌落乙級。甲骨文10沒有從計劃跌落乙級。這是很容易看到的外部如何串連將在效能在這種情況下，內部聯結的SQL Server上。 這是一個簡單的例子，而不是實際的一個獨立的查詢。為什麼要加入到一個表，如果你不需要？ 而設計的觀點時，這可能是一個非常重要的設計考慮。經常是“做一切”的觀點是建立聯結一切可能需要涉及到中央台。 （尤其是如果有做adhoc查詢不理解關聯式模式）視圖可以包括來自多個表中的所有相應和列。但是從視圖中的表的子集的可能只訪問列。如果表的串連與外串連，那麼最佳化器可以（不）從計劃落了人需要的表。 關鍵是要確保該外部聯結給出了正確的結果。正如Aaronaught所說的-你不能一味替代OUTER JOIN的INNER JOIN和expect的結果。但是，出於效能原因的觀點是何時能。 最後一個注意-我沒有測試過對效能的影響，鑒於上述情況，但在理論上似乎你應該能夠安全地替換INNER JOIN與OUTER JOIN，如果您還添加了狀態<FOREIGN_KEY> IS NOT空的地方 
3. 我所知道的幾起案例，其中左串連已經比內連線速度更快。 其根本原因，我能想到的是這樣的： 如果你有兩個表與您攜手與索引（兩個表）的列。 內部聯結將產生的結果不管你迴圈遍曆上表中的一個，並配以索引表中的兩個索引中的條目，如果你會做相反的：在迴圈中的條目索引表中的兩個，並配以指數表中的一個。 問題是，當你有舊的統計資料，我查詢最佳化索引的統計資訊來找到表與至少匹配的條目（根據你的其他條件）。 如果你有兩個表中的每個100萬，在表中的一個有10行匹配，並在表中的兩個有100000行匹配。最好的辦法是做一個索引掃描的表之一，並在表中兩個匹配10。相反將是一個索引掃描的遍曆100000行和嘗試匹配100000，只有10所以，如果統計不正確的最佳化器可能會選擇錯誤的表和索引遍曆。 如果最佳化器選擇最佳的左串連在它被寫入命令將執行比內串連好。 但是，最佳化器也可以最佳化左串連子最佳化的左半聯結。為了使選擇你想你FORCE ORDER提示之一。 
4. 試試這兩個查詢（一個與內部和左串連）與OPTION (FORCE ORDER)在結束後的結果。OPTION (FORCE ORDER)是一個查詢提示強制最佳化器與您在查詢中提供的串連順序產生執行計畫。 如果INNER JOIN開始執行一樣快LEFT JOIN，它是 在一個完全由INNER JOINs時，串連順序並不重要。這給自由查詢最佳化下令其認為合適的聯結，所以該問題可能依靠最佳化上。 同LEFT JOIN，這是不是這樣的改變串連順序將改變查詢的結果。引擎必須按照您提供的查詢，這可能比最佳化的更好的串連順序。 不知道這個回答你的問題，但我曾經在這特色的查詢進行計算的一個項目，其中最多的最佳化。我們有情況下，一個FORCE ORDER將減少一個查詢的執行從5分鐘至10秒。 
5. 您的效能問題更可能是串連你正在做的和你要加入的列是否具有索引或數量不限的。 你可以很容易地做9全表最壞的情況下會掃描每個加盟。 
6. 已經做了一些左外部和內部串連和一直沒能找到一個consisten之間的差異。有許多變數。我工作的一個報告資料庫中有很多的表有大量的欄位，在（providers版本和當地背景工作執行緒）的許多變化。這是不可能建立所有覆蓋索引等多種查詢的需要和處理曆史資料。已經看到的內層查詢kill伺服器效能兩個大（百萬到上千萬行）表是內部加入了兩個拉大了許多領域，並沒有覆蓋索引的存在。 但最大的問題，似乎並沒有在上面appeaer。也許你的資料庫是精心設計與觸發器和精心設計的交易處理，以確保良好的資料。礦經常有，他們沒有預計NULL值。是的表定義可以執行無空值，但不是在我的選項 所以，問題是...你設計你的查詢只對速度，交易處理更高優先順序運行一分鐘的代碼。或者你去的準確性，一個左外串連將提供。該內部串連必須找到雙方的，所以一個意想不到的空不僅會從兩個表中刪除資料，但有可能整個行和它發生這麼好聽，不 你可以非常快地得到90％所需要的資料，並沒有發現內部串連有默默的內部串連可以更快，但我不相信任何人做這樣的假設，除非他們已審閱的執行計畫。速度是重要的，但是精確度更重要。

INNER JOIN與LEFT JOIN在SQL Server的效能