nlst命令（ncl命令）

在学习完本章后，您将可以：

l 生成执行计划

l 阅读执行计划

l 设计数据库，使其可以依据数据的用途高效地存储数据

l 使用聚集索引和非聚集索引

l 索引XML列

l 索引视图

l 整理索引碎片

l 使用数据库引擎优化顾问

第5章学习了如何在数据库中获取数据的汇总信息。如果数据正确地存储在数据库中，SQL Server可以快速而高效地返回包括汇总信息的结果。本章将解释SQL Server存储数据的不同方式，它如何获取数据，以及设计数据库的时候应该考虑什么，以便在SQL Server中获得最高的执行效率。

6.1 了解执行计划

SQL Server执行查询的时候，首先会确定执行该查询的最佳方式。这个决定包括如何且以何种顺序来访问和联接数据，如何且何时执行计算和聚合等等。这些工作由SQL Server中一个称为“查询优化器”的子系统负责。查询优化器使用数据分布的统计信息、与查询涉及的数据库对象相关的元数据、索引信息和其他因素来计算多种执行计划的可能性。对于每一种可能，查询优化器会基于数据的统计信息估计出执行的开销并选择执行开销最小的计划。当然，对于有些查询来说，执行这个计算的过程可能超过了按效率最低的计划执行所花费的时间，因此，SQL Server并不计算每一个查询的所有计划。因而，SQL Server有一个复杂的算法来找出一个合理的、接近最小可能开销的计划。这个执行计划生成之后，会被存储在一个缓存器中(大部分在SQL Server的虚拟内存中)。这个查询随后由数据库引擎按该计划所指示的方式执行。

注意缓存器中的执行计划可以在执行相同或者类似查询的时候重用。因此，执行计划会尽量存储在缓存器中。要想深入了解缓存执行计划，请访问http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx，参考题为“Batch Compilation, Recompilation,and Plan Caching Issues in SQL Server 2005”的白皮书。

SQL Server查询优化器能否针对给定的查询生成高效的执行计划，取决于以下两个因素：

l 索引就像一本书的索引一样，数据库索引提供了在表中快速查询特定行的能力。每一张表中可以存在许多索引。在表中索引的支持下，SQL Server查询优化器可以找出并使用正确的索引来优化对数据的访问。如果没有索引，查询优化器只有一个选择，那就是对表中的数据进行全部扫描以找出要找的数据行。本章后文将介绍索引是如何工作的以及如何设计和创建索引。

l 数据的分布统计 SQL Server会保存数据分布的有关统计信息。如果这些统计信息丢失或者过时了，查询优化器就无法计算出高效的执行计划。在许多情况下，统计信息会自动生成并更新。本章后文将介绍如何生成统计信息，以及如何管理这些信息。

可以看出，执行计划的高效与否决定了这个查询是否能够在毫秒、秒、甚至分钟数量级的时间内完成，因此对于SQL Server性能，执行计划的生成是至关重要的。我们可以通过分析低效查询的执行计划来确定是否丢失了索引，数据分布统计信息是否过时或丢失了，或SQL Server是否选择了一个低效的计划(这种情况不常发生)。

注意当然，一个好的执行计划也可能造成查询被低效执行。在这种情况下，查询优化并不是问题。问题可能更多出现在其他方面，例如查询设计、对资源的争夺、磁盘I/O (输入/输出)、内存、CPU和网络等等。您可以访问www.microsoft.com/ technet/prodtechnol/sql/2005/tsprfprb.mspx，阅读白皮书“Troubleshooting Performance Problems in SQL Server 2005”，了解这方面的情况。

Ø 查看查询执行计划

1. 从“开始”菜单中，依次选择“所有程序”|“Microsoft SQL Server 2005”|“SQL Server Management Studio”。单击“新建查询”按钮打开一个“新建查询”窗口并在“可用数据库”下拉菜单中选择“AdventureWorks”将数据库上下文更改为AdventureWorks。

2. 执行以下SELECT语句。这个示例的代码包含在示例文件Viewing Query Plans.sql中。

SELECT SalesOrderID, OrderQTY

FROM Sales.SalesOrderDetail

WHERE ProductID = 712

ORDER BY OrderQTY DESC

3. 按Ctrl+L或者在“查询”菜单上选择“显示估计的执行计划”显示这个查询的执行计划。执行计划如图6.1所示。

估计执行计划是在不真正执行查询的情况下生成的。查询由查询优化器进行优化，但是并没有执行。在处理运行时间长的查询时，查询优化器的这一特性具有明显的优势，因为没有必要在查询完成之后再看执行计划。阅读图形化显示的执行计划时，应该从右向左，从上向下阅读。每一个图标代表计划中的一个运算符，并且图中的箭头表示了在这些运算符之间的数据交换过程。箭头的宽度代表运算符之间传递数据的数量。这里不打算具体介绍每一个可用的运算符，但会介绍图6.1所示的执行计划中的运算符：

l SQL Server使用聚集索引扫描来访问数据。这种扫描是真实的数据访问操作，详见后文描述。

l 数据随后传递到排序运算符，它将根据语句的ORDER BY子句来对数据进行排序。

l 数据随后发送至客户。

图6.1 查看“执行计划”

在学习索引和联接的时候，我们将讨论SQL Server所用的最重要的操作符。完整的操作符列表请参见SQL Server Books Online的主题“Graphical Execution Plan Icons”。

在每一个操作符图标下面的开销百分比显示了这个操作在查询总开销中所占的百分比。可通过这个数字清楚地了解哪一个操作使用的执行资源最多。在这个例子中，“聚集索引扫描”的开销最大，占此查询总开销的89%。

4. 鼠标指针移到“聚集索引扫描”操作符，此时会出现一个黄色窗口，如图6.2所示。

这个窗口提供了此操作的详细信息。刚才，只知道SQL Server用扫描操作来获取数据。但是在这个窗口中可以看到，它会基于Sales.SalesOrderDetail表中的聚集索引执行一次聚集索引扫描操作来找出ProductID为712的记录。这些信息可以在“谓词”区域找到。同样，这个窗口还显示了估计的开销、估计的行数和估计行的大小。行数是基于SQL Server为该表所存储的统计信息而估计出的，而开销数是基于统计信息和参考系统的开销数得出的，因此根据开销数并不能计算出这个查询会在计算机上运行多长的时间。这些信息只能用于判断一个操作和其他操作相比是节约还是昂贵。

5. 以上有关操作符的信息还可以在SQL Server Management Studio的“属性”窗口中看到。右键单击操作符图标，在弹出菜单中选择“属性”即可打开“属性”窗口。

6. 执行计划还可以保存。右键单击要保存的计划，然后在弹出菜单中选择“将执行计划另存为”来保存该执行计划。执行计划将以XML的格式存储，扩展名为.sqlplan。在SQL Server Management Studio中，在“文件”菜单选择“打开”|“文件”即可打开文件。已保存的执行计划。

7. 目前看到的只是一个查询的估计执行计划。还可以显示一个实际的执行计划。实际的执行计划与估计的执行计划类似，但会包括实际的数量(不是估计的)，例如行数和重绕次数等。按“Ctrl+M”或在“查询”菜单中选择“包括实际的执行计划”来包括实际的执行计划。随后按“F5”执行查询。执行结果会按通常的方式显示出来，但执行计划将显示在“执行计划”选项卡中。

接下来看索引是如何工作以及它们是如何提高查询性能的。在SQL Server中可以定义两种不同类型的索引：聚集索引和非聚集索引。为了理解索引是如何提高数据访问速度的，以及在特定情况下该使用哪种索引，我们首先要了解数据和索引是如何存储在数据文件中的，以及SQL Server如何访问数据文件中的数据。

6.2.1 堆结构

SQL Server数据库中的一个数据文件会以8KB大小分页。每一页可以包括数据、索引、或者其他SQL Server需要为其维护数据文件的数据类型。然而，大多数的页是数据页或者索引页。页是SQL Server读、写数据文件的单元。每一页只包括一个数据对象的数据或索引信息。所以，在每一个数据页上，只能找到一个对象的数据。同样地，在一个索引页上，也只能找到一个索引的信息。在SQL Server 2000中，将一个数据行分别存储在不同页上是不可能的，这意味着一个数据行必须在一页上，这会导致数据行有8 060字节的大小限制(大型对象数据除外)。在SQL Server 2005中，对于变长数据类型，例如nvarchar，varbinary，CLR等，这个限制不复存在。对于变长数据类型，数据行可以跨越几个页，但是对于定长数据类型，一个数据行依然必须存储在一页上。

创建一个没有任何索引的表并向其中插入数据的时候，SQL Server会搜索未被使用的页来存储这些数据。为了追踪哪些页保存了这个表的数据，SQL Server会为每一个表设立一个或多个IAM (索引分配映射)页。这些IAM页指向保存表数据的页。这个表的数据以无索引的方式存储在页上，并且只是通过IAM页联系在一起，所以这个表被称作堆。SQL Server必须通过阅读这个表的IAM页并且通过扫描IAM页指向的所有页来访问一个堆的数据。这种操作称为表扫描。表扫描以无序的方式读取所有数据。如果一个查询要搜索一个特定的行，那么一个堆的表扫描必须读取表中的所有行来找到它，这是一种非常低效的操作。

Ø 检验堆结构

1. 打开SQL Server Management Studio。打开一个“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 在以下示例中，将创建两个表，分别为dbo.Orders 和 dbo.OrderDetails。键入并执行以下语句来创建表并为其添加数据。此示例的完整代码包含在示例文件Examining Heap Structures.sql中。

3. 现在创建了两张堆结构的表。键入以下语句来查询dbo.Orders表。在执行之前按“Ctrl+M”或在“查询”菜单中选择“包括实际的执行计划”来包括实际的执行计划。然后执行这个查询。

SET STATISTICS IO ON;

SELECT * FROM dbo.Orders

SET STATISTICS IO OFF

选项会打开一个特性，使SQL Server将语句执行期间I/O操作的有关信息发回给用户。这是一个用于判断查询I/O开销的极好特性。

4. 切换到“消息”选项卡。您会看到与图6.3相似的信息。

输出信息表明SQL Server需要对表中的数据进行一次扫描并需要为此操作执行178页的读操作(逻辑读)。输出信息同样表明，为了执行此操作没有用到物理读(物理读或者物理先读)。没有物理读的原因是，在此例中，数据已经保存在缓存中。如果您的信息窗口表明对于这个查询进行了物理读，那么在再次执行此查询之后您会看到物理读的数量会比上次执行时少了。因为SQL Server会将最近访问的数据页保存在缓冲中以提高性能。

图6.3 “消息”选项卡

5. 切换到“执行计划”选项卡。在执行计划中，如图6.4所示，可以看出，SQL Server使用一次表扫描操作来访问数据，这是惟一的选择。

图6.4 使用表扫描操作来访问数据

6. 现在对这个查询稍作修改以获取特定的行。

SET STATISTICS IO ON;

SELECT * FROM dbo.Orders

WHERE SalesOrderID =46699;

SET STATISTICS IO OFF;

7. 检查输出的消息和图形化的执行计划。，可以看出SQL Server仍然需要为此查询读取178页并使用表扫描。使用表扫描是因为SQL Server没有索引可用，因此只能在表中扫描所有的数据来找到需要查找的行。

可以看出，在表没有索引的时候，SQL Server会使用表扫描来访问表。这种扫描迫使SQL Server扫描所有的数据，全然不顾表的大小。在非常大的表中，表扫描会花费很长的时间。

6.2.2 表中的索引(1) SQL Server有两种类型的索引：聚集索引和非聚集索引。这两种索引虽然都是平衡树，但是它们的构建方式不同。现在来看看它们有什么区别。

6.2.2.1 聚集索引

聚集索引是一种特殊的平衡树。这种平衡树与前面的平衡树相比，差别在于索引的叶子级。在聚集索引中，叶子级并不包括索引键和指针；它们就是数据本身。这个差异意味着数据并不存储在堆结构中。它们存储在索引的叶子级，并按索引键进行排序。这种设计具有两个优点：

l SQL Server不需要依据指针来访问数据。数据直接存储在索引中。

l 数据依据索引键排序，这是主要的优点。无论什么时候，只要SQL Server需要依据索引键排序数据，都不必再执行排序操作，因为数据已经排好序了。

由于数据包含在聚集索引之中，因此只能为每个表定义一个聚集索引。以下语法用于创建一个聚集索引：

CREATE [ UNIQUE ] CLUSTERED INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )

可以看出，可以定义索引为“unique”(惟一的)，这意味着两个行不能有相同的索引键值。

注意 SQL Server在一个表上创建主键或惟一性约束的时候会创建一个惟一索引。在主键定义后，如果在表中还没有定义聚集索引，SQL Server会默认为这个主键建立聚集索引。创建主键或者惟一约束时定义的索引的类型可以这样指定：在CREATE或ALTER TABLE 语句中使用CLUSTERED或NONCLUSTERED 关键字。

Ø 创建并使用聚集索引

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 键入并执行以下语句在Orders表上创建一个聚集索引。此示例的代码包含在示例文件CreatingAndUsingClusteredIndexes.sql中。

CREATE UNIQUE CLUSTERED INDEX CLIDX_Orders_SalesOrderID
ON dbo.Orders(SalesOrderID)

3. 现在再执行前面执行过的两个SELECT语句来检验区别。要保证在执行时包括实际的执行计划。

SET STATISTICS IO ON;
SELECT * FROM dbo.Orders;

SELECT * FROM dbo.Orders
WHERE SalesOrderID =46699;

SET STATISTICS IO OFF;

4. 切换到“执行计划”选项卡。

图6.6 “执行计划”选项卡

可以看出，这里的SQL Server不再使用表扫描。现在，由于数据不再存储在堆结构中，因此SQL Server执行了索引操作。执行计划表明将使用两个主要的索引操作，它们是：

l 索引扫描读取表中的所有数据，通过索引的叶子级所进行的扫描。由于第一个SELECT语句没有WHERE子句，因此SQL Server知道需要获取所有数据，这些数据存储在索引的叶子级上。

l 索引查找 SQL Server查找特定值的一个操作。这个操作起始于索引的根并将查询值传递给索引的分支以进行查询。

这两个操作同样可以组合起来获取一个特定范围的数据。在这种局部扫描操作中，SQL Server会首先找到范围的起始值，然后持续扫描到范围的终点值结束。

5. 切换到“消息”选项卡，如图6.7所示。

图6.7 “消息”选项卡

可以看出，第一个SELECT语句执行了与使用堆结构进行表扫描时相同的页读取操作。这并不奇怪，因为SELECT语句要求获取所有数据。因此SQL Server不得不获取所有数据。但是，第二个SELECT语句只进行了两次页读取，这比前面有了很大的改进，少读了178页。SQL Server只需在索引上进行查找，相较于在所有数据页上进行扫描，它需要的I/O操作要少得多。

6. 键入以下SELECT语句，它会按顺序获取数据。图6.8显示了执行结果。然后按“Ctrl+L”来获取估计的执行计划。

SELECT * FROM dbo.Orders
ORDER BY SalesOrderID;

SELECT * FROM dbo.Orders
ORDER BY OrderDate;

可以看出，第一个语句只执行了一次聚集索引扫描而并没有对数据进行排序。这是因为这个列是一个聚集索引键列，数据已经根据SalesOrderID进行了排序。因此，SQL Server为了按顺序获取行只需要在叶子级扫描数据并返回结果。

对于第二个查询，数据必须在获取之后进行排序。因此，在聚集索引扫描操作之后有一个在OrderDate列上进行的排序操作。由于排序操作是非常昂贵的操作，因此第二个操作的开销占据了整个查询开销的93%。所以，对于经常需要进行排序的列，最好为其定义聚集索引。经常需要进行聚合分组的列也如此，因为在进行聚合数据的时候，SQL Server首先需要依据分组条件对其进行排序。

图6.8 执行结果

现在要在OrderDetails表上构建一个复合索引。一个复合索引是一个为多个列定义的索引。索引键将依据第一个索引键列进行排序，然后是第二个，依次类推。在使用两个以上的列一起作为条件进行查询的时候，或者在需要通过多列来惟一确定一行的时候，这种索引是非常有用的。

Ø 构建一个复合聚集索引

1. 在SQL Server Management Studio中，键入并执行以下语句在OrderDetails表上创建一个复合聚集索引。

CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails
ON dbo.OrderDetails(SalesOrderID,SalesOrderDetailID)

2. 现在键入以下两个SELECT语句。第一个语句查询SalesOrderID的值为指定值的行，第二个语句查询SalesOrderDetailID值为指定值的行。这两个列都是CLIDX_OrderDetails索引的索引列。按“Ctrl+L”来显示估计的执行计划。

SELECT * FROM dbo.OrderDetails
WHERE SalesOrderID = 46999

SELECT * FROM dbo.OrderDetails
WHERE SalesOrderDetailID = 14147

可以看出，在第一个搜索复合索引第一个列的值的查询中，SQL Server使用一个索引查找来找到特定的行。在第二个查询中，它使用一个非常昂贵的索引扫描操作。使用这个索引扫描操作是因为不可能只依据复合索引第二个列找到值，因为这个索引起初是根据第一个列来进行排序的。因此，安排复合索引中索引列的顺序是非常重要的。记住，复合索引只适用于附加列与第一个列经常被组合查询或需要通过几个列来强制惟一性的时候。

6.2.2 表中的索引(2)

与聚集索引相比，非聚集索引并不在索引的叶子级包含所有数据行。相反，它在叶子级包含所有键列和指向表中行的指针。指针的编写及使用方式取决于表是一个堆还是一个有聚集索引的表。

l 堆如果表没有聚集索引，SQL Server将在非聚集索引的叶子级存储一个指向物理行的指针(文件id、页id合页中的行id)。在这种情况下，SQL Server通过查询索引进而依据指针指向来获取行的方式查找一个特定的行。

l 聚集索引当一个聚集索引存在的时候，SQL Server会在非聚集索引的叶子级将此行的聚集索引的键存储为指针。如果SQL Server要根据非聚集索引获取一行，会在非聚集索引中进行查找，找出合适的聚集键，然后再通过聚集索引来获取行。

由于非聚集索引并不包括整个数据行，因此在一个表上可以建立多达249个的非聚集索引。创建非聚集索引的语法与创建聚集索引的语法非常相似：

CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )

Ø 创建并使用非聚集索引

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 键入并执行以下SELECT语句并按“Ctrl+L”来显示估计的执行计划。此示例的代码包含在示例文件CreatingAndUsingNonclusteredIndexes.sql中。

SELECT DISTINCT SalesOrderID, CarrierTrackingNumber
FROM dbo.OrderDetails
WHERE ProductID = 776

由于ProductID列没有索引，因此SQL Server执行了一次聚集索引操作。为了加速这个查询，SQL Server需要ProuctID列有一个索引。由于在OrderDetails表上已经定义了一个聚集索引，因此必须使用非聚集索引。

注意为了使结果惟一，这个执行计划使用了排序操作。

3. 键入并执行以下语句在OrderDetail 表的ProductID列创建一个非聚集索引。

CREATE INDEX NCLIX_OrderDetails_ProductID
ON dbo.OrderDetails(ProductID)

4. 使用前一个SELECT语句并按“Ctrl+L”来显示估计的执行计划。图6.9显示了执行结果。

SELECT DISTINCT SalesOrderID, CarrierTrackingNumber
FROM dbo.OrderDetails
WHERE ProductID = 776

将鼠标指针移到“索引查找”操作上方，会看到SQL Server在NCLIX_OrderDetails_ProductID上执行一个索引查找来获取实际的行。由于这张表存在聚集索引，它获取聚集键的列表作为指针。这个列表是嵌套循环操作的输入。嵌套循环操作是联接操作(联接将在本章后文讨论)的一种类型。嵌套循环操作在聚集索引上通过使用一个索引查询来获取数据的实际行，这些行随后被传送到惟一排序操作上以使结果惟一。在存在聚集索引的情况下，SQL Server就是这样借助于非聚集索引获取行的。

5. 现在来看一下SQL Server如何在存在非聚集索引而没有聚集索引的表上访问数据的。键入并执行以下DROP INDEX语句来删除OrderDetails表上的聚集索引。

DROP INDEX OrderDetails.CLIDX_OrderDetails

图6.9 执行结果
6. 键入与前面一样的SELECT语句并按“Ctrl+L”来显示估计的执行计划。图6.10显示了执行结果。

SELECT DISTINCT SalesOrderID, CarrierTrackingNumber
FROM dbo.OrderDetails
WHERE ProductID = 776

图6.10 执行结果

可以看出，由于SQL Server 通过索引所获取的指针是指向物理数据行的指针而不是聚集键，因此SQL Server现在使用一个RID查找操作来获取行。SQL Server用RID查找操作是SQL Server直接在页上获取行。
7. 键入并执行以下语句来再次创建聚集索引。

CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails
ON dbo.OrderDetails(SalesOrderID,SalesOrderDetailID)

6.2.2.3 使用包含性索引

在使用非聚集索引的时候，SQL Server并不总是需要在第二个步骤获取整个数据行。这种情形发生在非聚集索引包括SQL Server执行操作所需的所有表数据的时候。此时，便将这个索引称为包含性索引，因为它包含整个查询。包含索引可以极大地加速查询，正如前一个示例的两个执行计划中一样。在这些查询中，获取实际数据行的操作占有整个查询开销的97%。换一句话说，如果没有这个操作，查询速度将提高32倍。让我们来看一下包含性索引是如何工作的。

Ø 使用包含性索引

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 一个包含性索引的意思是它会包含查询所需的所有数据。仔细看看前一个例子中使用的查询(如下所示)，可以看出SQL Server需要SalesOrderID，CarrierTrackingNumber和ProductID列。

由于以前创建的非聚集索引NCLIX_OrderDetails_ProductID创建在ProductID列上，因此它包括了这个列。同时，由于SalesOrderID列是聚集索引的键，因此它还包括了SalesOrderID列。因此，SalesOrderID是SQL Server在非聚集索引上使用的指针。如此一来，SQL Server只能通过查找聚集索引获取数据的方式获取CarrierTrackingNumber。在第二个查询中，CarrierTrackingNumber并不在SELECT列表中。我们可以通过在启用“包含实际执行计划”后键入并执行以下语句来看一下区别。此示例的代码包含在示例文件UsingCoveredIndexes.sql中。

SET STATISTICS IO ON
--not covered
SELECT DISTINCT SalesOrderID, CarrierTrackingNumber
FROM dbo.OrderDetails
WHERE ProductID = 776

--covered
SELECT DISTINCT SalesOrderID
FROM dbo.OrderDetails
WHERE ProductID = 776
SET STATISTICS IO OFF

图6.11中，可以看出，对于第二个查询，在TrackingNumber列没有被选择的时候，由于索引包含查询，因此SQL Server不需要访问聚集索引来获取数据。为每一个行访问聚集索引的开销很大，因此第二个查询只占用了这一批整个开销的1%。从“信息”选项卡中可以看出，和第一个查询所需的709次页读操作相比，对于包含性查询，SQL Server只进行了两页读操作。

由此可见包含性索引的重要作用。当然，我们无法估计查询结果所需要的列。但一般原则是，只获取真正需要的列，最大限度地利用包含性索引规则。为了图方便，在SELECT语句中使用*可不是一个好的习惯。

3. 假设在查询中需要CarrierTrackingNumber，并且由于性能的原因要使用包含性索引。SQL Server 2005允许在一个非聚集索引中包括其他列。所包括的列将与索引键一起存储在非聚集索引的叶子级，以便在不访问聚集索引的情况下获取它们。为了在非聚集索引中包括CarrierTrackingNumber，需要键入并执行以下语句来删除索引，进而再次创建包含有其他列的索引。

DROP INDEX dbo.OrderDetails.NCLIX_OrderDetails_ProductID
CREATE INDEX NCLIX_OrderDetails_ProductID
ON dbo.OrderDetails(ProductID)
INCLUDE (CarrierTrackingNumber)

图6.11 包含性查询的结果
4. 执行先前没有包含的查询看它是否已经被包含了。
SET STATISTICS IO ON
SELECT DISTINCT SalesOrderID, CarrierTrackingNumber
FROM dbo.OrderDetails
WHERE ProductID = 776
SET STATISTICS IO OFF

从执行计划可以看出，它不再需要访问聚集索引了。在“信息”选项卡中可以看出，和以前进行了709次页读取操作相比，现在的查询只需要5次页读取操作。

注意在数据发生更改的时候，包含的列会加大SQL Server的开销。这是因为在发生数据更改的时候，SQL Server需要更改每个索引并且它在数据文件中会占用更多的空间。因此，在非聚集索引中包含列虽然可以提高查询性能，但为应用程序中的每个查询创建包含列的索引并不是一个好做法。这个特性只能选择地用于加速有性能问题的查询。

6.2.2 表中的索引(3)

6.2.2.4 计算列上的索引

dbo.OrderDetails表有一个计算列LineTotal。这个列的值由以下公式计算得出，代表一行的LineTotal。

每次访问这个列的时候，SQL Server会根据公式引用的原始行的值计算出这个列的值。虽然在SELECT子句中使用LineTotal列并不是个问题，但如果在一个WHERE子句或者一个聚合函数(例如MAX或MIN)中使用该列作为谓词，就会有问题了。当计算列用于查询的时候，SQL Server不得不为表中的每一行计算相应的值然后再在结果中查找出需要找的行。由于总是需要进行表或完整聚集索引扫描，因此这个过程非常低效。针对这种类型的查询，可以在计算列上通过建立索引的方式来解决这个问题。计算列如果建有索引，SQL Server会事先计算出结果并对计算出的结果建立索引。

注意在计算列上建立索引是有一些限制的。最主要的限制是，计算列必须具有确定性，且必须精确。有关这些限制的更多信息，可以参见SQL Server Books Online 主题“Creating Indexes on Computed Columns”。

Ø 在计算列上创建及使用索引

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 假设希望获取所有LineTotal 具有指定值的SalesOrderID。键入以下查询并按Ctrl+L查看在计算列上没有索引支持情况下的估计执行计划。图6.12显示了执行结果。可以看出，SQL Server需要进行一次聚集索引扫描，然后计算列的值，随后筛选出需要的行。此示例的代码包含在示例文件IndexesOnComputedColumns.sql中。

图6.12 执行结果

3. 键入并执行以下CREATE INDEX语句在计算列上建立一个索引。

CREATE NONCLUSTERED INDEX NCL_OrderDetail_LineTotal

ON dbo.OrderDetails(LineTotal)

4. 选中第一个查询并按“Ctrl+L”查看在计算列上有索引后的新执行计划。如图6.13所示，SQL Server使用新建的索引来获取数据，使查询速度比以前快了许多。

SELECT SalesOrderID

FROM OrderDetails

WHERE LineTotal = 27893.619

5. 关闭SQL Server Management Studio。

图6.13 执行结果

6.2.2.5 XML列上的索引

SQL Server 2005具有本地XML数据类型。XML数据类型的实例作为二进制大型对象(BLOBs)存储并且其大小可以大到2 GB。XML数据可以通过XQuery进行查询，但在没有索引的情况下，查询XML数据类型会非常耗时间。由于SQL Server需要在运行期间分割包括XML的二进制大型对象来评估查询，因此这种情况在大的XML实例上会表现得尤为明显。

为了提高XML数据类型的查询性能，可以对XML列进行索引。XML索引可以分为两种类型：主XML索引及辅助XML索引。

6.2.2.6 创建并使用主XML索引

在XML列创建的第一个索引是主XML索引。在创建这个索引的时候，SQL Server分割XML内容并创建几个包含元素及属性名、到根节点的路径、节点类型及值等信息的数据行。有了这些信息，SQL Server可以更简单地支持XQuery请求。要创建一个主XML索引，基本表上必须有一个进行聚集索引的主键。建立主XML索引的语法如下所示：

CREATE PRIMARY XML INDEX index_name

ON <object> ( xml_column_name )

Ø 创建一个主XML索引

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 键入并执行以下语句来创建一个供以下过程使用的表。此示例的代码包含在示例文件CreatingAndUsingPrimaryXMLIndexes.sql中。

3. 使用以下CREATE INDEX语句在表Production.ProductModel 的列CatalogDescription创建一个主XML索引。

CREATE PRIMARY XML INDEX PRXML_Products_CatalogDesc

ON dbo.Products (CatalogDescription);

4. 以下语句使用一个XQuery方法来获取在XML文档中具有特定路径的XML数据。键入并执行这个语句，注意包括实际的执行计划。

5. 在图6.14显示的执行计划中，可以看出SQL Server使用XML列的索引来找到需要查找的行并通过一个嵌套循环操作来获取实际的行数据。SQL Server随后对聚集索引使用了一次索引查找，方式与前面的非聚集索引相同。

图6.14 使用XML列的索引进行查找

6. 关闭SQL Server Management Studio

6.2.2.7 辅助XML索引

由于XML数据的分割，主XML索引可以提高XQuery的性能。虽然如此，但SQL Server仍然需要扫描分割后的数据找出被查询的数据。为了进一步提高查询的性能，在主XML索引的基础上还可以创建辅助XML索引。辅助XML索引有三种类型，每一种类型都在XML列上支持特定的查询类型，提供在特定情形下创建它所需的索引类型的功能。这三种辅助XML索引是：

l PATH辅助XML索引使用.exist方法时，这种索引可用于判断一个特定的路径是否存在。

l VALUE辅助XML索引在完整路径未知或包含通配符时，可以用此索引执行一个基于值的查询。

l PROPERTY辅助XML索引在通向值的路径未知的时候，此索引可用于获取值。

创建辅助XML索引的一般语法是：

CREATE XML INDEX index_name

ON <object> ( xml_column_name )

USING XML INDEX xml_index_name

FOR { VALUE | PATH | PROPERTY }

Ø 创建并使用辅助XML索引

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 键入并执行以下语句在XML列CatalogDescription创建一个辅助path, value和property索引。此示例的代码包含在示例文件CreatingAndUsingSecondaryXMLindexes.sql中

3. 键入以下使用XQuery方法的语句，按“Ctrl+L”显示估计的执行计划。检验SQL Server在使用这些新建索引时的不同处理方式。

4. 关闭SQL Server Management Studio。

6.2.3 视图索引

一个没有任何索引的视图不需要任何存储空间。当一个语句使用它的时候，SQL Server会将视图的定义与语句合并，并对其进行优化，生成有关执行计划并获取数据。在视图处理或联接很多行的时候，这个过程的开销会很大。在这种情况下，如果视图经常被请求，那么对其进行索引可以大大提高查询性能。

当视图被索引的时候，会像一个聚集索引的表一样被处理并且其索引结果会存在一个数据文件中。在基表数据发生更改的时候，SQL Server会自动维护这个索引。视图索引会显著提高对视图数据的访问速度，但在基表数据发生变化的时候，维护视图上的索引会增加额外的开销，这是肯定的。因此，在视图处理多行数据或与聚合函数同时使用的时候，或者在基表中的数据并不经常改变的时候可以考虑使用索引视图。

在SQL Server 2005 Enterprise，Developer或Evaluation Edition中，索引视图可以加速没有直接引用视图的查询。例如，如果要处理的查询包括一个聚合函数，SQL Server优化器发现一个索引视图已经包含这个聚合，就会从索引获取聚合结果而不必重新计算它。

可以通过以下步骤创建一个索引视图。

Ø 创建一个索引视图

1. 使用SCHEMABINDING子句来创建一个视图。这个视图必须符合许多要求。例如，它只能引用同一数据库中的基表。所有引用的函数必须是确定性的；行集函数、派生表和子查询都不能在索引视图中使用。创建索引视图的完整要求列表可以在SQL Server Books Online 的主题“索引视图”中找到。

2. 在视图上创建一个惟一索引。这个索引的叶子级由视图的完整结果集组成。

3. 在聚集索引的基础上根据需求创建非聚集索引。非聚集索引可以按平常的方式创建。

Ø 创建并使用索引视图

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 键入并执行以下语句创建一个视图以通过分组订单的月份来聚合LineTotal。此示例的代码包含在示例文件CreatingAndUsingIndexedViews.sql中。

3. 键入并执行以下SELECT语句。在“信息”选项卡中可以看到SQL Server在执行此语句的过程中需要进行几乎1000次的页读操作。

4. 键入并执行以下CREATE INDEX语句在vOrderDetails视图创建一个惟一聚集索引。

CREATE UNIQUE CLUSTERED INDEX CLIDX_vOrderDetails_Year_Month
ON dbo.vOrderDetails(Year,Month)

5. 执行以下SELECT语句。注意，由于结果数据已经被计算并存储在索引中，因此SQL Server现在只需要进行两次页读操作。

6. 如果安装的是SQL Server 2005 Enterprise，Developer或Evaluation版，键入并执行以下这个没有引用视图的SELECT语句，然后按“Ctrl+L”来查看估计的执行计划，如图6.15所示。

图6.15 查看估计的执行计划

7. 以上查询计划表明，SQL Server对视图使用聚集索引来获取数据。因为这样可以更加高效地通过合计视图中找到的月聚合来创建YearTotal聚合。因此可以看出，现在的索引视图可以在不记录查询本身的情况下提高查询速度。

8. 关闭SQL Server Management Studio。

6.2.4 加速联接操作的索引

联接操作用于将表或者中间结果联接在一起。SQL Server使用三种类型的联接操作：

l 嵌套循环联接这种联接使用一个联接输入作为内部输入表，另一个联接输入作为外部输入表。嵌套循环会在每一个内部输入行进行一次并在外部输入中搜索相应的行。当外部输入联接条件的列上有索引的时候，SQL Server可以使用索引查找在外部输入中查找行。如果不存在索引，SQL Server就只好根据内部输入的每一个行，使用扫描操作在外部输入中找出匹配的行。嵌套循环常用于内部输入的行数较少的情况，因为此时这种联接方式最高效。

l 合并联接这种联接适用于联接输入在其联接列上已排好序时。在合并联接操作中，SQL Server对已排序的输入进行一次扫描，然后将数据合并在一起。合并联接非常高效，但数据必须被提前排序，这意味着在联接列上必须有索引。如果联接列上没有索引，SQL Server会将输入行先排序。但是这种情况并不经常发生，因为排序数据工作的效率通常都较低。

l 哈希联接这种联接用于海量、未排序、没有索引的输入。哈希联接在联接列上使用哈希操作来将输入联接在一起。为了计算并存储哈希操作的结果，SQL Server所需要的内存和CPU时间都比其它联接操作多。

Ø 检验联接操作

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 键入并执行以下语句，不要忘了包括实际的执行计划。此示例的代码包含在示例文件ExaminingJoinOperations.sql中。这个语句的执行计划如图6.16所示。可以看出，SQL Server使用一个索引查找来获取内部输入的行(来自OrderDetails表的数据)，然后使用一个嵌套循环联接操作。因为此时在外部输入(来自Orders表的数据)中只有一个匹配行。由于匹配的索引存在，因此外部输入的匹配行同样通过索引查找获取。还可以在“消息”选项卡中看到，SQL Server只需要5次页读操作来获取数据。

3. 更改查询获取更多的SalesOrderID。对此，由于在索引中有排序的行并且内部输入的行较多，因此SQL Server会使用一个合并联接。键入并执行以下语句。可以看出，SQL Server需要19次页读操作来执行这个查询。

图6.17 执行计划

SELECT o.SalesOrderID, o.OrderDate, od.ProductID
FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
ON o.SalesOrderID = od.SalesOrderID
WHERE o.SalesOrderID BETWEEN 43659 AND 44000

4. 键入并执行以下批操作，它将联接所需要的索引删除。

DROP INDEX CLIDX_Orders_SalesOrderID
ON dbo.Orders

DROP INDEX CLIDX_OrderDetails
ON dbo.OrderDetails

5. 查询执行刚才执行的SELECT语句(如下所示)并检查执行计划及I/O操作发生了什么变化。如图6.18所示。

前一个查询计划表明，由于第一个查询联接的内部输入很小，因此SQL Server对其再次使用了嵌套循环联接。而对于第二个联接，由于其数据输入不再是已排序的，因此SQL Server对其使用了哈希联接。由于没有可用的索引，SQL Server对于前面的查询只能对基表进行表扫描，这两个查询共请求了1000次页读操作。

通过本小节可以了解到，具有能提供支持的索引对于联接操作是非常重要的。最为一个通用的规则，由于外键约束几乎是所有查询的联接条件，因此它们都应该被索引。

图6.18 执行计划

6.2.5 数据分布和统计

在最后一个示例中，将看到SQL Server会基于联接的输入大小选择不同的联接操作符。同样，对于其他操作，例如索引查找或扫描，SQL Server需要知道操作会牵涉到多少行，并以此来决定最佳的操作符。由于SQL Server需要在实际访问数据之前做出决定，因此这个决定需要以统计信息做为依据。SQL Server使用如下详细步骤以列为基础自动创建并更新这些统计信息。

1. 一个查询提交至SQL Server。

2. SQL Server查询优化器首先确定需要访问哪些数据。

3.    SQL Server查看需要访问的列的统计信息。
l          如果统计信息已经存在并且是最近的，SQL Server继续执行。
l          在没有统计信息的时候，SQL Server会生成新的统计信息。
l          在统计信息存在但已过时的时候，SQL Server为数据计算新的统计信息

4. SQL Server查询优化器生成查询计划。

这些是SQL Server的默认行为，对于多数数据库来说这是最佳的选择。可以使用ALTER DATABASE语句来告知SQL Server它应该异步更新统计信息。这意味着在生成执行计划的时候它不必等待新的统计信息。当然，这也意味着生成的查询计划会根据老的统计信息生成，因此可能不是优化的。在特定情况下，用户希望能够人工生成并更新统计信息。这可以通过CREATE STATISTICS 或UPDATE STATISTICS语句来实现。同样地，可以在数据库级将ALTER DATABASE语句执行时进行的自动索引创建和更新这个功能关闭。由于默认的行为是大多数情况下的最佳工作方式，因此所有这些选项应该在特定的情况下使用。有关这些选项的详细信息，可以访问http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx，阅读白皮书“Statistics Used by the Query Optimizer in Microsoft SQL Server 2005”。

Ø 查看数据分布统计

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 为了获取已有的统计信息，可以查询sys.stats和sys.stats_columns视图。键入并执行以下查询找出dbo.OrderDetails表的有关统计信息。此示例的代码包含在示例文件DataDistributionStatistics.sql中。

查询结果中包含统计信息的名称及其计算的列。索引列统计信息的命名与索引相对应。名称以_WA_Sys_开头的索引信息是SQL Server自动为没有索引的列创建的统计信息。

3. DBCC SHOW_STATISTICS语句可以用于从一个获取统计信息记录中获取统计信息。键入并执行以下语句来显示LineTotalcolumnofthedboOrderDetails表的统计信息。

DBCC SHOW_STATISTICS('dbo.OrderDetails', 'NCL_OrderDetail_LineTotal')

图6.19显示的结果面板显示了DBCCSHOW_STATISTICS语句输出的一部分。第一部分显示了常规信息，例如创建日期、表中的行数以及多少行被取样。数据密度信息也显示出来了。密度是一个显示列中值的密度的值。除了这个常规信息，SQL Server会定义数据的范围，将其称作步骤异为这些步骤存储其分布的统计信息。基于这些分布信息，SQL Server可以在使用特定值所属范围的统计信息来查找特定值的时候估计出受影响的行数。对于每一个步骤，SQL Server存储以下信息。

图6.19 数据分布统计

l          RANGE_HI_KEY是步骤的上限值。
l          EQ_ROWS是与步骤的上限值相等的样本中的行数。
l          RANGE_ROWS是步骤范围内的行数，不包括上限。
l          DISTINCT_RANGE_ROWS步骤范围内非重复值的数目。
l          AVG_RANGE_ROWS步骤范围内重复值的平均数目。

4. 关闭SQL Server Management Studio。

6.2.6 索引碎片

一个聚集索引创建后，数据按顺序存储在叶子级。在非聚集索引中，索引键按顺序存储在叶子级上。为了在叶子级上获得最大的性能，索引的页会在逻辑和物理上进行排序。SQL Server依据恰当的物理顺序来存储这些索引页以在磁盘上获得尽可能快的读取速度。从磁盘上读取数据的过程中，开销最大的过程是移动磁盘臂。如果需要读的数据页顺序存储，那么磁盘臂移动的次数就会降到最小，读取性能因而得到优化。

数据插入表中时，会被存储在聚集索引叶子级页的特定页上。非聚集索引的索引键也需要插入非聚集索引叶子级页的正确页。如果这个页满了，SQL Server会执行一次页拆分，意味着SQL Server会分派一个新页并将这个新页与正确的索引相连接。这种情况将导致索引碎片，也就是说数据页的逻辑顺序将不再与物理顺序相匹配。UPDATE和DELETE语句同样会导致索引碎片。

为了减少索引碎片，在索引创建时可以使用FILLFACTOR选项。这个选项定义了在索引创建时索引的叶子级页应该被填充的比例。FILLFACTOR越低，叶子级页在不被拆分的情况下容纳的条目更多，碎片发生的可能性因而越低。然而，较低的FILLFACTOR会使每一个叶子级页的初始存储数据变少，因而会产生较大的索引。

如果被索引的表不是只读表，它的索引早晚会产生碎片。具有碎片的索引可以使用语句ALTER INDEX进行碎片整理以提高数据访问速度。碎片整理有两个选项：

l REORGANIZE 重新组织索引意味着使用冒泡排序操作排序叶子级页。REORGANIZE只排序数据页，并不是页中的所有条目，这意味着FILLFACTOR不能与重新组织同用。

l REBUILD 重建索引意味着这个索引将被重新构建。这个操作比重组织操作所花的时间更长，但效果更好。FILLFACTOR选项可以与其共同使用来以期望的百分比再次填充页。如果没有FILLFACTOR选项，叶子级页将被完全填充。ONLINE选项同样可以在重建索引时使用。如果不使用ONLINE选项，创建将在脱机状态下执行，这意味着表在创建期间会被锁定。脱机创建比联机重建快，但由于它会锁住表，因此在需要访问表中数据的时候不能使用脱机重建。

Ø 维护索引

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 使用行集函数sys.dm_db_physical_stats来获取碎片信息。键入并执行以下语句来获取碎片大于50%的索引列表。以下代码包含在示例文件IndexFragmentation.sql中。

3. 键入并执行以下语句来对索引PK_Employee_EmployeeID执行一次脱机重建。

4. 关闭SQL Server Management Studio。

注意对索引进行碎片整理的过程一般需要自动运行。具体做法是使用维护计划或使用SQL Server代理来计划运行自己写的脚本(参见SQL Server Books Online的“How to: Create a Maintenance Plan”的主题)。

6.3 用“数据库引擎优化顾问”来优化查询

为数据库项目创建正确索引并不简单。需要考虑许多因素：

l          数据库的数据模型
l          表中数据的数量和分布
l          对数据库执行哪些查询
l          查询发生的频率
l          数据更新的频率

为了帮助我们设计索引，SQL Server提供了一个称为“数据库引擎优化顾问”的工具。数据库引擎优化顾问需要一个工作负荷文件，该文件可以是包含需要优化的语句的一个文本文件，也可以是SQL Server Profiler生成的一个跟踪文件。随后，“数据库引擎优化顾问”会使用SQL Server 查询优化器和已有的数据库为数据库物理设计结构提出更改建议，例如创建、更改或删除各种索引。

Ø 使用“数据库引擎优化顾问”

1. 打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2. 键入并执行以下语句，将使用这些语句来通过“数据库引擎优化顾问”优化数据库。以下代码包含在示例文件UsingDatabaseEngineTuningAdvisor.sql中。

3. 为了将这段脚本存为一个工作负荷文件，需要打开“文件”菜单并选择“另存为”并指定文件名称为dta.sql。

4. 在SQL Server Management Studio中，在“工具”菜单上选择“数据库引擎优化顾问”。连接到SQL Server实例。

5. 选择在步骤3存储的文件作为工作负荷文件并选择数据库“AdventureWorks”作为要优化的数据库。如图6.20所示。

图6.20 选择要优化的数据库

6. 按工具条上的“开始分析”按钮。

7. 在分析完成之后，窗口中将显示建议，如图6.21所示。

8. “数据库引擎优化顾问”建议创建两个索引。可以在“操作”菜单中选择“保存建议”来保存生成索引的脚本。

9. 关闭“数据库引擎优化顾问”。

可以看出，SQL Server会尽可能地优化这两个查询。只有在这两个查询应该被优化并且对它们的优化对数据库的其他操作没有什么影响的时候，才能体现出查询优化的好处。为了优化所有的数据库索引，最好使用SQL Server Profiler 追踪，它会为“数据库引擎优化顾问”提供一个数据库的常规工作负荷。借助于这些信息，“数据库引擎优化顾问”可以根据数据库中的其他负荷来优化查询。在分析完工作负荷后，务必保存并查看建议。

图6.21 显示建议

小结

在本章中，学习了SQL Server如何存储并访问索引或未被索引的数据。通过分析执行计划和I/O统计信息，已经认识到正确的索引对于优化性能的重要性。同时还学习了何时以及如何使用和维护不同的索引类型(在表6.1中区别列出)。

表6.1 索引类型

可以看出，找到正确的索引设计是很重要的。“数据库引擎优化顾问”可以为构建索引设计提供巨大的帮助。

nlst命令（ncl命令）

6.1 了解执行计划

6.2.2.1 聚集索引

6.2.2.3 使用包含性索引

6.2.2.4 计算列上的索引

6.2.2.5 XML列上的索引

6.2.2.6 创建并使用主XML索引

6.2.2.7 辅助XML索引

相关推荐