SQL Server On Linux(22)—— SQL Server On Linux性能(8)——性能进阶简介——分区(2)

本人新书上市,请多多关照: 《SQL Server On Linux运维实战 2017版从入门到精通》

本篇开始专门对性能进行一系列讲解,这一系列不限于Linux平台,更多的是针对SQL Server本身。

上一文简单介绍了一下分区的大概情况,本文把分区功能拆散细讲。

分区函数说明

首先了解一下分区函数,分区函数是分区过程的第一步(当然调研才是实际第一步),它用于指定整个分区的逻辑层内容,它有几个特征:

  1. 分区函数指定一个数据类型,这个类型必须与表分区列的数据类型相同。
  2. 分区函数是数据库级别的,但是不属于用户自定义函数,它们是仅用于分区的特定对象。所以它们相对于用户自定义函数有下面的区别:
    a) 不存在于sys.all_objects 或 sys.objects系统表中,但是可以从sys.partition_functions中查看。
    b) 分区函数不包含在数据库架构里面。
    c) 可以使用$PARTITION函数查询。
  3. 创建分区函数的时候有些注意事项:
    a) 范围值应该是离散和有限的数值。
    b) 隐式地从最小到最大排序。
    c) NULL被认为比最小值还要小。

使用场景

下面按照最简单的使用场景说起:

首先说一下一些通用的内容:

  1. 分区总数为边界值总数+1。
  2. 任何给定的边界值只能存在于一个分区中,不能跨分区存在。
  3. 分区号从1开始记录。

在定义分区函数时,非常重要的一步是确定使用RANGE LEFT还是RANGE RIGHT。

单整型边界值

举个例子,比如我们对一个连续的数值做分区,这个范围值从0~100。然后我希望10作为分区的边界值,在定义分区函数的时候其中的RANGE LEFT和RANGE RIGHT有什么区别的?

简单来说,RANGE后面的值,就是说定义中的边界值最后落到哪个分区。如果是RANGE LEFT,那么分区值属于左边的分区。比如

CREATE PARTITION FUNCTION PF1_Left (int)
AS RANGE LEFT FOR VALUES (10);

即分区1(0…10), 分区2(11…100)。知道了这个特点之后,就知道了RANGE RIGHT就是分区1(0…9),分区2(10…100)。

注意我们这里限定了单个值,所以这里只会拆分成2个分区,同时在使用了LEFT的情况下,大于边界值的数据全部都会被分到后面(右边)的分区,而RIGHT的情况下,小于边界值的数据则会分到前面(左边)的分区中。

当定义了分区函数之后,可以使用$PARTITION函数来查询特定的参数值位于哪个分区中,比如SELECT $PARTITION.PF1_Left(10),会返回属于分区1。由于单值边界值在实际使用中还是很少的,所以就不多说了。

多整型边界值

在实际使用中,很少只对数据表进行一次分区(也就是使用单值边界值),常见的都是进行多个分区,也就是使用多个边界值来拆分分区。下面我们从最简单的整型边界值来看一下。

相对于单值而言,多值边界值有以下特点:

  1. 定义边界值时需要从小到大。否则SQL Server会发出警告然后自己排序。
  2. 所有的边界值都遵循当前函数定义中的LEFT或者RIGHT。也就是说分区函数的RANGE值决定了后续整个分区的分区方向。
  3. 对于整型而言,最大的边界值是:SQL Server单表最大支持分区数-1。比如2008支持1000,只能使用999为最大值,SQL 2014~2019支持15000,那么就可以到14999个。
  4. NULL可用于分区,对于LEFT而言,NULL是第一个分区(分区号为1),只存储NULL值。对于RIGHT而言,所有NULL都在第二个分区,第一个分区为空。

什么时候用LEFT什么时候用RIGHT?这个需要根据实际情况而定,不过通常大家都会希望得到一些所谓的“建议”,如果非要说,那么更常用的是RIGHT,因为我们的习惯性思维里面,会更偏向于定义中的值作为分区的第一个值,这样更好理解和操作,否认有时候计算的过程你还得通过加1或者减1的方式来计算分区。在上一文S QL Server On Linux(21)—— SQL Server On Linux性能(7)——性能进阶简介——分区(1) 中也可以看到它使用的是RIGHT。

关于LEFT和RIGHT的使用例子,可以参考官方文档 分区函数Examples , 特别是里面的E和F两个,比较有用。

日期型边界值

相对于数值型而言,实际运用中***日期型***是更常用的边界值类型。日期型跟数值型相似,不过要注意当使用datetime/datetime2/time等会带上***时分秒***的值的时候,如果你不指定具体时间,那默认就是00:00:00.000为起始。另外对于date类型,要注意闰年的情况。

另外还有一个日期类型需要注意,就是datetimeoffset,因为可以带上时区,所以定义的时候,时区的增加可能会导致数据落在非预期的分区中。

对于日期类型,个人建议始终使用标准格式比如YYYYMMDD或者YYYY-MM-DD,如果你非要使用DD/MM/YYYY或者MM/DD/YYYY这种容易混淆的格式,建议通过一个函数不管是SQL端还是应用端来保证数据格式的一致性。

其他可能的数据类型边界值

出现最多的可能就是字符型,比如我们可能会需要对月份的名字、省市区、其他类型状态等值进行分区,就会用到字符型边界值。字符型的问题在于“排序”,小写字母排在大写字母之前。另外一个问题在于数据类型的定义***需要一模一样***,不能函数定义为varchar(10),但是数据表的列为varchar(20)等。

修改分区函数

数据库上所有的功能几乎都可以用某些方式来修改,分区函数也一样,即使已经使用了,也可以修改函数,比如修改边界值从而实现增减分区。不过,分区函数也有不能修改的地方,比如使用的 数据类型RANGE配置 ,一旦创建并使用,则不能修改,只能通过删除重建。

很多时候,分区不一定能够全部预配置,这时候就会出现“动态”分配。也就是需要拆分下一个分区。通过ALTER PARTITION FUNCTION的SPLIT子句来添加一个新的边界值实现。但是要满足下面条件:

  1. 与现有边界值类型兼容且不重叠。
  2. 遵循现有的RANGE配置。
  3. 可以使用变量或函数,但是需要保证满足上面两个条件。

当对已分区的表添加新边界值时,会产生分区中数据移动,导致日志增长(因为存在插入和删除操作),这种操作会引发某些性能问题,最好在空闲时候执行。假设现在有这样一个分区(使用RANGE RIGHT):

{0…9},{10…19},{20…29},{30…max}

当你添加一个边界值是15的时候:

ALTER PARTITION FUNCTION XXX() SPLIT RANGE(15);

结果会变成:

{0…9},{10…14},{15…19},{20…29},{30…max}

后面的分区号会顺延。同时原有分区{10…19}中从15~19的值会移到新的分区(分区3),然后在旧分区(分区2)中删除这部分的数据。

MERGE:

MERGE跟SPLIT是反操作,也就是SPLIT是拆分,MERGE是合并,在一些归档操作中会使用到,比如2019年我按照月份进行了12个分区,但是到了2020年,按照业务实际情况,可能可以把2019的分区全部合到一个分区中,那么这个时候就可以用MERGE。只需要在ALTER PARTITION FUNCTION中使用MERGE子句把边界值删除即可。比如上面的例子:

{0…9},{10…14},{15…19},{20…29},{30…max}

通过

ALTER PARTITION FUNCTION XXX() MERGE RANGE(15);

结果变成了:

{0…9},{10… 19},{20…29},{30…max}

当你把所有的边界值都删除之后,表只有一个分区。同时sys.partition_range_values目录视图中将找不到这个信息,因为边界值已经被移除。

数据移动

SPLIT和MERGE都会对现有的分区方案和分区表产生影响,如果对一个空的分区进行SPLIT成两个空分区,那么这个操作算元数据操作,没有数据移动, MERGE也同理。

但是如果对非空的分区进行这两类操作,那么就会出现数据移动(插入和删除),这种是需要记录事务日志的操作,很有可能引起日志的突增,需要密切关注。

本文主要介绍了分区函数的使用事项,其完整语法可以查看官网的对应文档,因为自从Azure出来之后,很多功能并不能在云平台和本地版中通用,增加了不少学习和使用及迁移的难度。不过针对分区函数,在云版本和本地版本之间没有差异。

创建了分区函数之后,就开始进行分区方案(Parition Scheme)的创建了。下一文会对其讲解。

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章