SQL Server大表如何快速删除数据

 

在SQL Server中，如何快速删除大表中的数据呢？  回答这个问题前，我们必须弄清楚上下文环境和以及结合实际、具体的需求，不同场景有不同的应对方法。

 

 

1： 整张表的数据全部删除

 

 

如果是整张表的数据全部清空、删除，这种场景倒是非常简单，TRUNCATE TABLE肯定是最快的。 反而用DELETE处理的话，就是一个糟糕的策略。

 

 

2： 大表中删除一部分数据

 

 

对于场景1、非常简单，但是很多实际业务场景，并不能使用TRUNCATE这种方法，实际情况可能只是删除表中的一部分数据或者进行数据归档后的删除。假设我们遇到的表为TEST，需要删除TEST表中的部分数据。那么首先我们需要对表的数据量和被删除的数据量做一个汇总统计，具体，我们应该采用下面方法：

 

·           检查表的数据量，以及要删除的数据量。然后计算删除的比例，

 

    sp_spaceused 'dbo.TEST';

 

    SELECT COUNT(*) AS DELETE_RCD WHERE TEST WHERE ......<删除条件>

 

 

2.1 删除大表中绝大部分的数据，但是这个绝大部分怎么定义不好量化，所以我们这里就量化为60%。如果删除的数据比例超过60%，就采用下面方法：

 

1： 新建表TEST_TMP

 

2:  将要保留的数据转移到TEST_TMP

 

3： 将原表TEST重命名为TEST_OLD， 而将TEST_TMP重命名为TEST

 

4： 检查相关的触发器、约束，进行触发器或约束的重命名

 

5： 核对操作是否正确后，原表（TEST_OLD）要么TRUANCATE后，再DROP掉。要么保留一段时间，保险起见。

 

注：至于这个比例60%是怎么来的。这个完全是个经验值，有简单的测试，但是没有很精确和科学的概率统计验证。

 

 

另外，还要考虑业务情况，如果一直有应用程序访问这个表，其实这种方式也是比较麻烦的，因为涉及数据的一致性，业务中断等等很多情况。但是，如果程序较少访问，或者在某个时间段没有访问，那么完全可以采用这种方法。

 

 

2.2 删除大表中部分数据，如果比例不超过60%

 

 

1：先删除或禁用无关索引（无关索引，这里指执行计划不用到的索引，这里是指对当前DELETE语句无用的索引）。因为DELETE操作属于DML操作，而且大表的索引一般也非常大，大量DELETE将会对索引进行维护操作，产生大量额外的IO操作。

 

2：用小批量,分批次删除（批量删除比一次性删除性能要快很多）。不要一次性删除大量数据。一次性删除大量记录。会导致锁的粒度范围很大，并且锁定的时间非常长，而且还可能产生阻塞，严重影响业务等等。而且数据库的事务日志变得非常大。执行的时间变得超长，性能非常糟糕。

 

批量删除时，到底一次性删除多少数量的记录数，SQL效率最高呢？  这个真没有什么规则计算，个人测试对比过，一次删除10000或100000，没有发现什么特别规律。（有些你发现的“规律”，换个案例，发现不一样的结果，这个跟环境有关，有时候可能是一个经验值）。不过一般用10000，在实际操作过程，个人建议可以通过做几次实验对比后，选择一个合适的值即可。

 

案例1：

 

DECLARE @delete_rows INT;
DECLARE @delete_sum_rows INT =0;
DECLARE @row_count INT=100000

WHILE 1 = 1
    BEGIN
        DELETE TOP ( @row_count )
        FROM    dbo.[EmployeeDayData]
        WHERE    WorkDate < CONVERT(DATETIME, '2012-01-01 00:00:00',120);

        SELECT  @delete_rows = @@ROWCOUNT;

            SET @delete_sum_rows +=@delete_rows
            IF @delete_rows = 0
            BREAK;
        END;
SELECT @delete_sum_rows;

 

 

 

案例2：

 

DECLARE @r INT;
DECLARE @Delete_ROWS  BIGINT;

SET @r = 1;
SET @Delete_ROWS =0
WHILE @r > 0
BEGIN
    BEGIN TRANSACTION;
        DELETE TOP (10000) -- this will change
           YourSQLDba..YdYarnMatch
           WHERE Remark='今日未入' and Operation_Date<CONVERT(datetime, '2019-05-30',120);

          SET @r = @@ROWCOUNT;

          SET @Delete_ROWS += @r;

    COMMIT TRANSACTION;

    PRINT(@Delete_ROWS);

END

 

 

该表有下面两个索引

 

USE [YourSQLDba]
GO


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YdYarnMatch]') AND name = N'IX_YdYarnMatch_N2')
DROP INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch] WITH ( ONLINE = OFF )
GO

USE [YourSQLDba]
GO


CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch]
(
    [Job_No] ASC,
    [GK_No] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

USE [YourSQLDba]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YdYarnMatch]') AND name = N'IX_YdYarnMatch_N1')
DROP INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch] WITH ( ONLINE = OFF )
GO

USE [YourSQLDba]
GO

CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch]
(
    [Operation_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

 

 

重点：实践证明，如果新建一个索引，能够避免批量删除过程中执行计划走全表扫描，也能大大加快删除的速度。个人对这个案例进行了测试、验证。发现加上合适索引后，让DELETE语句走Index Seek后，删除效率确实大大提升。

 

 

删除索引IX_YdYarnMatch_N2，保留索引IX_YdYarnMatch_N1，但是发现SQL执行计划走全表扫描，执行SQL时，删除非常慢

 

 

删除索引IX_YdYarnMatch_N1，重新创建索引IX_YdYarnMatch_N1后，执行计划走Index Seek，删除效率大大提示。

 

CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch]

(

    [Operation_Date] ASC ,

    Remark

   

)

 

注意：此处索引名相同，但是索引对应的字段不一样。

 

 

 

所以正确的做法是：

 

1：先删除或禁用无关索引（对当前DELETE语句无用的索引），删除前生成对应的SQL，以便完成数据删除后，重新创建索引。注意，前提是在操作阶段，这个操作不会影响应用。否则应重新考虑。

 

2：检查测试当前SQL的执行计划，能否创建合适的索引，加快DELETE操作。如上面例子所示

 

3：批量循环删除记录。

 

4：在ORACLE数据库中，有些表的设置可以减少对应DML操作的日志生成量，但是SQL Server没有这些功能，但是要及时关注或调整事务日志的备份情况。

 

    如果我们能将将数据库的恢复模式设置为SIMPLE，那么可以减少日志备份引起的额外的IO开销。但是很多生产环境不能切换用户数据库的恢复模式。

 

 

其实说了这么多，SQL Server中大表快速删除索引的方法就是将一次性删除改成分批删除，逐次提交而已。其它的方式都是一些辅助方式而已。另外，如果你想亲自做一些细节测试，建议参考博客https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes