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

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

1： 整张表的数据所有删除数据库

若是是整张表的数据所有清空、删除，这种场景却是很是简单，TRUNCATE TABLE确定是最快的。 反而用DELETE处理的话，就是一个糟糕的策略。app

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

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

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

    sp_spaceused 'dbo.TEST'; spa

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

2.1 删除大表中绝大部分的数据，可是这个绝大部分怎么定义很差量化，因此咱们这里就量化为60%。若是删除的数据比例超过60%，就采用下面方法：code

1： 新建表TEST_TMPorm

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