如何加快从大型数据库表中删除？ [英] How do I speed up deletes from a large database table?

问题描述

这里是我想解决的问题：我最近完成了一个数据层重新设计，允许我负载平衡我的数据库跨多个碎片。为了保持碎片平衡，我需要能够将数据从一个分片迁移到另一个分片，这涉及从分片A到分片B的复制，然后从分片A删除记录。但是我有几个表非常大，并且有许多外键指向它们，因此从表中删除单个记录可能需要一秒钟以上。

Here's the problem I am trying to solve: I have recently completed a data layer re-design that allows me to load-balance my database across multiple shards. In order to keep shards balanced, I need to be able to migrate data from one shard to another, which involves copying from shard A to shard B, and then deleting the records from shard A. But I have several tables that are very big, and have many foreign keys pointed to them, so deleting a single record from the table can take more than one second.

在某些情况下，我需要删除数百万条记录

In some cases I need to delete millions of records from the tables, and it just takes too long to be practical.

禁用外键不是一个选项。删除大批量行也不是一个选项，因为这是一个生产应用程序，大量删除锁定太多资源，导致失败。我使用Sql Server，我知道分区表，但对分区的限制（和企业版的许可证费用）是不现实的，他们是不可能的。

Disabling foreign keys is not an option. Deleting large batches of rows is also not an option because this is a production application and large deletes lock too many resources, causing failures. I'm using Sql Server, and I know about partitioned tables, but the restrictions on partitioning (and the license fees for enterprise edition) are so unrealistic that they are not possible.

当我开始处理这个问题时，我认为困难的部分是编写算法，它算出如何从叶级删除行到数据模型的顶部，这样就不会违反外键约束。但是解决这个问题没有好结果，因为删除需要一夜之间消失的记录需要几周时间。

When I began working on this problem I thought the hard part would be writing the algorithm that figures out how to delete rows from the leaf level up to the top of the data model, so that no foreign key constraints get violated along the way. But solving that problem did me no good since it takes weeks to delete records that need to disappear overnight.

我已经建立了一种将数据标记为虚拟删除的方法，就应用程序而言，数据已经消失了，但是我仍然处理大型数据文件，大型备份和较慢的查询，因为表格的大小。

I already built in a way to mark data as virtually deleted, so as far as the application is concerned, the data is gone, but I'm still dealing with large data files, large backups, and slower queries because of the sheer size of the tables.

任何想法？

推荐答案

请参阅：优化SQL Server上的删除

此MS支持文章可能感兴趣：如何解决SQL Server中的锁定升级导致的阻止问题：

This MS support article might be of interest: How to resolve blocking problems that are caused by lock escalation in SQL Server:

将大批量操作分成几个较小的操作。对于
示例，假设您运行以下
查询从审计
表中删除了几百个
000个旧记录，然后您发现它
导致了锁升级阻止
其他用户：

Break up large batch operations into several smaller operations. For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users:

DELETE FROM LogMessages WHERE LogDate < '2/1/2002'    

通过一次删除这些记录几个
，您可以
大大减少每个事务
累积的
锁的数量，并防止锁升级。对于
示例：

By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. For example:

SET ROWCOUNT 500
delete_more:
     DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

减少查询的锁定占用空间大型扫描或大型
数量的书签查找可以
增加锁定
升级的机会;另外，它增加
的死锁机会，并且通常
不利地影响并发性和
性能。

Reduce the query's lock footprint by making the query as efficient as possible. Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance.

这篇关于如何加快从大型数据库表中删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！