02.PostgreSQL 查询处理期间发生了什么？

PostgreSQL 查询处理期间发生了什么？

文中主要内容引用自PostgreSQL指南：内幕探索

查询处理是PostgreSQL中最为复杂的子系统。如PostgreSQL官方文档所述，PostgreSQL支持SQL2011标准中的大多数特性，查询处理子系统能够高效地处理这些SQL。

一、PostgresSQL 执行流程是怎样的？

先来一个上帝视角图，下面就是 PostgreSQL 查询处理的流程，也从图中可以看到 PostgreSQL 内部架构里的各个功能模块。

PostgreSQL 的查询流程主要可以分以下几个模块，也有很多文章将解析器与分析器放在一起，这里参考PostgreSQL指南：内幕探索：

1. 解析器（Parser）

   解析器根据SQL语句通过词法分析、语法分析生成一颗语法解析树（parse tree） 。

2. 分析器（Analyzer）

   分析器对语法解析树进行语义分析，生成一颗查询树（query tree）。

3. 重写器（Rewriter）

   重写器按照规则系统中存在的规则，对查询树进行改写。

4. 计划器（Planner）

   计划器基于查询树，生成一颗执行效率最高的计划树（plan tree）。

5. 执行器（Executor）

   执行器按照计划树中的顺序访问表和索引，执行相应查询。

PostgreSQL的查询处理在官方文档中有详细的描述

二、解析器（Parser）

与mysql的解析器逻辑类似，很多文章将解析器与分析器放在一起；

解析器基于SQL语句的文本，进行词法分析和语法分析，生成一颗后续子系统可以理解的语法解析树。下面是一个具体的例子。

考虑以下查询：

# SELECT id, data FROM tbl_a WHERE id < 300 ORDER BY data;

其语法解析树如下：

SELECT查询中的元素和语法解析树中的元素有着对应关系。比如，(1)是目标列表中的一个元素，与目标表的'id'列相对应，(4)是一个WHERE子句，诸如此类。

当解析器生成语法分析树时只会检查语法，只有当查询中出现语法错误时才会返回错误。解析器并不会检查输入查询的语义，举个例子，如果查询中包含一个不存在的表名，解析器并不会报错，语义检查由分析器负责。

二、分析器（Analyzer）

分析器对解析器产出的语法解析树（parse tree）进行语义分析，并产出一颗查询树（query tree）。

查询树如下所示：

简要介绍一下上图中的查询树：

• targetlist 是查询结果中**列（Column）**的列表。在本例中该列表包含两列：id 和data。如果在输入的查询树中使用了*（星号），那么分析器会将其显式替换为所有具体的列。
• 范围表rtable是该查询所用到关系的列表。本例中该变量包含了表tbl_a的信息，如该表的表名与oid。
• 连接树jointree存储着FROM和WHERE子句的相关信息。
• 排序子句sortClause是SortGroupClause结构体的列表。

查询树的细节可查看官方文档。

三、重写器（Rewriter）

类似于mysql的预处理阶段

PostgreSQL的规则系统正是基于重写器实现的；当需要时，重写器会根据存储在pg_rules中的规则对查询树进行转换。

视图

在PostgreSQL中，视图是基于规则系统实现的。当使用CREATE VIEW命令定义一个视图时，PostgreSQL就会创建相应的规则，并存储到系统目录中。

假设下面的视图已经被定义，而pg_rule中也存储了相应的规则。

# CREATE VIEW employees_list 
#   AS SELECT e.id, e.name, d.name AS department 
#      FROM employees AS e, departments AS d WHERE e.department_id = d.id;

当执行一个包含该视图的查询，解析器会创建一颗如下图所示的语法解析树。

# SELECT * FROM employees_list;

在该阶段，重写器会基于pg_rules中存储的视图规则将rangetable节点重写为一颗查询子树，与子查询相对应。

四、计划（优化）器（Planner）

类似于mysql的优化阶段

计划器主要负责将 SQL 查询语句的执行方案确定下来。

计划器从重写器获取一颗查询树（query tree），会根据表连接顺序和索引等信息去计算不同路径的可能代价值，最后选出最优者。基于查询树生成一颗能被执行器高效执行的（查询）计划树（plan tree）。

在PostgreSQL中，计划器是完全基于代价估计（cost–based）的；它不支持基于规则的优化与提示（hint）。

一个简单的计划树以及其与EXPLAIN命令的关系如下图所示

计划树由许多称为**计划节点（plan node）**的元素组成，每个计划节点都包含着执行器进行处理所必需的信息，在单表查询的场景中，执行器会按照从终端节点往根节点的顺序依次处理这些节点。

比如图中的计划树就是一个列表，包含一个排序节点和一个顺序扫描节点；因而执行器会首先对表tbl_a执行顺序扫描，并对获取的结果进行排序。

五、执行器（Executor）

类似于mysql的执行阶段

执行器最后执行plan，遍历每个节点，以致完成。最后将查询结果返回给客户端。

执行器会通过缓冲区管理器来访问数据库集簇的表和索引。当处理一个查询时，执行器会使用预先分配的内存空间，比如temp_buffers和work_mem，必要时还会创建临时文件。

执行器，缓冲管理器，临时文件之间的关系

除此之外，当访问元组的时候，PostgreSQL还会使用并发控制机制来维护运行中事务的一致性和隔离性。

总结

可以看到， PostgreSQL 的查询流程主要分五个子系统：

1. 解析器（Parser）

   解析器根据SQL语句生成一颗语法解析树（parse tree） 。

2. 分析器（Analyzer）

   分析器对语法解析树进行语义分析，生成一颗查询树（query tree）。

3. 重写器（Rewriter）

   重写器按照规则系统中存在的规则，对查询树进行改写。

4. 计划器（Planner）

   计划器基于查询树，生成一颗执行效率最高的计划树（plan tree）。

5. 执行器（Executor）

   执行器按照计划树中的顺序访问表和索引，执行相应查询。

问题

PostgreSQL与Mysql的查询处理主要区别是什么？

1. 数据库类型：
   • MySQL是一种关系型数据库（Relational Database），而PostgreSQL是一种面向对象关系型数据库（Object-Relational Database）。这意味着PostgreSQL在关系型数据库的基础上，增加了对对象的支持，可以存储和操作更复杂的数据结构[1]。
2. 查询复杂性：
   • PostgreSQL对于查询的复杂性提供了更高级的支持。它支持更复杂的存储过程和存储函数，可以进行更灵活和高级的数据处理操作[1]。而MySQL对于存储过程和函数的支持相对较弱。
3. 索引类型：
   • MySQL主要使用二叉搜索树（B-Tree）索引，而PostgreSQL支持多种类型的索引，包括GIN和Hash等。这使得PostgreSQL在某些特定的查询场景下可以提供更高效的查询性能[1]。
4. 数据加密：
   • 在客户端和服务器之间的加密方面，MySQL使用传输层安全性（TLS）协议进行加密，而PostgreSQL支持SSL加密。SSL提供了更强的加密和安全性，可以更好地保护数据的完整性[1]。
5. 高级数据类型：
   • PostgreSQL提供了更多的高级数据类型支持，如hstore和自定义数据类型。这使得PostgreSQL在处理特定类型的数据时更加灵活和强大[1]。MySQL在这方面的支持相对较弱。
6. 并发控制：
   • PostgreSQL支持多版本并发控制（MVCC），这意味着在读写操作同时发生时，能够处理并发访问的能力。而MySQL不直接支持MVCC，对于高并发环境下的并发控制可能相对简单[1]。

需要注意的是，上述区别是一般性的概述，实际的差异可能更加复杂，并且取决于具体的使用情境和配置设置。选择适合自己需求的数据库取决于企业的目标和资源限制。一般来说，PostgreSQL是一个更为强大和高级的数据库管理系统，适用于需要在大型环境中快速执行复杂查询的组织。而MySQL则是一个更适合预算和空间有限的企业的理想解决方案

参考文献

​ 1.https://www.ibm.com/cloud/blog/postgresql–vs–mysql–whats-the-difference

​ 2.PostgreSQL指南：内幕探索

​ 3.PostgreSQL 9.4.4 中文手册

代码007普通

打赏 收藏 海报 链接