【翻译】SQL Server索引进阶：第二级，深入非聚集索引

zhoujun.g

　　原文地址：
　　Stairway to SQL Server Indexes: Level 2, Deeper into Nonclustered Indexes
　　本文是SQL Server索引进阶系列（Stairway to SQL Server Indexes）的一部分。
　　在第一级中介绍了SQL Server中的非聚集索引。而且在第一个学习的例子中，我们证明了在从表中获取一行数据的情况下，索引带来的潜在的好处。在这一级中，我们继续介绍非聚集索引，看看他们在提升查询性能中做出的贡献。
　　

　　
我们先来介绍一些理论，了解一些索引的内部信息，帮助我们解释理论，然后执行一些查询。这些查询会在包含和不包含索引的两种情况被执行，开启性能报告，我们可以看到索引产生的影响。
　　

　　
我们继续使用AdventureWorks 数据库的部分表，主要集中在Contact表。我们将只是用一个索引，在上一级中使用的FullName索引，来证明我们的观点。为了确保我们很好的控制Contact表的索引，我们将做两份拷贝，一份建立FullName索引，一份不建立索引。
　　

　　

　　

• 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;
  

　　

　　

　　
非聚集索引
　　

　　
在Contacts_index 表建立非聚集索引
　　

　　

　　

　　

• CREATE INDEX FullName
  
• 
  
•         ON Contacts_index
  
• 
  
• ( LastName, FirstName );
  

　　

　　

　　
请记住，非聚集索引顺序存储索引键，通过标记来访问表中真正的数据。你可以把标签看做一种指针。将来的级别中会描述标签的格式，标签的用法，标签的细节。
　　

　　
另外，SQL Server的非聚集索引的入口还有一些内部使用的头信息，还有一些可选的数据值。这些在后面的文章中都会有介绍，现在都不是重点内容。
　　

　　
到目前为止，我们只需要知道，键使得SQL Server找到合适的索引入口，入口的标签使得SQL Server访问表对应的行数据。
　　

　　
索引入口有序的好处
　　

　　
索引的入口是有序的，因此SQL Server可以快速的定位入口。扫描可以从头部开始，可以从尾部开始，也可以从中间开始。
　　

　　
因此，如果一个查询，请求所有LastName以S开头的Contact用户（where lastname like 's%'）。SQL Server会快速定位到第一个S开头的记录，然后通过索引，使用标签访问数据行，直到第一个T开头的记录。
　　

　　
如果选择的列都包含在索引中，上面的查询会执行的更快。如果我们执行
　　

　　

　　

• SELECT FirstName, LastName
  
• 
  
•     FROM Contact
  
• 
  
•     WHERE LastName LIKE 'S%';
  

　　

　　

　　
SQL Server快速的导航到S入口，然后通过索引，忽略标签，直接从索引的入口返回数据，直到第一个T入口。在关系数据库的名词中，叫做查询全覆盖索引。
　　

　　
很多SQL的操作都可以从索引中受益，包括：ORDER BY, GROUP BY, DISTINCT, UNION( not UNION ALL ), JOIN ... ON 。
　　

　　
谨记从左到右的键顺序的重要性。我们建立的索引对于lastname=“ashton”很管用，但是对于firstname=“ashton”作用会小很多，甚至没有用。
　　

　　
测试一些简单的查询
　　

　　
如果你要执行下面的查询，确保你执行了前面的脚本，创建了contact_index和contact_noindex表，而且也在contact_index表创建了LastName, FirstName索引。
　　

　　
开启统计
　　

　　

　　

• SET STATISTICS io ON
  
• 
  
• SET STATISTICS time ON
  

　　

　　

　　
因为contact表中的数据只有19972行，很难得到有意义的统计时间。大部分的查询都显示CPU time： 0 毫秒，因此我们可以关闭time统计，只显示io统计。如果你需要一张大表来统计真实的time信息，可以用文章后面的脚本构建一个百万行数据的contact表。下面的测试都以19972行的表为测试对象。
　　
　　
测试一个完全覆盖的查询
　　

　　
第一个查询是一个覆盖索引的查询，获取contact表中lastname以S开头的记录的一部分列。下面是执行的信息。
　　

　　


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

没有索引的情况下
(2130 row(s) affected)　　
Table 'Contacts_noindex'. Scan count 1, logical reads 568.

有索引的情况
(2130 row(s) affected)　　
Table 'Contacts_index'. Scan count 1, logical reads 14.

索引产生的影响
IO从568次减少到14次
注释
覆盖查询的索引是个好东西。没有索引，就会进行全表扫描。2130行，表明以S开头的记录占到了10%的数据。　　测试一个非完全覆盖的查询
　　我们修改一下查询，还是相同的查询，只是获取的列包含了一些没有建立索引的列，下面是执行的结果。
　　

　　


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

没有索引的情况下
(2130 row(s) affected)　　
Table 'Contacts_noindex'. Scan count 1, logical reads 568.

有索引的情况
(2130 row(s) affected)　　
Table 'Contacts_index'. Scan count 1, logical reads 568.

索引产生的影响
IO没有影响
注释
在查询的过程中没有使用到索引。在这种情况下，SQL Server觉得使用索引查找，比不适用索引直接扫描，还要做更多的工作。　　测试一个非完全覆盖的查询，但是提供更多的条件
　　

　　我们修改一下查询，还是相同的查询，只是缩减了查询结果的范围，增加使用索引的好处，下面是执行的结果。
　　

　　


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

没有索引的情况下
(107 row(s) affected)　　
Table 'Contacts_noindex'. Scan count 1, logical reads 568.

有索引的情况
(107 row(s) affected)　　
Table 'Contact_index'. Scan count 1, logical reads 111.

索引产生的影响
IO从568次减少到111次。
注释　　SQL Server访问了107条入口，都在索引的连续范围内。每个入口的标签都被用来获取对应的行数据。这些行在表中不是连续的。
　　这些查询用到了索引，但是不如第一次的覆盖查询效果好，尤其是在IO的读取方面。
　　你希望读取107次索引，然后获取107条数据，产生107次读取。
　　之前的查询，请求了2130行数据，没有用到索引。这次请求107行数据，使用了索引。你很像知道使用索引的临界点在哪里？在后面的级别中我们将会介绍这方面的内容。
　　
　　
测试一个完全覆盖的聚合查询
　　

　　
最后一个例子是一个聚合查询，包含了count计算。
　　

　　


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

没有索引的情况下
(2130 row(s) affected)　　
Table 'Contacts_noindex'. Scan count 1, logical reads 568.

有索引的情况
(104 row(s) affected)　　
Table 'Contacts_index'. Scan count 1, logical reads 4.

索引产生的影响
IO从568次减少到4次
注释
所有需要查询的信息都包含在索引中，很好的使用了索引。　　测试一个非完全覆盖的聚合查询
　　我们修改一下查询，还是相同的查询，只是获取的列包含了一些没有建立索引的列，下面是执行的结果。
　　

　　


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

没有索引的情况下
(2130 row(s) affected)　　
Table 'Contacts_noindex'. Scan count 1, logical reads 568.

有索引的情况
(105 row(s) affected)　　
Table 'ContactLarge'. Scan count 1, logical reads 111.

索引产生的影响
IO从568次减少到111次
注释
。　　结论
　　当目前位置，我们知道非聚集索引有下面的一些好处：

• 是一些有序的入口集合。
  
• 表中的每一行都有一个入口。
  
• 包含一个索引键和一个标签。
  
• 用户负责创建的。
  
• SQL Server来维护。
  
• SQL Server用来最小化查询尝试，来满足客户的请求。
  

　　通过一些例子，我们看到SQL Server通过索引可以满足查询的请求，也有一些查询会完全忽略索引，还有一些会部分的使用索引。基于这个原因，我们来更新一下在第一级中的一个结论。
　　当一个请求到达数据库的时候，SQL Server只有三种访问数据的方式：

• 访问非聚集索引，避免访问表。这只发生在索引包含了请求中的所有数据。
  
• 通过索引键访问非聚集索引，然后使用标签访问表中的行数据。
  
• 忽略非聚集索引，扫描表找到请求的行数据。
  

　　通常来说，第一种是理想的，第二种要比第三种好。在之前的介绍中，我们知道了如何增加索引的使用可能性，如何决定更高效的使用非聚集索引。但是我们需要知道更多的索引内部的细节。
　　在我们知道细节之前，我们还需要介绍其他类型的SQL Server索引：聚集索引。
　　代码下载

• NonClustered.SQL
  
• Level 2 - NonClustered.sql
  
• Level2_MillionRowContactTable.sql