SQL Server 索引

领测软件测试网

　　 
　　我们已讨论了服务器上硬件设备的 I/O 特性。接下来将讨论如何在物理上将 SQL Server 数据和索引结构放置在磁盘驱动器上。将详细介绍这些结构以便将这些知识应用于磁盘 I/O 性能。
　　

　　SQL Server 数据页和索引页都是 8K 字节大。SQL Server 数据页包含除了文本和图像数据以外所有与表的某一行相关的数据。对于文本和图像数据，包含与文本/图像列有关的行的 SQL Server 数据页将包含一个指针，该指针指向一个包含一个或多个 8 KB 页的 B 树结构，文本/图像数据便包含在在该 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 查询，而聚集索引更适合于要求返回许多行的查询。
　　
　　有关详细信息，请在 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 从索引页而不是数据页获取所有需要的数据，因此数据的压缩率更高，使 SQL Server 可以节省磁盘 I/O 操作。
　　覆盖索引按照 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 an Index”。
　　在非聚集索引中，选择性很重要，因为如果在只有少量唯一值的大型表上创建非聚集索引，使用非聚集索引将不会节省数据检索中的 I/O。实际上，使用这种索引产生的 I/O 可能比对表进行连续扫描所产生的 I/O 多得多。比较适合非聚集索引的有发票编号、唯一的客户编号、社会安全号码和电话号码。
　　对于查找或搜索唯一值不是很多的列和列范围的查询

文章来源于领测软件测试网 https://www.ltesting.net/