SQL Server 大數(shù)據(jù)管理——表分區(qū)

一. ???創(chuàng)建分區(qū)文件組/文件

--創(chuàng)建分區(qū)文件組

alter database test add filegroup test2015

alter database test add filegroup test2016

alter database test add filegroup test2017

alter database test add filegroup test2018

--創(chuàng)建分區(qū)文件

alter database test

add file(name='test2015'

,filename='D:\DB\testPartion\test2015.ndf'

,size=1mb

,filegrowth=1mb)

to filegroup test2015;

alter database test

add file(name='test2016'

,filename='D:\DB\testPartion\test2016.ndf'

,size=1mb

,filegrowth=1mb)

to filegroup test2016;

alter database test

add file(name=N'test2017'

,filename=N'D:\DB\testPartion\test2017.ndf'

,size=1mb

,filegrowth=1mb)

to filegroup test2017

alter database test

add file(name=N'test2018'

,filename=N'D:\DB\testPartion\test2018.ndf'

,size=1mb

,filegrowth=1mb)

to filegroup test2018

二. ???創(chuàng)建分區(qū)函數(shù)

--創(chuàng)建分區(qū)函數(shù)

create partition function f_TestDate(datetime)

as range right for values('2016-01-01','2017-01-01','2018-01-01')

注意：

1.????????F_TestDate 為分區(qū)函數(shù)名，分區(qū)的字段是datetime類型

2.????????Right 表示該分區(qū)包含右邊界值，上面分區(qū)函數(shù)會把數(shù)據(jù)分為


小于2016.1.1

大于等于2016.1.1 且小于2017.1.1

大于等于2017.1.1 且小于2018.1.1

大于等于2018.1.1

四個分區(qū)，若把right換為left，則分區(qū)變?yōu)?/p>


小于等于2016.1.1

大于2016.1.1 且小于等于2017.1.1

大于2017.1.1 且小于等于2018.1.1

大于2018.1.1

三. ???創(chuàng)建分區(qū)方案

--創(chuàng)建分區(qū)方案

create partition scheme s_TestDate

as partition f_TestDate to (test2015,test2016,test2017,test2018)

注意：

1.????????分區(qū)方案是建立在分區(qū)函數(shù)的基礎(chǔ)上的，所以先建立分區(qū)函數(shù)，再建立分區(qū)方案

2.????????分區(qū)個數(shù)比分區(qū)邊界值多1

3.????????本分區(qū)方案每個分區(qū)建在一個文件組上，當(dāng)然也可以把所有分區(qū)建立在一個文件組上

--創(chuàng)建分區(qū)方案,所有分區(qū)均建立在主文件組上

create partition scheme s_TestDate

as partition f_TestDate all to ([primary])

兩種方案的優(yōu)劣待續(xù)……

四. ???創(chuàng)建分區(qū)表

4.1??新建分區(qū)表

create table tradelog

(

ID int,

productID int,

tradedate datetime

) on s_TestDate(tradedate)

注：創(chuàng)建分區(qū)表，用的是s_TestDate分區(qū)方案名稱

4.2??對已有表分區(qū)

若表上沒有聚集索引，可以通過創(chuàng)建聚集索引，對表進(jìn)行分區(qū)

CREATE CLUSTERED INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]

(

[tradedate]

) ON [s_TestDate]([tradedate])

--如果不需要聚集索引,刪除聚集索引

DROP INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]

若表上已有聚集索引，刪除聚集索引，再通過上面腳本重建聚集索引。或者通過WITH（DROP_EXISTING=ON）重建聚集索引，腳本如下：

CREATE CLUSTERED INDEX [CLI_tn_TestDate] ON [dbo].[tradelog_noClusterIndex]

(

[tradedate]

)WITH (DROP_EXISTING = ON) ON [s_TestDate]([tradedate])

五. ???增加分區(qū)

增加分區(qū)的方法是將某個現(xiàn)有的分區(qū)“拆分”為兩個分區(qū)并重新定義新分區(qū)的邊界。

--向分區(qū)表插入1000W行數(shù)據(jù)

DECLARE @max AS INT, @rc AS INT;?

SET @max = 10000000;?

SET @rc = 1;?

INSERT INTO tradelog(id,productID,tradedate) VALUES(1,1,'2014-01-01');?

WHILE @rc * 2 <= @max?

BEGIN?

? ? INSERT INTO dbo.tradelog(id,productID,tradedate) SELECT id + @rc,id + @rc+1,DATEADD(mi,id,tradedate) FROM dbo.tradelog;?

? ? SET @rc = @rc * 2;?

END?

INSERT INTO dbo.tradelog (id,productID,tradedate)

SELECT id + @rc,id + @rc+1,DATEADD(mi,id,tradedate) FROM dbo.tradelog WHERE id + @rc <= @max;?

go?

--查看分區(qū)表的現(xiàn)狀

;with cte as

(select

object_id

,OBJECT_NAME(i.object_id) tableName

,i.index_id

,dds.partition_scheme_id

,dds.destination_id as partition_number

,fg.groupid

,fg.groupname

,f.fileid

,f.name

,f.filename

--,p.partition_id

--,p.rows

from sys.destination_data_spaces dds,sys.indexes i,sys.sysfilegroups fg,sys.sysfiles f

where dds.partition_scheme_id=i.data_space_id

and dds.data_space_id=fg.groupid

and fg.groupid=f.groupid

)

,cte1 as(

select

ps.data_space_id as partition_scheme_id

,ps.name partiton_schemes_name

,pf.name partition_function_name

,pf.function_id

--,prv.value AS BoundaryValue

from sys.partition_schemes ps ,sys.partition_functions pf

where ps.function_id=pf.function_id

--and pf.function_id=prv.function_id

)

select cte.tableName,cte.groupname,cte.name,cte.filename

,cte.partition_number,cte1.partiton_schemes_name,cte1.partition_function_name,p.rows

,prv.boundary_id,prv.value BoundaryValue

from cte

inner join cte1 on cte.partition_scheme_id=cte1 .partition_scheme_id

left join sys.partition_range_values prv on cte1.function_id=prv.function_id and cte.partition_number=prv.boundary_id

left join sys.partitions? p on cte.object_id=p.object_id and cte.index_id=p.index_id and cte.partition_number=p.partition_number

where

cte.object_id=OBJECT_ID('dbo.tradelog','U')

可以看到tradelog表按交易時間列分為4區(qū)，分區(qū)邊界值為16、17、18三年的1月1日，其中

16年以前的數(shù)據(jù)存在文件test2015上

16年數(shù)據(jù)存在文件test2016上

17年數(shù)據(jù)存在文件test2017上

18年及以后的數(shù)據(jù)存在文件test2018上

現(xiàn)在增加一個分區(qū)，將2019以后的數(shù)據(jù)分開，或者說將原4分區(qū)以2019年1月1日為分區(qū)邊界拆分為兩個分區(qū)，具體腳本如下：

--創(chuàng)建新分區(qū)文件組

alter database test add filegroup test2019

--創(chuàng)建新分區(qū)文件

alter database test

add file(name='test2019'

,filename='D:\DB\testPartion\test2019.ndf'

,size=1mb

,filegrowth=1mb)

to filegroup test2019;

alter partition scheme s_TestDate

next used test2019

alter partition function f_TestDate()

split range('2019-01-01 0:00:00')

重新執(zhí)行分區(qū)狀態(tài)查詢腳本，結(jié)果如下圖：

可以看到，源第4分區(qū)被拆分為兩個分區(qū)，并且2019年以后的數(shù)據(jù)被移動到新的文件test2019上。

可以有這樣一個結(jié)論，新增分區(qū)后，新增的邊界值，到下一個分區(qū)邊界值之間的數(shù)據(jù)，將被移動到新的文件上，無論是拆分第1個分區(qū)，還是拆分中間的某個分區(qū)（如拆分第4個分區(qū)），如下圖：

六. ???合并分區(qū)

減少分區(qū)的方法是將兩個分區(qū)的邊界“合并”成一個。 減少分區(qū)操作將重新填充一個分區(qū)而不對另一個分區(qū)進(jìn)行分配。

--分區(qū)合并

alter partition function f_TestDate()

merge range('2018-07-01 0:00:00')




分區(qū)合并的數(shù)據(jù)移動方向剛好和增加分區(qū)的方向相反，分區(qū)合并后，將合并分界點(diǎn)的后一個分區(qū)數(shù)據(jù)移動到前一個分區(qū)的文件中。這個結(jié)論在數(shù)據(jù)自動歸檔中將極為有用，因?yàn)閿?shù)據(jù)歸檔最后一步是將合并后的空文件、文件組回收，這樣就可以確定回收的文件名

七. ???分區(qū)數(shù)據(jù)移到普通表

create table tradelog_partition1

(

ID int,

productID int,

tradedate datetime

) on test2015

alter table tradelog switch partition 1 to tradelog_partition1

把分區(qū)表的某個分區(qū)數(shù)據(jù)轉(zhuǎn)移到普通表，要求

1.??????普通表必須和對應(yīng)的分區(qū)在同一個文件組下

2.??????普通表和分區(qū)表結(jié)構(gòu)相同，包括字段、數(shù)據(jù)類型、數(shù)據(jù)長度、索引等

分區(qū)表上在tradedate上有聚集索引，但普通表tradelog_partition1上沒有建聚集索引，執(zhí)行上述腳本就會報如下錯誤：


八. ???普通表數(shù)據(jù)移到某一分區(qū)

alter table tradelog_partition1 switch to tradelog partition 1

在tradelog_partition1的tradedate上創(chuàng)建聚集索引，重新執(zhí)行上面的腳本，又報了如下錯誤

What happen？？這是因?yàn)榉謪^(qū)1上有CHECK日期要在2014到2016之間，而tradelog_partition1上沒有這個檢查，所以，在表上加上如下檢查：

ALTER TABLE dbo.tradelog_partition1

ADD CONSTRAINT TradeDate_Switch_CHECK CHECK

(TradeDate >= CONVERT(DATE,'2014-01-01') AND TradeDate < CONVERT(DATE,'2016-01-01')

? ? ? ? ? ? AND TradeDate IS NOT NULL);

GO

再執(zhí)行移動數(shù)據(jù)，數(shù)據(jù)又重新移回到分區(qū)1中。

————————————————

版權(quán)聲明：本文為CSDN博主「三空道人」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。

原文鏈接：https://blog.csdn.net/zhoujunah/article/details/79744590

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容