SQL删除性能
delete from a A where a.ID = 132.
A表包含大约5000条记录，A.ID是A表的主键。但是删除需要很长时间。有时它也会超时。该表包含三个索引，并由三个外键引用。谁能解释一下为什么即使我们是基于主键删除，它仍然需要很长时间。请告诉我一些方法来优化这个问题......？
delete from a A where a.ID = 132.
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 ...?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
可能的原因：
1) 级联删除操作
2) 触发器
3) 主键列的类型不是整数，从而强制对每个 pk 值进行类型转换以进行比较。这需要全表扫描。
4）您的查询是否真的像您在问题中发布的那样以点结尾？如果是这样，该数字可能会被视为浮点数而不是整数，从而导致类似于 3)
5) 的类型转换，您的删除查询正在等待其他一些慢查询来释放锁
Possible causes:
1) cascading delete operations
2) trigger(s)
3) the type of your primary key column is something other than an integer, thereby forcing a type conversion on each pk value to do the comparison. this requires a full table scan.
4) does your query really end in a dot like you posted it in the question? if so, the number may considered to be a floating point number instead of an integer, thereby causing a type conversion similar to 3)
5) your delete query is waiting for some other slow query to release a lock
显然，这不应该花很长时间。然而，这里没有足够的信息来弄清楚到底为什么。不过，我可以告诉您，您应该关注外键。
如果它们施加来自其他更大的表的约束，则可能会减慢速度。您还可能会发现超时是由于阻止删除的完整性检查造成的（那么问题是为什么您没有收到异常而不是超时）。
我的下一步是删除外键，然后检查性能。然后一次将每一项添加回来并随时检查性能。
其他操作（例如插入、选择、更新）是否需要很长时间？
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?
第一个想法：外键索引？
第二个想法：触发器触发？
First thought: Indexes on foreign keys?
Second thought: Triggers firing?
尝试更新统计数据。 5000 行并不是什么大问题。如果您定期这样做，您也应该安排对该表的维护（即重新构建索引、更新统计信息等）
Try to update the statistics. 5000 rows is not a big deal. If you're doing this regularly you should schedule maintenance on that table as well (i.e. re-build indexes, update stats etc.)
正如其他人所观察到的，可能的嫌疑人是外键。
首先，因为如果依赖表依次被其他表引用，而其他表又可能被引用，则 ON DELETE CASCADE 可以积聚动力，依此类推。
其次，因为其他用户可能对需要删除的行有锁。这是超时最可能的原因。它的工作原理完全取决于数据库的风格和版本。例如，旧版本的 Oracle (<=8.0) 需要锁定整个从属表，除非外键列已建立索引。
As others have observed, the probable suspects are the foreign keys.
Firstly because the ON DELETE CASCADE can gather momentum if the dependent tables in turn are referenced by other tables, which in turn may be referenced, and so on.
Secondly, because other users may have locks on the rows which need to be deleted. This is the most likely cause of the timeouts. Quite how this works will depend on the flavour and version of your database. For instance, older versions of Oracle (<=8.0) needed to lock the entire dependent table unless the foreign key columns were indexed.