想移除歷史交易資料時,很直覺想到串DML Delete;但如果刪除交易量大,怕影響線上交易時,辛苦的攻城師需要改成預存程序分批刪除,每次刪2,000筆還放慢半秒,但鎖定物件較多,批次時間也久;如果資料表能獲得DBA大人同意設置成Partition Table,用上Switch Partition可以飛快的轉出資料;不過Switch Partition手續有些繁瑣,得先建立相同分割檔案群組、相同結構的空資料表再轉移。
SQL Server 2016更方便了,TRUNCATE TABLE支援指定分割編號,秒殺的概念,來試試。
Docs上對TRUNCATE TABLE (Transact-SQL)的解釋
移除所有資料列的資料表或指定的分割區資料表,而不記錄個別資料列刪除。
TRUNCATE TABLE 類似於不含 WHERE 子句的 DELETE 陳述式;不過,TRUNCATE TABLE 比較快,使用的系統資源和交易記錄資源也比較少。
建立測試環境
建立資料庫,檔案群組、檔案
--CREATE DataBase [PartitionTableTruncateTestDb]
GO
alter database [PartitionTableTruncateTestDb] add filegroup [PartFG01];
alter database [PartitionTableTruncateTestDb] add filegroup [PartFG02];
GO
alter database [PartitionTableTruncateTestDb] add file ( name=[PartFGFile01],filename='T:\SQL\PartFGFile01.ndf',size=10MB) TO FileGroup[PartFG01]
alter database [PartitionTableTruncateTestDb] add file ( name=[PartFGFile02],filename='T:\SQL\PartFGFile02.ndf',size=10MB) TO FileGroup[PartFG02]
建立分割函數及分割配置Scheme(這邊我們想用考試成績作為分割條件,60分及格的一組,不及格的一組)。
CREATE PARTITION FUNCTION PFInt(int) AS RANGE RIGHT FOR VALUES (60)
GO
CREATE PARTITION SCHEME [PSchemeInt] AS PARTITION [PFInt] TO ([PartFG01], [PartFG02])
GO
驗證分割函數是否符合預期
SELECT
59 AS val,$PARTITION.PFInt(59) AS partition UNION ALL
SELECT
60,$PARTITION.PFInt(60) UNION ALL
SELECT
61,$PARTITION.PFInt(61)
59分是分割1,60分是分割2
建立分割資料表
新增5個人的成績,巧虎、琪琪、桃樂比、妙妙、小花,但是這次考試琪琪、桃樂比都不及格。
DROP TABLE IF EXISTS [TestPartitionTable]
CREATE TABLE [dbo].[TestPartitionTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](300) NOT NULL,
[Score] [int] NOT NULL,
CONSTRAINT [PK_TestPartitionTable] PRIMARY KEY CLUSTERED
(
[id] ASC,
[Score] ASC
) ON [PSchemeInt](Score)
) ON [PSchemeInt](Score)
INSERT INTO [TestPartitionTable]
VALUES ('巧虎', 100), ('琪琪', 50), ('桃樂比', 40), ('妙妙 ', 80), ('小花 ', 70)
查詢分割內的筆數及資料所在的分割
--查詢分割內的資料筆數
SELECT
OBJECT_NAME(object_id) AS table_name
,partition_number
,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TestPartitionTable')
--查詢資料所在的分割
SELECT
%%lockres%% as lockres
,%%physloc%% as physloc
,b.file_id
,b.page_id
,b.slot_id
,$PARTITION.PFInt(score) as partition_number
,a.* from [TestPartitionTable] a
CROSS APPLY fn_PhysLocCracker(%%physloc%%) b
分割1(不及格)有2筆,分割2(及格)有3筆
指定Partition Number Truncate
TRUNCATE TABLE [TestPartitionTable]
WITH(Partitions(1))
也可以指定區間WITH (PARTITIONS (6 TO 8))或清單WITH (PARTITIONS (1, 5))
重新查詢資料表,只剩下分割2的資料了
刪除大型交易資料表(刪除 vs 截斷)
這樣或許不能展現他的對比效果,來把資料撐大一點,比較刪除和截斷的差異
TRUNCATE TABLE TestPartitionTable
INSERT INTO [TestPartitionTable]
VALUES ('巧虎', 100), ('琪琪', 50), ('桃樂比', 40), ('妙妙 ', 80), ('小花 ', 70)
GO
INSERT INTO TestPartitionTable SELECT name,score FROM TestPartitionTable
GO 20
EXEC sp_spaceused 'TestPartitionTable'
大概58秒完成,我們現在擁有一個524萬筆資料,大概3GB的資料表
查詢分割內的筆數
SELECT
OBJECT_NAME(object_id) AS table_name
,partition_number
,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TestPartitionTable')
分割1: 209萬,分割2: 314萬
比較兩個分割資料的移除時間
實驗組
SET STATISTICS IO ON
SET STATISTICS TIME ON
TRUNCATE TABLE [TestPartitionTable]
WITH(Partitions(2))
SQL Server 執行次數:
CPU 時間 = 0 ms,經過時間 = 1 ms。
對照組(刪除剩下的資料表)
SET STATISTICS IO ON
SET STATISTICS TIME ON
DELETE [TestPartitionTable]
資料表 'TestPartitionTable'。掃描計數 2,邏輯讀取 2258229,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。
SQL Server 執行次數:
CPU 時間 = 6235 ms,經過時間 = 15485 ms。
小結:
移除資料方法 |
筆數 |
時間 |
刪除(Delete) |
2,097,152 |
15,485ms |
截斷(Truncate) |
3,145,728 |
1ms |
SQL 2016也下放有限制的Partition Table功能到Standard版。
參考
Docs TRUNCATE TABLE (Transact-SQL)