更深入到非聚集索引:通往SQL Server索引级别2的阶梯

更深入到非聚集索引:通往SQL Server索引级别2的阶梯

大卫·杜兰特，2017/10/18(第一次出版:2014 /11/26)

 

该系列

 

本文是楼梯系列的一部分:SQL Server索引的阶梯

 

索引是数据库设计的基础，并告诉开发人员使用数据库非常了解设计器的意图。不幸的是，当性能问题出现时，索引常常被添加到事后。这里最后是一个简单的系列文章，它应该能让任何数据库专业人员快速“跟上”他们的步伐

在SQL Server索引阶梯上，第1级引入了SQL Server索引，特别是非聚集索引。作为我们的第一个案例研究，在从表中检索单个行时，我们演示了索引的潜在好处。在这个层次上，我们继续研究非聚集索引;检查它们对查询性能的贡献，这些查询的性能远远超过从表中检索单个行。

在我们的大多数层次中，我们引入了少量的理论，研究一些索引内部来帮助解释理论，然后执行一些查询。这些查询被执行，并且没有索引，并且随着性能报告统计数据的打开，我们可以查看索引的影响。

我们将使用在Level 1中使用的AdventureWorks数据库中的表子集，集中在整个级别的联系人表上。我们只使用一个索引，我们在第1级使用的FullName索引来说明我们的观点。为了确保我们控制了联系表上的索引，我们将在dbo模式中生成两个表的副本，并且只在其中一个表上构建FullName索引。这将提供我们所控制的环境:表的两个副本:一个是单独的非聚集索引，一个是没有索引的。

 

注:

在这个楼梯级别显示的所有TSQL代码都可以在本文的底部下载。

 

清单1中的代码制作了Person的副本。联系表，我们可以在任何时候重新运行这批程序，我们希望以“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:复制人的联系表

这里显示了联系人表的一个片段:

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非聚集索引。

CREATE INDEX FullName

 

            ON Contacts_index

 

    ( LastName, FirstName );

 

 

 

清单2.2 -创建非聚集索引

 

请记住，非集群索引存储索引键，以及用于访问表本身实际数据的书签。你可以把书签当作一种指针。未来的水平将更详细地描述书签、它的形式和使用。

这里显示了FullName索引的一个片段，包括LastName和FirstName作为键列，加上书签:

:---      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”(LastName像' S % '),SQL Server可以快速导航到第一个“S”条目(“Sabella,迪安娜”),然后遍历索引,使用书签访问的行,直到到达第一个“T”条目;在这一点上，它知道它已经检索了所有的“S”条目。

如果所有选定的列都在索引中，上述请求将执行得更快。因此,如果我们发布:

SELECT FirstName, LastName 

 

    FROM Contact 

 

WHERE LastName LIKE 'S%';

 

 

 

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

任何从序列数据中获益的SQL操作符都可以从索引中获益。这包括ORDER BY、组,不同的联盟(联盟),并加入…。

例如，如果请求请求以姓进行计数，那么SQL Server可以在第一个条目中开始计数，然后继续执行索引。每当最后一个名称的值发生变化时，SQL服务器输出当前计数并开始一个新的计数。与之前的请求一样，这是一个覆盖的查询;SQL Server仅访问索引，完全忽略表。

请注意键列的左到右顺序的重要性。如果一个人的名字是“阿什顿”，我们的索引非常有用，但是如果要求每个人的名字都是“阿什顿”，那就没有什么帮助了。

 

测试一些示例查询

 

如果您想执行接下来的测试查询，请确保运行脚本以创建新联系人表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，因此我们没有显示统计时间的输出;只有来自于statistics 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:运行覆盖查询时的执行结果

 

测试一个Non-Covered查询

 

接下来，我们修改查询以请求与以前相同的行，但是包括索引中没有的列。查询执行信息见表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:运行未覆盖的聚合查询时执行结果

 

结论

 

我们现在知道，非聚集索引具有以下特性。非聚集索引:

 

是一组有序的条目。

每一行有一个条目。

包含索引键和书签。

是由你。

由SQL Server维护。

由SQL服务器使用，以最小化满足客户端请求所需的工作量。

 

我们已经看到了示例，其中SQL服务器可以仅从索引中满足请求;而有些则完全忽略了指数;还有一些人使用了指数和表的组合。因此，我们通过更新在第1级开始时做的语句来关闭级别2。

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

访问非聚集索引，避免访问表。如果索引包含所有数据，则只有在查询请求的情况下才有可能

使用索引键访问非聚集索引，然后使用选中的书签(s)来访问表的各个行。

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

 

一般来说，第一个是理想的;第二个比第三个好。在即将到来的级别中，我们展示了您可以做些什么来增加您的索引将覆盖您的流行查询的概率，以及如何确定您的非覆盖查询是否足够有选择性地从索引中获益。但这将需要更详细的关于指数内部结构的信息，而不是我们所提出的。

在我们到达那个点之前，我们需要引入另一种类型的SQL Server索引;聚集索引。这就是第3级的主题。

 

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

posted @ 2017-11-08 11:14  我是墩儿  阅读(182)  评论(0)  编辑  收藏  举报