大数据操作：删除和去重

一些看似简单的数据操作，当作用于海量数据集时，就会出现“意料之外，却在情理之中”的问题，海量数据操作，需要采用特殊方法，才能“曲径通幽”。在删除海量数据时，需要注意日志的增长，索引碎片的增加和数据库的恢复模式，特别是利用大容量日志操作，来减少日志的增长和提高数据插入的速度。对于大数据去重，通过一些小小的改进，比如创建索引，设置忽略重复值选项等，能够提高去重的效率。

从海量数据中删除数据

日志

• 产生的事务日志暴增（SQL server）
• 数据库的恢复模式会影响日志文件的增长
• 把恢复模式设置为simple 或 bulk_logged 模式，能够很大程度减少删除操作产生的事务日志，避免日志暴增

索引

• 删除多余的索引，只保留一个必须的索引
• 数据删除后，重建索引，能够提高数据删除操作上的性能
• 海量中数据删除，每次删除数据，数据库相应的更新索引，这是很慢的磁盘IO操作
• 约到后面，索引碎片约到，更新索引就越慢

数据量大小

• 根据保留数据占总数据量的比例，选择不同的方法删除
• 如果大表中保留的数据较少，可以先把保留的数据存储到临时表中
• 把原始表删除，这样能够利用大容量日志操作，来减少日志的增长和提高数据插入的速度。

对分区表执行分区转移操作

SQL Server的分区表实际上是一系列物理上独立存储的“表”（也叫做分区）构成的，如果要删除的数据位于同一个分区，或者，一个分区中的数据都需要被删除，那么可以把该分区转移（switch）到一个临时表中，由于分区的转移仅仅是元数据库的变更，因此，不会产生任何的数据IO，分区转移瞬间完成。被剥离的分区，通过drop命令删除，整个过程仅仅会产生少量的IO操作，用于元数据变更；而不会产生用于数据删除的IO操作，这种方法，耗时最短，资源消耗最小，效率最高。

alter table original_table
SWITCH  PARTITION source_partition_number
TO temporary_table

drop table temporary_table

从海量数据中去重

数据去重：部分列去重和全部列去重

全部列去重，使用distinct子句来实现，由于distinct操作符会创建在tempdb中临时表，因此，distinct操作是IO密集型的操作。

部分列去重，一般采用row_number排名函数来实现，也可以考虑使用忽略重复值的唯一索引来实现。

使用row_number函数来实现

选择排名函数，是因为排名函数有部分列分区排序的功能：首先在部分列上创建索引，这样数据库引擎能够根据索引列快速排序，然后通过row_number函数和cte来实现重复数据的删除

使用忽略重复值的唯一索引来实现

通过插入和忽略重复值实现部分列的去重，相对来说，更容易控制，用户可以通过循环插入方式来执行，这样，在单独的一个事务中，控制插入数据的数量，能够控制产生的事务日志不至于太大，对于海量数据的去重，建议采用该方法。