postgres 基本语句_通过Postgres Internals执行Select语句

postgres 基本语句

这是Pat Shaughnessy根据其在巴塞罗那Ruby会议上的演讲而撰写的一系列Postgres帖子中的第三篇。 您还可以观看演示文稿的录像 。 该系列最初发布在他的个人博客上，经他的允许，我们正在Codeship上重新发布该系列。 您还可以阅读该系列的文章一和文章 二 。

尼莫船长带领Aronnax教授参观了机舱，从1870年的角度对未来技术进行了有趣的描述。

为在夏季准备此演示文稿，我决定通读PostgreSQL C源代码的一部分。

我执行了一个非常简单的select语句，并使用C调试器LLDB看着Postgres做了什么。 Postgres如何理解我的查询？ 它实际上是如何找到我要寻找的数据的？

这篇文章是我通过Postgres胆量旅行的非正式日记。 我将描述我走过的路以及在此过程中所看到的一切。 我将使用一系列简单的概念图来说明Postgres如何执行查询。

如果您了解C，我还将给您留下一些地标和路标，如果您决定入侵Postgres内部，可以寻找一下。

最后，Postgres源代码使我高兴。 它很干净，有据可查，并且易于遵循。

跟着我深入您每天使用的工具的旅程，亲自了解Postgres的内部运作方式。

寻找尼莫船长

这是我演讲前半部分的示例查询； 我们将跟随Postgres搜索Nemo上尉：

Aronnax教授和Nemo上尉绘制了鹦鹉螺的路线。

在这样的字符串列中查找单个名称应该很简单，不是吗？ 在探索Postgres内部时，我们将紧紧抓住这个select语句，就像深海潜水员用绳索找到返回地面的方式一样。

大图景

Postgres使用此SQL字符串做什么？ 它如何理解我们的意思？ 它如何知道我们在寻找什么数据？

Postgres处理每条SQL命令，我们使用四个步骤处理它。

第一步，Postgres 解析我们SQL语句，并将其转换为一系列C内存结构，即解析树 。 Next Postgres将分析并重写我们的查询，并使用一系列复杂的算法对其进行优化和简化。 之后，Postgres会生成一个计划以查找我们的数据。 在有计划之前，Postgres不会运行我们的查询。 最后，Postgres实际上执行我们的查询。 在此演示文稿中，我将简要介绍前三个主题，然后将更多的精力放在最后一步： Execute上 。

实现此四步过程的Postgres内部的C函数称为exec_simple_query 。 您可以在下面找到它的链接，以及LLDB回溯，该回溯提供了有关Postgres何时以及如何调用exec_simple_query的确切上下文。

解析

Postgres如何理解我们发送SQL字符串？ 它对我们的select语句中SQL关键字和表达式有何意义？ 通过一个称为解析的过程，Postgres将我们SQL字符串转换为它理解的内部数据结构，即解析树。

事实证明，Postgres使用与Ruby相同的解析技术，即一种称为Bison的解析器生成器 。 Bison在Postgres C生成过程中运行，并根据一系列语法规则生成解析器代码。 向我们发送SQL命令时，生成的解析器代码就是在Postgres内部运行的代码。 当生成的解析器在SQL字符串中找到相应的模式或语法并将新的C内存结构插入解析树数据结构中时，将触发每个语法规则。

我今天不花时间解释解析算法的详细工作原理。 如果您对这种事情感兴趣，建议您看一下我的书Ruby Under a Microscope 。 在第一章中，我详细介绍了Bison和Ruby使用的LALR解析算法的示例。 Postgres以完全相同的方式解析SQL语句。

使用LLDB并启用了一些C日志代码，我观察到Postgres解析器为Captain Nemo查询生成了这个解析树：

顶部是代表整个SQL语句的节点，下面是代表SQL语句语法不同部分的子节点或分支：目标列表（列的列表），from子句（表的列表） ，where子句，排序顺序和限制计数。

如果您想了解有关Postgres如何解析SQL语句的更多信息，请遵循从exec_simple_query到另一个C函数pg_parse_query的控制流。

如您所见，Postgres源代码中有许多有用且详细的注释，它们不仅解释正在发生的事情，而且指出重要的设计决策。

一劳永逸的事

上面的解析树应该看起来很熟悉-与我们之前看到的ActiveRecord创建的抽象语法树（AST）几乎完全相同。 回想一下演示文稿的前半部分，当我们执行此Ruby查询时，ActiveRecord生成了Captain Nemo select语句：

当调用诸如where和first的方法时，我们看到ActiveRecord在内部创建了一个AST。 稍后（ 请参阅第二篇文章 ），我们看到Arel gem使用基于访问者模式的算法将AST转换为示例select语句。

考虑到这一点，具有讽刺意味的是，Postgres对您SQL语句所做的第一件事就是将其从字符串转换回AST。 Postgres的解析过程会颠倒ActiveRecord之前所做的一切； Arel宝石所做的所有努力都是徒劳的！ 完全创建SQL字符串的唯一原因是通过网络连接与Postgres进行通信。 一旦Postgres获得了字符串，它就会将其转换回AST，这是一种表示查询的更加便捷和有用的方式。

了解这一点，您可能会问：有没有更好的方法？ 有没有某种方法可以在不编写SQL语句的情况下从概念上指定我们要向Postgres提供的数据？ 是否无需学习复杂SQL语言或支付使用ActiveRecord和Arel的性能开销？ 花费如此长的时间从AST生成SQL字符串，只是再次将其转换回AST，似乎是在浪费时间。 也许我们应该改用NoSQL数据库解决方案？

当然，AST Postgres的使用与ActiveRecord使用的AST有很大不同。 ActiveRecord的AST由Ruby对象组成，而Postgres的AST由一系列C内存结构组成。 相同的想法，但实现方式却大不相同。

分析和重写

一旦Postgres生成了一个解析树，它就会使用一组不同的节点将其转换为另一个树结构。 这就是所谓的查询树 。 返回exec_simple_query C函数，您可以看到它接下来调用了另一个C函数pg_analyze_and_rewrite 。

分析和重写过程让我有些费神，并掩盖了许多重要的细节，应用了一系列复杂的算法和启发式方法来尝试优化和简化SQL语句。 如果您执行了带有子选择以及多个内部和外部联接的复杂select语句，那么优化的空间很大。 Postgres很有可能减少子选择子句或联接的数量，以产生运行速度更快的更简单的查询。

对于我们的简单select语句，这是pg_analyze_and_rewrite生成的查询树：

我不假装理解pg_analyze_and_rewrite背后的详细算法。 我只是观察到在我们的示例中查询树在很大程度上类似于解析树。 这意味着select语句是如此简单，Postgres无法进一步简化它。

计划

开始执行查询之前，Postgres采取的最后一步是创建一个计划。 这涉及到生成第三个节点树，这些树形成了Postgres可以遵循的指令列表。 这是我们的select语句的计划树：

想象一下，计划树中的每个节点都是某种机器或工人。 计划树类似于工厂中的数据管道或传送带。 在我的简单示例中，树中只有一个分支。 计划树中的每个节点都从下面的节点获取一些输出数据，对其进行处理，然后将结果作为输入返回到上面的节点。 在下一节中，我们将按照Postgres执行计划。

启动查询计划过程的C函数称为pg_plan_queries 。

注意每个计划节点中的startup_cost和total_cost值。 Postgres使用这些值来估算完成计划所需的时间。 您无需使用C调试器即可查看查询的执行计划。 只需在查询中添加SQL EXPLAIN命令即可，如下所示：

尽管pg_plan_queries中有复杂的计划算法， 但这是一种了解Postgres在内部对您的查询进行内部操作以及其运行速度缓慢或效率低下的有效方法 。

执行极限计划节点

到目前为止，Postgres已经解析了您SQL语句，并将其转换回AST。 然后，它可能以更简单的方式优化并重写了您的查询。 第三，Postgres编写了一个计划，将遵循该计划来查找并返回您要查找的数据。 最后，是时候让Postgres真正执行查询了。 它是如何做到的？ 当然，它遵循了计划！

让我们从计划树的顶部开始然后向下移动。 跳过根节点，Postgres用于Captain Nemo查询的第一个工作线程称为Limit。 您可能会猜到，Limit节点实现了LIMIT SQL命令，该命令将结果集限制为指定的记录数。 同一计划节点还实现OFFSET命令，该命令将在指定行启动结果集窗口。

Postgres第一次调用Limit节点时，它将计算极限值和偏移值，因为它们可能被设置为某些动态计算的结果。 在我们的示例中，偏移量为0，限制为1。

接下来，“限制计划”节点重复调用子计划，在我们的示例中为“排序”，一直计数直到达到偏移值：

在我们的示例中，偏移量值为零，因此此循环将加载第一个数据值并停止迭代。 然后Postgres返回从子计划加载到调用或上层计划的最后一个数据值。 对我们来说，这将是子计划中的第一个价值。

最后，当Postgres继续调用Limit节点时，它将一次从子计划中传递数据值：

在我们的示例中，因为限制值为1，所以限制将立即返回NULL，这表明上层计划没有更多数据可用。

Postgres使用代码在名为nodeLimit.c的文件中实现Limit节点

您可以看到Postgres源代码使用诸如元组 （一组值，每一列一个）和子计划之类的词。 此示例中的子计划是“排序”节点，它出现在计划中的“限制”下方。

执行排序计划节点

限制过滤器的数据值从何而来？ 从出现在计划树中“限制”下的“排序计划”节点中。 Sort从其子计划加载数据值，并将其返回到其调用计划Limit。 这是Limit节点首次调用Sort时要执行的操作，以获取第一个数据值：

您会看到排序功能与限制功能大不相同。 在返回任何内容之前，它将立即将子计划中的所有可用数据加载到缓冲区中。 然后，它使用Quicksort算法对缓冲区进行排序 ，最后返回第一个排序的值。

对于第二次和后续调用，Sort只是从排序后的缓冲区返回其他值，而无需再次调用子计划：

排序计划节点由称为ExecSort的C函数实现：

执行SeqScan计划节点

ExecSort在哪里获得其值？ 从其子计划中，或者出现在计划树底部的SeqScan节点。 SeqScan代表顺序扫描 ，它意味着浏览表中的值，并返回与给定过滤器匹配的值。 要了解扫描如何与我们的过滤器一起使用，让我们逐步浏览一个假名填充虚假名称的用户表，寻找Nemo船长。

Postgres从表中的第一条记录开始（在Postgres源代码中称为关系 ），并从计划树执行布尔表达式。 简单来说，Postgres会问一个问题：“这是尼莫船长吗？” 由于劳里安·古德温（Laurianne Goodwin）不是尼莫船长（Nemo），因此Postgres退居下一记录。

不，坎迪斯也不是尼莫船长。 Postgres继续：

……最终找到了尼莫船长！

Postgres使用称为ExecSeqScan的C函数实现SeqScan节点。

我们在做什么错了？

现在我们完成了！ 我们一直遵循简单的select语句，贯穿Postgres的全部内容，并看到了如何对其进行解析，重写，计划和最终执行。 在执行了数千行C代码之后，Postgres找到了我们正在寻找的数据！ 现在，Postgres要做的就是将Captain Nemo字符串返回给我们的Rails应用程序，ActiveRecord可以创建

一个Ruby对象。 我们终于可以回到应用程序的表面了。 但是Postgres不会停止！ 尽管我们已经找到了Nemo上尉，但Postgres不仅会简单地返回，而且会继续扫描用户表：

这里发生了什么？ 为什么Postgres浪费了时间，尽管已经找到了我们要查找的数据，但仍继续搜索？

答案位于“排序”节点中的计划树上方。 回想一下，为了对所有用户进行排序，ExecSort首先将所有值加载到缓冲区中，方法是反复调用子计划，直到没有剩余值为止。

从南极返回时，鹦鹉螺内部的空气供应开始耗尽。

这意味着ExecSeqScan将继续扫描到表的末尾 ，直到拥有所有匹配的用户为止。

如果我们的用户表中包含成千上万甚至上百万条记录（假设我们在Facebook或Twitter上工作），ExecSeqScan将不得不遍历每条用户记录并为每条记录执行字符串比较。

这显然是低效且缓慢的，并且随着添加越来越多的用户记录而变得越来越慢。

如果我们只有一个船长Nemo记录，则ExecSort将仅对单个匹配记录进行“排序”，而ExecLimit将通过该偏移量/限制过滤器传递该单个记录……但前提是ExecSeqScan遍历了所有名称。

下次

我们如何解决这个问题？ 如果我们对users表SQL查询花费越来越多的时间来执行该怎么办？ 答案很简单：我们创建一个索引。

在本系列的下一篇也是最后一篇文章中，我们将学习如何创建Postgres索引以及如何避免使用ExecSeqScan。 更重要的是，我将向您展示Postgres索引的样子：它如何工作以及为什么它会加快这种查询的速度。

通过@pat_shaughnessy“通过Postgres内部进行选择语句”

点击鸣叫

翻译自: https://www.javacodegeeks.com/2015/06/following-a-select-statement-through-postgres-internals.html

postgres 基本语句