目錄
- 1、 需求說(shuō)明
- 2、實(shí)現(xiàn)思路
- 2.1分區(qū)原理
- 2.2 水平分區(qū)優(yōu)點(diǎn)
- 2.3 實(shí)現(xiàn)思路
- 3、實(shí)現(xiàn)步驟
- 3.1代碼創(chuàng)建分區(qū)表
- 3.1.1 創(chuàng)建數(shù)據(jù)庫(kù)
- 3.1.2 添加文件組
- 3.1.3 添加文件
- 3.1.4 定義分區(qū)函數(shù)
- 3.1.5 定義分區(qū)架構(gòu)
- 3.1.6 定義分區(qū)表
- 3.2 界面向?qū)П矸謪^(qū)
- 3.2.1 創(chuàng)建數(shù)據(jù)庫(kù)
- 3.2.2 創(chuàng)建文件組
- 3.2.3 添加文件
- 3.2.4 定義分區(qū)表
- 3.2.5 添加分區(qū)函數(shù)和分區(qū)架構(gòu)
- 3.3 動(dòng)態(tài)添加分割點(diǎn)
- 4、測(cè)試數(shù)據(jù)
- 4.1 添加測(cè)試數(shù)據(jù)
- 4.1.1 新建測(cè)試表
- 4.1.2 編寫T-SQL添加測(cè)試數(shù)據(jù)
- 5、補(bǔ)充說(shuō)明
- 5.1 分區(qū)分表理解
- 5.2 水平分區(qū)分表疑惑
- 5.3 其它說(shuō)明
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為文件初始大?。▎挝唬簁b/mb/gb/tb)、filegrowth為文件自動(dòng)增量(單位:kb/mb/gb/tb)、maxsize為文件增大的最大大?。▎挝唬簁b/mb/gb/tb/unlimited)
代碼示例:
ALTER DATABASEDemo ADD FILE (
NAME='DemoFile1',
FILENAME='D:\ProgramFiles\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DemoFile1.mdf',
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ū))。
代碼示例:
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 ] )
[ ; ]
代碼示例:
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)
代碼示例:
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):
CREATE proc[dbo].[Demo_FileGroup_Add]
as
declare
@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_sql
Exec 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)完全一致,代碼如下:
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ǔ)句如下:
declare
@num bigint, --id
@test_date datetime2(0),--時(shí)間
@test_desc varchar(300),--描述
@count int--計(jì)數(shù)器
begin
set @num= 0 --設(shè)置初始id
set @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(@count10)--每個(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分
end
end
修改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ì)很少見(jiàn)到和用到,因?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)步。