【SQL Server性能优化】删除大量数据的方法比较

如果你要删除表中的大量数据，这个大量一般是指删除大于10%的记录，那么如何删除，效率才会比较高呢？而如何删除才会对系统的影响相对较小呢？

下面先做一个实验，然后对这个实验的结果进行分析，然后得出结论。

1、创建数据库


   
  
      use master   
         go   
           
         if exists( select * from sys.databases where name = ‘test‘)   
         drop database test   
         go   
           
         create database test   
         go

2、创建表


   
  
      use test   
         go   
           
           
         if exists( select * from sys.tables where name = ‘t‘)   
         drop table t   
         go   
           
         create table t(i int,v varchar( 100) default replicate( ‘a‘, 100)   
         ,vv varchar( 100) default replicate( ‘a‘, 100),   
         vvv varchar( 100) default replicate( ‘a‘, 100));

3、插入数据

用下面的代码添加100000条记录,消耗9秒：


   
  
      declare @i int;   
           
         set @i = 1   
           
         begin tran   
           
         while @i <= 100000   
         begin   
         insert into t(i) values(@i)   
           
         set @i = @i   1   
         end   
           
         commit tran

而如果用下面的代码，添加100000条记录,消耗43秒：


   
  
      declare @i int;   
           
         set @i = 1   
           
         while @i <= 100000   
         begin   
         begin tran   
         insert into t(i) values(@i) --没执行一次就提交一次,效率较差    
         commit tran   
           
         set @i = @i   1   
         end

重复插入数据，消耗1分38秒


   
  
      insert into t   
         select *   
         from t   
         go 6

最后总共插入了640万条数据。

4、建立索引

create index idx_t_idx1 on t(i)

5、进行如下设置，是为了预防SQL Server使用太多内存，而导致死机


   
  
      sp_configure ‘ show advanced option ‘,1   
         go   
         reconfigure   
         go   
            
         sp_configure ‘ max server memory (MB) ‘,3584   
         go   
         reconfigure   
         go

6、把上面创建的表t数据，复制成t1和t2两个表，对t1表建立索引


   
  
      if exists( select * from sys.tables where name = ‘t1‘)   
         drop table t1   
         go   
           
         select * into t1   
         from t   
           
         create index idx_t1_idx1 on t1(i)   
         go   
           
           
         if exists( select * from sys.tables where name = ‘t2‘)   
         drop table t2   
         go   
           
         select * into t2   
         from t

7、对t1表进行删除操作，一次删除1000个数，每个数有64条，所以每次删除64000条。共删除1000次,所以删除640000条记录，总耗时82秒


   
  
      dbcc dropcleanbuffers   
         go   
           
         declare @i int = 20000;   
         declare @start_time datetime; -- = getdate();   
           
         while @i <30000   
         begin   
           
         set @start_time = GETDATE();   
           
         delete from t1 where I> [email protected] and i< [email protected]   999   
           
         set @i  = 1000   
         select DATEDIFF( second,@start_time, getdate())   
           
         end

8、删除t2表的数据，耗时44秒


   
  
      delete from t2   
         where I>= 20000 and i< 30000

通过上面的测试发现：

1、在大量插入操作时，在完成操作后再提交，比每次插入操作后马上就提交，效率要高。

2、在删除大量数据时，就算运用索引，甚至同时运用索引和分批操作，效率也不如不用索引，直接通过表扫描删除来的高。

但表扫描的问题是会锁住整个表，阻塞其他事务，导致系统业务大面积瘫痪。

所以，虽然通过直接的删除方法会速度快，但如果通过索引和分批处理，那么只会锁定需要删除的一批数据，而其他的数据则不会锁定，那么导致的阻塞问题就小多了。

3、所以结合上面的2点，当大批量操作时，如果最后提交，那么整个操作效率更高，但是可能会导致阻塞的问题，因为不及时提交，会导致其他事务都被阻塞。

同样的，通过直接删除效率可能更高，但会锁表，会导致严重的阻塞问题，而通过索引和分批处理，虽然效率不是太高，但可以分批处理，相当于分批提交，而每一批都通过索引，只锁住需要处理的记录，而其他的记录都不会锁住，那么就不太会导致阻塞的问题。

所以，大批量的删除操作，如果通过全表扫描，适合在晚上系统比较空闲的维护时间内进行；而如果一定要在白天执行，那么可以考虑通过索引和分批处理，来减少阻塞的问题，但还是会对系统产生一定的影响，特别是内存方面。

不想长大啊发布了416 篇原创文章 · 获赞 135 · 访问量 94万他的留言板关注

秒客网

【SQL Server性能优化】删除大量数据的方法比较

相关文章