《PostgreSQL技术内幕——原理探索》第四章 外部数据包装器与并行查询

0    148    2

Tags:

👉 本文共约9250个字,系统预计阅读时间或需35分钟。

本章将介绍两种相当实用,而且很有趣的特性:外部数据包装器(Foreign Data Wrapper FDW)并行查询(Parallel Query)

4.1 外部数据包装器(FDW)

2003年,SQL标准中添加了一个访问远程数据的规范,称为SQL外部数据管理(SQL/MED)。PostgreSQL在9.1版本开发出了FDW,实现了一部分SQL/MED中的特性。

在SQL/MED中,远程服务器上的表被称为外部表(Foreign Table)。 PostgreSQL的外部数据包装器(FDW) 使用与本地表类似的方式,通过SQL/MED来管理外部表。

图4.1 FDW的基本概念

Fig. 4.1. Basic concept of FDW.

安装完必要的扩展并配置妥当后,就可以访问远程服务器上的外部表了。 例如假设有两个远程服务器分别名为postgresqlmysql,它们上面分别有两张表:foreign_pg_tblforeign_my_tbl。 在本例中,可以在本地服务器上执行SELECT查询以访问外部表,如下所示。

此外还可以在本地连接来自不同服务器中的外部表。

Postgres wiki中列出了很多现有的FDW扩展。但只有postgres_fdwfile_fdw 是由官方PostgreSQL全球开发组维护的。postgres_fdw可用于访问远程PostgreSQL服务器。

以下部分将详细介绍PostgreSQL的FDW。 4.1.1节为概述,4.1.2节介绍了postgres_fdw扩展的工作方式。

Citus

Citus是由citusdata.com开发的开源PostgreSQL扩展,它能创建用于并行化查询的分布式PostgreSQL服务器集群。citus算是PostgreSQL生态中机制上最为复杂,且商业上最为成功的扩展之一,它也是一种FDW。

4.1.1 概述

使用FDW特性需要先安装相应的扩展,并执行一些设置命令,例如CREATE FOREIGN TABLECREATE SERVERCREATE USER MAPPING(细节请参阅官方文档)。

在配置妥当之后,查询处理期间,执行器将会调用扩展中定义的相应函数来访问外部表。

图4.2 FDW是如何执行的

Fig. 4.2. How FDWs perform.

  1. 分析器为输入的SQL创建一颗查询树。
  2. 计划器(或执行器)连接到远程服务器。
  3. 如果启用了use_remote_estimate选项(默认关闭),则计划器将执行EXPLAIN命令以估计每条计划路径的代价。
  4. 计划器按照计划树创建出纯文本SQL语句,在内部称该过程为逆解析(deparesing)
  5. 执行器将纯文本SQL语句发送到远程服务器并接收结果。

如有必要,执行器会进一步处理接收到的结果。 例如执行多表查询时,执行器会将收到的数据与其他表进行连接。

以下各节介绍了每一步中的具体细节。

4.1.1.1 创建一颗查询树

分析器会根据输入的SQL创建一颗查询树,并使用外部表的定义。当执行命令CREATE FOREIGN TABLEIMPORT FOREIGN SCHEMA时,外部表的定义会被存储至系统目录pg_catalog.pg_classpg_catalog.pg_foreign_table中。

4.1.1.2 连接至远程服务器

计划器(或执行器)会使用特定的库连接至远程数据库服务器。 例如要连接至远程PostgreSQL服务器时,postgres_fdw会使用libpq。 而连接到mysql服务器时,由EnterpriseDB开发的mysql_fdw使用libmysqlclient

当执行CREATE USER MAPPINGCREATE SERVER命令时,诸如用户名,服务器IP地址和端口号等连接参数会被存储至系统目录pg_catalog.pg_user_mappingpg_catalog.pg_foreign_server中。

4.1.1.3 使用EXPLAIN命令创建计划树(可选)

PostgreSQL的FDW机制支持一种特性:获取外部表上的统计信息,用于估计查询代价。一些FDW扩展使用了该特性,例如postgres_fdwmysql_fdwtds_fdwjdbc2_fdw

如果使用ALTER SERVER命令将use_remote_estimate选项设置为on,则计划器会向远程服务器发起查询,执行EXPLAIN命令获取执行计划的代价。否则在默认情况下,会使用默认内置常量值作为代价。

尽管一些扩展也会执行EXPLAIN命令,但目前只有postgres_fdw才能忠于EXPLAIN命令的真正意图,因为PostgreSQL的EXPLAIN命令会同时返回启动代价和总代价。而其他DBMS的FDW扩展一般无法使用EXPLAIN命令的结果进行规划。 例如MySQL的EXPLAIN命令仅仅返回估计的行数, 但如第3章所述,PostgreSQL的计划器需要更多的信息来估算代价。

4.1.1.4 逆解析

在生成执行计划树的过程中,计划器会为执行计划树上外部表的扫描路径创建相应的纯文本SQL语句。 例如图4.3展示了下列SELECT语句对应的计划树。

图4.3展示了一个存储着纯文本形式SELECT语句的ForeignScan节点,PlannedStmt是执行计划树对应的数据结构,包含指向ForeignScan节点的链接。 这里,postgres_fdw从查询树中重新创建出SELECT纯文本语句,该过程在PostgreSQL中被称为逆解析(deparsing)

图4.3 扫描外部表的计划树样例

Fig. 4.3. Example of the plan tree that scans a foreign table.

使用mysql_fdw时,则会从查询树中重新创建MySQL相应的SELECT语句。 使用redis_fdwrw_redis_fdw会创建一条Redis中的SELECT命令

4.1.1.5 发送SQL命令并接收结果

在进行逆解析之后,执行器将逆解析得到的SQL语句发送到远程服务器并接收结果。

扩展的开发者决定了将SQL语句发送至远程服务器的具体方法。 例如mysql_fdw在发送多条SQL语句时不使用事务。 在mysql_fdw中执行SELECT查询的典型SQL语句序列如下所示(图4.4)。

  • (5-1)将SQL_MODE设置为'ANSI_QUOTES'
  • (5-2)将SELECT语句发送到远程服务器。
  • (5-3)从远程服务器接收结果。这里mysql_fdw会将结果转换为PostgreSQL可读的格式。所有FDW扩展都实现了将结果转换为PostgreSQL可读数据的功能。

图4.4 mysql_fdw执行一个典型SELECT查询时的SQL语句序列

Fig. 4.4. Typical sequence of SQL statements to execute a SELECT query in mysql_fdw

下面是远程服务器的日志,列出了实际接收到的语句。

postgres_fdw中的SQL命令顺序要更为复杂。在postgres_fdw中执行一个典型的SELECT查询,实际的语句序列如图4.5所示。

  • (5-1)启动远程事务。远程事务的默认隔离级别是REPEATABLE READ;但如果本地事务的隔离级别设置为SERIALIZABLE,则远程事务的隔离级别也会设置为SERIALIZABLE
  • (5-2)-(5-4)声明一个游标,SQL语句基本上以游标的方式来执行。
  • (5-5)执行FETCH命令获取结果。默认情况下FETCH命令一次获取100行。
  • (5-6)从远程服务器接收结果。
  • (5-7)关闭游标。
  • (5-8)提交远程事务。

图4.5 postgres_fdw执行一个典型SELECT查询时的SQL语句序列

Fig. 4.5. Typical sequence of SQL statements to execute a SELECT query in postgres_fdw.

这里是远程服务器的实际日志。

postgres_fdw中远程事务的默认隔离级别

远程事务的默认隔离级别为REPEATABLE READ,官方文档给出了原因和说明:

当本地事务使用SERIALIZABLE隔离级别时,远程事务也会使用SERIALIZABLE隔离级别,否则使用REPEATABLE READ隔离级别。 这样做可以确保在远程服务器上执行多次扫表时,每次的结果之间都能保持一致。因此,即使其他活动在远程服务器上进行了并发更新,单个事务中的连续查询也将看到远程服务器上的一致性快照。

4.1.2 postgres_fdw的工作原理

postgres_fdw扩展是一个由PostgreSQL全球开发组官方维护的特殊模块,其源码包含在PostgreSQL源码树中。

postgres_fdw正处于不断改善的过程中。 表4.1列出了官方文档中与postgres_fdw有关的发行说明。

表4.1 与postgres_fdw有关的发布说明(摘自官方文档)

版本描述
9.3postgres_fdw模块正式发布
9.6在远程服务器上执行排序 在远程服务器上执行连接 如果可行,在远程服务器上执行UPDATEDELETE 允许在服务器与表的选项中设置批量拉取结果集的大小
10如果可行, 将聚合函数下推至远程服务器
前一节描述了postgres_fdw如何处理单表查询,接下来的小节将介绍postgres_fdw如何处理多表查询,排序操作与聚合函数。

本小节重点介绍SELECT语句;但postgres_fdw还可以处理其他DML(INSERTUPDATEDELETE)语句。

PostgreSQL的FDW不会检测死锁

postgres_fdw与FDW功能并不支持分布式锁管理器与分布式死锁检测功能, 因此很容易产生死锁。 例如某客户端A更新了一个本地表tbl_local与一个外部表tbl_remote,而另一个客户端B以相反的顺序更新tbl_remotetbl_local,则这两个事务陷入死锁。但PostgreSQL无法检测到这种情况, 因而无法提交这些事务。

4.1.2.1 多表查询

当执行多表查询时,postgres_fdw使用单表SELECT语句依次拉取每个外部表,并在本地服务器上执行连接操作。

在9.5或更早版本中,即使所有外部表都存储在同一个远程服务器中,postgres_fdw也会单独拉取每个表再连接。

在9.6或更高版本中,postgres_fdw已经有所改进,当外部表位于同一服务器上且use_remote_estimate选项打开时,可以在远程服务器上执行远程连接操作。

执行细节如下所述。

9.5及更早版本:

我们研究一下PostgreSQL如何处理以下查询:两个外部表的连接:tbl_atbl_b

EXPLAIN的执行结果如下

结果显示,执行器选择了归并连接,并按以下步骤处理:

  • 第8行:执行器使用外部表扫描拉取表tbl_a
  • 第6行:执行器在本地服务器上对拉取的tbl_a行进行排序。
  • 第11行:执行器使用外表扫描拉取表tbl_b
  • 第9行:执行器在本地服务器上对拉取的tbl_b行进行排序。
  • 第4行:执行器在本地服务器上执行归并连接操作。

下面描述执行器如何拉取行集(图4.6)。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复