《Microsoft Sql server 2008 Internal》读书笔记--第八章The Query Optimizer(2)

《Microsoft Sql server 2008 Interna》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Interna》索引目录：

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

上文主要介绍了查询优化的入门基础和查询优化器如何浏览查询计划中的规则和属性。现在我们继续来了解查询可选计划的存储和操作符如（计算标量、计算序列等）。

■可选计划的存储-the "Memo" 

查询优化器包含一种避免存储重复信息的机制，这样，在编译进程中存储内存（和时间）。这个结构被叫做"Memo", 它的一个用途是找出前期浏览过的子树，以避免再优化计划的这些区域。它存活于优化的整个过程。
这些备忘录(“Memo”）通过被按组排序等量树来work。如果你在组内执行一个子树，子树里的每个可选计划返回相同的逻辑结果。概念上，每个来自逻辑查询树操作开始于它所在的组，引用别的组，而不是直接引用其他的存储在Memo内的操作。这个模式被用于避免存储树在查询期间被存储一次以上，它也能够避免查询优化器搜索两样的可能可选计划一次以上。
除了存储等效可选计划，组也被用于属性结构。基于同一个组的可选计划有相效的逻辑和标量属性。逻辑属性实际上在SQL Server被命名为“组属性”，即便没有被存储在Memo中。因此，每个组内的可选计划应该有相同的输出列、键列、可能的分区等等。计算这些属性是代价高昂的，因此，这个结构有助于在优化期间避免不必要的工作。
所有可考虑的计划都存储在Memo中。对一个大的查询，Memo可能包含上千的组，每个组有N多的可选计划。合起来，这代表了数目巨大的可行计划。尽管大多数的查询不会在优化期间消耗大量的内存，但巨大的数据仓库查询在优化期间消耗完某个机器所有的内存是有可能的。如果查询优化器在查询在系列计划时将要耗完所有的内存时，它可以去“捡”一个“足够好的”计划而不是耗光内存。
当查询优化器为一个计划完成搜索时，它遍历Memo,从根开始，从每一个适合查询需求的每个组选择一个最佳的可选计划，这些操作被集成进最终的查询计划，并被转换成一种可以被SQL Server中的查询执行组件理解的格式，这个最终的树转型也包含一些小数量的运行时优化改写，非常像查询计划生成的输出。

 ■操作符

SQL Server有大约40个逻辑操作符和一些物理操作符。有一些操作符极其常见，比如Join和Filter。其他不常见，如Segment,Sequence Projects,和UDX。SQL Server　2008中的操作符遵循下列模式：

要SQL Server中的每个操作符通过请求一个或其子行，返回正确的行给调用者。调用者可能是另一个操作符或可能是被送去给user(如果它在查询树中是顶级操作符)

每一个操作符在一定时间内返回一行。调用器必须被每行调用。这种设计上的统一，允许操作符可以被以多种方式组合。它允许新的操作符（在查询优化器没有主要变化(比如属性框架)的前提下）被加到系统，这有助于查询优化器选择一个计划。

我们来看几个操作符的例子作简要说明。

 ■Compute Scalar--Project（计算标量）

 计算标量，在SQL Server中叫“Project”，是一个简单的操作符，它旨在声明一系列column,计算值，或来自查询树内的其他操作符的限制列。这些对应于SQL语言的Select 列表。查询优化器在优化期间不断在查询树周围移动，试图从查询优化器逻辑（如处理连接顺序，索引选择、或其他的优化）的其余部分分离。

  ■compute Sequence--Sequence Project

计算序列在查询优化器内被作为序列项目，这个操作符有点类似于计算标量。不过它计算一个新值加到输出流。关键是它工作在一个排序Stream，包含被保留在行与行之间的状态。例如Ranking函数使用这个操作符。它通过使用一个不同的物理操作并强化了一些额外的限制（比如查询优化器能怎样记录这些表达式）。此操作符用在ranking和windows函数中。

  ■Semi-Join

 “semi-Join”（半连接）这个词来源于学术数据库词汇。它被用于描述一个操作：执行一个连接但是只返回来自于它输入值的一部分。查询处理器使用这个内部机制处理大多数的子查询。SQL Server以这种方式设置子查询，是因为这样可以使了解一整套可能为查询而作的转换更加容易。同时因为semi-join和常规的连接的运行时实现是类似的。与大多数的看法相反，一个子查询并不是总是执行并缓存在临时表中。它更多被看作一个常规的连接。查询优化器有转换规则，能转换常规的连接为半连接。

通常子查询是最自然的方式，代表你想使用 SQL的方式。有时候，子查询使用因为很少使用索引表，丢失声明，或写谓词的方式等而对查询优化器了解如何使用域限制属性框架而言是迟钝的而备受指责。但子查询是不可或缺的。因此，如果开发人员提倡“no subqueries”,请检查你的系统，其他的错误掩盖了表面现象。

我们用一个例子说明：

Create table customers(custID int identity,custName nvarchar(100));
create table Orders(orderid int identity,custid int,orderdate date,amount money);
truncate table customers
truncate table Orders
insert into customers(custName) values('Conor sdfsd');
insert into customers(custName) values('paul randal');
insert into Orders(custid,orderdate,amount) values(1,'2010-04-01',49.23);
insert into Orders(custid,orderdate,amount) values(2,'2010-04-12',65.00);
insert into Orders(custid,orderdate,amount) values(3,'2010-04-13',123.44);

1、使用inner join

 

2、使用distinct 加inner join

 

3、使用子查询

可以看到，有时子查询反而比inner join有更低的成本评估。

 注意：左、右Semi-Join必须处理操作中保留的子行。不幸的是，对于任何混淆这些操作的意思的人而言，SQL Server Managgement Studio和前面的工具中设置的计划被转换了。在转换格式上left 代表top，right代表bottom。

 ■Apply

 "Cross Apply"和"Outer Apply"在SQL Server2005中新增，它们代表一种特殊的子查询（一个左输出的值通过一个参数连接到右child。）有时，这被叫做"correlated nested loops join"，它代表通过一个参数到另外一个子查询。最常见的一个应用是一个索引lookup join。

 Create table idx1(col1 int primary key,col2 int);
create table idx2(col1 int primary key,col2 int);
go
select * from idx1
cross apply(
select * from idx2 where idx1.col1=idx2.col1) as a

select * from idx1 c
inner join idx2 o on c.col1=o.col1

 

 

 在两种情况下，一个来自outer表的值作为一个参数被用于在inner表搜索。注意：一个标准的inner join也能生成一个seek,那意味着作为优化进程的一部分，查询优化器考虑转换一个join为一个Apply。

Apply操作符很像是存储过程。对每个来自outer(left)侧的行，一些在inner(right)侧的逻辑被估算，0或更多调用right子查询的行被返回。 查询优化器有时能移走关联，转换一个Apply为一个更常用的join。

  ■Spools

 SQL Server有一定数量的不同的特殊的池。每个池都被调整为某些场景。概念上，它们做同样的事情：从输入中读取所有的行，存在内存中，并spill到磁盘，允许操作符从cache中读取这些行。池存在主要是做一个行的拷贝，这对在某些更新计划中的事务持久化和通过缓存一个复杂的子表达式而后在查询中多次使用时改善性能是非常重要的。

最奇异的池操作叫做common subexpression spool。 这个池有一种能力：一次写入，在查询中的不同children多次读取。这是当前仅有的一个在最终查询计划中的有多个parent的操作符。后面章节中会继续提到。我们看一个例子：

create table window1(col1 int,col2 int)
go
declare @i int=0;
while @i<100
begin
insert into window1(col1,col2) values(@i/10,RAND()*100);
set @i=@i+1;
end

   ■Exchange

交换操作符在查询计划中被用于设置并行。在展示计划中作为Gather streams，Repartition Streams,or disturate Streams操作。在SQL Server中，并行存在于一些区域，比如系统试图用额外的CPU来提高速度。