MSSQL、MySQL 数据库删除大批量千万级百万级数据的优化

转载 2016年08月29日 20:24:23
SQL Server上面删除1.6亿条记录,不能用Truncate(因为只是删除其中少部分数据)。
经过实验,每次删除400万条要花1.5 - 3小时,而且是越到后面越慢,正常的话,需要大约102个小时,大约4天半时间。这在生产环境下是不能接受的

经过一个处理之后,我每次删除400万条记录花5 - 6分钟,删除全部1.6亿条记录花了4 - 5个小时!

为什么??

每次删除记录,数据库都要相应地更新索引,这是很慢的IO操作,而且后面索引碎片越来越多,就更慢,这就是为什么一开始只花1.5小时,后面要3小时才能删除400万条记录的原因。

删除之前,做个完整备份。
在删除前先保存当前索引的DDL,然后删除其索引
然后根据使用的删除条件建立一个临时的索引(这是提高速度的另外一个重要原因!)
开始删除操作,完成之后再重建之前的索引。

如果需要保留的数据比较少的话,可以把要保留的数据备份出来。在drop表。重新创建,先不要急着创建索引、主键,把数据导回去,然后在建索引、约束之类的。


记得在删除的时候不要在记录日志的模式下面,否则日志文件就要爆了。

2、在My SQL数据库使用中,有的表存储数据量比较大,达到每天三百万条记录左右,此表中建立了三个索引,这些索引都是必须的,其他程序要使用。  
     由于要求此表中的数据只保留当天的数据,所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据,使用delete删除表中的上百万条记录时,MySQL删除速度非常缓慢每一万条记录需要大概4分钟左右,这样删除所有无用数据要达到八个小时以上,这是难以接受的。
     查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的,于是删除掉其中的两个索引后测试,发现此时删除速度相当快,一百万条记录在一分钟多一些,可是这两个索引其他模块在每天一次的数据整理中还要使用,于是想到了一个折中的办法:

删除数据之前删除这两个索引,此时需要三分钟多一些,然后删除其中无用数据,此过程需要不到两分钟,删除完成后重新创建索引,因为此时数据库中的数据相对较少,约三四十万条记录(此表中的数据每小时会增加约十万条),创建索引也非常快,约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时,大大节省了时间。

MySQL中删除数据的两种方法

在MySQL中有两种方法可以删除数据,一种是DELETE语句,另一种是TRUNCATE TABLE语句。DELETE语句可以通过WHERE对要删除的记录进行选择。而使用TRUNCATE TABLE将删...
  • apache6
  • apache6
  • 2008年08月06日 21:26
  • 198889

mysql 批量删除数据库中的所有数据

select CONCAT("truncate ", table_name, ";") from INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA='clo...

MSSQL、MySQL 数据库删除大批量千万级百万级数据的优化

本文转自:http://hi.baidu.com/momodetang/blog/item/5c0891435fd1090f9313c617.html ...

MySQL删除大批量数据

MySQL删除大批量数据 1.删除大表的部分数据 一个表有1亿6000万的数据,有一个自增ID。最大值就是1亿6000万,需要删除大于250万以后的数据,有什么办法可以快速删除? 看到mys...

MySQL批量清除表数据

delete from 表名;  truncate table 表名;  不带where参数的delete语句可以删除MySQL表中所有内容,使用truncate table也可以清空mysql表...

MySQL:按前缀批量删除表格

想要实现mysql>drop table like "prefix_%"没有直接可用的命令,不过可以通过mysql语法来组装,SELECT CONCAT( 'DROP TABLE ', GROUP_C...
  • iefreer
  • iefreer
  • 2013年10月12日 00:20
  • 11637

如何在生产环境删除百万级以上的数据

公司的用户被人恶意注册了,user_id是连续着的,这些用户现在要清理掉,但是数据量太大,如何快速生成200w的delete语句呢? ps:生产环境不建议delete from user where...

MySQL删除表大量数据

假设有一个表(syslogs)有1000万条记录,需要在业务不停止的情况下删除其中statusid=1的所有记录,差不多有600万条,直接执行 DELETE FROM syslogs WHERE st...

记一次mysql删除大量数据的优化

最近工作中,遇到了要删除一些相关库的数据信息,涉及的数据有点多。 实际场景是这样的,公司的一个电商项目,电商为卖家准备了报表的相关数据,报表业务在实现时,将一些订单信息冷库备份到报表的库中,然后再在...

教你如何调整Mysql中insert、update、delete的顺序来以提高效率

MySQL还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。 我们首先应该确定应用的...
  • softn
  • softn
  • 2016年06月03日 11:24
  • 811
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MSSQL、MySQL 数据库删除大批量千万级百万级数据的优化
举报原因:
原因补充:

(最多只允许输入30个字)