澳门金沙vip 4

Partition:Partiton Scheme是否指定Next Used?

在SQL Server中,为Partition Scheme多次指定Next
Used,不会出错,最后一次指定的FileGroup是Partition Scheme的Next
Used,建议,在执行Partition Split操作之前,都要为Partition
Scheme指定Next Used。

通常情况下,我们创建的表都是未分区的表,或者说,只有一个分区的表,数据只能存储在一个文件组(File
Group)中,默认情况下,表数据存储在PRIMARY文件组。对表进行分区后,每一个分区都独立存储在文件组(File
Group)中。把表分区,实际上是把逻辑上完整的表,按照特定的字段拆分成多个分区,每个分区分配到(相同或不同的)FileGroup中,每一个分区在文件组中都是独立存储,即使多个分区分配到相同的文件组中。

但是,SQL Server是否提供metadata,查看Partiton Scheme是否指定Next Used
FileGroup?答案是系统视图:sys.destination_data_spaces。如果存在FileGroup被指定为Next
Used ,那么视图返回的Partition的个数会比Partition
Function划分的分区数量多1个。

为什么要对数据表(Table)分区?这是因为分区(Partition)之间是物理上独立存储的,对单个分区进行操作,不会影响其他分区,这能提高查询性能,避免死锁;而分区切换甚至能够不移动数据,而删除海量数据。

一,分析视图:sys.destination_data_spaces

一,创建分区表

该视图返回三列,表示Partition
Scheme的每个Partition和FileGroup之间的关系:

在创建表时,使用ON 子句指定存储的逻辑位置:

  • partition_scheme_id
    :ID of the partition-scheme that is partitioning to the data space.
                     
  • destination_id
    :ID (1-based ordinal) of the destination-mapping, unique within the
    partition scheme.     
  • data_space_id
    :ID of the data space to which data for this scheme’s destination
    is being mapped.
  • on  filegroup:表示逻辑存储位置是FileGroup;
  • on  partition_scheme ( partition_column )
    :表示逻辑存储位置是partition
    scheme,存储引擎按照partition_column把表拆分成多个分区,每一个分区都存储在指定的一个Filegroup中。

    CREATE TABLE [schema_name . ] table_name
    ( )
    [ ON { partition_scheme ( partition_column ) | filegroup } ]

从表的存储结构来分析这三列的意义:

分区是表的逻辑存储空间,而分区的物理存储空间是由FileGroup指定的,FileGroup是File的集合,每一个File都属于唯一的FileGroup。把表的存储空间分割到不同的FileGroup中,逻辑上是将table的存储管理体系增加了一层
Partition,介于Table和FileGroup中间,Table的数据存储在Partition,Partition存储在FileGroup中,FileGroup管理着File,File是实际存储data的物理文件。

  • *澳门金沙vip,*partition_scheme_id :**是数据表存储的空间,该空间不是具体的某个FileGroup。普通的表只有一个分区,只能存储在单个FileGroup中,但是,通过Partition
    Scheme,将表数据分割成多个分区,每个分区存储到指定的FileGroup中,在物理存储上,每个分区都是分开(separate)存储的。
  • destination_id:是Partition
    Number,每个分区的编号
  • data_space_id:是FileGroupID,分区存储的FileGroup。

二:创建分区表的步骤

二,测试用例

Step1, 创建分区函数

1,创建分区函数

分区函数的作用是提供分区字段的类型和分区的边界值

-- create parition function
CREATE PARTITION FUNCTION pf_int_Left (int)
AS 
RANGE LEFT 
FOR VALUES (10,20);
CREATE PARTITION FUNCTION [pf_int](int) 
AS RANGE LEFT 
FOR VALUES (10, 20)

2,创建分区scheme

pf_int
的含义是按照int类型分区,分区的边界值是10,20,left表示边界值属于左边界。两个边界值能够分成三个分区,别是(-infinite,10],(10,20],(20,+infinite)。

--create partition scheme
CREATE PARTITION SCHEME PS_int_Left
AS 
PARTITION pf_int_Left
TO ([primary], [primary], [primary]);

Step2,创建分区scheme

3,在split partition之前,必须使用alter partition scheme 指定一个Next
Used FileGroup。如果Partiton Scheme没有指定 next used
filegroup,那么alter partition function split range command
执行失败,不改变partition scheme。

分区scheme的作用是为Parition分配FileGroup,Partition
Scheme和FileGroup在逻辑上等价,都是数据存储的逻辑空间,只不过Partition
Scheme指定的是多个FileGroup。

--split range and add new one boudary value
ALTER PARTITION FUNCTION pf_int_Left ()
split range (30);
CREATE PARTITION SCHEME [ps_int] 
AS PARTITION [pf_int] 
TO ([PRIMARY], [db_fg1], [db_fg1])

Msg 7710, Level 16, State
1, Line 2
Warning: The partition
scheme ‘PS_int_Left’ does not have any next used filegroup. Partition
scheme has not been changed.

不管是在不同的FileGroup中,还是在相同的FileGroup中,分区都是独立存储的。

4,如果检查 Partiton Scheme是否指定Next Used FileGroup?

Step3,创建分区表

使用sys.destination_data_spaces视图来检查,该系统视图返回Partition
和filegroup之间的Mapping关系。如果一个FileGoup被alter partition scheme
标记为next used Filegroup,那么Partition 的个数会比多Partition
function划分的分区多一个。

创建分区表,实际上是使用on子句指定table存储的逻辑位置。

select ps.name as PartitionSchemeName,
    ps.data_space_id as PartitionSchemeID,
    pf.name as PartitionFunctionName,
    ps.function_id as PartitionFunctionID,
    pf.boundary_value_on_right,
    dds.destination_id as PartitionNumber,
    dds.data_space_id as FileGroupID
from sys.partition_schemes ps
inner join sys.destination_data_spaces dds
    on ps.data_space_id=dds.partition_scheme_id
inner join sys.partition_functions pf
    on ps.function_id=pf.function_id
where ps.name='PS_int_Left'
create table dbo.dt_test
(
    ID int,
    code int
)
on [ps_int] (id)

澳门金沙vip 1

二,查看分区表的元数据

上述脚本返回3个partition,说明没有next used filegroup。

分区表的元数据是指分区函数,分区scheme,分区边界值,分区对应的文件组,这些元数据都能从SQL
Server提供的系统视图中查看。

5,使用 alter partition scheme标记 next used filegroup

1,查看分区函数

--alter partition scheme to mark next used filegroup
ALTER PARTITION SCHEME PS_int_Left 
NEXT USED [db_fg1];

使用系统视图:sys.partition_functions
查看当前数据库中创建的分区函数,使用sys.partition_range_values
查看每个分区的边界值,使用sys.partition_parameters查看分区函数的参数,分区函数的参数是分区表的分区列,分区列是什么不重要,重要的是分区列的数据类型,如下图,分区函数的ID是65536,参数的系统类型system_type_id是56:

查看分区个数

澳门金沙vip 2

select ps.name as PartitionSchemeName,
    ps.data_space_id as PartitionSchemeID,
    pf.name as PartitionFunctionName,
    ps.function_id as PartitionFunctionID,
    pf.boundary_value_on_right,
    dds.destination_id as PartitionNumber,
    dds.data_space_id as FileGroupID
from sys.partition_schemes ps
inner join sys.destination_data_spaces dds
    on ps.data_space_id=dds.partition_scheme_id
inner join sys.partition_functions pf
    on ps.function_id=pf.function_id
where ps.name='PS_int_Left'

从sys.types查看对应的数据类型,类型名称是int,这说明分区列的数据类型必须是int:

澳门金沙vip 3

澳门金沙vip 4