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

wxsheng

　　《Microsoft Sql server 2008 Interna》读书笔记订阅地址：
　　http://www.iyunv.com/downmoon/category/230397.html/rss
　　《Microsoft Sql server 2008 Interna》索引目录：
　　《Microsoft Sql server 2008 Internal》读书笔记--目录索引
　　SQL Server内置的查询优化器负责对一个给定的SQL语句执行判断作出最合理的查询计划。因为查询优化器并没有过多的内容展示于外界，因此不像SQL引擎 中的其他组件那样为人熟知。第八章（The Query Optimizer）主要介绍查询优化器及其工原理，本章读完后，你应该会加深在较高层次的优化架构的理解，并熟知一个特定的查询计划为什么会被查询优化 器所采用。此外，你应该能解决特定的案例，比如查询优化器没有选择预期的执行计划，以及什么因素影响了优化器的选择。
　　第八章是本书的另外核心技巧之一－－查询优化，它在底层数据存储的基础上，主要介绍对 已有的SQL语句或查询进行针对性的测试和优化方法。这里有一些实用的技巧。本章分两部分：第一部分介绍了查询优化器的基本机制，包括它使用的高级架构及 如何定义每个计划的一整套替代方案。第二部分查询优化器的特定领域，以及它们如何适应Framwwork。例如“如何选择索 引？”、“策略如何使用？”、“我如何理解更新计 划?”等。本文先开个头。
　　 ■总览
　　对于一个简单的查询，基本的编译“管道(pipeline)” 如下：
http://k9pulq.blu.livefilestore.com/y1px2OzP0J7nSj0JvzbkYO5eqB-y0FeMzzcvs-MY4DLp-EoGpjouZi4seNQw56ySK26C6WZQql09ovAUkywrRqYljn_SSF2nKEA/2010-05-11%2010-19-48.png
　　当一个查询被编译时，首先被解析为一个等价的树表达式(tree representation)。对符合SQL语法的合法表达式，第二阶段是执行一系列的（针对这个表达式的）验证步骤，这个阶段称为绑定 (Binding)，在此阶段中，树中的列和表与存储在元数据中的列和表进行比较， 以确保列和表存在。这个阶段对用户是可见的。这一阶段也对查询作语义检查，以确保查询是有效的。比如确保绑定到GROUP BY操作的列是有效的。一旦查询树被绑定并被判定为一个有效的查询，查询优化器接管查询，并开始评估不同可能的查询计划。查询优化器执行查询并返回给系统 执行。执行组件运行查询，并返回查询的结果。
　　SQL Server查询优化器提供了一些附加内容，来扩展这个图，以方便开发人员和DBA。比如优化计划被缓存，因为它们对生产而言成本昂贵，且经常被重复执 行。当底层数据被充分改变时，旧的查询计划被重新编译。SQL Server也支持T-SQL语言，这意味着在SQL引擎中，可以一次批量处理多个SQL语句请求。查询优化器不考虑批编译和工作和工作量分析，因此本章主要关注于单个查询编译。
　　 ■树格式(Tree Format)
　　当你提交一个 SQL查询到查询处理器时，SQL字符串被解析为一个树表达式，树的每个节点代表了一个将要被执行的查询操作。如下语句：


select * from Customers C inner join Orders O on C.cid=O.cid where C.data='2010-4-30' 　　可能的查询如下：
http://k9pulq.blu.livefilestore.com/y1p6ajU0WNT4JJbn_2nci4lxijAOtIVYoEw2gerz0d0_H-TVlW1oxVtKAMj8lxWMauqGnR-PU_bsYwNf_QgI63XpbKDpLeeipEP/2010-05-11%2010-51-44.png
　　在整个编译进程 中，查询处理器实际上使用不同的查询树格式，例如查询优化器执行的一个任务是根据预期结果的逻辑描述，转换树为一个能被执行的实际的物理计划。最明显的是 转换一个逻辑join(比如inner join)为一个物理join(一个哈希join,merge join或嵌套循环join)。Most of the  tree formats are pretty close to each other。(这句邀月真不知如何翻译？)
　　■优化是什么
　　目前为止，我们讨论的是转换一个逻辑查询为等价的物理查询计划。查询优化器的另外一个任务是在众多查询计划中找到一个高效的查询计划。
首先，这个计划应该是看起来对每个SQL查询而言都是一个“明显的”最佳计划，并且应该选择尽可能快的计划。实际上，查询优化实际上是一个非常困难的问题。考虑下面的查询：


select * from A
inner join B on (A.a=B.b)
inner join C on (A.a=C.c)
inner join D on (A.a=D.d)
inner join E on (A.a=E.e)
inner join F on (A.a=F.f)
inner join G on (A.a=G.g)
inner join H on (A.a=H.h)　　这 个查询有多个可能的实现计划，因为inner join可能被以不同的顺序计算。事实上，查询计划的可能数量实际上比N!=N*(N-1)*……& amp;#8230;…要大得多。随着查询中表数量的增加，可供考虑的替代方案集迅速增加，变得计算机也无法计算。各种可能查询计划的存储也成了一个问题。在一个32-bit的Intel x86机器中，SQL server实际上只能使用1.6G内存来编译一个查询，存储每一种可能方案在内存是是不可能的。即便计算机能存储各种替代计划，用户绝不会等待N久来等待系统枚举各种可能的选择。查询优化器使用启发式解决这个问题，并用统计来引导这些启发，这正是本章要阐述的内容。
很多人会认为查询优化器会对每一个给定的查询而选择一个“最佳”的查询计划。可实际上不可能所有的可能计划都被筛选一遍，因此，快速选择一个“足够好的”计划，更贴近于“查询优化”的定义，这才是我们致力追求的目标。
■查询优化器如何浏览查询计划？
查询优化器使用框架(Framework)搜索和有效地比较各种可能的替代计划， 这个框架允许SQL Server考虑复杂的，并不容易判断的方式实现给定的查询。持续地跟踪所有可替代计划并有效地找出一个计划并非一件易事，SQL Server的搜索框架包括几个组件，有助于它在有效而可靠地执行任务。
■ 规则(Rules)
查询优化器是一个搜索框架，从一个给定的查询树，查询优化器考虑将树 从当前状态转化为一个不同的的等效状态，而这个状态将被存储在内存中。在SQL Server使用的框架中，这个转化是通过“规则(rule)",这些规则和你在学校里喜欢学习的工科数学定理非常类似。例如"A inner join B"和"B inner join A"结果肯定是一样的，就像数学中的(2+1)和(1+2)的结果相等一样。这些规则被匹配到树模式，如果适于生成新的替代计划，则规则被应用。这些规则 组成了查询优化器工作的基础，它们也有助于编码某些必须的启发式（以使其在合理的时间范围内执行搜索）。
　　查询优化器有各种不同的规则。规则启发性地重写一个查询树为一个新的形态(shape),被称为“替代规则”， 考虑工科数学等式的规则被称为“勘探规则(exploration rules)”。这些规则生成一些新的树形态，但并不直接执行。转换逻辑树为将要被执行的物理树的规则被称为“ 实现规则(implementation rule)”，这些生成的物理替代计划中的最佳者最终被查询优化器输出作为最终的查询执行计划。
　　查询优化的更多原文，请查看：http://www.SQLserverInternal.com/companion/
　　■属性(Properties)
　　查询框架用一种（针对规则而言）对它更易于工作的格式采集关于查询树的信息，这些信息称为属性(Properties)。它们从子树(sub-tree) 中收集信息，这些信息有助于做决定什么样的规则能被在一个更高的点上被处理，例如，一个在SQL Server中使用的属性是一个在数据中（组成惟一键的）列的集合。看下面的一个例子：


use TestDb
go
IF EXISTS (SELECT * FROM sys.tables        
            WHERE name = 'Demotable')
        DROP TABLE Demotable;
GO
CREATE TABLE [dbo].[Demotable](
    [Col1] [int] NOT NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL
)
GO
insert into Demotable
select 1,3,4
union all select 2, 4,3
union all select  3,6,5
union all select 4,8,8
go
select Col1,Col2,max(Col3) from Demotable group by Col1,Col2http://k9pulq.blu.livefilestore.com/y1pBfaj1qWeIqryXjj2KcgB9lx84yPkEUN3ByHTHXTjHM9gcJSeteWMWUvsonJrmgNqbLiH-5LWeqW2grXOhxE3XS2Bw_uNPXLW/2010-05-11%2016-01-12.png
http://k9pulq.blu.livefilestore.com/y1p3a6Ecf-P297VrWAe7yiTGZ3IngQIBaZbE2zWYSjYFCAVbM4YSnNhtHIM7QeAZBnk5Y_qHZCycsxRwWj-jxsBDyx9FG9wFH5X/2010-05-12%2000-11-12.png
如果列(col1,col2）组成了惟一键，那么group by 就没有必要了，看下列语句：


IF EXISTS (SELECT * FROM sys.tables        
            WHERE name = 'Groupby')
        DROP TABLE Groupby;
GO
CREATE TABLE [dbo].[Groupby](
    [Col1] [int] NOT NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL
)
go
alter table groupby add constraint uniquel unique(col1,col2)
GO
insert into Groupby
select 1,3,4
union all select 2, 4,3
union all select  3,6,5
union all select 4,8,8
go
select Col1,Col2,max(Col3) from Groupby group by Col1,Col2http://k9pulq.blu.livefilestore.com/y1p3a6Ecf-P294nt0VTHhGbmKqAiq9VCN8d4jsv9oX-I5fyyGLdaP7_WGaEI4KyG0jzZx4fovYFgE5wG40Ck3ycetpfCVzmY_bP/2010-05-12%2000-12-05.png
  SQL Server在优化期间采集N多属性。正如其他编译器所做的那样，SQL Server查询优化器采集有关每个查询中引用的列的域限制信息。从预测、连接条件、分区信息中采集信息，检查限制以知道这些预测如何被用于优化查询。关 于纯量属性的一个有用的场景是矛盾探测(in contradiction detection)。查询优化器能判定查询是否不会返回任何行。
　　看下 面的例子 ，


create table domaintable(col int)
go
select * from domaintable d1
inner join domaintable d2 on d1.col=d2.col
where d1.col>5 and d2.col