SQL Server索引简介:SQL Server索引进阶 Level 1

作者:David Durant,2014/11/05(首次发布:2011/02/17)

关于系列

本文属于进阶系列的:Stairway to SQL Server Indexes

索引是数据库设计的基础,并告诉开发人员使用数据库大量关于设计人员的意图。不幸的是,当性能问题出现时,索引通常被添加为事后的想法。最后这一系列简单的文章,应该能使任何数据库专业人员快速的“加快速度”。


此第一级引入SQL Server索引:数据库对象,使SQL Server能够在最短时间内查找和/或修改所请求的数据,使用最少的系统资源实现最大性能。良好的索引还将允许SQL Server实现最大的并发性,以便一个用户运行的查询对其他人运行的查询几乎没有影响。最后,索引提供了一种实现数据完整性的有效方法,通过在创建唯一索引时保证键值的唯一性。这个级别是一个介绍;它涵盖了概念和用法,但将物理细节留给了更高的层次。

对数据库开发人员的透彻理解对于数据库开发人员来说非常重要,其中一个原因来自于所有其他原因:当SQL Server从客户端到达的请求时,SQL Server只有两种可能的方式来访问所请求的行:

  • 它可以扫描包含数据的表中的每一行,从第一行开始,并继续到最后一行,检查每一行以查看它是否符合请求标准。
  • 或者,如果有益的索引可用,则可以使用索引来定位所请求的数据。 第一个选项始终可用于SQL Server。第二个选项只有在您指示SQL Server创建有益的索引时才可用,但可以显着提高性能,因为我们稍后会在此级别进行说明。

因为索引具有与它们相关联的开销(它们占用空间并且必须与表保持同步),所以它们不是SQL Server所必需的。完全没有索引的数据库是可能的。它可能会执行得很差,一定会有数据完整性问题,但SQL Server将允许它。

但是,这不是我们想要的。我们都希望数据库运行良好,具有数据完整性,同时将索引开销降至最低。这个水平将使我们开始实现这一目标。

样例数据库

在这个楼梯中,我们将用例子来说明关键概念。 这些示例基于Microsoft AdventureWorks示例数据库。 我们专注于销售订单功能。 五个表将给我们很好的组合事务和非交易数据; 客户,SalesPerson,产品,SalesOrderHeader和SalesOrderDetail。 为了保持重点,我们使用一列子集。

AdventureWorks正常化,所以销售人员信息被分为三个表格: 销售人员,员工和联系人。 对于一些例子,我们将它们视为单个表。 我们将使用的完整的表,以及它们之间的关系,如图1.1所示。

图1.1:将在此楼梯中使用的AdventureWorks表

注意: 此楼梯级别中显示的所有TSQL代码可以与文章一起下载(请参阅本文底部的链接)

什么是索引?

我们开始对索引的研究,一个简短的故事,一个使用一个旧的而且被证明的技术,我们在本文中将引用索引的基本概念。

你离开你的房子跑几个差事。当你回来的时候,你会发现你女儿垒球教练的消息等着你。三个女孩,特雷西,丽贝卡,艾米已经失去了球队帽。你能摆脱竞技产品店,买女孩的帽子。他们的父母会在下一场比赛中报销你。

你知道女孩,你知道他们的父母。但你不知道他们的帽子大小。在你镇的某个地方有三个住宅,每个都有一个您需要的信息。没问题,你只要打电话给父母,得到帽子的大小。您可以拨打手机,然后到达索引 - 电话簿的白页。

您需要达到的第一个住所是Helen Meyer。估计“迈耶”将位于人口中部附近,你跳到白页的中间;只发现你在页面上的标题为“Kline-Koerber”。你进一步向前跳,到达“Niger-Nyeong”页面。一个甚至更小的跳跃让你进入“马尔多纳多 - 尼格尔”页面。意识到您现在在正确的页面,您扫描页面,直到您到达“迈耶,海伦”行并获得电话号码。使用电话号码,您可以到达迈耶居所,并获取您需要的信息。

您再重复一次这个过程,再到另外两个居住地,再获得两个帽子大小。

你刚刚使用了一个索引,并且使用它与SQL Server使用索引的方式大致相同。因为有很大的相似之处,以及白皮书和SQL Server索引之间的一些区别。 实际上,您刚刚使用的索引代表SQL Server支持的两个SQL Server索引类型:集群和非聚簇。白页最好代表非聚簇索引的概念。因此,在这个层次上,我们引入非聚簇索引。后续级别将引入集群索引,并对这两种类型进行更深入的挖掘。

非聚簇索引

白页类似于非聚簇索引,因为它们不是数据本身的组织;而是一种机制或地图来帮助您访问该数据。数据本身就是我们需要联系的实际人员。电话公司不会安排该镇的住宅有意义的顺序,将房屋从一个位置移动到另一个位置,以使同一垒球队中的所有女孩彼此隔壁相隔,房屋不按居民姓氏组织。相反,它给你一本书包含每个住所的一个条目。这些条目由白页的搜索关键字排序;姓氏,名字,中间初始和街道地址。每个条目都包含搜索关键字和使您可以访问住所的数据;电话号码。

像一个条目白皮书,SQL Server非聚簇索引中的每个条目都包含两部分:

  • 搜索键,如姓氏 - 名字 - 中间初始。 。在SQL Server术语中,这是索引键。
  • 书签与电话号码相同,允许SQL Server直接导航到与该索引条目对应的表中的行。

此外,SQL Server非聚簇索引条目具有一些仅内部使用的头信息,并且可能包含一些可选信息。这两个都将在以后的层面上予以涵盖;此时对于非聚簇索引的理解也不重要。

像白页一样,在搜索关键字序列中维护一个SQL Server索引,以便可以在一组小的“跳转”中访问任何特定的条目。给定搜索关键字,SQL Server可以快速获取该密钥的索引条目。与白页不同,SQL Server索引是动态的。也就是说,SQL Server会在每次添加,删除行或修改搜索关键字列值时更新索引。

正如白页中的条目序列与城镇内的住宅地理序列不同;非聚簇索引中的条目序列与表中的行序列不同。索引中的第一个条目可能是表中最后一行,索引中的第二个条目可能是表中第一行。如果事实与索引不同,索引始终是有意义的序列;表的行可以完全没有排序。

创建索引时,SQL Server会在基础表中的每一行的索引中生成并维护一个条目(当覆盖过滤后的索引时,将会遇到此通用规则的一个例外)。您可以在表上创建多个非聚簇索引,但不能包含包含来自多个表的数据的索引。

而最大的区别是:SQL Server不能使用电话。它必须使用索引条目的书签部分中的信息导航到表的相应行。当SQL Server需要数据行中的任何信息,但不在相应的索引条目中时,这将是必需的,例如Tracy Meyer的垒球帽大小。所以,为了更好的比喻,白页的条目包含一组GPS坐标而不是一个电话号码。然后,使用GPS坐标导航到由白页条目表示的住宅。

创造和受益于非聚集索引

我们通过两次查询我们的示例数据库来结束这个级别。 确保您正在使用适用于SQL Server 2005的AdventureWorks版本,可由SQL Server 2008使用。AdventureWorks2008数据库具有不同的表结构,下面的查询将失败。 我们每次都会运行相同的查询; 但在我们在表上创建一个索引之前,第一个执行将会发生,第二个执行将在我们创建一个索引之后。 每次SQL Server会告诉我们在检索所请求的信息方面做了多少工作。 我们将在我们的联系表中找到“Helen Meyer”行(她的行位于表的中间附近)。 最初,表不会在FirstName列或LastName列上有一个索引。 为确保您可以多次运行示例,请确保我们将在第三批中构建的索引不存在,方法是运行以下代码:

IF EXISTS (SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('Person.Contact')
AND name = 'FullName')
DROP INDEX Person.Contact.FullName; 

清单1.1 - 确保索引不存在

我们的任务将需要四个SQL命令批处理。

第一个命令批次:

SET STATISTICS io ON
SET STATISTICS time ON
GO

清单1.2 - 开启统计

上述批次通知SQL Server,我们希望我们的查询作为输出的一部分返回性能信息。

第二个命令批次:

SELECT *
    FROM Person.Contact
    WHERE FirstName = 'Helen'
        AND LastName = 'Meyer';
GO

清单1.3 - 检索一些数据

第二批检索“Helen Meyer”行:

584 Helen Meyer helen2@adventure-works.com   0-519-555-0112

再加上以下性能信息:

Table 'Contact'. Scan count 1, logical reads 569.
SQL Server Execution Times:   CPU time = 3 ms.

该输出通知我们,我们的请求执行了569个逻辑IO,并且需要大约3毫秒的处理器时间来执行此操作。 您的处理器时间值可能不同。

第三个命令批次:

CREATE NONCLUSTERED INDEX FullName
            ON Person.Contact
    ( LastName, FirstName );
GO

清单1.4 - 创建非聚集索引

此批次在联系人表的名字和姓氏列上创建非聚簇复合索引。 复合索引是具有多个列的索引,确定索引行序列。

第四个命令批次:

SELECT *
    FROM Person.Contact
    WHERE FirstName = 'Helen'
        AND LastName = 'Meyer';
GO

清单1.3(再次)

最后一批是重新执行我们原始的SELECT语句。 我们得到与以前一样返回的同一行; 但是这次性能统计数据是不同的

Table 'Contact'. Scan count 1, logical reads 4.
SQL Server Execution Times:   CPU time = 0 ms.

该输出通知我们,我们的请求只需要4个逻辑IO; 并且需要非常少量的处理器时间来检索“Helen Meyer”行。

结论

创建精心挑选的索引可以大大提高数据库性能。 在下一级,我们将开始研究索引的物理结构。 我们将研究为什么这个非聚集索引对这个查询是如此有益的,为什么可能并不总是这样。 未来的水平将涵盖其他类型的指数,指数的附加利益,与索引相关的成本,监控和维护您的指数以及最佳做法; 所有这些都旨在为您提供必要的知识,为您自己的数据库中的表创建最佳的索引计划。

代码下载

Resources:

Level 1 - IntroToIndexes_Durant_Code.sql | Level 1 - MillionRowContactTable.sql


本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Spark学习技巧

flink 有状态udf 引起血案一

最近在做一个画像的任务,sql实现的,其中有一个udf,会做很多事情,包括将从redis读出历史值加权,并将中间结果和加权后的结果更新到redis。

2785
来自专栏杨建荣的学习笔记

分分钟搭建MySQL Group Replication测试环境(二)(r12笔记第41天)

之前总结过一篇,分分钟搭建MySQL Group Replication测试环境(r11笔记第82天),但是有一个地方还有待改善,那就是那个脚本仅仅支持s...

3737
来自专栏Java学习网

MySQL大表优化方案

  当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化:   单表优化   除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分...

8126
来自专栏杨建荣的学习笔记

物化视图中的统计信息导致的查询问题分析和修复 (r7笔记第47天)

今天开发的同事下午反馈给我一个问题,说有操作直接卡住了,听这个描述,感觉很可能是查询慢了。 于是连接到环境中,查看了一下正在执行的sql语句情况,发现下面的语句...

3515
来自专栏PHP在线

PHP开发人员常犯的10个MysqL错误

对于大多数web应用来说,数据库都是一个十分基础性的部分。如果你在使用PHP,那么你很可能也在使用MySQL—LAMP系列中举足轻重的一员。 对于很多新手们来说...

3968
来自专栏腾讯Bugly的专栏

移动客户端中高效使用 SQLite

导语 iOS 程序能从网络获取数据。少量的 KV 类型数据可以直接写文件保存在 Disk 上,App 内部通过读写接口获取数据。稍微复杂一点的数据类型,也可以将...

5446
来自专栏更流畅、简洁的软件开发方式

用node.js实现ORM的一种思路

  ORM是O和R的映射。O代表面向对象,R代表关系型数据库。二者有相似之处同时也各有特色。就是因为这种即是又非的情况,才需要做映射的。   理想情况是,根据关...

2549
来自专栏码神联盟

数据库SQL优化大总结1之- 百万级数据库优化方案

小编最近几天一直未出新技术点,是因为小编在忙着总结整理数据库的一些优化方案,特此奉上,优化总结较多,建议分段去消化,一口吃不成pang(胖)纸 ...

2.8K8
来自专栏数据和云

经典案例:如何优化Oracle使用DBlink的SQL语句

作者介绍 赵全文 就职于太极计算机股份有限公司,在中央电化教育馆做Oracle DBA的驻场运维工作。具有3年左右的Oracle工作经验,目前擅长Oracle数...

4449
来自专栏PHP在线

PHP开发人员常犯的10个MysqL错误

对于大多数web应用来说,数据库都是一个十分基础性的部分。如果你在使用PHP,那么你很可能也在使用MySQL—LAMP系列中举足轻重的一员。 对于很多新手们来说...

3024

扫码关注云+社区

领取腾讯云代金券