SQL Server 中聚集索引、大型表的 DELETE 性能
我有一个包含超过 2000 万行的表，当我这样做时：
DELETE [Table] WHERE ID = ?
需要 40 多秒。 ID 列是聚集的。
这是你所期望的吗？或者是否可以对此进行优化？
I have a table with more than 20 million rows, and when i do:
DELETE [Table] WHERE ID = ?
It takes over 40 seconds. The ID column is clustered.
Is this what you could expect? or is it possible to optimize this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
除了他们的答案中包含的要点 JNK 之外，我看到的一个特殊杀手是当您从一个或多个外键约束的引用表中删除行以及引用表中的引用列时（ s) 没有索引 - 在接受删除之前，您将强制对每个表进行表扫描。
In addition to the fine points JNK included in their answer, one particular killer I've seen is when you're deleting rows from the referenced table for one or more foreign key constraints, and the referencing column(s) in the referencing table(s) aren't indexed - you're forcing a table scan on each of those tables to occur before the delete can be accepted.
这将取决于许多您没有告诉我们的因素...
删除了多少行？更多的行显然意味着更多的时间。
还有其他索引吗？ 每个索引都需要更新，而不仅仅是集群索引。如果您要删除 10 个索引，则需要大约 10 倍的时间（非常粗略）。
还有其他活动吗？如果发生更新或插入，则很可能会出现等待和争用。
一般来说，操作所需的秒数很大程度上取决于您的硬件设置。如果您在台式机上运行此程序，而不是在具有高性能阵列和 12 核的服务器上运行，则期望会非常不同。
This is going to depend on a lot of factors that you don't tell us about...
How many rows are deleted? More rows obviously means more time.
Are there other indexes? Every index needs to get updated, not just the clustered. If you are deleting through 10 indexes it will take about 10x as long (very roughly).
Is there other activity? If there are updates or inserts happening there are very likely waits and contention.
Also very generally speaking, the number of seconds an operation takes is HIGHLY dependent on your hardware setup. If you ran this on a desktop machine vs. a server with a high-performance array and 12 cores the expectations will be very different.
也可以尝试批量删除数据。例子
Also try deleting data in a batch. Example