2018年，CockroachDB从头开始实现了基于成本的查询优化器，在每个版本中都得到了稳步的改进。查询优化器是系统中理解SQL查询语义并决定如何执行的部分；执行计划在执行时间上会有很大的不同，所以选择一个好的计划很重要。在这篇文章中，我们将介绍CockroachDB v20.1中一些与优化器相关的改进。
我们所做的很大一部分改变都是围绕着添加转换规则来改善各种类型的查询，因此要详细讨论的内容太多。在这个版本中，我们增加了15个新的转换规则（总共224个）。然而，有几个重要的领域值得讨论：优化器驱动的外键检查；极限 "提示 "的传播；WITH RECURSIVE子句；以及一种提取查询计划和执行信息的新方法。
优化器驱动的外键检查
CockroachDB从第一个版本开始就支持外键，并在v2.0版本中加入了外键操作。从那时起，SQL层发生了很多变化--最明显的是，我们现在有了一个具有重要功能的优化器，比如定位意识。然而，外键的基础结构基本上没有变化，仍然使用优化器之前的原始设计。我们一直在努力重新实现外键，以便它们能够利用优化器及其不断增长的智能，在v20.1中，外键检查默认是由优化器计划的。在这一节中，我们将详细介绍外键以前是如何工作的，现在又是如何工作的。
外键描述了两个表之间的关系；在一个特定的关系中，我们称一个表为 "子 "表，另一个为 "父 "表。外键将子表中某一列的可能值限制在父表（同样是某一列）中存在的一组值。一个简单的例子是考虑一个客户的父表和一个订单的子表。订单表有一个 "客户 "列；通过声明一个外键约束，我们说订单只能指向有效的客户。
强制执行该约束是数据库的责任。这意味着，如果我们插入一个订单，我们必须检查该客户是否有效（存在于客户表中）。相反，当我们删除一个客户时，我们必须确保该客户没有剩余的订单，这将成为 "孤儿"。
这就带来了一个问题：我们究竟如何执行这些FK（外键）检查？我们最初的实现是通过要求子表和父表在外键列上都有索引来解决这个问题。然后，这种关系实际上是这两个索引之间的映射，而索引将始终被用来执行FK检查。
这种方法的问题是，它在模式方面有限制性。额外的索引带来了开销（在存储和插入过程中的额外工作方面），在某些情况下，它们并没有帮助加快工作负载的任何重要方面。在一个多区域的部署中，一个特定的索引总是被使用，这一事实阻止了对重复索引模式的有效使用，在这种模式下，每个区域都有自己的本地访问索引。
新的实现方式消除了这些桎梏，将外键关系与索引分离开来。现在，优化器将外键检查建立为通用的 "子查询"，并将它们与相关的突变一起计划。这允许优化器使用其通常的逻辑来确定执行这些检查的最佳方式（包括对位置敏感的索引选择）。
不幸的是，有一个陷阱。外键操作可以用于更复杂的行为，比如将父表中的删除自动级联到子表中的删除。我们还没有重新实现级联，所以我们必须退回到实现这些动作的现有代码（仍然依赖于双索引方案）。因为现有的代码需要继续发挥作用，所以我们无法为v20.1删除双索引的要求。然而，我们正在将此作为v20.2的一个高度优先项目进行工作。尽管有这样的注意事项，这项工作在v20.1中确实带来了切实的好处--最重要的是外键检查的位置敏感索引选择。
软限制
优化器从一开始就对Limit 算子有各种规则。特别是，我们尽可能地 "压低 "限制（"压低 "是一种花哨的说法，即我们尽可能地在执行过程中尽早应用限制）。但是，允许推倒Limit 算子的情况是相当严格的。这是因为该操作符是一个 "硬 "限制，意味着我们只有在绝对确定一定数量的行足以得到一个正确的查询结果时才能使用它。
有很多情况下，我们可以猜测（估计）一个限制，而没有硬性的保证。例如，考虑一个简单的查询，如SELECT * FROM t WHERE status=’done’ LIMIT 10 。这个查询的执行计划（假设我们在status 上没有索引）是进行全表扫描，只保留通过过滤器的前10条记录。在最坏的情况下，我们可能需要读取整个表；但是在实践中，根据有多少行通过条件，我们可能需要扫描的行数要少很多。在这个计划的执行过程中，一旦产生了10条记录，执行将提前停止，所以我们实际上不会扫描整个表。然而，优化器在估计该计划的成本时并没有考虑到这一点，这意味着它可能选择了另一个它错误地认为成本较低的计划。除了帮助选择正确的计划外，对我们实际需要扫描的行数的估计也有助于提高工作效率：我们可以配置扫描的内部 "批次 "大小（我们默认检索10000个键！）。
在20.1版本中，我们增加了 "软 "限制的基础设施。软限制是优化器内部的一个属性，它被视为一个提示，大致定义为 "没有承诺，但是在这个操作符产生了这么多行之后，执行可能会提前完成"。在上面的例子中，我们使用表的统计数据来估计status='done' 条件的选择性，并计算出扫描的软限制（根据软限制来确定其初始批次的键值）。如果统计数据显示（例如）大约一半的行有status='done' ，那么软限制将是20。
这项工作的一个重要方面是，优化器现在能够通过考虑早期执行完成情况来更准确地估计表达式的成本。在更复杂的情况下，特别是涉及到查找连接时，修正后的成本计算会产生巨大的差异（这里很好地描述了一个例子）。
鸣谢。感谢Celine O'Neil，她在实习期间做了软限制的工作。
递归CTEs
我们在这个版本中实现的一个新功能是支持WITH RECURSIVE 语法，该语法定义了一个递归的共表表达式。这改善了我们对一般SQL的覆盖，特别是对PostgreSQL的兼容性。
有趣的是，递归CTE的增加使得SQL成为一种图灵完备的语言：原则上你可以用SQL来进行任何计算（就像一种成熟的编程语言）；请看这个演示。在实践中，这意味着你可以做一些很酷的事情，比如分形。
使用递归CTE在SQL中生成的Sierpinski三角。 源于此。Michael Malis
语句诊断捆绑
20.1版本还为优化器添加了一个语句诊断包。CockroachDB提供了一些方法来了解查询的计划和执行情况。
不幸的是，在向我们报告问题时，没有一个用户可以采取单一的行动来提取所有的相关信息。在许多情况下，这导致了用户和我们的工程师在调查问题时来回耗费时间。
将这些信息统一成用户容易消费的东西，这个问题很复杂，我们正在努力攻克它。在短期内，我们希望至少能简化信息的收集工作。解决方案当然是引入EXPLAIN的另一个变种：在v20.1中，EXPLAIN ANALYZE (DEBUG)类似于EXPLAIN ANALYZE ，但会创建一个语句诊断包：一个.zip文件，包含来自许多 "口味 "的EXPLAIN 的所有信息，以及完整的表统计和查询跟踪数据。该包可以从AdminUI中下载。
我们还引入了一种新的机制来触发这些信息的收集：从AdminUI的语句页面，用户可以选择一个语句指纹并激活诊断功能。下次运行带有此指纹的查询时，将自动收集诊断包。
结论
从头开始建立一个基于成本的SQL优化器不是一个小的努力，我们将在未来的版本中继续增加更多的改进和功能。要开始使用这个版本中的新功能，请下载CockroachDB v20.1。
而且，如果你想建立一个SQL优化器，Cockroach Labs正在招聘。
