SQL SERVER分區(qū)具體例子詳解

閱讀目錄

提出問題解決問題

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

回到目錄 提出問題

需解決問題：有一個數(shù)據(jù)表數(shù)據(jù)很大，我們通常的查詢是在一個季度中。我們需要將以往年份的數(shù)據(jù)按不同年份存在文件組里，當年的數(shù)據(jù)分為4個季度存，如果到了新的一年，將之前4個季度的合并到一年中，新的一年又按4個季度分區(qū)。

回到目錄 解決問題

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

建立模擬環(huán)境

1.首先建立數(shù)據(jù)庫，和創(chuàng)建表。

新建個數(shù)據(jù)庫，新建測試表。數(shù)據(jù)文件放在一個好找的文件夾內(nèi)，方便分區(qū)文件一并放在其中。

2.創(chuàng)建模擬數(shù)據(jù)。

我用C#程序模擬插入了一些數(shù)據(jù)，時間從2015-9-1號到2017-4-1每天一天數(shù)據(jù)。此時表的屬性如下，文件組Primary，未分區(qū)。

建立分區(qū)文件

新建5個文件組，對應5個數(shù)據(jù)庫文件，Y2015存放2015年的數(shù)據(jù)，Q1，Q2，Q3，Q4存放4個季度的數(shù)據(jù)，這里我們將文件都放在了同一個文件夾，如果條件允許，放在不同的磁盤上會增加讀寫效率。

建立分區(qū)函數(shù)

分區(qū)函數(shù)RANGE有區(qū)分LEFT和RIGHT
LEFT是第一個分區(qū)小于等于邊界，第二個分區(qū)大于
RIGHT是第一個分區(qū)小于邊界，第二個分區(qū)大于等于

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')

建立分區(qū)方案

這個分區(qū)函數(shù)將分為6個文件組

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

建立好的分區(qū)函數(shù)和分區(qū)方案如下：

建立分區(qū)索引完成分區(qū)

分區(qū)索引必須是聚集索引，我們建標時用SQL里的主鍵設(shè)置會自動將ID設(shè)置為聚集索引這里我們需要把原先的主鍵改為分聚集索引，在建立分區(qū)索引。

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

這樣表分區(qū)就完成了。

查詢分區(qū)中的數(shù)據(jù)

我們可以查下在不同分區(qū)中的數(shù)據(jù)，語句如下：

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

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

這樣查詢所有2015年的數(shù)據(jù)。

分區(qū)新增和合并

現(xiàn)在 2015年的數(shù)據(jù)在2015文件組，2016年數(shù)據(jù)在4個季度的文件組，2017年數(shù)據(jù)在Primary的文件組，現(xiàn)在要將2016年的數(shù)據(jù)放在新增的2016文件組，4個季度的文件組放2017年的數(shù)據(jù)，Primary放2018年后的。

1.新建2016的文件組

2.分區(qū)合并
先將所有季度文件組都合并，這樣2017年數(shù)據(jù)之前都在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');

可以在分區(qū)方案上查看創(chuàng)建SQL語句，這時的分區(qū)方案已經(jīng)更改為：

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

3.分區(qū)新增

首先將2016年的數(shù)據(jù)放在Y2016文件組

--選擇文件組
ALTER?PARTITION?SCHEME?PartitionScheme??
NEXT?USED?[Y2016]?;
--修改分區(qū)函數(shù)??
ALTER?PARTITION?FUNCTION?PartitionFunc()??
SPLIT?RANGE?(N'2016-01-01T00:00:00.000')?;

同理將2017年的數(shù)據(jù)分別放在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')?;

現(xiàn)在查看分區(qū)函數(shù)和分區(qū)方案的創(chuàng)建語句如下：

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')

分區(qū)記錄如下：

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

將分區(qū)表轉(zhuǎn)換成普通表

1.刪除分區(qū)索引

刪除分區(qū)索引后，并沒有編程普通表

2.在原有分區(qū)索引字段，建立普通索引

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