SQLServer之索引简介

索引设计基础知识

       索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。 索引包含由表或视图中的一列或多列生成的键。 这些键存储在一个结构（b 树）中，使 sql server 可以快速高效地找到与键值关联的行。

       为数据库及其工作负荷选择正确的索引是一项需要在查询速度与更新所需开销之间取得平衡的复杂任务。 如果索引较窄，或者说索引关键字中只有很少的几列，则需要的磁盘空间和维护开销都较少。 而另一方面，宽索引可覆盖更多的查询。 您可能需要试验若干不同的设计，才能找到最有效的索引。 可以添加、修改和删除索引而不影响数据库架构或应用程序设计。 因此，应试验多个不同的索引而无需犹豫。

       sql server 中的查询优化器可在大多数情况下可靠地选择最高效的索引。 总体索引设计策略应为查询优化器提供可供选择的多个索引，并依赖查询优化器做出正确的决定。 这在多种情况下可减少分析时间并获得良好的性能。 若要查看查询优化器对特定查询使用的索引，请在 sql server management studio 中的“查询”菜单上选择“包括实际的执行计划”。

        不要总是将索引的使用等同于良好的性能，或者将良好的性能等同于索引的高效使用。 如果只要使用索引就能获得最佳性能，那查询优化器的工作就简单了。 但事实上，不正确的索引选择并不能获得最佳性能。 因此，查询优化器的任务是只在索引或索引组合能提高性能时才选择它，而在索引检索有碍性能时则避免使用它。

索引分类规则 

• 聚集还是非聚集。
• 唯一还是非唯一。
• 单列还是多列。
• 索引中的列是升序排序还是降序排序。
• 非聚集索引是全表还是经过筛选。

索引简介

聚集索引

聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。

聚集索引基于数据行的键值在表内排序和存储这些数据行，在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。

每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

注意事项

一般情况下，定义聚集索引键时使用的列越少越好。

适用情况：

• 唯一或包含许多不重复的值。

• 按顺序被访问。
• 定义为 identity。
• 经常用于对表中检索到的数据进行排序。

不适用情况：

• 频繁更改的列

  这将导致整行移动，因为数据库引擎必须按物理顺序保留行中的数据值。 这一点要特别注意，因为在大容量事务处理系统中数据通常是可变的。

• 宽键

  宽键是若干列或若干大型列的组合。 所有非聚集索引将聚集索引中的键值用作查找键。 为同一表定义的任何非聚集索引都将增大许多，这是因为非聚集索引项包含聚集键，同时也包含为此非聚集索引定义的键列。

非聚集索引

索引理解为一种特殊的目录，目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

非聚集索引包含索引键值和指向表数据存储位置的行定位器。

可以对表或索引视图创建多个非聚集索引，一个表如果没有聚簇索引时，可有250个非聚簇索引。

通常，设计非聚集索引是为改善经常使用的、没有建立聚集索引的查询的性能。

与使用书中索引的方式相似，查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。 这使非聚集索引成为完全匹配查询的最佳选择，因为索引包含说明查询所搜索的数据值在表中的精确位置的项。

每个非聚簇索引提供访问数据的不同排序顺序。

注意事项

适用于：

• 某列常用于集合函数(如sum,....)。
• 覆盖查询。
• 大量非重复值，如姓氏和名字的组合（前提是聚集索引被用于其他列）。
• 查寻出的数据不超过表中数据量的20%。

唯一索引

唯一索引能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。

只有当唯一性是数据本身的特征时，指定唯一索引才有意义。

使用多列唯一索引，索引能够保证索引键中值的每个组合都是唯一的。

聚集索引和非聚集索引都可以是唯一的。 只要列中的数据是唯一的，就可以为同一个表创建一个唯一聚集索引和多个唯一非聚集索引。

注意事项

优点：

• 能够确保定义的列的数据完整性。
• 提供了对查询优化器有用的附加信息。

主键索引

主键是一个约束（constraint），它依附在一个索引上，这个索引可以是聚集索引，也可以是非聚集索引。

在数据库关系图中为表定义一个主键将自动创建主键索引，只能说明它上面有个索引，但不一定就是聚集索引。

主键具有唯一性，而只有唯一性索引才具有唯一性，主键索引要求主键中的每个值是唯一的。

当在查询中使用主键索引时，它还允许快速访问数据。

主键索引是唯一索引的特殊类型。

主键是索引，但索引不一定是主键。

筛选索引

筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。

筛选索引使用筛选谓词对表中的部分行进行索引。

与全表索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。

筛选索引与全表索引相比具有以下优点：

• 提高了查询性能和计划质量
• 减少了索引维护开销
• 减少了索引存储开销
• 仅包含少量非 null 值的稀疏列。
• 包含多种类别的数据的异类列。
• 包含多个范围的值（如美元金额、时间和日期）的列。
• 由列值的简单比较逻辑定义的表分区。

注意事项

适用于：

• 数据子集的筛选索引

  在列中只有少量相关值需要查询时，可以针对值的子集创建筛选索引。

• 异类数据的筛选索引

          表中含有异类数据行时，可以为一种或多种类别的数据创建筛选索引。

• 键列

          最好在筛选索引定义中包含少量的键或包含列，并且只包含查询优化器为查询执行计划选择筛选索引所需的列。 无论某一筛选索引是否涵盖了查询，查询优化器都可以为查询选择此筛选索引。 但是，如果某一筛选索引涵盖了查询，则查询优化器更有可能选择此筛选索引。

• 筛选谓词中的数据转换运算符

          如果筛选索引结果的筛选索引表达式中指定的比较运算符会导致隐式或显式数据转换，则转换发生在比较运算符的左边时，会出现错误。 解决方法是在比较运算符的右边编写包含数据转换运算符（cast 或 convert）的筛选索引表达式。

全文索引概念

全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。 全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。 全文索引是一种特殊类型的基于标记的功能性索引，它是由 sql server全文引擎生成和维护的。 生成全文索引的过程不同于生成其他类型的索引。 全文引擎并非基于特定行中存储的值来构造 b 树结构，而是基于要编制索引的文本中的各个标记来生成倒排、堆积且压缩的索引结构。 全文索引大小仅受运行 sql server 实例的计算机的可用内存资源限制。

从 sql server 2008开始，全文索引与数据库引擎集成在一起，而不是像 sql server早期版本那样位于文件系统中。对于新数据库，全文目录现在为不属于任何文件组的虚拟对象；它仅是一个表示一组全文索引的逻辑概念。

注意事项

• 全文索引是针对数据表，只能对表创建全文索引，不能对数据库创建全文索引。
• 每个数据库可以不包含全文目录或包含多个全文目录，一个全文目录可以包含多个全文索引，但一个全文索引只能用于构成一个全文目录。
• 一个数据表只能创建一个全文索引，一个全文索引可以包含多个字段。
• 创建全文索引的表必须要有一个唯一的非空索引，并且这个唯一的非空的索引只能是一个字段，不能是组合字段。
• 若要对某个表创建全文索引，该表必须具有一个唯一且非 null 的列。您可以对以下类型的列创建全文索引：char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 和 varbinary(max)，从而可对这些列进行全文搜索。对数据类型为 varbinary、varbinary(max)、image 或 xml 的列创建全文索引需要您指定类型列。类型列是用来存储每行中文档的文件扩展名（.doc、.pdf、xls 等）的表列。

xml索引

xml 索引分为下列类别：主 xml 索引和辅助 xml 索引

主 xml 索引

主 xml 索引对 xml 列中 xml 实例内的所有标记、值和路径进行索引。 若要创建主 xml 索引，相应 xml 列所在的表必须对该表的主键创建了聚集索引。 sql server 使用此主键将主 xml 索引中的行与包含此 xml 列的表中的行关联起来。

主 xml 索引是中的 xml blob 的已拆分和持久的表示形式xml数据类型列。 对于列中的每个 xml 二进制大型对象 (blob)，索引将创建数个数据行。 该索引中的行数大约等于 xml 二进制大型对象中的节点数。 当查询检索完整的 xml 实例时， sql server 会提供此 xml 列中的实例。 xml 实例中的查询使用主 xml 索引，并可以通过使用索引本身返回标量值或 xml 子树。

辅助 xml 索引

为了增强搜索性能，可以创建辅助 xml 索引。 必须有了主 xml 索引才能创建辅助索引。 辅助索引的类型如下：

• path 辅助 xml 索引

　　   如果查询通常对 xml 类型列指定路径表达式，则 path 辅助索引可以提高搜索的速度。

• value 辅助 xml 索引

          如果查询是基于值的查询，例如 /root/productdescription/@*[. = "mountain bike"] 或 //productdescription[@name = "mountain bike"]，且没有完全指定路径或路径包含有通配符，则生成基于主 xml 索引中的节点值所创建的辅助 xml 索引可以更快地获得结果。

          value 索引的键列是主 xml 索引的节点值和路径。 如果您的工作负荷涉及到查询 xml 实例中的值，但不知道包含这些值的元素名称或属性名称，则 value 索引可能会很有用。

• property 辅助 xml 索引

          从单个 xml 实例检索一个或多个值的查询适用 property 索引。 通过检索对象属性时，会发生这种value （) 方法的xml类型并且知道对象的主键值。

          property 索引是对主 xml 索引的列（pk、path 和节点值）创建的，其中 pk 是基表的主键。

以下为创建一个或多个辅助索引的一些准则：

• 如果工作负荷对 xml 列大量使用路径表达式，则 path 辅助 xml 索引可能会提高工作负荷的处理速度。 最常见的情况是在 transact-sql 的 where 子句中对 xml 列使用 exist() 方法。

• 如果工作负荷通过使用路径表达式从单个 xml 实例中检索多个值，则在 property 索引中聚集各个 xml 实例中的路径可能会很有用。 这种情况通常出现在属性包方案中，此时提取对象的属性并且已知其主键值。

• 如果工作负荷涉及查询 xml 实例中的值，但不知道包含那些值的元素名称或属性名称，则您可能希望创建 value 索引。 这通常出现在 descendant 轴查找中，例如 //author[last-name="howard"]，其中 <author> 元素可以出现在层次结构的任何级别上。 这种情况也出现在通配符查询中，例如 /book [@* = "novel"]，其中查询将查找具有某个值为“novel”的属性的 <book> 元素。