【分享】SQL Server查询优化器系列–原理介绍

kinzent 2013-01-10 09:18:10

加精

这是新的“深入SQL Server查询优化器内幕系列”，主要是介绍原理。

PART I，PART II，PART III, PART IV

挖掘优化器

我们的测试查询语句产生一个优化的物理执行计划，完全不同于查询的逻辑形式。下面显示的是执行计划的预估成本为0.0295单位。

因为我们很清楚知道数据库的结构，我们可能想知道为什么优化器不选择使用表product里name列上唯一的非聚集索引来基于 LIKE 谓词筛选行。我们可以使用索引hint强制要使用的索引：

这是了不起的，毫无疑问，索引查找比我们以前的扫描便宜，但优化器仍然选择使用合并连接，这意味两个输入都在Product ID上排序了。索引查找的结果是以Name列排序（索引键），而不是Product ID排序的，所以需要一个Sort。看起来，新的Sort成本超出了在扫描上使用索引查找所节省，因为使用了索引提示的查询计划的成本估计为0.0316单位。

当然，这些数字是相当小的，因为AdventureWorks不是一个大的数据库，但在实际系统中，这些差异会变得很重要。无论如何，让我们的坚持使用索引查找的想法，为什么优化器如此热衷于合并连接的优化呢，即使它需要额外的排序，而且我们没有一个在Product ID上的ORDER BY子句？如果没有一个顶层的ORDER BY，我们给了优化器自由地以任何方便的顺序返回结果 - 也许我们可以做的更好，迫使优化器使用索引查找和哈希连接，而不是合并连接？

好了，排序已经消失了，所以这个计划看起来直观简单一点的，但预估成本再次增加，至0.0348单位。哈希连接有相当高的启动成本（它需要被授予一个内存工作区）。我们可以尝试其他的事情，但似乎可以肯定的，在这种情况下，优化器的行为开始是正确的了。

上面的手动探索展示了优化器一般情况下确实能很快找到了一个很好的计划（有时它甚至可以找到尽可能最好的计划）。在这里，'好'和'最好'的衡量是以优化器自己的成本模型进行的。一个特定的执行计划形状是否可以在一个给定的系统上执行得更快，则是另一个问题。例如，我可能会发现，第一个合并连接的执行计划运行速度对我来说是最快的，然而你可能会发现在查找（seek）和排序（sort）的运行速度对你来说是最快。我们可能都会发现，速度快取决于需要的索引和数据是否在内存中，还是需要从持久性存储中获取。可以把所有这些事情放在一旁，重要的是，我们都可能会发现，在优化器的执行计划大部分时间是相当不错。

模型和限制

优化器使用三种主要模型作为其推理的基础：基数估计，逻辑关系的等价和成本计算的物理运算符（cardinality estimation, logical relational equivalences, and physical operator costing）。良好的基数估计（期望在逻辑树的每个节点上都进行行计数）是至关重要的，如果这些数字是错误的，其随后所有的决定都是可疑的。幸运的是，检查基数估计还是比较容易的，可以简单通过比较查询计划中实际的和预估的行数。有一些细微之处需要注意 - 例如在解释嵌套循环连接的inner侧时。如果您使用免费的SQL Sentry Plan Explorer工具，会为你处理这些常见的错误解释。

基数估计中所使用的模型是复杂的，包含各种复杂的公式和计算。尽管如此，它仍然只是一种模型，因此将至少在一定程度上偏离现实。以后我们可会多谈谈关于可以帮助我们确保良好基数估计的一些内容，但现在我们只是需要注意，该模型根植于关系理论和统计分析。

逻辑关系的等价（如 A inner join B 等于 B inner join A）是基于成本的优化器探索（exploration）规则的基础。不是所有可能的关系转换都包含在数据库产品中（记住，优化器的目标是迅速找到一个很好的计划，而不是穷举所有可能的计划）。因此，你使用的SQL语法往往会影响优化器对执行计划的优化，即使用不同的SQL语法，表达了相同的逻辑要求时，也会有这样的问题。此外，熟练的查询调优者，在有足够的时间时，往往是能够做到比优化器好，也许人可以更深刻地体会数据。这种手动调优的缺点是，它通常会在未来随着数据量和分布的变化而需要人工干预。

成本计算的物理运算符是三种模式最简单的，它使用已显示出的公式为大部分硬件上的大部分查询生成良好的可供选择的物理计划。所使用的数字可能在不同的硬件上会不同，但幸运的是，在大多数情况下，这以变得不是太重要。毫无疑问，该模型将随着时间的推移、新的硬件趋势的不断涌现而被更新，并且已有证据显示在SQL Server 2012的显示计划输出，正朝着这个方向发展。

Assumptions假设

所有的模型使假设更简单，基数估计和成本模式并没有什么不同。有些东西是太难作为模型，有些东西是刚好没有被纳入到模型中。还有些东西可能可以作为模型，但没太多的实用价值或者比较复杂性或资源消耗太多。一些会真正影响执行计划质量主要的简化的假设是：

All queries start executing with a cold cache 所有的查询从一个冷缓存开始执行
这并没有像它听起来那么疯狂。从磁盘获取数据往往在一个查询中的所有成本里占据主导地位，将预计已经在高速缓存中数据量模型化是困难的，并且这个假设确实至少同样会影响一切。优化器确实包含一些逻辑在第一次访问时来计算可能在缓存中的page数
Statistical information is independent 统计信息是独立的
实际数据库中的列之间的相关性经常存在，所以这个假设是有问题的。多列统计信息，筛选索引和索引视图，有时在这一点上是可以有帮助的。
Distribution is uniform 分布是均匀的
与此相反，这是系统没有信息时的假设。举个例子：成本假设一个索引上的seek（查找）在索引的所有范围里是随机分布。
帮助优化器

有三种方法来和优化器一起工作

1.忽略它。它会在默认的配置和自动统计的框中工作的很好。
2.使用语法技巧，提示，和计划指南改写计划
3.为优化器尽可能提供最好的信息，并为一小部分有问题的查询重写它。
在不同的环境下，这三个方法都是有效的，可能第三个方法可能是建议的一个切入点。还有更多的帮助优化器不仅仅是确保统计是最新的，但是：

使用一个关系型设计

这可能是最大的一个话题。各种模型都假设您的数据库有一个合理的关系型设计，其范式不一定是3NF或更高，但如果你的结构更接近关系原则，就更好。基数估计在简单的关系型操作工作得最好，如连接，投影，选择，并、group by。避免那些会强迫基数估计进行猜测的复杂表达式和非关系型查询特性。前面提到，基于成本的优化器的勘探规则是基于逻辑关系等价的，所以具有关系型设计和编写的查询可以给你最好的机会来让这些规则更好的运行。

使用约束

使用 check 约束、外键、唯一约束，可以为优化器提供关于你数据的信息。简化和勘探（simplification and exploration）的规则在逻辑树中匹配模式，并且可能还需要匹配的节点上设置的某些属性。约束和键提供一些最强大的和最基本的逻辑属性 — — 省略这些会阻止许多优化器的规则成功转化为高效的物理计划。如果有一个整数列，只应包含某些值，那就为其添加 check 约束。如果存在外键关系，则强制执行它。如果有键存在，就声明它。可能没法完全预测这种为优化器提供这种类型的信息所带来的好处，但根究经验，这个好处比以往任何时候大多数人期望的会更大。

统计信息、索引列和计算列

在适当情况下（例如位置分布是不寻常或存在相关性）提供不只是默认采样且自动创建的统计信息。创建索引可以为广泛的查询提供潜在的有用的访问路径。如果在你的WHERE字句的列上有表达式，可以考虑添加一个完全匹配该表达式的计算列。计算列不必保存或进行索引；系统可以为计算列自动创建索引，以避免基数猜测。

Deep Trees

庞大、复杂的查询生成了庞大、复杂的逻辑树。任何错误会随着树的大小而倍增（也许会指数级增加），可能的查询计划的搜索空间可能会大大扩展，因此事件一般也会变得更复杂。将一个复杂的查询分割成更小，更简单，更关系型的查询，将会使优化器的价值更大化。另一个好处是，保存在临时表中的小型中间结果可以创建统计，这在许多情况下都是有帮助的。当然，总会有这样的一种情况，一个复杂查询所需的最终性能，往往在性能的差异上是比较小的，因此，可能不值得未来对此投入维护成本，因为，整体查询的手工调整会变得很脆弱。

不透明的操作符和新特性

用户定义函数（不是那种in-line类型的）可能看起来很方便，但它们对优化器来说完全是不透明的--优化器需要猜测UDF的基数和所生成行的分布。较新的功能在优化引擎里往往得到更浅的支持。只是知道将它们合并起来可能产生计划的质量参差不齐。通过使用各种光鲜新特性的各种手段时，需要注意结合这些特性时可能产生质量参差不齐的执行计划。

Trace Flags

下面总结了本系列里面所有用到的trace flag（假设所有的3604已经打开）

7352 : Final query tree
8605 : Converted tree
8606 : Input, simplified, join-collapsed, and normalized trees
8607 : Output tree
8608 : Initial memo
8615 : Final memo
8675 : Optimization stages and times

上面的这些trace flag从2005到2012都可以工作的很好。还有大量优化相关的flag（有些只能在2012上工作），部分列出如下：

2373 : Memory before and after deriving properties and rules (verbose)
7357 : Unique hash optimization used
8609 : Task and operation type counts
8619 : Apply rule with description
8620 : Add memo arguments to 8619
8621 : Rule with resulting tree

这些flag也是未公开的，也是得不到支持的（不公开说明微软还没决定这些跟踪标记是否固定，以后可能会随着版本的变化而更改），仅用于学习的目的。需要注意使用这些trace flag的风险。

最后的思考

我希望你通过该系列能够获得一些优化器的内部运作情况：逻辑树、简化（simplification）、基数估计（cardinality estimation）、逻辑探测(logical exploration）和物理实现（physical implementation）。这个变态的内部工作机制是很有趣的，当然，我希望大家能够从这些文字中获得对优化器更好的理解：优化器如何将查询文本转化为一个执行计划，且关系型设计和简单的查询可以让优化器更好的为你工作。充分利用优化器，可以让你有更多的时间来做更有意义的事情--如新的项目，优化奇怪有趣的查询，任何你可以做的事，而不是一直在不停的优化优化。。。

有很多非常有用的资源可以更深入的学习SQL Server。充分利用这些资源可以让你持续提高你的专业水平，更重要的是，通过实验可以加强你的经验水平。深入了解一个查询执行计划和所包含的属性，这里可能有些东西需要你花费一些时间来思考并研究以便更好的理解，但这些付出是非常值得的。

还有一个附件，包括了代码和相关的ppt。感谢你们的阅读。

文档下载：

深入了解优化器原理文档

...全文