SQL SERVER分區具體例子詳解

標籤：--   count   測試表   知識   表分區   函數   有助於   existing   sch   

在日常工作中，我們會遇到以下的情況，一個表每日數萬級的增長，而查詢的資料通常是在本月或今年，以前的資料偶爾會用到，但查詢和插入的效率越來越慢，用資料庫分區會有助於解決這個問題。關於分區的理論知識網上很多我這裡就不在累贅，我從一個實際例子出發，看如何將一個已經運行了很長時間的普通表進行分區。

回到目錄提出問題

需解決問題：有一個資料表資料很大，我們通常的查詢是在一個季度中。我們需要將以往年份的資料按不同年份存在檔案組裡，當年的資料分為4個季度存，如果到了新的一年，將之前4個季度的合并到一年中，新的一年又按4個季度分區。

回到目錄解決問題

好了我們將一步步的開始解決問題。

建立類比環境

1.首先建立資料庫，和建立表。

建立個資料庫，建立測試表。資料檔案放在一個好找的檔案夾內，方便分區檔案一併放在其中。

2.建立類比資料。

我用C#程式類比插入了一些資料，時間從2015-9-1號到2017-4-1每天一天資料。此時表的屬性如下，檔案組Primary，未分區。

建立分區檔案

建立5個檔案組，對應5個資料庫檔案，Y2015存放2015年的資料，Q1，Q2，Q3，Q4存放4個季度的資料，這裡我們將檔案都放在了同一個檔案夾，如果條件允許，放在不同的磁碟上會增加讀寫效率。

建立分區函數

分區函數RANGE有區分LEFT和RIGHT
LEFT是第一個分區小於等於邊界，第二個分區大於
RIGHT是第一個分區小於邊界，第二個分區大於等於

CREATE PARTITION FUNCTION [PartitionFunc](datetime) AS RANGE RIGHT FOR VALUES (N‘2016-01-01T00:00:00‘, N‘2016-04-01T00:00:00‘,N‘2016-07-01T00:00:00‘,N‘2016-10-01T00:00:00‘,N‘2017-01-01T00:00:00‘)

建立資料分割配置

這個分區函數將分為6個檔案組

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [Q1],[Q2],[Q3],[Q4],[PRIMARY])

建立好的分區函數和資料分割配置如下：

建立分區索引完成分區

分區索引必須是叢集索引，我們建標時用SQL裡的主鍵設定會自動將ID設定為叢集索引這裡我們需要把原先的主鍵改為分叢集索引，在建立分區索引。

CREATE CLUSTERED INDEX [ClusteredIndex_CreateDate] ON [dbo].[SchemTest](    [CreateDate])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionScheme]([CreateDate])

這樣表分區就完成了。

查詢分區中的資料

我們可以查下在不同分區中的資料，語句如下：

select $PARTITION.PartitionFunc(CreateDate) as 分區編號,count(ID) as 記錄數 from SchemTest group by $PARTITION.PartitionFunc(CreateDate)  

select * from SchemTest where $PARTITION.PartitionFunc(CreateDate)=1  

這樣查詢所有2015年的資料。

分區新增和合并

現在 2015年的資料在2015檔案組，2016年資料在4個季度的檔案組，2017年資料在Primary的檔案組，現在要將2016年的資料放在新增的2016檔案組，4個季度的檔案組放2017年的資料，Primary放2018年後的。

1.建立2016的檔案組

2.分區合并
先將所有季度檔案組都合并，這樣2017年資料之前都在2015檔案組

ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-01-01T00:00:00‘);ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-04-01T00:00:00‘);  ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-07-01T00:00:00‘);  ALTER PARTITION FUNCTION PartitionFunc() MERGE RANGE (N‘2016-10-01T00:00:00‘);  

可以在資料分割配置上查看建立SQL語句，這時的資料分割配置已經更改為：

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [PRIMARY])

3.分區新增

首先將2016年的資料放在Y2016檔案組

--選擇檔案組ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Y2016] ;--修改分區函數  ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2016-01-01T00:00:00.000‘) ;

同理將2017年的資料分別放在2017年的各個季度中

ALTER PARTITION FUNCTION PartitionFunc()  MERGE RANGE (N‘2017-01-01T00:00:00‘);ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q1] ;ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-01-01T00:00:00.000‘) ;ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q2] ; ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-04-01T00:00:00.000‘) ;ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q3] ;ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-07-01T00:00:00.000‘) ;ALTER PARTITION SCHEME PartitionScheme  NEXT USED [Q4] ;ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2017-10-01T00:00:00.000‘) ;ALTER PARTITION SCHEME PartitionScheme  NEXT USED [PRIMARY] ;ALTER PARTITION FUNCTION PartitionFunc()  SPLIT RANGE (N‘2018-01-01T00:00:00.000‘) ;

現在查看分區函數和資料分割配置的建立語句如下：

CREATE PARTITION SCHEME [PartitionScheme] AS PARTITION [PartitionFunc] TO ([Y2015], [Y2016], [Q1], [Q2], [Q3], [Q4], [PRIMARY])CREATE PARTITION FUNCTION [PartitionFunc](datetime) AS RANGE RIGHT FOR VALUES (N‘2016-01-01T00:00:00.000‘, N‘2017-01-01T00:00:00.000‘, N‘2017-04-01T00:00:00.000‘, N‘2017-07-01T00:00:00.000‘, N‘2017-10-01T00:00:00.000‘, N‘2018-01-01T00:00:00.000‘)

分區記錄如下：

如果分區變動比較大不推薦用合并和刪除的方法，因為容易出錯，如果分12個月建議像下面一樣，先將分區錶轉換為普通表，再把普通表分區。

將分區錶轉換成普通表

1.刪除分區索引

刪除分區索引後，並沒有編程普通表

2.在原有分區索引欄位，建立普通索引

CREATE CLUSTERED INDEX [IX_SchemTest]  ON SchemTest(CreateDate) ON [Primary]

 

SQL SERVER分區具體例子詳解