原地址: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ū)中:
至此,我們已經(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ì)您有幫助就好】元
