网络协议
PostgreSQL 和 MySQL 协议均属于数据库网络协议。网络协议是通信双方必须遵守的一组约定，包括语法、语义和时序三个要素。其中语法规定数据和控制信息的组织结构；语义定义数据传递的信息内容，以及控制信息的执行和响应方式；时序规范数据和控制信息的发送顺序。PostgreSQL 和 MySQL 协议均处于 OSI 模型的最上层，属于应用层协议。
网络协议一般都包含消息、握手和查询，其中查询包括简单查询和扩展（预处理）查询，以下分别从这几个方面简单介绍 PostgreSQL 和 MySQL 协议。
PostgreSQL 协议简介
消息
PostgreSQL 协议中，客户端和服务器间的所有通信都是以消息为载体的。消息的第一个字节表示消息类型，其后 4 个字节用于表示除消息类型外消息剩余部分（包括长度自身）的长度（字节数），其余部分为消息内容，由具体的消息类型决定。由于历史原因，客户端发送的最初消息（启动消息）不包含消息类型字节。如下，图 1 和图 2 分别示意了启动消息和简单查询报文：
图 1 启动消息报文（客户端发送）
图 2 简单查询报文（客户端发送）
如希望了解更多的报文格式，请参阅 PostgreSQL 官方网站：52.7. 消息格式 (postgres.cn)
特定数据类型的数据可以用几种不同的格式中的任意一种来传递。 从 PostgreSQL 7.4 开始，只支持 “文本”（格式代码：0）和 “二进制”（格式代码：1）两种格式，但是协议为未来的扩展提供了的手段，所有其它的格式代码都保留给将来定义。客户端可以为每个传输的参数值和查询结果的每个列指定一个格式代码。
文本形式的数值是特定数据类型的输入 / 输出转换函数生成或接受的任何字符串。在传输形式上，字符串没有末尾空字符（也不允许在中间嵌入空字符），原因是某些报文（参见图 1 启动消息报文）已经使用了’ ’字符作为各个属性的分隔符；如果客户端要想把收到的值当作 C 字符串处理，那么必须自己加上一个。
整数的二进制形式采用网络字节序（BE，高位在前）。对于其它数据类型，需要参阅官方技术文档（52.6. 消息数据类型 (postgres.cn)）或者源代码获取其二进制形式的信息。复杂数据类型的二进制形式可能在不同服务器版本之间变化；文本格式通常是最具有移植性的选择。
握手
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 握手报文
查询
此处的查询指的是广义查询，包括我们通常所说的所有 DML、DCL 和 DDL 语句。PostgreSQL 的查询分为简单查询和扩展查询（相当于 MySQL 的预处理查询）。
简单查询
简单查询是由客户端端发送一条 Query 消息给服务器进行初始化的。这条消息包含一个用文本字符串表达的 SQL 命令（或者一些命令）。 服务器根据查询命令串的内容发送一条或者更多条响应消息给前端，并且最后是一条 ReadyForQuery 响应消息。ReadyForQuery 通知前端它可以安全地发送新命令了（否则前端必须能发现较早发出的命令失败而稍后发出的命令成功的情况）。
一个常见的简单查询流程如图 5 所示，简单查询过程中涉及的报文如图 6 所示，简单查询可以一次传递多条 SQL，如需了解简单查询过程中其他更复杂的情况和多条 SQL 的处理逻辑，请参阅官方技术文档：52.2. 消息流 (postgres.cn) 下 52.2.2 节：简单查询。
图 5 简单查询流程
图 6 简单查询涉及报文
扩展查询
在扩展查询协议中，SQL 命令的执行是分割成多个步骤的。步骤与步骤之间保存的状态是由两类的对象代表的：预备语句（prepared statements）和入口（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 所示：
图 7 扩展查询
图 8 扩展查询相关报文
MySQL 协议简介
消息
与 PostgreSQL 协议相同，MySQL 协议中客户端和服务器间的通信也都是以消息为载体的。不同的是，MySQL 统一使用小端序（LE，低位在前）作为消息数据的编码字节序，数据类型更为多样，报文结构也更加复杂。
MySQL 协议的基本数据类型分为整数类型和字符串类型。其中整数类型分为定长（协议中规定的固定长度）整数类型和长度可编码的整数类型。字符串类型分为定长（协议中规定的固定长度）字符串、以 Null（’00’字节）结尾的字符串、可变长度（长度由协议中其他部分规定）字符串、长度可编码（以长度可编码整数为前缀）的字符串以及包中剩余字符串（数据包结尾部分的字符串，长度可由包长度和当前位置索引计算）类型。
其中长度可编码字符串的编码规则如图 9 所示：
图 9 长度可编码整数的编码规则
MySQL 通用的消息报文格式如图 10 所示：
图 10 MySQL 通用消息报文格式
其中，报文头部由 3 字节定长整数类型的 payload_length（payload 长度）和 1 字节定长整数类型的 sequence_id 组成（报文唯一标识，用户客户端和服务器通信时区别报文），其余部分为可变长字符串类型的 payload，用于承载消息内容。一个简单的例子如图 11 所示：
图 11 MySQL 报文简单示例
如果有效 payload 大于或等于 224-1 字节（16Mb），则长度设置为 224-1 (ff ff ff)，并且将与其余有效 payload 一起发送附加数据包，直到数据包的有效 payload 小于 224-1 字节。发送 224-1（16 777 215）字节的数据时，第一段报文如图 12 所示：
图 12 发送大于 16Mb 字节报文的第一段
如希望了解更多的报文格式，请参阅 MySQL 官方网站：MySQL: Generic Response Packets、MySQL: Protocol::HandshakeV10、MySQL: Protocol::HandshakeResponse:、MySQL: COM_QUERY、MySQL: Text Resultset
握手
MySQL 握手流程相对于 PostgreSQL 而言更加简单，由于 MySQL 报文中含有 ID 和状态标识，握手后无需进行状态同步。
与 PostgreSQL 不同，客户端和服务器建立 TCP 连接后，由服务器首先发送握手报文：MySQL: Protocol::Handshake，较早版本的 MySQL 服务器会发送 MySQL: Protocol::HandshakeV9: 报文，3.21.0 版本后的 MySQL 服务器发送 MySQL: Protocol::HandshakeV10 报文。
下面以 MySQL: Protocol::HandshakeV10 报文为例进行简单讲解。如图 13 所示，报文 Payload 的首字节是长度为 1 的定长整数，内容固定为 10，表示握手报文的版本号；其后是一个以 NULL 结尾的字符串，用于表示人类可读的服务器版本信息；之后是一个长度为 4 的定长整数用来保存连接 id，用来区分不同的客户端连接；接下来是一个 8 字节定长字符串，用于保存认证插件（对于口令认证就是挑战随机数，后边会讲到）的前 8 字节；而后是一个 1 字节定长整数，内容固定为 0x00，用于表示前述认证插件前 8 字节的结束；然后是一个 2 字节定长整数，用于保存能力协商位域的较低 2 字节；其后是 1 字节定长整数用于表示服务器协议使用的字符集的低 8 位；之后用 2 字节定长整数表示服务器状态，接下来是一个 2 字节定长整数，用于保存能力协商位域的较高 2 字节；如果客户端和服务器都启用了客户端插件认证（CLIENT_PLUGIN_AUTH）则接下来的一个 1 字节定长整数是认证插件数据（auth_plugin_data）的长度，否则是一个 0x00 字节；而后是 10 字节定长字符串的保留填充位，内容均为 0；然后是一个可变长字符串，用于保存认证插件的其余字节，长度取 13 与认证插件长度减去 8 后的结果两者间的最大值；最后，如果客户端和服务器都启用了客户端插件认证（CLIENT_PLUGIN_AUTH）则追加一个以 NULL 结尾的字符串用于表示使用的认证方法的名称。有关认证方法的更多信息请参阅 MySQL 官网：MySQL: Authentication Methods。
图 13 HandshakeV10 报文
在收到服务器发送的握手报文后，客户端需要发送一个 MySQL: Protocol::HandshakeResponse: 响应报文，较早版本客户端回复 MySQL: Protocol::HandshakeResponse320: 报文，4.1 版本以后的客户端回复 MySQL: Protocol::HandshakeResponse41: 报文，报文各部分信息的含义参见官方文档，在此不再赘述。
服务器收到客户端握手响应报文后会进行身份验证和能力协商，如果身份验证和能力协商通过，则会发送 MySQL: OK_Packet 报文，否则会发送 MySQL: ERR_Packet，整个流程如图 14 所示。
图 14 MySQL 协议握手流程
比较有意思的是 MySQL 特有的口令认证机制，这里简单介绍下。数据库管理端使用本地 root 用户登录 MySQL 后，会为创建的远程数据库用户设置口令，服务器将口令使用两次 SHA1 算法加密后存入用户系统表（mysql.users 的 authentication_string）。在客户端使用创建的数据库用户远程登录时，建立 TCP 连接后数据库会生成一个随机的盐值，称为挑战随机数，并通过 MySQL: Protocol::Handshake 报文传递给客户端，客户端将用户输入的身份验证口令进行 2 次 SHA1 加密后与这个挑战随机数相加，然后再进行一次 SHA1 加密，最终与使用一次 SHA1 加密的口令进行异或（XOR）操作，将生成的哈希值（hash_val = SHA1（pwd） XOR SHA1（salt + SHA1（SHA1（pwd））））通过 MySQL: Protocol::HandshakeResponse: 报文发送给服务器，服务器将存储在本地的，使用两次 SHA1 加密的口令加上本次连接生成的挑战随机数之后进行 SHA1 加密（SHA1（salt + SHA1（SHA1（pwd）））），再与客户端发送的值进行异或操作，所得到的值正好是使用一次 SHA1 加密的口令（利用的原理是异或运算的基本性质 A = A XOR B XOR B），将其再进行一次 SHA1 加密后，与用户系统表中的值进行比较，如果相同即可判断本次登录输入的口令正确，否则认为口令输入错误，身份验证失败。
另一个有意思的机制是 MySQL 的能力协商机制，MySQL 在客户端和服务器分别使用一个位域来保存各自支持的能力，每一位为 1 表示支持该位对应的能力，为 0 则表示不支持，在握手阶段，服务器先通过 MySQL: Protocol::Handshake 报文向客户端发送自己支持的能力，客户端通过 MySQL: Protocol::HandshakeResponse: 报文返回自己支持的能力。双方均会保存对方的能力标识位域，在判断对方是否支持某种能力时，只需要将这个值和需要判断的能力标识位域进行逐位与运算，如果不为 0 则表示支持该种能力。这就是 MySQL 所谓的能力协商。
查询
MySQL 协议所指的查询也是广义查询，分为简单查询和预处理查询，其中预处理查询对应 PostgreSQL 的扩展查询。
简单查询
由于使用了有状态报文，MySQL 客户端和服务器每次的通信都不需要额外的同步报文，因此 MySQL 的简单查询流程相比 PostgreSQL 更加简单。查询时客户端使用 MySQL: COM_QUERY 报文发送要执行的 SQL 语句，服务器执行 SQL 语句后，使用 MySQL: COM_QUERY Response 报文返回执行结果，如图 15 所示。
图 15 MySQL 简单查询流程
MySQL: COM_QUERY 报文结构如图 16 所示，MySQL: COM_QUERY Response 报文不是某种特定的报文，而是一类报文的总称。当 SQL 语句执行成功时，服务器返回 MySQL: OK_Packet 报文，执行失败则返回 MySQL: ERR_Packet 报文，当客户端请求查询结果时，服务器还可以通过 MySQL: Text Resultset 报文返回查询的结果集，而 MySQL: LOCAL INFILE Request 则是在文件导入过程中服务端要求客户端指定预先存储在服务端需要导入数据库的数据文件名称。
图 16 MySQL: COM_QUERY 报文
预处理查询
与简单查询类似，由于使用了有状态报文，MySQL 的预处理查询流程相对 PostgreSQL 也在很大程度上进行了简化。如图 17 所示，MySQL 预处理的主干流程只包括预处理（prepare）和执行（execute）两个阶段，其余灰色线条表示的交互都是可选的，只在特定场景下才会出现。预处理阶段客户端将需要预处理的 SQL 语句通过 MySQL: COM_STMT_PREPARE 报文发送给服务器，服务器完成预处理后向客户端发送 MySQL: COM_STMT_PREPARE 报文用于返回预处理的操作结果，双方通过 statement_id 区分不同的预处理流程；而后客户端发送 MySQL: COM_STMT_EXECUTE 报文携带要执行的预处理语句所需的参数，服务器完成执行后返回 MySQL: COM_STMT_EXECUTE Response 报文。其中 MySQL: COM_STMT_EXECUTE Response 报文也是一类报文，当执行成功时返回 MySQL: OK_Packet 报文，执行失败时返回 MySQL: ERR_Packet 报文，如果预处理语句是查询操作，使用 MySQL: Binary Protocol Resultset 报文返回查询的结果集，如图 18 所示。
当客户端执行 SQL 的预处理参数值是 BLOB 或者 CLOB 等大字段类型时，发送 MySQL: COM_STMT_PREPARE 报文后和发送 MySQL: COM_STMT_EXECUTE 报文前会额外发送 MySQL: COM_STMT_SEND_LONG_DATA 报文用于传输大字段数据。如果客户端使用游标查询，则打开游标执行 MySQL: COM_STMT_EXECUTE 后会发送 MySQL: COM_STMT_FETCH 报文用于分批获取游标查询的结果集，服务器获取批次数据后则使用 MySQL: COM_STMT_FETCH Response 报文发送各批次数据。当客户端需要重设本次预处理的参数，但需要复用预处理语句时，则发送 MySQL: COM_STMT_RESET 报文请求服务器重置预处理语句，服务器重置成功时返回 MySQL: OK_Packet 报文，失败则返回 MySQL: ERR_Packet 报文。
图 17 MySQL 预处理查询流程
图 18 MySQL: Binary Protocol Resultset 报文
总结
以上仅从最简单的握手和查询场景介绍了 PostgreSQL 和 MySQL 两种数据库的通信协议，没有涉及更复杂的身份验证方式、命令协议和存储过程等，希望深入了解的小伙伴可以自行查询官网内容。
最后通过一组对比和相关分析来尝试理清 PostgreSQL 和 MySQL 协议两者之间的异同，以及造成两者差异的原因。
对比
图 19 表格主要从消息格式、握手和身份认证，以及预处理流程等方面对 PostgreSQL 和 MySQL 协议进行了简单对比。总体而言，MySQL 相对 PostgreSQL 拥有更丰富的数据类型和结构复杂的有状态报文，使得协议的交互过程变得更加简单。
分析
由于通信协议是一个服务器应用在设计之初就必须考虑的问题，因此分析 PostgreSQL 和 MySQL 协议之间的差别，主要应该从两者出现的时代背景入手。
PostgreSQL 诞生于 1986 年，当时的计算机硬件和网络设备都比较原始，程序设计时需要考虑内存和带宽的限制，因此不适合发送结构过于复杂的报文。同时受限于当时的科技、经济和社会水平，互联网尚未得到完全的开发，电子商务尚未兴起，对数据库的性能要求也相对较低，因此可以接受以较慢的速度多次交互。以上因素共同决定了 PostgreSQL 协议报文简单而交互较多的特点。
MySQL 虽然萌芽较早，但实际正式的 1.0 版本在 1996 年才正式发布，此时计算机硬件和网络设备都已经有了长足的进步，已经有足够的内存和带宽来设计更为复杂的网络协议报文，而当时科技、经济和社会的进一步发展也催生了互联网和电子商务的兴起和繁荣，对数据库的响应速度也提出了更高的要求，因此 MySQL 进一步挖掘硬件的潜力，通过更复杂的报文来换取更少的交互次数是情理之中的选择。
通过 MySQL 和 PostgreSQL 协议的对比分析，可以预见未来在网络速度和稳定性都有较大提升的前提下，未来的数据库网络协议的报文规划会更加合理，数据通信的规则也会更加完善。
作者：浪潮云溪数据库
