分区表的索引

在分区表上创建的索引分为:分区对齐索引(Aligned Index)和非对齐索引。对齐索引是指索引结构的分区和基础表的分区相同,这意味着,在表的一个分区上创建的索引,索引结构也在同一个分区中。索引结构在每个分区中都是独立存储和维护的,如果索引结构和表结构在同一个分区(也就是在同一个文件组上),那么索引是与基础表对齐的索引。创建对齐索引,并不意味着必须使用相同名称的分区scheme,只要分区schem使用的分区边界和每个分区存储的文件组相同,这两个分区schem是等价的,使用等价的分区scheme创建的索引和基础表是对齐的。

索引对齐能够提升查询性能,能够实现分区的隔离和分区的切换(switch)操作,分区的隔离能够避免分区列上的竞争,从而有效避免死锁;通过分区的切换和数据表的截断(TRUNCATE TABLE)这一操作组合,使得在删除海量数据表上的一个分区时,能够瞬间完成,而分区对齐的好处不止如此,建议创建对齐索引。

在分区表上创建索引,如果没有使用ON子句指定文件组,那么,默认创建的索引是分区索引,分区scheme和基础表相同,索引的每一个分区和基础表对应的分区存储在同一个文件组,这就是所谓的对齐索引。另外一种情况,基础表是没有分区的,而索引是分区的,在这种情况下创建分区索引时,需要在CREATE INDEX的ON子句中指定分区scheme。

在分区表上创建对齐索引,分区列必须包含在聚集索引键,唯一索引键(唯一索引,主键约束,唯一约束)中;对于对齐的非聚集索引(不是唯一索引),分区列可以是包含列,也可以是索引键。

一,对齐的非聚集索引

创建分区函数,分区scheme,分区表和分区索引:

-- create parition function
create partition function pf_int_left (int)
as 
range left 
for values (10,20);

--create partition scheme
create partition scheme ps_int_left
as 
partition pf_int_left
to ([primary], [primary], [primary]);

-- create partition heap
create table dbo.dt_partitiontable
(
ID int not null,
code int not null,
name varchar(10) null,
)
on PS_int_Left(ID);
View Code

在分区表上创建非聚集索引,在默认情况下,创建的是对齐索引:

--create nonclustered index
create nonclustered index idx_partition_Code
on dbo.dt_partition_table(Code)

在分区表上创建辅助索引(不是唯一索引和聚集索引)时,默认创建的是对齐索引。如果索引键中包含分区列,并且使用相同的分区scheme,那么创建的索引就是和基础表对齐的索引。如果没有显式指定分区scheme,或者索引键中没有包含分区列,SQL Server自动向辅助索引中添加分区列,作为包含列。

二,对齐的聚集索引

在创建聚集索引时,SQL Server保证分区列是聚集索引键。如果没有显式指定分区列为聚集索引键,那么SQL Server会自动添加分区列作为索引列,注意:聚集索引没有包含列。

在创建聚集索引的语句中,如果没有显式指定分区列和分区scheme,那么SQL Server 使用基础表上的分区列和分区scheme创建聚集索引,SQL Server自动把分区列添加到索引键中。

create clustered index cidx_partition_table_code
on dbo.dt_partition_table(code)

创建聚集索引的目的是为了从物理上组织数据表的存储结构,由于,对表分区影响表的物理结构,使得表的数据按照物理存储顺序存储,因此,SQL Server内部强制分区列必须是聚集索引的一列。

三,对齐的唯一索引

在创建唯一索引(唯一索引和唯一约束)时,唯一索引键中必须显式包含分区列,SQL Server 强制每个分区上的索引列都是唯一的。这意味着,在创建对齐的唯一索引时,不管创建的是聚集索引还是非聚集索引,必须在唯一索引键中显式指定分区列。

--drop clustered index
drop index cidx_partition_table_code
on dbo.dt_partition_table

--create unique clustered index
create unique clustered index cidx_partition_table_code
on dbo.dt_partition_table(code)

--create unique nonclustered index
create unique nonclustered index idx_partition_table_code
on dbo.dt_partition_table(code)

由于唯一约束在底层使用唯一索引来保证唯一性,因此,在分区表上创建的唯一约束,必须显式包含分区列。

如果没有显式指定分区列,SQL Server会抛出错误消息:

Column 'ID' is partitioning column of the index 'xxx'. Partition columns for a unique index must be a subset of the index key.

四,对齐的主键约束

在SQL Server 内部,主键约束(Primary Key)自动创建唯一索引(unique index),只不过索引列必须是非空的(not null),因此,在创建主键约束时,不管创建的是聚集索引还是非聚集索引,主键必须显式包含分区列。

-- create pk nonclustered index
alter table dbo.dt_partition_table
add constraint PK__partition_table_code_nonclustered
primary key nonclustered (code)

-- create pk clustered index
alter table dbo.dt_partition_table
add constraint PK__partition_table_code_clustered
primary key clustered (code)

如果没有显式指定分区列,SQL Server会抛出错误消息:

Column 'ID' is partitioning column of the index 'PK__xxxx'. Partition columns for a unique index must be a subset of the index key.

五,非对齐索引

也可以在分区表上创建非对齐索引,所谓非对齐索引,是指索引的分区scheme和基础表的分区scheme不同,其物理存储结构也不同。在默认情况下,SQL Server在分区表上创建的是对齐索引,因此,要创建非对齐索引,必须在CREATE INDEX命令中显式指定索引结构存储的数据空间,数据空间是文件组,或者跟基础表不同的分区scheme。这意味着,非对齐索引存储在特定的文件组上,或者跟其基础表有不同的分区scheme。

create nonclustered index idx_partition_table_code
on dbo.dt_partition_table(code)
on file_group_name;

六,查看索引的元数据

使用系统视图:sys.indexes 查看索引的元数据:

select i.name as index_name
    ,i.index_id
    ,i.type_desc as index_type
    ,ds.name as data_space
    ,ds.type_desc as data_space_type
    ,i.is_unique
    ,i.is_unique_constraint
    ,i.is_primary_key
    ,ic.index_column_id
    ,col_name(ic.object_id,ic.column_id) as column_name
    ,ic.key_ordinal
    ,ic.partition_ordinal
    ,ic.is_included_column
from sys.indexes i 
inner join sys.index_columns ic
    on i.object_id=ic.object_id
        and i.index_id=ic.index_id
inner join sys.data_spaces ds 
    on i.data_space_id=ds.data_space_id
where i.object_id=object_id('table_name','U')
order by i.index_id
    ,ic.index_column_id

 

参考文档:

Partitioned Tables and Indexes

posted @ 2017-11-23 11:30  悦光阴  阅读(4725)  评论(0编辑  收藏  举报