MySQL网络协议分析

本篇文章向大家介绍《MySQL网络协议分析》，主要包括网络编程、MySQL、网络传输协议，具有一定的参考价值，需要的朋友可以参考一下。

MySQL对大家来说，都应该很熟悉了，从大学里的课程到实际工作中数据的存储查询，很多时候都需要用到数据库，很多人也写过与数据库交互的程序，在Java中你可能一开始会使用原生mysql-connector-java来进行操作，后来你会接触到Hibernate，Mybatis等ORM框架，其实它们底层也是基于mysql-connector-java，但很多时候我们并不清楚程序是怎么跟数据库具体交互的，比如执行一个SQL查询，程序是如何从MySQL中获取数据的呢？今天就让我们来看看最基础的MySQL网络协议分析。

引言

阅读本文之前你需要对网络协议需要有基本的了解，比如两台机子之间的数据是如何通信的，硬件层可以暂时不需了解，但网络层和传输层的协议要有一定的理解，比如IP数据包，TCP/IP协议，UDP协议等相关概念，有了这些基础，有利于你阅读本文。

背景

在历史悠久的时代，数据库只作为单机存储，也不怎么需要与程序进行交互的时候的首，它的网络通信并不是那么重要，但随着时代的发展，数据库不再只是单纯的作为一个数据的仓库了，它需要提供与外界的交互，比如远程连接，程序操作数据库等，这时候一份规范的网络通信的协议就非常重要了，比如它是如何校验权限，如何解析SQL语句，如何返回执行结果都需要用到相应的协议，很多时候我们并不需要接触这些内容，因为它太底层了，我们直接使用把它们封装好的第三方包就可以了，为什么还要去学习它的网络协议呢？确实对于一开始学习编程的人来说，这有点操之过急，反而有时候会适得其反，但当你对这一方面有了一定的了解之后，你便会迫不及待得想去探索更深层的奥秘，去了解并学习我们平常用的第三方类库是怎么去实现，明白它的底层原理，甚至对一些莫名其妙的bug也不会再害怕。

MySQL连接方式

分析协议，我们首先要了解如何与数据库连接，说到MySQL连接方式，大家突然可能有点懵，其实它一直伴随着我们，比如我们第一次装数据库完成后执行的第一次登录，比如你没有设置密码：

mysql -uroot

这是最基本的一种数据库连接方式，那么MySQL连接方式到底有几种呢？到MySQL5.7为止，总共有五种，分别是TCP/IP，TLS/SSL，Unix Sockets，Shared Memory，Named pipes，下面我们就来看看这五种的区别：

从上表中我们可以清晰看出每种连接方式的区别，接下里我会具体说明几种连接是怎么操作的，由于我的机子是Mac OS系统，这里只模拟非Windows系统下的三种方式,因为这三种方式都是默认开启的，我们不需要进行任何配置：

1.Unix Sockets：

mysql -uroot

若你在本机使用这种方式连接MySQL数据库的话，它默认会使用Unix Sockets。

2.TCP/IP：

mysql --protocol=tcp -uroot
mysql -P3306 -h127.0.0.1 -uroot

连接的时候我们指定连接协议，或者指定相应的IP及端口，我们的连接方式就变成了TCP/IP方式。

3.TLS/SSL：

mysql --protocol=tcp -uroot --ssl=on
mysql -P3306 -h127.0.0.1 -uroot --ssl=on

上表说过，TLS/SSL是基于TCP/IP的，所以我们只需再指定打开ssl配置即可。

然后我们可以通过以下语句来查询目前数据库的连接情况：

SELECT DISTINCT connection_type from performance_schema.threads where connection_type is not null

那么我们如何选择连接方式呢？个人总结了以下几个原则：

• 若是你能确定程序和数据库在同一台机子(类Unix系统)上，推荐使用Unix Sockets，因为它效率更高；
• 若数据库分布在不同的机子上，且能确保连接安全或者安全性要求不是那么高，推荐使用TCP/IP，反之使用TLS/SSL；

MySQL数据包

通信中最重要的就是数据，那么程序是如何和MySQL Server进行通信，并交互数据的呢？比如如何验证账户，发送查询语句，返回执行结果等，我先画一个流程图来模拟一下整个过程，帮助大家理解：

整个过程相对来说还是比较清晰的，我们对连接请求和断开请求不需要过分关心，只需要了解这一点就可以了，重要的是其他几点，那么在这几步中，数据是怎么进行交互的呢？

其实主要就是两步，Client将执行命令编码成Server要求的格式传输给Server端执行，Server端将执行结果传输给Client端，Client端再根据相应的数据包格式解析获得所需的数据。

1.基本数据类型

虽然网络中的数据是用字节传输的，但它背后的数据源都是有类型的数据，MySQL协议也有基本的数据类型，好比Java中的8种基本数据类型，但MySQL协议中简单的多，它只有两种基本数据类型，分别为Integer(整型)，String(字符串)，下面我们就来看看这两种类型。

Integer(整型)

首先Integer在MySQL协议中有两种编码方式，分别为FixedLengthInteger和LengthEncodedInteger
,其中前者用于存储无符号定长整数，实际中使用的不多，这里着重讲一下后者。

使用LengthEncodedInteger编码的整数可能会使用1, 3, 4, 或者9 个字节，具体使用字节取决于数值的大小，下表是不同的数据长度的整数所使用的字节数：

举个简单的例子，比如1024的编码为：

use godpan

相应的报文格式则为：

PreparedStatement ps = connection.prepareStatement("SELECT * FROM `godpan_fans` where id=?");
ps.setInteger(1, 1);
ps.executeQuery();

得到下面的PREPARE_OK包，仅供参考：

PSOK{statementId=1, columns=5, parameters=1}

如果上面的columns大于0，以及parameters大于0，则将有额外的两个包传输，分别是columns的信息以及parameters的信息，对应信息结构：

到此整个PREPARE_OK包发送完毕。

Row Data Binary

这个包跟上面提到的Row Data包有什么差别呢？主要有两点：

• 用不同的方式定义NULL；
• 数据编码不再单纯的使用LengthEncodedString，而是根据数据类型的不同进行相应的编码；

后面我会分别解释这两点，我们先来看看它的结构：

现在我们来看一下它的两个特点，首先我们来看它是如何来定义NULL的，首先我们看到他的结构中有一个Null Bit Map，除去两个标识位，真正用于标识数据信息的就是(col_count+7)/8位字节，这里我先给出结论，后面再给大家具体分析：

上面给出了标识NULL的基本算法，原则是哪个参数（次序为n)为NULL，则Null Bit Map相应的值加上2^n,8个参数为一个周期，以此类推。

接着我们来看一下第二点，是如何用具体值类型来对相应的值进行编码的，这里主要分为三类，基本数据类型，时间类型，字符串类型；

• 基本数据类型：比如TINYINT使用一个字节编码，FLOAT使用四个字节，DOUBLE使用8个字节等；
• 时间类型：使用类似LengthEncodedString的编码方式编码，具体可参考MySQL_PROTOCOL；
• 字符串类：不属于上面两类的都属于字符串类型，使用普通的LengthEncodedString；

Execute包

Execute包顾名思义是一个执行包，它是由Client端发送到Server端的，但它和普通的命令又有点不同，它主要是用来执行预处理语句，并会携带相应参数，具体结构如下：

Execute包从Client端发送到Server端后可能会得到以下几个结果：

• OK包
• ERROR包
• Result Set包（可能多个）

我们需要根据包的不同类型来进行不同的处理。

总结

本篇文章主要讲述了MySQL的连接方式，通信过程及协议，以及传输包的基本格式和相关传输包的类型，内容相对来说，比较多也比较复杂，我也是将近三周才写完，但总体按照我自学的思路走，不会太绕，有些点可能需要细心思考下，写的有误的地方也希望大家能指正，希望对大家有所帮助，后面可能会写几个实例和大家一起学习。

好了，本文到此结束，带大家了解了《MySQL网络协议分析》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！