SqlServer2008R2 分区管理
标签(空格分隔): .Net
分区的背景
当数据量太大时,可以通过数据分区来提升效率
分区的优劣
优势
- 提供性能:这个是大多人数分区的目的,把一个表分部到不同的硬盘或其他存储介质中,提高并发,会大大提升查询速度。
- 提高稳定性:当一个分区出了问题,不会影响其他分区,仅仅是当前坏的分区不可用。
- 便于管理:把一个大表分成若干个小表,则备份和恢复的时候不再需要备份整个表,可以单独备份分区。
- 存档:将一些不太常用的数据,单独存放。如:将1年前的数据记录分到一个专门的存档服务器存放。
劣势
- 跨分区的查询数据处理不好速度会减慢
分区操作
创建文件与文件组
一个文件只可以对应一个文件组,一个文件组可以对应多个文件,最终数据是相对于文件组操作,这样数据操作更简单,而一个文件组中包含多个文件的话,数据插入是按初始比例插入
查询效率:单一文件组多分区文件 > 多文件组多分区文件 >单一文件组单一分区文件 > 默认
参考别人链接:https://www.cnblogs.com/knowledgesea/p/3696912.html
语法:alter database <数据库名> add filegroup <文件组名>
alter database FenQuTest add filegroup Group1
alter database FenQuTest add filegroup Group2
alter database FenQuTest add filegroup Group3
语法:alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>
--<数据标识> (name:文件名,fliename:物理路径文件名,size:文件初始大小kb/mb/gb/tb,filegrowth:文件自动增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited)
alter database FenQuTest add file (name=N'g1',filename=N'C:\fq\g1.ndf',size=5Mb,filegrowth=5mb) to filegroup Group1
alter database FenQuTest add file (name=N'g2',filename=N'C:\fq\g2.ndf',size=5Mb,filegrowth=5mb) to filegroup Group2
alter database FenQuTest add file (name=N'g3',filename=N'C:\fq\g3.ndf',size=5Mb,filegrowth=5mb) to filegroup Group3
创建分区函数与分区方案
分区函数: 以时间(datetime)分割来创建分区方案,等于分割数据的数据归右边分区(RANGE RIGHT)
CREATE PARTITION FUNCTION [OrderInfo_SubareaFun](datetime) AS RANGE RIGHT FOR VALUES (
N'2017-07-01T00:00:00',N'2017-08-01T00:00:00', N'2017-09-01T00:00:00')
分区方案
CREATE PARTITION SCHEME [OrderInfo_SubareaSCHEME] AS PARTITION [OrderInfo_SubareaFun] TO ([PRIMARY],[Group1],[Group2],[Group3])
映射数据
映射数据:确保表中不含聚集索引
![]()
![]()
–删除表中的聚集索引
ALTER TABLE [dbo].[Test] DROP CONSTRAINT [PK__Test_Id]
ALTER TABLE [dbo].[Test] ADD CONSTRAINT [PK__Test_Id] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--映射数据
CREATE CLUSTERED INDEX [ClusteredIndex_on_Test_SubareaPlan] ON [dbo].[Test]
(
[CreateOn]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [OrderInfo_SubareaSCHEME]([CreateOn])
查看分区
参看每个分区的总数据
SELECT $PARTITION.[OrderInfo_SubareaFun](CreateOn) AS Partition,
COUNT(*) AS [COUNT]
FROM dbo.Test
GROUP BY $PARTITION.[OrderInfo_SubareaFun](CreateOn)
ORDER BY Partition ;
查看某个分区的流水
SELECT *
FROM dbo.Test
WHERE $PARTITION.[OrderInfo_SubareaFun](CreateOn) =3
查看一笔数据属于哪个分区
select $partition.[OrderInfo_SubareaFun]('2017-9-1')
删除分区
alter partition function OrderInfo_SubareaFun()
merge range(N’1500000’)