第十三章——表和索引分割區（2）——使用拆分刪除和載入大資料

標籤：

原文: 第十三章——表和索引分割區（2）——使用拆分刪除和載入大資料

前言：

        很多時候需要對大資料量進行歸檔或者刪除，並周期性載入大資料量到一個大表中，現在來做個簡單的例子，你經常需要刪除大資料量表中的大量資料。同時，你想載入大量資料到這個表中，當表中資料有數十億時，這個操作可能消耗幾個小時，但是如果你的表有分區，那麼執行起來會很有效。

本文將類比刪除一個季度的資料，並載入整個季度到現有表，其中使用了拆分（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 masterGOIF DB_ID(‘Sample_DB‘) IS NOT NULL     DROP DATABASE Sample_DBCREATE 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%)GOUSE Sample_DBGOCREATE PARTITION FUNCTION pf_Quaterly_RangeRight(DATETIME)AS RANGE RIGHT FOR VALUES(‘20110101‘,‘20110401‘,‘20110701‘,‘20111001‘,‘20120101‘)GOCREATE PARTITION SCHEME ps_Quaterly_RangeRightAS PARTITION pf_Quaterly_RangeRight ALL TO ([PRIMARY])GO

注意：當分區列是datetime類型時，應該總是嘗試使用RANGE RIGHT，這樣會使得分區更好，如果使用RANGE LEFT，你必須考慮特定的time部分，包含毫秒。

3、 建立一個表tbl_MyData並插入275000行資料：

USE Sample_DBGOCREATE TABLE tbl_MyData    (      RecordDateTime DATETIME NOT NULL ,      RecordID INT NOT NULL ,      RecordData VARCHAR(40) NOT NULL    )GOCREATE CLUSTERED INDEX idx_tbl_MyData_RecordDateTimeON 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 TGO

4、 執行下面語句，檢驗分區數量： 

USE Sample_DBGOSELECT  partition_number ,        rowsFROM    sys.partitionsWHERE   object_id = OBJECT_ID(‘tbl_MyData‘)ORDER BY partition_number

5、 結果如下：

6、 現在先移除2011年第一個季度也就是分區1的資料：

USE Sample_DBGOIF OBJECT_ID(‘tbl_MyStagingData‘) IS NOT NULL     DROP TABLE tbl_MyStagingDataCREATE TABLE tbl_MyStagingData    (      RecordDateTime DATETIME NOT NULL ,      RecordID INT NOT NULL ,      RecordData VARCHAR(40) NOT NULL    )GOCREATE CLUSTERED INDEX idx_tbl_MyStagingData_RecordDateTime ON tbl_MyStagingData(RecordDateTime,RecordID)GO

7、 現在，切換tbl_MyData的分區2到分期表tbl_MyStagingData並清空分期表。在清空以後，檢查分區資料：

USE Sample_DBGOALTER TABLE tbl_MyDataSWITCH PARTITION 2 TO tbl_MyStagingData PARTITION 1GOTRUNCATE TABLE tbl_MyStagingDataGOSELECT  partition_number ,        rowsFROM    sys.partitionsWHERE   object_id = OBJECT_ID(‘tbl_MyData‘)ORDER BY partition_number

8、 結果可見，分區已經清空：

9、 現在，把分區2與分區1合并，設定下一個使用分區架構的檔案組到PRIMARY並驗證資料：

 USE Sample_DBGOALTER PARTITION FUNCTION pf_Quaterly_RangeRight()MERGE RANGE (‘20110101‘)GOALTER PARTITION SCHEME ps_Quaterly_RangeRightNEXT USED [PRIMARY]SELECT  partition_number ,        rowsFROM    sys.partitionsWHERE   object_id = OBJECT_ID(‘tbl_MyData‘)ORDER BY partition_number

10、  結果如下：

11、 現在載入資料到tbl_MyData，為了實現這個步驟，需要產生一些資料並插入到分期表tbl_MyStagingData：

USE Sample_DBGOALTER TABLE tbl_MyStagingDataADD CONSTRAINT ck_tbl_MyStagingData_RecordDateTimeCHECK (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 TGO

12、  現在通過拆分最後一個空的分區來建立一個新的分區，拆分後，把分區tbl_MyStagingData切換到tbl_MyData的分區5中，並設定下一個分區架構使用Primary：

USE Sample_DBGOALTER PARTITION FUNCTION pf_Quaterly_RangeRight()SPLIT RANGE (‘20120401‘)GOALTER TABLE tbl_MyStagingDataSWITCH PARTITION 1 TO tbl_MyData PARTITION 5ALTER PARTITION SCHEME ps_Quaterly_RangeRightNEXT USED [PRIMARY]GO

13、現在來驗證一下資料：

USE Sample_DBGOSELECT  partition_number ,        rowsFROM    sys.partitionsWHERE   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檔案組。通過一些列的操作處理分區中的資料。

 

擴充知識：

本文顯示了一個簡單的例子，可以在生產環境中配置周期性實現。重點是指令碼要足夠智能化和動態化。

 

 

第十三章——表和索引分割區（2）——使用拆分刪除和載入大資料