深入非聚集索引:楼梯SQL Server二级索引
通过大卫·杜兰特,2017/10/18(第一次出版:2014/11/26)
该系列
本文是楼梯系列的一部分:SQL Server的阶梯索引
索引数据库设计的基础,告诉开发人员使用数据库设计者的意图。 不幸的是索引时往往是后加上的性能问题出现。 终于在这里是一个简单的系列文章,应该让任何数据库专业迅速“加速”
SQL Server的一级索引楼梯介绍了SQL Server索引,一般来说,和非聚集索引。 作为我们的第一个案例研究中,我们演示了索引的潜在好处,当从一个表中检索单个行。 在这个层面上,我们继续我们的调查的非聚集索引; 检查他们的贡献好的查询性能的情况下,超越从一个表中检索单个行。
将在大多数我们的水平,我们引入少量的理论,研究一些指数内部为了帮助解释这个理论,然后执行一些查询。 有或没有执行这些查询索引和性能报告统计数据,这样我们可以查看指标的影响。
我们将使用从AdventureWorks数据库表的子集,我们用于一级、集中联系表在这个水平。 我们将使用一个指数FullName我们用于一级指数,来说明我们的观点。 以确保我们控制上的索引联系表,我们会让两个表的副本dbo模式,只有构建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;
清单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 );
创建索引FullName在Contacts_index(姓,FirstName);
清单2.2 -创建一个非聚集索引
记住一个非聚集索引存储索引键,以及一个书签用来访问表中的实际数据本身。 你能想到的书签作为一种指针。 未来的水平将描述书签,其形式和使用,详细。
的一个片段FullName指数显示,组成的姓和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非聚集索引条目有internal-use-only头信息和可能包含一些可选的数据值。 这些将在以后的水平; 也不是重要的在这个时候非聚集索引的基本理解。
现在,我们需要知道的是,键值使SQL Server能够找到合适的索引条目(年代); 和条目的收藏价值使得SQL Server访问相应的数据表中的行。
索引条目的好处在序列
索引条目排序的索引键值(s),那么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 Server输出当前计数,并开始一个新的计数。 与前面的请求,这是一个覆盖查询; SQL Server访问索引,完全忽略了表。
注意从左到右的顺序的重要性的关键列。 我们的指数是非常有用的,如果一个请求要求每个人的姓“艾什顿”,但很少或根本没有帮助如果请求是对每个人都是谁的名字“艾什顿”。
测试一些示例查询
如果你想执行测试查询,确保你运行该脚本创建两个版本新联系表,dbo.Contacts_index和dbo.Contacts_noindex;创建并运行脚本名,姓指数dbo.Contacts_index。
验证断言在前面的小节中,我们将在相同的性能统计数据,我们使用1级和运行一些查询; 有和没有索引。
SET STATISTICS io ON SET STATISTICS time ON
SQLSELECT 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 ImpactIO reduced from 568 reads to 14 reads.CommentsAn 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.
测试一个Non-Covered查询
接下来,我们修改我们的查询请求与之前相同的行,但不包括列索引。 表2.2中给出了查询执行信息。