分区表的索引

深藏阁楼爱情的钟 2022-02-02 07:51 256阅读 0赞

# [分区表的索引][Link 1] #

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

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

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

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

**一，对齐的非聚集索引**

创建分区函数，分区scheme，分区表和分区索引：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

-- 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);

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

--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 强制每个分区上的索引列都是唯一的。这意味着，在创建对齐的唯一索引时，不管创建的是聚集索引还是非聚集索引，必须在唯一索引键中显式指定分区列。

![复制代码][copycode.gif]

--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)

![复制代码][copycode.gif]

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

如果没有显式指定分区列，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），因此，在创建主键约束时，不管创建的是聚集索引还是非聚集索引，主键必须显式包含分区列。

![复制代码][copycode.gif]

-- 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)

![复制代码][copycode.gif]

如果没有显式指定分区列，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 查看索引的元数据：

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

参考文档：

[Partitioned Tables and Indexes][]

**作者**： [悦光阴][Link 2]

**出处**： [http://www.cnblogs.com/ljhdo/][Link 2]

本文版权归作者和博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，且在文章页面醒目位置显示原文连接，否则保留追究法律责任的权利。

分类: [Partition][]

标签: [Partition][Partition 1], [分区索引][Link 3]

本文转自悦光阴博客园博客，原文链接：http://www.cnblogs.com/ljhdo/p/5015995.html，如需转载请自行联系原作者

[Link 1]: http://www.cnblogs.com/ljhdo/p/5015995.html
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20220202/92f92267c0084538ac2aea2c75a8cf8a.png
[copycode.gif]: /images/20220202/06ac27bf75be4066b87ed9f3bcfa855b.png
[Partitioned Tables and Indexes]: https://msdn.microsoft.com/en-us/library/ms190787%28v=sql.110%29.aspx
[Link 2]: http://www.cnblogs.com/ljhdo/
[Partition]: http://www.cnblogs.com/ljhdo/category/763026.html
[Partition 1]: http://www.cnblogs.com/ljhdo/tag/Partition/
[Link 3]: http://www.cnblogs.com/ljhdo/tag/%E5%88%86%E5%8C%BA%E7%B4%A2%E5%BC%95/