How Good Are Query Optimizers, Really？【论文内容翻译】

最新推荐文章于 2024-07-26 09:40:31 发布

YannAdams

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量210

点赞数

分类专栏： AI4DB # Plan Optimization 文章标签：数据库人工智能

原文链接：https://dl.acm.org/doi/10.14778/2850583.2850594

版权

AI4DB 同时被 2 个专栏收录

16 篇文章 8 订阅

订阅专栏

Plan Optimization

16 篇文章 2 订阅

订阅专栏

标题：查询优化器到底有多好?

***摘要***：找到一个好的连接顺序对查询性能至关重要。在本文中，我们介绍了Join Order Benchmark (JOB)，并使用一个复杂的真实数据集和真实的多连接查询，实验性地重新审视了经典查询优化器架构中的主要组件。我们研究了工业强度基数估计器的质量，发现所有的估计器通常都会产生很大的误差。我们进一步表明，虽然估计对于寻找良好的连接顺序至关重要，但如果查询引擎过于依赖这些估计，则查询性能将不令人满意。使用另一组测量成本模型影响的实验，我们发现它对查询性能的影响远小于基数估计值。最后，我们研究了计划枚举技术，比较了穷举动态规划和启发式算法，发现穷举枚举可以提高性能，尽管基数估计不是最优的。

介绍

寻找良好的连接顺序问题是数据库领域研究最多的问题之一。图1展示了经典的、基于成本的方法，它可以追溯到System R[36]。为了获得有效的查询计划，查询优化器枚举有效连接顺序的某些子集，例如使用动态规划。使用基数估计作为其主要输入，成本模型然后从语义等效的计划备选方案中选择最便宜的备选方案。

理论上，只要基数估计和成本模型准确，该体系结构就能得到最优的查询计划。实际上，基数估计通常是基于简化的假设，如一致性和独立性来计算的。在现实世界的数据集中，这些假设经常是错误的，这可能导致次优的，有时甚至是灾难性的计划。

在这篇实验和分析论文中，我们研究了经典查询优化架构的三个主要组成部分，以回答以下问题：

基数估计器有多好，糟糕的估计何时会导致查询变慢?
准确的成本模型对于整个查询优化过程有多重要?
所枚举的计划空间需要多大?

为了回答这些问题，我们使用了一种新颖的方法，该方法允许我们隔离单个优化器组件对查询性能的影响。我们的实验是使用一个真实的数据集和113个多连接查询进行的，这些查询提供了一个具有挑战性、多样化和现实的工作负载。本文的另一个新颖之处在于，它关注日益常见的主存场景，即所有数据都放入RAM。

本文的主要贡献如下：

我们设计了一个具有挑战性的工作负载，名为Join Order Benchmark (JOB)，它基于IMDB数据集。该基准是公开的，以促进进一步的研究。
据我们所知，本文首次使用真实的数据集和真实的查询对连接排序问题进行了端到端研究。
通过量化基数估计、成本模型和计划枚举算法对查询性能的贡献，我们为查询优化器的完整设计提供了指导方针。我们还表明，许多灾难性的计划是可以轻易避免的。

本文的其余部分组织如下：我们首先在第2节讨论重要背景和我们的新基准。第3节展示了主要关系数据库系统的基数估计器对许多实际查询(特别是多连接查询)产生了不好的估计。在第4节中分析了这些错误估计导致缓慢性能的条件。我们表明，这在很大程度上取决于查询引擎对这些估计的依赖程度，以及物理数据库设计的复杂程度，即可用索引的数量。主要依赖于哈希连接和全表扫描的查询引擎即使在存在较大基数估计错误的情况下也相当健壮。可用的索引越多，查询优化器的问题就越难解决，从而导致运行时远离最优查询计划。第5节表明，使用当前使用的基数估计技术，成本模型误差的影响与基数估计误差相比是微不足道的，甚至非常简单的成本模型似乎也足够了。第6节研究了不同的计划枚举算法，并展示了穷举连接顺序枚举(尽管存在大量基数错误估计和次优成本模型)可以提高性能，而使用启发式算法会影响表的性能。最后，在讨论了第7节的相关工作之后，我们在第8节中提出了我们的结论和未来的工作。

背景和方法

许多查询优化论文忽略基数估计，只研究随机生成的合成查询的连接排序的搜索空间探索(例如[32,13])。其他论文仅在理论上(例如，[21])或经验上(例如，[43])孤立地研究基数估计。虽然这两种方法对于理解查询优化器都很重要和有趣，但它们并不一定反映真实的用户体验。

本文的目标是研究在实际设置中所有相关查询优化器组件对端到端查询性能的贡献。因此，我们使用基于真实数据集和广泛使用的PostgreSQL系统的工作负载进行实验。PostgreSQL是一个具有相当传统架构的关系数据库系统，因此它是我们实验的一个很好的主题。此外，它的开源特性允许人们检查和更改其内部结构。在本节中，我们将介绍Join Order Benchmark，描述PostgreSQL的所有相关方面，并介绍我们的方法。

2.1 The IMDB Data Set

许多关于查询处理和优化的研究论文使用标准基准测试，如TPC-H、TPC-DS或Star Schema Benchmark(SSB)。虽然这些基准测试已经证明了它们在评估查询引擎方面的价值，但我们认为，对于查询优化器的基数估计组件来说，它们不是很好的基准测试。原因是为了方便地扩展基准数据，数据生成器使用了与查询优化器相同的简化假设(一致性、独立性、包含原则)。相比之下，现实世界的数据集充满了相关性和非均匀数据分布，这使得基数估计更加困难。第3.3节展示了PostgreSQL的简单基数估计器实际上在TPC-H上工作得很好。

因此，我们没有使用合成数据集，而是选择了Internet Movie data Base (IMDB)。它包含了大量关于电影的信息，以及演员、导演、制作公司等相关事实。这些数据可以作为文本文件免费用于非商业用途。此外，我们使用开源的imdbpy包将文本文件转换为包含21个表的关系数据库。该数据集允许人们回答诸如“哪些演员在2000年至2005年间上映的评分在8分以上的电影中扮演角色?”之类的问题。像大多数现实世界的数据集一样，IMDB充满了相关性和非均匀数据分布，因此比大多数合成数据集更具挑战性。我们的快照是2013年5月的，导出为CSV文件时占用3.6 GB。两个最大的表，cast_info和movie_info分别有36M和15M行。

2.2 The JOB Queries

基于IMDB数据库，我们构建了分析的SQL查询。由于我们关注的是连接排序，这可以说是最重要的查询优化问题，因此我们将查询设计为具有3到16个连接，每个查询平均有8个连接。查询13d是一个典型的例子，用于查询美国公司制作的所有电影的评分和上映日期：

每个查询由一个select-project-join块组成。查询的连接图如图2所示。图中的实边表示键/外键边(1:n)，箭头指向主键一侧。虚线表示外键/外键连接(n: m)，这是由于传递连接谓词而出现的。我们的查询集由33个查询结构组成，每个查询结构只有2-6个变体，它们的选择不同，总共有113个查询。请注意，根据基表谓词的选择性，相同查询结构的变体具有不同的最优查询计划，从而产生非常不同的(有时是数量级上的)运行时间。此外，一些查询具有比示例更复杂的选择谓词(例如，使用LIKE的析取或子字符串搜索)。

我们的查询是“现实的”和“特别的”，因为它们回答的问题可能是电影爱好者合理提出的。我们还认为，尽管查询具有简单的SPJ结构，但查询模型解决了连接排序问题的核心困难。对于基数估计器，由于数据集中包含大量的连接和相关性，查询是具有挑战性的。然而，我们并没有试图“欺骗”查询优化器，例如，通过选择具有极端相关性的属性。此外，我们有意不包括更复杂的连接谓词，如不等式或非代理键谓词，因为此工作负载的基数估计已经相当具有挑战性。

我们提出JOB用于未来在基数估计和查询优化方面的研究。查询集可在线获取：http://www-db.in.tum.de/˜leis/qo/job.tgz

2.3 PostgreSQL

PostgreSQL的优化器遵循传统的教科书架构。使用动态规划枚举连接顺序，包括密集树，但不包括具有交叉积的树。成本模型用于决定哪个计划更便宜，在5.1节中有更详细的描述。基表的基数是使用直方图(分位数统计)、最常见的值及其频率和域基数(不同值计数)来估计的。这些每个属性的统计信息是由analyze命令使用一个关系样本计算出来的。对于不能应用直方图的复杂谓词，系统采用没有理论基础的特别方法(“魔法常数”)。为了组合同一个表的连接谓词，PostgreSQL简单地假设独立性，并将单个选择性估计值的选择性相乘。

使用公式估计连接的结果大小：

其中 $T_{1}$ 和 $T_{2}$ 为任意表达式，dom(x)为属性x的域基数，即x的不同值的个数，该值是连接基数估计的主要输入。总而言之，PostgreSQL的基数估计器是基于以下假设：

uniformity：除了最频繁的值外，所有值都假定具有相同数量的元组
independence：属性上的谓词(在同一表中或来自连接表)是独立的
principle of inclusion：连接键的域重叠，使得来自较小域的键在较大域中具有匹配

PostgreSQL的查询引擎接受一个物理算子计划，并使用volcano风格的解析来执行它。最重要的访问路径是全表扫描和查找非集群B+Tree索引。连接可以使用嵌套循环(有或没有索引查找)、内存哈希连接或sort-merge连接来执行，如果需要的话，排序可以溢出到磁盘。使用哪个连接算法由优化器决定，不能在运行时更改。

2.4 Cardinality Extraction and Injection

我们将IMDB数据集加载到5个关系数据库系统中：PostgreSQL、HyPer和3个商业系统。接下来，我们使用默认设置运行每个数据库系统的统计信息收集命令，以生成估计算法使用的特定于数据库的统计信息(例如，直方图或样本)。

然后，我们使用特定于数据库的命令(例如，使用PostgreSQL的EXPLAIN命令)获得所有测试查询中间结果的基数估计。稍后，我们将使用这些不同系统的估计来获得最优查询计划(w.r.t.各自的系统)。并在PostgreSQL中运行这些计划。例如，链查询的中间结果

此外，在外键和索引嵌套循环连接上索引的可用性引入了对额外中间结果大小的需求。例如，如果外键A.bid上存在非唯一索引，则还需要估计A⋈B和A⋈B⋈C。原因是选择A.x = 5只能在从A.bid上的索引中检索到所有匹配的元组后才能应用，因此系统在选择之前和之后产生两个中间结果。除了来自不同系统的基数估计外，我们还通过执行SELECT COUNT(*) 查询获得每个中间结果的真实基数。

我们进一步修改了PostgreSQL，允许任意连接表达式的基数注入，允许PostgreSQL的优化器使用其他系统的估计(或真正的基数)而不是自己的估计。这允许直接度量来自不同系统的基数估计值对查询性能的影响。请注意，IBM DB2通过允许用户显式指定谓词的选择性，允许用户对估计过程进行有限形式的控制。然而，选择性注入不能完全建模相互关系相关性，因此不如为任意表达式注入基数的能力通用。

2.5 Experimental Setup

商业系统的基数是使用一台运行Windows 7的笔记本电脑获得的。所有的性能实验都是在两台Intel Xeon X5570 CPUs(2.9 GHz)的服务器上进行的，总共8核，在Linux上运行PostgreSQL 9.4。PostgreSQL不并行查询，所以在查询处理过程中只使用一个核心。系统有64 GB的RAM，这意味着整个IMDB数据库被完全缓存在RAM中。中间查询处理结果(例如，哈希表)也很容易装入RAM，除非选择了一个非常糟糕的计划，其中包含非常大的中间结果。

我们将每个算子的内存限制(工作内存)设置为2 GB，由于更频繁地使用内存内哈希连接而不是外部内存sort-merge连接，因此性能要好得多。另外，我们将缓冲池大小(shared_buffers)设置为4GB，将PostgreSQL使用的操作系统缓冲区缓存大小(effective_cache_size)设置为32GB。对于PostgreSQL，通常建议使用操作系统缓冲，除了它自己的缓冲池，并保持大部分内存在操作系统端。这三个设置的默认值非常低(MBs，而不是GBs)，这就是为什么通常建议增加它们的原因。最后，通过将geqo_threshold参数增加到18，我们强制PostgreSQL总是使用动态规划，而不是对于超过12个连接的查询使用启发式。

基数估计

基数估计是找到一个好的查询计划的最重要的因素。除非基数估计(大致)正确，否则即使是穷举连接顺序枚举和完全准确的成本模型也毫无价值。然而，众所周知，基数估计有时会出现数量级的错误，而这种错误通常是导致查询缓慢的原因。在本节中，我们通过将估计与真实的基数进行比较，实验地研究关系数据库系统中基数估计的质量。

3.1 Estimates for Base Tables

为了测量基表基数估计的质量，我们使用q-error，这是估计的与真实基数的差异因素。例如，如果表达式的真实基数为100，则10或1000的估计值的q-error都为10。使用比率而不是绝对差或二次差可以获得这样的直觉，即在制定计划决策时，只有相对差才重要。如果查询的q-error是有界的，那么q-errors进一步提供了计划质量的理论上限[30]。

表1显示了我们工作负载中629个基表选择的q-error的第50、90、95和100个百分位数。对于所有系统，中值q-error接近1的最优值，表明所有选择中的大多数都是正确估计的。然而，所有系统都会对某些查询产生错误估计，并且不同系统之间基数估计的质量差异很大。

从单个选择来看，我们发现DBMS A和HyPer通常可以很好地预测甚至复杂的谓词，如使用LIKE的子字符串搜索。为了估计基表的选择性，HyPer使用每个表1000行的随机样本，并在该样本上应用谓词。只要选择性不太低，就可以获得任意基表谓词的准确估计。当我们观察DBMS A和HyPer产生2以上错误的选择时，我们发现它们中的大多数谓词的真实选择性极低（例如， $10^{-5}$ 或 $10^{-6}$ ）。当选择在样本上产生零元组时，通常会发生这种情况，而系统又回到了特定的估计方法（“神奇常数”）。因此，DBMS A似乎也使用了抽样方法。

其他系统的估计更差，似乎是基于每个属性的直方图，这些直方图对许多谓词都不起作用，也无法检测属性之间的（反）相关性。请注意，在运行各自的统计数据收集工具后，我们使用默认设置获得了所有估计值。一些商业系统支持使用抽样进行基表估计、多属性直方图（“列组统计”）或以前查询运行的事后反馈[38]。但是，这些功能在默认情况下要么未启用，要么不是完全自动的。

3.2 Estimates for Joins

现在让我们把注意力转向连接的中间结果的估计，这更具挑战性，因为采样或直方图不能很好地工作。图3在一张图中总结了超过100000个基数估计。对于查询集的每个中间结果，我们计算估计值与真实基数不同的因素，区分高估和低估。该图显示了每个中间结果大小的一个“方框图”（请注意左下角的图例），这允许人们比较错误是如何随着连接数量的增加而变化。纵轴使用对数标度来包含因子 $10^{8}$ 的低估和因子 $10^{4}$ 的高估。

尽管DBMS A的基表估计更好，但除DBMS B外，连接估计误差的总体方差（如方框图所示）对于所有系统都是相似的。对于所有系统，我们通常观察到1000或更大的错误估计。此外，正如方框图高度的增加所证明的那样，随着连接数量的增加，误差呈指数级增长（注意对数标度）[21]。对于PostgreSQL，16%的1个连接的估计值是错误的，错误系数为10或更多。如果有2个连接，则此百分比将增加到32%，如果有3个连接，将增加到52%。对于我们比较的系统中具有最佳估计器的DBMS A，相应的百分比仅略好，在15%、25%和36%。

另一个引人注目的观察结果是，所有经过测试的系统(尽管DBMS A程度较低)都倾向于系统性地低估具有多个连接的查询的结果大小。这可以从图3中误差分布的中位数推断出来。对于我们的查询集，确实会出现中间结果随着连接数量的增加而减少的情况，因为应用了更多的基表选择。然而，真正的减少要小于PostgreSQL使用的独立性假设(显然其他系统也是如此)所预测的。低估在DBMS B中最为明显，它经常对具有2个以上连接的查询估计1行。另一方面，DBMS A的估计值的中位数更接近事实，尽管它们的方差与其他一些系统相似。我们推测，DBMS A使用依赖于连接大小的阻尼因子，类似于多少优化器组合了多个选择性。许多估计器结合多个谓词的选择性(例如，对于基本关系或具有多个连接的子表达式)不是通过假设完全独立性，而是通过使用阻尼因子“向上”调整选择性。这样做的动机源于这样一个事实：需要应用的谓词越多，它们的独立性就越不确定。

考虑到PostgreSQL的连接估计公式的简单性(参见第2.3节)以及它的估计与商业系统竞争的事实，我们可以推断出当前的连接大小估计器是基于独立性假设的。没有测试的系统能够检测到连接交叉相关性。此外，基数估计是非常脆弱的，正如我们观察到的大量极大错误(因子1000或更多)和以下事情所说明的那样：在PostgreSQL中，我们观察到相同的简单2-join查询的基数估计不同，这取决于where子句中from and/or join谓词中关系的语法顺序！通过简单地交换谓词或关系，我们观察到对于同一个查询，估计有3、9、128或310行(真实基数为2600)。

注意，本节没有对不同系统的查询优化器进行基准测试。特别是，我们的结果并不意味着DBMS B的优化器或由此产生的查询性能一定比其他系统差，尽管估计器中的误差更大。查询运行时间在很大程度上取决于系统的优化器如何使用这些估计值，以及它对这些数字的信任程度。复杂的引擎可能采用自适应算子(例如[4,8])，从而减轻误估的影响。然而，结果确实表明，基数估计的最新技术远非完美。

3.3 Estimates for TPC-H

我们前面已经说过，TPC-H中的基数估计是一个相当微不足道的任务。图4通过显示3个较大的TPC-H查询和4个JOB查询的PostgreSQL估计误差的分布，证实了这一说法。请注意，在图中，我们报告了单个查询的估计错误(而不是像图3中那样报告所有查询的估计错误)。显然，TPC-H查询工作负载并没有给基数估计器带来许多困难的挑战。相反，我们的工作负载包含的查询通常会导致严重的高估和低估错误，因此可以认为是基数估计的一个具有挑战性的基准。

3.4 Better Statistics for PostgreSQL

如2.3节所述，在PostgreSQL中，连接估计最重要的统计数据是不同值的数量。这些统计数据是根据固定大小的样本估计的，我们观察到对于大型表的严重低估。为了确定错误估计的不同计数是否是基数估计的潜在问题，我们精确地计算了这些值，并用真实值替换了估计值。

图5显示，真正的不同计数略微改善了误差的方差。然而，令人惊讶的是，低估基数的趋势变得更加明显。原因是，最初的、被低估的不同计数导致了更高的估计，而这些估计偶然地更接近事实。这是谚语“两错得正”的一个例子，即两个错误(部分地)相互抵消。这种行为使得分析和修复查询优化器问题非常令人沮丧，因为修复一个查询可能会破坏另一个查询。

糟糕的基数估计何时会导致查询变慢?

虽然前一节中显示的大的估计错误确实令人警醒，但是大的错误并不一定会导致缓慢的查询计划。例如，与查询的其他部分相比，错误估计的表达式可能比较便宜，或者相关的计划替代可能被类似的因素错误估计，从而“抵消”了原始错误。在本节中，我们将研究糟糕基数可能导致查询缓慢的条件。

一个重要的观察是，查询优化与物理数据库设计密切相关：索引的类型和数量严重影响计划搜索空间，因此影响系统对基数错误估计的敏感程度。因此，在本节开始时，我们将使用一个只有主键索引的相对健壮的物理设计进行实验，并表明在这样的设置中，基数错误估计的影响可以在很大程度上得到缓解。之后，我们演示了对于具有许多索引的更复杂的配置，基数错误估计使其更有可能在很大程度上错过最佳计划。

4.1 The Risk of Relying on Estimates

为了测量基数错误估计对查询性能的影响，我们将不同系统的估计注入到PostgreSQL中，然后执行结果计划。使用相同的查询引擎，可以通过(很大程度上)从不同的查询执行引擎中抽象出来，孤立地比较基数估计组件。此外，我们注入真正的基数，它计算相对于成本模型的最优计划。我们根据运行时相对于最佳计划的减速对其进行分组，并在下表中报告分布情况，其中每列对应一组：

少数查询使用正确而不是错误的基数会稍微变慢。这种影响是由成本模型错误引起的，我们将在第5节中对此进行讨论。然而，正如预期的那样，当使用估计时，绝大多数查询都会较慢。使用DBMS A的估计，78%的查询比使用正确基数慢不到2倍，而对于DBMS B，只有53%的查询是这种情况。这证实了上一节中关于基数估计的相对质量的发现。不幸的是，所有的估计器偶尔都会导致计划花费不合理的时间并导致超时。然而，令人惊讶的是，尽管我们在下文中显示了糟糕的估计，但许多观察到的减速很容易避免。

当使用估计值查看未在合理时间内完成的查询时，我们发现大多数查询都有一个共同点：PostgreSQL的优化器决定引入嵌套循环连接（无需索引查找），因为基数估计值非常低，而实际上真实基数更大。正如我们在上一节中看到的，系统性的低估经常发生，这偶尔会导致引入嵌套循环连接。

PostgreSQL选择嵌套循环连接的根本原因是它完全基于成本来选择连接算法。例如，如果使用嵌套循环连接算法的成本估计为1,000,000，而使用哈希连接的成本估计为1,000,001，那么即使存在允许使用哈希的相等连接谓词，PostgreSQL也将始终倾向于使用嵌套循环算法。当然，考虑到嵌套循环连接的复杂度为O( $n^{2}$ )，哈希连接的复杂度为O(n)，并且经常低估，这个决定是非常危险的。即使估计是正确的，与哈希连接相比，嵌套循环连接的任何潜在性能优势都非常小，因此承担这种高风险只能带来非常小的回报。

因此，我们在接下来的所有实验中禁用了嵌套循环连接(而不是索引嵌套循环连接)。如图6b所示，在不使用这些有风险的嵌套循环连接的情况下重新运行所有查询时，尽管使用了PostgreSQL的估计，但我们没有观察到更多的超时。

此外，尽管有更少的连接算法选项，但没有一个查询的执行速度比以前慢，这证实了我们的假设，即嵌套循环连接(没有索引)很少有任何好处。然而，这个更改并不能解决所有问题，因为与真实基数相比，仍然有许多查询的速度要慢10倍以上(参见红条)。

在调查剩余查询仍然没有达到预期效果的原因时，我们发现大多数查询都包含一个哈希连接，其中构建输入的大小被低估了。PostgreSQL(包括9.4版本)基于基数估计选择内存哈希表的大小。估计过低会导致哈希表的大小过小，冲突链很长，因此性能很差。即将发布的9.5版本会根据哈希表中实际存储的行数在运行时调整哈希表的大小。我们将这个补丁反向移植到基于9.4的代码库中，并在所有剩余的实验中启用它。图6c显示了禁用嵌套循环连接后的更改效果。与真实基数相比，只有不到4%的查询偏差超过2倍。

总而言之，“纯粹基于成本”，即不考虑基数估计的固有不确定性和不同算法选择的渐近复杂性，可能导致非常糟糕的查询计划。不应该选择那些很少比更健壮的算法提供更大好处的算法。此外，如果可能的话，查询处理算法应该在运行时自动确定它们的参数，而不是依赖于基数估计。

4.2 Good Plans Despite Bad Cardinalities

具有不同连接顺序的计划的查询运行时间通常相差很多数量级(参见第6.1节)。然而，当数据库只有主键索引时，就像到目前为止的所有实验一样，一旦禁用了嵌套循环连接并启用了重新哈希，大多数查询的性能接近使用真实基数获得的性能。考虑到基数估计的质量很差，我们认为这是一个令人惊讶的积极结果。有必要反思一下为什么会出现这种情况。

主要原因是，如果没有外键索引，大多数大型(“fact”)表需要使用全表扫描进行扫描，这会影响不同连接顺序的效果。连接顺序仍然很重要，但结果表明，基数估计通常足够好，可以排除所有灾难性的连接顺序决策，比如使用非选择性连接谓词连接两个大表。另一个重要的原因是，在主内存中选择一个索引嵌套循环连接，而哈希连接可能更快，这从来都不是灾难性的。在完全缓存所有数据和索引的情况下，我们测量了哈希连接相对于索引嵌套循环连接的性能优势，在PostgreSQL中最多为5倍，在HyPer中最多为2倍。显然，当必须从磁盘读取索引时，随机IO可能会导致更大的因素。因此，主存设置的容忍度要高得多。

4.3 Complex Access Paths

到目前为止，所有的查询执行都是在数据库上执行的，索引仅基于主键属性。为了查看当有更多索引时查询优化问题是否变得更加困难，我们额外索引了所有外键属性。图7b显示了额外的外键索引的效果。我们看到了巨大的性能差异，40%的查询速度慢了2倍！请注意，这些结果并不意味着添加更多索引会降低性能(尽管这种情况偶尔会发生)。实际上，总体性能通常会显著提高，但是可用的索引越多，查询优化器的工作就越困难。

4.4 Join-Crossing Correlations

在我们的社区中有一个共识，即在关联查询谓词存在的情况下对中间结果基数的估计是查询优化研究的前沿。本文研究的JOB工作负载由实际数据组成，其查询包含许多相关谓词。我们专注于单表子查询基数估计质量的实验(参见表1)表明，保留表样本(HyPer和DBMS A)的系统可以获得几乎完美的估计结果，甚至对于相关谓词(在同一表内)也是如此。因此，基数估计研究的挑战似乎存在于相关谓词涉及通过joins连接的不同表中的列的查询中。我们称之为“join-crossing correlations”。这种相关性经常出现在IMDB数据集中，例如，出生在巴黎的演员很可能在法国电影中扮演角色。

考虑到这些连接交叉相关性，人们可能想知道是否存在允许利用这些关联的复杂访问路径。一个与此相关的例子是ROX[22]，尽管它最初是在XQuery处理中设置的。它研究了DBLP共同作者查询上下文中的运行时连接顺序查询优化，该查询计算了在三个特定场所中有多少Authors发表了Papers。这些连接来自不同场所的作者集的查询显然具有连接交叉相关性，因为在VLDB中发表文章的作者通常是数据库研究人员，可能也在SIGMOD中发表文章，但不是在Nature中发表文章。

在DBLP的情况下，Authorship是一个n:m关系，它将关系Authors与关系Papers链接起来。[22]中的最优查询计划使用index-nested-loop连接，将每个作者查找到Authorship.author(索引的主键)中，然后对Paper.venue进行筛选限制，这需要使用另一个连接进行查找。venue上的过滤器通常必须在这两个连接之后计算。然而，[22]存储Authorship的物理设计是由Paper.venue分割的。这种分区产生了惊人的效果：不是只有一个Authorship表和一个主键索引，而是物理上有很多，每个venue分区都有一个。这意味着通过访问正确的分区，在连接发生之前隐式地强制执行过滤器(免费的)。因此，这种特定的物理设计导致最佳计划如下：首先将来自SIGMOD的较小的authorship集与来自Nature的较大的authorship集连接起来，几乎不会产生任何结果元组，从而使随后的嵌套循环索引查找连接到VLDB的成本非常低。如果这些表没有被分区，那么从所有SIGMOD作者到Authorships的索引查找将首先找到所有合著的论文，其中绝大多数是不相关的，因为它们是关于数据库研究的，并且没有发表在Nature上。如果没有这种分区，就无法避免这种大的中间结果，并且没有查询计划在效率上接近分区情况：即使基数估计能够预测连接交叉相关性，也没有物理方法可以从这种知识中获益。

从这个例子中可以得出的教训是，查询优化的效果总是由访问路径方面的可用选项决定的。如[22]中所示，在连接交叉谓词上使用分区索引是一种不明显的物理设计替代方案，它甚至会通过引入连接交叉列(Paper.venue)作为表(Authorship)的分区键来修改模式。分区DBLP设置只是如何处理特定连接交叉相关性的一个示例，而不是通用的解决方案。连接交叉相关性仍然是数据库研究的一个开放前沿，涉及物理设计、查询执行和查询优化的相互作用。在我们的JOB实验中，我们没有尝试绘制这个大部分未知空间的图表，而是描述(连接交叉)相关性对当前最先进的查询处理的影响，限制我们使用标准的PK和FK索引。

成本模型

成本模型指导从搜索空间中选择计划。当代系统的成本模型是复杂的软件工件，是30多年研究和开发的结果，主要集中在传统的基于磁盘的系统领域。例如，PostgreSQL的成本模型由4000多行C代码组成，并考虑了各种微妙的因素，例如，它考虑了部分相关的索引访问、感兴趣的顺序、元组大小等。因此，评估复杂的成本模型对整体查询性能的实际贡献是很有趣的。

首先，我们将通过实验建立PostgreSQL成本模型(基于磁盘的DBMS的典型成本模型)与查询运行时间之间的相关性。然后，我们将把PostgreSQL的成本模型与另外两个成本函数进行比较。第一个成本模型是PostgreSQL模型的优化版本，适用于所有数据都放入RAM的主存设置。第二个成本模型是一个非常简单的函数，它只考虑查询估计过程中产生的元组数量。我们发现，毫不奇怪，成本模型之间的差异与基数估计误差相比相形见绌。我们在具有外键索引的数据库实例上进行了实验。我们首先简要介绍一个典型的面向磁盘的复杂成本模型，即PostgreSQL模型。

5.1 The PostgreSQL Cost Model

PostgreSQL的面向磁盘的成本模型将CPU和I/O成本以一定的权重结合在一起。具体地说，一个算子的成本被定义为访问磁盘页面(包括顺序的和随机的)的数量和在内存中处理的数据量的加权和。查询计划的成本是所有算子成本的总和。总和中使用的权重参数的默认值(成本变量)由优化器设计人员设置，旨在反映随机访问、顺序访问和CPU成本之间的相对差异。

PostgreSQL文档包含以下关于成本变量的说明：“不幸的是，没有定义良好的方法来确定成本变量的理想值。最好将它们视为特定安装将接收到的整个查询组合的平均值。这意味着仅仅根据几个实验就改变它们是非常危险的。”对于需要实际设置这些参数的数据库管理员来说，这些建议并不是很有帮助；毫无疑问，大多数人不会改变这些参数。这个评论当然不是针对PostgreSQL的，因为其他系统也有类似的复杂成本模型。一般来说，调整和校准成本模型(基于采样，各种机器学习技术等)一直是许多论文的主题(例如，[42,25])。因此，研究成本模型对整体查询引擎性能的影响是很重要的。这将间接显示成本模型错误对查询性能的贡献。

5.2 Cost and Runtime

成本函数的主要优点是它能够预测在给定基数估计的情况下，哪个备选查询计划将是最快的；换句话说，重要的是它与查询运行时间的相关性。PostgreSQL中查询的成本和运行时间之间的关系如图8a所示。此外，我们还考虑了引擎注入了真实基数的情况，并在图8b中绘制了相应的数据点。对于这两个图，我们拟合线性回归模型(显示为一条直线)并突出显示标准误差。在这两种情况下，查询的预测成本与其运行时间相关。然而，差的基数估计会导致大量的异常值和非常宽的标准误差区域，如图8a所示。正如之前所观察到的[42]，只有使用真实的基数才能使PostgreSQL成本模型成为运行时间的可靠预测器。

直观地看，图8中的一条直线对应于一个理想的成本模型，该模型总是为更昂贵的查询分配(预测)更高的成本。当然，任何单调递增的函数都可以满足这一要求，但是线性模型提供了最简单和最接近观测数据的拟合。因此，我们可以将偏离这条线的情况解释为成本模型的预测误差。具体来说，我们考虑查询Q的成本模型的绝对百分比误差：

，其中 $T_{real}$ 是观察到的运行时间， $T_{pred}$ 是我们的线性模型预测的运行时间。使用PostgreSQL的默认成本模型和真实基数，成本模型的中值误差为38%。

5.3 Tuning the Cost Model for Main Memory

如上所述，成本模型通常涉及由数据库管理员进行调优的参数。在基于磁盘的系统(如PostgreSQL)中，这些参数可以分为CPU成本参数和I/O成本参数，默认设置反映了假设工作负载中这两类之间的预期比例。

在许多设置中，默认值不是最优的。例如，PostgreSQL的默认参数值表明处理元组比从页面读取元组便宜400倍。然而，现代服务器经常配备非常大的RAM容量，并且在许多工作负载中，数据集实际上完全适合可用内存(不可否认，PostgreSQL的core在几十年前形成的，当时数据库服务器只有几兆字节的RAM)。这并不能完全消除页面访问成本(由于缓冲区管理器的开销)，但可以显著弥合I/O和CPU处理成本之间的差距。

可以说，在主内存工作负载的成本模型中需要做的最重要的更改是减少这两组之间的比例。我们通过将CPU成本参数乘以50来实现这一点。使用改进的参数运行工作负载的结果绘制在图8中间的两个子图中。比较图8b和图8d，我们看到调优确实改善了成本和运行时间之间的相关性。另一方面，从比较图8c和图8d可以明显看出，参数调优改进仍然被估计基数和真实基数之间的差异所掩盖。请注意，图8c中有一组异常值，优化器无意中发现了非常好的计划(运行时间约为1 ms)，而没有意识到这一点(因此成本非常高)。这是查询规划中由基数错误估计引起的“振荡”的另一个迹象。

此外，我们测量了调优的成本模型的预测误差ε，如章节5.2所定义。我们观察到调优提高了成本模型的预测能力：中位数误差从38%下降到30%。

5.4 Are Complex Cost Models Necessary?

如上所述，PostgreSQL的成本模型非常复杂。据推测，这种复杂性应该反映影响查询执行的各种因素，例如磁盘查找和读取的速度、CPU处理成本等。为了找出这种复杂性在主存设置中是否真的是必要的，我们将它与一个非常简单的成本函数 $C_{mm}$ 进行对比。这个成本函数是为主存设置量身定制的，因为它不模拟I/O成本，而只计算在查询执行期间通过每个算子的元组的数量：

在上面的公式中，R是一个基本关系，τ≤1是一个参数，与连接相比，该表扫描的成本会打折扣。成本函数区分hash $\bowtie ^{HJ}$ 和index-nested loop $\bowtie ^{INL}$ 连接：后者扫描 $T_{1}$ 并对R上的索引执行索引查找，从而避免对R进行全表扫描。当索引嵌套循环连接的右侧有一个选择时，会出现一种特殊情况，在这种情况下，我们考虑基表索引中元组查找的次数，并从成本计算中放弃选择(因此使用乘数

，接上)对于索引嵌套循环连接，我们使用常数λ≥1来估计索引查找比哈希表查找的开销大多少。具体地说，我们设λ = 2， τ = 0.2。与我们之前的实验一样，当内部关系不是索引查找时(即，非索引嵌套循环连接)，我们禁用嵌套循环连接。

使用 $C_{mm}$ 作为成本函数运行我们的工作负载的结果如图8e和8f所示。我们看到，即使我们微不足道的成本模型也能够使用真正的基数相当准确地预测查询运行时间。为了量化这个论点，我们通过改变真实基数的成本模型来衡量运行时间的改进：就所有查询的几何平均值而言，我们的优化成本模型比标准PostgreSQL模型的运行时间快41%，但即使是一个简单的 $C_{mm}$ 也比内置成本函数的查询速度快34%。这种改进并非微不足道，但另一方面，当我们用实际基数替换估计基数时，所观察到的查询运行时间的改进就显得微不足道了(参见图6b)。这允许我们重申我们的主要信息，即基数估计比成本模型重要得多。

计划空间

除了基数估计和成本模型之外，最后一个重要的查询优化组件是计划枚举算法，该算法探索语义等效连接顺序的空间。已经提出了许多不同的算法，包括穷举算法(例如[29,12])和启发式算法(例如[37,32])。这些算法在选择最佳计划时考虑不同数量的候选解决方案(构成搜索空间)。在本节中，我们将研究需要多大的搜索空间才能找到一个好的计划。

本节的实验使用了一个独立的查询优化器，它实现了动态规划(DP)和许多启发式连接枚举算法。我们的优化器允许注入任意基数估计。为了充分探索搜索空间，在本节中我们没有实际执行优化器生成的查询计划，因为由于我们的查询具有大量的连接，这将是不可行的。相反，我们首先使用估计值作为输入运行查询优化器。然后，我们用真正的基数重新计算结果计划的成本，为我们提供一个非常接近计划实际运行时间的值。我们使用第5.4节中的内存成本模型，并假设它完美地预测了查询运行时间，对于我们的目的来说，这是一个合理的假设，因为成本模型的错误与基数错误相比可以忽略不计。这种方法允许我们在不执行所有计划的情况下比较大量的计划。

6.1 How Important Is the Join Order?

我们使用Quickpick[40]算法来可视化不同连接顺序的成本。Quickpick是一种简单的随机算法，它随机选择连接边，直到所有连接关系完全连接。每次运行都会生成一个正确的，但通常很慢的查询计划。通过每个查询运行该算法10,000次并计算结果计划的成本，我们获得了随机计划成本的近似分布。图9显示了5个代表性示例查询和三种物理数据库设计的密度图：没有索引、只有主键索引和主键+外键索引。通过运行动态规划和真实基数获得的最优计划(带有外键索引)将成本归一化。

这些图在水平成本轴上使用对数刻度，清楚地说明了连接排序问题的重要性：最慢甚至中位数成本通常比最便宜的计划要贵几个数量级。分布的形状各不相同。对于某些查询，有许多好的计划(例如，25c)，而对于其他查询，则很少(例如，16d)。分布有时宽(例如，16d)，有时窄(例如，25c)。“无索引”和“PK 索引”配置的图非常相似，这意味着对于我们的工作负载，单独的主键索引并不能很大程度地提高性能，因为我们在主键列上没有选择。在许多情况下，“PK+FK 索引”分布在图的左侧有额外的小峰，这意味着在这种指数配置下的最优计划比在其他配置下要快得多。

我们还分析了整个工作负载，以确认这些直观的观察结果：不带索引的计划比最优计划最多贵1.5倍的计划的百分比为44%，比带主键索引的计划的百分比为39%，而比带外键索引的计划的百分比仅为4%。最差计划和最佳计划之间的平均分数，即分布宽度，无索引时为101倍，有主键索引时为115倍，有外键索引时为48120倍。这些汇总统计数据突出显示了三种索引配置的显著不同的搜索空间。

6.2 Are Bushy Trees Necessary?

大多数连接排序算法不列举所有可能的树形状。实际上，所有优化器都忽略跨产品的连接顺序，这将大大缩短优化时间，而对查询性能的影响可以忽略不计。Oracle走得更远，没有考虑密集连接树[1]。为了量化限制搜索空间对查询性能的影响，我们修改了DP算法，只枚举左深树、右深树或之字形树。

除了明显的树形状限制外，这些类中的每一个都暗示了对连接方法选择的约束。我们遵循加西亚-莫利纳等人的教科书中的定义，这与Ramakrishnan和Gehrke的书中的定义相反：使用哈希连接，右深树首先从每个关系中创建哈希表，然后以管道方式在所有这些哈希表中进行探查，而在左深树中，从每个连接的结果构建一个新的哈希表。之字形树是所有左深树和右深树的超集，在之字形树中，每个连接算子必须至少有一个基本关系作为输入。对于index-nested循环连接，我们还采用以下约定：连接的左子节点是元组的来源，这些元组在右子节点的索引中查找，右子节点必须是一个基表。

使用真正的基数，我们计算三种受限树形状的最优计划的成本。我们将这些成本除以最优树(它可以有任何形状，包括“密集”)，从而测量通过限制搜索空间而损失的性能。表2中的结果显示，之字形树在大多数情况下提供了不错的性能，最坏的情况比最佳密集计划的成本高2.54倍。正如预期的那样，左深树比之字形树更差，但仍然可以产生合理的性能。另一方面，右深树的表现比其他树的形状差得多，因此不应该专门使用。右深树的糟糕性能是由于需要从每个基本关系创建大型中间哈希表以及只能通过索引查找完成最底部的连接这一事实造成的。

6.3 Are Heuristics Good Enough?

到目前为止，我们使用了动态规划算法来计算最优连接顺序。然而，考虑到基数估计的质量很差，人们可能会合理地问是否有必要使用穷举算法。因此，我们将动态规划与随机启发式和贪婪启发式进行比较。

“Quickpick-1000”启发式是一种随机算法，它选择最便宜的(基于估计的基数)1000个随机计划。在所有的贪婪启发式算法中，我们选择了Greedy Operator Ordering(GOO)，因为它被证明优于其他确定性近似算法[11]。GOO维护一组连接树，每个连接树最初由一个基本关系组成。然后，该算法将成本最低的一对连接树组合为单个连接树。Quickpick-1000和GOO都可以生成复杂的计划，但显然只能探索部分搜索空间。本实验中的所有算法都在内部使用PostgreSQL的基数估计值来计算查询计划，我们使用真实的基数来计算“真实”成本。

表3显示，尽管基数估计错误，但使用动态规划全面检查搜索空间是值得的。然而，由估计误差引入的误差比启发式算法造成更大的性能损失。与其他一些启发式方法(例如[5])相比，GOO和Quickpick-1000并不真正意识到索引。因此，GOO和Quickpick-1000在可用索引很少的情况下工作得更好，在有更多好的计划的情况下也是如此。

总而言之，我们的结果表明，与仅枚举搜索空间的子集的算法相比，详尽地枚举所有密集树提供了适度但并非微不足道的性能优势。好的基数估计带来的性能潜力肯定要大得多。然而，考虑到穷举枚举算法的存在，它可以非常快速地为具有数十个关系的查询找到最优解(例如，[29,12])，很少有必要使用启发式或禁用密集树的情况。

结论及未来工作

在本文中，我们为传统智慧提供了定量证据，这些智慧是在三十年的查询优化器实践经验中积累起来的。我们已经证明查询优化对于有效的查询处理是必不可少的，并且穷举枚举算法比启发式算法找到更好的计划。我们还展示了关系数据库系统会产生很大的估计错误，并且随着连接数量的增加而迅速增加，而这些错误通常是糟糕计划的原因。与基数估计相反，成本模型对整体查询性能的贡献是有限的。

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

我们鼓励社区使用Join Order Benchmark作为进一步实验的测试平台，例如复杂访问路径的风险/回报权衡。此外，研究驻留磁盘数据库和分布式数据库会很有趣，它们提供了与主内存设置不同的挑战。

YannAdams

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
How Good Are Query Optimizers, Really？【论文内容翻译】

寻找良好的连接顺序问题是数据库领域研究最多的问题之一。图1展示了经典的、基于成本的方法，它可以追溯到System R[36]。为了获得有效的查询计划，查询优化器枚举有效连接顺序的某些子集，例如使用动态规划。使用基数估计作为其主要输入，成本模型然后从语义等效的计划备选方案中选择最便宜的备选方案。理论上，只要基数估计和成本模型准确，该体系结构就能得到最优的查询计划。实际上，基数估计通常是基于简化的假设，如一致性和独立性来计算的。
复制链接

扫一扫

专栏目录