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数据库中的一个数据文件会以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中。

 

USE AdventureWorks

GO

 

CREATE TABLE dbo.Orders(

    SalesOrderID int NOT NULL,

    OrderDate datetime NOT NULL,

    ShipDate datetime NULL,

    Status tinyint NOT NULL,

    PurchaseOrderNumber dbo.OrderNumber NULL,

    CustomerID int NOT NULL,

    ContactID int NOT NULL,

    SalesPersonID int NULL

    );

 

CREATE TABLE dbo.OrderDetails(

    SalesOrderID int NOT NULL,

    SalesOrderDetailID int NOT NULL,

    CarrierTrackingNumber nvarchar(25),

    OrderQty smallint NOT NULL,

    ProductID int NOT NULL,

    UnitPrice money NOT NULL,

    UnitPriceDiscount money NOT NULL,

    LineTotal AS (isnull((UnitPrice*((1.0)-UnitPriceDiscount))*OrderQty,(0.0)))

    );

 

INSERT INTO dbo.Orders

SELECT SalesOrderID, OrderDate, ShipDate, Status, PurchaseOrderNumber,

    CustomerID, ContactID, SalesPersonID

FROM Sales.SalesOrderHeader;

 

INSERT INTO dbo.OrderDetails(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,

OrderQty,

    ProductID, UnitPrice, UnitPriceDiscount)

SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,OrderQty,

    ProductID, UnitPrice, UnitPriceDiscount

FROM Sales.SalesOrderDetail;

 

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

 

SET STATISTICS IO ON;

 

SELECT * FROM dbo.Orders

 

SET STATISTICS IO OFF

 

       SET STATISTICS IO选项会打开一个特性，使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扫描所有的数据，全然不顾表的大小。在非常大的表中，表扫描会花费很长的时间

为了提高数据访问的性能，应该为列定义索引。定义索引的列称作索引键列。在列上建立的索引与一本书的索引类似。它包括在此列上排序好的值以及指向可以找到这些实际数据行所在页的指针。

为了找到具有特定值的索引列所在的行，SQL Server会在索引上查询这个值并依据指针来读取找到的行。相较于使用表扫描时进行的全数据扫描，这个操作简单且节约得多。

SQL Server 中的索引以一种树形结构创建，称作平衡树。图6.1展示了平衡树的一般结构。可以看出，树的最下级称作叶子级。可以将叶子级看作是书的索引。它指出每一数据行的入口，并根据索引行进行排序。为了在索引中快速地找到值，树通过使用<(小于)和>(大于)的比较操作构建。索引级别的数量取决于行的数量以及索引键的大小。在现实中，一个索引页包含的条目比图6.5中的多得多。由于页的大小是8KB, SQL Server可以通过一个索引页指向几百个数据页。因此，即使表包括数百万行的数据，索引一般也没有很多级别。我们可以用这个特性快速地查找指定的数据。