主键删除需要多长时间？

Question

想象一个简单的表格结构：

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

Table1 有几百万行记录（例如说 350 万）。我使用主键进行删除操作：

DELETE FROM Table1 WHERE ID = 100;

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

您认为这个删除操作需要多长时间？几毫秒？几百毫秒？一秒钟或更长时间？几秒钟？假设机器没有被拖慢，并且可以轻松处理请求。

现在我面临这样一个情况：此类删除操作需要大约700ms的时间。对我来说，这似乎太慢了。我想知道是否有人同意这个速度太慢，以及如何提高其速度的建议！

以下是实际执行计划:

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

聚集索引删除（81%）涉及到聚集PK、非聚集唯一索引和非聚集非唯一索引。

- Josh M.

---

当你说“这个表有几百万行”时，你是在谈论Table1还是Table2？如果Table2有几百万行而且Table1ID上没有索引，那么这可能会非常慢。 - Mike Christensen

抱歉，Table1 有三百五十万行。 - Josh M.

是的，通过其主键删除单个行应该非常快。在Table2上进行查找可能是减速的原因。请问您能否发布执行计划？ - Mike Christensen

请问您能否发布XML计划？我对FK验证Scan持怀疑态度。它应该是对索引的查找。我还想检查删除操作符并查看受影响的索引。 - usr

@usr - 我认为您无法在非唯一索引中进行搜索，因此它正在执行扫描。看起来它花费大部分时间在聚集索引上，这将是Table1，对吗？我想知道为什么那部分需要这么长时间... - Mike Christensen

@usr - 在帖子中添加了XML执行计划链接。谢谢。 - Josh M.

Answer 1

问题在于聚集索引扫描以验证外键。当删除成功且没有匹配的记录会导致违规时，则需要扫描整个table2。该表有1,117,190行，因此这是一项昂贵的操作，可以从索引中受益。

执行计划中显示的10％数字仅基于某些建模假设的估算。

整个计划的成本为0.0369164，其中对table2的扫描成本为0.0036199，其余所有内容占剩余的0.0332965。但请注意，对于聚集索引扫描运算符，Estimated CPU Cost为1.22907，Estimated IO Cost为10.7142（总计11.94327而不是0.0369164）。

这种差异的原因是扫描位于反半连接运算符下，并且扫描可以在找到匹配行后立即停止。根据建模假设，估计的子树成本会缩小，因为假设只有很小一部分表已被扫描。

如果没有FK违规并且删除成功，则需要扫描整个表，因此使用未缩小的数字将更具信息量。

如果使用代表实际上发生的完整扫描的11.94327成本重新计算百分比，则此扫描运算符显示为计划成本的99.7％（11.94327 /（11.94327 + 0.0332965））。

- Martin Smith

---

谢谢，为“Table2”添加一个索引使删除速度提高了10倍！ - Josh M.

Answer 2

如果所有被访问的页面都在缓存中，您可以预期CPU成本和日志写入时间约为1毫秒或更短。实际上，客户端库的开销可能比服务器负载更大。

对于每个不在缓存中的页面，您可以预期在磁盘上进行5-10毫秒的磁盘查找。粗略地说，在Table1中每个被访问的索引中，您可以预期一个这样的访问加上一个在Table2中用于验证FK的访问。

执行计划会告诉您确切要执行哪些物理操作。

700毫秒似乎很多（70个索引？！）。请发布实际的执行计划。服务器未加载且没有由锁定引起的阻塞吗？

- usr

---

请记住，外键默认情况下不被索引，因此如果必须扫描table2进行验证，我非常怀疑我们在谈论单个页面访问。还有，table1中的主键可能不是聚集的。还可能存在触发器、CDC、Change Tracking等各种可能影响基本操作的因素。 - Aaron Bertrand

执行计划显示对2个非聚集索引进行维护。@JoshM - 它正在扫描一个1,117,190行的表（正如您所说，没有匹配项），因此10%的成本估计似乎非常错误。如果在table2中为FK列创建索引，则情况应该会有所改善。也许它在这里使用了包含假设，并假定连接可能会返回匹配项。如果是这样，在这种情况下似乎不太合适。 - Martin Smith

@MartinSmith - 看起来你是对的。我在FK上面添加了一个索引，删除时间从700毫秒降到了73毫秒。太好了！把你的评论转换成答案，我会接受它。 - Josh M.

@AaronBertrand - 顺便问一下，Plan Explorer 能展示这个内容吗？如果在半连接的扫描中从估算值中发出 1 行但实际计数为 0，则您知道必须进行完全扫描，而任何成本缩减都是无效的。 - Martin Smith

或者更普遍地说，从父级输入到左半连接的行数减去从子级输入的行数就是发生的完整扫描次数。 - Martin Smith