转 -- 查询优化器内核剖析

网上看到的文章，原址如下：

http://www.itpub.net/thread-1608586-1-1.html

 

系列文章索引：   
查询优化器内核剖析第一篇
查询优化器内核剖析第二篇：产生候选执行计划&执行计划成本估算
查询优化器内核剖析第三篇：查询的执行与计划的缓存 & Hint提示
查询优化器内核剖析第四篇：从一个实例看执行计划
查询优化器内核剖析第五篇：进一步的了解执行计划
查询优化器内核剖析第七篇：执行引擎之数据访问操作---Scan查询优化器内核剖析
第八篇：执行引擎之数据访问操作---Seek(上)





    SQL Server的查询优化器是一个基于成本的优化器。它为一个给定的查询分析出很多的候选的查询计划，并且估算每个候选计划的成本，从而选择一个成本最低的计划进行执行。实际上，因为查询优化器不可能对每一个产生的候选计划进行优化，所以查询优化器会在优化时间和查询计划的质量之间进行一个平衡，尽可能的选择一个“最优”的计划。

    所以，查询优化器成为SQL Server中最重要的一个组件，并且影响着SQL Server的性能。选择正确或错误的执行计划意味着查询执行时间可能存在着毫秒的，几分钟，甚至几个小时之间的差异。

    了解查询优化的内部机制，可以帮助DBA和开发人员能够编写更好的查询，或者给查询优化器提供信息使得它可以产生有效的执行计划。本系列文章讲述的查询优化器的内部运作的知识，此外，还会告诉你如何使用查询优化器的相关信息进行性能诊断。

     下面，我们首先来看看：查询优化器是如何工作的。

     在SQL Server数据库引擎的核心是两个主要部分组成：存储引擎和查询处理器（也被称为关系引擎）。存储引擎负责在磁盘和内存之间以最优化的方式读取数据，同时维护数据的完整性。查询处理器，顾名思义，接受提交给SQL Server所有的查询，并且为产生他们的最佳执行计划，然后执行该计划，并提供所需的结果。

    我们将查询以T-SQL的形式提交给SQL Server。因为SQL语句是一个高层抽象的声明性的语言，它仅仅只是定义了要从数据库中获取什么样的数据，而没有告诉如何去获取这些数据（或者说，没有定义获取数据的方法和步骤）。所以，对于SQL Server所接受到的每一个查询，查询处理器的首要任务就是产生一个计划，这个计划就描述了如何去执行查询，之后就由存储引擎去执行这个计划了。

    为了确保已经达到在查询处理器认为是最好的计划执行查询，查询处理器执行不同的步骤，整个查询处理过程如图所示：

    当然，上面的图只是一个最简单的示例图，下面，给大家看另外一个图，体会一下一个查询处理的过程：

       我们在后续的文章中会看到每一个步骤的详细讲解与应用，下面我们就简单的介绍图中的一些步骤（为了简单起见，我们以第一幅图为例子）

• 
  
• Parsing 和Binding（解析与绑定）：在一个查询提交给了数据库之后，首先就要被进行语法的解析，如果这个查询的语法是没有问题的，那么这个Parsing过程的输入结果就是一个逻辑树，在这个逻辑树种每一个节点都表示了这个查询进行的每个操作，例如读取某个表，进行inner join等。
  

        下面，给大家看一个逻辑树的例子，对于下面的查询：

               产生的逻辑树如下：

               这个过程就是编译原理的一个文法词法的解析。
               谈完了Parsing，之后的操作就是Binding了，这个操作现在改名字为Algebrizer。这个操作主要就是检查解析产生的逻辑树中的对象是否存在，例如Customer是否是数据库中的表，CustomerID字段是否在Customer表中等。
               经过了这个Binding之后，就会产生另外一个树形的数据结构，传递给下一个步骤。
     2. 查询优化。这个过程主要是使用上述过程中的Algebrizer Tree进行优化的处理过程，我们这里大体的可以将这个优化的处理过程分为两个步骤：

         a. 产生执行计划。在这个过程中，查询优化器会使用之前的树，产生执行计划。这个过程主要是将树上的逻辑操作转换为物理操作（其实就是存储引擎可以调用的方法，这些方法就是实实在在的去读取数据的）。

         b.估算每个执行计划的成本。一个逻辑操作可以有很多的物理操作与其对应，而每个物理操作的成本不一样，同时，也没用所谓的“什么物理操作比其他的物理操作更优” ，一切视情况可认定。在这个过程中产生很多的候选执行计划，并且查询优化器会综合考虑很多的情况，选择一个它认为“比较优”的计划，传递给存储引擎。

     3. 查询的执行与计划的缓存。这个过程比较简单了，主要是存储引擎去执行执行计划，同时为了避免相类似的SQL查询重新编译，使用过的执行计划会被缓存在计划缓存池中。

    基本是，我们可以看出，查询优化的过程就是一个将逻辑操作映射为物理操作的过程。
   我们在下一篇中稍微深入的看看候选执行计划的产生以及估算它们的成本！

 

查询优化器内核剖析第二篇：产生候选执行计划&执行计划成本估算

本篇的议题如下：
    产生候选执行计划
    执行计划成本估算

产生候选执行计划

    我们知道，查询优化器的基本的目标就是为我们的查询语句找出一个比较高效的执行计划。即使是一个非常简单的查询，也会存在很多的不同方式去访问数据，而这些不同的方式都是可以得到相同的结果的，所以，查询优化器必须要很“明智的”从这些大量的执行计划中找出了一个“最佳”的出来。

    为了得到最好的计划，查询优化器必须在某些条件的限制下，尽可能多的创建和评估大量的候选执行计划。看到这里，就有一点需要注意了“查询优化器是尽可能多的创建候选执行计划”，而不是为一个查询产生所有的执行计划。在SQL Server中，我们把一个查询产生的候选执行计划的集合称之为“搜索空间(search space)”。很显然，搜索空间中的所有的执行计划都返回相同的结果。
给一张示意图，让大家更好理解一点，如下所示：

    注：图中的Search Space中的曲线代表执行计划

    从理论上说，为了找到最佳的执行计划的查询，基于成本的查询优化器应该生成搜索空间中存在的所有可能的执行计划，并正确估计每个计划的成本。然而，一些复杂的查询可能有成千上万，或者甚至数百万可能的执行计划，查询优化器不可能去产生并评估一个查询的每一个候选的执行计划，如果那样，评估所有计划的时间会非常的长，并且严重影响查询的整体的执行时间。

    查询优化器必须优化的时间和执行计划的质量之间取得平衡。例如，如果查询优化器花1秒钟的时间找到了一个比较好的执行计划，并且这个计划的执行时间是1分钟，那么这个时候，就没有必要再去花费5分钟的时间去为这个查询找更优的执行计划。因此SQL Server不会做一个详尽的全部查找，而是尽快找到一个合适的有效的计划。由于查询优化器是有时间限制的，那么就可能选择的计划可能是最优方案，也有可能只是一些接近最优的方案。

    候选的执行计划是在查询优化器的内部通过使用转换规则，启发式算法产生的。候选的执行计划在优化过程中一直保存在称之为“Memo(中文翻译可能为“备忘录”，以后我们就直接使用英文名称，很多的技术术语翻译过来之后就变味了)”的内存组件中。从这里我们就可以知道：如果为了复杂的查询产生所有的候选执行计划势必会占用大量的内存。

    我们这里只是简单的介绍一下候选执行计划的产生，后面我们会对每一个步骤进行详细的分析。

执行计划成本估算

    查询优化器需要为产生的候选的执行计划进行成本的估算，从而选择一个成本最低的。为了估算一个计划的成本，查询优化器会使用一些成本估算的公式来计算一个计划的成本，这些成本估算公式会考虑很多资源的使用，例如CPU，I/O，内存等。成本估算主要是取决于算法中采用的物理操和估算的将要处理的数据记录的量（估算数据记录的量也被称之为“基数估算”）。

    为了便于进行基数估算，SQL Server会使用并且维护统计数据(statistics)，统计数据描述了表中数据的值的分布情况，或者简单的理解为“元数据-描述数据的数据”。一旦采用基数估算得出了吗，每个操作的成本和对资源的要求，那么查询优化器就会将这个成本数值进行累计，从而得出整个就会的成本。我们这里不会讨论过多与统计数据相关的知识，在后面中会详细的讲述。

    在下一篇文章中，我们会讲述计划的执行与缓存，以及与Hint相关的话题。

查询优化器内核剖析第三篇：查询的执行与计划的缓存 & Hint提示

本篇议题如下：

查询的执行与计划的缓存

Hint提示

 

首先看到第一个议题

查询的执行与计划的缓存

     一旦查询被优化之后，存储引擎就使用选中的执行计划将结果返回，而被使用的这个执行计划就会被保存在内存中一个被称之为“计划缓存”的地方，从而使得这个执行计划可以被重用，从而节省CPU等资源。

    尽管我们可以把执行计划缓存起来，便于重用，但是在某些情况，对于某些查询而言，计划重用并不是很好的选择。这是为什么？这主要取决于表中数据的分布情况和查询中所使用的参数，其实这种情况也被称为“参数嗅探(Parameter Sniffing，翻译过来还是很别扭的)”。
这里，我们就简单的说一下（详细的后续文章会慢慢的介绍）。例如，假设有这样一个查询语句，如下图所示：

    执行如下：

    这个时候，存储过程运行，产生了一个执行计划，并且被缓存起来了。
    之后，又要运行这个查询，但是传入的参数不同，如下：

    这个时候，这个查询可以使用之前创建的执行计划，但是这个时候也很有可能之前的执行计划对与参数870不是最优化的。因为之前在生成的执行计划的时候，查询优化器是通过传入的897创建的执行计划。

    试想：如果在表中的数据，有很多的ProductId的值都为897，即使我们在ProductId上面建立了索引，但是还有可能查询优化器决定在执行计划会采用扫描整表来获取数据。而对于参数值为870的时候，在表中的存在相同的ProductId为870的数据很少，这个时候，如果采用索引查找，可能会更快，那么就说明之前的执行计划中的整表扫描不适合了！

    当然，这里只是简要的说明了一下，大家可能不是非常的明白，没关系，只要知道有这么个情况就行了，我们在后面会详细剖析。
有时候，即使执行计划已经在计划缓存中存在了，但是有可能随着一些元数据的改变（修改表的结构，移除索引等操作）会导致执行计划失效，或者不是比较优化的，或者甚至从计划缓存中移除。当SQL Server存在内存压力的时候，一些执行计划也会被移除，释放内存。

Hint提示

    在大部分情况下，查询优化器都会选择比较高效的执行计划，

    但是，在有些情况下，查询优化器选择的执行计划没有达到预期的效果，或者说，查询优化器做出了错误的选择。造成这个问题的原因是我们没有为SQL Server提供准确的信息，例如数据库的统计信息过期了。

    同时，在有些情况下，我们根据我们的经验和分析判断，发现SQL Server选择的执行计划不是我们想要的。

    当遇到上面的情况的时候，我们就可以给查询优化器一些提示信息，去告诉它该如何产生执行计划，也就是我们自动的去干预查询优化器的默认行为。

    为了让大家有一个感性的认识，我这里举一个例子，对于下面的查询：

    我们通过查询它的执行计划，如下图所示：

    我们发现，这个查询计划不够高效，于是我们改写查询语句，如下：

    在语句中，我们强制的使得查询优化器选择我们要的连接方式，

    执行计划如下：

    大家如果对这里的知识不明白，没有关系，这里的例子只是让大家感受一下。

    注意：一般情况下，我们没有必要去干扰查询优化器的工作，因为它会已经足够的“智能”去选择更好的执行计划，除非我们非常有信心，并且证明我们用一些Hint会提升性能，这个时候，我们可以加入Hint。事实是，我们加入的Hint都是会产生很多的问题。

    本篇就暂时到这里！后一篇文章就稍微详细一点的来看看与执行计划相关的一些话题。

查询优化器内核剖析第四篇：从一个实例看执行计划

这几天也收到了一些朋友的来信说：为什么你花这么多的时间将这些东西，直接告诉我性能优化的方法就行了。这个问题，其实早就说过了：学习查询优化器不是我们的目的，而是通过它，我们掌握SQL Server是如何处理我们的SQL的，掌握执行计划，掌握为什么产生I/O问题，为什么CPU使用老高，为什么你的索引加了不起作用…
    如果，我告诉你，你去加个索引，换SAN存储，这样意义不大！数据库优化就是这样的：没有所谓的“绝对手段，一下子把性能搞上去，一切都是看情况而定”，都是通过不断的分析，抽丝剥解。不带头脑的优化，能好到那里去？

     在前几篇文章中，我们已经谈了一些查询优化器的相关的基础介绍，也大致的了解了它到底是干什么的。查询优化器的结果就是产生执行计划，执行计划就是一个树，这个树由很多的物理操作组成，而这些物理操作就定义了如何去存储设备中去获取数据。

     我们可以以很多的不同的方式，例如图形化，文本，XML的形式来查看一个给定查询的实际的执行计划和估计的执行计划。这些不同格式的执行计划的区别主要在于包含的信息的详细程度不同。
      
    当需要查看一个查询的实际的执行计划的时候，这个查询比较要执行。然而，如果查看估计的执行计划，此时整个查询是不需要实际执行的。如果查询是个需要消耗很长时间，很多资源的查询，我们在分析问题的时候，会先查看这个查询估计的执行计划，并且这样做也不会对使用数据库的其他用户产生影响。
     查看实际执行计划和估计的执行计划方式有很多，最简单的方式就是在SQL Server管理界面点击如下按钮：
     查看估计的执行计划：

    查看实际的执行计划 :

    下面，我们就来通过一个简单的示例讲述执行计划，这里采用示例数据库：AdventureWorks。

     我们在SQL Server中输入以下查询：

    然后，点击“Include Actual Execution Plan”按钮，然后执行SQL语句，看到如下显示：

 

    在图中，我们可以看到一些物理操作符号以图标显示，例如Index Scan，Hash Aggregate。第一个图标称为结果操作符，它返回了查询的结果。
     
    每一个物理的操作符，其实就是存储引擎中实现的一些基本的操作或者方法。例如，一个逻辑的join（就是我们在SQL写的inner join之类的），可以再执行计划中以不同的物理join操作实现（Nested Loops Join, Merge Join, Hash Join）。当然，这里没有所谓的“那种物理操作好，哪种不好”，得看具体情况。
           
    每个物理操作执行的时候，就会去获取一些数据，然后将数据传递给它下一个物理操作，知道全部的操作完成，返回结果。在查看执行计划的时候，需要“从右向左，从下到上”进行。
在执行计划中，每个物理操作都有一些“箭头”相连，这些箭头就表明了执行的先后顺序，并且箭头的粗细也放映了传递数据的多少，越粗就表明数据越多。         
  
     我们可以通过把鼠标放在这些箭头上面，查看更多的信息。如下：

   通过查看提示信息，我们可以知道：Index Scan这个操作读取了19614条数据，这些数据之后被传递给了Hash  Aggregate操作。Hash  Aggregate执行之后，就将这些数据通过City字段做了一个distinct的处理，将575条数据给了下一个操作：

    对于执行计划中出现的一些物理操作，一般基本会通过三个方法来实现它们的功能（这里要把操作和方法的概念搞清楚，可能在很多的编程语言中，一个操作就是一个方法，或者说操作就是方法，这里的操作和方法和那些不同，一个操作是有几个方法来实现和完成的,为了便于理解，大家这里就把每一个操作理解为一个类吧）：
               Open()方法：这个方法初始化一个物理操作
               GetRow()方法：这个方法每次都从它的上一个操作中获取一行数据
               Close()方法：执行完毕，做一些相关的清理等工作
               
    因为GetRow()方法每次只能从上一个操作中获取一个数据，那么如果上一个操作传递了很多的数据，那么这个物理操作就要多次调用上一个操作的GetRow()方。在上面的例子中，Hash  Aggregate操作只调用一次Index Scan的Open()方法，然后调用19615次Index Scan的GetRow()方法，最后调用一次Index Scan的Close()方法。

    其实我们还可以通过这个图形化的执行计划得到更多的信息！为了使得大家更好地消化今天的知识，余下的内容，下次接着讲述。

查询优化器内核剖析第五篇：进一步的了解执行计划

在上一篇中，我们稍微的谈了一些有关执行计划的知识，本篇的目的主要是补充上一篇，因为执行计划的东西很多很多，如果要讲清楚，那就得用一本书的篇幅来讲述，看以后的安排，如果有时间，可以进一步的尝试写写。
    在上一篇中，我们说了，可以再图形化执行计划中查看数据的流向等相关信息，同时，也可以通过把鼠标放在一些操作或者箭头上查看更多的信息。另外，我们还可以在物理操作符和箭头上，点击右键，选择“属性”，查看更多的信息，如下图所示：

    其实在很多的时候，图形化展示的信息是很有限的，毕竟有很多的信息不方便在图形中显示出来，所以为了查看更加具体的信息，我们可以查看执行计划的XML格式和普通文本格式（普通文本的格式以后会被微软逐近的弃用，但是如果是对SQL Server2000进行调优，那么还是只能用普通文本格式的执行计划来进行的）。

     如果在之前我们得到的图形化的执行计划的显示窗口，点击右键，然后选择“Show Execution Plan XML”，如下图所示：

    这个时候，就显示如下：

    我们这里不会讲述如何来解读这个XML格式的执行计划，以后可能会用一些专题来讲述，并且在本系列的后文文章中，如果用到了，会做适当的必要性的讲解。

    除此之外，我们还可以通过SQL语句来获取执行计划（只能用SQL语句来获取XML格式和普通文本格式的执行计划）。
    我们先用一个表格来总结一下查看执行计划的几种方式，如下：

    注意：很多的语句都是Set  Show_XXX On，有“On”就有对应的“Off“。
    下面，我们就来查看之前的SQL语句的XML格式的执行计划：
   

   

    运行之后，得到如下的结果显示：

    点击蓝色的链接，在SQL Server 2008中就看可以到这个XML格式的执行计划以图形化的形式展示出来。

    如果是在2005中，那么就会直接显示XML格式。这个时候，我们可以把这些XML格式的执行计划保存为.sqlplan为后缀的文件，那么双击就可以以图形化的形式查看了。
   

    如果运行下面的SQL语句：

    那么就会显示基于普通文本的格式的执行计划，如下所示：

   

    普通文本格式的不好之处就是不要阅读，但是它包含的信息是最多的。

    除了采用上面的的方法来查看之外，还可以利用DMV（动态管理视图）来查看和SQL Profiler或者SQL Trace来查看，我个人比较喜欢这个格式，但是DMV的学习曲线更加的陡峭，但是一旦学会，可以做很多的事情，特别是在性能优化与故障排除方面。、

    在这里，我介绍一下使用DMV来看。之前我们谈到的，当一个查询被优化之后，它的执行计划就会被放在了计划缓存中，也就是在内存中，所以我们可以采用sys.dm_exec_query_plan这个动态管理函数（DMF）来获取这个查询的执行计划（DMF常常和DMV结合在一起使用，以后谈到DMV，我们就指代：DMV和DMF）。


    我们运行下面的查询：

   
    在上面的查询中，涉及到了sys.dm_exec_requests这个动态管理视图，这个DMV就是放映了当前正在执行查询，而session_id就是当前运行的这个查询的回话id，我们可以通过查看一些系统表得到。这里就稍微的提及一下，我们会面还有更多的讲述！

    今天就到这里！下一篇讲述Join的一些知识，大家可以认为Join没有什么，但是“次Join非彼Join“，大家拭目以待 ：）。

查询优化器内核剖析第六篇：谈谈Join的顺序问题，纠正江湖偏方

可以说Join的顺序是查询优化过程中最复杂的问题（当然，这里说的复杂，是对于查询优化器而言，我们很多时候没有看到，也没用怎么在意这个复杂性），因为它涉及到了为每个可选的Join的顺序计算成本。可以这样说：Join的顺序与个数直接与产生候选的执行计划的数量挂钩，从而极大的影响查询优化器的优化过程。
           提到Join，我们都很熟悉了：就是把来自两个表的基于一些公共的信息进行联合的操作。Join的操作每次都只能在两个表之间进行，因此，如果一个查询要Join的表有N个，那么，就需要进行（N-1）次的Join操作，即：先第1个表和第2个表Join，然后Join的结果在和第3个表进行Join，以此类推。

               关于Join，查询优化器会根据以下两点做出重要的决定：

• 
  
  
  • 
    
    
    • 
      
    • Join顺序的选择
    • Join算法的选择
      

               在这部分，我们重要谈谈Join的顺序的问题，相信看完之后，一定会让大家对Join有一个全新的认识，至于Join算法，因为这涉及到了执行引擎的部分，我们会在后面的文章中讲述。

              之前提过，表的Join的顺序决定了一个查询的性能与成本。对已一个查询而言，可以产生很多的候选执行计划，虽然这些执行计划返回的结果一样，但是不同的Join产生的执行计划的成本确实相差很大的。

               因为Join操作有着交换律和结合律的特性，所以，即使一个再简单的查询，也会因为Join的顺序不同而产生很多不同的执行计划，而执行计划的数量也会随着Join表的个数的增加而呈指数倍的增加。而查询优化器的任务就是在这些众多的执行计划中选择一个比较优的Join顺序的执行计划。

为了便于理解，我们先来看看几个概念，然后深入的讲述一些例子。

       首先看看“交换律”。这一个数学概念，因为SQL  Server是关系型数据，其理论基础就是关系集合论，而现在的数学都是建立在集合的基础上的，因此SQL Server在设计的时候，也引入了很多的数学的理论。Join操作的就体现了交换律：A  Join B等于B Join A。

       交换律也定义了哪一个表被先访问，例如在一个Nest Loops Join的操作中，首先被访问的表就被称之为“外部表”，而第二个表就称之为“内部表”。在Hash Join中，首先被访问的表称之为“内部构建表”，第二个表为“探针输入”。定义哪一个表为“外部表”或者为“内部构建表”、“探针输入”对性能的影响都是巨大的。

       Join操作的另外一个特性就是：结合律，即：(A Join B) Join C等于A Join (B join C)，这个概念也不难理解。下面，我们就通过一个实例来看看。

我们AdventureWorks示例数据库为例子，我们为下面的这个查询产生实际的执行计划：

 

我们来看看执行计划，如下：

 

 

可以看出：

• 
  
  
  • 
    
  • 我们在代码中Join的顺序是：(Contact  Join Individual)Join Customer，但是在执行计划中的Join顺序却是：(Sales.Customer JOIN Sales.Individual) JOIN Person.Contact。
    

                    从这里就可以知道：我们在代码中的Join顺序，不见得就是最后执行计划的顺序，也证明了网络上面流行的一些偏方是错误的：通过改变Join的顺序来提升性能。因为查询优化器相当的智能，对于Join的顺序，它有着自己的决定和考虑。

              2.查询优化器会根据很多的信息来决定到底逻辑的Join操作最后又哪一种物理Join操作来执行。在例子的代码中，我们的Join都是逻辑的Inner Join，但是最后查询优化器会选择合适的物理Join操作，例如对于(Sales.Customer JOIN Sales.Individual)，选择的就是Merge Join，因为这个两个表是基于聚集索引进行连接，并且数据都是按照聚集索引来排序的（更多的讲述，我们后面文章会涉及）。

当然，我们也可以通过使用Hint来要求查询优化器按照我们的要求来强制的产生Join的类型和顺序，我们来看下面的一个例子：

 

这时候，产生的执行计划如下：

 

可以看出，查询优化器按照我们的要求进行了Join的连接顺序。

      
        我们诚然可以通过使用Hint来改变查询优化器的行为，但是我们同时也要注意每个执行执行的成本。很多时候，我们使用Hint会带来很多的问题，而且查询优化器常常会选择比较好的执行方式和执行计划（在我们提供了正确的信息的情况下，例如统计信息等）。
            
        我们之前提到过：表的Join的顺序会随着表数量的增长而呈指数倍的增加。而实际的情况是，只有很少的一些表，它们的Join的顺序的组合方式的数量会达到成千上万，组合的方式的数量主要取决于查询树的结构。而且，查询优化器也不可能为这个每一种组合都去进行成本估算，而是采用启发式的算法，根据查询树的结构和形状来减少搜索的空间。

这里可能不怎么好理解，我们稍微的讲一讲。

   
        在之前的相关文章中介绍过：一个提交的查询语句经过一些处理之后，会以树状的结构出现在查询处理器中，而树形的结构对查询优化器来说又是至关重要的。树形的结构基本可以分为：左深度树，右深度树，多路树。

例如，对于：JOIN( JOIN( JOIN(A, B), C), D)这样的语句而言，可能产生左深度树或者右深度树，如图所示：

 

而对于：JOIN(JOIN(A, B), JOIN(C, D))的语句，可能产生下面的树：

 

根据表数量的不同，Join的顺序会产生很多的不同的树形，而这不同的树形最终会产生出不同的执行计划。

下面，我们就给出一个表数据与Join组合方式的数量关系。  

 

表数量	左深度树的数量	多路树的数量
1	1	1
2	2	2
3	6	12
4	24	120
5	120	1,680
6	720	30,240
7	5,040	665,280
8	40,320	17,297,280
9	362,880	518,918,400
10	3,628,800	17,643,225,600
11	39,916,800	670,442,572,800
12	479,001,600	28,158,588,057,600

很吓人吧！左深度树的数量基于是n！个（n的拉斐尔函数，n的阶乘，其中n是表的个数）。对于多路树，数量则是(2n–2)!/(n–1)!。

         另外，需要记住的就是：上面还只是产生的执行计划的可能数量而言，对于其中的每一个执行计划，查询优化器还要去选择不同的物理操作和数据访问方法（例如，是进行Table Scan还是Index Scan还是Index Seek等），而这些再次乘以之前的数量，查询优化器的工作量可想而知。

最后一个问题是：查询优化器会对上面的所有的组合方式的成本进行估算吗？

答案是：不会！查询优化器会综合考虑很多的因素来决定。

我们后面文章会讲述，今天到这里为止！

查询优化器内核剖析第七篇：执行引擎之数据访问操作---Scan

执行引擎就是由大量的物理操作组成的（而这些物理操作又会去调用存储引起的相关方法），这些操作被查询处理器用来高效的执行我们的查询。

这里不要将查询处理器与查询优化器搞混淆，它们不是同一个东西。为了使得大家对相关的概念有一个清楚的认识，请看到下面这一个图：

 

从图中，就可以一目了然的直到查询处理器与查询优化器之间的关系：

• 
  
  
  • 
    
  • 数据库基本上有两大部分组成：关系引擎，存储引擎。（建议朋友们去仔细的阅读这几篇文章：SQL 内部机制之：一个查询的生命周期）
  • 我们这里所说的“查询处理器”就是图中的关系引擎，而查询优化器只是其中的一个部分。
    

       我们最近将要介绍的“执行引擎”，就是上图中的“Query Executor”。在介绍执行引擎的过程中，我这里主要会着重的介绍几类在我们查询中常见的一些操作：数据访问，聚合，Join，还有并行操作。当然，在执行引擎中，还有更多的操作，如果大家感兴趣，可以去参看SQL Server的联机丛书。

我们首先将会介绍给数据访问的几个操作：scan，seek，还有lookup。

 

相信大家对这些操作应该有所了解的，我这里稍微的提及一下。

Scan：这个操作会读取整个数据结构，这个数据结构可以是一个堆表，聚集索引，非聚集索引。

Seek：这个操作不会读取整个数据结构，而是直接通过索引定位到要读取的那一行。所以Seek操作只能发生在聚集索引与非聚集索引上。

堆表：就是没有建立聚集索引的表，表中的数据没有按照顺序进行存储。一旦一个表建立了聚集索引，那么表中的数据就会按照聚集索引排序存储。

 

另外，非聚集索引可以建立在堆表上，也可以建立在还有聚集索引的表上。

我们通过下面的一个表做一下总结（看看不同的数据结构可以支持何种物理操作），然后迅速进入Scan操作的详细讲解。

 

数据结构	Scan	Seek
堆表	Table Scan
聚集索引	Clustered Index Scan	Clustered Index Seek
非聚集索引	Index Scan	Index Seek

      还是和之前一样，我们从一个例子入手，这里依然使用示例数据库AdventureWorks。我们首先来看到一个Table Scan的操作（也就是整表扫描） ，看到如下的查询：

查看实际的执行计划，如下图：

 

通过查看DatabaseLog表的定义，我们发现这个表是一个堆表，即，这个表没有聚集索引，如下所示：

下面，我们再看一个Clustered Index Scan（聚集索引扫描）的例子，看到如下查询：

 

执行计划如下如所示：

 

        通过查看Address表的定义，发现这个表确实还有聚集索引：

        可以上面的例子可以知道：

• 
  
  
  • 
    
  • 虽然同是进行了 Scan操作，但是因为表的一些特性不同，而最后选择的具体的物理操作Scan也不一样。
  • 不管是Table Scan还是Clustered Index Scan，它们都是对相应的数据结构进行了全部的扫描，不同的是：前者发生在堆表上，后者发生在含有聚集索引的表上。
    

下面，给大家看一个比较有意思的查询：

 

 

执行计划如下：

 

        图中显示的是Index  Scan，也就说，这个查询没有去扫描表的所有数据页，而是去扫描索引，这个成本小得多。之所以进行了这个操作，是因为在这个表上存在一个非聚集索引，定义如下：

        这个非聚集索引包含了我们查询中的两个字段：[City]，[StateProvinceID]。又因为AddressID是这个表的聚集索引，对于一个有聚集索引的表而言，它的所有的非聚集索引都会包含一个对聚集索引的引用，这样是为了加快数据检索的速度（大家可以想想为什么？）。所以，我们上面查询中的三个操作都可以在索引页中找到，就没有必要去扫描底层的表了。

 

查询优化器内核剖析第八篇：执行引擎之数据访问操作---Seek

前言：自从本系列文章开始以后，收到了一些朋友的来信，也有很多的朋友对我说，写内核分析的文章不受欢迎，因为没有多少人会关注这个东西。确实，每每在分析一些底层机制或者内核的时候，不断是书还是博客，很少能够特别“火”的。从自己的经验看来，作为一个技术人员，如果想要走得更远，更高，这些东西是很有必要的！也许大家还记得当我们遇到问题时候，无法下手的囧况，也许还记得，当我们做了一个修改之后，只能给出“可能会搞定”这样不确定的答案。为什么？因为东西掌握的不够深入，全面，导致我们顾此失彼，以至于心虚！
        曾经有朋友说：你是搞开发的，需要懂把数据库搞的那么深吗？我曾经也认为：确实没有必要，不是有DBA吗？后来，我发现我错了：第一，没有几个公司有很明确的DBA，很多时候开发人员做着DBA的事情；第二，数据库是一个应用的关键，性能优化，很大的程度上，都需要关注数据库；第三，如果想以后为项目做架构，做设计，不懂数据库，不考虑它的存储架构，设计，维护，性能，甚至是灾难恢复，如何敢说自己会做架构，如何扛起这么大一个责任。朋友们可能就说了：这么多，搞的完吗？看需要，需要啥，就去学习啥，开始的时候，为了应急，可以懂个大概，等时间有了，再深入！

        不得不承认我们技术界有些浮躁的气氛，存在着很多的“潜规则论“，”关系论“，”忽悠论“等，但是不管怎么样，作为一个技术人员，要过活下去，要有点竞争力，技术这一关是要扎扎实实过的！我们分析内核，不是单从理论上讲述，而是从实用的角度，我也不想浪费大家的时间去看毫无用处的文章，也不想浪费自己的时间写没有营养的东西。

如果大家认为说的还有点道理，就接着往下看技术的部分；如果不赞同，大家可以选择不看下面的文章，避免浪费大家的 时间!

        在上一篇文章中介绍了Scan的操作，因为我每次都只是介绍一小点，所以，朋友们可能阅读起来没有感觉，一是因为这些东西确实不好讲，二是，我希望每次写一点，利于大家消化，等到整个系列文章完整之后，大家就会全面掌握这些知识的每一个细节，自己在啃这些东西的时候，也是一点点的消化的。

说了这么多，我们言归正传，对于Seek，这个操作主要是发生在聚集索引和非聚集索引上面。

为了讲明这个问题，我们依然采用的是数据库AdventureWorks，看看下面的一个例子，看到下面的查询：

我们运行这个查询并且查看它的执行计划，如下图：

 

很显然，这个查询最后在执行的时候，采用了Seek操作。

 

下面，我们就来具体的看看，为什么？

首先，我们讲解一下与数据库索引存储相关的知识。

        我们知道，聚集索引是按 B 树结构进行组织的,既然是B树组织，那么就有叶子结点和非叶子节点之分。聚集索引B 树的顶端节点称为根节点；聚集索引中的底层节点称为叶节点。在根节点与叶节点之间的任何索引级别统称为中间级。

        在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页都处于链接在双向链接所在列表中。

可以这么说，聚集索引的叶子结点存储的是按聚集索引顺序排列的数据本身，而中间结点和根节点则在维护索引和其层级。如图：

 

        对于每个聚集索引，在系统表 sys.system_internals_allocation_units 中的 root_page 列指向该聚集索引某个特定分区的顶部。下图就是sys.system_internals_allocation_units的数据：

SQL Server 将从索引中向下移动以查找与某个聚集索引键对应的行。

为了查找键的范围，SQL Server 将在索引中移动以查找该范围的起始键值，然后用向前或向后指针在数据页中进行扫描。

为了查找数据页链的首页，SQL Server 将从索引的根节点沿最左边的指针进行扫描。

 

看完了上面的介绍之后，大家基本对聚集索引的存储有了大致的理解。

下面我们首先来看看Person.Address表的聚集索引的定义：

 

               大家看到上面的这个图，这个表的聚集索引就是AddressID。当查询在执行的时候，就会使用AddressID作为索引键通过B树一下子就找到了这个键对应的数据页，然后读数据！

               

非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：

• 
  
  
  • 
    
  • 基础表的数据行不按非聚集键的顺序排序和存储。
  • 非聚集索引的叶层是由索引页而不是由数据页组成。
    

       非聚集索引既可以建在堆表结构上也可以建在聚集索引表上；非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。

如果表是堆则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。

 

      如果表包含有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

我们来看下一个例子：

这个查询的执行计划如下：

 

               毫无疑问，进行了一个非聚集索引的Seek操作，下面我们就稍微具体的来看看。

               还是看看这个查询中的一些字段以及这个表中的非聚集索引的定义：

               我们发现，这个查询中的StateProvinceID字段上建立了聚集索引，之前所说的：在一个包含有聚集索引的表中，非聚集索引会包含对聚集索引的引用，以便快速的查找数据。也就说：我们查询中的两个字段，都是在索引中的，此时只要查找这个索引结构就行了：因为查询中的两个字段都在索引结构里，没有必要去读底层数据页了，这个速度也是很快的。

 

查询优化器内核剖析第九篇：执行引擎之数据访问操作---Bookmark Lookup

这里有一个问题：非聚集索引在快速查找数据的时候非常有用，如果查询的某些字段不在索引中，此时会发生什么？或者换句话说，如果非聚集索引中没有包含查询中所有的字段，此时如何处理？
               此时，查询优化器就决定哪一种方式比较高效： “结合非聚集索引来快速的查询底层的表”还是“直接去底层的表扫描数据”。
               为了说明问题，我们通过一个例子来说明。看到下面的查询：

               其中，AddressID和StateProvinceID包含在非聚集索引中，如果我们此时要查询另外两个不在索引中的字段：City和ModifiedDate，此时查询计划如下所示：

               在上面的例子中，查询优化器选择使用IX_Address_StateProvinceID这个非聚集索引快速的去底层的数据表中获取额外的数据列。另外索引没有包含查询中的所有数据，而要去底层的数据表中获取数据，这个操作就被称之为“Bookmark lookup（书签查找）”。

               我们下面来看另外一个查询，如下：

               此时，执行计划如下：

               这个时候，我们就纳闷了：查询优化器选择了Clustered Index Scan，为什么没有使用Bookmark Lookup。我们两次执行的查询基本上是同一个，只是StateProvinceID的参数值不一样。其实在这里，查询优化器会根据提供的参数值去估算谓词的基数，从而选择更加高效的执行计划。其实这里也说明了一个问题：对于同一个查询，不同的参数值，产生的执行计划不一样，换句话说，通过参数值A产生的执行计划，可能对于参数B不是最优的，这就是“参数嗅探”的问题，我们后面会讲述的。

               此时，查询优化器根据通过使用统计数据，为StateProvinceID值20进行估算，发现返回的数据有很多，并且通过成本的比较，还发现使用Table Scan的效率比进行多次的Bookmark lookup更好。

               这时，大家可能有一个疑问了：查询优化器决定把Bookmark Lookup操作转为Scan操作的一个临界点是什么。

               Ok，因为一个Bookmark Lookup操作需要进行随机的I/O，此时这个操作还是有点昂贵的，因为，如果某个查询中，这样的操作很多的话，查询优化器认为还不如进行Scan（Clustered Index Scan  或者  Table Scan）。对于前面的例子，当StateProvinceID值为32的时候，查询优化器通过估算发现，结果只是返回一条数据，此时进行Bookmark Lookup是可以的；但是当值为20的时候，返回300多条数据，此时的成本还不如整个扫描来的快。

               朋友们可能又要问了：我们可以测试从返回的数据条1到300，来看看，到底是数据返回多少条的时候发生了两个操作的转换，来找到临界点。

               其实，这两个操作的转换不是看数据到底返回了多少条，而是基于成本进行估算的。其实，我们完全可以查看IX_Address_StateProvinceID的统计数据来分析（我们后文会详细讲述统计数据的分析）。其实，通过我不断的测试发现：当数据条数返回为62条的时候，就发生了两个操作的转换。下面，我们就来做个实验：将StateProvinceID的值分别给163和71来测试一下。

               当值为163的时候，执行计划如下：

               当值为71的时候，执行计划如下：

               通过查看执行计划，我们就看到:当查询优化器估算如果返回结果为62条数据的时候，使用了Bookmark操作。当估算的结果为106条的时候，就选用了Clustered Index Scan操作（因为不存在一个参数值的返回的结果在62与106之间，也就说不存在一个StateProvinceID的值，返回的结果数据条数落在62至106区间之间）。

               最后，我们稍微的看一下堆表。因为非聚集索引可以在堆表上面建立，所以，我们可以在堆表上面执行Bookmark操作。下面我们来看看一个查询：

               然后，我们建立一个索引：

               执行计划如下：

               从图中看出，此时Bookmark Lookup的物理实现选择了RID Lookup操作，而不是选择了Key Lookup物理操作，原因很简单，因为堆表上面没有聚集索引，所以必须通过RID去查询底层数据表去获取额外的数据列。

 

查询优化器内核剖析第十篇：执行引擎之数据访问操作- Aggregations(聚合)

前言：本系列文章不知不觉已经到了第十篇了。有朋友一直坚持看本系列，非常感谢。有朋友问我，看是看了，貌似用不着啊。到现在为止，确实用不上，但是，这是明白查询化优化器的必经之路，也是学会查看执行计划的必备知识，更是以后性能调优的基本技能，厚积薄发。              
       相信对于聚合操作，大家应该是非常的熟悉了。在数据库中，我们常常采用聚合操作来对一批数据进行处理，例如SUM, AVG or MAX，等。我们查询中的聚合操作，属于逻辑操作，在数据库底层，这些逻辑的聚合操作是有两类不同的物理操作来实现的：Stream Aggregate 和 Hash Aggregate。

        在真正开始讲述这两个物理操作之前，我这里首先介绍一下排序(Sort)和哈希(Hash)。这两个操作非常重要，例如，如果要进行Stream Aggregate操作，那么就要先保证数据是排序了的。排序的操作是非常消耗内存的，如果内存不足，就会使用tempdb数据来存放数据，从而使得磁盘的读写操作变多和CPU的使用变高。另外，对于没有排序的数据，在聚合的时候，就会采用哈希表的形式，关于这一点，我们在后面讲述Join的时候会进一步的讲述。

       总是记住一点就是：如果数据是排序的了，那么聚合的物理操作就会选择Stream Aggregate，那么Join的物理操作就会使用Merge Join；如果数据没有排序，那么聚合就会选项Hash Aggregate，Join就会选择Hash Join。如果我们想在在没有排序的数据上面使用“在排序数据上面才有的操作”，那么数据就会被先排序（被我们或者被数据库）。

Stream Aggregate

还是像之前一样，我们通过一个例子来说明，如下：

 

执行计划如下：

 

       为了理解上面的执行计划，这里为大家普及一个概念：如果一个查询只使用了聚合函数，同时没有使用Group By，那么这个聚合就称为scalar 聚合，就是说，这个聚合只返回一个值，那么这个聚合的物理操作总是用Stream Aggregate来实现的。对于这个“scalar”的理解，大家可以联想一下ADO.NET中的SqlCommand的一个操作:ExecuteScalar()，返回一个值。

       上面的执行计划中使用了Stream Aggregate操作吗，为了更好的查看相关的信息，我们来看看这个查询的文本的执行计划，运行如下查询：

文本执行计划如下：

 

       大家可以看到：在AVG聚合函数执行的时候，Stream Aggregate操作使用了Count和SUM聚合函数，把这两个聚合函数的结果分别放在Expr1004和Expr1005中，Compute Scalar操作验证做了一个除数是否为零的操作，之后就进行一个转换数据类型的操作，最后就执行运算。              

我们再看一个例子，如下：

 

通过查看执行计划，发现有点怪，如下：

 

 

发现没有Sort操作，没有数据排序，为什么使用Stream Aggregate？

       原来SalesOrderID是SalesOrderDetail的聚集索引，也就是说，数据已经是排序的了，所以这里没有Sort操作，并且SUM的聚合操作选用了Stream Aggregate实现。   

看到这里，大家心里可能就想了：怎么都是Stream Aggregate？什么意思？看到现在没有什么感觉。

下面我们就来看看 hash Aggregate，看完了之后，两者结合理解，就差不多了。

Hash Aggregate

查询优化器在一个很大的并且数据没有排序的表上面为聚合操作选择Hash Aggregate。我们来看下面的一个查询，如下：

 

执行计划如下：

 

       在上面的查询中需要根据ContactID来对整个表的数据进行分组的统计，又因为这个表中有3万多条数据，所以对于大量无序数据的一些操作，基本都是采用在内存中建立哈希表的方式来处理。后面我们讲到的Hash Join也是这样的。
下面，我们再来看另外一个查询，如下：

 

执行计划如下：

 

       大家看这里，应该会非常的纳闷：这个查询和刚刚之前的一个查询差不多，并且ProductLine也不是聚集索引，为什么这里采用了Stream Aggregate，而之前的那个采用了Hash Aggregate？

       通过查看Product表本身，我发现：表中的数据只有506条，也就是说，这是一个小表，热查询优化器认为对于这个查询把数据先排序，然后实现Stream Aggregate的成本比Hash Aggregate的成本低。
              
       说的这里，也许大家也想起了一件事情：有时候，我们在一个小表上面建立索引，但是在真正执行的时候，我们发现查询优化器没有使用这个索引，因为它认为做整表扫描比使用索引更加高效。所以这里，首先进行了排序，在执行计划中出现了一个Sort操作，而后执行计划中选择了Stream Aggregate。

               下面，我们来做一些操作验证我们之前的一些说法：无序的大表上面的聚合操作会使用Hash，那么有序的大表就会使用Stream Aggregate。

               下面，我们在SalesOrderHeader表上面建立一个索引，如下：

               执行计划如下：

 

果然，因为在ContactID建立了索引，数据就进行了一定程度的排序。

 

此时，我们可以进一步的想：如何我们直接在查询语句中明确的使用排序操作，结果是不是也会使用Stream Aggregate呢，查询如下：

 

查看执行计划，如下：

 

 

看到这里，也许大家又要郁闷了，为什么会这样？

       其实，此时查询优化器会在“先Sort，然后Stream Aggregate”和“先Hash Aggregate，然后 Sort”这两个之前进行成本的计算，选择成本最小的那个。

       所以，可以看出“没有绝对的标准答案，一定都是视情况而定”（中国的教育从小告诉我们任何时候一定有个标准的肯定答案，此时很多时候不是这样的，特别是性能优化的时候）。