[转]SQL Server系列之 删除大量数据

目录 SQL Server, 数据库
转自:http://www.cnblogs.com/panchunting/archive/2013/04/27/SQL_Tech_001.htm
一、写在前面 – 想说爱你不容易
为了升级数据库至SQL Server 2008 R2,拿了一台现有的PC做测试,数据库从正式库Restore(3个数据库大小夸张地达到100G+),而机器内存只有可怜的4G,不仅要承担DB Server角色,同时也要作为Web Server,可想而知这台机器的命运是及其惨烈的,只要MS SQL Server一启动,内存使用率立马飙升至99%。没办法,只能升内存,两根8G共16G的内存换上,结果还是一样,内存瞬间被秒杀(CPU利用率在0% 徘徊)。由于是PC机,内存插槽共俩,目前市面上最大的单根内存为16G(价格1K+),就算买回来估计内存还是不够(卧槽,PC机伤不起啊),看样子别 无它法 — 删数据!!!删除数据 – 说的容易, 不就是DELETE吗?靠,如果真这么干,我XXX估计能“知道上海凌晨4点的样子”(KB,Sorry,谁让我是XXX的Programmer,哥在这方面绝对比你牛X),而且估计会暴库(磁盘空间不足,产生的日志文件太大了)。二、沙场点兵 – 众里寻他千百度

为了更好地阐述我所遇到的困难和问题,有必要做一些必要的测试和说明,同时这也是对如何解决问题的一种探究。因为毕竟这个问题的根本是如何来更 好更快的操作数据,说到底就是DELETE、UPDATE、INSERT、TRUNCATE、DROP等的优化操作组合,我们的目的就是找出最优最快最好 的方法。为了便于测试,准备了一张测试表Employee

1. 数据插入PK

1.1. 循环插入,执行时间为38026毫秒

1.2. 事务循环插入,执行时间为6640毫秒

1.3. 批量插入,执行时间为220毫秒

1.4. CTE插入,执行时间也为220毫秒

小结:

  • 按执行时间,效率依次为:CTE和批量插入效率相当,速度最快,事务插入次之,单循环插入速度最慢;
  • 单循环插入速度最慢是由于INSERT每次都有日志,事务插入大大减少了写入日志次数,批量插入只有一次日志,CTE的基础是CLR,善用速度是最快的。

 

2. 数据删除PK

2.1. 循环删除,执行时间为1240毫秒

2.2. 批量删除,执行时间为106毫秒

2.3. TRUNCATE删除,执行时间为0毫秒

小结:

  • TRUNCATE太快了,清除10W数据一点没压力,批量删除次之,最后的DELTE太慢了;
  • TRUNCATE快是因为它属于DDL语句,只会产生极少的日志,普通的DELETE不仅会产生日志,而且会锁记录。

 

三、磨刀霍霍 – 犹抱琵琶半遮面

由上面的第二点我们知道,插入最快和删除最快的方式分别是批量插入和TRUNCATE,所以为了达到删除大数据的目的,我们也将采用这两种方式 的组合,其中心思想是先把需要保留的数据存放之新表中,然后TRUNCATE原表中的数据,最后再批量把数据插回去,当然实现方式也可以随便变通。

1. 保留需要的数据之新表中->TRUNCATE原表数据->还原之前保留的数据之原表中

脚本类似如下

SELECT * INTO #keep FROM Original WHERE CreateDate > '2011-12-31'
TRUNCATE TABLE Original
INSERT Original SELECT * FROM #keep

第一条语句会把所有要保留的数据先存放至表#keep中(表#keep无需手工创建,由SELECT INTO生效),#keep会Copy原始表Original的表结构。PS:如果你只想创建表结构,但不拷贝数据,则对应的脚本如下

SELECT * INTO #keep FROM Original WHERE 1 = 2

第二条语句用于清除整个表中数据,产生的日志文件基本可以忽略;第三条语句用于还原保留数据。

几点说明:

  • 你可以不用SELECT INTO,自己通过写脚本(或拷贝现有表)来创建#keep,但是后者有一个弊端,即无法通过SQL脚本来获得对应的表生成Script(我的意思是和原 有表完全一致的脚本,即基本列,属性,索引,约束等),而且当要操作的表比较多时,估计你肯定会抓狂;
  • 既然第一点欠妥,那考虑新建一个同样的数据库怎么样?既可以使用现有脚本,而且生成的数据库基本一致,但是我告诉你最好别这么做,因为第一要跨库,第二,你得准备足够的磁盘空间。

 

2. 新建表结构->批量插入需要保留的数据->DROP原表->重命名新表为原表

CREATE TABLE #keep AS (xxx) xxx — 使用上面提到的方法(使用既有表的创建脚本),但是不能够保证完全一致;

INSERT #keep SELECT * FROM Original where clause

DROP TBALE Original

EXEC SP_RENAME ‘#keep’,’Original’

这种方式比第一种方法略快点,因为省略了数据还原(即最后一步的数据恢复),但是稍微麻烦点,因为你需要创建一张和以前原有一模一样的表结构,包括基本列、属性、约束、索性等等。

三、数据收缩 – 秋风少落叶

数据删除后,发现数据库占用空间大小并没有发生变化,此时我们就用借助强悍的数据收缩功能了,脚本如下,运行时间不定,取决于你的数据库大小,多则几十分钟,少则瞬间秒杀

DBCC SHRINKDATABASE(DB_NAME)

SQL Server 命令行创建用户及权限

目录 SQL Server, 数据库

USE [master]
GO
CREATE LOGIN [liups_loginame] WITH PASSWORD=N’liups88′, DEFAULT_DATABASE=[liups-dbname], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [liups-dbname]
GO
CREATE USER [liups_loginame] FOR LOGIN [liups_loginame]
GO
USE [liups-dbname]
GO
ALTER ROLE [db_owner] ADD MEMBER [liups_loginame]
GO

USE [liups-dbname]
GO

DROP USER [liups_loginame]
GO

USE [master]
GO

DROP LOGIN [liups_loginame]
GO