代码改变世界

【原】Sql Server性能优化——Slide Window

2010-03-29 19:14  拖鞋不脱  阅读(3414)  评论(3)  编辑  收藏  举报

虽然对数据库进行分区本身就能提高查询的性能，结合压缩，也能减少每次查询的IO。但如果数据持续增长，过于久远的历史数据就成了一个包袱，它们从来不在查询结果中出现，却或多或少的影响着每次查询的时间，成了一个挥之不去的阴影。此外，由于一个分区表的分区是有上限的（在2005中这一上限是1000），我们也并不能在一张表上一直分区下去。所以在这种情况下，我们一般需要同时拥有两张表，一张保存了最近的数据，用来应付所有的查询，这张表要足够精简，在其上的查询要足够敏捷；同时有另一张表，保存所有过时的数据——我们并不能把过时的数据一删了事。

这里面的关键问题是，既然数据是随着时间持续增长的，那么当下有用的数据可能在几天后就过时了，那么怎样将这一部分过时的数据从活动表迁移到存档表，而且要保证迁移过程的快速、平稳呢？如果采用常规的Select、Insert、Delete来进行数据迁移，会有如下问题：

  1. IO过大，效率必然较低。
  2. 迁移过程表被锁住，所有查询都会被搁置。
  3. 恢复困难，如果想将移出的数据再移回来，需要进行同样的操作，IO和锁表的问题同样存在。

那么很容易想到，利用之前提到的分区的Switch操作来解决迁移的问题，将整个分区而不是数据在活动表和存档表中迁移。由于Switch的元数据操作属性，这一几乎没有什么IO的操作效率极高，而且也不会锁表。基于以上方法进行的周期性自动化的数据迁移，就是Slide Window的基础。

原理图

假设我们已经有一张活动表，分了四个区，分别对应2009年，2010年一月、二月以及三月以后的数据：

image

同时我们有另外一个存档表，分成了两个区， 第一个区对应2009年以前的数据，另外一个分区的范围是2010年之后：

image

注意，活动表的第一个分区、存档表中的第二个分区是没有数据的，这是进行Slide Window的前提条件。

现在，我们考虑将活动表中的一月份的数据放入存档表中，而且我们还要保证在迁移之后，两张表保持和迁移前相似的状态。

那么我们可以采用如下的步骤：

1.在存档表中建立新分区：

image

2.将活动表的第二个分区挪到存档表的第二个分区中：

image

迁移的结果如下：

image

至此，我们已经完成了数据的迁移，但为了恢复两张表到之前的状态，我们还需要以下两个步骤：

3.合并存档表的第一和第二个分区。

4.拆分活动表的第三个分区。

最后的结果，活动表：

image

存档表：

 image

我们可以看到迁移过后，两张表的分区数量没有变，而且存档表的第二个分区依然是空的。当需要迁移二月份的数据时，我们可以采用和上面完全一样的步骤进行迁移。而这一过程，类似在时间轴上开了一个窗口，将当前数据在活动表上展示，随着时间推移，窗口不断向前滑动（活动表的边界前移），而且窗口大小（活动表的分区数）始终保持不变，这就是Slide Window（滑动窗口）这一名称的来源。

建立存档表

建立存档表最简单的方法是选中分好区的活动表，在Storage菜单中选择“Manage Partition”，然后选择“Create a stagin table for partition switching”。建议将“Staging table name”改成固定的没有数字后缀的名字，之后随便选择一个“Switch Parition”，最后生成创建存档表的脚本。

image

由于指定了待切换的分区，所以这里脚本中会添加对应的约束，由于我们要创建的存档表并不应该有边界限定，所以应当把脚本中添加边界约束的部分删除，运行，生成存档表。

接下来要对存档表进行分区，一般来说，存档表分成两个区就可以应对任意分区数量的活动表了，当然，分的更多也没有问题。存档表的分区边界要和活动表的对应边界一致，也就是存档表的第一个分区和第二个分区的边界等于活动表的第一个分区和第二个分区的边界。

此时存档表中并没有数据，我们可以用

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表获得。所以这个存储过程只需要输入一个变量，即活动表新拆分出的分区的边界。

注意

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

后记：

关于数据库的“压缩”、“分区”、“滑动窗口”，这里就算告一段落了。虽然题目是“SqlServer性能优化”，其实这里提到的仅仅是关于性能优化比较偏门的一小部分，最常用的类似“索引”、“查询结构”之类的性能优化技巧并没有涉及。

而即使是这一小部分，也是诚惶诚恐。虽然这些技巧都在实际应用中实践过，但真要写出来，却还是有些力不从心，写博的过程基本就是重新学习的过程，其间翻阅了很多资料，不求有功，但求无过，不至于误人子弟。但毕竟百密一疏，而且即使看的资料，也不敢保证来源真确。所以来往过客，如有个中高手，能指点出文中瑕疵疏漏，不至于谬误流传，小弟不胜感激。