SQL Server查询优化器的工作原理

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

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

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

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

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

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

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

 

 

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

 

 

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

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

[原文参考: http://tech.it168.com/a2012/0329/1332/000001332016.shtml ，感谢原文作者]