1、 需求說(shuō)明
將數(shù)據(jù)庫(kù)demo中的表按照日期字段進(jìn)行水平分區(qū)分表。要求數(shù)據(jù)文件按一年一個(gè)文件存儲(chǔ)，且分區(qū)的分割點(diǎn)會(huì)根據(jù)時(shí)間的增長(zhǎng)自動(dòng)添加（例如現(xiàn)在是2017年1月1日，將其作為一個(gè)分割點(diǎn)，即將2017年1月1日之前的數(shù)據(jù)存儲(chǔ)到數(shù)據(jù)文件a中，將2017年1月1日的之后的數(shù)據(jù)存儲(chǔ)到數(shù)據(jù)文件b中；當(dāng)時(shí)間到2018年1月1日時(shí)，自動(dòng)將2018年1月1日添加為一個(gè)新的分區(qū)分割點(diǎn)，并將2017年1月1日至2018年1月1日的數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)文件b中，將2018年1月1日之后的數(shù)據(jù)存儲(chǔ)在一個(gè)新的數(shù)據(jù)文件c中，以此類推）。
2、實(shí)現(xiàn)思路
2.1分區(qū)原理
要實(shí)現(xiàn)這一功能，首先要了解數(shù)據(jù)庫(kù)對(duì)水平分區(qū)表進(jìn)行分區(qū)存儲(chǔ)的原理。
所謂水平分區(qū)分表，就是把邏輯上的一個(gè)表，在物理上按照你指定的規(guī)則分放到不同的文件里，把一個(gè)大的數(shù)據(jù)文件拆分為多個(gè)小文件，還可以把這些小文件放在不同的磁盤下。這樣把一個(gè)大的文件拆分成多個(gè)小文件，便于我們對(duì)數(shù)據(jù)的管理。
2.2 水平分區(qū)優(yōu)點(diǎn)
l 便于存檔
l 便于管理：備份恢復(fù)時(shí)可以單一的備份或者恢復(fù)某一個(gè)分區(qū)
l 提高可用性：一個(gè)分區(qū)故障，不影響其他分區(qū)的正常使用
l 提高性能：提升查詢數(shù)據(jù)的速度
2.3 實(shí)現(xiàn)思路
① 創(chuàng)建數(shù)據(jù)庫(kù)
② 在創(chuàng)建的數(shù)據(jù)庫(kù)中添加文件組
③ 在文件組中添加新的文件
④ 定義分區(qū)函數(shù)
⑤ 定義分區(qū)架構(gòu)
⑥ 定義分區(qū)表
⑦ 定義代理作業(yè)，自動(dòng)添加分區(qū)分割點(diǎn)
⑧ 測(cè)試數(shù)據(jù)
注意：
² 分區(qū)表依賴于分區(qū)架構(gòu)，而分區(qū)架構(gòu)又依賴與分區(qū)函數(shù)，所以在穿件分區(qū)函數(shù)、分區(qū)架構(gòu)、分區(qū)表是要按照對(duì)應(yīng)的順序創(chuàng)建。
² 分區(qū)函數(shù)并不屬于具體的分區(qū)架構(gòu)和分區(qū)表，它們之間僅僅是使用關(guān)系。
² 分區(qū)表只能在創(chuàng)建的時(shí)候指定為分區(qū)表
3、實(shí)現(xiàn)步驟
3.1代碼創(chuàng)建分區(qū)表
3.1.1 創(chuàng)建數(shù)據(jù)庫(kù)
新建數(shù)據(jù)庫(kù)，并將其命名為demo
3.1.2 添加文件組
代碼格式：
alter database <數(shù)據(jù)庫(kù)名稱> add filegroup<文件組名>
代碼示例：
alter database demoadd filegroup demofilegroup
3.1.3 添加文件
代碼格式：
alter database <數(shù)據(jù)庫(kù)名稱> add file <數(shù)據(jù)標(biāo)識(shí)> to filegroup<文件組名稱>
注意：數(shù)據(jù)標(biāo)識(shí)中name為邏輯文件名、filename為物理文件路徑名、size為文件初始大小（單位：kb/mb/gb/tb）、filegrowth為文件自動(dòng)增量（單位：kb/mb/gb/tb）、maxsize為文件增大的最大大小（單位：kb/mb/gb/tb/unlimited）
代碼示例：
|
1
2
3
4
5
6
|
alter databasedemo add file ( name='demofile1', size=5mb, filegrowth=5mb) tofilegroup demofilegroup |
在此我們重復(fù)執(zhí)行示例代碼，在示例文件組下添加三個(gè)文件。
3.1.4 定義分區(qū)函數(shù)
分區(qū)函數(shù)是用于判定數(shù)據(jù)行該屬于哪個(gè)分區(qū),通過(guò)分區(qū)函數(shù)中設(shè)置邊界值來(lái)使得根據(jù)行中特定列的值來(lái)確定其分區(qū)。
代碼格式：
create partitionfunction partition_function_name( input_parameter_type )
asrange [ left | right ]
forvalues ( [ boundary_value [ ,...n ] ] )
[ ; ]
其中“left”和“right”決定了“values”中的邊界值被劃分到哪一個(gè)分區(qū)中（即，邊界值屬于左側(cè)分區(qū)還是右側(cè)分區(qū)）。
代碼示例：
|
1
2
3
|
create partitionfunction demopartitionfunction( datetime2(0) ) asrange right forvalues ('2016-01-01 00:00:00','2017-01-01 00:00:00') |
查看分區(qū)函數(shù)是否創(chuàng)建成功：
select * from sys.partition_functions
3.1.5 定義分區(qū)架構(gòu)
定義完分區(qū)函數(shù)僅僅是知道了如何將列的值區(qū)分到了不同的分區(qū)，而每個(gè)分區(qū)的存儲(chǔ)方式，則需要分區(qū)構(gòu)架來(lái)定義。分區(qū)構(gòu)架僅僅是依賴分區(qū)函數(shù).分區(qū)構(gòu)架中負(fù)責(zé)分配每個(gè)區(qū)屬于哪個(gè)文件組，而分區(qū)函數(shù)是決定如何在邏輯上分區(qū)。
代碼格式：
create partitionscheme partition_scheme_name
aspartition partition_function_name
[ all ]to ( { file_group_name | [ primary ] } [ ,...n ] )
[ ; ]
代碼示例：
|
1
2
3
|
create partitionscheme demopartitionscheme aspartition demopartitionfunction to ( demofilegroup,[primary],demofilegroup) |
查看分區(qū)架構(gòu)是否創(chuàng)建完成：
select * from sys.partition_schemes
3.1.6 定義分區(qū)表
表在創(chuàng)建的時(shí)候就已經(jīng)決定是否是分區(qū)表了。雖然在很多情況下都是你在發(fā)現(xiàn)已經(jīng)表已經(jīng)足夠大的時(shí)候才想到要把表分區(qū)，但是分區(qū)表只能夠在創(chuàng)建的時(shí)候指定為分區(qū)表。
代碼格式：
create tabletable_name(
...
) onpartition_scheme_name(column_name)
代碼示例：
|
1
2
3
4
5
|
create tabledemotable(demo_id bigint,demo_date datetime2(0),demo_desc varchar(50)) ondemopartitionscheme(demo_date) |
3.2 界面向?qū)П矸謪^(qū)
在3.4、3.5、3.6中，我們已經(jīng)詳細(xì)的描述了如何定義分區(qū)函數(shù)、分區(qū)架構(gòu)以及分區(qū)表，但這些都是通過(guò)代碼實(shí)現(xiàn)的，下面，我們來(lái)通過(guò)sql server 2012 management studio的界面向?qū)?chuàng)建分區(qū)表。
3.2.1 創(chuàng)建數(shù)據(jù)庫(kù)
右鍵點(diǎn)擊“數(shù)據(jù)庫(kù)”，選擇第一項(xiàng)“新建數(shù)據(jù)庫(kù)(n)…”，新建數(shù)據(jù)庫(kù)，如圖1所示：
圖1 新建數(shù)據(jù)庫(kù)
3.2.2 創(chuàng)建文件組
右鍵數(shù)據(jù)庫(kù)demo，選擇“屬性”，如圖2所示：
圖2 數(shù)據(jù)庫(kù)屬性界面
在屬性界面中，點(diǎn)擊箭頭所示的“文件組”選項(xiàng)，進(jìn)入文件組編輯界面，如圖3所示：
圖3 文件組管理界面
在文件組管理界面中點(diǎn)擊箭頭①所示的“添加”選項(xiàng)，添加新的文件組，界面中會(huì)出現(xiàn)箭頭②所示的屬性框，并鍵入對(duì)應(yīng)的屬性值，之后點(diǎn)擊“確定”，完成新建文件組。
3.2.3 添加文件
和添加文件組的方式一樣，右鍵數(shù)據(jù)庫(kù)demo，選擇“屬性”，打開數(shù)據(jù)庫(kù)屬性界面，這次選擇“文件”，打開文件管理界面，如圖4所示：
圖4 文件管理界面
在文件管理界面中，點(diǎn)擊箭頭①所示的“添加”選項(xiàng)，添加新的文件，在新添加的箭頭②所示的區(qū)域，根據(jù)實(shí)際需求，填寫對(duì)應(yīng)的文件屬性值，填寫完成后點(diǎn)擊“確定”。其中，一個(gè)文件組中可以添加多個(gè)文件，即“文件組”屬性的值是可以重復(fù)的。
3.2.4 定義分區(qū)表
在sql server 2012 management studio的界面中，找到目標(biāo)數(shù)據(jù)庫(kù)下的“表”菜單，右鍵點(diǎn)擊，選擇“新建數(shù)據(jù)庫(kù)表”，打開新建數(shù)據(jù)庫(kù)表界面，新建一個(gè)分區(qū)表。如圖5所示：
圖5 新建數(shù)據(jù)庫(kù)表
3.2.5 添加分區(qū)函數(shù)和分區(qū)架構(gòu)
完成新建分區(qū)表后，我們就可以在分區(qū)表上添加分區(qū)函數(shù)和分區(qū)架構(gòu)了。右鍵點(diǎn)擊分區(qū)表，選擇“存儲(chǔ)”，然后選擇“創(chuàng)建分區(qū)”，開始添加分區(qū)函數(shù)和分區(qū)架構(gòu)，如圖6所示：
圖6 新建分區(qū)界面
點(diǎn)擊“下一步”，如圖7所示：
圖7 選擇分區(qū)列
在圖7所示的界面中，勾選分區(qū)列，勾選完成后，選擇“下一步”，如圖8所示：
圖8 填寫分區(qū)函數(shù)
在圖8所示的界面填寫分區(qū)函數(shù)名稱，填寫完成后點(diǎn)擊“下一步”，如圖9所示：
圖9 填寫分區(qū)架構(gòu)
在圖9所示的界面中填寫需要?jiǎng)?chuàng)建的分區(qū)架構(gòu)的名稱，填寫完成后點(diǎn)擊“下一步”，如圖10所示：
圖10 指定文件組
如圖10所示，按照?qǐng)D示箭頭步驟，一步步設(shè)置文件組參數(shù)。首先選擇分區(qū)邊界值劃分在左邊界分區(qū)還是右邊界分區(qū)，然后進(jìn)行第二步，設(shè)置分區(qū)所屬文件組，再設(shè)置分區(qū)邊界值（該值要與分區(qū)表的分區(qū)字段類型對(duì)應(yīng)），最后點(diǎn)擊“預(yù)計(jì)存儲(chǔ)空間(e)”對(duì)其他參數(shù)進(jìn)行自動(dòng)填充。設(shè)置完成后點(diǎn)擊“下一步”，如圖11所示：
圖11 腳本設(shè)置
在圖11所示的界面中，根據(jù)實(shí)際需求完成最后的設(shè)置(一般不做設(shè)置)，然后點(diǎn)擊“完成”，在下一個(gè)界面中再次點(diǎn)擊“完成”，然后等待數(shù)據(jù)庫(kù)執(zhí)行操作，最后關(guān)閉界面。
分區(qū)完成后，右鍵點(diǎn)擊分區(qū)表，選擇“屬性”，然后選擇“存儲(chǔ)”，打開如圖12所示界面：
圖12 表分區(qū)查看
從圖12中可以看到數(shù)據(jù)庫(kù)表已經(jīng)完成分區(qū)。
3.3 動(dòng)態(tài)添加分割點(diǎn)
要完成動(dòng)態(tài)的向分區(qū)函數(shù)中添加分割點(diǎn)的功能，首先我們來(lái)理一下思路：首先我們要向文件組中添加一個(gè)新的文件或者直接添加一個(gè)新的文件組，添加完成后，需要修改分區(qū)架構(gòu)，來(lái)告知數(shù)據(jù)庫(kù)新分的分區(qū)數(shù)據(jù)存儲(chǔ)到那個(gè)文件或者文件組中，最后在分區(qū)函數(shù)中添加新的分割點(diǎn)，完成動(dòng)態(tài)添加分區(qū)分割點(diǎn)的功能。
根據(jù)這個(gè)思路，我編寫的了一個(gè)存儲(chǔ)過(guò)程，用于動(dòng)態(tài)的添加分割點(diǎn)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
create proc[dbo].[demo_filegroup_add]asdeclare@file_name varchar(20),--要添加的文件名稱@add_sql nvarchar(max)--在文件組下添加新文件的sql語(yǔ)句begin set @file_name='demofile'+left((convert(varchar,(dateadd(yy, datediff(yy,0,(dateadd(yy,1,getdate()))), 0)),120)),4)--動(dòng)態(tài)拼接文件名set @add_sql='alter database demo add file (name='+@file_name+',filename=''d:\programfiles\microsoft sql server\mssql11.mssqlserver\mssql\data\'+@file_name+'.mdf'',size=5mb,filegrowth=5mb)to filegroup demofilegroup'--select @add_sqlexec sp_executesql@add_sql--執(zhí)行向文件組中添加文件的sql語(yǔ)句alter partitionscheme demopartitionschemenext used 'demofilegroup' --告知數(shù)據(jù)庫(kù)新建立的分區(qū)放在哪個(gè)文件組（修改分區(qū)架構(gòu)）alter partitionfunction demopartitionfunction() split range (convert(varchar,dateadd(yy, datediff(yy,0,(dateadd(yy,1,getdate()))), 0),120))--在分區(qū)函數(shù)中添加分割點(diǎn)end |
注意：在執(zhí)行該存儲(chǔ)過(guò)程之前，一定要確保文件組中沒(méi)有即將添加的文件，并且在分區(qū)函數(shù)中，沒(méi)有要添加的分割點(diǎn)，否則會(huì)報(bào)錯(cuò)，存儲(chǔ)過(guò)程不能執(zhí)行。
4、測(cè)試數(shù)據(jù)
4.1 添加測(cè)試數(shù)據(jù)
4.1.1 新建測(cè)試表
新建一個(gè)未分區(qū)的testtable表，其表結(jié)構(gòu)與分區(qū)表demotable表結(jié)構(gòu)完全一致，代碼如下：
|
1
2
3
4
5
|
create table[dbo].[testtable]( [demo_id][bigint], [demo_date][datetime2](0), [demo_desc][varchar](50)) |
4.1.2 編寫t-sql添加測(cè)試數(shù)據(jù)
t-sql語(yǔ)句如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
declare@num bigint, --id@test_date datetime2(0),--時(shí)間@test_desc varchar(300),--描述@count int--計(jì)數(shù)器beginset @num= 0 --設(shè)置初始idset @test_date= '2015-01-01 00:00:00'--設(shè)置初始日期set @test_desc='屈賈誼于長(zhǎng)沙，無(wú)非明主；竄梁鴻于海曲，豈乏明時(shí)？'while (@test_date<'2019-01-01 00:00:00') --設(shè)置日期上限begin set@count = 0 while(@count<10)--每個(gè)時(shí)間點(diǎn)添加10條數(shù)據(jù) begin insertinto dbo.demotable values(@num,@test_date,@test_desc+cast(@count as varchar)) --添加數(shù)據(jù) set@count=@count+1 --計(jì)數(shù)自增 set@num = @num +1 --id自增 end set@test_date = dateadd(mi,1,@test_date) --每一個(gè)時(shí)間點(diǎn)添加完10條數(shù)據(jù)后，時(shí)間自增1分endend |
修改t-sql語(yǔ)句中insert部分的表明，分別向兩張表中添加測(cè)試數(shù)據(jù)，添加21038400行數(shù)據(jù)，結(jié)果如下：
圖13 分區(qū)表插入數(shù)據(jù)耗時(shí)統(tǒng)計(jì)
圖14 分區(qū)表存儲(chǔ)信息
圖15 普通表插入數(shù)據(jù)耗時(shí)統(tǒng)計(jì)
圖16 普通表存儲(chǔ)信息
從圖13與圖15中可以看出，同樣插入21038400行數(shù)據(jù)，分區(qū)表耗時(shí)3740秒，普通表耗時(shí)3920秒，分區(qū)表快4.6%。考慮到運(yùn)行環(huán)境對(duì)測(cè)試數(shù)據(jù)的影響，在此我們先對(duì)此數(shù)據(jù)不做評(píng)價(jià)，畢竟4.6%不是很明顯。
而從圖14與圖16的對(duì)比中可以看出，分區(qū)表的索引空間為0.125mb，而普通表的索引空間為0.008mb。那么為什么分區(qū)表的索引空間要比普通表的索引空間大呢？其實(shí)這個(gè)問(wèn)題個(gè)人理解大致是：創(chuàng)建分區(qū)表就是將數(shù)據(jù)存儲(chǔ)在不同的文件中，然后數(shù)據(jù)庫(kù)會(huì)根據(jù)分區(qū)函數(shù)和分區(qū)架構(gòu)，以分區(qū)列為索引列，創(chuàng)建分區(qū)索引來(lái)管理數(shù)據(jù)存放的位置，所以分區(qū)表的索引空間理所當(dāng)然會(huì)比普通標(biāo)表的索引空間大。
默認(rèn)情況下，分區(qū)表中創(chuàng)建的索引使用與分區(qū)表相同分區(qū)架構(gòu)和分區(qū)列，這樣，索引將于表對(duì)齊。將表與其索引對(duì)齊，可以使管理工作更容易進(jìn)行，對(duì)于滑動(dòng)窗口方案尤其如此。若要啟動(dòng)分區(qū)切換，表的所有索引都必須對(duì)齊。
在創(chuàng)建索引時(shí)，也可以指定不同的分區(qū)方案（schema）或單獨(dú)的文件組（filegroup）來(lái)存儲(chǔ)索引，這樣sql server 不會(huì)將索引與表對(duì)齊。
在已分區(qū)的表上創(chuàng)建索引（分區(qū)索引）時(shí)，應(yīng)該注意以下事項(xiàng)：
l 唯一索引
建立唯一索引（聚集或者非聚集）時(shí)，分區(qū)列必須出現(xiàn)在索引列中。此限制將使sql server只調(diào)查單個(gè)分區(qū)，并確保表中寵物的新鍵值。如果分區(qū)依據(jù)列不可能包含在唯一鍵中，則必須使用dml觸發(fā)器，而不是強(qiáng)制實(shí)現(xiàn)唯一性。
l 非唯一索引
對(duì)非唯一的聚集索引進(jìn)行分區(qū)時(shí)，如果未在聚集鍵中明確指定分區(qū)依據(jù)列，默認(rèn)情況下sql server 將在聚集索引列中添加分區(qū)依據(jù)列。
對(duì)非唯一的非聚集索引進(jìn)行分區(qū)時(shí)，默認(rèn)情況下sql server 將分區(qū)依據(jù)列添加為索引的包含性列，以確保索引與基表對(duì)齊，若果索引中已經(jīng)存在分區(qū)依據(jù)列，sql server 將不會(huì)像索引中添加分區(qū)依據(jù)列。
5、補(bǔ)充說(shuō)明
5.1 分區(qū)分表理解
分區(qū)分表分為垂直分區(qū)分表和水平分區(qū)分表，根據(jù)我自己查閱資料，總結(jié)如下：
垂直分區(qū)分表是在sql server 2005之前大量使用的，垂直分表相對(duì)很少見到和用到，因?yàn)檫@可能是數(shù)據(jù)庫(kù)設(shè)計(jì)上的問(wèn)題了。如果數(shù)據(jù)庫(kù)中一張表有部分字段幾乎從不不更改但經(jīng)常查詢，而部分字段的數(shù)據(jù)頻繁更改，這種設(shè)計(jì)放到同一個(gè)表中就不合理了，相互影響太大了。在已存在改情況的表的時(shí)候，可以考慮按列拆分表，即垂直拆分。拆分完成后，通過(guò)分表之間設(shè)置外鍵關(guān)聯(lián)，然后創(chuàng)建視圖和觸發(fā)器等對(duì)表進(jìn)行增、刪、改、查操作。
而水平分區(qū)分表是sql server2005之后被大量使用的。其原理就是將邏輯上的一個(gè)表，在物理上拆分，將數(shù)據(jù)存儲(chǔ)在不同的文件組中，這個(gè)我們前邊已經(jīng)講過(guò)了，這里就不在贅述。
5.2 水平分區(qū)分表疑惑
在自己學(xué)習(xí)水平分區(qū)分表的過(guò)程中發(fā)現(xiàn)一個(gè)問(wèn)題，描述如下：
在創(chuàng)建分區(qū)表的時(shí)候，似乎可以將不同分區(qū)的數(shù)據(jù)存放在同一個(gè)文件組下的不同文件中，也可以將不同分區(qū)的數(shù)據(jù)分別存放在不同的文件組，那么這兩種存儲(chǔ)數(shù)據(jù)的方式對(duì)數(shù)據(jù)查詢的性能有影響嗎？
這個(gè)問(wèn)題我覺(jué)得可以做一個(gè)小小的測(cè)試。
5.3 其它說(shuō)明
學(xué)習(xí)是永無(wú)止境的，這篇文章只是我個(gè)人在學(xué)習(xí)sql server水平分區(qū)分表的時(shí)候做的一些總結(jié)，其中添加了一些個(gè)人理解，如果有不對(duì)的地方，歡迎與我交流，大家相互學(xué)習(xí)，共同進(jìn)步。
原文鏈接：https://blog.csdn.net/exceptionalboy/article/details/78851327
