SQL Server數(shù)據(jù)庫(kù)分區(qū)分表的示例分析

這篇文章主要介紹SQL Server數(shù)據(jù)庫(kù)分區(qū)分表的示例分析，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

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中，以此類(lèi)推）。

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è)小文件，還可以把這些小文件放在不同的磁盤(pán)下。這樣把一個(gè)大的文件拆分成多個(gè)小文件，便于我們對(duì)數(shù)據(jù)的管理。

2.2 水平分區(qū)優(yōu)點(diǎn)

l  便于存檔

l  便于管理：備份恢復(fù)時(shí)可以單一的備份或者恢復(fù)某一個(gè)分區(qū)

l  提高可用性：一個(gè)分區(qū)故障，不影響其他分區(qū)的正常使用

l  提高性能：提升查詢(xún)數(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ū)表依賴(lài)于分區(qū)架構(gòu)，而分區(qū)架構(gòu)又依賴(lài)與分區(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ù)名稱(chēng)> ADD FILEGROUP<文件組名>

代碼示例：

ALTER DATABASE DemoADD FILEGROUP DemoFileGroup

3.1.3  添加文件

代碼格式：

ALTER DATABASE <數(shù)據(jù)庫(kù)名稱(chēng)> ADD FILE <數(shù)據(jù)標(biāo)識(shí)> TO FILEGROUP<文件組名稱(chēng)>

注意：數(shù)據(jù)標(biāo)識(shí)中name為邏輯文件名、filename為物理文件路徑名、size為文件初始大小（單位：kb/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)架僅僅是依賴(lài)分區(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，選擇“屬性”，打開(kāi)數(shù)據(jù)庫(kù)屬性界面，這次選擇“文件”，打開(kāi)文件管理界面，如圖4所示：

圖4 文件管理界面

在文件管理界面中，點(diǎn)擊箭頭①所示的“添加”選項(xiàng)，添加新的文件，在新添加的箭頭②所示的區(qū)域，根據(jù)實(shí)際需求，填寫(xiě)對(duì)應(yīng)的文件屬性值，填寫(xiě)完成后點(diǎn)擊“確定”。其中，一個(gè)文件組中可以添加多個(gè)文件，即“文件組”屬性的值是可以重復(fù)的。

3.2.4 定義分區(qū)表

在SQL Server 2012 Management Studio的界面中，找到目標(biāo)數(shù)據(jù)庫(kù)下的“表”菜單，右鍵點(diǎn)擊，選擇“新建數(shù)據(jù)庫(kù)表”，打開(kāi)新建數(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ū)”，開(kāi)始添加分區(qū)函數(shù)和分區(qū)架構(gòu)，如圖6所示：

圖6 新建分區(qū)界面

點(diǎn)擊“下一步”，如圖7所示：

圖7 選擇分區(qū)列

在圖7所示的界面中，勾選分區(qū)列，勾選完成后，選擇“下一步”，如圖8所示：

圖8 填寫(xiě)分區(qū)函數(shù)

在圖8所示的界面填寫(xiě)分區(qū)函數(shù)名稱(chēng)，填寫(xiě)完成后點(diǎn)擊“下一步”，如圖9所示：

圖9 填寫(xiě)分區(qū)架構(gòu)

在圖9所示的界面中填寫(xiě)需要?jiǎng)?chuàng)建的分區(qū)架構(gòu)的名稱(chēng)，填寫(xiě)完成后點(diǎn)擊“下一步”，如圖10所示：

圖10 指定文件組

如圖10所示，按照?qǐng)D示箭頭步驟，一步步設(shè)置文件組參數(shù)。首先選擇分區(qū)邊界值劃分在左邊界分區(qū)還是右邊界分區(qū)，然后進(jìn)行第二步，設(shè)置分區(qū)所屬文件組，再設(shè)置分區(qū)邊界值（該值要與分區(qū)表的分區(qū)字段類(lèi)型對(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ǔ)”，打開(kāi)如圖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è)思路，我編寫(xiě)的了一個(gè)存儲(chǔ)過(guò)程，用于動(dòng)態(tài)的添加分割點(diǎn)：

CREATE proc[dbo].[Demo_FileGroup_Add]
as
declare
@file_name varchar(20),--要添加的文件名稱(chēng)
@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 編寫(xiě)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(@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分
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)常查詢(xún)，而部分字段的數(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ò)了，這里就不在贅述。

以上是“SQL Server數(shù)據(jù)庫(kù)分區(qū)分表的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！