SQLServer2008删除⼤量数据
⼀、写在前⾯ - 想说爱你不容易
为了升级数据库⾄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、UPDA
TE、INSERT、TRUNCATE、DROP等的优化操作组合，我们的⽬的就是出最优最快最好的⽅法。为了便于测试，准备了⼀张测试表tmp_employee
--Create table tmp_employee
CREATE TABLE[dbo].[tmp_employee] (
[EmployeeNo]INT PRIMARY KEY,
[EmployeeName][nvarchar](50) NULL,
[CreateUser][nvarchar](50) NULL,
[CreateDatetime][datetime]NULL
);
1. 数据插⼊PK
1.1. 循环插⼊，执⾏时间为38026毫秒
--循环插⼊
SET STATISTICS TIME ON;
DECLARE@Index INT=1;
DECLARE@Timer DATETIME=GETDATE();
WHILE@Index<=100000
BEGIN
INSERT[dbo].[tmp_employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_'+CAST(@Index AS CHAR(6)), 'system', GETDATE()); SET@Index=@Index+1;
END
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];
SET STATISTICS TIME OFF;
1.2. 事务循环插⼊，执⾏时间为6640毫秒
--事务循环
BEGIN TRAN;
SET STATISTICS TIME ON;
DECLARE@Index INT=1;
DECLARE@Timer DATETIME=GETDATE();
WHILE@Index<=100000
BEGIN
INSERT[dbo].[tmp_employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_'+CAST(@Index AS CHAR(6)), 'system', GETDATE()); SET@Index=@Index+1;
END
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];
SET STATISTICS TIME OFF;
COMMIT;
1.3. 批量插⼊，执⾏时间为220毫秒
SET STATISTICS TIME ON;
DECLARE@Timer DATETIME=GETDATE();
INSERT[dbo].[tmp_employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime)
SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), 'Employee_', 'system', GETDATE()
FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2
ORDER BY C1.[OBJECT_ID]
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];
SET STATISTICS TIME OFF;
1.4. CTE插⼊，执⾏时间也为220毫秒
SET STATISTICS TIME ON;
DECLARE@Timer DATETIME=GETDATE();
;WITH CTE(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) AS(
SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), 'Employee_', 'system', GETDATE()
FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2
ORDER BY C1.[OBJECT_ID]
)
INSERT[dbo].[tmp_employee]SELECT EmployeeNo, EmployeeName, CreateUser, CreateDatetime FROM CTE;
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];
SET STATISTICS TIME OFF;
⼩结：
按执⾏时间，效率依次为：CTE和批量插⼊效率相当，速度最快，事务插⼊次之，单循环插⼊速度最慢；
单循环插⼊速度最慢是由于INSERT每次都有⽇志，事务插⼊⼤⼤减少了写⼊⽇志次数，批量插⼊只有⼀次⽇志，CTE的基础是CLR，善⽤速度是最快的。
2. 数据删除PK
2.1. 循环删除，执⾏时间为1240毫秒
SET STATISTICS TIME ON;
DECLARE@Timer DATETIME=GETDATE();
DELETE FROM[dbo].[tmp_employee];
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];
SET STATISTICS TIME OFF;
2.2. 批量删除，执⾏时间为106毫秒
SET STATISTICS TIME ON;
DECLARE@Timer DATETIME=GETDATE();
SET ROWCOUNT100000;
WHILE1=1
BEGIN
BEGIN TRAN
DELETE FROM[dbo].[tmp_employee];
COMMIT
IF@@ROWCOUNT=0
BREAK;
END
SET ROWCOUNT0;
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];
SET STATISTICS TIME OFF;
2.3. TRUNCATE删除，执⾏时间为0毫秒
SET STATISTICS TIME ON;
DECLARE@Timer DATETIME=GETDATE();
TRUNCATE TABLE[dbo].[tmp_employee];
SELECT DATEDIFF(MS, @Timer, GETDATE()) AS[执⾏时间(毫秒)];
SET STATISTICS TIME OFF;
⼩结：
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 WHERE1=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中，如何快速删除⼤表中的数据呢？回答这个问题前，我们必须弄清楚上下⽂环境和以及结合实际、具体的需求，不同场景有不同的应对⽅法。
1：整张表的数据全部删除
如果是整张表的数据全部清空、删除，这种场景倒是⾮常简单，TRUNCATE TABLE肯定是最快的。反⽽⽤DELETE处理的话，就是⼀个糟糕的策略。2：⼤表中删除⼀部分数据
对于场景1、⾮常简单，但是很多实际业务场景，并不能使⽤TRUNCATE这种⽅法，实际情况可能只是删除表中的⼀部分数据或者进⾏数据归档后的删除。假设我们遇到的表为TEST，需要删除TEST表中的部分数据。那么⾸先我们需要对表的数据量和被删除的数据量做⼀个汇总统计，具体，我们应该采⽤下⾯⽅法：
·检查表的数据量，以及要删除的数据量。然后计算删除的⽐例，
sp_spaceused'dbo.TEST';
SELECT COUNT(*) AS DELETE_RCD WHERE TEST WHERE ......<；删除条件>
2.1 删除⼤表中绝⼤部分的数据，但是这个绝⼤部分怎么定义不好量化，所以我们这⾥就量化为60%。如果删除的数据⽐例超过60%，就采⽤下⾯⽅法：
1：新建表TEST_TMP
2: 将要保留的数据转移到TEST_TMP
3：将原表TEST重命名为TEST_OLD，⽽将TEST_TMP重命名为TEST
4：检查相关的触发器、约束，进⾏触发器或约束的重命名
5：核对操作是否正确后，原表（TEST_OLD）要么TRUANCATE后，再DROP掉。要么保留⼀段时间，保险起见。
注：⾄于这个⽐例60%是怎么来的。这个完全是个经验值，有简单的测试，但是没有很精确和科学的概率统计验证。
另外，还要考虑业务情况，如果⼀直有应⽤程序访问这个表，其实这种⽅式也是⽐较⿇烦的，因为涉及数据的⼀致性，业务中断等等很多情况。但是，如果程序较少访问，或者在某个时间段没有访问，那么完全可以采⽤这种⽅法。
2.2 删除⼤表中部分数据，如果⽐例不超过60%
1：先删除或禁⽤⽆关索引（⽆关索引，这⾥指执⾏计划不⽤到的索引，这⾥是指对当前DELETE语句⽆⽤的索引）。因为DELETE操作属于DML操作，⽽且⼤表的索引⼀般也⾮常⼤，⼤量DELETE将会对索引进⾏维护操作，产⽣⼤量额外的IO操作。
2：⽤⼩批量，分批次删除（批量删除⽐⼀次性删除性能要快很多）。不要⼀次性删除⼤量数据。⼀次性删除⼤量记录。会导致锁的粒度范围很⼤，并且锁定的时间⾮常长，⽽且还可能产⽣阻塞，严重影响业务等等。⽽且数据库的事务⽇志变得⾮常⼤。执⾏的时间变得超长，性能⾮常糟糕。
批量删除时，到底⼀次性删除多少数量的记录数，SQL效率最⾼呢？这个真没有什么规则计算，个⼈测试对⽐过，⼀次删除10000或100000，没有发现什么特别规律。（有些你发现的“规律”，换个案例，发现不⼀样的结果，这个跟环境有关，有时候可能是⼀个经验值）。不过⼀般⽤10000，在实际操作过程，个⼈建议可以通过做⼏次实验对⽐后，选择⼀个合适的值即可。
案例1：
DECLARE @delete_rows INT;
DECLARE @delete_sum_rows INT =0;
DECLARE @row_count INT=100000
WHILE 1 = 1
BEGIN
DELETE TOP ( @row_count )
sql容易学吗FROM dbo.[EmployeeDayData]
WHERE WorkDate < CONVERT(DATETIME, '2012-01-01 00:00:00',120);
SELECT @delete_rows = @@ROWCOUNT;
SET @delete_sum_rows +=@delete_rows
IF @delete_rows = 0
BREAK;
END;
SELECT @delete_sum_rows;
案例2：
DECLARE @r INT;
DECLARE @Delete_ROWS BIGINT;
SET @r = 1;
SET @Delete_ROWS =0
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (10000) -- this will change
YourSQLDba..YdYarnMatch
WHERE Remark='今⽇未⼊'and Operation_Date<CONVERT(datetime, '2019-05-30',120);
SET @r = @@ROWCOUNT;
SET @Delete_ROWS += @r;
COMMIT TRANSACTION;
PRINT(@Delete_ROWS);
END
该表有下⾯两个索引
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论