SQLServer⼤表如何快速删除数据
在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
该表有下⾯两个索引
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开销。但是很多⽣产环境不能切换⽤户数据库的恢复模式。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。