MyException - 我的异常网
当前位置:我的异常网» Sql Server » 大数据操作:剔除和去重

大数据操作:剔除和去重

www.MyException.Cn  网友分享于:2013-06-29  浏览:0次
大数据操作:删除和去重

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

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

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

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

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

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

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

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

--ALTER DATABASE database_name SET RECOVERY SIMPLE ;  

while @index<@EndIndex
begin
    delete table_name 
    where index<=@index;

    set @index+=@Increment
end

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

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

--ALTER DATABASE database_name SET RECOVERY BULK_LOGGED ;  

insert into 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,使用忽略重复值的唯一索引来实现

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

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

create unique index index_name
on new_table
(
index_columns
)
with(ignore_dup_key=on)

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

 

9楼pursuer.chen(陈敏华)
归档删除的方式比较快,这个挺赞同;但是把数据库改成单用户模式去删除数据的风险还是很大,可以定期删除利用备份日志的方式控制日志。
Re: 悦光阴
@pursuer.chen,我遇到过一次,数据量太大,定期删除,测试一下,需要N年才能完成,后来,我采用的方法是,insert+select方式,比直接删除快很多
8楼岁月挑山工
涨知识了,谢谢楼主分享
7楼风微
赞,支持
6楼牛腩
支持支持343
Re: 悦光阴
@牛腩,谢谢支持
Re: 悦光阴
@150530,谢谢
4楼谷仁儿
删除数据可是个高风险的活啊,得谨慎
Re: 悦光阴
@谷仁儿,是的,但有时,业务归档的数据,放在数据库中,影响查询性能,又不得不删除,明知有风险,也必须做
Re: 悦光阴
@谷仁儿,使用软删除,这样,即使错删,也可以恢复
3楼路虎
好文,学习了
2楼0o一泓秋水o0
学习了
1楼屁宝
赞!支持!!!

文章评论

2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
程序员都该阅读的书
程序员都该阅读的书
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
老程序员的下场
老程序员的下场
漫画:程序员的工作
漫画:程序员的工作
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
总结2014中国互联网十大段子
总结2014中国互联网十大段子
程序员必看的十大电影
程序员必看的十大电影
每天工作4小时的程序员
每天工作4小时的程序员
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
一个程序员的时间管理
一个程序员的时间管理
那些争议最大的编程观点
那些争议最大的编程观点
Java程序员必看电影
Java程序员必看电影
中美印日四国程序员比较
中美印日四国程序员比较
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
如何成为一名黑客
如何成为一名黑客
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
旅行,写作,编程
旅行,写作,编程
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
我是如何打败拖延症的
我是如何打败拖延症的
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
程序员和编码员之间的区别
程序员和编码员之间的区别
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
我的丈夫是个程序员
我的丈夫是个程序员
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
编程语言是女人
编程语言是女人
代码女神横空出世
代码女神横空出世
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
10个调试和排错的小建议
10个调试和排错的小建议
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
为什么程序员都是夜猫子
为什么程序员都是夜猫子
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
程序员的鄙视链
程序员的鄙视链
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有