SQL Server查询优化器剖析详解(一)

在JOIN操作中（需要从多个数据表提取数据时），MySQL只有在主键和外键的数据类型相同时才能使用索引。SQLServer的优化器对年夜部分人来讲长短常奥秘的，如今我们就来看看优化器是怎样创建一个可实行的企图，和其利用的划定规矩。为了申明优化器实行的历程，我们将会经由过程慢慢施加需要的划定规矩的设置，来发生更好的实行企图。

上面是一个复杂的查询（利用AdventureWorks示例数据库），显现了在数据堆栈中每一个产物的总数量：
SELECTP.ProductNumber,P.ProductID,total_qty=SUM(I.Quantity)FROM
Production.ProductPINNERJOINProduction.ProductInventoryASI
ONI.ProductID=P.ProductID
WHEREP.ProductNumberLIKENT%
GROUPBYP.ProductID,P.ProductNumber;

在SQLServer射中查询优化器前，SQLServer已为剖析和绑定查询做了一年夜堆事情。但当入手下手剖析和绑定查询时，完成了一棵由逻辑干系操纵符构成的树，以下：

登录/注册后可看大图

优化器必要把这棵树转化为能够被查询处置器（QueryProcessor）实行的企图。假如查询优化器除转化逻辑干系运算符为它找到的第一个无效情势外，而没做其他事变，我们会失掉如许的企图：

登录/注册后可看大图

这个可实行企图有两个全表扫描(fulltablescan)，一个笛卡尔积(Cartesian)，一个在两个谓词上的过滤器(Filter)，和一个聚合(aggregate)。要发生最优的查询企图，有一个很长的路要走，但它究竟发生准确的了局了。（这边的ComputeScalar，是确保SUM汇总在没有行被处置时前往NULL而不是零）。

婚配和使用划定规矩
优化器经由过程改换逻辑运算为它所晓得的对应的物理操纵来天生全部实行企图。这类范例的转换是由优化器外部经由过程实行一系列划定规矩来完成的。比方，逻辑运算的“INNERJOIN”，对应的物理操纵则是嵌套轮回（NestedLoops）（还存在merge和hashjoin的划定规矩）。

下面所展现的从逻辑树到实行企图的图中，优化器共婚配和实行了5个划定规矩：
1，GET转化为扫描（scan）
2，Join转化为嵌套轮回（nestedloop)
3，SELECT转化为过滤器（Filter)
4，GroupbyAggregate转化为streamaggregate
5，GroupbyAggregate转化为Hashaggregate

第一条划定规矩代替的逻辑表扫描失掉的。第二条划定规矩完成的逻辑毗连利用如后面提到的嵌套轮回。第三把一切的谓词（包含连接谓词）转化为一个过滤器操纵。第四和第五条划定规矩代表了两种可供选择的战略来让物理操纵层面实行会萃。在这类情形下，优化器依据本钱选择了一个流聚合在一个哈希聚合。

好了，假如它发生的那种企图按期，没有人会购置SQLServer。侥幸的是，另有良多其他优化器可使用的划定规矩，现实上有近四百种划定规矩。不外，查询优化器发生如许一个使人懊丧的企图，是由于十多个独自的划定规矩被封闭，我们是特地如许做的。

跟着更多的划定规矩被实行，我们会发明有很多勘察（exploration）和替换(substitution)划定规矩。这些会基于数学等价或启示式将逻辑哀求变更成等价的情势。比方，“joinCommute”是条勘察划定规矩。这条划定规矩的现实标明了AJOINB同等于BJOINA（外部连接）。

优化器一样也有简化的划定规矩和强迫的划定规矩。经由过程使用一切这些划定规矩，会发生相干替换战略，个中最好的将被归入最初的企图。

改善企图
下面的可实行企图的一个分明的不敷的地方是，它在的两个源表过滤器上实行了笛卡尔乘积。此时，评价连接谓词的同时举行物理毗连，实在会更无效。实行此义务的划定规矩被称为SELonJN（SELECTJOIN）。必要夸大的是，这不是T-SQLSELECT语句，它是SELECT干系操纵：使用于行的过滤器。使SELonJN优化划定规矩，我们能够失掉一个更好的企图：

登录/注册后可看大图

笛卡尔乘积已换成了一个更加一般的NestedLoops运算符，这是一个将过滤器移进连接谓词的了局（往失落了filter）。现实上，过滤器运算符完整消散–谓词“ProductNumberLIKE’T％”没被剖析吗？实在它也被今后推了–一切Product表扫描的由来。
这仍旧不是一个好的企图：我们每次从Product表中的找到每行相婚配的ProductNumber谓词时，就要扫描全部Inventory表。我们必要晓得非会萃索引的划定规矩—复杂的将一个GET转化为表扫描所依附的基础划定规矩。使用一些更多的划定规矩后，我们发生以下实行企图：

这一个是好一点了，我们如今利用了准确的非会萃索引，但这个谓词被使用为扫描了–而不是我们但愿的索引查找（indexseek)。再使用一些别的的划定规矩后，我们将会失掉了一个十分无效的实行企图。

优化器已把谓词”ProductNumberLIKE‘T%’从一个过滤器Filter变化为在Product表上的索引扫描（indexscan），但这仍旧是一个遗留的谓词（索引扫描的效力还不是最高的）。我们必要使能一个新的转换划定规矩（SelResToFilter）来让优化重视新将LIKE语句天生为索引查找（indexseek）:

登录/注册后可看大图

注重到，LIKE如今已睁开为一个可SARG的情势（SARG：SearchableArguments-http://baike.baidu.com/view/587074.htm），本来的LIKE谓词如今变成从索引查找前往的行。

剩下效力不高的部分是为了婚配我们新的查找操纵前往的行而对全部Inventory表索引举行的扫描。（实行企图是右上部分先被实行）。此时，在NestedLoop操纵内里会实行JOIN这个谓词（婚配在两个表之间的ProductId）。假如在Inventory表的会萃索引长进行索引查找，那末我们又能够进步我们的实行效力了。

为了告竣这一的方针，我们必要做两件事：

将复杂的嵌套轮回毗连（naivenestedloopsjoin）转换为一个基于索引嵌套轮回毗连（indexnestedloopsjoin）.。（嵌套轮回查询）
利用以后的Product.ProductId值来驱动每次的Inventory表查找。
第一件事能够经由过程一个叫JNtoIdxLookup的划定规矩来告竣。第二个则请求一个联系关系的轮回毗连–也叫做Apply.这条划定规矩的称号也叫做AppIdxToApp。

在优化器使用了上述两个划定规矩（JNtoIdxLookup和AppIdxToApp)后，我们失掉以下的实行企图：

登录/注册后可看大图

如今，我们已离最好的实行企图很近了。最初一步将盘算标量（ComputeScalar）转化为流聚合（StreamAggregate）。后面提到，ComputeScalar的感化是确保在没有行被处置时SUM聚合操纵前往NULL而不是0.

从ComputeScalar的寄义来看，这边ComputeScalar用于评价基于StreamAggregate所发生的COUNT(*)的了局的CASE语句。我们能够移除这个盘算标量（computescalar）的举措，要盘算COUNT(*),我们必要利用一个叫做NormalizeGbAgg的划定规矩来调剂GROUPBY谓词。一旦使用完该划定规矩后，我们失掉以下的终极实行企图：

登录/注册后可看大图

再来看下我们最后的实行企图如何，是否是是有很年夜的改善？

下一步，我们将会看到怎样定制可用的划定规矩，并先容更多的查询优化这类操作的执行过程是，1)按照新的表定义建立一个临时表tmpa，2)将原表数据拷贝到临时表，3)将原始表改名tmpb，4)将tmpa改名为原表名，5)将tmpb删除。

如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

仓酷云

这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。

现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.

可以动态传入参数，省却了动态SQL的拼写。

原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。