原地址：http://www.cnblogs.com/smjack/archive/2010/03/29/1699941.html
?
雖然對(duì)數(shù)據(jù)庫進(jìn)行分區(qū)本身就能提高查詢的性能，結(jié)合壓縮，也能減少每次查詢的IO。但如果數(shù)據(jù)持續(xù)增長(zhǎng)，過于久遠(yuǎn)的歷史數(shù)據(jù)就成了一個(gè)包袱，它們從 來不在查詢結(jié)果中出現(xiàn)，卻或多或少的影響著每次查詢的時(shí)間，成了一個(gè)揮之不去的陰影。此外，由于一個(gè)分區(qū)表的分區(qū)是有上限的（在2005中這一上限是 1000），我們也并不能在一張表上一直分區(qū)下去。所以在這種情況下 ，我們一般需要同時(shí)擁有兩張表，一張保存了最近的數(shù)據(jù)，用來應(yīng)付所有的查詢，這張表要足夠精簡(jiǎn)，在其上的查詢要足夠敏捷；同時(shí)有另一張表，保存所有過時(shí)的數(shù)據(jù)——我們并不能把過時(shí)的數(shù)據(jù)一刪了事。
這里面的關(guān)鍵問題是，既然數(shù)據(jù)是隨著時(shí)間持續(xù)增長(zhǎng)的，那么當(dāng)下有用的數(shù)據(jù)可能在幾天后就過時(shí)了，那么 怎樣將這一部分過時(shí)的數(shù)據(jù)從活動(dòng)表遷移到存檔表，而且要保證遷移過程的快速、平穩(wěn)呢 ？如果采用常規(guī)的Select、Insert、Delete來進(jìn)行數(shù)據(jù)遷移，會(huì)有如下問題：
- IO過大，效率必然較低。
- 遷移過程表被鎖住，所有查詢都會(huì)被擱置。
- 恢復(fù)困難，如果想將移出的數(shù)據(jù)再移回來，需要進(jìn)行同樣的操作，IO和鎖表的問題同樣存在。
那么很容易想到，利用之前提到的分區(qū)的Switch操作來解決遷移的問題， 將整個(gè)分區(qū)而不是數(shù)據(jù)在活動(dòng)表和存檔表中遷移。 由于Switch的元數(shù)據(jù)操作屬性，這一幾乎沒有什么IO的操作效率極高，而且也不會(huì)鎖表。基于以上方法進(jìn)行的周期性自動(dòng)化的數(shù)據(jù)遷移，就是 Slide Window的基礎(chǔ) 。
原理圖
假設(shè)我們已經(jīng)有一張活動(dòng)表，分了四個(gè)區(qū)，分別對(duì)應(yīng)去年，今年一月、二月以及三月以后的數(shù)據(jù)：
同時(shí)我們有另外一個(gè)存檔表，分成了兩個(gè)區(qū)， 第一個(gè)區(qū)對(duì)應(yīng)今年以前的數(shù)據(jù)，另外一個(gè)分區(qū)的范圍是今年之后：
注意， 活動(dòng)表的第一個(gè)分區(qū) 、 存檔表中的第二個(gè)分區(qū)是沒有數(shù)據(jù)的，這是進(jìn)行Slide Window的前提條件 。
現(xiàn)在，我們考慮將活動(dòng)表中的一月份的數(shù)據(jù)放入存檔表中，而且我們還要保證在遷移之后，兩張表保持和遷移前相似的狀態(tài)。
那么我們可以采用如下的步驟：
1.在存檔表中建立新分區(qū)：
2.將活動(dòng)表的第二個(gè)分區(qū)挪到存檔表的第二個(gè)分區(qū)中：
遷移的結(jié)果如下：
至此，我們已經(jīng)完成了數(shù)據(jù)的遷移，但為了恢復(fù)兩張表到之前的狀態(tài)，我們還需要以下兩個(gè)步驟：
3.合并存檔表的第一和第二個(gè)分區(qū)。
4.拆分活動(dòng)表的第三個(gè)分區(qū)。
最后的結(jié)果，活動(dòng)表：
存檔表：
?
我 們可以看到遷移過后，兩張表的分區(qū)數(shù)量沒有變，而且存檔表的第二個(gè)分區(qū)依然是空的。當(dāng)需要遷移二月份的數(shù)據(jù)時(shí)，我們可以采用和上面完全一樣的步驟進(jìn)行遷 移。而這一過程，類似在時(shí)間軸上開了一個(gè)窗口，將當(dāng)前數(shù)據(jù)在活動(dòng)表上展示，隨著時(shí)間推移，窗口不斷向前滑動(dòng)（活動(dòng)表的邊界前移），而且窗口大小（活動(dòng)表的 分區(qū)數(shù)）始終保持不變，這就是Slide Window（滑動(dòng)窗口）這一名稱的來源。
建立存檔表
建立存檔表最簡(jiǎn) 單的方法是選中分好區(qū)的活動(dòng)表，在Storage菜單中選擇“Manage Partition”，然后選擇“Create a stagin table for partition switching”。建議將“Staging table name”改成固定的沒有數(shù)字后綴的名字，之后隨便選擇一個(gè)“Switch Parition”，最后生成創(chuàng)建存檔表的腳本。
由于指定了待切換的分區(qū)，所以這里腳本中會(huì)添加對(duì)應(yīng)的約束，由于我們要?jiǎng)?chuàng)建的存檔表并不應(yīng)該有邊界限定，所以應(yīng)當(dāng)把腳本中添加邊界約束的部分刪除，運(yùn)行，生成存檔表。
接下來要對(duì)存檔表進(jìn)行分區(qū)，一般來說，存檔表分成兩個(gè)區(qū)就可以應(yīng)對(duì)任意分區(qū)數(shù)量的活動(dòng)表了，當(dāng)然，分的更多也沒有問題。存檔表的分區(qū)邊界要和活動(dòng)表的對(duì)應(yīng)邊界一致，也就是存檔表的第一個(gè)分區(qū)和第二個(gè)分區(qū)的邊界等于活動(dòng)表的第一個(gè)分區(qū)和第二個(gè)分區(qū)的邊界。
此時(shí)存檔表中并沒有數(shù)據(jù)，我們可以用
ALTER TABLE
[STable] SWITCH
PARTITION
1
TO
[DTable]
PARTITION
1
將活動(dòng)表的第一個(gè)分區(qū)遷移到存檔表的第一個(gè)分區(qū)中。這樣活動(dòng)表的第一個(gè)分區(qū)、存檔表的第二個(gè)分區(qū)為空，也就達(dá)成了前文所述的執(zhí)行Slide Window的前提條件。
自動(dòng)執(zhí)行
有了存檔表，就可以進(jìn)行滑動(dòng)窗口了。以用時(shí)間類型字段做分區(qū)依據(jù)的表為例，這里我把執(zhí)行腳本存到一個(gè)存儲(chǔ)過程里：
CREATE PROCEDURE
[dbo]
.
[sp_SlideWindow]
@SplitRange
SMALLDATETIME
-- 指定活動(dòng)表新增分區(qū)的邊界
AS
BEGIN
DECLARE
@SwitchRange
SMALLDATETIME
DECLARE
@MergeRange
SMALLDATETIME
--獲得活動(dòng)表、存檔表合并分區(qū)以及存檔表的新分區(qū)的邊界
SELECT
@MergeRange
=
CONVERT
(
SMALLDATETIME
,
value
)
FROM
sys
.
partition_range_values
,
sys
.
partition_functions
WHERE
sys
.
partition_functions
.
function_id
=
sys
.
partition_range_values
.
function_id
AND
sys
.
partition_functions
.
name
=
'E_Alive_Partition_Func'
AND
boundary_id
=
1
SELECT
@SwitchRange
=
CONVERT
(
SMALLDATETIME
,
value
)
FROM
sys
.
partition_range_values
,
sys
.
partition_functions
WHERE
sys
.
partition_functions
.
function_id
=
sys
.
partition_range_values
.
function_id
AND
sys
.
partition_functions
.
name
=
'E_Alive_Partition_Func'
AND
boundary_id
=
2
BEGIN TRANSACTION
ALTER PARTITION
SCHEME [E_Alive_Partition_Schema]
NEXT
USED [PRIMARY]
ALTER PARTITION
SCHEME [E_Staging_Partition_Schema]
NEXT
USED [PRIMARY]
--在活動(dòng)表中新增分區(qū)
ALTER PARTITION FUNCTION
[E_Alive_Partition_Func]
()
SPLIT
RANGE
(
CONVERT
(
NVARCHAR
,
@SplitRange
,
120
))
--在存檔表中新增分區(qū)
ALTER PARTITION FUNCTION
[E_Staging_Partition_Func]
()
SPLIT
RANGE
(
CONVERT
(
NVARCHAR
,
@SwitchRange
,
120
))
--切換分區(qū)
ALTER TABLE
[Alive_Table] SWITCH
PARTITION
2
TO
[Staging_Table]
PARTITION
2
--合并活動(dòng)表分區(qū)與存檔表分區(qū)
ALTER PARTITION FUNCTION
[E_Alive_Partition_Func]
()
MERGE RANGE
(
CONVERT
(
NVARCHAR
,
@MergeRange
,
120
))
ALTER PARTITION FUNCTION
[E_Staging_Partition_Func]
()
MERGE RANGE
(
CONVERT
(
NVARCHAR
,
@MergeRange
,
120
))
COMMIT TRANSACTION
END
在整個(gè)滑動(dòng)窗口的操作過程中，活動(dòng)表和存檔表分別合并和拆分了兩次，有四個(gè)相關(guān)邊界值。而由于活動(dòng)表的合并邊界值和存檔表的合并邊界值是一樣的，所 以實(shí)際有三個(gè)邊界值。其中合并邊界就是第一個(gè)分區(qū)和第二個(gè)分區(qū)的邊界，而存檔表的拆分邊界就是活動(dòng)表的第二個(gè)分區(qū)和第三個(gè)分區(qū)的邊界，這些都可以通過 sys.partition_range_values, sys.partition_functions表獲得。所以這個(gè)存儲(chǔ)過程 只需要輸入一個(gè)變量，即活動(dòng)表新拆分出的分區(qū)的邊界 。
注意
- 由于新拆分的分區(qū)中非聚集索引不會(huì)應(yīng)用原來的壓縮方式，所以如有需要，應(yīng)當(dāng)在存儲(chǔ)過程中補(bǔ)充對(duì)相應(yīng)索引做壓縮的操作。
- 之所以在存儲(chǔ)過程中先進(jìn)行分區(qū)的拆分，再進(jìn)行分區(qū)切換，最后進(jìn)行分區(qū)合并，是考慮對(duì)空的分區(qū)（切換前的存檔表的第二分區(qū)、切換后的活動(dòng)表的一、二分區(qū)）進(jìn)行拆分或者合并效率比較高。
- 以 上只是Slide Window的一種方式，事實(shí)上，如果對(duì)歷史數(shù)據(jù)不那么在意，我們依然可以用分區(qū)切換的方式，將舊的數(shù)據(jù)移出然后刪除。或者使用多個(gè)存檔表，每次都將活動(dòng) 表的最后一個(gè)分區(qū)移到新的存檔表中，這樣省去了合并存檔表分區(qū)的性能消耗，但多個(gè)存檔表可能在管理上會(huì)比較麻煩。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
