SQL Server DRI（ON DELETE CASCADE）运行缓慢吗？ [英] Is SQL Server DRI (ON DELETE CASCADE) slow?

问题描述

我一直在分析其中一个与删除操作特别慢有关的重复出现的错误报告（性能问题）。长话短说：看来 CASCADE DELETE 键在很大程度上是负责任的，我想知道（a）这是否有意义，以及（b）为什么如此

I've been analyzing a recurring "bug report" (perf issue) in one of our systems related to a particularly slow delete operation. Long story short: It seems that the CASCADE DELETE keys were largely responsible, and I'd like to know (a) if this makes sense, and (b) why it's the case.

我们有一个小部件的模式，这些小部件位于相关表和相关表的大图的根部，依此类推。 。十分清楚，我们不鼓励从该表中删除；这是核选项，用户丝毫没有幻想。尽管如此，有时有时还是要做。

We have a schema of, let's say, widgets, those being at the root of a large graph of related tables and related-to-related tables and so on. To be perfectly clear, deleting from this table is actively discouraged; it is the "nuclear option" and users are under no illusions to the contrary. Nevertheless, it sometimes just has to be done.

模式看起来像这样：

Widgets
   |
   +--- Anvils [1:1]
   |    |
   |    +--- AnvilTestData [1:N]
   |
   +--- WidgetHistory (1:N)
        |
        +--- WidgetHistoryDetails (1:N)

列定义如下所示：

Widgets (WidgetID int PK, WidgetName varchar(50))
Anvils (AnvilID int PK, WidgetID int FK/IX/UNIQUE, ...)
AnvilTestData (AnvilID int FK/IX, TestID int, ...Test Data...)
WidgetHistory (HistoryID int PK, WidgetID int FK/IX, HistoryDate datetime, ...)
WidgetHistoryDetails (HistoryID int FK/IX, DetailType smallint, ...)

真的没有什么太吓人的。 小部件可以是不同的类型， Anvil 是一种特殊类型，因此关系为1：1（或更大）。准确地1：0..1）。然后会有大量数据-随着时间的推移，每个 Anvil 可能收集数千行 AnvilTestData

Nothing too scary, really. A Widget can be different types, an Anvil is a special type, so that relationship is 1:1 (or more accurately 1:0..1). Then there's a large amount of data - perhaps thousands of rows of AnvilTestData per Anvil collected over time, dealing with hardness, corrosion, exact weight, hammer compatibility, usability issues, and impact tests with cartoon heads.

然后每个小部件都具有腐蚀，精确的重量，锤子兼容性，可用性问题以及使用卡通头的冲击测试。各种交易的悠久而无聊的历史-生产，库存移动，销售，缺陷调查，RMA，维修，客户投诉等。单个小部件可能有10-20k个细节，或根本没有，具体取决于它

Then every Widget has a long, boring history of various types of transactions - production, inventory moves, sales, defect investigations, RMAs, repairs, customer complaints, etc. There might be 10-20k details for a single widget, or none at all, depending on its age.

因此，毫不奇怪，这里的每个级别都有一个 CASCADE DELETE 关系。如果需要删除小部件，则意味着出现了严重错误，我们需要删除该小部件一直存在的任何记录，包括其历史记录，测试数据等。

So, unsurprisingly, there's a CASCADE DELETE relationship at every level here. If a Widget needs to be deleted, it means something's gone terribly wrong and we need to erase any records of that widget ever existing, including its history, test data, etc. Again, nuclear option.

所有关系均已建立索引，统计数据均为最新。普通查询速度很快。该系统趋向于平稳地进行除删除操作以外的所有操作。

Relations are all indexed, statistics are up to date. Normal queries are fast. The system tends to hum along pretty smoothly for everything except deletes.

最后，由于各种原因，我们只允许一次删除一个小部件，因此在这里很重要。删除语句如下所示：

Getting to the point here, finally, for various reasons we only allow deleting one widget at a time, so a delete statement would look like this:

DELETE FROM Widgets
WHERE WidgetID = @WidgetID

对于小部件而言，非常简单，无害的删除... 要花费超过2分钟的时间 没有数据！

Pretty simple, innocuous looking delete... that takes over 2 minutes to run, for a widget with no data!

在执行计划中苦苦挣扎之后，我终于能够挑选出 AnvilTestData 和 WidgetHistoryDe​​tails 会删除为成本最高的子操作。因此，我尝试关闭 CASCADE （但保留实际FK，只需将其设置为 NO ACTION ）并重写脚本如下所示：

After slogging through execution plans I was finally able to pick out the AnvilTestData and WidgetHistoryDetails deletes as the sub-operations with the highest cost. So I experimented with turning off the CASCADE (but keeping the actual FK, just setting it to NO ACTION) and rewriting the script as something very much like the following:

DECLARE @AnvilID int
SELECT @AnvilID = AnvilID FROM Anvils WHERE WidgetID = @WidgetID

DELETE FROM AnvilTestData
WHERE AnvilID = @AnvilID

DELETE FROM WidgetHistory
WHERE HistoryID IN (
    SELECT HistoryID
    FROM WidgetHistory
    WHERE WidgetID = @WidgetID)

DELETE FROM Widgets WHERE WidgetID = @WidgetID

这两项优化均带来了显着的加速，每一次都将执行时间缩短了近一分钟，因此，原来的2分钟删除操作现在大约需要5-10秒-至少对于 new 小部件而言，没有太多的历史记录或测试数据。

Both of these "optimizations" resulted in significant speedups, each one shaving nearly a full minute off the execution time, so that the original 2-minute deletion now takes about 5-10 seconds - at least for new widgets, without much history or test data.

请绝对清楚，仍然有来自 WidgetHi的CASCADE 故事到 WidgetHistoryDe​​tails ，其中扇出度最高，我只删除了一个 Widgets 。

Just to be absolutely clear, there is still a CASCADE from WidgetHistory to WidgetHistoryDetails, where the fanout is highest, I only removed the one originating from Widgets.

级联关系的进一步扁平化导致逐渐减少的戏剧性但仍然明显的加速，以至于删除了 new 小部件一旦删除所有对较大表的级联删除并将其替换为显式删除，几乎是瞬时的。

Further "flattening" of the cascade relationships resulted in progressively less dramatic but still noticeable speedups, to the point where deleting a new widget was almost instantaneous once all of the cascade deletes to larger tables were removed and replaced with explicit deletes.

我正在使用 DBCC DROPCLEANBUFFERS 和 DBCC FREEPROCCACHE 。我已禁用了所有可能导致速度进一步降低的触发器（尽管无论如何它们都会出现在执行计划中）。而且我也正在针对较旧的小部件进行测试，并且注意到那里的速度也有了明显提高；删除原本需要5分钟才能完成的操作，现在需要20-40秒。

I'm using DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each test. I've disabled all triggers that might be causing further slowdowns (although those would show up in the execution plan anyway). And I'm testing against older widgets, too, and noticing a significant speedup there as well; deletes that used to take 5 minutes now take 20-40 seconds.

现在，我是选择永不中断理念的热心支持者，但是除了 CASCADE DELETE 关系的令人沮丧，令人难以置信的低效率外，似乎对这种行为没有任何逻辑上的解释。

Now I'm an ardent supporter of the "SELECT ain't broken" philosophy, but there just doesn't seem to be any logical explanation for this behaviour other than crushing, mind-boggling inefficiency of the CASCADE DELETE relationships.

所以，我的问题是：

• 这是SQL Server中DRI的已知问题吗？（我似乎无法在Google或SO中找到对此事的任何引用；我怀疑答案是否定的。）

• Is this a known issue with DRI in SQL Server? (I couldn't seem to find any references to this sort of thing on Google or here in SO; I suspect the answer is no.)

如果没有，是否还有我所看到的行为的另一种解释？

如果是已知问题，为什么会出现问题，并且我可以使用更好的解决方法？

推荐答案

SQL Server 最适合基于集合的操作，而 CASCADE 的删除方式最适合其性质，以记录为基础。

SQL Server is best at set-based operations, while CASCADE deletions are, by their nature, record-based.

SQL Server 与其他服务器不同，它尝试优化基于立即集的操作，但是它可以工作仅深一层。它需要删除上级表中的记录才能删除下级表中的记录。

SQL Server, unlike the other servers, tries to optimize the immediate set-based operations, however, it works only one level deep. It needs to have the records deleted in the upper-level tables to delete those in the lower-level tables.

换句话说，级联操作是上下运行的，而您的解决方案可以向下运行，这是基于集合的且更有效。

In other words, cascading operations work up-down, while your solution works down-up, which is more set-based and efficient.

以下是示例架构：

CREATE TABLE t_g (id INT NOT NULL PRIMARY KEY)

CREATE TABLE t_p (id INT NOT NULL PRIMARY KEY, g INT NOT NULL, CONSTRAINT fk_p_g FOREIGN KEY (g) REFERENCES t_g ON DELETE CASCADE)

CREATE TABLE t_c (id INT NOT NULL PRIMARY KEY, p INT NOT NULL, CONSTRAINT fk_c_p FOREIGN KEY (p) REFERENCES t_p ON DELETE CASCADE)

CREATE INDEX ix_p_g ON t_p (g)

CREATE INDEX ix_c_p ON t_c (p)

，此查询：

DELETE
FROM    t_g
WHERE   id > 50000

及其计划：

  |--Sequence
       |--Table Spool
       |    |--Clustered Index Delete(OBJECT:([test].[dbo].[t_g].[PK__t_g__176E4C6B]), WHERE:([test].[dbo].[t_g].[id] > (50000)))
       |--Index Delete(OBJECT:([test].[dbo].[t_p].[ix_p_g]) WITH ORDERED PREFETCH)
       |    |--Sort(ORDER BY:([test].[dbo].[t_p].[g] ASC, [test].[dbo].[t_p].[id] ASC))
       |         |--Table Spool
       |              |--Clustered Index Delete(OBJECT:([test].[dbo].[t_p].[PK__t_p__195694DD]) WITH ORDERED PREFETCH)
       |                   |--Sort(ORDER BY:([test].[dbo].[t_p].[id] ASC))
       |                        |--Merge Join(Inner Join, MERGE:([test].[dbo].[t_g].[id])=([test].[dbo].[t_p].[g]), RESIDUAL:([test].[dbo].[t_p].[g]=[test].[dbo].[t_g].[id]))
       |                             |--Table Spool
       |                             |--Index Scan(OBJECT:([test].[dbo].[t_p].[ix_p_g]), ORDERED FORWARD)
       |--Index Delete(OBJECT:([test].[dbo].[t_c].[ix_c_p]) WITH ORDERED PREFETCH)
            |--Sort(ORDER BY:([test].[dbo].[t_c].[p] ASC, [test].[dbo].[t_c].[id] ASC))
                 |--Clustered Index Delete(OBJECT:([test].[dbo].[t_c].[PK__t_c__1C330188]) WITH ORDERED PREFETCH)
                      |--Table Spool
                           |--Sort(ORDER BY:([test].[dbo].[t_c].[id] ASC))
                                |--Hash Match(Inner Join, HASH:([test].[dbo].[t_p].[id])=([test].[dbo].[t_c].[p]))
                                     |--Table Spool
                                     |--Index Scan(OBJECT:([test].[dbo].[t_c].[ix_c_p]), ORDERED FORWARD)

首先， SQL Server 从 t_g 删除记录，然后加入用<$ c删除的记录$ c> t_p 并从后者中删除，最后，连接从中删除的记录 t_p 和 t_c ，并从 t_c 中删除​​。

First, SQL Server deletes records from t_g, then joins the records deleted with t_p and deletes from the latter, finally, joins records deleted from t_p with t_c and deletes from t_c.

在这种情况下，单三张表的连接效率会更高，这就是您的解决方法。

A single three-table join would be much more efficient in this case, and this is what you do with your workaround.

如果让您感觉更好， Oracle 不会以任何方式优化级联操作：它们始终是 NESTED LOOPS 如果您忘记在引用列上创建索引，上帝会为您提供帮助。

If it makes you feel better, Oracle does not optimize cascade operations in any way: they are always NESTED LOOPS and God help you if your forgot to create an index on the referencing column.

这篇关于SQL Server DRI（ON DELETE CASCADE）运行缓慢吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！