如何在沒有提示的情況下強制合併連接
前段時間我使用查詢提示，但後來意識到大多數時候 SQL Server 比我更聰明。而且最好建構一個額外的索引/重組數據或查詢並獲得比強制伺服器使用計劃更好的結果這通常效率低下，但對於某些數據子集來說足夠快。但是現在我處於不知道如何更好地組織數據的情況。
我有兩張桌子。第一個表 T1 是 (Id, CustomerId)，第二個表 T2 具有相同的列。我想在 CustomerId 上加入 T1 到 T2。並獲得前 N 行。在這種情況下，優化器看到我只需要 N 個 top 並說：“嘿，我將使用循環並很快找到 N 個匹配項，尤其是當我使用索引搜尋時。” 但它不起作用，因為沒有滿足條件的數據。因此它使用 a
loop join來連接一個 25m 的表和一個 100k 的表，這非常慢。
當我強制 SQL Server 使用合併連接時，我得到以下計劃，該計劃在一秒鐘內執行：
我不想強迫它有兩個原因：
- 首先，正如我之前所說，SQL Server 足夠智能。
- 其次，我使用的是 ORM，很難在生成的查詢中註入提示，所以我想避免它。
在這種情況下我該怎麼辦？
要在沒有提示的情況下從嵌套循環聯接切換到合併聯接，您需要具有合併聯接的計劃的估計成本低於具有嵌套循環的計劃。從技術上講，您還需要查詢優化器在計劃探索期間找到成本較低的計劃，但您對此無能為力。
首先，關於合併連接成本的說明。根據我的經驗，SQL Server 對合併連接成本相當悲觀。當您考慮到合併連接的 IO 要求在少量數據更改時會發生多少變化時，這似乎是可以理解的。考慮一個簡單的範例，其中一個表具有 1 到 10000 的整數，而另一個表具有 100001 到 1100000 的整數：
DROP TABLE IF EXISTS X_SMALL_TABLE; CREATE TABLE X_SMALL_TABLE (ID INT NOT NULL PRIMARY KEY (ID)); INSERT INTO X_SMALL_TABLE WITH (TABLOCK) (ID) SELECT N FROM dbo.GetNums(10000); UPDATE STATISTICS X_SMALL_TABLE WITH FULLSCAN; DROP TABLE IF EXISTS X_LARGE_TABLE; CREATE TABLE X_LARGE_TABLE (ID INT NOT NULL PRIMARY KEY (ID)); INSERT INTO X_LARGE_TABLE WITH (TABLOCK) (ID) SELECT N + 100000 FROM dbo.GetNums(1000000); UPDATE STATISTICS X_LARGE_TABLE WITH FULLSCAN;如果我將表連接在一起，我將返回 0 行。如果我強制執行，
MERGE JOIN那麼 SQL Server 將掃描小表中的所有行，並且只掃描大表中的一行。預設情況下，SQL Server 會根據連接鍵升序遍歷表。但是，查詢優化器估計將從大表中掃描所有 1000000 行，並隨後為該運算符分配相對較大的成本：
從實際計劃中我們可以看到，只掃描了一行。如果我們只在小表中插入一個新行會發生什麼？
INSERT INTO X_SMALL_TABLE SELECT 1200000; UPDATE STATISTICS X_SMALL_TABLE WITH FULLSCAN;由於
ID一旦我們在小表中達到 10000，合併連接算法就會按順序執行，ID我們應該遍歷大表中的所有行，直到達到ID1200000。
實際計劃確認從兩個表中讀取了所有行。因此，數據的微小變化可能會導致合併連接性能的巨大差異。基於此，SQL Server 對合併聯接成本持悲觀態度似乎是合理的，如果您試圖讓合併聯接出現在查詢中，這將對您不利。在您的第二個查詢中，您掃描來自
CustomerDiscountCards. 僅該操作就可能比整個嵌套循環連接計劃具有更高的估計成本。使用提示強制
MERGE JOIN注意時，MERGE JOIN連接提示還會FORCE ORDER向查詢添加提示。根據問題中的資訊，您的第二個查詢由於MERGE JOIN. 由於新的加入順序，它執行得很快。優化器連接在一起CustomerDiscountCards並DiscountCards在兩個表上使用全掃描並找到 0 個匹配的行。任何針對 0 行結果集的內部連接都會很快，因為查詢執行可以在該點停止（忽略在此之前發生的阻塞運算符）。因此，與其專注於強制合併連接（如果沒有提示可能會很困難），我將專注於讓連接順序正確。強制連接順序的一種簡單方法是將您想要的第一個結果集具體化到臨時表中。您可以加入初始查詢並將匹配的相關行放入臨時表中
CustomerDiscountCards。DiscountCards對於您的測試案例，這應該在一秒鐘內執行，因為您實際上不會將任何行插入到臨時表中。如果您的第二個查詢連接到空表，它應該幾乎立即完成。您將需要測試其他參數值的查詢性能，因為您可能會將相當數量的行和列放入 tempdb。您可以重寫您的 SQL 以有效地強制連接順序，而無需使用臨時表。當然，使用
FORCE ORDER提示是最簡單的方法，但是您說您不想使用提示，並且該提示可能很難使用。它適用於整個查詢，因此如果您的數據發生變化，您最終可能會表現不佳。對於如何在沒有提示的情況下強制連接順序的想法，它看起來像將要在派生表中首先連接的表配對並添加多餘的TOP運算符似乎將 SQL Server 推向正確的方向：INNER JOIN ( SELECT TOP 9223372036854775807 ... FROM CustomerDiscountCards INNER JOIN DiscountCards ON ... ) t ON ...但是，我無法得出結論，SQL Server 是否真的被迫與查詢的其餘部分分開評估該聯接。和以前一樣，您需要進行測試。
為了給你一個最終的選擇，因為最初的問題似乎與行目標有關，你可以對優化器隱藏行目標。實現此目的的一種方法是使用OPTIMIZE FOR查詢提示並為您的
TOP語句使用變數。考慮下面的查詢片段：DECLARE @TOP BIGINT = 10; SELECT TOP (@top) ... OPTION (OPTIMIZE FOR (@TOP = 9223372036854775807))該查詢將僅返回它在結果集中找到的前 10 行。但是，將創建該計劃，就好像它返回前 9223372036854775807 行一樣。這可能不利於您遇到問題的嵌套循環連接計劃，但很難說這通常會表現如何。
最終，很難用我們在問題中獲得的資訊說更多。假設您的查詢在某些情況下可以返回行（沒有太多理由持續執行始終不返回任何行的查詢），因此您需要針對所有不同情況測試您的選項。
