閱讀目錄

提出問題解決問題

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

回到目錄 提出問題

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

回到目錄 解決問題

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

建立模擬環(huán)境

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

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

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

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

建立分區(qū)文件

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

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

分區(qū)函數(shù)RANGE有區(qū)分LEFT和RIGHT
LEFT是第一個(gè)分區(qū)小于等于邊界，第二個(gè)分區(qū)大于
RIGHT是第一個(gè)分區(qū)小于邊界，第二個(gè)分區(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ū)方案

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

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

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

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

分區(qū)索引必須是聚集索引，我們建標(biāo)時(shí)用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個(gè)季度的文件組，2017年數(shù)據(jù)在Primary的文件組，現(xiàn)在要將2016年的數(shù)據(jù)放在新增的2016文件組，4個(gè)季度的文件組放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語句，這時(shí)的分區(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年的各個(gè)季度中

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ū)變動比較大不推薦用合并和刪除的方法，因?yàn)槿菀壮鲥e(cuò)，如果分12個(gè)月建議像下面一樣，先將分區(qū)表轉(zhuǎn)換為普通表，再把普通表分區(qū)。

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

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

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

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

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