翻译（八）——更深层次的非聚集索引：通往SQL Server索引的2级阶梯

更深层次的非聚集索引：通往SQL Server索引的2级阶梯

David Durant，2017 / 10 / 18（首次出版：2014 / 11 / 26）

该系列

本文是阶梯系列的一部分：SQL Server索引的阶梯。

索引是数据库设计的基础，并告诉开发人员使用数据库大量关于设计者的意图。不幸的是，当性能问题出现时，索引常常会作为事后考虑添加。这里最后是一系列简单的文章，应该能让数据库专业人员快速地与它们同步。

一般的SQL Server索引楼梯介绍SQL Server索引，1级，和非聚集索引的具体。作为第一个案例研究，我们在检索一个表中的一行时，展示了索引的潜在好处。在这个层面上，我们继续我们的非聚集索引的调查；检查他们的情况下，超越从表中检索单个行良好的查询性能的贡献。

正如我们这些级别中的大多数情况一样，我们引入少量的理论，检查一些索引内部来帮助解释理论，然后执行一些查询。这些查询是在有索引和不带索引的情况下执行的，并且启用了性能报告统计，这样我们就可以查看索引的影响。

我们将使用AdventureWorks数据库中的表的子集，我们在1级的应用，主要集中在联系表在这个水平。我们将只使用一个索引，全称指数，我们在1级的应用，来说明我们的观点。为了确保我们控制在接触表的索引，我们将在dbo架构制作两份表，只有建立这指数对其中。这会给我们的环境控制：两份表：一个与一个单一的非聚集索引和一个没有指标。

注：

所有的TSQL代码在这个楼梯水平显示可在文章底部下载。

清单1中的代码复制了联系人表的副本，我们随时可以重新启动这个批处理，希望从一个“清白的历史”开始。

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：复制此人的表格。联系人表

这里显示了联系人表的一段代码：

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表创建非聚集索引的表。

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的非聚集索引条目有一些内部使用的标题信息，可能包含一些可选的数据值。这些都将被以后的水平；无论是重要的在这个时候对非聚集索引的基本认识。

现在，我们需要知道的是，键值使SQLServer能够找到适当的索引条目；条目的书签值使SQLServer能够访问表中相应的数据行。

索引项顺序的好处

索引的条目按索引键值进行排序，因此SQLServer可以快速遍历两个方向的条目。对已排序的条目的扫描可以从索引的开始、索引的结束或索引中的任何条目开始。

因此，如果一个请求要求所有联系人的姓的开头字母“S”（其中两样的%），SQL Server可以快速定位到第一个“S”条目（“Sabella，迪安娜”），然后遍历索引，通过书签访问行，直到到达第一个“T”进入；在这一点上，它知道它已经挽回了所有的“S”条目。

如果所有选定列都在索引中，则上述请求执行速度会更快。因此，如果我们发出：

SELECT FirstName, LastName 

    FROM Contact 

    WHERE LastName LIKE 'S%';

SQL Server可以快速导航到第一个“s”条目，然后遍历索引条目，忽略书签并直接从索引条目检索数据值，直到到达第一个“T”条目。在关系数据库术语中，索引覆盖了查询。

从排序数据中获益的任何SQL运算符都可以从索引中获益。这包括顺序，分组，不同，联盟（不是全部），并加入。

例如，如果一个请求要求按姓氏计算联系人，SQLServer可以在第一个条目开始计数，并继续索引。每当姓氏的值发生变化时，SQLServer输出当前计数并开始一个新的计数。与前面的请求一样，这是一个覆盖查询；SQLServer只访问索引，完全忽略表。

注意键列的左到右顺序的重要性。我们的索引是非常有用的，如果一个请求要求每个人的姓是“艾什顿”，但它是很少或没有帮助，如果请求是为每个人的名字是“艾什顿”。

测试一些示例查询

如果你想要执行的测试查询，跟进，确保你运行脚本来创建新的联系人表这两个版本，dbo.contacts_index和dbo.contacts_noindex；并运行脚本创建的姓氏，名字dbo.contacts_index指数。

为了验证前一节中的断言，我们打开了我们在第1级使用的相同的性能统计数据，并运行一些查询；带索引和不带索引。

SET STATISTICS io ON
 
SET STATISTICS time ON

由于接触表从AdventureWorks数据库中只有19972行，它将很难获得有意义的值的统计时间。我们的大多数查询将显示CPU时间值为0，因此我们不显示统计时间的输出，仅来自统计IO，它反映了可能要读取的页数。这些值将允许我们从相对意义上比较查询，以确定哪些索引的查询比其他查询性能更好。如果需要更大的表进行更真实的时序测试，则可以使用本文构建一个一百万行版本的联系人表的脚本。下面的所有讨论将假定您使用的是标准的19972行表。

测试覆盖查询

我们的第一个查询是一个将由索引覆盖的查询；一个检索所有名字以s开头的联系人的有限行集。查询执行信息如表2.1所示。

SQL查询语句

SELECT FirstName, LastName
FROM dbo.Contacts  -- execute with both Contacts_noindex and
-- Contacts_index
WHERE LastName LIKE 'S%'

没有索引

(2130行受影响)
表 'Contacts_noindex'.扫描计数1, 逻辑读取 568.

有索引

(2130 行受影响)
表 'Contacts_index'.扫描计数 1, 逻辑读取 14.

索引影响

从 568 读取减少到14 读取

结论

一个查询的索引是一件很好的事情。如果没有索引，整个表将被扫描以查找行。“2130行”统计表明，“S”是一个流行的姓氏的初始字母，发生在所有联系人中的百分之十。

表2.1：运行覆盖查询时的执行结果

测试非覆盖查询

接下来，我们修改查询以请求与以前相同的行，但包括不在索引中的列。查询执行信息如表2.2所示。

SQL查询语句

SELECT *
FROM dbo.Contacts  -- execute with both Contacts_noindex and
-- Contacts_index
WHERE LastName LIKE 'S%'

没有索引

跟之前的查询一样. (因为这也是一张表格的扫描).

有索引

(2130行受影响)
表 'Contact_index'.扫描结果 1行, 逻辑读取568.

索引的影响

一点都没有影响

结论

在执行查询时，从未使用过索引！

SQL Server决定从索引条目跳到表中的对应行2130次（每行一次）比扫描一百万行的整个表要查找它所需的2130行要多的工作。

表2.2：运行非覆盖查询时的执行结果

测试非覆盖查询，但更具选择性

这一次，我们使查询变得更有选择性，也就是说，我们缩小了被请求的行数。这增加了索引对该查询有利的可能性。查询执行信息如表2.3所示。

 

SQL查询语句

SELECT *
FROM dbo.Contacts  -- execute with both Contacts_noindex and
-- Contacts_index
WHERE LastName LIKE 'Ste%'

没有索引

跟之前的查询一样. (因为这也是一张表格的扫描).

有索引

(107行受影响)
Table 'Contact_index'. 扫描总计1行, 逻辑读取111.

索引的影响

从 读取568行到读取111 行

结论

SQL Server访问了107个“%1”条目，所有这些条目都位于索引中。然后将每个条目的书签检索到相应的行。表中没有连续排列的行。指数受益此查询；但不如以后的第一次查询，“覆盖”的查询；特别是在iOS需要检索每一行数。您可能会认为读取107个索引条目加107行需要107 + 107读取。需要111个阅读的原因将在更高的层次上讨论。现在，我们会说很少的读取用于访问索引条目；大多数用于访问行。因为前面的查询请求了2130行，并没有从索引中受益；而这个请求107行的查询确实从索引中受益，您可能也会疑惑“临界点在哪里？”“SQL Server决策背后的计算也将在将来的一个层次上进行讨论。

表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

没有索引

跟之前的查询一样. (因为这也是一张表格的扫描).

有索引

(104行受影响)
Table 'Contacts_index'.扫描总计 1行,逻辑上读取 行4.

索引的影响

从读取568行减少到读取4行

结论

查询所需的所有信息都在索引中，并且在理想序列中的索引中用于计算计数。所有的“姓始于“STE”条目连续在指标；并在该组中，全部为单一名/姓值的条目组合在一起。不需要访问表，也不需要对中间结果进行任何排序。同样，包含查询的索引也是一件好事情。

表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

没有索引

跟之前的查询一样（因为都是扫描一张表）

有索引

(105行受影响)
Table 'ContactLarge'. 扫描一行, 逻辑读取111行.

索引影响

IO从568读取减少到111读取；与前面的非覆盖查询相同

结论

处理查询时所做的中间工作并不总是出现在统计数据中。技术，使用内存或tempdb排序和合并数据的例子。实际上，索引的好处可能比统计数据显示的要大。

表2.5：执行结果运行一个非承保的聚集查询时

结论

现在我们知道，非聚集索引具有以下特点。非聚集索引：是一组有序的条目。

每一行的基础表有一个条目。

包含索引键和书签是你创造的。

由SQLServer维护。

SQL Server用于最小化满足客户请求所需的工作量。

我们已经看到了一些例子，其中SQL Server可以单独满足索引的请求，有些则完全忽略索引；还有一些则使用索引和表的组合。为此，我们通过更新我们在第1级开始时所做的声明，关闭了第2级。

当请求到达数据库时，SQL Server只有三种可能的方法来访问语句请求的数据：

访问只是非聚集索引，避免访问表。只有当索引包含查询所请求的表的所有数据时，才有可能。

使用索引键（S）来访问非聚集索引，然后使用选定的书签（S）来访问表的单个行。

忽略非聚集索引并扫描所请求行的表。

总的来说，第一个是理想的，第二个比第三个更好。在即将到来的级别中，我们将展示您可以做什么来增加索引覆盖您的热门查询的可能性，以及如何确定您的非覆盖查询是否有足够的选择性以从索引中获益。但是，这将需要更详细的信息，内部结构的指标比我们还没有提出。

在达到这一点之前，我们需要介绍另一种类型的SQLServer索引：聚集索引。这是第3级的主题。

原文来源：Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2

http://www.sqlservercentral.com/articles/Stairway+Series/72286/

 

posted @ 2017-11-22 10:20  Angular_JS  阅读(156)  评论(0)  编辑  收藏  举报