為什麼 SQL Server 使用聚集索引掃描進行自引用 FK 級聯刪除

我舉了一個例子來說明我的問題是什麼：

設置：

CREATE TABLE [dbo].[Test](
   [TestId] [bigint] IDENTITY(1,1) NOT NULL,
   [ParentTestId] [bigint] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestId] ASC)
)
GO

ALTER TABLE [dbo].[Test]  WITH CHECK ADD  CONSTRAINT [FK_Test_ParentTest] 
     FOREIGN KEY([ParentTestId])
REFERENCES [dbo].[Test] ([TestId])
GO

ALTER TABLE [dbo].[Test] CHECK CONSTRAINT [FK_Test_ParentTest]
GO

DECLARE @iter INT
SET @iter = 1
WHILE @iter < 1000
BEGIN
   INSERT INTO dbo.Test ( ParentTestId )
   VALUES  ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
   ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
   ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
   ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),
   ( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null ),( null )
   SET @iter = @iter + 1
END
go

這將創建一個自引用表並向其中添加超過 40,000 行。

行動：

DELETE FROM dbo.Test WHERE TestId = 200

然後我想從這個表中刪除一行。如果您打開實際查詢計劃並執行上述語句，您可以看到 20% 的成本用於自引用鍵的聚集索引掃描。

在這個場景中這沒什麼大不了的，但我的真實場景在一個大表中有超過 2500 萬行。

所以，我有兩個問題：

1. 為什麼要進行索引掃描？ 它有一個主鍵/聚集索引值。為什麼它不進行索引搜尋？
2. 我怎樣才能讓它做一個索引搜尋？（刪除一行大約需要 1 分鐘。）

編輯：我認為這可能是由於 SQL Server 正在查看其他行是否引用了我正在刪除的行。但是我將“強制外鍵約束”設置為“否”，並且執行聚集索引掃描仍然需要相同的成本。

它需要驗證您嘗試刪除的行不是現有行的父行。

您在 上沒有索引ParentTestId。

所以它必須進行掃描。

CREATE NONCLUSTERED INDEX ix ON  [dbo].[Test](ParentTestId)

然後你看到一個搜尋。

順便說一句：在這種情況下，20% 的估計掃描成本可能被低估了。

FK 驗證在左半連接下，SQL Server 會花費它，好像只需要部分掃描，它會找到匹配的行並且刪除將失敗。

據推測，您實際刪除的行往往會成功，因此需要進行全面掃描以驗證沒有衝突的行。

使用跟踪標誌4138關閉行目標

DELETE FROM dbo.Test
WHERE  TestId = 200 
OPTION (querytraceon 4138 )

重新計算成本的計劃顯示 CI 掃描為 100% 而不是 20%（因為它現在假設需要進行全面掃描）

估計成本的這種差異足以顯示缺失的索引建議。

然而，該計劃中顯示的成本仍然不是很有代表性。您可能會注意到它們加起來高達 219%。

此外，帶有和不帶有跟踪標誌的查詢的總體計劃成本在0.0168268. 實際上，完整 CI 掃描的成本應為0.152373( 0.0485075 + 0.103866)

但它的上限似乎不超過原始計劃成本（並且總體計劃成本也沒有向上調整，因此百分比不正確）

引用自：https://dba.stackexchange.com/questions/57018