SqlServer索引

本文转载自：http://www.alixixi.com/WebData/Subject20/20051222616265.html 我们已讨论了服务器上硬件设备的 I/O 特性。接下来将讨论如何在物理上将 SQL Server 数据和索引结构放置在磁盘驱动器上。将详细介绍这些结构以便将这些知识应用于磁 SQL Server

本文转载自：http://www.alixixi.com/WebData/Subject20/20051222616265.html

我们已讨论了服务器上硬件设备的 I/O 特性。接下来将讨论如何在物理上将 SQL
Server 数据和索引结构放置在磁盘驱动器上。将详细介绍这些结构以便将这些知识应用于磁
　　SQL Server 数据页和索引页都是 8K 字节大。SQL Server 数据页包含除了文本和图像
数据以外所有与表的某一行相关的数据。对于文本和图像数据，包含与文本/图像列有关的行
的 SQL Server 数据页将包含一个指针，该指针指向一个包含一个或多个 8 KB 页的 B 树结
　　SQL Server 索引页仅包含组成特定索引的列中的数据。这意味着与 8 KB 数据页相比，
索引页可以有效地将与更多行相关的信息压缩到一个 8 KB 页。可以想象索引的 I/O 性能可
由此获得改进。如果所提取的列（这些列构成了索引的一部分）占表的行大小的百分比相对较
低，这种推想是对的。当 SQL 查询要求某个表中的一个行集，这些行的某些值与查询中的列
相匹配，SQL Server 可以节省 I/O 操作和时间，因为可以只读取索引页来查找这些值，然
后只访问表中满足查询的所需行，而无须执行 I/O 操作以扫描表中所有行来找到所需行。如
　　有两种 SQL Server 索引，且这两种索引均建立在由 8 KB 索引页所组成的 B 树结构上
。它们的不同在于 B 树结构的底部，其底部在 SQL Server 文档中称为叶级。索引 B 树结
构的上半部分称为非叶级索引。为每个索引建立的 B 树结构都在 SQL Server 表中定义。
　　图 2 说明了非聚集索引和聚集索引在结构上的不同。要记住的关键点是：在非聚集索引
中，叶级节点仅包含参与索引的数据以及快速找到相关数据页上其它行数据的指针。最糟糕的
情况是，从非聚集索引中获得的每一行都要求一个额外的不连续磁盘 I/O 才能检索行数据。
最好的情况是，所需要的行有许多都位于相同的数据页，因此在提取每个数据页时可检索多行
。如果是聚集索引，索引的叶级节点是表的实际数据行。因此，检索表数据时不需要指针跳动
。基于聚集索引的范围扫描执行情况很好，因为聚集索引的叶级（即表的所有行）在物理上按
照组成聚集索引的列顺序排列在磁盘上，因此，可以执行 64 KB 扩展盘区 I/O。并且，如果
聚集索引 B 树（非叶级和叶级）上没有大量分割的页，这些 64 KB I/O 还可以在物理上连
续。虚线表示 B 树结构中还有其它 8 KB 页，但未显示出来。
　　 　　
　　图 2 聚集和非聚集索引 B 树结构
　　聚集索引
　　每个表中只能有一个聚集索引。物理原因很简单。尽管聚集索引 B 树结构的上半部分（
在 SQL Server 文档中通常称为非叶级）与非聚集索引 B 树的结构相似，但是聚集索引 B
树的下半部分是与表相关的实际的 8 KB 数据页。这里暗含着两种性能：
　　通过聚集索引基于关键字搜索来检索 SQL 数据时不需要指针跳动（类似于硬盘上位置的
不连续改变）就可以获得相关的数据页，因为叶级聚集索引已经是相关的数据页。
　　聚集索引的叶级按照组成聚集索引的列排序。因为聚集索引的叶级包含表的实际 8 KB 数
据页，这意味着整个表的行数据在物理上按照聚集索引确定的顺序排列在磁盘驱动器上。当根
据聚集索引的值从这个表中提取大量行时，这种排列提供了一种潜在的 I/O 性能优势，因为
使用的是连续磁盘 I/O（除非该表上发生了页拆分，我们将在后面的“FILLFACTOR 和
PAD_INDEX 的重要性”中讨论这个问题）。这就是根据执行范围扫描检索大量的行时所使用的
　　非聚集索引
　　如果要根据键值从大型 SQL Server 表提取具有良好选择性的少数几行，非聚集索引最有
用。以前已提到过，非聚集索引是由 8 KB 索引页组成的 B 树。索引页的 B 树的底部或叶级
包含组成该索引的列中的所有数据。当用非聚集索引检索表中与键值匹配的信息时，将搜索整
个索引 B 树，直到在索引叶级找到一个与键值匹配的值。如果需要的列不是索引组成的一部
分，则会发生指针跳动。该指针跳动可能需要在磁盘上进行一个不连续 I/O 操作。如果表以
及它相应的索引 B 树很大，甚至可能要求从另一个磁盘中读取数据。如果多个指针指向同一
个 8 KB 数据页，则对 I/O 性能的影响比较小，因为只须将该数据页读入数据高速缓存一次
。如果某个 SQL 查询涉及到要用非聚集索引进行搜索，那么对于所返回的每一行，均需要一
次指针跳动。这些指针跳动可以解释为什么非聚集索引更适合于只返回表中一行或几行的
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“nonclustered index”
　　覆盖索引
　　非聚集索引的一个特例是覆盖索引。覆盖索引的定义是在选择条件和 WHERE 谓词上均满
足 SQL 查询的所有列的基础上建立的非聚集索引。覆盖索引可以节省大量的 I/O，因此可极
大地改善查询的性能。但是有必要在新建索引（以及与它相关的 B 树索引结构维护）所需要
的代价和覆盖索引所带来的 I/O 性能增益之间进行权衡。如果覆盖索引对于 SQL Server 上
经常运行的查询或查询组极其有利，那么创建覆盖索引是值得的。
　　覆盖索引的示例
　　Select col1,col3 from table1 where col2 = 'value'.
　　Create index indexname1 on table1(col2,col1,col3).
　　或者
　　使用 SQL Server Enterprise Manager 中的 Create Index Wizard 创建索引。从 SQL
Server Enterprise Manager 菜单栏中选择 Tools/Wizards，左击 Database 旁边的 + 图标
将出现数据库向导，然后双击 Create Index Wizard 启动该向导。
　　本例中创建出来的索引“indexname1”是一个覆盖索引，因为它包括 SELECT 语句和
WHERE 谓词中的所有列。即在执行此查询期间，SQL Server 不需要访问与 table1 相关的数
据页。SQL Server 使用索引 indexname1 可以获得满足查询所需要的全部信息。在 SQL
Server 已遍历与 indexname1 相关的 B 树，并找到 col2 等于“value”的索引关键字范围
，SQL Server 就知道它可以从覆盖索引的叶级（底层）提取所有需要的数据
(col1,col2,col3)。这从两个方面改进了 I/O 性能：
　　SQL Server 从索引页而不是数据页获取所有需要的数据，因此数据的压缩率更高，使
　　覆盖索引按照 col2 将所有需要的数据以物理方式组织在磁盘上。使硬盘得以连续返回与
where 谓词 (col2 = "value") 相关的所有索引行。从而为我们提供了更好的 I/O 性能。
实际上，从磁盘 I/O 的角度来看，覆盖索引成了此查询以及可被此覆盖查询中的列完全满足
　　总而言之，如果覆盖索引中的所有列的字节数比该表中单行的字节数少，并且可以肯定将
反复执行使用此覆盖索引的查询，那么使用覆盖索引是有意义的。但是在创建大量覆盖索引之
前，请考虑下面的内容，它讲述了 SQL Server 7.0 如何智能、自动、快捷地为查询创建覆盖
　　自动覆盖索引或隐蔽查询
　　SQL Server 7.0 的新查询处理器提供索引交叉功能。索引交叉功能使查询处理器得以考
察某个特定表中的多个索引，根据这些索引建立哈希表以及使用哈希表减少特定查询的 I/O。
从索引交叉得来的哈希表实际上已成为覆盖索引，提供与覆盖索引相同的 I/O 性能优势。对
于很难预先确定要对数据库执行哪些查询的数据库用户环境，索引交叉可提供更大的灵活性。
这种情况下一种好的策略是在所有将被频繁查询的列中定义单列、非聚集索引，并使索引交叉
　　有关详细信息，请在 SQL Server Books Online 中搜索字符串“query tuning
recommendations”和“designing an index”。
　　以下是利用索引交叉的示例
　　Select col3 from table1 where col2 = 'value'

　　Create index indexname1 on table1(col2)

　　Create index indexname2 on table1(col3)
　　或者
　　使用 SQL Server Enterprise Manager 中的 Create Index Wizard 创建索引。从 SQL
Server Enterprise Manager 菜单栏中选择 Tools/Wizards，左击 Database 旁边的 + 图标
将出现数据库向导，然后双击 Create Index Wizard 启动该向导。
　　在前面的例子中，“indexname1”和“indexname2”是在 SQL Server 表“table1”上创
建的非聚集和单列索引。当执行此查询时，查询处理器可识别出使用这两个索引的索引交叉在
哪些情形中比较有利。Query optimizer 将自动将两个索引哈希在一起以节省查询执行中的
I/O。此过程无需查询提示。覆盖索引（无论是明确声明为覆盖索引还是声明为索引交叉）所
　　索引选择
　　如何选择索引可显著影响所产生的磁盘 I/O，并因而影响查询性能。上一节已解释了非聚
集索引适合少量行的检索、聚集索引适合范围扫描的原因。以下是一些补充信息：
　　使索引尽可能紧凑（列数和字节数最少）。对于聚集索引尤其应如此，因为非聚集索引将
使用聚集索引作为它定位行数据的方法。有关详细信息，请在 SQL Server Books Online 中
搜索字符串“using clustered indexes”、“Index Tuning Recommendations”和“Design
　　在非聚集索引中，选择性很重要，因为如果在只有少量唯一值的大型表上创建非聚集索引
，使用非聚集索引将不会节省数据检索中的 I/O。实际上，使用这种索引产生的 I/O 可能比
对表进行连续扫描所产生的 I/O 多得多。比较适合非聚集索引的有发票编号、唯一的客户编
　　对于查找或搜索唯一值不是很多的列和列范围的查询，聚集索引比非聚集索引要好得多，
因为聚集索引在物理上按顺序排列表数据，从而可以进行键值的连续 64 KB I/O。比较适合聚
集索引的有州/省、分公司、销售日期、邮政编码和客户地区。在只有唯一值的列上定义聚集
索引是一种浪费，除非系统中的典型查询提取大型的连续范围的唯一值。当试图提取每个表中
的最佳列以创建聚集索引时，要询问的关键问题是：“是否有许多查询需要根据该列的顺序来
提取大量的行？”答案因每个用户环境的不同而不同。某个公司可能根据日期范围进行大量查
询，而另一公司可能根据银行分行的范围来进行大量查询。
　　可从聚集索引中受益的 WHERE 谓词的示例：
　　WHERE > some_value
　　WHERE BETWEEN some_value AND some_value
　　WHERE some_value
　　聚集索引的选择、连续键数据以及如何查找热点
　　选择聚集索引实际上需要确定两项主要内容；首先，从为范围扫描提供连续 I/O 的角度
，确定表中的哪一列将从聚集索引中获益最多。然后，在避免热点的同时使用聚集索引来影响
表数据的物理位置。当在硬盘上放置数据，造成许多查询试图同时在磁盘的同一区域读或写数
据时容易发生热点。产生磁盘 I/O 瓶颈，因为硬盘接收的并发磁盘 I/O 请求超过了它所能
处理的数量。解决方案是停止从该磁盘中提取这么多数据，或者将数据分散在多个磁盘上以支
持 I/O 需求。如果要在数百或数千个 SQL Server 用户中获得良好的并发数据存取性能，则
这种关于数据的物理位置的考虑至关重要。
　　这两方面的决定经常相互冲突，最佳的总体考虑是在这二者之间找到一种平衡。在较高的
用户负荷环境中，改进的并发性（通过避免热点来实现）经常比在该列中设置聚集索引以获得
　　在以前的 SQL Server 版本中，对于没有聚集索引的表（这样的表通常称为堆集表），插
入的行总是放置在磁盘中表的物理结尾处。这将使得十分繁忙的表的末尾有可能产生热点。
SQL Server 7.0 的新存储管理算法提供了可用空间管理，从而避免了这一行为。现在当在堆
集表中插入行时，SQL Server 利用 PFS 页可以在表中快速找到可以在其中插入行的可用空间
。PFS 页指明整个表中的可用空间。它恢复已删除的空间，并避免插入热点，因为插入被分散
在整个表的物理磁盘空间中。可用磁盘管理影响聚集索引的选择。因为聚集索引影响数据的物
理位置，所以当在物理上基于某一列排列聚集索引，且该列中的最高列值处发生许多并发插入
时，则将有可能因将数据放置在同一个物理磁盘位置上而发生热点。对于值单调递增的列，应
注意该列的聚集索引如何按照该列在磁盘上连续排列数据行，并记住将聚集索引放置在另一列
或者不在表中包括聚集索引，此连续数据排列不会变为另一列或根本就不会发生。
　　以下是一个常用的方案以帮助说明如何选择聚集索引。假设某个表包含一个发票日期列、
一个唯一的发票编号列和其它数据。假设每天大约有 10,000 个新的记录要插入到这个表，而
SQL 查询经常需要搜索这个表中的所有记录以找到一个星期的数据，且有许多用户需要并发
存取这个表。不适合在发票编号上设置聚集索引的原因有以下几点：首先，发票编号是唯一值
，且用户不会按发票编号的范围进行搜索，因此物理上将发票编号连续放置在磁盘上对搜索不
会有什么帮助，因为不可能进行发票编号的范围扫描。第二，发票编号的值很可能是单调递增
（如 1001、1002、1003 等等）。如果在发票编号上设置聚集索引，所有新行均将插入到表的
末尾（最大的发票编号的旁边），因此，将在同一个物理磁盘位置上产生热点。
　　接下来，考察发票日期列。要获得最大的连续 I/O，可在发票日期上设置聚集索引，因为
用户经常搜索一个星期的数据（大约有 70,000 行）。但是从并发的角度来看，发票日期可能
不适合用于聚集索引。如果在发票日期上设置聚集索引，日期的特性决定了所有的数据都将插
入到表的末尾，因此表的末尾所在的硬盘有可能发生热点。请注意插入发生在表的末尾这样一
个事实可能会与在同一天插入 10,000 行这样一个事实有所抵消，因此发票日期发生热点的可
能性比发票编号要小。而且，硬件 RAID 控制器可以帮助将 10,000 行分散在多个磁盘上，这
　　对于以上情况没有十全十美的解决方案。可能有必要确定是否值得承担发生热点的风险，
如果值得，则可以在发票日期中设置聚集索引，以加速涉及到发票日期范围的查询。如果是这
种情况，需仔细监视与该表相关的磁盘上的磁盘队列，并记住插入在到达表的末尾时可能需要
依次排队。我建议在这种情况下，在发票日期中定义聚集索引，因为具有可基于发票日期进行
范围扫描这一优势，且发票编号在磁盘上的物理位置不连续。
　　让我们考察另一个让人感觉愉快一些的例子。假设某个表由发票编号、发票日期、发票金
额、销售办公室和其它数据组成。假设每天要在这个表中插入 10,000 个记录。且用户经常根
据销售办公室查询发票金额。因此，应该在销售办公室这一列中设置聚集索引，因为可根据它
进行范围扫描。并且因为新插入的行可能包含不同的销售办公室，因此插入将均匀分散在整个
　　在某些情况下，范围扫描可能不是最重要的问题。假设某个超大型的职员表有职员编号、
社会安全编号和其它数据。当插入行时，职员编号就递增。我们假设这个表每天有 100,000
个检索，并且每个检索都是根据社会安全编号提取一个记录。在社会安全编号中创建非聚集索
引可以为这种情况提供极好的查询性能。在社会安全编号中创建聚集索引可能会提供比非聚集
索引稍好的查询性能，但是因为没有涉及范围扫描，所以可能有些多余。本例中的问题是是否
在这个表中定义聚集索引。在以前的 SQL Server 版本中，很重要的一点就是即便查询不要求
使用聚集索引，也总要在表中定义聚集索引，因为这样有助于删除行空间的恢复。有了 SQL
Server 7.0 新的空间分配算法和存储结构，这已不再是问题。本例中我们建议您在社会安全
编号中创建聚集索引。对于数据的分布不遵循职员编号连续模式且社会安全编号分布十分均匀
的表，该方法非常适合查找该表中的某一列。如果在这种均匀分布的列数据中创建聚集索引，
职员记录将均匀地分布在磁盘上。这种分布以及将在下一节讨论的 FILLFACTOR 和
PAD_INDEX 将在整个表中提供开放数据页区域以插入数据。假定新插入职员记录的社会安全编
号均匀分布，职员表将均匀填充，从而避免发生页拆分。如果某个表中没有均匀分布的列，就
有必要在这个表中创建整型列，并用均匀分布的值填充这一列，以便在这一列中创建聚集索引
。查询中没有使用定义了聚集索引的“填充”列或“多余”列，但是使用该列可在磁盘中均匀
分布数据 I/O，以改进表的存取并发和总体 I/O 性能。这对于存取频繁的大型 SQL 表是一
种非常有效的方法。本例中另一种可能的解决方案是不在这个表中创建聚集索引。本例中，
SQL Server 7.0 对空间管理的各个方面进行管理。SQL Server 查找可用空间以插入行，重
复使用已删除行的空间，而且在有意义时，自动重新组织磁盘上数据页的物理顺序（以产生更
多的连续 I/O）。数据页的重新组织发生在数据库文件的自动收缩操作过程中。有关详细信息
，请在 SQL Server Books Online 中搜索字符串“Managing Space Used by Objects”和“
　　考虑热点的另一种方法是在选择环境中进行考虑。如果许多用户正在用十分接近但实际上
彼此都不在同一行的键值选择数据，大多数磁盘 I/O 活动将发生在磁盘 I/O 子系统的相同
物理区域中。假如某一列可将键值均匀分布在磁盘中，那么在这一列中为表定义聚集索引，可
以使磁盘 I/O 活动的分布更加均匀。如果所有的选择使用的都是同一个唯一键值，则使用聚
集索引将不会帮助平衡这个表的磁盘 I/O 活动。使用 RAID（硬件或软件）可以通过将 I/O
分布在多个磁盘驱动器中帮助解决这一问题。可以将此处所介绍的这类行为看成磁盘存取争用
　　FILLFACTOR 和 PAD_INDEX 的重要性
　　如果 SQL Server 数据库要经历大量的插入活动，那么很重要的一点是进行计划，以便在
索引页和数据页上提供和维持开放空间，防止出现页拆分。当某个索引页或数据页不再能容纳
任何新的行，但由于该页中所定义的数据的逻辑顺序需要插入一行时，便会发生页拆分。发生
页拆分时，SQL Server 需要分割整页中的数据，并将大约一半数据移动到新的页，以使这两
页均有一些开放空间。这会消耗一些系统资源和时间。
　　当最初生成索引时，SQL Server 将索引 B 树结构放置在连续的物理页上，以便通过连续
I/O 扫描索引页获取最佳 I/O 性能。当由于发生页拆分，需要将新的页插入索引的逻辑 B
树结构时，SQL Server 必须分配新的 8 KB 索引页。这种插入发生在硬盘上的其它位置，从
而打断了索引页的物理连续特性。它使 I/O 操作从连续变为不连续，从而使得性能减低一半
。可以通过重建索引页以恢复索引页的物理连续顺序来解决过多的页拆分。聚集索引的叶级也
　　请在 Performance Monitor 中关注“SQL Server: Access Methods - Page Splits”。
此计数器中的非零值表示发生了页拆分，应用 DBCC SHOWCONTIG 进一步分析。有关如何使用
此命令的细节，请在 SQL Server Books Online 中搜索“DBCC SHOWCONTIG”。
　　DBCC SHOWCONTIG 是非常有用的命令，可用它来检查表中是否发生了过多的页拆分。
Scan Density 是 DBCC SHOWCONTIG 所提供的关键指示器。这个值越接近 100% 越好。如果这
个值低于 100%，使用 DROP_EXISTING 选项在该表上重建聚集索引以便对这个表进行整理。
CREATE INDEX 语句的 DROP_EXISTING 选项允许重建已有的索引，提供比先除去索引然后重建
索引更好的重建性能。有关详细信息，请在 SQL Server Books Online 中搜索字符串“
　　CREATE INDEX 和 DBCC REINDEX 命令中的 FILLFACTOR 选项提供一种方法，以指定在索
引页和数据页中要留出的开放空间的百分比。CREATE INDEX 的 PAD_INDEX 选项在非叶级索引
页中应用为 FILLFACTOR 所指定的百分比。如果没有 PAD_INDEX 选项，FILLFACTOR 将主要影
响聚集索引的叶级索引页。将 PAD_INDEX 选项与 FILLFACTOR 一起使用是一个不错的主意。
有关详细信息，请在 SQL Server Books Online 中搜索“page split”和“pad_index”。
　　为 FILLFACTOR 指定的最佳值取决于给定时间帧内插入一个 8 KB 索引页和数据页中的新
数据量。要记住的重要一点是，SQL Server 索引页中所包含的行通常比数据页要多得多，因
为索引页只包含与索引相关的列的数据，而数据页则包含整行数据。同时还需记住多久出现一
次维护窗口，以重建索引避免产生页拆分。仅当大多数索引页和数据页已填满数据时才开始重
建索引。要使得这种情况得以发生，需要为给定的表正确选择聚集索引。如果聚集索引均匀分
布数据，以至新的行插入与该表相关的所有数据页中，数据页将均匀填充。总而言之，这将延
缓页拆分的发生，并且它是重建聚集索引所必需的条件。需要确定的另一方面是 FILLFACTOR
，其选择一部分根据预计将在给定时间帧内插入 8 KB 页的键范围内的行数，以及系统中预定
　　在这种情况下也需要进行抉择，以便找到一种既在页中留出足够的开放空间又适当产生页
拆分的性能折衷方案。如果为 FILLFACTOR 指定一个小的百分比，它将在索引页和数据页中留
出大量的开放空间。这有助于避免产生页拆分，但是却无法通过将数据压缩到页中改善性能。
索引页和数据页上的压缩数据越多，SQL Server 运行越快，因为如果数据页和索引页上的压
缩数据越多，则通常情况下可以用更少的页和 I/O 操作提取更多的数据。将 FILLFACTOR 指
定得过高会使页中留出的开放空间过少，使得页溢出的速度过快，从而引起页拆分。
　　在使用 FILLFACTOR 和 PAD_INDEX 之前，需记住读操作往往大大超过写操作，即便在联
机事务处理 (OLTP) 系统中也是如此。使用 FILLFACTOR 将减慢所有的读操作，因为它使表更
加分散（减少了数据压缩）。比较好的方法是在使用 FILLFACTOR 和 PAD_INDEX 之前，使用
Performance Monitor 将 SQL Server 的读操作和写操作进行比较，仅当写操作占了读操作的
相当大一部分（也就是说超过 30%）的时候使用这些选项。
　　如果写操作占了读操作的相当大一部分，那么在十分繁忙的 OLTP 系统中，最佳的方法是
尽可能地指定一个高的 FILLFACTOR，这将在每个 8 KB 页中留出最少量的可用空间，但仍然
能防止发生页拆分，并可使 SQL Server 到达下一个可用时间窗口重建索引。这种方法既获得
了 I/O 性能（尽可能地使页填满）又避免了页拆分（不让页溢出）。这可能需要进行一些实
验，如用不同的 FILLFACTOR 值重建索引，然后模拟表中的负载活动以验证最佳的
FILLFACTOR 值。在确定了最佳的 FILLFACTOR 值后，可使预定的索引重建任务像 SQL
Server Task 一样自动执行。有关使任务自动进行的详细信息，请在 SQL Server Books
Online 中搜索字符串“creating a task”。
　　如果 SQL Server 数据库中没有写活动，可以将 FILLFACTOR 设为 100%，以便完全填满
所有的索引页和数据页，从而获得最佳的 I/