U9数据库索引设计指南.doc

U9数据库索引设计指南文件编号：版本号：V1.0修改状态：编写人：黄卫审核人：张红斌批准人：批准时间：适用对象该指南适用于U9设计人员和开发人员。适用数据库产品该指南适用于MS SQL Server 2005数据库。版本记录此部分要记录该文档形成过程中的历次版本变更过程及变更的内容版本修改与参与人修改时间修改原因修改概述审批人1.0黄卫2007/06/01原始文档建立张红斌相关文档此部分包含对该文档起指导与约束作用的相关文档以及预计在该文档指导与约束下将要建立的文档。1、 U9数据库设计规范约定u 标有 的条目表示强制性规范。u 无前缀词的“索引”默认指的是非聚集索引。数据库索引设计细则索引概述索引是为了加速数据检索而设计的数据库对象。与书中的索引一样，数据库中的索引可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。另外，索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。索引类型聚集索引官方说明：在 SQL Server 中，索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和叶节点包含含有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。（聚集索引结构图）聚集索引与非聚集索引最大的不同，是索引的叶节点不仅包含索引项，还同时包含数据行，即：聚集索引和表中数据构成了树结构。这意味着获取每条记录（select *）都会比非聚集索引要少一次IO。聚集索引最适合排序性质的范围查询，因为从索引树上搜索到第一行数据后，可以不再做索引查找，而是连续读取直至超出需要的数据范围。这个效率是非常高的！聚集索引键值的排列顺序决定了表数据的物理存储顺序，所以一个表只能建立一个。默认情况下，会为主键自动创建聚集索引。构成聚集索引键的列越少越好，这样不光可以提高B树便利效率，而且非聚集索引的索引叶节点要包含聚集索引键。U9主数据表的主键（ID）和多语表主键（ID、SysMLFlag）默认采用的是聚集索引。如果有特殊情况需要对其它字段使用聚集索引，需要审批。以下访问方式，较适合建立聚集索引：u 包含大量非重复值的列。u 使用下列运算符返回一个范围值的查询：BETWEEN、=、 和 =。u 被连续访问的列。u 返回大型结果集的查询。u 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。u OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。以下访问方式，不适合建立聚集索引：u 频繁更改的列 这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。u 宽键 来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。非聚集索引官方说明：非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点： u 基础表的数据行不按非聚集键的顺序排序和存储。u 非聚集索引的叶层是由索引页而不是由数据页组成。非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。（非聚集索引结构）非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。设置为非聚集索引的字段值（键值）是包含在非聚集索引中的。如果需要查询的字段已经完全包含在非聚集索引中是不需定位到表中再次查找的。这种情况被称为“索引覆盖”。以下访问方式，较适合建立非聚集索引：u 包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引。u 不返回大型结果集的查询。u 返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。u 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。u 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。唯一索引官方说明：创建唯一索引可以确保任何生成重复键值的尝试都会失败。创建 UNIQUE 约束和创建与约束无关的唯一索引并没有明显的区别。进行数据验证的方式相同，而且对于唯一索引是由约束创建的还是手动创建的，查询优化器并不加以区分。目前SQL Server数据库产品中，按照存储结构划分的索引类型只有聚集索引和非聚集索引两种。唯一索引是从功能上划分出来的索引种类。唯一索引也可以加速查询速度。U9数据表的业务主键在不超过16个字段的情况下，都是使用唯一索引保证记录唯一的。（超过16个字段使用触发器检查唯一）唯一索引可以保证其包含的键值组合唯一，与主键（Primary Key）不同的是唯一索引允许键值为Null。 索引相关概念索引的选择性索引的选择性是以每个索引键值对应的数据行数来衡量。良好的选择性要求选择的行数较少，最好的选择性是唯一索引。使用DBCC Show_Statistics(tablename, indexname)可以查看索引的选择性。密度（即重复度）越低，表示选择性越高。下面为一个表中的三个字段和相应记录：（表记录）Col1的记录唯一性最高，选择性最好；Col2有部分重复记录，选择性一般；Col3全是重复记录，选择性最差。选择性排序：Col1Col2Col3。索引是按照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）。索引扫描将检索索引的一定范围或全部行。当在执行计划中发现索引扫描时，应该警惕是否索引或操作不合理。以下情况可能会发生索引扫描：u Where谓词中对索引字段进行了函数运算。u 索引的前导列选择不当，查询中靠后的索引键是主要的检索依据。u Where谓词中对索引字段进行了，以通配符开头的like操作。如：Col1 like%aaau 需要查询索引字段的大部份或完整的行集。应用索引场景物理主键为数据表物理主键和多语表物理主键（ID、SysMLFlag）默认建立聚集索引。如果有特殊情况需要对其它字段使用聚集索引，需要审批。业务主键为数据表的业务主键建立非聚集唯一索引。如果索引字段超过16个，UBFStadio自动将唯一索引转换为触发器。为了保证唯一，不能将业务主键字段放在包含性列中。业务主键的主要功能是保证记录的业务唯一性和加快按业务查询记录的速度。设计业务主键时注意将字段使用频率和字段选择性从高到低顺序排列。选择前导列建立复合索引时，注意将选择性最好且使用频繁的字段作为前导列。前导列的选择将直接影响索引的使用效率。下面是性能分析时发现的一个索引前导列选择不当的例子：凭证分录（GL_Entry）加载时发出如下SQL：其执行计划：索引扫描占整个SQL消耗的76%。索引扫描是非聚集索引使用率不高的一种现象。进一步观察其具体参数：其为我们提供了以下信息：1、 索引扫描是响应SQL中“where (A.Voucher = 1)” 的操作。2、 被使用的索引名为a26718ce-67b0-429f-84df-84b19d32e801_Unique。3、 该索引包含两个索引键：SerialNo、Voucher。具体察看a26718ce-67b0-429f-84df-84b19d32e801_Unique的定义：索引键SerialNo放在了Voucher前面。SerialNo是分录的顺序号，只要在同一凭证中顺序号不重复即可，业务上单独按照其进行筛选的概率非常低。Voucher是分录的凭证信息，是凭证和分录联系的纽带。Voucher被放在SerialNo后面，直接导致了“where (A.Voucher = 1)” 利用索引效率不高。改变索引键顺序：“where (A.Voucher = 1)”操作由“索引扫描”变为“索引查找”：从成本占用率76%到1%，优化效果非常明显。实现索引覆盖当Select谓词中使用字段不是很多时，应尽量实现索引覆盖。例如，对GL_Entry有如下查询：如果单独对Voucher建立索引其执行计划如下：Select谓词中对GL_Entry字段访问较少，我们应该考虑实现索引覆盖。OpposingAccountCodes和OpposingAccountNames是nvarchar字段，作为索引键会降低索引遍历效率。比较好的选择是将其作为索引包含性列放在索引的叶子节点上，这样既可以实现索引覆盖，又不影响索引遍历效率。将索引修改如下：修改后的执行效率更高：需要注意的是，如果SQL为如下方式：最佳的索引还是：通过SQL Server自带的工具“数据库引擎优化顾问”可能会建议将Voucher作为索引键，将表的其它所有字段都作为索引包含性列，但是这样将会严重影响insert和update操作的效率。请时刻牢记：每个非聚集索引中的字段（索引键和包含性列）都是表中相应字段的一个独立的副本。索引个数和其包含的内容越多，每次修改记录时维护这些副本的代价就越重。关联表索引对于组合或引用关系的子表，要求在关联主表的列上建立索引。例如应为子表RCV_ReceivementLine关联主表的Receivement建立索引。如果索引只包含Receivement字段适合进行如下查询：当查询的Where谓词中有对RCV_ReceivementLine表其它字段的过滤条件，应该考虑建立组合索引：对上面的查询应该建立Receivement和StorageType的联合索引。从两个字段中挑选选择性最好的作为索引前导列。（本例中使用StorageType作为前导列）建立索引后应该查看相关SQL的执行计划以确定索引是否被使用及使用效率。没有建立索引的执行计划：建立索引后的执行计划：没有建立联合索引前，对RCV_ReceivementLine进行的是聚集索引扫描。这里需要注意，表一旦建立聚集索引后，整体结构就是按照聚集索引键构建成的B树，而不是以往的堆结构，聚集索引扫描此时就是变相的表扫描。建立索引IX_StorageType_Receivement后，首先在该索引中查找到符合条件的键值，然后定位到表RCV_ReceivementLine中关联Select B.*所需要的其它字段。树表索引应用TreeEntity版型的表，应为ID和ParentID建立索引，当ID已经为聚集索引时只需要为ParentID及其它过滤字段建立索引即可。例如平台资源表（UBF_RES_ResourceValue）的结构如下：ResourceName和ParentResourceName构成了树结构，一般会使用如下查询SQL对树进行查询：在本例中应该建立如下索引：执行计划：没有将ResourceName加入到IX_RES索引中，是因为ResourceName建立了聚集索引，ResourceName的内容已经包含在非聚集索引IX_RES中。在表中没有建立聚集索引时，非聚集索引是通过RID（堆定位指针）定位到表中记录。一旦建立了聚集索引，非聚集索引中都会包含聚集索引的键值，如果必要，非聚集索引会通过这个键值定位到表中的记录。排序分组操作需要关注Order By和Group By谓词的索引设计。Order By和Group By的谓词是需要排序的，某些情况下为Order By和Group By的谓词建立索引，会避免查询时的排序动作。例如对GL_Entry有如下查询：如果单独对Voucher建立索引其执行计划如下：排序操作占整个SQL消耗的30%。将索引进行调整可以避免该排序消耗。索引调整为：执行计划：联接操作不合理当出现不合理的联接操作（此处的联接操作是比表与表间联接更细粒度的查询概念）时，应该考虑到索引因素。当表中没有索引或其它原因导致索引无法利用时，非大记录集之间的关联操作可能会发生哈希关联。出现哈希关联并不一定意味着缺少索引，但是应该对执行计划仔细观察，最终决定其是否合理。官方说明：嵌套循环联接嵌套循环联接也称为嵌套迭代，它将一个联接输入用作外部输入表（显示为图形执行计划中的顶端输入），将另一个联接输入用作内部（底端）输入表。外部循环逐行消耗外部输入表。内部循环为每个外部行执行，在内部输入表中搜索匹配行。最简单的情况是，搜索时扫描整个表或索引；这称为单纯嵌套循环联接。如果搜索时使用索引，则称为索引嵌套循环联接。如果将索引生成为查询计划的一部分（并在查询完成后立即将索引破坏），则称为临时索引嵌套循环联接。查询优化器考虑所有这些不同形式。如果外部输入很小而内部输入很大且预先创建了索引，则嵌套循环联接尤其