深入到非聚集索引：到SQL Server索引的阶梯2级

作者David Durant，2017/10/18（第一版：2014/11/26）

原文链接: http://www.sqlservercentral.com/articles/Stairway+Series/72286/ 该系列 本文是“Stairway系列：SQL Server索引的阶梯”的一部分 索引是数据库设计的基础，并告诉开发人员使用数据库关于设计者的意图。不幸的是，当性能问题出现时，索引往往被添加为事后考虑。这里最后是一个简单的系列文章，应该使他们快速地使任何数据库专业人员“快速” SQL Server索引阶段1中的级别1通常引入了SQL Server索引，特别引入了非聚簇索引。作为我们的第一个案例研究，我们演示了从表中检索单个行时索引的潜在好处。在这个层面上，我们继续调查非集群指标。在超出从表中检索单个行的情况下，检查他们对良好查询性能的贡献。 就像大多数这些层面的情况一样，我们引入少量的理论，检查一些索引内部的内容来帮助解释理论，然后执行一些查询。这些查询是在没有索引的情况下执行的，并且打开了性能报告统计信息，以便查看索引的影响。 我们将使用我们在Level 1中使用的AdventureWorks数据库中的表的子集，集中在整个级别的Contact表。我们将只使用一个索引，即我们在1级中使用的FullName索引来说明我们的观点。为了确保我们控制Contact表上的索引，我们将在dbo模式中创建表的两个副本，并仅在其中一个上创建FullName索引。这将给我们我们的受控环境：表的两个副本：一个具有单个非聚集索引，另一个没有任何索引。

注意： 在这个楼梯级别显示的所有TSQL代码可以在文章底部下载。 清单1中的代码创建了Person.Contact表的副本，我们可以在我们希望以“clean slate”开始的任何时候重新运行这个批处理。

IF EXISTS (

 

    SELECT *

 

        FROM sys.tables 

 

        WHERE OBJECT_ID = OBJECT_ID('dbo.Contacts_index'))

DROP TABLE dbo.Contacts_index;

GO

IF EXISTS (

 

    SELECT *

 

        FROM sys.tables 

 

        WHERE OBJECT_ID = OBJECT_ID('dbo.Contacts_noindex'))

 

    DROP TABLE dbo.Contacts_noindex;

GO

SELECT * INTO dbo.Contacts_index

 

    FROM Person.Contact;

SELECT * INTO dbo.Contacts_noindex

 

    FROM Person.Contact;

清单2.1：制作Person.Contact表的副本联系人表格的一个片段显示在这里：

ContactID FirstName MiddleName LastName  EmailAddress

                                 1288   Laura  F   Norman    laura1@adventure-works.com 651   Michael     Patten    michael20@adventure-works.com 1652   Isabella  R  James     isabella6@adventure-works.com 1015  David  R   Campbell    david8@adventure-works.com 1379  Balagane     Swaminath    balaganesan0@adventure-works.c 742   Steve      Schmidt    steve3@adventure-works.com 1743  Shannon  C   Guo      shannon16@adventure-works.com 1106  John   Y    Chen     john2@adventure-works.com 1470   Blaine        Dockter    blaine1@adventure-works.com 833  Clarence   R.  Tatman    clarence0@adventure-works.com 1834  Heather  M   Wu      heather6@adventure-works.com 1197  Denise  H   Smith     denise0@adventure-works.com 560  Jennifer  J.   Maxham    jennifer1@adventure-works.com 1561  Ido        Ben-Sacha     ido1@adventure-works.com 924  Becky   R.   Waters    becky0@adventure-works.com

非聚集索引条目 以下语句在Contacts_index表上创建我们的FullName非聚簇索引。

Contacts_index table.

CREATE INDEX FullName

 

            ON Contacts_index

 

    ( LastName, FirstName );

清单2.2 - 创建一个非聚集索引请记住，非聚簇索引按顺序存储索引键，以及用于访问表中实际数据的书签。 您可以将书签看作一种指针。 未来的层次将更详细地描述书签，其形式和使用。 这里显示FullName索引的片段，包括姓氏和名字作为键列，加上书签：

:---      Search Key Columns   :         Bookmark

                                   

Russell      Zachary                  =>   Ruth        Andy                    =>   Ruth        Andy                   =>   Ryan        David                   =>   Ryan       Justin                  =>   Sabella      Deanna                  =>   Sackstede     Lane                   =>   Sackstede     Lane                   =>   Saddow        Peter                  =>   Sai          Cindy                  =>   Sai          Kaitlin                 =>   Sai          Manuel                  =>   Salah         Tamer                  =>   Salanki        Ajay                   =>   Salavaria       Sharon                  =>  

每个条目都包含索引键列和书签值。另外，SQL Server非聚簇索引条目具有一些仅供内部使用的头信息，并可能包含一些可选的数据值。这两个都将在后面的层面进行讨论。在这个时候，对非基本指标的基本理解也不重要。 现在，我们只需要知道键值就能使SQL Server找到合适的索引条目;并且该条目的书签值使SQL Server能够访问表中相应的数据行。 索引条目的好处是在顺序 索引的条目按索引键值进行排序，所以SQL Server可以在任一方向上快速遍历条目。顺序条目的扫描可以从索引的开始，索引的结尾或索引内的任何条目开始。 因此，如果一个请求要求所有以姓氏字母“S”开头的联系人（WHERE LastName LIKE'S％'），SQL Server可以快速导航到第一个“S”项（“Sabella，Deanna”），然后遍历索引，使用书签访问行，直到到达第一个“T”条目;在这一点上它知道它已经检索了所有的“S”条目。 如果所有选定的列都在索引中，上面的请求会更快地执行。因此，如果我们发出：

SELECT FirstName, LastName

 

    FROM Contact

 

    WHERE LastName LIKE 'S%';

SQL Server可以快速导航到第一个“S”条目，然后遍历索引条目，忽略书签并直接从索引条目检索数据值，直到达到第一个“T”条目。在关系数据库术语中，索引已经“覆盖”了查询。 从序列数据中受益的任何SQL操作符都可以从索引中受益。这包括ORDER BY，GROUP BY，DISTINCT，UNION（不是UNION ALL）和JOIN ... ON。 例如，如果一个请求通过姓氏询问联系人的数量，SQL Server可以从第一个条目开始计数，然后沿索引继续。每次更改姓氏的值时，SQL Server都会输出当前计数并开始新的计数。与之前的请求一样，这是一个覆盖查询; SQL Server只访问索引，完全忽略表。 请注意按键列从左到右的顺序的重要性。如果一个请求询问所有姓“Ashton”的人，我们的索引是非常有用的，但是如果这个请求是针对所有名字是“Ashton”的人，那么这个索引几乎没有任何帮助。 测试一些样本查询 如果要执行后续的测试查询，请确保运行脚本以创建新的联系人表的两个版本：dbo.Contacts_index和dbo.Contacts_noindex;并运行该脚本以在dbo.Contacts_index上创建LastName，FirstName索引。 为了验证上一节中的断言，我们打开了在1级中使用的相同性能统计信息，并运行一些查询;有和没有索引。

SET STATISTICS io ON

SET STATISTICS time ON

由于AdventureWorks数据库中的Contacts表中只有19972行，所以很难获得有意义的统计时间值。 我们大多数的查询会显示一个CPU时间值为0，所以我们不显示统计时间的输出; 只从统计数据IO中反映出可能需要读取的页数。 这些值将允许我们在相对意义上比较查询，以确定哪些查询具有哪些索引比其他索引执行得更好。 如果您想要更大的表进行更加实际的计时测试，则可以使用本文提供的构建百万行版本的Contact表的脚本。 接下来的所有讨论都假设你使用的是标准的19972行表。 测试涵盖的查询 我们的第一个查询是一个将被索引覆盖的查询; 一个为所有姓氏以“S”开头的联系人检索一组有限的列。 查询执行信息如表2.1所示。

SQL	SELECT FirstName, LastName FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'S%'
Without Index	(2130 row(s) affected) Table 'Contacts_noindex'. Scan count 1, logical reads 568.
With Index	(2130 row(s) affected) Table 'Contacts_index'. Scan count 1, logical reads 14.
Index Impact	IO reduced from 568 reads to 14 reads.
Comments	An index that covers the query is a good thing to have. Without an index, the entire table is scanned to find the rows. The “2130 rows” statistic indicates that “S” is a popular initial letter for last names, occurring in ten percent of all contacts.

表2.1：运行覆盖查询时的执行结果 测试一个不包含的查询 接下来，我们修改我们的查询以请求与之前相同的行，但包括不在索引中的列。 查询执行信息见表2.2。

SQL	SELECT * FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'S%'
Without Index	Same as previous query. (Because it is a table scan).
With Index	(2130 row(s) affected) Table 'Contact_index'. Scan count 1, logical reads 568.
Index Impact	No impact at all.
Comments	The index was never used during the execution of the query! SQL Server decided that jumping from an index entry to the corresponding row in the table 2130  times (once for each row) was more work than scanning the entire table of one million rows to find the 2130  rows that it needed.

表2.2：运行非覆盖查询时的执行结果 测试一个不包含的查询，但更有选择性 这一次，我们使我们的查询更具选择性; 也就是说，我们缩小了被请求的行数。 这增加了索引对该查询有利的可能性。 查询执行信息如表2.3所示。

SQL	SELECT * FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'Ste%'
Without Index	Same as previous query. (Because it is a table scan).
With Index	(107 row(s) affected) Table 'Contact_index'. Scan count 1, logical reads 111.
Index Impact	IO reduced from 568 reads to 111 reads..
Comments	SQL Server accessed the 107 “Ste%” entries, all of which are located consecutively within the index.  Each entry’s bookmark was then used to retrieve to corresponding row.  The rows are not located consecutively within the table. The index benefitted this query; but not as much as it benefitted the first query, the “covered” query; especially in terms of number of IOs required to retrieve each row.  You might expect that reading 107 index entries plus 107 rows would require 107 + 107 reads.  The reason why only 111 reads were required will be covered at a higher level.  For now, we will say that very few of the reads were used to access the index entries; most were used to access the rows. Since the previous query, which requested 2130 rows, did not benefit from the index; and this query, which requested 107 rows, did benefit from the index - you might also wonder “where does the tipping point lie?”  The calculations behind SQL Server’s decision also will be covered in a future level.

表2.3：运行更具选择性的非覆盖查询时的执行结果 测试涵盖的聚合查询 我们最后一个示例查询将是一个聚合查询; 这是一个涉及计数，合计，平均等的查询。 在这种情况下，这是一个查询，告诉我们在联系人表中名称重复的程度。 结果部分看起来像这样：

Steel         Merrill             1 Steele        Joan                1 Steele        Laura               2 Steelman      Shanay              1 Steen         Heidi               2 Stefani       Stefano             1 Steiner       Alan                1 查询执行信息见表2.4。

SQL	SELECT LastName, FirstName, COUNT(*) as 'Contacts' FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'Ste%' GROUP BY LastName, FirstName
Without Index	Same as previous query. (Because it is a table scan).
With Index	(104 row(s) affected) Table 'Contacts_index'. Scan count 1, logical reads 4.
Index Impact	IO reduced from 568 reads to 4 reads.
Comments	All the information needed by the query is in the index; and it is in the index in the ideal sequence for calculating the counts.  All the “last name begins with ‘Ste’” entries are consecutive within the index; and within that group, all the entries for a single FirstName / LastName value are grouped together. No accessing of the table was required; nor was any sorting of intermediate results needed.  Again, an index that covers the query is a good thing to have.

表2.4：运行覆盖聚合查询时的执行结果 测试未覆盖的聚合查询 如果我们改变查询来包含不在索引中的列，我们可以得到我们在表2.5中看到的性能结果。

SQL	SELECT LastName, FirstName, MiddleName, COUNT(*) as 'Contacts' FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'Ste%' GROUP BY LastName, FirstName, MiddleName
Without Index	Same as previous query. (Because it is a table scan).
With Index	(105 row(s) affected) Table 'ContactLarge'. Scan count 1, logical reads 111.
Index Impact	IO reduced from 568 reads to 111 reads; same as the previous non-covered query
Comments	Intermediate work done while processing the query does not always appear in the statistics. Techniques that use memory or tempdb to sort and merge data are examples of this.  In reality, the benefit of an index may be greater than that shown by the statistics.

• 表2.5：运行非覆盖聚合查询时的执行结果 结论我们现在知道非聚集索引具有以下特征。非聚集索引： •是一组有序的条目。 •每行基础表中有一个条目。 包含一个索引键和一个书签。 •由您创建。 •由SQL Server维护。 •SQL Server使用它来尽量减少满足客户端请求所需的工作量。 我们已经看到了SQL Server可以单独满足索引请求的例子。有些则完全忽略了指标。还有一些使用了索引和表格的组合。为此，我们通过更新在第一级开始时的陈述来关闭第二级。 当请求到达您的数据库时，SQL Server只有三种可能的方式来访问该语句所请求的数据： •只访问非聚簇索引并避免访问表。这只能在索引包含查询请求的所有数据的情况下才有可能 •使用索引键访问非聚簇索引，然后使用选定的书签访问表的各个行。 •忽略非聚簇索引并扫描表中的请求行。 一般来说，第一个是理想的;第二个比第三个好。在即将到来的级别中，我们将展示如何提高索引覆盖广受欢迎的查询的可能性，以及如何确定您的非覆盖查询是否具有足够的选择性以从您的索引中受益。但是，这将需要比我们尚未提出的更详细的索引内部结构信息。 在我们达到这一点之前，我们需要介绍另一种SQL Server索引;聚集索引。这是3级的主题。 可下载的代码 •NonClustered.SQL   资源： Level 2 - NonClustered.sql | Level2_MillionRowContactTable.sql 本文是SQL Server索引阶梯的一部分