前言:
很多时候需要对大数据量进行归档或者删除,并周期性加载大数据量到一个大表中,现在来做个简单的例子,你经常需要删除大数据量表中的大量数据。同时,你想加载大量数据到这个表中,当表中数据有数十亿时,这个操作可能消耗几个小时,但是如果你的表有分区,那么执行起来会很有效。
本文将模拟删除一个季度的数据,并加载整个季度到现有表,其中使用了拆分(splitting)、合并(merging)和切换分区(switching)。
假设我们需要存储季度数据,并且每个季度存放在一个分区。现在需要清除最久的季度和加载最新的季度数据,比如分区中包含:
1、 季度1:2011年1月到2011年3月
2、 季度2:2011年4月到2011年6月
3、 季度3:2011年7月到2011年9月
4、 季度4:2011年10月到2011年12月
现在需要删除季度1并加载2012年第一季度的数据到一个新的分区中。为了实现这个功能,可以使用在date和time列上针对每个季度创建分区。可以合并两个分区或者拆分现有有分区。
本文将演示使用滑动窗口实现,在滑动窗口中,可以清除最久的分区。
步骤:
1、 打开SQLServer
2、 执行下面脚本,创建一个分区函数,并使用RANGE RIGHT,然后创建一个分区架构:
USE Sample_DBUSE master GO IF DB_ID('Sample_DB') IS NOT NULL DROP DATABASE Sample_DB CREATE DATABASE Sample_DB ON PRIMARY ( NAME=N'Sample_DB',FILENAME=N'C:\SQLData\Sample_DB.mdf', SIZE=3072KB,FILEGROWTH=1024KB ) LOG ON ( NAME =N'Sample_DB_log',FILENAME=N'C:\SQLData\Sample_DB_log.ldf', SIZE=1024KB ,FILEGROWTH=10% ) GO USE Sample_DB GO CREATE PARTITION FUNCTION pf_Quaterly_RangeRight(DATETIME) AS RANGE RIGHT FOR VALUES ('20110101','20110401','20110701','20111001','20120101') GO CREATE PARTITION SCHEME ps_Quaterly_RangeRight AS PARTITION pf_Quaterly_RangeRight ALL TO ([PRIMARY]) GO
注意:当分区列是datetime类型时,应该总是尝试使用RANGE RIGHT,这样会使得分区更好,如果使用RANGE LEFT,你必须考虑特定的time部分,包含毫秒。
3、 创建一个表tbl_MyData并插入275000行数据:
USE Sample_DB GO CREATE TABLE tbl_MyData ( RecordDateTime DATETIME NOT NULL , RecordID INT NOT NULL , RecordData VARCHAR(40) NOT NULL ) GO CREATE CLUSTERED INDEX idx_tbl_MyData_RecordDateTime ON tbl_MyData(RecordDateTime,RecordID) ON ps_Quaterly_RangeRight(RecordDateTime) INSERT INTO tbl_MyData SELECT '2011' + RIGHT('0' + CAST(( CASE WHEN ID % 12 = 0 THEN 12 ELSE ID % 12 END ) AS VARCHAR), 2) + RIGHT('0' + CAST(( CASE WHEN ID % 28 = 0 THEN 28 ELSE ID % 28 END ) AS VARCHAR), 2) AS RecordDateTime , ID , RecordData FROM ( SELECT TOP 275000 ID = ROW_NUMBER() OVER ( ORDER BY c1.name ) , RecordData = NEWID() FROM sys.columns AS C1 CROSS JOIN sys.columns AS C2 CROSS JOIN sys.columns AS C3 ) AS T GO
4、 执行下面语句,检验分区数量:
USE Sample_DB GO SELECT partition_number , rows FROM sys.partitions WHERE object_id = OBJECT_ID('tbl_MyData') ORDER BY partition_number
5、 结果如下:
6、 现在先移除2011年第一个季度也就是分区1的数据:
USE Sample_DB GO IF OBJECT_ID('tbl_MyStagingData') IS NOT NULL DROP TABLE tbl_MyStagingData CREATE TABLE tbl_MyStagingData ( RecordDateTime DATETIME NOT NULL , RecordID INT NOT NULL , RecordData VARCHAR(40) NOT NULL ) GO CREATE CLUSTERED INDEX idx_tbl_MyStagingData_RecordDateTime ON tbl_MyStagingData(RecordDateTime,RecordID) GO
7、 现在,切换tbl_MyData的分区2到分期表tbl_MyStagingData并清空分期表。在清空以后,检查分区数据:
USE Sample_DB GO ALTER TABLE tbl_MyData SWITCH PARTITION 2 TO tbl_MyStagingData PARTITION 1 GO TRUNCATE TABLE tbl_MyStagingData GO SELECT partition_number , rows FROM sys.partitions WHERE object_id = OBJECT_ID('tbl_MyData') ORDER BY partition_number
8、 结果可见,分区已经清空:
9、 现在,把分区2与分区1合并,设置下一个使用分区架构的文件组到PRIMARY并验证数据:
USE Sample_DB GO ALTER PARTITION FUNCTION pf_Quaterly_RangeRight() MERGE RANGE ('20110101') GO ALTER PARTITION SCHEME ps_Quaterly_RangeRight NEXT USED [PRIMARY] SELECT partition_number , rows FROM sys.partitions WHERE object_id = OBJECT_ID('tbl_MyData') ORDER BY partition_number
10、 结果如下:
11、 现在加载数据到tbl_MyData,为了实现这个步骤,需要生成一些数据并插入到分期表tbl_MyStagingData:
USE Sample_DB GO ALTER TABLE tbl_MyStagingData ADD CONSTRAINT ck_tbl_MyStagingData_RecordDateTime CHECK (RecordDateTime>='20120101' AND RecordDateTime<'20120401') INSERT INTO tbl_MyStagingData SELECT '2012' + RIGHT('0' + CAST(( CASE WHEN ID % 3 = 0 THEN 3 ELSE ID % 3 END ) AS VARCHAR), 2) + RIGHT('0' + CAST(( CASE WHEN ID % 28 = 0 THEN 28 ELSE ID % 28 END ) AS VARCHAR), 2) AS RecordDateTime , ID , RecordData FROM ( SELECT TOP 275000 ID = ROW_NUMBER() OVER ( ORDER BY c1.name ) , RecordData = NEWID() FROM sys.columns AS C1 CROSS JOIN sys.columns AS C2 CROSS JOIN sys.columns AS C3 ) AS T GO
12、 现在通过拆分最后一个空的分区来创建一个新的分区,拆分后,把分区tbl_MyStagingData切换到tbl_MyData的分区5中,并设置下一个分区架构使用Primary:
USE Sample_DB GO ALTER PARTITION FUNCTION pf_Quaterly_RangeRight() SPLIT RANGE ('20120401') GO ALTER TABLE tbl_MyStagingData SWITCH PARTITION 1 TO tbl_MyData PARTITION 5 ALTER PARTITION SCHEME ps_Quaterly_RangeRight NEXT USED [PRIMARY] GO
13、现在来验证一下数据:
USE Sample_DB GO SELECT partition_number , rows FROM sys.partitions WHERE object_id = OBJECT_ID('tbl_MyData') ORDER BY partition_number
14、结果如下:
分析:
本文使用了CREATEPARTITION FUNCTION命令,首先创建了一个分区函数pf_Quaterly_RangeRight,通过定义RecordDateTime来做分区列,使得20110101~20120101进行分区。然后使用CREATE PARTITION SCHEME命令创建分区架构ps_Quaterly_RangeRight,映射所有分区到PRIMARY文件组。通过一些列的操作处理分区中的数据。
扩展知识:
本文显示了一个简单的例子,可以在生产环境中配置周期性实现。重点是脚本要足够智能化和动态化。