删除主键需要多长时间? [英] How long should a Primary Key delete take?

问题描述

描绘一个简单的表结构:

Picture a simple table structure:

Table1        Table2
----------    ----------
ID<-------|   ID
Name      |-->Table1ID
              Name

Table1 有几百万行(例如 350 万行).我通过主键发出删除:

Table1 has a few million rows (say 3.5 million for example). I issue a delete by Primary Key:

DELETE FROM Table1 WHERE ID = 100;

Table2 中没有引用 Table1 且 ID = 100 的行，因此删除操作不会违反任何外键约束.

There is no row in Table2 that references Table1 with ID = 100, so the delete works without violating any Foreign Key constraints.

您预计删除需要多长时间?几毫秒的数量级?几百毫秒?一秒钟或更长时间?几秒钟?等等，假设机器没有陷入困境并且可以轻松处理请求.

How long would you expect the delete to take? On the order of a few milliseconds? A few hundred milliseconds? A second or more? A few seconds? Etc., assuming the machine is not bogged down and readily handles the request.

现在，我遇到了这样的删除操作需要大约 700 毫秒的情况.对我来说，这似乎太慢了.我很好奇我是否不在基地，或者其他人是否认为这太慢了，以及有助于加快速度的建议！

Now, I have this situation where a delete like this is taking around 700ms. To me, this seems too slow. I'm curious if I'm off-base or if others agree this is too slow, and recommendations to help make it faster!

这是实际的执行计划:

(此处为 XML 执行计划:http://pastebin.com/q9hSMLi3)

(XML Execution plan here: http://pastebin.com/q9hSMLi3)

聚集索引删除 (81%) 命中聚集 PK、非聚集唯一索引和非聚集非唯一索引.

The Clustered Index Delete (81%) hits the Clustered PK, a Non-Clustered Unique Index, and a Non-Clustered Non-Unique Index.

推荐答案

问题是用于验证外键的聚集索引扫描.

The issue is the clustered index scan to validate the foreign key.

当删除成功并且没有匹配的记录会导致违规时，则需要扫描所有table2.该表有 1,117,190 行，因此这是一项开销很大的操作，绝对可以从索引中受益.

When the delete succeeds and there are no matching records that would cause a violation then all of table2 needs to be scanned. This table has 1,117,190 rows so this is an expensive operation that could definitely benefit from an index.

执行计划中显示的 10% 数字只是基于某些建模假设的估计.

The 10% figure shown in the execution plan is just an estimate based on certain modelling assumptions.

整个计划的成本为 0.0369164，表 2 上的扫描成本为 0.0036199，其余所有费用为 0.0332965.但是请注意，对于聚集索引扫描运算符，估计 CPU 成本为 1.22907，估计 IO 成本为 10.7142(总计 11.94327 而不是 0.0369164).

The entire plan is costed at 0.0369164 with the scan on table 2 costed at 0.0036199 and everything else accounting for the remaining 0.0332965. However notice that for the clustered index scan operator the Estimated CPU Cost is 1.22907 and Estimated IO Cost is 10.7142 (totaling 11.94327 not 0.0369164).

这种差异的原因是扫描是在反半连接运算符下进行的，一旦找到匹配的行，扫描就会停止.估计子树成本在建模假设下按比例缩小，即这将在仅扫描表的很小一部分后发生.

The reason for this discrepancy is that the scan is under an anti semi join operator and the scan can stop as soon as a matching row is found. The estimated subtree cost is scaled down under the modelling assumption that this will happen after only a very small proportion of the table has been scanned.

如果没有 FK 违规并且删除成功，则需要扫描整个表，因此使用未按比例缩小的数字会提供更多信息.

In the case that there are no FK violations and the delete succeeds then the entire table needs to be scanned so it would be more informative to use the unscaled down figure.

如果使用 11.94327 成本重新计算百分比，该操作代表实际发生的完整扫描，则该扫描操作显示为计划成本的 99.7% (11.94327/(11.94327 + 0.0332965)).

If the percentages are reworked out using the 11.94327 cost for that operator that represents the full scan that happened in practice then this scan operator shows up as being 99.7% of the plan cost (11.94327 / (11.94327 + 0.0332965)).

这篇关于删除主键需要多长时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！