1,分區作用:將數據分放到多個物理磁盤上,利用並行,多個磁盤同時吞吐以提升IO能力,同時每個分區數據量小,也減少了數據的掃描,提升了命中速度。實際上表邏輯上並未拆分,只是分散存儲於不同的物理文件上,相當於把一張表大數據無限極細化到多張表上,多個驅動上,但是訪問時卻還是一樣的訪問,因為本身並未新建任何表。
注:即使放到一塊磁盤上,因每個分區數據量小,掃描范圍減少,也能提高速度。
表分區方法:1,按時間分區:好處,查詢速度快。壞處:因當前數據一直寫到一塊分區上,故寫不快。
2,HASH分區:數據均勻的分散到各分區,好處:因當前數據寫到不同分區,故寫快。但在查詢方面,要聯合多個分區,故查詢不快。
注:分區分為硬件與軟件分區,此頁是軟件分區。硬件分區指用磁盤陣列的方式分區,硬件負責將數據將到不同磁盤上,查詢與寫入速度都會提高。
操作:指定數據要分區的位置-創建文件組及對應文件, 數據以何種規則適移到分區上-創建分區函數, 進行數據遷移。
操作:指定數據要分區的位置-創建文件組及對應文件, 數據以何種規則適移到分區上-創建分區函數, 進行數據遷移。
步驟:
/*1,指定數據存放位置:增加文件組,並指定文件存放位置*/
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_50]
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_100]
ALTER DATABASE BRM_LVJIAN
ADD FILEGROUP [gf_bdorder_150]
ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_50',
FILENAME = N'g:\Mssql_Filegroup\bdorder_50.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_50]
ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_100',
FILENAME = N'I:\Mssql_Filegroup\bdorder_100.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_100]
ALTER DATABASE BRM_LVJIAN
ADD FILE ( NAME = N'gf_bdorder_150',
FILENAME = N'I:\Mssql_Filegroup\bdorder_150.ndf' , SIZE = 300MB , FILEGROWTH = 10% )
TO FILEGROUP [gf_bdorder_150]
/*2,指定數據如何存放:創建分區函數*/
--LEFT,RIGHT指定"="是在最左邊還是右邊
--LEFT: <=1, 1<10<=20, >20
--RIGHT: <1,1<=10<20, >=20
CREATE PARTITION FUNCTION [partitionFunc_bdOrder] (int)
AS RANGE LEFT FOR VALUES ('500000','1000000')
select max(id) from bdorder
/*3,數據的遷移,應用分區位置與函數,為了方便,將二者合二唯一,提出分區架構的概念*/
CREATE PARTITION SCHEME [partitionScheme_bdOrder]
AS PARTITION [partitionFunc_bdOrder]
TO ([PRIMARY],gf_bdorder_50,gf_bdorder_100,gf_bdorder_150)
--數據遷移到分區
ALTER TABLE dbo.bdOrder DROP CONSTRAINT PK_BDOrder
ALTER TABLE dbo.bdOrder add CONSTRAINT PK_BDOrder PRIMARY KEY CLUSTERED (ID)
ON [partitionScheme_bdOrder](ID)
--恢復成原來的默認狀態,因原來的分區架構是<span style="color: #ff0000;">PRIMARY</span>,故修改表即可,如下:
ALTER TABLE dbo.bdorder DROP CONSTRAINT PK_BDOrder
ALTER TABLE dbo.bdorder add CONSTRAINT PK_BDOrder PRIMARY KEY CLUSTERED (ID)
ON [PRIMARY]
/*查詢*/
--分區
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.bdOrder')
--數據所在分區
SELECT $PARTITION.[partitionFunc_bdOrder](1000000)
SELECT top 20 id,orderno,orderdate,$PARTITION.[partitionFunc_bdOrder](id) FROM bdOrder
ORDER BY newid()
--數據分布
SELECT $PARTITION.partitionFunc_bdOrder(id) AS Partition_num,
MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num
FROM dbo.[bdorder]
GROUP BY $PARTITION.partitionFunc_bdOrder(id)
ORDER BY $PARTITION.partitionFunc_bdOrder(id)
select * from
sys.partition_range_values
/*若數據量增大,需要增加分區,擴大范圍,操作如下*/
操作方法:增加分區,增加范圍
ALTER DATABASE [D] ADD FILEGROUP [GF2]
ALTER DATABASE [WSBOOK] ADD FILE ( NAME = N'GF2', FILENAME = N'E:\E\E1\DGF2.ndf' , SIZE = 5MB , FILEGROWTH = 10% )
TO FILEGROUP [GF2]
為架構添加范圍
ALTER PARTITION SCHEME [D_PARTITION_SHEME]
NEXT USED GF2
這句話就是讓下一個分區使用和現在已經存在的分區GF2分區中,
2.添加一個范圍
ALTER PARTITION FUNCTION [D_PARTITIONFUNC]()
SPLIT RANGE ('700000')
可以合並一個范圍,其實就是<span style="color: #ff0000;">刪除分區</span>,如MERGE RANGE(要刪除的原分區界點)
ALTER PARTITION FUNCTION [D_PARTITIONFUNC]() MERGE RANGE ('400000')
3.查詢分區分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.B')
4.刪除分區
DROP PARTITION SCHEME [D_PARTITION_SHEME]
DROP PARTITION FUNCTION [D_PARTITIONFUNC]