IGNORE_DUP_KEY = ON 的索引過濾掉非重複記錄
下面是一個腳本，可用於重現我面臨的問題。基本上問題是這樣的：為什麼 (ID1 = 6, ID2 = 7) 的記錄沒有填充到最終的#tt2 表中？我可以手動插入它，所以它不會違反 UNIQUE 約束，但它不會作為 INSERT/SELECT 的一部分填充。
USE [tempdb] GO IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1 CREATE TABLE [#t1] ([ID] INT , [V] VARCHAR(10)) INSERT INTO #t1 (ID, V) VALUES (1,'A'), (2,'B'), (3,'B'), (4,'C'), (5,'E'), (6,'E') IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2 CREATE TABLE [#t2] ([ID] INT , [V] VARCHAR(10)) INSERT INTO #t2 (ID, V) VALUES (1,'A'), (2,'B'), (3,'C'), (4,'C'), (5,'D'), (6,'E'), (7,'E') IF OBJECT_ID('tempdb..#tt') IS NOT NULL DROP TABLE #tt SELECT t1.ID AS ID1, t2.ID AS ID2, t1.V AS V INTO #tt FROM #t1 t1 JOIN #t2 t2 ON t1.V=t2.V --SELECT * FROM #tt IF OBJECT_ID('tempdb..#tt2') IS NOT NULL DROP TABLE #tt2 CREATE TABLE #tt2 (ID1 INT, ID2 INT, V VARCHAR(10)) CREATE UNIQUE INDEX IDX_TT_ID1 ON #tt2 (ID1) WITH (IGNORE_DUP_KEY = ON) CREATE UNIQUE INDEX IDX_TT_ID2 ON #tt2 (ID2) WITH (IGNORE_DUP_KEY = ON) -- Query 1 - this SELECT returns 9 records. SELECT ID1, ID2, V FROM #tt ORDER BY ID1, ID2 -- Query 2 - this INSERT populates 4 records, but I would expect 5 INSERT INTO #tt2 (ID1, ID2, V) SELECT ID1, ID2, V FROM #tt ORDER BY ID1, ID2 -- Why are there only 4 records in this table? (I would expect 5, but the record with ID1 = 6, ID2 = 7 is missing) SELECT * FROM #tt2 -- I can INSERT the missing record manually: INSERT INTO #tt2 (ID1, ID2, V) VALUES (6, 7, 'E')
我認為出現混淆是因為索引分別定義為 ID1 和 ID2 上的單列。這意味著對每個單獨執行“忽略重複”檢查，並且只有當一行通過兩項檢查時，它才會被插入到最終的 #tt2 表中。我會一步一步來展示工作。
系統檢查重複項的一種方法是對行進行排序，然後按順序處理它們。這就是我的本地（2017）實例上發生的情況。那麼讓我們看一下#tt的內容，有序：
Row ID1 ID2 V i 1 1 A ii 2 2 B iii 3 2 B iv 4 3 C v 4 4 C vi 5 6 E vii 5 7 E iix 6 6 E ix 6 7 E從第 i 行開始，系統會詢問“我已經看到 ID1=1 還是已經看到 ID2 = 1？” 由於兩者都是假行，因此將 i 傳遞到輸出。與第二行類似。然而，對於第 iii 行，ID2=2 與已經看到的第 ii 行匹配。所以第三行被拒絕。接受第 iv 行。第 v 行被拒絕，因為它與第 iv 行具有相同的 ID1 值。接受第 vi 行。第 vii 行被拒絕 - ID1=5 與第 vi 行匹配。第 iix 行被拒絕，因為 ID2=6 匹配第 vi 行。最後，第 ix 行在 ID1（匹配 iix）和 ID2（匹配 vii）中都被拒絕。
輸出是 i, ii, iv, vi 並且這些被插入到#tt2。
Row ID1 ID2 V i 1 1 A ii 2 2 B iv 4 3 C vi 5 6 E可以在之後插入 {6,7,E}，因為它與通過過濾並實際插入 #tt2 的任何行不衝突。ID1=6 的所有行由於其 ID2 值而被消除，而 ID2=7 的行由於其 ID1 值而被消除。
以上是產生觀察輸出的邏輯算法的解釋。正如在實際執行計劃中所觀察到的，該算法的物理實現是不同的，但等效的。這是計劃： 圖 1 - INSERT 的實際執行計劃
物理計劃按 ID1（圖像中的 A）排序，並為每個值 (B) 取第一行。然後將其輸出按 ID2 (C) 排序，並且再次保留每個值的第一行 (D)。無論哪一行通過兩個過濾器，都將插入#tt2。
這裡棘手的部分是“第一”這個詞。關係數據庫表沒有內在順序。查詢優化器 (QO) 可以自由地以它選擇的任何順序處理行，只要它的結果在邏輯上等同於送出的 SQL。這讓我想知道是否可以在不更改查詢的情況下從相同的數據產生不同的結果。我可以。
排序很昂貴，因此如果 QO 有其他選項，它通常會選擇其他選項。一種這樣的選擇是讀取索引。我發現在#tt.ID1 上有一個索引，QO 會使用它並跳過排序。SQL Server 的排序是保留順序的，因此如果我可以在備用排序中顯示 ID2，將為每個 ID1 值選擇不同的“第一”行，並且整體輸出將不同。我創建了一個與#tt2 相同的新目標表#tt3 並執行了它（ORDER BY 不影響這個INSERT）
CREATE CLUSTERED INDEX CIX ON #tt(ID1 ASC, ID2 DESC); INSERT INTO #tt3 (ID1, ID2, V) SELECT ID1, ID2, V FROM #tt;製作了這個
Row ID1 ID2 V i 1 1 A ii 2 2 B v 4 4 C vii 5 7 E這與之前的結果 i、ii、iv、vi 不同。
您會注意到，如果您按 ID1 asc, ID1 desc .. 對 #tt 的內容進行排序。
Row ID1 ID2 V i 1 1 A ii 2 2 B iii 3 2 B v 4 4 C iv 4 3 C vii 5 7 E vi 5 6 E ix 6 7 E iix 6 6 E.. 並應用邏輯“我見過”算法獲得此替代結果。
問題仍然存在，為什麼 QO 先處理 ID1 再處理 ID2？我相信這是由定義約束的順序驅動的。我原以為將 #tt 聚集索引定義為 (ID2 asc, ID1 desc) 會導致 ID2 首先被處理，因為這樣可以避免排序。它不是。它仍然首先處理 ID1，並帶回排序運算符。實際上，在 V 列中創建另一個約束會相應地將其添加到計劃中。似乎 sys.indexes.index_id 順序中的處理可能在 QO 中被硬編碼？
總之，我認為建議謹慎行事。物理層的變化會影響這個查詢的結果。如果目標表的約束被刪除並重新創建，我們最好希望它們以與以前相同的順序返回。如果源表經過優化，比如使用索引，它可能會影響訪問路徑以及哪一行“最先”出現。這沒有考慮分配順序掃描或旋轉木馬掃描。
如果涉及更多行，則 QO 很可能會換成使用散列連接。天知道這會如何影響事情。
