Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > sqlserver 删除大数据

sqlserver 删除大数据

WBOY
Release： 2016-06-07 15:39:55
Original
1500 people have browsed it

一、写在前面 - 想说爱你不容易 为了升级数据库至SQL Server 2008 R2，拿了一台现有的PC做测试，数据库从正式库Restore(3个数据库大小夸张地达到100G)，而机器内存只有可怜的4G，不仅要承担DB Server角色，同时也要作为Web Server，可想而知这台机器的命运是

一、写在前面 - 想说爱你不容易

　　为了升级数据库至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

sqlserver 删除大数据

<span>--</span><span>Create table Employee</span>
<span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span> (
    <span>[</span><span>EmployeeNo</span><span>]</span> <span>INT</span> <span>PRIMARY</span> <span>KEY</span>,
    <span>[</span><span>EmployeeName</span><span>]</span> <span>[</span><span>nvarchar</span><span>]</span>(<span><strong>50</strong></span>) <span>NULL</span>,
    <span>[</span><span>CreateUser</span><span>]</span> <span>[</span><span>nvarchar</span><span>]</span>(<span><strong>50</strong></span>) <span>NULL</span>,
    <span>[</span><span>CreateDatetime</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span>
);
Copy after login

sqlserver 删除大数据

1. 数据插入PK

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

sqlserver 删除大数据

<span>--</span><span>循环插入</span>
<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Index</span> <span>INT</span> <span>=</span> <span><strong>1</strong></span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>WHILE</span> <span>@Index</span> <span> <span><strong>100000</strong></span>
<span>BEGIN</span>
    <span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) <span>VALUES</span>(<span>@Index</span>, <span>'</span><span>Employee_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@Index</span> <span>AS</span> <span>CHAR</span>(<span><strong>6</strong></span>)), <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>());
    <span>SET</span> <span>@Index</span> <span>=</span> <span>@Index</span> <span>+</span> <span><strong>1</strong></span>;
<span>END</span>

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;</span>
Copy after login

sqlserver 删除大数据

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

sqlserver 删除大数据

<span>--</span><span>事务循环</span>
<span>BEGIN</span> <span>TRAN</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Index</span> <span>INT</span> <span>=</span> <span><strong>1</strong></span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>WHILE</span> <span>@Index</span> <span> <span><strong>100000</strong></span>
<span>BEGIN</span>
    <span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) <span>VALUES</span>(<span>@Index</span>, <span>'</span><span>Employee_</span><span>'</span> <span>+</span> <span>CAST</span>(<span>@Index</span> <span>AS</span> <span>CHAR</span>(<span><strong>6</strong></span>)), <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>());
    <span>SET</span> <span>@Index</span> <span>=</span> <span>@Index</span> <span>+</span> <span><strong>1</strong></span>;
<span>END</span>

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;

<span>COMMIT</span>;</span>
Copy after login

sqlserver 删除大数据

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

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>(EmployeeNo, EmployeeName, CreateUser, CreateDatetime)
<span>SELECT</span> <span>TOP</span>(<span><strong>100000</strong></span>) EmployeeNo <span>=</span> ROW_NUMBER() <span>OVER</span> (<span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>), <span>'</span><span>Employee_</span><span>'</span>, <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>()
<span>FROM</span> SYS.COLUMNS <span>AS</span> C1 <span>CROSS</span> <span>JOIN</span> SYS.COLUMNS <span>AS</span> C2
<span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;
Copy after login

sqlserver 删除大数据

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

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

;<span>WITH</span> CTE(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) <span>AS</span>(
    <span>SELECT</span> <span>TOP</span>(<span><strong>100000</strong></span>) EmployeeNo <span>=</span> ROW_NUMBER() <span>OVER</span> (<span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>), <span>'</span><span>Employee_</span><span>'</span>, <span>'</span><span>system</span><span>'</span>, <span>GETDATE</span>()
    <span>FROM</span> SYS.COLUMNS <span>AS</span> C1 <span>CROSS</span> <span>JOIN</span> SYS.COLUMNS <span>AS</span> C2
    <span>ORDER</span> <span>BY</span> C1.<span>[</span><span>OBJECT_ID</span><span>]</span>
)
<span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span> <span>SELECT</span> EmployeeNo, EmployeeName, CreateUser, CreateDatetime <span>FROM</span> CTE;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;
Copy after login

sqlserver 删除大数据

小结：

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

 

2.  数据删除PK

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

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>DELETE</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;
Copy after login

sqlserver 删除大数据

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

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>SET</span> <span>ROWCOUNT</span> <span><strong>100000</strong></span>;

<span>WHILE</span> <span><strong>1</strong></span> <span>=</span> <span><strong>1</strong></span>
<span>BEGIN</span>
    <span>BEGIN</span> <span>TRAN</span>
    <span>DELETE</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>;
    <span>COMMIT</span>
    <span>IF</span> <span><strong>@@ROWCOUNT</strong></span> <span>=</span> <span><strong>0</strong></span>
        <span>BREAK</span>;
<span>END</span>

<span>SET</span> <span>ROWCOUNT</span> <span><strong>0</strong></span>;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;
Copy after login

sqlserver 删除大数据

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

sqlserver 删除大数据

<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>;
<span>DECLARE</span> <span>@Timer</span> <span>DATETIME</span> <span>=</span> <span>GETDATE</span>();

<span>TRUNCATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Employee</span><span>]</span>;

<span>SELECT</span> <span>DATEDIFF</span>(MS, <span>@Timer</span>, <span>GETDATE</span>()) <span>AS</span> <span>[</span><span>执行时间(毫秒)</span><span>]</span>;
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span>;
Copy after login

sqlserver 删除大数据

 小结：

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

 

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

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

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

　　脚本类似如下

<span>SELECT</span> <span>*</span> <span>INTO</span> #keep <span>FROM</span> Original <span>WHERE</span> CreateDate <span>></span> <span>'</span><span>2011-12-31</span><span>'</span>
<span>TRUNCATE</span> <span>TABLE</span> Original
<span>INSERT</span> Original <span>SELECT</span> <span>*</span> <span>FROM</span> #keep
Copy after login

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

<span>SELECT</span> <span>*</span> <span>INTO</span> #keep <span>FROM</span> Original <span>WHERE</span> <span><strong>1</strong></span> <span>=</span> <span><strong>2</strong></span>
Copy after login

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

几点说明：

  • 你可以不用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'

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

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

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

<span>DBCC</span> SHRINKDATABASE(<span>DB_NAME</span>)
Copy after login
Related labels：
sqlserver delete data
source：php.cn
Previous article：sqlserver数据类型问题 Next article：MySql的级联关系
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
SQL Server: Using CASE WHEN expression with WHERE clause I have a new SpringBoot project with SQLServer and I need to replace the MySQL native quer...
From 2024-04-03 14:46:55
0
1
351
Unable to connect to SQL Server in Laravel I can't connect to sql-server in Laravel. I have given sqlserver configuration values ​​in...
From 2024-03-31 13:08:48
0
1
395
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template