深入到非聚集索引:到SQL Server索引级别2的阶梯。
大卫·杜兰特，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·诺曼[email protected]
651年迈克尔彭定康[email protected]
1652伊莎贝拉R詹姆斯[email protected]
1015年大卫·R·坎贝尔[email protected]
1379年Balagane Swaminath [email protected]
742年史蒂夫•施密特[email protected]
1743年香农C郭[email protected]
1106年约翰·Y陈[email protected]
1470年布莱恩Dockter [email protected]
833年克拉伦斯·R。 Tatman [email protected]
1834希瑟·吴[email protected]
丹尼斯·H·史密斯1197 [email protected]
560年詹妮弗·J。 Maxham [email protected]
1561年被罩Ben-Sacha [email protected]
924年贝基R。水域[email protected]
。

非聚集索引条目

下面的语句创建我们的Contacts_index FullName非聚集索引表。

创建索引FullName在Contacts_index(姓,FirstName);

清单2.2 -创建一个非聚集索引

记住一个非聚集索引存储索引键,以及一个书签用来访问表中的实际数据本身。你能想到的书签作为一种指针。未来的水平将描述书签,其形式和使用,详细。

的一个片段FullName指数显示,组成的姓和FirstName作为键列,加上书签:

——搜索键列:书签

。

罗素Zachary = >
露丝安迪= >
露丝安迪= >
瑞安大卫= >
瑞安贾斯汀= >
Sabella迪安娜= >
Sackstede巷= >
Sackstede巷= >
Saddow彼得= >
赛辛迪= >
赛Kaitlin = >
赛Manuel = >
Salah添马舰= >
Salanki Ajay = >
Salavaria沙龙= >

每个条目包含索引键列和收藏价值。此外,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 dbo。联系人——Contacts_noindex和执行 ——Contacts_index 在LastName像' S %
没有索引	(2130行受影响) 表“Contacts_noindex”。扫描数1,逻辑读568。
与指数	(2130行受影响) 表“Contacts_index”。扫描数1,逻辑读14。
指数的影响	从568年读14读IO减少。
评论	覆盖索引查询是一件好事。没有索引,找到扫描整个表行。 “2130行”的统计数据表明,“S”是一个流行的姓氏首字母,发生在百分之十的联系人。

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

测试一个Non-Covered查询

接下来,我们修改我们的查询请求与之前相同的行,但不包括列索引。表2.2中给出了查询执行信息。

SQL	SELECT * dbo。联系人——Contacts_noindex和执行 ——Contacts_index 在LastName像' S %
没有索引	与之前的查询相同。 (因为它是一个表扫描)。
与指数	(2130行受影响) 表“Contact_index”。扫描数1,逻辑读568。
指数的影响	根本没有影响。
评论	该指数是查询的执行期间从未使用过! SQL Server决定从索引项跳到相应的表中的行2130次为每一行(曾经)是更多的工作比扫描整个表的一百万行找到需要的2130行。

表2.2:non-covered查询运行时执行结果

测试一个Non-Covered查询但更有选择性

这一次,我们让我们的查询更多的选择性; 也就是说,我们缩小被请求的行数。这增加的概率指数将有利于查询。表2.3中给出了查询执行信息。

SQL	SELECT * dbo。联系人——Contacts_noindex和执行 ——Contacts_index 在LastName像“Ste %”
没有索引	与之前的查询相同。 (因为它是一个表扫描)。
与指数	(107行受影响) 表“Contact_index”。扫描数1,逻辑读111。
指数的影响	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(*)“联系人” dbo。联系人——Contacts_noindex和执行 ——Contacts_index 在LastName像“Ste %” 集团由名,姓
没有索引	与之前的查询相同。 (因为它是一个表扫描)。
与指数	(104行受影响) 表“Contacts_index”。扫描数1,逻辑读4。
指数的影响	从568年读到4读IO减少。
评论	所需的所有信息的查询索引; 是在理想的指数序列计算数量。所有的“姓始于Ste”条目中连续指数; 和组内,一个姓/名值的所有条目被组合在一起。不需要访问的表; 也不是任何排序所需的中间结果。再次,索引,查询是一件好事。

表2.4:当运行覆盖聚合查询执行结果

测试一个Non-Covered聚合查询

如果我们改变查询包括列索引,我们得到了性能结果见表2.5。

SQL	选择LastName、FirstName MiddleName,COUNT(*)“联系人” dbo。联系人——Contacts_noindex和执行 ——Contacts_index 在LastName像“Ste %” MiddleName GROUP BY名,姓
没有索引	与之前的查询相同。 (因为它是一个表扫描)。
与指数	(105行受影响) 表“ContactLarge”。扫描数1,逻辑读111。
指数的影响	IO减少从568年读111读; 以前一样non-covered查询
评论	中间工作处理查询时并不总是出现在统计数据。技术,使用内存或tempdb排序和合并数据的例子。在现实中,索引的好处可能大于所示的统计信息。

表2.5:noncovered聚合查询运行时执行结果

结论

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

是一组分类的条目。
基本表的每一行有一个条目。
包含一个索引键和一个书签。
是由你。
是由SQL服务器。
使用SQL Server来最小化所需的努力满足客户的要求。

和我们看到的例子中,SQL Server从索引就可以满足要求; 和它完全忽略了指数; 还有一些常用的索引和表的组合。出于这个原因,我们关闭二级通过更新语句在一级的开始。

当一个请求到达您的数据库,SQL Server只有三个可能的方法来访问数据要求的声明:

访问非聚集索引,避免访问表。这只可能在索引中包含的所有数据,对于这个表,被请求的查询
使用索引键(s)来访问非聚集索引,然后使用所选的书签(s)来访问表的单个行。
忽略了非聚簇索引表和扫描请求的行。

一般来说,第一个是理想; 和第二个比第三个。在即将到来的水平,我们显示如何增加您的索引的概率将覆盖你的热门查询,以及如何确定你non-covered足够选择性受益于您的索引查询。但这将需要更多的详细信息索引的内部结构比我们还没有。
才能达到这一点,我们需要引入其他类型的SQL Server指数; 聚集索引。这是三级的主题。

翻译：深入到非聚集索引:到SQL Server索引级别2的阶梯。