U9数据库索引设计指南

U9数据库索引指南 文件编号：   版本号： V1.0 修改状态：   编写人： 黄卫 审核人： 张红斌 批准人：   批准时间：       适用对象 该指南适用于U9设计人员和开发人员。 适用数据库产品 该指南适用于MS SQL Server 2005数据库。 版本 此部分要记录该文档形成过程中的历次版本变更过程及变更的内容 版本 修改与参与人 修改时间 修改原因 修改概述 审批人 1.0 黄卫 2007/06/01 原始文档建立   张红斌                                                 相关文档 此部分包含对该文档起指导与约束作用的相关文档以及预计在该文档指导与约束下将要建立的文档。 1、 《U9数据库#设计#》 约定 ◆ 标有 ★ 的条目示强制性规范。 ◆ 无前缀词的“索引”默认指的是非聚集索引。 数据库索引设计细则 索引概述 索引是为了加速数据检索而设计的数据库对象。与书中的索引一样，数据库中的索引可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。 另外，索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。 索引类型 聚集索引 官方说明： 在 SQL Server 中，索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和叶节点包含含有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。 （聚集索引结构图） 聚集索引与非聚集索引最大的不同，是索引的叶节点不仅包含索引项，还同时包含数据行，即：聚集索引和表中数据构成了树结构。这意味着获取每条记录（select *）都会比非聚集索引要少一次IO。 聚集索引最适合排序性质的范围查询，因为从索引树上搜索到第一行数据后，可以不再做索引查找，而是连续读取直至超出需要的数据范围。这个效率是非常高的！ 聚集索引键值的排列顺序决定了表数据的物理存储顺序，所以一个表只能建立一个。默认情况下，会为主键自动创建聚集索引。构成聚集索引键的列越少越好，这样不光可以提高B树便利效率，而且非聚集索引的索引叶节点要包含聚集索引键。 U9主数据表的主键（ID）和多语表主键（ID、SysMLFlag）默认采用的是聚集索引。如果有特殊情况需要对其它字段使用聚集索引，需要审批。★ 以下访问方式，较适合建立聚集索引： ◆ 包含大量非重复值的列。 ◆ 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。 ◆ 被连续访问的列。 ◆ 返回大型结果集的查询。 ◆ 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。 ◆ OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。 以下访问方式，不适合建立聚集索引： ◆ 频繁更改的列 这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。 ◆ 宽键 来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。 非聚集索引 官方说明： 非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点： ◆ 基础表的数据行不按非聚集键的顺序排序和存储。 ◆ 非聚集索引的叶层是由索引页而不是由数据页组成。 非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。 （非聚集索引结构） 非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。 设置为非聚集索引的字段值（键值）是包含在非聚集索引中的。如果需要查询的字段已经完全包含在非聚集索引中是不需定位到表中再次查找的。这种情况被称为“索引覆盖”。 以下访问方式，较适合建立非聚集索引： ◆ 包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引。 ◆ 不返回大型结果集的查询。 ◆ 返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。 ◆ 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。 ◆ 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。 唯一索引 官方说明： 创建唯一索引可以确保任何生成重复键值的尝试都会失败。创建 UNIQUE 约束和创建与约束无关的唯一索引并没有明显的区别。进行数据验证的方式相同，而且对于唯一索引是由约束创建的还是手动创建的，查询优化器并不加以区分。 目前SQL Server数据库产品中，按照存储结构划分的索引类型只有聚集索引和非聚集索引两种。唯一索引是从功能上划分出来的索引种类。唯一索引也可以加速查询速度。 U9数据表的业务主键在不超过16个字段的情况下，都是使用唯一索引保证记录唯一的。（超过16个字段使用触发器检查唯一） 唯一索引可以保证其包含的键值组合唯一，与主键（Primary Key）不同的是唯一索引允许键值为Null。 索引相关概念 索引的选择性 索引的选择性是以每个索引键值对应的数据行数来衡量。良好的选择性要求选择的行数较少，最好的选择性是唯一索引。使用DBCC Show_Statistics(tablename, indexname)可以查看索引的选择性。密度（即重复度）越低，表示选择性越高。 下面为一个表中的三个字段和相应记录： （表记录） Col1的记录唯一性最高，选择性最好；Col2有部分重复记录，选择性一般；Col3全是重复记录，选择性最差。选择性排序：Col1〉Col2〉Col3。 索引是按照B树构建，索引键值的选择性会直接决定树中节点的重复度，因此直接影响索引的遍历效率。 索引的选择性是建立索引最重要的依据，不要为选择性差的列建立索引，例如性别。由于通过索引获取数据不仅要读取数据块，还要读取索引块，选择性差的索引会导致读取更多的索引块才能定位到需要的数据。在实际应用过程中，发现这种索引不仅无用，还可能反而带来性能问题，因为有时候查询优化器会被误导做出错误的选择。 当一个索引由多个列构成时，应注意将选择性强的列放在前面。仅仅前后次序的不同，性能上就可能出现数量级的差异。 当选择性较差的字段出现在Select谓词中，考虑到索引覆盖可以将该字段放在索引包含性列中，这样其将被放置在索引树的叶子节点上，避免其参与索引树中间结点的构建。 索引覆盖 索引覆盖的含义是被查询的列均包含在非聚集索引中，无须再额外通过索引项去表中查找数据。对于查询数据较多的情况，这可以减少大量的IO。 当没有形成索引覆盖的时候，查询计划中将看到bookmark lookup或clustered index seek，这是由于查询所需数据不能在非聚集索引中完全找到，需要定位到表中获取。 SQL Server 2005提供了索引包含性列功能，增加了索引覆盖的可能。一般非聚集索引的设计可以考虑将Where谓词中的字段作为索引键值，Select谓词中的字段（不包含在Where谓词中的）作为索引包含性列放在非聚集索引的叶子节点上。 例如，表Table1有字段Col1、Col2、Col3、Col4。如果为Col2和Col3建立了非聚集索引IX_Col2_Col3： Create Index IX_Col2_Col3 On Table1(Col2, Col3) 当发生类似 Select Col3  from Table1 Where Col2=1001或Select Col2, Col3  from Table1 Where Col2=1001 and Col3=1这样的查询，只在IX_Col2_Col3中就可以获取全部数据，此时不需要定位到表中再次查询。 索引前导列 当索引由多个字段组成时，字段在索引中的排列顺序会直接影响查询时索引的使用效率。通常情况索引中第一个字段的检索效率最高，也被称为前导列。多字段组成的索引，要按照字段使用频率和字段选择性从高到低顺序排列。 索引查找 索引查找是一种逻辑运算符。其物理实现可以是聚集索引查找（Clustered Index Seek）和非聚集索引查找（NoClustered Index Seek）。 索引查找只检索特定范围的行。在索引键选择性较好并且索引键的信息可以直接定位的时候，在查询计划中会看到此运算符。索引查找是索引利用率最高的一种表现。    索引扫描 索引扫描是一种逻辑运算符。其物理实现可以是聚集索引扫描（Clustered Index Scan）或非聚集索引扫描（NoClustered Index Scan）。 索引扫描将检索索引的一定范围或全部行。当在执行计划中发现索引扫描时，应该警惕是否索引或操作不合理。以下情况可能会发生索引扫描： ◆ Where谓词中对索引字段进行了函数运算。 ◆ 索引的前导列选择不当，查询中靠后的索引键是主要的检索依据。 ◆ Where谓词中对索引字段进行了，以通配符开头的like操作。如：Col1 like‘%aaa’ ◆ 需要查询索引字段的大部份或完整的行集。 应用索引场景 物理主键★ 为数据表物理主键和多语表物理主键（ID、SysMLFlag）默认建立聚集索引。如果有特殊情况需要对其它字段使用聚集索引，需要审批。 业务主键★ 为数据表的业务主键建立非聚集唯一索引。如果索引字段超过16个，UBFStadio自动将唯一索引转换为触发器。为了保证唯一，不能将业务主键字段放在包含性列中。 业务主键的主要功能是保证记录的业务唯一性和加快按业务查询记录的速度。设计业务主键时注意将字段使用频率和字段选择性从高到低顺序排列。 选择前导列 建立复合索引时，注意将选择性最好且使用频繁的字段作为前导列。前导列的选择将直接影响索引的使用效率。