【论文翻译】How Good Are Query Optimizers, Really?

How Good Are Query Optimizers, Really?

摘要

    查找良好的连接顺序对查询性能至关重要，本文分析了在真实数据集上基数估计往往会产生较大误差，而虽然估计对寻找良好的链接顺序至关重要，但是过度依赖这些估计，最终的结果也无法让人满意。本文还测试了成本模型的实验，发现它对查询性能的影响远小于基数估计值。最后研究计划枚举技术，比较穷举动态规划和启发式算法，并发现穷举枚举在次优的基数估计下也能改善性能。

1.概述

    确定良好的连接顺序是研究最多的问题，图一说明了经典的基于成本方法

本文主要通过实验回答三个问题：

    1.基数估计有多好?什么时候糟糕的估计会导致查询变慢?

    2.准确的成本模型对整个查询优化过程有多重要?

    3.枚举的计划空间需要多大?

本文的主要贡献包括：

    1.设计了一个工作负载(JOB)，它基于IMDB数据集。该基准公开可用以促进进一步的研究。

    2.首次提出了使用真实数据集和真实查询的连接排序问题的端到端研究。

    3.通过量化基数估计、成本模型和计划枚举算法对查询性能的影响，我们为查询优化器的完整设计提供了指导方针。我们还表明，许多灾难性的计划可以很容易地避免。

2.背景和方法

2.1 IMDB数据集

包含了大量关于电影和演员、导演、制作公司等相关事实的信息。

2.2 JOB基准

基于IMDB数据库构造,关注连接顺序，将查询设计为3-16个连接数量，平均8个连接数量。

2.3 PostgreSQL

优化器遵循传统架构，连接顺序使用动态规划方法枚举，成本模型决定执行方案，使用直方图(分位数统计)、最常见的值及其频率和域基数(不同的值计数)估计基表的基数。简单地假设它们是独立的，并将单个选择性估计的选择性相乘。

其中dom代表基数函数，T代表表达式。postgre的查询引擎使用全表扫描和索引的访问方式，在连接时可以选择进行循环嵌套（有无索引),内存散列和聚合连接的方式？？？排序可以溢出到磁盘。
主要流程如下：1.基数提取 2. 计算中间结果基数 3.基数注入 4.评估对查询性能的影响。

3.基数估计

基数估计是查询计划最重要的因素，但是有时会产生数量级的错误会导致查询速度变慢。

3.1基表估计

使用q-error作为实验指标，是估计值和真实值的比例。

可见中位数估计比较准确，其他估计指标误差很大。

3.2连接估计

图三总结了每个中间结果计算估计值和真实基数的不同

现象1：连接越多q-error越大，误差可达到1000倍，呈指数级增长，界限越宽

现象2：对于多连接，趋向于低估

现象3：DBMS A估计更趋向中值

结论：尽管估计器中有更大的误差。查询运行时在很大程度上取决于系统优化器如何使用估算值，以及它对这些数字的信任程度。一个复杂的引擎可能使用自适应算子(例如，[4,8])，从而减轻错误估计的影响。然而，结果确实表明，最先进的基数估计还远远不够完美。

3.4 更好的统计PoatgreSQL

为了确定错误估计的不同计数是否是基数估计问题的根本原因，我们精确计算了这些值并用真实值替换估计值。

我们发现误差的方差提高了但是基数被低估的趋势越来越明显。原因是被低估的不同计数导致了更高的估计值，而这个估计值意外地更接近真相。两个错误的部分互相抵消了。

4.糟糕的基数估计何时会导致查询速度变慢？

查询优化与物理数据库设计模切相关，索引的类型和数量严重影响搜索空间，当数据库设计更多的索引时，会导致错过最优的查询计划，导致查询性能变慢。

4.1依赖估计的风险

将不同系统的估计注入PostgreSQL执行得到的计划

少部分使用真实基数会慢一点，这是由成本模型误差引起的。绝大部分的查询都要慢一些。
当估计值超过合理的查询时间时，由于基数非常低，PostgreSQL优化器决定引入嵌套循环连接，前一章已经看到基数低估非常频繁，这偶尔会导致引入嵌套循环连接。Postgre选择使用循环嵌套连接的原因是使用成本来选择连接算法，例如如果循环嵌套连接的成本是1000而哈希算法是1001就会一直选择循环嵌套连接算法。因此我们禁用循环嵌套，我们没有观察到更多的超时。这也证实了我们的猜测，循环嵌套很少有任何好处，但是我们依然没有解决所有问题，许多查询速度仍然慢10倍以上。

这个原因是散列连接构建的输入大小被低估了，9.4版本之前低估会导致哈希表太小，碰撞链很长。我们将新版的补丁反向移植到前面，性能得到了提升。

4.2基数不好时的好计划

如果没有外键索引，大多数表需要全表扫描，抑制了不同连接顺序的影响，连接顺序仍然很重要，但是结果表明基数估计通常足够好，可以排除所有灾难性的连接顺序决策。

5.成本模型

成本模型从搜索空间指导计划的选择。我们建立在PG上，观察成本模型与查询运行性能联系，然后，再比较两种不同的成本模型（基于内存和简单函数的成本模型）

5.1 PostgreSQL成本模型

结合了CPU和IO成本，操作符的成本被定义为访问的磁盘页面(包括顺序和随机的)数量和内存中处理的数据量的加权总和。然后，查询计划的成本就是所有操作符成本的总和。再将每一步成本相加得到总成本，总成本再与手工设计的权值相乘，得到查询计划的成本估算值。
大多数管理人员会使用参数的默认值，但是其往往是次优的，

5.2 成本和运行时间

成本函数重要的是它与查询运行时间的相关性，如图a所示：我们也考虑了真实的基数情况并在图b中展示

5.3 调优主存的成本模型

如上所述，成本模型通常涉及数据库管理员需要调优的参数，分为CPU成本和IO成本参数。在主存的工作负载成本模型中，最需要做的是减少这两组的比例，将ＣＰＵ成本参数乘以５０绘制结果在c，d中。对比b和d可以看到，调优确实改善了成本和运行时间之间的相关性。从c和d中可以发现，带来的改进还是被估计和真实之间的差异所掩盖。

5.4 复杂的成本模型是否有必要？

为了探究是否有必要，我们将它与一个简单的成本函数Cmm进行对比：

结果如e和f所示，能够准确预测时间，且对真正基数而言的成本函数，也是一样，但使用真正基数与估计基数横向对比，改变就相当大；结论：基数估计比成本模型更加重要。

6.计划空间

最后一个重要的查询优化组件是计划枚举算法。已经提出过不少算法，既有穷举的也有启发式的。我们将在本节研究需要多大的搜索空间才能找到一个好的计划。

6.1 连接顺序有多重要？

使用Quickpick算法可视化不同连接顺序的成本，随机挑选连接边，直到所有关系被连接，每次生成一个正确但慢得查询计划，对每个查询运行算法10000次，获得分布图，使用动态规划和真正基数获得最优计划。

可以得到如下结论：

1. 最慢计划或者中间成本计划都比最优计划高几个数量级，说明连接顺序重要性

2. PK并不能提高性能，但PK+FK可以

3. 比最佳计划高1.5倍的计划占比44%（无索引）、39%（PK）、4%（PK+FK）

4. 最佳与最差之间分布宽度，101倍（无索引）、115倍（PK）、48120倍（PK+FK）

6.2 浓密树重要吗？

大多数连接排序算法没有枚举所有可能的树形状。实际上，所有的优化器都会忽略带有交叉乘积的连接顺序，这大大减少了优化时间，而对查询性能的影响可以忽略不计。Oracle更进一步，不考虑浓密的join树。为了量化限制搜索空间对查询性能的影响，我们修改了DP算法，只枚举左深树、右深树和z形树。

使用真正基数结果，Z字最好 > 左深树 > 右深树

6.3 启发式算法足够好吗？

比较了动态规划，随机和贪婪启发式算法。

结果表示，尽管基数估计有误，但是值得使用动态规划算法全面检查搜索空间。当可用索引很少时，GOO和Quickpick-1000工作得更好。

7. 相关工作

1. 保留用于基数估计的表样本的系统对单表结果大小的预测要比应用独立假设和使用单列直方图的系统好得多。
2. 对于连接交叉关联单表示例无法捕获，目前的系统仍然使用独立性假设，现在的研究工作主要基于连接样本，草图、图形模型、采样、相关样本。实验表明如果不解决交叉相关性随着更多的连接，基数估计会严重下降。
3. 学习连接交叉相关性的另一种方法是利用查询反馈，就像LEO中一样。这需要一个健全的数学基础，为此我们定义了最大熵。为了真正释放正确预测连接交叉关联的基数的潜力，我们还需新的物理设计和访问路径。
4. 不仅要选择预期成本最低的方案，而且要考虑估计的概率分布，避免比其他方案速度稍快但存在严重低估风险的方案。
5. 通过使查询引擎的运行时行为更加“性能健壮”，通常可以减轻成本错误估计。连接动态切边、散列与排序间改变、顺序扫描与索引查找间切换、分组和分区实际数量改变聚合策略等减小成本错误估计的影响。
6. 对成本模型进行的实验表明，调优成本模型比改变基数估计带来的好处要少。
7. 我们接用了随机查询优化中的Quickpick方法测试计划枚举，表征和可视化搜索空间。另一个著名的搜索空间可视化方法是Picasso,测试表明，物理设计和访问路径选择越丰富，好的查询计划就越少。
8. 查询优化是一个核心课题，现在还远没有解决，本文介绍了基于高度相关的IMDB真实数据集的连接顺序基准，以及一种度量基数估计精度的方法。希望能促进这一重要课题的进一步创新。

8. 结论与未来工作

在本文中，我们证明了：

1. 查询优化对有效的查询处理必不可少
2. 穷尽枚举法可以找到比启发式更好的计划
3. 估计错误会随着连接的数量快速增长
4. 与基数估计相比，成本模型对总体的查询性能贡献是有限的。

接下来，我们将看到在大量索引的设置中提高计划质量的两种主要途径。首先，数据库系统可以包含文献中提出的更先进的估计算法。第二种方法是增加运行时和查询优化器之间的交互。我们把对这两种方法的评价留给今后的工作