续第二章：分析基本查询的图形执行计划表连接

到目前为止，我们介绍了单张表的例子，下面将引入一些新的元素：连接。分析以下查询：

SELECT   e . [Title],
        a. [City] ,
        c. [LastName]  +   ', '  +  c. [FirstName] AS EmployeeName
FROM    [HumanResources]. [Employee] e
JOIN [HumanResources]. [EmployeeAddress] ed  ON e. [EmployeeID]
=  ed . [EmployeeID]
JOIN [Person]. [Address] a ON [ed]. [AddressID] =
[a] . [AddressID]
         JOIN [Person]. [Contact] c ON e. [ContactID] =
c . [ContactID]

其执行计划如下所示：

从上图可以看出，根据每个运算执行开销不同，查询有多个运算步骤构成。我们可以找出执行开销最大的前3个运算，由大至小介绍：

l Person.Address表的“索引扫描”（45%）

l HumanResources.EmployeeAddress和Person.Address之间的Hash Match连接运算(28%)

l Person.Contact表的“索引查找”（18%）

下面我们来逐一分析这3种运算符，从上图的最右端，首先针对HumanResources.EmployeeAddress表的的“索引扫描”，然后是另外一个Person.Address表的“索引扫描”操作。后者的运算开销最大，通过“工具提示”窗口可以知道，该操作使用索引为IX_Address_AddressLine_AddressLine2_City_StateProvincedID_PostalCode，存储引擎需要遍历19614行来查找所需要的数据行。

从输出列看出，查询优化器需要获取AddressID和City列，依据这两列的索引选择性，查询优化器计算最佳获取数据的方式是通过索引获取，其开销占45%。估计运算开销是查询优化器执行某个运算时计算出的一个数来评估某个运算的相对开销，该数越低，其性能越好。

哈希匹配（连接）

接着上面的例子，紧接着是两个“索引扫描”通过“哈希匹配连接”进行合并，即执行计划中第2个开销最大的运算，如下图所示：

在讨论“哈希匹配连接”前，我们需要了解两个新的概念：hashing和hash table，hashing是一种编程技术，它将数据转换成一种易于搜索的符号形式，例如，表中的每一行数据都可以通过转换成表示行内容的唯一值，它有点类似于将数据加密的形式，hashed value可以转换回原来的数据，hashing通常用于将数据转换成更易于搜索的数据格式。

换句话说，哈希表其实是一种数据结构，它将所有元素的数据按等大小进行分类以使得快速访问这些元素，哈希函数可以决定这些元素归为哪一类，例如，从表中取一行，并将该行做哈希运算，将其哈希值存到哈希表中。

现在我们了解了这些术语，哈希匹配连接发生在以下情况：对连接的两张表的小表的行进行哈希，然后将这些数据存入哈希表，然后根据哈希表中的值逐行来处理大表中的数据，来查找连接匹配的那些行数据，由于小表为哈希表提供了值，表的大小保持最小，因为哈希值代替了真实的值，可以进行快速的比较，只要哈希过的表相对较小，这是一个很快的处理过程；换言之，若两张表都很大，相对于其它连接类型，哈希匹配连接的效率并不是有效的。

在本例中，来自HumanResources.EmployeeAddress.AddressID与Person.Address表中的数据匹配。对于大数集，哈希匹配连接通常是效率较好的，尤其其中的一个表较小，另外，哈希匹配连接也适用于那些在连接的列上没有排序的情况；例如，当在执行计划中看到“哈希匹配连接”时，将会提供以下提示：

l 缺失或不正确的索引

l 缺失WHERE子句

l WHERE子句的条件存在计算或类型转换（即非SARG）意味着不会使用现有的索引

对查询优化器而言，连接两张表最有效的方式是采用“哈希匹配连接”，这并不代表没有其他的连接方法，例如通过添加适当的索引、添加WHERE子句来限制返回数据的个数；换句话说，当你看到“哈希匹配连接”时，这是一个很好的暗示，来判断连接运算是否可以进行优化，如果可以，那更好，否而，就没有需要做的。

在本例中，由于估计的行数（约282行）与实际的行数（290行）相差不大，可以忽略不计，然而对于相差较大的，可能需要更新统计，这也是估计计划与实际计划不同之处的原因。

聚集索引查找

在“哈希匹配连接”之后，是在Person.contact表执行的聚集索引查找运算，另外，PK_Contact_ContactID既是主键又是聚集索引，也是计划中第三个开销较大的运算。

从上图的“Seek Predicates”看，该运算是连接HumanResources.Employee表和Person.Contact表的ContactID列。

嵌套循环连接

紧接着“聚集索引查找”是“嵌套循环连接”运算，如下图所示 ：

计算标量

在“SELECT”运算右边的是“计算标量”运算，如下图所示：

由于返回的结果集中存在别名为“EmployeeName”的定义，故产生了标量运算，其开销为0.0000282.

合并连接

除了“哈希匹配和嵌套循环连接”外，查询优化器可以执行合并连接，下面看一下合并连接的例子：

SELECT c . CustomerID
FROM Sales. SalesOrderDetail od
JOIN Sales . SalesOrderHeader oh
ON od . SalesOrderID = oh . SalesOrderID
JOIN Sales . Customer c
ON oh . CustomerID = c. CustomerID

其执行计划如下所示：

根据执行计划，查询优化器在Customer表上执行“聚集索引扫描”操作，在SalesOrderHeader表上执行“非聚集索引扫描”操作，由于查询中未指定WHERE子句，故采用扫描每一个表获取数据。

接着，Customer表和SalesOrderHeader表通过“合并连接”来连接两个表的行，此外，合并连接要求两个输入表的连接列是排过序的；例如，在合并连接的提示窗口看到，连接列是Sales和CustomerID，在本例，由于连接列的数据都是排序的，因此合并连接是效率较高的连接方式，若连接的列是未排序的，查询优化器可能要对连接列进行排序，然后执行“合并”连接或者执行较少的“哈希连接”。

接着，第三个表使用“哈希匹配连接”来连接前两个表，最终返回连接行数据。

上面讨论的“合并连接”的性能关键在于连接列是否是排过序的，若没有排序，查询优化器会在执行合并连接前对数据进行排序，这说明连接表采用合并连接不是理想的方式或者需要考虑索引。

添加WHERE子句

下面我们对在“表连接”部分的查询进行小小改动，添加WHERE子句来看一下其执行计划:

SELECT  e . [Title],
        a. [City] ,
        c. [LastName] + ', ' + c. [FirstName] AS EmployeeName
FROM [HumanResources]. [Employee] e
JOIN [HumanResources]. [EmployeeAddress] ed ON e. [EmployeeID] = ed . [EmployeeID]
JOIN [Person]. [Address] a ON [ed]. [AddressID] = [a] . [AddressID]
JOIN [Person]. [Contact] c ON e. [ContactID] = c . [ContactID]
WHERE e.Title = 'Production Technician - WC20'

其执行计划如下所示：

从右开始，我们看到查询优化器根据WHERE子句使用“聚集索引扫描”操作，同时WHERE子句也限制行数为22条，可以查看其运算符如下所示：

查询优化器采用可用的统计可以事先进行判断，正如比较返回的“估计行数与实际行数”。

与先前的查询相比，查询优化器可以使用更有效的“嵌套循环连接”，其原因在于在数据初始时使用了WHERE子句。

通常，对不擅长T-SQL的开发人员，最简单的方法就是返回所有行给应用程序，即不添加WHERE子句，本例只是非常简单的查询（有较小的数据集），你可以以此作为例子，从最后的子树开销可以看出，使用WHERE子句时其开销为0.112425，而不使用时，开销为0.400885，试想一下，如果操作的数据集相当大，查询的复杂度也随之增加。

GROUP BY和ORDER BY的执行计划

当查询中加入其它子句时，执行计划中会出现不同的运算。

排序（Sort）

下面来看一个简单的例子：

SELECT *
FROM Production.ProductInventory
ORDER BY Shelf

其执行计划如下所示：

“聚集索引扫描”运算的输出结果作为“Sort”运算的输入，如果在Order BY子句未指定顺序，其默认顺序为升序，可以从Sort提示窗口看出：

如果将鼠标放在“Sort”图标上，会看到有1069行数据将要处理，Sort运算将处理来自“聚集索引扫描”的输出的1069条，并对其排序，最终将结果返回给SELECT运算符。

注意到：排序操作花费了76%，由于排序的列没有索引，所以在查询执行时需要排序运算。

另外其他需要考虑的：排序真的有必要吗？如果没有排序，移除它可以降低负载。有没有可能将数据进行预排序？例如，使用聚集索引进行数据的排序？这并不是万能的，如果的确是，可能需要创建适当的聚集索引来对数据进行排序操作。

如果执行计划中包含多个SORT运算，需要查看是否这些都有必要，否则需要重写代码来减少排序等操作来完成要求。

如果修改以下查询：

SELECT *
FROM Production.ProductInventory
ORDER BY ProductID

其执行计划如下所示：

本例中的查询与先前的查询是相同的，也包含了ORDER BY子句，唯一不同的是，在执行计划中并未看到SORT运算符，其原因是排序的列发生了改变，该列有一个聚集索引，也就是说读取的数据不需要再排序。查询优化器可以识别出数据是否已经排序。如果你没有选择但是需要对数据进行排序，可以考虑使用SQL SERVER 2005的Profiler工具来监测是否有SORT WARNING事件产生。为了提升性能，SQL Server总是尝试在内在中进行排序操作，替代在磁盘上，毕竟在内存中排序要比在磁盘上快得多，但是如果排序的数量较大，SQL Server无法在内存中对这些数据进行排序，取而代之的是将这些数据写入Tempdb数据库，此操作发生时，将会产生Sort Warning事件。如果在服务器上监测到大量排序和Sort Warning事件产生，那就需要为服务器添加更多的内存或加速TEMPDB的访问。

哈希匹配（聚合）

之前我们介绍过连接的“哈希匹配运算”，当查询中有“聚合”运算时，哈希匹配运算也会出现，下面来看一个使用COUNT运算的查询：

SELECT [City] ,
COUNT( [City] ) AS CityCount
FROM [Person]. [Address]
GROUP BY [City]

其执行计划如下所示：

从上面的查询计划，首先是“索引扫描”，由于要返回所有行，也没有WHERE子句来过滤数据，这些行需要进行聚合以便执行COUNT聚合运算，由于查询优化器需要按每一个城市进行统计，所以需要执行哈希匹配运算，注意一点，在哈希运算下方的“Aggregate”，这是区别于连接运算中的哈希匹配运算，同连接中的哈希匹配来一样，哈希匹配运算（聚合）会在内存中创建临时的哈希表来统计那些进行GROUP BY的列的行（本例中为City），一旦结果聚合完毕，其结果回传回给我们。

通常，有聚合的查询的开销是比较大的，提高聚合的速度性能就是使用WHERE来限制聚合的行数。

筛选(Filter)

在上例的查询中添加一个HAVING子句，将使执行计划变得更为复杂：

SELECT [City] ,
COUNT( [City] ) AS CityCount
FROM [Person]. [Address]
GROUP BY [City]
HAVING COUNT(City) >1

其执行计划如下所示：

通过添加HAVING子句，其运算符也添加到执行计划中，它主要限制输出列中City超过1的那些行。另外注意到，哈希匹配运算处理的行数是575条，而FILTER处理了348条。

添加HAVING子句虽减少了返回数据的条数，实际上，它增加了处理查询结果的额外资源，由于HAVING子句仅在聚合运算后才起作用，这降低了性能；正如之前的例子，如果要提高聚合查询的性能，只有一种方法就是在查询中添加WHERE子句来限制处理的行数。