深入非聚集索引:楼梯SQL Server二级索引
原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/72286/
Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2
By David Durant, 2017/10/18 (first published: 2014/11/26）
深入非聚集索引:楼梯SQL Server二级索引
通过大卫·杜兰特,2017/10/18(第一次出版:2014/11/26)
该系列
本文是楼梯系列的一部分:SQL Server的阶梯索引
索引数据库设计的基础,告诉开发人员使用数据库设计者的意图。 不幸的是索引时往往是后加上的性能问题出现。 终于在这里是一个简单的系列文章,应该让任何数据库专业迅速“加速”
SQL Server的一级索引楼梯介绍了SQL Server索引,一般来说,和非聚集索引。 作为我们的第一个案例研究中,我们演示了索引的潜在好处,当从一个表中检索单个行。 在这个层面上,我们继续我们的调查的非聚集索引; 检查他们的贡献好的查询性能的情况下,超越从一个表中检索单个行。
将在大多数我们的水平,我们引入少量的理论,研究一些指数内部为了帮助解释这个理论,然后执行一些查询。 有或没有执行这些查询索引和性能报告统计数据,这样我们可以查看指标的影响。
我们将使用从AdventureWorks数据库表的子集,我们用于一级、集中联系表在这个水平。 我们将使用一个指数FullName我们用于一级指数,来说明我们的观点。 以确保我们控制上的索引联系表,我们会让两个表的副本dbo模式,只有构建FullName指数其中之一。 这将给我们的控制环境:两份表:一个与一个单一的非聚集索引,一个没有任何索引。
注意:
所有TSQL代码所示这楼梯水平可以在文章底部的下载。
清单1中的代码的副本Person.Contact表,我们可以随时重新运行这个批处理我们希望从一个“白纸”开始。
如果存在(选择*从sys.tables&# 160;在哪里OBJECT_ID=OBJECT_ID(“dbo.Contacts_index”))删除表dbo.Contacts_index;去如果存在(选择*从sys.tables&# 160;在哪里OBJECT_ID=OBJECT_ID(“dbo.Contacts_noindex”))删除表dbo.Contacts_noindex;去选择*成dbo.Contacts_index从Person.Contact;选择*成dbo.Contacts_noindex从Person.Contact;
清单2.1:复制人。 联系表
的一个片段联系人表所示:
ContactID FirstName MiddleName LastName EmailAddress
。
。
1288年劳拉·F·诺曼laura1@adventure-works.com
651年迈克尔彭定康michael20@adventure-works.com
1652伊莎贝拉R詹姆斯isabella6@adventure-works.com
1015年大卫·R·坎贝尔david8@adventure-works.com
1379年Balagane Swaminath balaganesan0@adventure-works.c
742年史蒂夫•施密特steve3@adventure-works.com
1743年香农C郭shannon16@adventure-works.com
1106年约翰·Y陈john2@adventure-works.com
1470年布莱恩Dockter blaine1@adventure-works.com
833年克拉伦斯·R。 Tatman clarence0@adventure-works.com
1834希瑟·吴heather6@adventure-works.com
丹尼斯·H·史密斯1197 denise0@adventure-works.com
560年詹妮弗·J。 Maxham jennifer1@adventure-works.com
1561年被罩Ben-Sacha ido1@adventure-works.com
924年贝基R。 水域becky0@adventure-works.com
。
非聚集索引条目
下面的语句创建我们的Contacts_index FullName非聚集索引表。
创建索引FullName在Contacts_index(姓,FirstName);
清单2.2 -创建一个非聚集索引
记住一个非聚集索引存储索引键,以及一个书签用来访问表中的实际数据本身。 你能想到的书签作为一种指针。 未来的水平将描述书签,其形式和使用,详细。
的一个片段FullName指数显示,组成的姓和FirstName作为键列,加上书签:
——搜索键列:书签
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”条目。
上述请求执行更快如果所有选中的列索引。 因此,如果我们发布:
选择FirstName,姓从联系在哪里姓就像' S %;
SQL Server可以快速导航到第一个“S”条目,然后遍历索引条目,忽略了书签和检索数据值直接从索引条目,直到它到达第一个“T”条目。 在关系数据库术语中,该指数“覆盖”查询。
任何SQL操作符的好处从测序数据可以从索引中受益。 这包括ORDER BY、组,不同的联盟(联盟),并加入…。
例如,如果请求要求计数的联系人的姓,SQL Server可以在第一项开始计数,并进行指数。 每次姓的价值变化,SQL Server输出当前计数,并开始一个新的计数。 与前面的请求,这是一个覆盖查询; SQL Server访问索引,完全忽略了表。
注意从左到右的顺序的重要性的关键列。 我们的指数是非常有用的,如果一个请求要求每个人的姓“艾什顿”,但很少或根本没有帮助如果请求是对每个人都是谁的名字“艾什顿”。
测试一些示例查询
如果你想执行测试查询,确保你运行该脚本创建两个版本新联系表,dbo.Contacts_index和dbo.Contacts_noindex;创建并运行脚本名,姓指数dbo.Contacts_index。
验证断言在前面的小节中,我们将在相同的性能统计数据,我们使用1级和运行一些查询; 有和没有索引。
设置数据io在
设置数据时间在
因为联系人表的AdventureWorks数据库中只有19972行,它将很难得到有意义的统计值。 我们大多数的查询会显示CPU时间值为0,所以我们不显示统计数据的输出时间; 只从统计数字IO,反映了可能的页面数量必须阅读。 这些值将使我们能够比较查询在相对意义上,确定哪些查询与索引比别人表现得更好。 如果你想要一个更大的表更现实的计时测试,脚本构建版本的一百万行联系本文表是可用的。 所有的讨论将假设您使用的是标准的19972 -行表。
测试覆盖查询
我们的第一个查询将被索引的查询; 一个检索一组有限的列的所有联系人的姓始于“S”。 表2.1中给出了查询执行信息。
|
SQL |
选择FirstName、LastName |
|
没有索引 |
(2130行受影响) |
|
与指数 |
(2130行受影响) |
|
指数的影响 |
从568年读14读IO减少。 |
|
评论 |
覆盖索引查询是一件好事。 没有索引,找到扫描整个表行。 |
表2.1:覆盖查询运行时执行结果
测试一个Non-Covered查询
接下来,我们修改我们的查询请求与之前相同的行,但不包括列索引。 表2.2中给出了查询执行信息。
|
SQL |
SELECT * |
|
没有索引 |
与之前的查询相同。 (因为它是一个表扫描)。 |
|
与指数 |
(2130行受影响) |
|
指数的影响 |
根本没有影响。 |
|
评论 |
该指数是查询的执行期间从未使用过! |
表2.2:non-covered查询运行时执行结果
测试一个Non-Covered查询但更有选择性
这一次,我们让我们的查询更多的选择性; 也就是说,我们缩小被请求的行数。 这增加的概率指数将有利于查询。 表2.3中给出了查询执行信息。
|
SQL |
SELECT * |
|
没有索引 |
与之前的查询相同。 (因为它是一个表扫描)。 |
|
与指数 |
(107行受影响) |
|
指数的影响 |
IO减少从568年读111读. . |
|
评论 |
SQL Server 107年访问“Ste %”条目,都是坐落在指数连续。 每个条目的书签被用来检索到相应的行。 行不位于连续在桌子上。 这个查询索引受益; 但不是它受益第一个查询,查询“覆盖”; 尤其是所需要的IOs数检索每一行。 你可能会认为,阅读107索引条目需要107 + 107 + 107行。 为什么只有111读取被要求将覆盖在一个更高的水平。 现在,我们很少会说,读的是用于访问索引条目; 大部分被用来访问的行。 因为前面的查询请求的2130行,没有受益于指数; 这个查询,要求107行,受益于指数——您可能还想“临界点又在何方? “SQL Server的决定背后的计算也将在未来的水平。 |
表2.3:更多的选择性non-covered查询运行时执行结果
测试一个聚集查询
我们最后将一个聚合查询样例查询; 是一个查询,涉及计算,总计平均等等。 在这种情况下,它是一个查询,告诉我们名字的范围内重复联系表。
结果,在某种程度上,是这样的:
钢美林1
斯蒂尔琼1
斯蒂尔劳拉2
斯蒂尔曼Shanay 1
Steen海蒂2
蒂芬妮斯特凡诺1
施泰纳Alan 1
查询执行信息表2.4中可以看到。
|
SQL |
选择名,姓,COUNT(*)“联系人” |
|
没有索引 |
与之前的查询相同。 (因为它是一个表扫描)。 |
|
与指数 |
(104行受影响) |
|
指数的影响 |
从568年读到4读IO减少。 |
|
评论 |
所需的所有信息的查询索引; 是在理想的指数序列计算数量。 所有的“姓始于Ste”条目中连续指数; 和组内,一个姓/名值的所有条目被组合在一起。 不需要访问的表; 也不是任何排序所需的中间结果。 再次,索引,查询是一件好事。 |
表2.4:当运行覆盖聚合查询执行结果
测试一个Non-Covered聚合查询
如果我们改变查询包括列索引,我们得到了性能结果见表2.5。
|
SQL |
选择LastName、FirstName MiddleName,COUNT(*)“联系人” |
|
没有索引 |
与之前的查询相同。 (因为它是一个表扫描)。 |
|
与指数 |
(105行受影响) |
|
指数的影响 |
IO减少从568年读111读; 以前一样non-covered查询 |
|
评论 |
中间工作处理查询时并不总是出现在统计数据。 技术,使用内存或tempdb排序和合并数据的例子。 在现实中,索引的好处可能大于所示的统计信息。 |
表2.5:noncovered聚合查询运行时执行结果
结论
我们现在知道非聚集索引具有以下特性。 非聚集索引:
- 是一组分类的条目。
- 基本表的每一行有一个条目。
- 包含一个索引键和一个书签。
- 是由你。
- 是由SQL服务器。
- 使用SQL Server来最小化所需的努力满足客户的要求。
和我们看到的例子中,SQL Server从索引就可以满足要求; 和它完全忽略了指数; 还有一些常用的索引和表的组合。 出于这个原因,我们关闭二级通过更新语句在一级的开始。
当一个请求到达您的数据库,SQL Server只有三个可能的方法来访问数据要求的声明:
- 访问非聚集索引,避免访问表。 这只可能在索引中包含的所有数据,对于这个表,被请求的查询
- 使用索引键(s)来访问非聚集索引,然后使用所选的书签(s)来访问表的单个行。
- 忽略了非聚簇索引表和扫描请求的行。
一般来说,第一个是理想; 和第二个比第三个。 在即将到来的水平,我们显示如何增加您的索引的概率将覆盖你的热门查询,以及如何确定你non-covered足够选择性受益于您的索引查询。 但这将需要更多的详细信息索引的内部结构比我们还没有。
才能达到这一点,我们需要引入其他类型的SQL Server指数; 聚集索引。 这是三级的主题。
下载代码
资源:
二级- NonClustered.sql|Level2_MillionRowContactTable.sql
这篇文章的一部分SQL Server的阶梯索引楼梯
