【译】索引进阶（一）：SQL SERVER索引介绍

2022-04-07 10:53:27

  【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】

原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/72284/

对于数据库设计来说，索引是至关重要的，它告诉了那些大量使用数据库的开发者其所使用的数据库的设计意图。然而不幸的是，在实际开发过程中，索引常常是作为一个性能问题发生时候的解决方案被添加上的。这一些列关于索引的介绍可以让你与专业的数据库设计人员保持思想及设计上的一致。

第一节介绍SQL SERVER的索引，它是使SQL SERVER在最小的时间内查询或者修改所要请求的数据的一种数据库对象，它目的在于使用最小的系统资源达到最大的性能优化。除此之外，好的索引设计可以保证数据库达到最大的并发数，这样一个用户的查询就几乎不会影响另一个用户的查询请求。最后，索引提供了保证数据一致性的有效方式。这个章节只是对于索引的简单介绍，包含了基本的概念和用法，对于其具体的实现细节后续章节会予以描述。

对于数据库开发者来说，对于索引的总体理解显得特别重要，其原因之一在于：当一个对于SQL SERVER的请求从客户端到达时，SQL SERVER仅仅有两种可能的方式来访问到所所请求的数据：

# 它可以扫描数据库表的每一行数据，从第一行到最后一行，检查每一行来判断其是否满足所请求的查询条件。

# 或者，如果一个有用的索引存在，它会用此索引来直接定位到所请求的数据。

对于SQL SERVER来说，第一种选择总是可用的，而只有当你指导数据库创建了合适的索引之后，第二个选择才是可用的，但是他却可以导致突出的性能优化，如我们之后在本章所演示的那样。

因为索引是由开销的（它们占用一定的存储空间并必须与数据库表数据保持一致），他们不是SQL SERVER所必须的，很有可能一个数据库没有任何索引存在，也有可能它在性能提升上表现得一般并且带来数据一致性的问题，但是SQL SERVER仍然允许它的存在。然而，索引带来的这些问题都不是我们想要的，我们都希望数据库性能优异，数据一致，同时索引开销尽可能的小，这一章节我们开始朝着这个目标前前进。

示例数据库

整个系列文章我们会用例子来演示关键概念，这些例子基于微软的AdventureWorks 示例数据库，我们关注于其销售订单功能上，如下五张表较好的混合了事务及非事务数据：Customer, SalesPerson, Product, SalesOrderHeader, and SalesOrderDetail，为了保证我们的关注点，我们使用其部门列数据。

AdventureWorks 具有良好的数据库设计结构，因此销售人员信息分散在三个表中：SalesPerson, Employee and Contact。对于一些例子来说，我们将其看作是一张表，我们将用到的所有表以及它们之间的关系如下图所示：

什么是索引

我们以一个简短的故事来开始对于索引的研究：

你由于一些事务离开了你的房子，当你回来的时候，你发现一条来自于自己女儿足球教练的信息等你处理，信息说有三个女孩：Tracy，Rebecca，Amy 搞丢了她们的帽子。请你务必去趟体育用品商店为姑娘们买下帽子，她们的家长会在下一次比赛时候偿还你的。

你认识这些女孩和她们的家长，而且很乐意帮女孩们买帽子，但是却不知道女孩们的帽子的大小，你需要的信息在女孩们位于镇里的住宅中，没有问题，你立即打电话给她们的家长从而可以获取这些信息。第一个你需要到达的住宅是Helen Meyer，假设Meyer处于zholn中间位置，你翻到黄页中间，翻到合适的页，然后扫描那些名字，找到“Meyer, Helen” 行，然后便得到了电话号码，用这个电话号码，你到达了他的房子然后得到了你所要的信息：帽子的大小。

按如上步骤重复两次，你到达了另外两个住宅，得到了另两个大小。

你正是使用了索引，并且你使用索引的方式和SQL SERVER使用索引的方式很像，在电话号码黄页与数据库索引之间具有很大的相似性及些许差别。

实际上，刚才你所使用的索引代表了SQL SERVER支持的两种数据库索引之一：非聚集索引。另一种是聚集索引，本章节重点介绍非聚集索引，后续章节会介绍聚集索引，并对两种类型的索引做深入分析。

非聚集索引

电话号码黄页类比于数据库的非聚集索引，其原因在于它们不是实际数据的组织形式，而是一种映射，帮助你访问到实际数据，数据本身是我们需要联系的人，电话公司不会安排城镇的住宅为一个有意义的序列，他们不会把房间从一个地点移动到另一个地点从而保证在一个足球队的女孩都住在一起，并且，房子也不会按照居民的姓氏排列，反而，电话号码本只是给你一个书签，包含了到达各个住宅的入口点，这些入口点按电话号码本的搜索键排列，就是姓氏，各个入口点包含了搜索键和使你能够到达住宅的数据：电话号码。

如同电话号码的入口点，SQL SERVER非聚集索引的入口点也包含两部分。

# 检索键：例如姓氏，在SQL SERVER术语来说：这就是索引键；

# 书签：类比于企业黄页中的电话号码，允许SQL SERVER直接导航至于此检索键交互的表数据行中。

除此之外，非聚集索引入口点还包含一些仅供内部使用的头信息，以及一些可选的信息，后续章节会对这些内容进行介绍，此刻对于理解非聚集索引来说，这些知识无关紧要。

像电话号码本一般，SQL SERVER索引按序列化的检索键组织起来，这样任何一个入口点可以通过数个跳转来达到。给定一个检索键，SQL SERVER可以快速的到达这个键对应的索引入口点，不同于电话本的是，SQL SERVER索引是动态的，这意味着SQL SERVER会在每一次数据的数据添加，移除，或者检索键发生更改时候更新相关索引。

电话号码本的排列顺序与住宅在地里位置上的排列顺序是不一样的，非聚集索引入口点的排列顺序与数据表中行的排列顺序也是不一样的。第一个入口点可能代表了表中的最后一行，第二个入口点则可能代表了数据库表中的第一行。正如事实上的，虽然数据库索引键是有意义的排列，一个表的行则可能是毫无顺序的。

当我们创建了一个索引，SQL SERVER索引中为表中的每一行数据都产生并且维护了一个入口点，你可以在一个表上创建多个非聚集索引，但是却无法创建索引包含两个表的数据，一个索引的局限于一个表结构。

其最大的不同在于，SQL SERVER不能使用电话号码，它必须使用索引入口点中书签部分的信息来导航到相应的行。当SQL SERVER需要一些在数据行中的信息但却不在入口点中的信息时，这个是非常必要的。

类似的，电话号码本可以包含一组GPS坐标信息而不是电话号码，你便可以用此GPS坐标导航至相应的住宅。

创建非聚集索引

我们通过两次查询示例数据库来结束本章节的介绍，确保你使用的AdventureWorks示例数据库是用于SQL SERVER 2005 版本的，当然它可以被后续版本使用，AdventureWorks2008版本数据库在表结构上会有些不同，导致如下查询失败。每次我们都会运行相同的查询，第一次执行时候，我们并没有创建索引，索引创建之后，我们执行第二次查询，每一次，SQL SERVER会告诉我们为了返回所请求的数据，多少工作被完成。我们将在Contact 表中查询“Helen Meyer”，最初在FirstName，LastName 表不存在任何索引。

为了确保我们可以多此运行我们的例子，保证我们将创建的索引不存在，我们可以运行如下SQL：

IF EXISTS (SELECT * FROM sys.indexes

WHERE OBJECT_ID = OBJECT_ID('Person.Contact')

AND name = 'FullName')

DROP INDEX Person.Contact.FullName; 

我们的任务需要执行4个SQL批量处理语句。

第一个SQL:

　　　　SET STATISTICS io ON

　　　　SET STATISTICS time ON

　　　　GO
上述命令通知SQL SERVER我们期望各个查询在输出部分返回性能信息。

第二个SQL:

      SELECT *

   　　　　 FROM Person.Contact

    　　　　WHERE FirstName = 'Helen'

       　　 AND LastName = 'Meyer';

　　　GO

上述语句返回信息：584 Helen Meyer helen2@adventure-works.com   0-519-555-0112
同时性能信息也被返回：如图

可以看出来我们的请求数据需要569次逻辑IO，需要毫秒去处理，你本地运行的时间可能不同。

接着我们创建索引：

CREATE NONCLUSTERED INDEX FullName

            ON Person.Contact

    ( LastName, FirstName );
GO

上述语句创建了非聚集组合索引，所谓组合索引便是索引列超过一列的索引。

我们再次执行上述查询：

SELECT *

    FROM Person.Contact

    WHERE FirstName = 'Helen'

        AND LastName = 'Meyer';

GO

这次性能数据不同:

结论

谨慎选择的索引能极大的改进数据库的性能，下一节我们将研究索引的物理结构，我们将检查为何非聚集索引对于我们的查询如此有益，以及为何并不总是如此有益，后续章节会包含其他类型的索引，以及索引的额外好处，索引的开销，监控并维护你的索引，以及最佳实践，所有着一切的目标在于提供给我们必要的知识，来为自己的数据库表创建最适宜的索引架构。

码农公寓

相关文章