SQL DELETE 性能 [英] SQL DELETE performance

问题描述

delete from a A where a.ID = 132.

表 A 包含大约 5000 条记录，A.ID 是表 A 中的主键.但是删除需要很长时间.有时它也会超时.该表包含三个索引，并由三个外键引用.谁能解释一下为什么即使我们根据主键删除它也需要很长时间.请告诉我一些优化这个问题的方法......?

The table A contains around 5000 records and A.ID is the primary key in the table A. But it is taking a long time to delete . Sometimes its getting timed out also . That table contains three indexes and it is referenced by three foreign keys . Can anyone explain me why its taking long time even though we are deleting based on the primary key . And please tell me some way to optimize this problem ...?

推荐答案

显然不应该花很长时间.但是，这里没有足够的信息来确切地找出原因.不过，我可以告诉您，您应该关注外键.

Obviously it should not be taking a long time. However, there isn't enough information here to figure out exactly why. I can tell you, though, that you should focus on the Foreign Keys.

如果它们对其他更大的表施加约束，它们会减慢速度.您可能还会发现您的超时是由于阻止删除的完整性检查(那么问题是为什么您没有收到异常而不是超时).

These can slow things down if they impose constraints from other, much larger, tables. You may also find out that your timeouts are due to integrity checks that prevent the delete (then the question is why you aren't getting exceptions instead of a timeout).

我的下一步是删除外键，然后检查性能.然后一次添加一个，并随时检查性能.

My next step would be to remove the foreign keys and then check performance. Then add each one back in at a time and check performance as you go.

其他操作(例如插入、选择、更新)是否需要很长时间?

Are other operations (e.g. Inserts, Selects, Updates) taking a long time?

这篇关于SQL DELETE 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！