使用許多外鍵調整級聯刪除的性能

November 22, 2019

我有一個需要很長時間的刪除查詢。查看執行計劃，我看到刪除查詢中的大部分估計成本都在數據模型的一部分中，該部分包含大量數據（例如，400k 行），這看起來不錯，但我不明白一件事.
數據模型的精簡視圖：
table ParentObject 
     int parentObjectId (PK)

table Child
     int childId (PK)
     int parentId (FK)
     &lt;stuff&gt;

table GrandChild
     int grandChildId (PK)
     int childId (FK)
     &lt;more stuff&gt;
其中一個父對象可能有 200,000 個子對象，而一個子對像有 2 個左右的 GrandChildren。我對調整以下性能感興趣：
DELETE FROM ParentObject WHERE parentObjectId = %d;
在 Grandchild 上，(childId, + 其他兩列) 以及主鍵索引上有一個額外的非聚集索引。在 child 上有一個額外的非聚集唯一索引（parentId，+另外兩列）。
我在查詢計劃中看到的是，在刪除 Grandchild 對象時，有兩個昂貴的排序操作與刪除混合在一起，我不明白它們為什麼會在那裡。
我應該看什麼來幫助這個刪除操作更快？需要排序嗎？如果我對 id 進行非規範化並將父 Id 添加到孫表中會有幫助嗎？我是否愚蠢地建立了索引？
完整的執行計劃在這裡。

為了直接回答您的主要問題，排序是為了呈現行以按索引鍵順序更新運算符（在這種情況下執行刪除）。這裡的工作原理是對鍵進行排序將促進對索引的順序訪問。
這可能是一個很好的優化，儘管細節取決於您的硬體、受影響的頁面在記憶體中的可能性以及排序是否可以在分配給它們的記憶體中完成。當優化器決定排序成本將通過與順序索引訪問相關的提高效率來償還時，它會DMLRequestSort在更新運算符上設置一個屬性：
優化器還可能決定將更新拆分為單獨的運算符以維護聚集索引（或堆），然後是非聚集索引。通常，它會決定多次排序 - 首先是聚集索引鍵，然後是非聚集索引。同樣，在排序被認為是最佳的情況下，每個索引更新運算符都將DMLRequestSort屬性設置為 true。
話雖如此，我首先要解決的問題是消除他們提供的連接運算符是嵌套循環連接的索引掃描，並刪除急切的索引線軸，每次查詢時都會將行插入空索引執行。急切的索引假離線通常是您缺少有用的永久索引的最明顯跡象。索引假離線運算符中的查找謂詞標識優化器希望索引的鍵。
缺少非聚集索引（需要急切索引假離線）的表範例如下：
child6gc8Selections
gc9s
child7s
gc6s
目前在嵌套循環連接下掃描的表範例如下：
child1
parentObjectMessages
child8s
child7s
child6s
child5s
child4s
child3s
child2s
以上圖為例，Clustered Index Scan 的輸出列表為Id, parentObjectId，Nested Loops Join 謂詞為child7s.parentObjectId = parentObject.Id，連接輸出列列表為child7s.Id。
child7s從該資訊來看，對於這部分查詢來說，一個很好的訪問方法（索引）似乎將作為一個包含的列parentObjectId進行鍵控。Id您應該能夠弄清楚如何最好地將其應用到您現有的索引策略中。
以下是優化器目前選擇散列連接的表範例。我會檢查這樣的表以確保這是一種合理的訪問方法：
child6gc8Selections
gc2s
gc5s
gc6Properties
該表child2bigChild還參與需要顯式排序的合併連接。同樣，我會檢查是否可以避免這種情況。
一旦基本的索引問題得到解決，我們可以在必要時查看其他優化。