HTML5技术

大数据操作:删除和去重 - 悦光阴

字号+ 作者:H5之家 来源:H5之家 2017-06-20 14:02 我要评论( )

一些看似简单的数据操作,当作用于海量数据集时,就会出现意料之外,却在情理之中的问题,海量数据操作,需要采用特殊方法,才能曲径通幽。在删除海量数据时,需要注意日志的增长,索引碎片的增加和数据库的恢复模式,特别是利用大容量日志操作,来减少日志

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

一,从海量数据中删除数据

从海量数据表中删除一半数据,看似简单,使用delete命令,如果真这么干,SQL Server产生的事务日志暴增,估计会把服务器硬盘爆掉。数据库的恢复模式会影响日志文件的增长,在删除海量数据时,根据采用的方法,相应地把恢复模式设置为simple,或bulk_logged 模式,能够在很大程度上减少删除操作产生的事务日志,从而避免日志暴增。

另外,在删除数据时,把表上的多余索引删除(注意,是删除多余的索引),只保留一个必需的索引;在数据删除完成之后,再重建索引,能够提高数据删除操作的性能。有人做过实验,从存储1.6亿条记录的大表中删除数据,每删除400万条要消耗1.5 - 3小时,越到后面速度越慢,为什么?这是因为,每次删除数据时,数据库都要相应地更新索引,这是很慢的硬盘 IO操作,并且,越到后面,索引碎片越多,更新索引就越慢,这就是在删除400万条记录时,一开始只消耗1.5小时,后面要消耗3小时原因。

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

1,循环删除,避免日志文件暴增

在从海量数据表中删除大量数据时,为了避免日志文件暴增,通常采用循环删除方法:首先设置恢复模式为simple,然后每次删除操作都只删除部分数据,这样,当单个删除操作执行完成时,事务日志会被及时清理,事务日志一般保持单个删除操作的事务日志量。

循环删除的伪代码如下,该方法仍有一些局限性,耗时过长,并且会长期使数据库处于简单恢复模式下:

table_name ;

2,将数据插入到临时表中,把原表truncate

如果原始表有一半以上的数据要被删除,从原始表中执行delete命令删除数据,效率十分低下,可以考虑,把原始表中的数据通过select语句筛选出来,然后批量插入导新表中,这种方式利用了大容量日志(Bulk Logged)操作的优势。由于 SELECT INTO,INSERT SELECT 是大容量日志操作,select命令不会产生大量日志文件,因此,执行插入比执行删除的效率更高。最后,执行drop命令,删除整个原始表,几乎不消耗任何时间。

new_table select column_list from original_table where filter_retain drop table original_table

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

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排名函数来实现,也可以考虑使用忽略重复值的唯一索引来实现。在实际的项目开发中,部分列去重更为常见。

1,使用row_number函数来实现

选择排名函数,是因为排名函数有部分列分区排序的功能:首先在部分列上创建索引,这样数据库引擎能够根据索引列快速排序,然后通过row_number函数和cte来实现重复数据的删除。在数据去重时,需要注意,如果删除的数据量太大,数据库引擎会产生大量的事务日志,导致日志文件暴增,在选择该方法时,需要慎重。

create index index_name on table_name ( index_columns ) with(data_compression=page); with cte as ( select index_columns, row_number() over(partition by index_columns order by ...) as rn from table_name ) delete from cte where rn>1

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

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

创建一个临时表,在部分列上创建忽略重复值的唯一索引:

index_name on new_table ( index_columns ) with(ignore_dup_key=on)

由于SQL Server不允许在包含重复值的数据表上创建唯一索引,因此,必须创建一个新的空表,新表时原始表的结构的复制,在部分列上创建忽略重复值的唯一索引。在执行插入操作时, IGNORE_DUP_KEY 选项会忽略重复的索引键值,并抛出警告(Warning)。

 

参考文档:

 

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

相关文章
  • 将数据库从服务器移到浏览器--indexedDB基本操作封装 - 大~熊

    将数据库从服务器移到浏览器--indexedDB基本操作封装 - 大~熊

    2017-06-20 10:01

  • 谈一下我们是怎么做数据库单元测试(Database Unit Test)的 - HarlanC

    谈一下我们是怎么做数据库单元测试(Database Unit Test)的 - HarlanC

    2017-06-17 12:00

  • 最新开源DBLayer,原来数据库操作可以这么简单 - 小小明

    最新开源DBLayer,原来数据库操作可以这么简单 - 小小明

    2017-06-15 17:01

  • 【线上问题】由防火墙导致的数据库空闲连接断开问题 - Trust_FreeDom

    【线上问题】由防火墙导致的数据库空闲连接断开问题 - Trust_FreeDom

    2017-06-15 16:00

网友点评
>