曾为浮云遮望眼，平沙莽莽黄入天。 终有一缘得一道，拨开云雾见青天！ ——论如何快速删除上亿级数据集思路

曾为浮云遮望眼，平沙莽莽黄入天。终有一缘得一道，拨开云雾见青天！     ——论如何快速删除上亿级数据集思路

缘起

多年前就遇到过一个问题，后来在工作中也困扰多年，被多次困扰。

曾经一个环境中一个业务数据库，每天增长差不多10G，然后半年差不多1.8T，到这个量级后，备份和维护时间都很长，虽然我们有时间窗口，此系统基本是5*8最繁忙，因此晚上可以做运维。 但是这样的数据集，在14年前高级的还是磁带库备份的时代，还是非常庞大了。

于是为了安全稳定，我们采取了分库的操作，将生产库保留半年数据，半年前数据移入历史库。3个月进行操作一次， 当时的方法通过测试，最快的方法，将现在生产库备份，还原到备份机器。然后生产库上进行删除半年前的数据。而备份库上进行删除现在6个月内的数据。 一般维护动作要进行48小时，我们必须在周一上班前恢复系统。非常麻烦。任何出错都会导致工作白做。

最近也遇到一个客户，每秒钟写入数据差不多2000多条，一天数据量差不多1亿条，客户缓存7天数据，每天需要删除7天前的数据。也就是每天晚上要删除近一亿条数据。

客户在azure上的数据库虚机使用了3块SSD盘，日常使用中查询写入都没问题。 但是进行大批量删除，就会导致系统缓慢，而客户系统基本是24小时运作，会影响业务。

开始进行了删除的优化，将数据集分片，经过测试，一次性删除 50万条比较良好， 每次将需要删除的数据取出50万来删除，不断循环直到删除完成结束。然后进行索引重建的操作，这样的情况也至少要3小时完成。 也是比较痛苦的。

解决方案

于是寻求高手解决方案，江湖中高人很多，群里一问，马上就有人给出了方案。思路是使用分区，使用truncate 可以删除分区， 一下豁然开朗！

思路如下，客户需要保存7天数据，正好是一周，因此相当于每周一就删除上周一的数据。

于是我做了如下方案：

--在数据库上创建7个文件组
ALTER DATABASE [test] ADD FILEGROUP [datagroup1]
ALTER DATABASE [test] ADD FILEGROUP [datagroup2]
ALTER DATABASE [test] ADD FILEGROUP [datagroup3]
ALTER DATABASE [test] ADD FILEGROUP [datagroup4]
ALTER DATABASE [test] ADD FILEGROUP [datagroup5]
ALTER DATABASE [test] ADD FILEGROUP [datagroup6]
ALTER DATABASE [test] ADD FILEGROUP [datagroup7]
--添加相应数据文件
ALTER DATABASE [test] ADD FILE 
( NAME = N'datagroup1_file1', 
FILENAME = N'E:\Data\datagroup1_file1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 TO FILEGROUP [datagroup1]
 ALTER DATABASE [test] ADD FILE 
( NAME = N'datagroup2_file1', 
FILENAME = N'E:\Data\datagroup2_file1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 TO FILEGROUP [datagroup2]
 ALTER DATABASE [test] ADD FILE 
( NAME = N'datagroup3_file1', 
FILENAME = N'E:\Data\datagroup3_file1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 TO FILEGROUP [datagroup3]
 ALTER DATABASE [test] ADD FILE 
( NAME = N'datagroup4_file1', 
FILENAME = N'E:\Data\datagroup4_file1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 TO FILEGROUP [datagroup4]
 ALTER DATABASE [test] ADD FILE 
( NAME = N'datagroup5_file1', 
FILENAME = N'E:\Data\datagroup5_file1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 TO FILEGROUP [datagroup5]
 ALTER DATABASE [test] ADD FILE 
( NAME = N'datagroup6_file1', 
FILENAME = N'E:\Data\datagroup6_file1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 TO FILEGROUP [datagroup6]
 ALTER DATABASE [test] ADD FILE 
( NAME = N'datagroup7_file1', 
FILENAME = N'E:\Data\datagroup7_file1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 TO FILEGROUP [datagroup7]
GO
​
--创建分区函数
CREATE PARTITION FUNCTION [WeekDayFunction](int) AS RANGE LEFT FOR VALUES ( 1, 2, 3, 4, 5,6)
GO
​
---创建分区构架
CREATE PARTITION SCHEME [WeekDayFunctionScheme] AS PARTITION [WeekDayFunction] 
TO ([Datagroup1], [Datagroup2], [Datagroup3], [Datagroup4], [Datagroup5], [Datagroup6], [Datagroup7])
GO
---创建测试表
CREATE TABLE [dbo].[testtable](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [USERID] [bigint] NULL,  
    [GPSTIME] [datetime] not NULL,
    [RECVTIME] [datetime] NULL,
    [LNG] [float] NULL,
    [LAT] [float] NULL,
    [Weeklyday] int null     
) on [WeekDayFunctionScheme]([Weeklyday])  
---大量进行数据灌入，使用下面语句可查询分区表情况
​
 SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber
      
      --查询结果 如下类似           
​

现在看到分区编号为1的分区有166万条记录，现在我要删除这个分区，

见证奇迹的时刻到来了！

执行如下代码：

truncate table [dbo].[testtable] with  ( PARTITIONS (1))

命令瞬间完成！再次执行上面代码，看到分区1的行数只有398了。因为删除后立刻又有写入。测试数据一直在写。

 

再次去查询了官方文档

与 DELETE 语句相比，TRUNCATE TABLE 具有以下优点：  

• 所用的事务日志空间较少。 

  DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一个项。 TRUNCATE TABLE 通过释放用于存储表数据的数据页删除数据，且仅在事务日志中记录页释放。 

• 使用的锁通常较少。 

  当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。 TRUNCATE TABLE 始终锁定表（包含架构 (SCH-M) 锁）和页，而不是锁定各行。

• 如无例外，在表中不会留有任何页。 

  执行 DELETE 语句后，表仍会包含空页。 例如，必须至少使用一个排他 (LCK_M_X) 表锁，才能释放堆中的空表。 如果执行删除操作时没有使用表锁，表（堆）中将包含许多空页。 对于索引，删除操作会留下一些空页，尽管这些页会通过后台清除进程迅速释放。 

  TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。 若要删除表定义及其数据，请使用 DROP TABLE 语句。 

  如果表包含标识列，该列的计数器重置为该列定义的种子值。 如果未定义种子，则使用默认值 1。 若要保留标识计数器，请使用 DELETE。

对于客户的这样查询业务较少的情况来说，这个方案可能是维护数据库最优化的方案了。按照规则做好分区，循环删除和写入分区。这样管理磁盘文件，也很好管理。此功能在SQL 2016 新版本支持。虽然这方问题还是有些后续问题，后续使用如有问题，请再见我的blog， 对于我还是有拨开云雾见青天的感觉！