如何在没有日志的情况下删除SQL中表的大数据?

2021-12-02 00:00:00 sql-server-2008 sql-server sql-optimization

我有一个大数据表.此表中有 1000 万条记录.

I have a large data table. There are 10 million records in this table.

这个查询的最佳方式是什么

What is the best way for this query

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())

推荐答案

  1. 如果您要删除该表中的所有行，最简单的选择是截断表，例如

  1. If you are Deleting All the rows in that table the simplest option is to Truncate table, something like

 TRUNCATE TABLE LargeTable
 GO

Truncate table 只会清空表，您不能使用 WHERE 子句来限制被删除的行，也不会触发任何触发器.

Truncate table will simply empty the table, you cannot use WHERE clause to limit the rows being deleted and no triggers will be fired.

  1. 另一方面，如果您要删除超过 80-90% 的数据，假设您总共有 1100 万行并且您想删除 1000 万行，另一种方法是插入这些 100 万行(您要保留的记录)到另一个临时表.截断这个大表并插入这 100 万行.

  1. On the other hand if you are deleting more than 80-90 Percent of the data, say if you have total of 11 million rows and you want to delete 10 million another way would be to Insert these 1 million rows (records you want to keep) to another staging table. Truncate this large table and Insert back these 1 million rows.

或者如果权限/视图或其他具有这个大表作为其基础表的对象没有受到删除此表的影响，您可以将这些相对较少的行放入另一个表中，删除此表并创建另一个具有相同架构的表，并将这些行导入回这个 ex-Large 表中.

Or if permissions/views or other objects which has this large table as their underlying table doesn't get affected by dropping this table, you can get these relatively small amounts of the rows into another table, drop this table and create another table with same schema, and import these rows back into this ex-Large table.

我能想到的最后一个选项是将数据库的恢复模式更改为 SIMPLE，然后使用如下所示的 while 循环小批量删除行:

One last option I can think of is to change your database's Recovery Mode to SIMPLE and then delete rows in smaller batches using a while loop something like this:

 DECLARE @Deleted_Rows INT;
 SET @Deleted_Rows = 1;


 WHILE (@Deleted_Rows > 0)
   BEGIN
    -- Delete some small number of rows at a time
      DELETE TOP (10000)  LargeTable 
      WHERE readTime < dateadd(MONTH,-7,GETDATE())

   SET @Deleted_Rows = @@ROWCOUNT;
 END

并且不要忘记将恢复模式更改回完整模式，我认为您必须进行备份才能使其完全有效(更改或恢复模式).

and don't forget to change the Recovery mode back to full and I think you have to take a backup to make it fully effective (the change or recovery modes).

相关文章