SQL Server对象类型（2）——4.2.索引（Index）（1）_sql server索引

4.2.1. 索引概念

索引是建立在表上的逻辑对象，其上建立索引的表称为该索引的基表，通过建立索引，可以提高访问其基表中数据的效率，从而起到优化性能的作用。前文中提到，索引也是和一个或多个分区相关联的，索引中的数据最终也是存储到其相应的分区里的,这点虽然和表类似，但索引相应的分区并不是按照堆的结构组织和存储的，索引结构和存储方式的具体细节，和索引的类型乃至具体版本相关，这里不做进一步探究，感兴趣的同学，可以参考官方或其他文档。SQL Server中，以普通的B-Tree索引最为常见和常用，日常工作中，提到索引，我们一般也是指这种索引。我们的学习也主要围绕这种索引展开。

--启示：

      1）读者思考下，为什么叫B-Tree索引呢？这里的B是指哪个单词呢？具体又是什么意思呢？

4.2.2. 索引注解

1）SQL Server中，所有索引对其基表来说并非强制，而是可选的，用户可以选择不创建，创建一个或多个，其有时可以大幅提升相关表数据的检索性能。作为关系数据库最基本、最重要的优化手段之一，索引在性能优化方面，堪称关系库解决性能问题的一把“利器”。

2）与所有其他关系库类似，SQL Server中，索引虽然也许会大幅提升对表数据的检索效率，但也会影响DML（Data Manipulation Languge,例如：insert,delete,update）操作的性能。因为DML操作在修改表数据时，也会自动修改和维护该表相关索引及其数据，而且因为索引中的数据必须是有序的，这种有序操作，有时会很大程度上影响整个DML操作的性能，所以，利用索引进行优化其实是在查询性能与DML操作性能之间的折衷和权衡。

3）作为一款相对成熟的商业库，SQL Server中的索引类型也比较全面而众多，这点来讲，各关系数据库中的分类、功能及具体实现机制也不尽相同，例如：SQL Server中索引类型包括，簇索引，非簇索引，唯一性索引，哈希索引，含盖列索引（Index With Included Columns），计算列索引，内存优化非簇索引，列存储索引，过滤索引，空间索引，全文索引等。在此，我们主要讲解最常用的簇索引和非簇索引。

4）簇索引和非簇索引：SQL Server中，创建一张表时，我们可以选择为其创建索引，也可以选择不为其创建索引；可以选择为其创建一个索引，也可以选择为其创建多个索引；可以选择为其创建簇索引，也可以选择不为其创建簇索引；可以选择为其既创建簇索引，也创建一个或多个非簇索引，等等。

簇索引的叶级数据页包含其基表的所有数据，因此，建有簇索引的表中的数据是有序的，其顺序与该簇索引中键值顺序一致。当一张表上不存在簇索引时，该表相应的分区是以堆的结构形式组织和存储的，一旦为其创建了一个簇索引，那么，系统将会为该表创建一个以B-tree结构形式组织和存储的分区，将该表数据由原来的堆分区移入该新建的B-tree分区中，将原来的堆分区清除并回收其相应空间。反之，当我们将一张表上的簇索引删除时，系统将为该表创建一个以堆的结构形式组织和存储的分区，将该表数据由原来的B-tree分区移入该新建的堆分区中，清除原来的B-tree分区并回收其相应空间。大家可以看到，在创建和删除簇索引的过程中，因为涉及堆分区和B-tree分区的创建及清除，以及表数据的移出和移入，期间，还会涉及表中数据的排序，该过程将会消耗大量的系统资源，因此，簇索引类似的相关操作在生产或重要环境还是尽量不做或少做。

SQL Server中，非簇索引与Oracle中的普通B-tree索引类似，其基表相应的分区及数据顺序不会因为非簇索引的存在与否、变化与否而发生改变，那就是，非簇索引对其基表数据的存储及顺序不会发生任何影响。

SQL Server中，一张表上只能创建一个簇索引，但可以为其创建一到多个非簇索引，当然，你也可以选择不为其创建任何索引。

--启示：

      1）请读者思考下，为什么不能在同一张表上创建多个簇索引？为什么又可以在同一张表上创建多个非簇索引？

5）唯一值数（Distinct Values）:一个字段中不同值的个数,例如：一张1000w行数据的表，性别字段的唯一值数就是2，而身份证号字段的唯一值数就是1000w。不像Oracle，SQL Server中并没有地方直接提供字段的唯一值数，我们之所以在这里提出这个概念并对其进行专门说明，那是因为，其对任一关系库的优化器来说，都至关重要，它是计算关系库统计信息中某些属性的基础，也是优化器进行精确计算的基石。

--启示：

      1）请读者思考下，我们如何才能获取表中某个或某几个字段的唯一值数？

6）密度（Denity）：该值由1/(Distinct Values)计算而来，其中，Distinct Values为某个或某些字段的唯一值数。虽然SQL Server中，计算字段密度的算法没有这么简单，也在随着版本的升级而变化，但密度这个概念与上述的唯一值数一样，它是关系库统计信息的核心，更是优化器赖以的重要基石。与上述的唯一值数相比，SQL Server中，密度更贴近实际的统计信息，只不过现实中，尤其是高版本SQL Server数据库密度计算更为复杂些，具体将在本书统计信息相关部分详述。

7）选择性（Selectivity）：(Distinct Values)/(总行数)的百分比，列选择性值越高，列的选择性就越好。该值与前述Distinct Values及密度同样，是关系库优化器的核心和基石，也许关系库中并没有地方直接提供该值，但这并不影响其对关系库优化器的重要性。

--启示：

      1）请读者思考下，列选择性的好与不好，意味着什么？从优化和索引角度，又意味着什么？

8）直方图（Histograms）：精确反映列数据分布情况的统计信息，避免了因字段数据分布倾斜，而导致优化器为SQL语句生成次优查询计划的问题。与Oracle不同，SQL Server中的直方图只有一种，其既不完全同于Oracle中的频率直方图（Frequency Histograms），也不完全同于其高度平衡直方图（Height Balance Histograms），大略讲的话，应该是两者的结合，SQL Server针对具体的数据分布，通过其内部的机制和算法来对相关数据进行评估，并为其生成优化器赖以的、最为理想的直方图。Oracle中，直方图的桶数（Buckets）最大为254个，而SQL Server中，与其对应的概念是步长（Steps），其最大值为200个，这里提到的这些具体数字，可能因数据库版本的不同而不同。

直方图是任何关系库优化器相关的重要机制，随着SQL Server不同新版本的发布和升级，其功能与具体实现机制也在不断变化和完善，这里，我们不再做进一步深入探讨，感兴趣的读者，可以参考官方或其他相关文档。

另外，与Oracle不同，SQL Server中有些视图虽然提供了统计信息相关的概略信息，但获取具体统计信息及直方图的相关信息，只能通过特定工具（SSMS）及相关特定工具命令（dbcc show_statistics），有关工具使用此处不再赘述，读者可以参考官方或其他相关文档，或者自己动手实践。有关特定工具命令，具体如下所示。

use test

go

dbcc show_statistics("test.dbo.t1",i12_t1);

go

--启示：

      1）请读者思考下，SQL Server，什么是直方图的步长？其作用是什么？

      2）SQL Server中，一张表的某个字段没有收集直方图，当一个带有该字段相关where条件的SQL语句运行时，优化器会如何处理该字段的数据分布情况？反之，优化器又会如何处理该字段的数据分布情况？

9）填充因子（fill_factor）：SQL Server中，该选项用于调整索引相关的数据存储和性能。索引创建和重建时，填充因子值决定了每个叶级数据页中用于填充数据的百分比，而剩余其他空间保留用于未来数据增长所需的空闲空间，例如：确定80的填充因子值，意味着每个叶级数据页中20%的空间将保持空闲，用于将来索引基表数据增加时提供索引扩展所需的空间。这些空闲空间位于索引行之间，而非索引的末端。填充因子的值为1~100之间，系统范围内的默认值为0，这意味着创建或重建索引时，叶级数据页被填满而非保留空闲空间。因此，填充因子值为0或100并没有什么区别。我们可以查询目录视图sys.indexes获取索引填充因子的相关信息。

select name,fill_factor from sys.indexes where ...;

--注：

      1）请读者思考下，索引的填充因子值是小比较好？还是大比较好？为什么？

      2）请读者思考下，索引的填充因子过大或过小，分别会有什么影响或后果？

      3）大家知道，Oracle中的索引有簇因子（clustering_ factor）的概念，而SQL Server中并没有该概念，那么，请读者思考下这是为什么？没有该概念会不会对SQL Server有什么影响？

10）索引信息的获取：可以通过查询目录视图sys.indexes来获取索引的相关信息。

select ... from sys.indexes where ...;

4.2.3. 一致索引（Aligned Index）和非一致索引（Nonaligned- Index）

1）一致索引和非一致索引的概念：Oracle中，分区表上的索引分为本地索引（Local Index）和全局索引（Global Index），本地索引的分区字段与其基表的分区字段完全一样，而全局索引的分区字段与其基表的分区字段并不相同。与Oracle类似，SQL SERVER中，分区表上的索引分为一致索引与非一致索引。一致索引与其基表建立在同一分区方案上，因此，当对该表及其一致索引分区结构进行维护时，SQL Server都能快速高效的对分区进行转换。一致索引与其基表并不一定非用同一分区函数，但其分区函数必须在本质上与其基表的相同。所谓本质相同，即，分区函数的参数类型、分区数及分区边界值必须相同。非一致索引独立于其基表，而拥有不同的分区方案，或其位于与其基表不同的另外文件组。非一致索引主要用于如下场景，其基表还未分区，或其键值唯一但不包含其基表分区字段，或其基表将通过不同的字段与其他多个表分别进行连接。

2）一致索引和非一致索引的区别及联系：SQL Server中，一致索引和非一致索引，都是针对其分区基表的。一致索引是分区索引，其分区方案依赖于其基表且本质相同，因此，两者的分区对应的数据完全相同；而非一致索引可以选择分区或不分区，其分区方案独立于其基表，因此，两者的分区对应数据也许并不相同。

3）一致索引和非一致索引索引的优势：SQL Server中，一致索引的优势就是便于管理和维护，期间，表及其索引的分区结构将被维护而不会被破坏与失效，管理和维护相关的操作也会非常高效。此外，用户应用分区相关的表和数据时，也会因为分区消除而性能得以大幅提升，甚至在有些场景的性能会是天壤之别；而非一致索引在应用分区相关的表及数据时，不需要考虑分区字段（Partition Column）与分区消除（Partition Elimination）等因素，同时，在某些特殊场景可以满足特定性能或功能需求。

--注：

      1）请读者了解并思考，什么是分区字段和分区消除？其作用分别是什么？

4）一致索引和非一致索引的劣势：SQL Server中，一致索引的劣势就是应用分区相关的表及数据时，需要考虑分区字段和分区消除等更多因素，不然，也许会出现不同程度的性能问题；非一致索引的劣势就是对分区相关的表及数据进行管理和维护期间，可能会对索引造成破坏，进而导致索引的失效和不可用，这样，不但会造成应用方面的问题，后期还会涉及到索引的处理和重建等工作。此外，多数应用分区相关表及数据的场景中，非一致索引的性能也许不会那么理想。

5）一致索引和非一致索引信息的获取：SQL Server中，可以通过查询sys.indexes和sys.partitions等目录视图获取一致索引和非一致索引的相关信息。

select t.name as table_name,i.name as index_name,i.index_id,i.type_desc as index_type,p.partition_id ,p.partition_number

from sys.tables t

inner join sys.partitions p on t.object_id=p.object_id

inner join sys.indexes i on p.object_id=i.object_id and p.index_id=i.index_id

where t.name='t1' and  i.index_id>=0

order by i.object_id,i.index_id,partition_number;

--启示：

      1）请读者思考，通过上述目录视图和SQL语句，我们是否能知道一个索引是一致索引或非一致索引？如果能，如何判断？