08. 删除重复&海量数据

重复数据，通常有两种：一是完全重复的记录，也就是所有字段的值都一样；二是部分字段值重复的记录。

一. 删除完全重复的记录
完全重复的数据，通常是由于没有设置主键/唯一键约束导致的。
测试数据：

if OBJECT_ID('duplicate_all') is not null
drop table duplicate_all
GO
create table duplicate_all
(
c1 int,
c2 int,
c3 varchar(100)
)
GO
insert into duplicate_all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 1,100,'aaa' union all
select 2,200,'bbb' union all
select 3,300,'ccc' union all
select 4,400,'ddd' union all
select 5,500,'eee'
GO

 

(1) 借助临时表

利用DISTINCT得到单条记录，删除源数据，然后导回不重复记录。

if OBJECT_ID('tempdb..#tmp') is not null
drop table #tmp
GO
select distinct * into #tmp
from duplicate_all
where c1 = 1
GO
delete duplicate_all where c1 = 1
GO
insert into duplicate_all 
select * from #tmp

如果表不大的话，可以把所有记录导出一次，然后truncate表后再用distinct导回，这样可以避免delete的日志操作。

 

(2) 使用ROW_NUMBER

with tmp
as
(
select *,ROW_NUMBER() OVER(PARTITION BY c1,c2,c3 ORDER BY(getdate())) as num
from duplicate_all
where c1 = 1
)
delete tmp where num > 1

如果多个表有完全重复的行，可以考虑通过UNION将多个表联合，插到一个新的同结构的表，SQL Server会帮助去掉表和表之间的重复行。

 

二. 删除部分重复的记录
部分列重复的数据，通常表上是有主键/唯一键约束的，可能是程序逻辑造成某些非主键/唯一键列值的数据重复。
测试数据：

if OBJECT_ID('duplicate_col') is not null
drop table duplicate_col
GO
create table duplicate_col
(
c1 int primary key,
c2 int,
c3 varchar(100)
)
GO
insert into duplicate_col
select 1,100,'aaa' union all
select 2,100,'aaa' union all
select 3,100,'aaa' union all
select 4,100,'aaa' union all
select 5,500,'eee'
GO

 

(1) 唯一索引

唯一索引有个忽略重复建的选项，在创建主键约束/唯一键约束时都可以使用这个索引选项。

if OBJECT_ID('tmp') is not null
drop table tmp
GO
create table tmp
(
c1 int,
c2 int,
c3 varchar(100),
constraint UQ_01 unique(c2,c3) with(IGNORE_DUP_KEY = ON)
)
GO
insert into tmp 
select * from duplicate_col
select * from tmp

 

(2) 借助主键/唯一键来删除
通常会选择保留主键/唯一键的最大/最小值，其他行删除。以下只保留重复记录中c1最小的行：

delete from duplicate_col 
where exists(select 1 from duplicate_col b where duplicate_col.c1 > b.c1 and (duplicate_col.c2 = b.c2 and duplicate_col.c3 = b.c3))
--或者
delete from duplicate_col 
where c1 not in (select min(c1) from duplicate_col group by c2,c3)

如果要保留重复记录中的第N行，可以参考05. 取SQL分组中的某几行数据。

 

(3) ROW_NUMBER
和删除完全重复记录的写法基本一样。

with tmp
as
(
select *,ROW_NUMBER() OVER(PARTITION BY c2,c3 ORDER BY(getdate())) as num
from duplicate_col
)
delete tmp where num > 1
select * from duplicate_col

 

三. 删除海量数据
删除海量数据时，如果想要提升性能，需要考虑的一个重要因素就是：如何减少日志操作？

1. 全表删除
全表删除的方式通常有3种：DROP, TRUNCATE, DELETE
(1) DROP/TRUNCATE
DROP和TRUNCATE是DDL操作，日志量都很少(只有回收数据页的记录，不记录页内每条数据的明细)，都释放所有数据页，以及重置IAM、PFS、GAM、SGAM中的标志位，释放的数据页可被其他表使用；

所不同的是，DROP同时也删除了系统目录里对于表的定义，相应的，表上所有定义的对象：INDEX、CONSTRAINT、TRIGGER等等也都将被删除，该表相关的IAM、PFS、GAM、SGAM页也将被释放(不只是重置标志位)；

(2) DELETE
不带条件的DELETE可以用来删除全表数据，所有被删除的行都将被记录日志，做全表删除时效率较差，不推荐；

 

2. 部分删除
对于表中部分数据做删除，如果是分区表的话，直接TRUNCATE分区是最好了，即使是用DELETE删除分区中部分数据，效率也不会太差；

如果不是分区表的话：

(1) 删除表里少部分数据
直接用DELETE删除； 

(2) 删除表里大部分数据
导出所需要保留的少数记录到临时表，然后TRUNCATE原表，再把临时表数据导回来；
举例：

SELECT * INTO tmp FROM TAB_NAME 
WHERE DATE_COL > = GETDATE()-1

TRUNCATE TABLE TAB_NAME

INSERT INTO TAB_NAME 
SELECT * FROM tmp

DROP TABLE tmp

如果不想再把临时表数据导回来，也可直接删除原表TAB_NAME，把tmp重命名为原表名，但不要忘了在tmp上创建原表的对象，如：索引/约束/触发器等等。

EXEC sp_rename 'TAB_NAME', 'TAB_NAME_OLD'
EXEC sp_rename 'tmp', 'TAB_NAME'
--create index/constraint/trigger...on new TAB_NAME
DROP TABLE TAB_NAME_OLD

 (3) 删除表里约一半数据

这时，如果表上没有分区的话，就会慢的特别明显。

不过，假设从n亿数据中删除一半数据：相比直接delete，还是情愿选择select into导出需要的数据，然后删除原表+rename新表来得快；

因为select into一半的数据，比delete一半的数据，通常是要快的，select into是最小化日志的，而且到了2014插入操作也可以并行了；

 

另外，ORACLE中的NOLOGGING选项，类似于SQL Server中的BULK_LOGGED恢复模式，在批量数据操作时才有效，比如：SELECT…INTO(ORACLE中对应create table as select * from…)，CREATE/ALTER INDEX 等等。并不是任何时候这个选项都有效的。

 

 小结：

(1) 海量数据的删除，尽量选择日志量较小的方式进行；
(2) NOLOGGING选项/BULK_LOGGED恢复模式，在删除数据时，派不上用场，通常用在批量导入或更新数据时。