SQL Server 2005技术内幕：查询、调整和优化6——子查询

子查询是强有力的工具，它可以书写许多表达式和许多复杂的查询。有很多不同类型的子查询和很多使用子查询的不同方式。

子查询是基本的连接。然而，一些子查询产生更复杂的连接使用非常不同寻常的连接特性。

在讨论具体例子之前，可以从不同方面对子查询进行分类。子查询采用 3 种计划来进行分类。

l  不相关 vs 相关子查询。不相关子查询不依赖外部查询，可以独立于外部查询而被评估，并且为外部查询的每一行返回相同的结果。只能从外部查询的上下文中来被估计，并且可能为外部查询的每一行返回不同的结果。

l  标量 vs 多行子查询。标量子查询返回或期望返回单一行（也就是单行），然而，多行子查询可能返回一个结果集。

l  有子查询出现的外部查询分句。子查询可以被用于几乎所有的上下文，包括 SELECT 列表和主查询中的 FORM 、 WHERE 、 ON 和 HAVING 子句。

 

不相关标量子查询

 

通过一些简单的不相关标量子查询来讨论子查询。下面的查询返回运费超过所有订单运费平均值的订单列表。

SELECT o1 . [OrderId] , o1 . [Freight]

FROM [Orders] o1

where o1 . [Freight] >

   (

      select AVG ( o2 . Freight ) from [Orders] o2

   )

注意到我们可以提取平均费用计算子句并作为完全独立的查询来执行。因此，这个子句查询不相关。同时，这个子查询使用了标量汇总，因此，返回正好是一行，这个子查询也是一个标量子查询。下面是这种查询计划：

正如你期望的， SQL Server 首先计算嵌套循环连接外面的平均运费来执行这个查询。这个查询需要对 Order 表（ alias[O2] ）执行一次扫描。因为这个查询计算得到精确的一行， SQL Server 然后在内部连接上扫描 Order 表（ alias[O1] ）一次。平均的运费计算结果被子查询（存储在 [Expr1004] 里）用来过滤来自第二次扫描的行。

 

另外，还有一种不相关标量子查询。这次希望找的哦啊那些已经根据名字选择的特定用户的订购订单。

 

SELECT o . [OrderId]

FROM [Orders] o

where o . [CustomerID] >

   (

      select C . [CustomerID] from [Customers] C

      where C . [ContactName] = 'Maria Anders'

   )

 

注意到这次子查询没有标量汇总来保证结果恰好是一行。此外 ContactName 上没有唯一索引，所以这个子查询实际返回多行是完全可能的。然而，该子查询被用在一个相等谓词上下文，是一个标量子查询，且必须返回单行。如果有两个客户都叫“ Maria Anders ”（没有这样的名字），这个查询一定失败。 SQL Server 保证子查询结果最多有一行，这是通过使用流汇总来计算行数的，然后给改计划添加一个断言操作：

如果断言操作符发现子查询返回多于一行（也就是说，如果 [Expr1005]>(1) 是真），将引发错误。

 

当子查询被当用一个表达式时，这是不被允许的。

 

注意到 SQL Server 使用断言操作符来检测其他条件，例如，约束条件（检查，参照完整性等），普通表达式的最大递归层次，警告重复键插入到建立在 IGNORE_DUP_KEY 选项的索引上。

 

ANY 聚合是一种特殊的内部唯一聚合，返回任何行。因为这种计划如果扫描 Customers 表结果集多余一行将产生一个错误，对 ANY 汇总没有实际的影响。这种计划可以简单地使用 MIN 或 MAX 聚合得到同样的结果。然而，一些聚合是必须的，因为流聚合期望每一个输出列在聚合里或在 GROUP BY 子句里。下面的查询因为同样的原因不能被编译：

select COUNT (*), C . [CustomerId]

from [Customers] C

where C . [ContactName] = 'Maria Anders'

选择列表中的列 'Customers.CustomerID' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

 

相关标量子查询

 

SQL Server 如何评估简单的不相关子查询，让我们探讨如果有一个相关标量子查询和尝试的第一个子查询相似，但是这次返回的是那些超过所有先前加入订单费用的平均值订单。

 

这次 SQL Server 不能独立地执行子查询。因为关联到列 OrderDate 上，子查询为主查询中每一行返回不同的结果。 SQL Server 首先、评估子查询，然后执行主查询。这次 SQL Server 首先评估主查询，然后根据主查询中的每一行评估一次子查询。

这个计划不是很复杂。 Index spool 立即在 [02] 上进行扫描，是一种及时 index spool 或临近索引轴。在 Orders Date 列上创建一个临时索引。被叫做及时 index spool ，因此它“及时”装入整个输入集，并且一被打开就建立临时索引。

这个索引使得子查询随后的评估更加高效，因为在列 OrderDate 上有一个谓词。流汇总为每一个子查询来计算平均运费。在流汇总上的索引 spool 是一个延迟的索引 spool ，它仅仅缓存子查询的结果。如果再次遇到任何 OrderDate ，将返回保存在缓存里的结果而不是重新计算子查询。它被叫做延迟的 spool ，因为“以延迟方式”只装载需要的结果。最后，最上面计划的过滤器与子查询结果集（ [Expr1004] ）的每一个订单进行运费比较，并返回满足要求的行。

 

下面是其他相关标量子查询的例子。假设要找到被同一个客户加入的超过所有订单平均值的订单。

SELECT o1 . [OrderId] , o1 . [Freight]

FROM [Orders] o1

where o1 . [Freight] >

   (

      select AVG ( o2 . [Freight] ) from [Orders] o2

      where o2 . [CustomerID] = o1 . [CustomerID]

   )

这个查询和前面一个非常相似，但却得到一个本质上完全不同的计划：

再次这个计划也不复杂。最外层的嵌套循环连接根据列 CustomerId 对聚集索引扫描的行进行分类。段操作符列 CustomerId 有相同值的行分成组（或段）。因为这些行都是被排序的，具有相同 CustomerId 值的行的集合将是连续的。接下来，表 spool— 一个段 spool- 读并保存其中一组拥有相同 CustomerId 值的行。

当 spool 完成载入一组行时，将为这个组返回单一的行（注意到段 spool 是唯一一种展示这种行为的 spool 类型，它不考虑读入多少行，而只显示单一的行）。这是最外层的嵌套循环连接执行内部的输入。两个页级表 spools—- 从属的 spools—- 重新执行原始段 spool 保存的组内的行。

通过使用段 spool ，优化器创建一种仅需要扫描表 Orders 一次的计划。谈到 spool 时，有这样一个例子，在这种计划里重新计算在不同位置的相同行的集合作为一个普通的子表达式 spool 。不是所有的普通子查询 spools 都是段 spools 。