PostgreSQL和MySQL协议简介（上）

开源软件联盟PostgreSQL分会 2021-11-26

1617

文章转载自公众号：浪潮云溪数据库

网络协议

PostgreSQL和MySQL协议均属于数据库网络协议。网络协议是通信双方必须遵守的一组约定，包括语法、语义和时序三个要素。其中语法规定数据和控制信息的组织结构；语义定义数据传递的信息内容，以及控制信息的执行和响应方式；时序规范数据和控制信息的发送顺序。PostgreSQL和MySQL协议均处于OSI模型的最上层，属于应用层协议。

网络协议一般都包含消息、握手和查询，其中查询包括简单查询和扩展（预处理）查询，以下分别从这几个方面简单介绍PostgreSQL和MySQL协议。

PostgreSQL协议简介

1、消息

PostgreSQL协议中，客户端和服务器间的所有通信都是以消息为载体的。消息的第一个字节表示消息类型，其后4个字节用于表示除消息类型外消息剩余部分（包括长度自身）的长度（字节数），其余部分为消息内容，由具体的消息类型决定。由于历史原因，客户端发送的最初消息（启动消息）不包含消息类型字节。如下，图1和图2分别示意了启动消息和简单查询报文：

图1 启动消息报文（客户端发送）

图2 简单查询报文（客户端发送）

如希望了解更多的报文格式，请参阅PostgreSQL官方网站：52.7. 消息格式 (postgres.cn)

特定数据类型的数据可以用几种不同的格式中的任意一种来传递。从PostgreSQL 7.4开始，只支持“文本”（格式代码：0）和“二进制”（格式代码：1）两种格式，但是协议为未来的扩展提供了的手段，所有其它的格式代码都保留给将来定义。客户端可以为每个传输的参数值和查询结果的每个列指定一个格式代码。

文本形式的数值是特定数据类型的输入/输出转换函数生成或接受的任何字符串。在传输形式上，字符串没有末尾空字符（也不允许在中间嵌入空字符），原因是某些报文（参见图1启动消息报文）已经使用了’\0’字符作为各个属性的分隔符；如果客户端要想把收到的值当作C字符串处理，那么必须自己加上一个。

整数的二进制形式采用网络字节序（BE，高位在前）。对于其它数据类型，需要参阅官方技术文档（52.6. 消息数据类型 (postgres.cn)）或者源代码获取其二进制形式的信息。复杂数据类型的二进制形式可能在不同服务器版本之间变化；文本格式通常是最具有移植性的选择。

2、握手

PostgreSQL的握手由客户端发起。要开启一个会话，客户端先请求与服务器建立TCP连接，然后发送一个启动消息(StartupMessage，参见图1)，这个消息包括用户名和用户希望连接的数据库名，以及要使用的特定协议版本，还可以指定额外的运行时参数设置。服务器根据这些信息和服务器配置文件（如pg_hba.conf）的内容来判断是否可以接受连接以及需要什么样的额外认证信息，而后发送合适的身份验证请求（如：Authentication）信息，前端必须发送合适的响应信息（比如携带数据库登录口令的PasswordMessage报文），如果口令通过验证，服务器向客户端发送AuthenticationOK报文，否则发送ErrorResponse来拒绝本次连接。

完成身份验证之后，前端需要等待服务器发送进一步的消息，此时服务器会启动一个异步进程（线程或协程）为当前客户端提供数据库服务（在此期间，服务器会尝试用客户端在启动消息中指定的额外运行时参数配置数据库，如果成功这些值将成为当前会话的缺省配置，配置失败则发送ErrorResponse报文并退出），正常情况下服务器之后会陆续发送ParameterStatus、BackendKeyData以及ReadyForQuery报文用于通知客户端可以接收查询指令。

握手的简单流程如图3所示，期间比较典型的报文如图4所示（其中ReadyForQuery报文的第6个字节表示一个事务指示器，I表示不在事务中，T表示在事务中，E表示在失败的事务中），如希望了解身份验证和服务器响应的更多情况，请参阅PostgreSQL官方网站：52.2. 消息流 (postgres.cn)下的52.2.1节：启动。

图3 握手流程

图4 握手报文

3、查询

此处的查询指的是广义查询，包括我们通常所说的所有DML、DCL和DDL语句。

PostgreSQL的查询分为简单查询和扩展查询（相当于MySQL的预处理查询）。

简单查询

简单查询是由客户端端发送一条Query消息给服务器进行初始化的。这条消息包含一个用文本字符串表达的SQL 命令（或者一些命令）。服务器根据查询命令串的内容发送一条或者更多条响应消息给前端，并且最后是一条ReadyForQuery响应消息。ReadyForQuery通知前端它可以安全地发送新命令了（否则前端必须能发现较早发出的命令失败而稍后发出的命令成功的情况）。

一个常见的简单查询流程如图5所示，简单查询过程中涉及的报文如图6所示，简单查询可以一次传递多条SQL，如需了解简单查询过程中其他更复杂的情况和多条SQL的处理逻辑，请参阅官方技术文档：52.2. 消息流 (postgres.cn)下52.2.2节：简单查询。

图5 简单查询流程

图6 简单查询涉及报文

扩展查询

在扩展查询协议中，SQL命令的执行是分割成多个步骤的。步骤与步骤之间保存的状态是由两类的对象代表的：预备语句（preparedstatements）和入口（portals）。一个预备语句代表一个文本查询字符串的经过分析、语意解析以及规划之后的结果。一个预备语句不代表它已经可以被执行，因为它可能还缺乏参数的值。一个入口代表一个已经可以执行的或者已经被部分执行过的语句，所有缺失的参数值都已经填充到位了。

扩展查询可以复用预处理结果来提高查询效率，此外由于参数是单独传递的，可以防范SQL注入。

在扩展查询协议中，客户端首先发送一个Parse消息，它包含一个文本查询字符串，另外还有一些可选的有关参数占位符的数据类型的信息，以及一个最终的预备语句对象的名字。服务器响应是一个ParseComplete或者ErrorResponse消息。参数的数据类型可以用OID来指定；如果没有给出，那么分析器将试图用应付无类型文字符串常量的方法来推导其数据类型。

如果命名的预处理语句对象创建成功，除非被显示删除，将存在到当前会话结束。如果是未命名的预处理语句对象，则只会存在到下一个未命名预处理语句的Parse或简单查询发送前。一旦服务器成功创建预处理语句对象，客户端就可以使用Bind消息将其转入执行状态。Bind消息给出源预备语句的名字（空字符串表示未命名预备语句）、目标入口的名字（空字符串表示未命名的入口）及用于那些在预备语句中出现的所有参数占位符的值。提供的参数集必须匹配那些预备语句所需要的参数（如果在Parse消息里声明任何void参数，那么在Bind消息里给它们传递NULL值）。Bind还指定被查询返回的数据的格式；格式可以在总体上声明，也可以对每个列进行声明。响应是BindComplete或ErrorResponse。

如果命名的入口对象创建成功，除非被显示删除，将存在到当前事务结束。客户端可以使用Execute消息执行创建成功的入口对象，Execute消息指定入口的名字（空字符串表示未命名入口）和一个最大的结果行计数（零表示“取出所有行”）。结果行计数只对包含返回行集的入口有意义；在其它情况下，该命令总是被执行到结束，而行计数会被忽略。Execute消息的可能响应和那些通过简单查询协议发出的查询一样，只不过执行不会导致后端发出ReadyForQuery或者RowDescription。

每个扩展查询消息序列完成后，前端都应该发出一条Sync消息。这个无参数的消息导致后端关闭当前事务——如果当前事务不是在一个BEGIN/COMMIT事务块中（“关闭”的意思就是在没有错误的情况下提交，或者是有错误的情况下回滚）。然后响应一条ReadyForQuery消息。Sync的目的是提供一个错误恢复的重新同步的点。如果在处理任何扩展查询消息的时候侦测到任何错误，那么后端会发出ErrorResponse，然后读取并抛弃消息直到一个Sync到来，然后发出ReadyForQuery并且返回到正常的消息处理中（但是要注意如果正在处理Sync的时候发生了错误，那么不会忽略任何东西— 这样就保证了为每个Sync发出一个并且只是一个ReadyForQuery）。

除了以上这些最基本的必须操作外，扩展查询协议还可以执行如下可选操作：

Describe消息（入口变体）指定一个现有的入口的名字（或者一个空字符串表示未命名入口）。响应可以是一个RowDescription消息，它描述了执行该入口将要返回的行；如果入口并不包含会返回行的查询则是一个NoData消息；如果入口不存在则是一个ErrorResponse。

Describe消息（语句变体）指定一个现有的预处理语句的名字（或者一个空字符串表示未命名预处理语句）。响应是一个描述该语句需要的参数的ParameterDescription消息，后面跟着一个描述该语句最终执行后返回的行的RowDescription消息（或者是 NoData 消息，如果该语句不返回行）。如果没有这样的预处理语句，则返回ErrorResponse。如果客户端发送Describe时还没有发出Bind，所以后端还不知道用于返回列的格式；在这种情况下，RowDescription消息里面的格式代码域将是零。

Close消息用于关闭一个现有的预处理语句或者入口，并且释放资源。对一个不存在的语句或者入口发出Close不是一个错误。响应通常是CloseComplete，但如果在释放资源的时候遇到了一些困难也可以是ErrorResponse。关闭一个预处理语句会隐含地关闭任何从该语句构造出来的打开的入口。

Flush消息不产生任何特定的输出，但会强制后端发送任何还在它的输出缓冲区中待处理的数据。如果客户端希望在发出更多的命令之前检查该命令的结果则Flush必须在除Sync外的任何扩展查询命令后面发出。如果没有Flush，后端返回的消息将组合成尽可能少的数据包，以减少网络负荷。

扩展查询简单流程如图7所示，过程中涉及的报文如图8所示：