MyException - 我的异常网
当前位置:我的异常网» Sql Server » Partition:增多分区

Partition:增多分区

www.MyException.Cn  网友分享于:2013-11-15  浏览:0次
Partition:增加分区

在关系型 DB中,分区表经常使用DateKey(int 数据类型)作为Partition Column,每个月的数据填充到同一个Partition中,由于在Fore-End呈现的报表大多数是基于Month的查询,按照Month分区的设计能够提高查询性能,但是,如果,前任DBA没有创建Schedule来维护Partition Function,不能增加新的Partition,所有新增加的数据都会插入到最后一个Partition中,导致最后一个Partition填充的数据量十分大,降低了分区表的查询性能。

一,最佳实践(Best Practices )

微软建议,避免对已填充的分区执行split或merge操作。在分区表的两端都保持空的分区(Empty Partition),第一个分区和最后一个分区是Empty Partition。通过对尾端的Empty Partition进行Split操作,就能在尾端增加分区,并且不会产生数据移动;当将数据从前端的第二个分区中归档后,第一个分区和第二个分区都是Empty Partition,Empty Partition进行Merge操作,不会产生数据的移动。

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

因此,受此Best Practices的影响,DB开发人员在创建分区表时,一般的做法是只创建特定数量的分区,并且只保持两端是Empty Partition,例如:

CREATE PARTITION FUNCTION [funcPartition_DateKey](int) 
AS RANGE RIGHT 
FOR VALUES (20100701, 20100801, <.....> , 20150501, 20150601)
GO

CREATE PARTITION SCHEME [schePartition_DataKey] 
AS PARTITION [funcPartition_DateKey] 
TO ([PRIMARY], <....>, [PRIMARY])
GO

改进建议:在SQL Server 2012中,在一个Table或索引上,最多能够创建15 000个分区(In SQL Server 2012, a table or index can have a maximum of 15,000 partitions),未雨绸缪,我们可以按照月份,一次性创建360个分区,每月一个分区,每年12个分区,服务30年,一劳永逸,当然,也可以创建Schedule,前提是,你必须保证Schedule按时执行成功。

如果,必须要对已填充的分区表,增加分区,要怎么做?

方法1,直接修改Partition Function,通过拆分末端Partition来增加分区

由于很多 Big Table 使用相同的Partition Schema进行分区,简单地从末端Partition为起点,逐个增加分区,在短时间内会产生海量的Disk IO操作,对系统产生极大的影响,例如

declare @DateKey int 
set @DateKey=20150701

while @DateKey<20200101
begin
  alter partition scheme [schePartition_DataKey]
  Next Used [primary];
  
alter partition function [funcPartition_DateKey]()   split range(@DateKey);
  
set @DateKey=cast(convert(nvarchar(8),dateadd(month,1,cast(cast(@DateKey as nvarchar(8)) as date)),112) as int); end

方法2,更改分区表的Partition Schema

创建新的Partition function 和 Partition Schema,逐个Table修改其Partition Schema,这个方法(Workaround),虽然实现过程比较繁琐,但是对系统性能的副作用最小,将影响控制在当前操作的Target Table。

Script1,创建新的Partition设计

--create Partition function
declare @DateKeyList varchar(max)
declare @DateKey int 
--set initial DateKey
set @DateKey=20140701;
while @DateKey<20200101 begin set @DateKeyList=coalesce(@DateKeyList+','+cast(@DateKey as varchar(8)),cast(@DateKey as varchar(8))) --Increase iterator set @DateKey=cast(convert(nvarchar(8),dateadd(month,1,cast(cast(@DateKey as nvarchar(8)) as date)),112) as int); end --print DateKey List --select @DateKeyList declare @sql nvarchar(max) set @sql=N' CREATE PARTITION FUNCTION [funcPartition_new_DateKey](int) AS RANGE RIGHT FOR VALUES ('+@DateKeyList+N');' EXEC sys.sp_executesql @sql GO --create partition schema CREATE PARTITION SCHEME [schePartition_new_DataKey] AS PARTITION [funcPartition_new_DateKey] all TO ([PRIMARY]); GO

Script2,逐个更新Table的Patition Schema

由于Patition Table中,可能存在不止一个Index,在rebuild table时,使用 drop_existing=on 能够减少分区表上nonclustered indexes的重建时间。

--rebuild table
create unique clustered index [PK__SchemaName_TableName_KeyColumn]
on SchemaName.TableName([KeyColumn],[CreatedDateKey])
with(data_compression=page,drop_existing=on)
on [schePartition_new_DataKey]([CreatedDateKey]);

--rebuild columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_ColumnStore_SchemaName_TableName] 
ON [SchemaName].[TableName]
(
    column list....
)

三,在同一个文件组中创建分区

在一个文件组中创建表的所有分区,每个分区在物理上都是独立的存储对象,只不过这些独立的存储对象位于同一个FileGroup。

1,创建Patition Schema时,使用 ALL 关键字指定只能指定一个FileGroup,所有的Partition 都创建在同一个FileGroup上;在Patition Schema创建成功之后,默认会将该FileGroup标记为Next Used

ALL Specifies that all partitions map to the filegroup provided in file_group_name, or to the primary filegroup if [PRIMARY] is specified. If ALL is specified, only one file_group_name can be specified.

If [PRIMARY] is specified, the partition is stored on the primary filegroup. If ALL is specified, only one file_group_name can be specified. Partitions are assigned to filegroups, starting with partition 1, in the order in which the filegroups are listed in [,...n]. The same file_group_name can be specified more than one time in [,...n].

2,在Patition schema中,只有一个FileGroup会被指定(Mark)为Next Used

如果FileGroup被指定为Next Used,意味着分区表的下一个分区将会创建在该FileGroup上。在创建Patition Schema时,指定 ALL关键字,不仅指定将表的所有分区都创建在同一个FileGroup上,而且,还将该FileGroup指定为Next Used。

If ALL is specified, the sole file_group_name maintains its NEXT USED property for this partition_function_name. The NEXT USED filegroup will receive an additional partition if one is created in an ALTER PARTITION FUNCTION statement. 

3,在执行Patition Split 操作时,必须存在一个FileGroup被指定为Next Used,否则,Split 操作失败

在指定Next Used时,要注意:

  • 在创建Partition Scheme时,指定ALL关键字,只会将一个FileGroup指定为Next Used;
  • 可以多次指定Next Used,最后一次指定的FileGroup是Next Used;
  • 一个FileGroup可以被多次指定为Next Used;即使该File Group已经用于存储(Hold)分区的数据; A filegroup that already holds partitions can be designated to hold additional partitions. 
  • 在完成一次Partition Split操作之后,之前的Next Used 已被使用,当前不存在被指定为Next Used的FileGroup,必须显式指定一个FileGroup作为新的Next Used,才能继续执行Partition Split操作;

If you create all the partitions in the same filegroup, that filegroup is initially assigned to be the NEXT USED filegroup automatically. However, after a split operation is performed, there is no longer a designated NEXT USED filegroup. You must explicitly assign the filegroup to be the NEXT USED filegroup by using ALTER PARITION SCHEME or a subsequent split operation will fail.

显式将一个FileGroup 指定为 Next Used

ALTER PARTITION SCHEME partition_scheme_name 
NEXT USED [ filegroup_name ] [ ; ]

 

Appendix

使用Alter Partition Function 命令执行拆分或合并分区的操作,每次操作,只能拆分一个,或合并一个分区:

ALTER PARTITION FUNCTION partition_function_name()
{ 
    SPLIT RANGE ( boundary_value )
  | MERGE RANGE ( boundary_value ) 
} [ ; ]

ALTER PARTITION FUNCTION repartitions any tables and indexes that use the function in a single atomic operation. However, this operation occurs offline, and depending on the extent of repartitioning, may be resource-intensive.

 

参考doc:

Rebuilding Existing Partitioned Tables to a New Partition Scheme

ALTER PARTITION FUNCTION (Transact-SQL)

ALTER PARTITION SCHEME (Transact-SQL)

文章评论

Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
10个调试和排错的小建议
10个调试和排错的小建议
一个程序员的时间管理
一个程序员的时间管理
老程序员的下场
老程序员的下场
程序员都该阅读的书
程序员都该阅读的书
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
程序员和编码员之间的区别
程序员和编码员之间的区别
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
如何成为一名黑客
如何成为一名黑客
为什么程序员都是夜猫子
为什么程序员都是夜猫子
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
总结2014中国互联网十大段子
总结2014中国互联网十大段子
Java程序员必看电影
Java程序员必看电影
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
 程序员的样子
程序员的样子
中美印日四国程序员比较
中美印日四国程序员比较
我是如何打败拖延症的
我是如何打败拖延症的
旅行,写作,编程
旅行,写作,编程
我的丈夫是个程序员
我的丈夫是个程序员
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序员必看的十大电影
程序员必看的十大电影
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
程序员应该关注的一些事儿
程序员应该关注的一些事儿
鲜为人知的编程真相
鲜为人知的编程真相
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
代码女神横空出世
代码女神横空出世
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有