设为首页 收藏本站
查看: 874|回复: 0

[经验分享] 【原】Sql Server性能优化——Slide Window

[复制链接]

尚未签到

发表于 2015-6-29 13:11:23 | 显示全部楼层 |阅读模式
  虽然对数据库进行分区本身就能提高查询的性能,结合压缩,也能减少每次查询的IO。但如果数据持续增长,过于久远的历史数据就成了一个包袱,它们从来不在查询结果中出现,却或多或少的影响着每次查询的时间,成了一个挥之不去的阴影。此外,由于一个分区表的分区是有上限的(在2005中这一上限是1000),我们也并不能在一张表上一直分区下去。所以在这种情况下,我们一般需要同时拥有两张表,一张保存了最近的数据,用来应付所有的查询,这张表要足够精简,在其上的查询要足够敏捷;同时有另一张表,保存所有过时的数据——我们并不能把过时的数据一删了事。
  这里面的关键问题是,既然数据是随着时间持续增长的,那么当下有用的数据可能在几天后就过时了,那么怎样将这一部分过时的数据从活动表迁移到存档表,而且要保证迁移过程的快速、平稳呢?如果采用常规的Select、Insert、Delete来进行数据迁移,会有如下问题:
  
       
  • IO过大,效率必然较低。   
  • 迁移过程表被锁住,所有查询都会被搁置。   
  • 恢复困难,如果想将移出的数据再移回来,需要进行同样的操作,IO和锁表的问题同样存在。
  那么很容易想到,利用之前提到的分区的Switch操作来解决迁移的问题,将整个分区而不是数据在活动表和存档表中迁移。由于Switch的元数据操作属性,这一几乎没有什么IO的操作效率极高,而且也不会锁表。基于以上方法进行的周期性自动化的数据迁移,就是Slide Window的基础
  原理图
  假设我们已经有一张活动表,分了四个区,分别对应2009年,2010年一月、二月以及三月以后的数据:
DSC0000.png
  同时我们有另外一个存档表,分成了两个区, 第一个区对应2009年以前的数据,另外一个分区的范围是2010年之后:
DSC0001.png
  注意,活动表的第一个分区存档表中的第二个分区是没有数据的,这是进行Slide Window的前提条件
  现在,我们考虑将活动表中的一月份的数据放入存档表中,而且我们还要保证在迁移之后,两张表保持和迁移前相似的状态。
  那么我们可以采用如下的步骤:
  1.在存档表中建立新分区:
DSC0002.png
  2.将活动表的第二个分区挪到存档表的第二个分区中:
DSC0003.png
  迁移的结果如下:
DSC0004.png
  至此,我们已经完成了数据的迁移,但为了恢复两张表到之前的状态,我们还需要以下两个步骤:
  3.合并存档表的第一和第二个分区。
  4.拆分活动表的第三个分区。
  最后的结果,活动表:
DSC0005.png
  存档表:
DSC0006.png
  我们可以看到迁移过后,两张表的分区数量没有变,而且存档表的第二个分区依然是空的。当需要迁移二月份的数据时,我们可以采用和上面完全一样的步骤进行迁移。而这一过程,类似在时间轴上开了一个窗口,将当前数据在活动表上展示,随着时间推移,窗口不断向前滑动(活动表的边界前移),而且窗口大小(活动表的分区数)始终保持不变,这就是Slide Window(滑动窗口)这一名称的来源。
  建立存档表
  建立存档表最简单的方法是选中分好区的活动表,在Storage菜单中选择“Manage Partition”,然后选择“Create a stagin table for partition switching”。建议将“Staging table name”改成固定的没有数字后缀的名字,之后随便选择一个“Switch Parition”,最后生成创建存档表的脚本。
DSC0007.png
  由于指定了待切换的分区,所以这里脚本中会添加对应的约束,由于我们要创建的存档表并不应该有边界限定,所以应当把脚本中添加边界约束的部分删除,运行,生成存档表。
  接下来要对存档表进行分区,一般来说,存档表分成两个区就可以应对任意分区数量的活动表了,当然,分的更多也没有问题。存档表的分区边界要和活动表的对应边界一致,也就是存档表的第一个分区和第二个分区的边界等于活动表的第一个分区和第二个分区的边界。
  此时存档表中并没有数据,我们可以用
ALTER TABLE [STable] SWITCH PARTITION 1 TO [DTable] PARTITION 1

  将活动表的第一个分区迁移到存档表的第一个分区中。这样活动表的第一个分区、存档表的第二个分区为空,也就达成了前文所述的执行Slide Window的前提条件。

自动执行
  有了存档表,就可以进行滑动窗口了。以用时间类型字段做分区依据的表为例,这里我把执行脚本存到一个存储过程里:


CREATE PROCEDURE [dbo].[sp_SlideWindow]
@SplitRange SMALLDATETIME -- 指定活动表新增分区的边界
AS
BEGIN
DECLARE @SwitchRange SMALLDATETIME
DECLARE @MergeRange SMALLDATETIME
--获得活动表、存档表合并分区以及存档表的新分区的边界
SELECT @MergeRange=CONVERT(SMALLDATETIME,value) FROM sys.partition_range_values, sys.partition_functions WHERE sys.partition_functions.function_id = sys.partition_range_values.function_id AND sys.partition_functions.name = 'E_Alive_Partition_Func' AND boundary_id = 1
SELECT @SwitchRange=CONVERT(SMALLDATETIME,value) FROM sys.partition_range_values, sys.partition_functions WHERE sys.partition_functions.function_id = sys.partition_range_values.function_id AND sys.partition_functions.name = 'E_Alive_Partition_Func' AND boundary_id = 2
BEGIN TRANSACTION
ALTER PARTITION SCHEME [E_Alive_Partition_Schema] NEXT USED [PRIMARY]
ALTER PARTITION SCHEME [E_Staging_Partition_Schema] NEXT USED [PRIMARY]
--在活动表中新增分区
ALTER PARTITION FUNCTION [E_Alive_Partition_Func]()SPLIT RANGE(CONVERT(NVARCHAR,@SplitRange,120))
--在存档表中新增分区           
ALTER PARTITION FUNCTION [E_Staging_Partition_Func]()SPLIT RANGE(CONVERT(NVARCHAR,@SwitchRange ,120))
--切换分区   
ALTER TABLE [Alive_Table] SWITCH PARTITION 2 TO [Staging_Table] PARTITION 2
--合并活动表分区与存档表分区
ALTER PARTITION FUNCTION [E_Alive_Partition_Func]() MERGE RANGE(CONVERT(NVARCHAR,@MergeRange,120))
ALTER PARTITION FUNCTION [E_Staging_Partition_Func]() MERGE RANGE(CONVERT(NVARCHAR,@MergeRange,120))
COMMIT TRANSACTION
END

  在整个滑动窗口的操作过程中,活动表和存档表分别合并和拆分了两次,有四个相关边界值。而由于活动表的合并边界值和存档表的合并边界值是一样的,所以实际有三个边界值。其中合并边界就是第一个分区和第二个分区的边界,而存档表的拆分边界就是活动表的第二个分区和第三个分区的边界,这些都可以通过sys.partition_range_values, sys.partition_functions表获得。所以这个存储过程只需要输入一个变量,即活动表新拆分出的分区的边界

注意


  • 由于新拆分的分区中非聚集索引不会应用原来的压缩方式,所以如有需要,应当在存储过程中补充对相应索引做压缩的操作。
  • 之所以在存储过程中先进行分区的拆分,再进行分区切换,最后进行分区合并,是考虑对空的分区(切换前的存档表的第二分区、切换后的活动表的一、二分区)进行拆分或者合并效率比较高。
  • 以上只是Slide Window的一种方式,事实上,如果对历史数据不那么在意,我们依然可以用分区切换的方式,将旧的数据移出然后删除。或者使用多个存档表,每次都将活动表的最后一个分区移到新的存档表中,这样省去了合并存档表分区的性能消耗,但多个存档表可能在管理上会比较麻烦。

  后记:
  关于数据库的“压缩”、“分区”、“滑动窗口”,这里就算告一段落了。虽然题目是“SqlServer性能优化”,其实这里提到的仅仅是关于性能优化比较偏门的一小部分,最常用的类似“索引”、“查询结构”之类的性能优化技巧并没有涉及。
  而即使是这一小部分,也是诚惶诚恐。虽然这些技巧都在实际应用中实践过,但真要写出来,却还是有些力不从心,写博的过程基本就是重新学习的过程,其间翻阅了很多资料,不求有功,但求无过,不至于误人子弟。但毕竟百密一疏,而且即使看的资料,也不敢保证来源真确。所以来往过客,如有个中高手,能指点出文中瑕疵疏漏,不至于谬误流传,小弟不胜感激。

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-81504-1-1.html 上篇帖子: SQL Server 管理数据收集 下篇帖子: SQL Server 2014 BI新特性(三)Power Query和Power Map功能预览
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表