暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / SQL Server系列之删除大量数据

SQL Server系列之删除大量数据

SQL数据库运维 2022-08-13
676

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

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


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

    --Create table Employee
    CREATE TABLE [dbo].[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].[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].[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].[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].[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].[Employee];


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

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

              （执行测试数据相关语句时所使用的服务器配置不同，执行时间将有所差别）

                SET STATISTICS TIME ON;
                DECLARE @Timer DATETIME = GETDATE();


                SET ROWCOUNT 100000;


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


                SET ROWCOUNT 0;


                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].[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 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)


                          文章来源：博客园

                          原文作者：舍长

                          版权声明：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利.

                          原文链接：http://panchunting.cnblogs.com/

                          点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。


                          动动小手点击加关注呦☟☟☟

                          sqlsql优化sql数据库select毫秒
                          文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                          评论