网络协议

　　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启动消息报文）已经使用了’\0’字符作为各个属性的分隔符；如果客户端要想把收到的值当作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协议的对比分析，可以预见未来在网络速度和稳定性都有较大提升的前提下，未来的数据库网络协议的报文规划会更加合理，数据通信的规则也会更加完善。

欢迎关注