数字中国·星火文集｜和协议解析与对比

和MySQL PgSQL协议。

分析和比较

神州数码集团

姜昆

在实现一个数据库系统，客户端，或者中间件的时候，需要注意数据库和客户端之间的通信，也就是数据库协议层的实现。现在业界广泛讨论的两个开源关系数据库是MySQL和PostgreSQL，包括NewSQL的一些实现，也会向上兼容MySQL或PostgreSQL来实现它们的协议层。但是，这两个数据库的协议实现是完全不同的，所以这就是为什么我们不能用他们的客户端连接到另一个数据库。

事实上，双方的协议层处理在双方的官方文件中都有详细说明。如果大家都是老手，可以直接在官网文档里啃生肉:

一种数据库系统

https://www.postgresql.org/docs/current/protocol.html

关系型数据库

https://dev . MySQL . com/doc/dev/MySQL-server/latest/PAGE _ protocol . html

那么初学者可以先看这篇文章，有一定的了解后，会更快的吸收公文。话不多说，言归正传。

客户端与服务器端的通信基于TCP协议，三方握手后正式建立连接。

图中显示了TCP连接后MySQL和PostgreSQL之间的通信流程。

在MySQL中，服务器会优先考虑另一个握手请求。这个过程也是三次握手。这是第一阶段，有两个目的。第一个目标是传递服务器即MySQL的一些参数信息，如服务器版本号、通信时的最大消息长度和代码等。第二个目的是认证用户，所以客户端需要输入用户密码等信息进行认证。当认证成功后，就可以进行正常的通信，即流行的客户端发送各种命令，服务器对不同的命令返回不同的结果响应，这就是所谓的第二阶段。

PostgreSQL的通信也分为两个阶段，第一阶段是启动和认证，第二阶段是正常通信。图中只显示了启动认证阶段，后面的正常通信和MySQL一样，不做过多解释。我们可以看到，在启动阶段，前端先发送启动包。与MySQL不同，启动包包含用户名、访问的数据库名、客户端名和其他信息。后端收到后会判断用户是否需要密码认证，如果需要，会发送Auth请求请求密码认证信息。这个地方PosgtreSQL有很多密码认证方法，后面会介绍。此时前端成功发送密码认证后，后端会开始发送各种参数状态，即一系列后端参数。比如server_version、server_encoding、client_encoding、session_authorization、DateStyle等等。在这个过程中，前端不需要做出任何响应，甚至可以直接忽略。整个过程持续到最后，后端向前端发送Ready For Query，这意味着第一阶段的结束。下面的前端可以执行普通的查询命令和其他操作。

我们都知道沟通的过程。让我们来看看消息是什么样的。如前所述，两个数据库在通信时会分为两个阶段，因此两个阶段中的消息是不同的。

我们来看看第一阶段。这两个数据库的消息是什么:

MySQL这里的初始化握手包是服务器发送的，包括一系列服务器参数，协议版本，数据库版本，线程ID，挑战随机数，服务器状态，字符编码等。，而PostgreSQL的启动包是客户端发送的，主要包括协议版本，用户名和数据库名等等。PostgreSQL的服务器参数将在稍后发送参数状态消息时逐个发送给客户端。这是两者在启动和初始化上最大的区别。

当两个数据库启动初始化后，客户端和服务器开始正常的命令交互阶段，称为第二阶段，在这个阶段两者的消息是相似的。

从上图可以直观的看出，两者的消息格式分别是消息长度、命令类型和具体的命令语句。另外MySQL会比PostgreSQL多一个序列号，然后直观的区别就是命令长度和命令类型在不同的位置。

在信息交流的过程中，我们需要注意以下三点:

1.MySQL消息的小字节序和PostgreSQL消息的大字节序。小端左边低，大端右边低。不知道的可以自己做。

2.在命令类型上，也就是cmd指令，MySQL是十六进制整数来判断是哪个命令，而PostgreSQL是通过一个char字符来判断命令类型。

3.在MySQL和PostgreSQL消息中，消息长度Length都是自带的，MySQL也包含了一个字节的序列号，所以两者的有效载荷长度都要按Length-4计算。

MySQL的命令类型:

PostgreSQL的命令类型:

更深入的了解，请参考官方文件中的详细介绍。

从服务器发送的命令类型，其实可以看出两个数据库的不同特点，以及两个数据库的一些独特功能。MySQL需要分成更详细的消息指令，很多常见的查询操作都是作为单独的消息指令提出来的，比如数据库创建、数据库切换等。，而PostgreSQL会更简洁。大部分命令都是直接简单的查询指令，而其他的则是几大功能，比如扩展查询。

至于后面几条消息的具体内容，我从两个数据库中提取了几条同类型的消息，但内容信息相差较大，进行了对比。

先说预处理查询，PostgreSQL中称之为扩展查询。预处理查询会在查询时输入一条语句进行预处理。该语句包含一些表示参数的标识符，然后参数将被填充并在以后执行。

Select * from的学生

申诉语句表示标识符，执行时传入具体的id值进行查询。

MySQL预处理过程:

1.准备:0x 16 COM _ STMT _准备预处理语句

消息:COM _ STMT _准备_确定

2.Execute: 0x17 COM_STMT_EXECUTE执行带参数的预处理语句。

消息:ok _ packet、err _ packet或二进制协议结果集

3.Close: 0x19 COM_STMT_CLOSE释放预处理语句。

PostgreSQL预处理过程:

1.分析:“P”预处理语句

回复:ParseComplete或ErrorResponse

2.bind:“B”将特定值绑定到预处理语句的参数，并指定返回数据的格式。

消息:BindComplete或ErrorResponse

3.Describe: 'D '描述预处理语句，主要是获取解析后的Param类型和查询计划返回的行信息。

消息回复:参数描述、行描述、无数据或错误响应

4.Execute: 'E '执行预处理语句。

消息回复:命令完成、emptyqueryresponse、错误响应或门户暂停。

5.关闭:“C”释放预处理语句

消息:关闭完成或错误响应

6.同步:“S”同步

回复:ReadyForQuery或ErrorResponse

两个进程中相同类型的指令是准备和执行。在准备阶段，两者是类似的，都是通过一个预处理语句Stmt。在MySQL中，会生成一个ID作为预处理语句的唯一Stmt ID，然后由这个StmtID指定具体的预处理语句，而在PostgreSQL中，直接用Stmt Name作为唯一表示。在执行阶段，两者会有很大的差距。MySQL需要在执行阶段获取具体的绑定值，包括其长度、类型、是否空等等，而PostgreSQL只需要传递一个语句名。参数绑定的工作将由PostgreSQL在bind阶段完成。我们可以简单理解为PostgreSQL将MySQL的执行消息分为绑定和执行两个阶段。

在这个扩展查询过程中，当PostgreSQL消息中确定了预处理语句后，在Bind阶段绑定参数值后，将执行计划，并生成一个直接可执行的对象，称为Portal。然后，在下面的消息中，指定执行过程中要执行的预处理语句时，不再指定语句名Stmt Name，而是指定Portal Name。包括一些其他的消息操作，你也可以选择使用门户名称，比如描述、执行和关闭等。

再来看看其他一些差距较大的同类型留言。刚才讨论的是它们是从客户端发送到服务器的。让我们看一下从服务器发送到客户端的数据写回消息。

当我们希望客户端写回一大段数据时，服务器会写回两种消息。第一个是行信息描述，即返回多少个字段，每个字段的含义、类型、格式等等。第二种消息是行数据。当数据返回多行数据时，每一行数据都将作为消息发送，返回多少行数据，就发送多少行数据消息。因此，在一个查询中，第一条消息需要发送一次，而第二条消息需要发送多次。

实际上，这两个数据消息没有什么不同，都返回一行数据。可能在格式上有些差异，但总体信息几乎是一样的。最大的区别是行信息描述的消息，在MySQL中称为ColumnDefinition，在PostgreSQL中称为RowDescription。该消息的主要内容是列字段信息。一个表通常有多行，所以这个消息是列信息的集合。那么我们主要关心的是栏目信息的差异。

类型字段描述结构{

名称[]字节//字段名称

TableOID uint32 //特定的表OID

TableAttributeNumber uint16 //特定表的列属性号，其他为0。

数据类型OID uint32 //数据类型OID

数据类型大小int16 //数据类型大小

类型修饰符int32 //类型修饰符

formant 16//字段的格式为0表示文本，1表示二进制。

}

类型列信息结构{

模式字符串//模式名称

表字符串//表名

OrgTable字符串//原始表名

名称字符串//字段名称

OrgName字符串//原始字段名称

ColumnLengthuint32 //列长度

设置charuint 16//代码

UINT 16//特定类型标志

十进制uint8 //十进制

类型uint8 //字段类型

默认长度uint 64//默认长度

默认值[]字节//默认值

}

上面给出了两个数据库的最后一列信息的结构。通过这两种结构中的属性，我们可以想出它们之间的区别。相同的部分是这个字段的名称、表、数据类型、数据大小、代码都返回。但是，我们可以注意到PostgreSQL中的一些字段是由OID描述的，比如表名和字段类型。因为在PostgreSQL的内部实现中，表和数据类型都会用唯一的oid来标记，而oid是PostgreSQL实现中非常重要的一点。MySQL中的字段会多一点，有些名称会附带原来的字段名和模式信息。我们都知道MySQL在实现上追求简单，但是在这个位置上有点复杂。

关于两者的消息格式，这里不需要画出详细的解释。可以去官网查链接:

关系型数据库

https://dev . MySQL . com/doc/internals/en/com-query-response . html # packet-protocol text::ResultsetRow

一种数据库系统

https://www . PostgreSQL . org/docs/13/protocol-message-formats . html

最后，看看错误响应消息之间的区别。如果数据库执行中有任何错误，它将返回错误响应消息。MySQL和PostgreSQL都有错误响应消息，区别非常大。让我们来看看两者的返回字段的结构。

类型错误响应结构{

严重性字符串

severity未本地化的字符串//仅在9.6和更高版本中

代码串

消息字符串

详细字符串

提示字符串

位置int32

内部位置int32

内部查询字符串

字符串在哪里

SchemaName字符串

表名字符串

ColumnName字符串

数据类型名称字符串

ConstraintName字符串

文件字符串

线路int32

常规字符串

未知字段映射[字节]字符串

}

类型SQLError struct {

代码uint16

消息字符串

状态字符串

}

从结构中可以直观的发现，与前面的列信息相反，PostgreSQL在这个块中返回了很多字段，而MySQL只返回了三个字段，代码、消息和状态。这三个字段很好理解，就是错误码、错误信息和状态。具体内容很多。如果你想知道，可以看官方文件:

关系型数据库

https://dev . MySQL . com/doc/MySQL-errors/8.0/en/server-error-reference . html

PostgreSQL在错误处理方面返回的信息远多于MySQL，包括错误级别、详细信息、提示等。，但实际上，在消息中，并不是所有的字段都会被填充。只有在因为某种原因报错时，才会填充相关字段，其他字段不需要返回。那么，为什么会有这么多字段，但是报错的理由实在是太多了呢？至于具体字段的含义，你可以去做。

一种数据库系统

https://www . PostgreSQL . org/docs/13/protocol-error-fields . html

在这里，我们也对MySQL和PostgreSQL的协议层有了基本的了解，包括它们的通信过程和消息格式等。，所以当我们实现自己的数据库客户端和中间件时，可以更快地上手。

由于文章的篇幅，再加上协议层也是一个非常大的知识点，所以很多地方没有深入的讲解和分析，大部分都是简单的介绍。当然，如果有什么问题想讨论，可以留言或者私聊。

PostgreSQL通信过程

1.postgresql SQL将首先发送一条SSLRequest消息，询问是否打开SSL加密。

2.如果需要打开，服务器会回复一个‘s’，否则会回复一个‘n’。

3.当回复为‘s’时，客户端开始与SSL握手，所有后续通信都用SSL加密。

4.在完成SSL初始握手或直接回复2中的‘n’后，客户端向服务器发送StartupMessage启动包。

5.当服务器收到启动包时，它认为它需要授权信息吗？如果是，它发送AuthenticationRequest。

在认证过程中，可以采用各种认证方式，不同的通信方式也不同，最后由服务器发送。

AuthenticationOk或ErrorResponse。

错误响应:连接尝试被拒绝。在服务中立即关闭连接。

AuthenticationOk:身份验证成功。

AuthenticationKerberosV5:前端必须加入服务器的KerberosV5认证会话。如果成功，服务器以AuthenticationOk响应，否则为ErrorResponse。

AuthenticationClearTextPassword:前端发送PasswordMessage，其中包含密码的明文形式。如果密码正确，服务器响应AuthenticationOk，否则为ErrorResponse。

AuthenticationcryptPassword:前端必须发送PasswordMessage，其中包含Crypt加密的密码。AuthenticationCryptPassport消息提供了一个2字符的salt。

AuthenticationMD5Password:前端必须发送PasswordMessage，包括用MD5加密的密码。AuthenticationMD5Password消息提供了一个4字符的salt。

AuthenticationSCMCredential:响应只能是本地Unix-domain连接到平台支持的SCM凭据消息。前端必须生成SCM凭证消息，然后发送单个数据字节。数据字节的内容很枯燥，它仅用于确保服务器等待足够长的时间，以便事件接受凭据消息。

AuthenticationGSS:前端发起GSSAPI流。发送前端PasswordMessage，包含GSSAPI数据流的第一部分，后续消息是服务器的AuthenticationGSSContinue所需要的。

AuthenticationSSPI:前端发起SSPI流。发送的前端PasswordMessage包含SSPI数据流，后续消息需要服务器的AuthenticationGSSContinue。

AuthenticationGSSContinue:该消息包含GSSAPI和SSPI流的后续响应数据。如果GSSAPI或SSPI数据需要更多数据来完成身份验证，前端必须发送另一个PasswordMessage。如果GSSAPI或SSPI身份验证完成，服务器将发送AuthenticationOK表示身份验证成功，发送ErrorResponse表示身份验证失败。

6.服务器在验证完成后发送一些参数信息，即ParameterStatus，包括server_version、client_encoding和DateStyle。

BackendKeydata:该消息提供了前端在将来取消请求时使用的密码密钥数据。前端不需要关注这个消息，但是必须等待ReadyForQuery消息。

ParameterStatus:这个消息告诉前端当前的配置参数，比如client_encoding或者DataStyle。前端可以忽略该消息或将其记录下来供以后使用。前端不需要响应这个消息，而是应该继续等待ReadyForQuery消息。

ReadyForQuery:初始化后，前端可以发送命令。

ErrorResponse:初始化失败，发送此消息后连接关闭。

NoticeResponse:警告消息。前端应该显示消息，然后继续等待ReadyForQuery或ErrorResponse。

7.最后，服务器发送一个ReadyForQuery，表示一切准备就绪，可以成功创建连接了。

8.从7开始，发送AuthenticationOk和ErrorResponse后到最后发送ReadyForQuery，客户端不会发送信息，而是一直等待。

9.完成初始化过程后，开始正常的查询通信。

command complete:SQL命令正常结束。

CopyInResponse:后端准备将数据从前端复制到表中。

CopyOutResponse:后端准备将数据从表复制到前端。

RowDescription:表示SELECT、FETCH等操作返回的行。该消息描述了列布局。此消息跟在DataRow消息之后。

DataRow:由SELECT和FETCH返回的行的集合。

EmptyQueryResponse:检测到空的查询字符串。

ErrorResponse:出现错误。

ReadyForQuery:查询过程结束。单独的消息通知前端，因为查询字符串可能包含多个SQL命令。无论成功或失败，ReadyForQuery将始终被发送。

NoticeResponse:表示查询包含相关的警告信息。提示符会继续执行其他消息中的命令，比如后端。