NoteDeep

  • mysql知识体系以及学习经验

  • mysql内部原理深度解析

    • mysql连接原理

  • 简介

mysql连接到底是什么？到底是如何建立的？我想很多人都有这样的疑问，让我们来深入学习一下吧。

mysql连接的两种方式

mysql客户端可以通过两种方式链接mysql，一种为unix socket，另外一种为基于tcp/ip协议。
Unix socket方式是通过使用一个Unix socket文件来实现连接的，默认位于 /tmp/mysql.sock。Unix socket方式比tcp/ip方式要快，但是不能连接远程的mysql。

tcp/ip的方式可以支持远程访问数据库
  • socket 方式：mysql -h localhost -uroot -p 或者 mysql -uroot -p
  • tcp/ip 方式：mysql -h 127.0.0.1 -uroot -p

TCP/IP连接全过程解析

登录服务器，通过tcpdump抓包。

tcpdump -S -nn -tttt -i lo host 127.0.0.1 and port 3306 and tcp -c 100 -w /tmp/tcpdump.cap

参数：
-S 将tcp的序列号以绝对值形式输出，而不是相对值。 
-nn 不进行端口名称的转换。
-tttt 在每一行中输出由date处理的默认格式的时间戳。
-i eth0 指定监听的网络接口   // 网卡的名称，可以通过ifconfig查看
host 127.0.0.1 and port 3306  设置监听127.0.0.1:3306的网络包
-c 100 表示监听100包就结束
-w 表示把抓取到的包写到一个cap文件中，cap文件可以通过wireshark打开，便于分析

另起一个终端，通过tcp的方式连接mysql 。
mysql -h 127.0.0.1 -uroot -p
select * from mysql.user limit 1;
exit;

停止抓包，此时会生成一个/tmp/tcpdump.cap 文件。为了便于查看，我们需要一个工具 ——Wireshark。
Wireshark是一个网络协议检测工具，可以用来分析tcpdump抓到的cap文件。

Wireshark中的截图：

第一步：tcp三次握手

端口45668就是本次的客户端，3306是本次的服务端。
  1. 客户端首先向服务端发出了一个【SYN】包，初始Seq number为3812756736。
  2. 服务端随后发出了一个【SYN，ACK】包，Ack number: 3812756737 （即等于客户端的初始 seq+1）。 服务端的初始Seq number为1854855471
  3. 最后客户端发出一个【ACK】包，Ack number: 1854855472（即等于服务端的初始 seq+1），Seq number：3812756737

tcp包由tcp header以及tcp data两部分组成。Seq表示tcp data中第一个数据字节的序号。
首次建立连接时，序列号并非从0开始，两端会使用 ISN 产生器，生成各自的初始Seq序列号（通常两者并不相等）。数值范围是 [0, 2^32)
seq、ack的作用：
  • 避免与之前连接的区段混淆，解决网络报乱序的问题。
  • 解决丢包的问题

第二步：继续寒暄+mysql用户身份验证


2-1)Mysql server发出问候包(mysql greeting)

Transmission Control Protocol, Src Port: 3306, Dst Port: 45668, Seq: 1854855472, Ack: 3812756737, Len: 99 Mysql Protocol： Server Greeting proto=10 version=5.7.18-0ubuntu0.16.04.1-log 表示发送server greeting包，对客户端的访问表示友好地问候。如下图：表示从1854855472开始，长度为99都是tcp data的内容。


根据tcp/ip协议栈：
mysql数据包封装在tcp data里面

tcp data数据Len为99 byte，Mysql Packet Len只有95 byte，这剩余的4个byte是mysql协议的头部。其实就是传输层的协议(Tcp)包裹着应用层的协议(Mysql)。
上图中的应用程序就是这里的mysql，

问候数据表中的字段解释
名称
Offset
长度
描述
Protocol
0
1
mysql协议的版本号
此处版本号为10
Version
1
ver_len
Zero-terminated服务端版本字符串. 长度不是固定的。接下来的字段的偏移量，都依赖这个值。
此处version为：5.7.18-0ubuntu0.16.04.1-log
此处ver_len = 28
Thread ID
ver_len+1
4
mysql内部，处理这个连接的线程ID
此处Thread ID为251418
Salt
ver_len+5
9
随机字符串
此处Salt为 6.}eJ\037\032Y
Server Capabilities
ver_len+14
2
用标志位表示，告诉客户端服务器是否具备某个能力。
此处为：0xf7ff （0x表示16进制）


Server Language
ver_len + 16
1
默认的字符集编码
Server Language: latin1 COLLATE latin1_swedish_ci (8)
Server Status
ver_len + 17
2
服务器状态位

Extended Server Capabilities
ver_len + 19
2
扩展的mysql server能力标志位
Extended Server Capabilities: 0x81ff
Authentication Plugin Length
ver_len + 21
1
mysql认证插件占用的长度
Authentication Plugin Length: 21
Unused
ver_len + 22
10
保留的，未使用的
Unused: 00000000000000000000
Salt
ver_len + 32
13
又一个加盐的随机字符串
Salt: T_<;3X)'^[r!
Authentication Plugin
ver_len + 45
22
使用的认证插件，长度正好是上面的Authentication Plugin Length的21+1 ，最后多出来的一个是00
Authentication Plugin: mysql_native_password


2-2)客户端回应mysql的问候

45668 → 3306 [ACK] Seq=3812756737 Ack=1854855571 Win=43776 Len=0 TSval=971245015 TSecr=971245015
这里Ack=1854855571。 是由于greeting的seq是1854855472，len为99. 1854855571 = 1854855472+99。
即客户端告诉服务端，（ 所有 <1854855571的数据我都已经成功接收了，也就是说，客户端成功接收了刚刚mysql server发出的问候包，没有丢包。）

2-3)客户端发起登录请求：

Transmission Control Protocol, Src Port: 45668, Dst Port: 3306, Seq: 3812756737, Ack: 1854855571, Len: 186
MySQL Protocol Login Request
和上面相似，tcp data的长度为186，而mysql packet 为182，说明mysql协议的header仍然是4byte。
如上图所示，客户端同样在告知mysql server 客户端具备的能力、客户端的编码、 登录的用户名、客户端所使用的username、以及加密过后的password（是通过之前server greeting中的两个salt字段进行加密生成的）、还有就是客户端所使用的认证插件，依然是mysql_native_password.

2-4)mysql server表示收到了你的登录请求

3306 → 45668 [ACK] Seq=1854855571 Ack=3812756923 Win=44800 Len=0 TSval=971245015 TSecr=971245015
这里的Ack number = 3812756923 = 3812756737 + 186 （同理）

2-5)mysql server 表示验证通过

Transmission Control Protocol, Src Port: 3306, Dst Port: 45668, Seq: 1854855571, Ack: 3812756923, Len: 11
MySQL Response OK

第三步：客户端请求执行query

ok，连接成功建立了，现在可以开始执行query了。

3-1）客户端获取mysql的系统变量version_comment

这里请求了 select @@version_comment limit 1;
Transmission Control Protocol, Src Port: 45668, Dst Port: 3306, Seq: 3812756923, Ack: 1854855582, Len: 37


3-2) 服务端返回系统变量version_comment

Transmission Control Protocol, Src Port: 3306, Dst Port: 45668, Seq: 1854855582, Ack: 3812756960, Len: 72
即：(Ubuntu)
这个version_comment是什么呢：
CMake 命令有一个COMPILATION_COMMENT Option，允许在构建MySQL时指定注释。version_comment就是该注释的值。

3-3）客户端表示收到了该结果

Transmission Control Protocol, Src Port: 45668, Dst Port: 3306, Seq: 3812756960, Ack: 1854855654, Len: 0
Ack: 1854855654 = 1854855582 + 72

3-4）客户端继续请求执行query

终于到了执行我们一开始的query语句的时候了！
select * from mysql.user limit 1;

3-5）服务端返回结果

Transmission Control Protocol, Src Port: 3306, Dst Port: 45668, Seq: 1854855654, Ack: 3812756997, Len: 3113
这里的结果tcp data居然长达 3113 byte，而且拆分成了很多mysql Protocol块。

3-6）客户端再次表示收到了结果

Transmission Control Protocol, Src Port: 45668, Dst Port: 3306, Seq: 3812756997, Ack: 1854858767, Len: 0
Ack: 1854858767 = 1854855654 + 3113

现在我们大概清楚了query在网络层面的过程，一条query执行，会分为三步
  1. client请求执行query
  2. server返回结果
  3. client表示收到了该结果

第四步：断开连接

4-1）客户端请求断开连接

Transmission Control Protocol, Src Port: 45668, Dst Port: 3306, Seq: 3812756997, Ack: 1854858767, Len: 5
MySQL Protocol Request Command Quit

4-2）类似tcp四次挥手过程，但是略有不同。

  1. 45668 → 3306 [FIN, ACK] Seq=3812757002 Ack=1854858767 Win=174720 Len=0 TSval=971248950 TSecr=971248296。客户端发出【FIN、ACK】包，表示我想要断开连接了。Seq=3812757002 = 3812756997 + 5。
  2. 3306 → 45668 [FIN, ACK] Seq=1854858767 Ack=3812757003 Win=44800 Len=0 TSval=971248950 TSecr=971248950。紧接着服务端也发出【FIN、ACK】包，表示我知道你想断开连接了，我也想断开连接。
  3. 45668 → 3306 [ACK] Seq=3812757003 Ack=1854858768 Win=174720 Len=0 TSval=971248950 TSecr=971248950。 最后，客户端发出【ACK】包，表示我也知道你要断开连接了。

查看mysql连接数（线程数）


show status like "Threads%";

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 3 |
| Threads_connected | 5 |
| Threads_created | 12070 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.01 sec)

Threads_connected : 当前的连接数
Threads_created: 曾经有多少个connection连接过数据库
Threads_cached：这个是mysql为了提高性能而在内部提供了一个线程的连接池，将空闲的连接不是立即销毁而是放到线程连接池中，如果新加进来连接不是立刻创建线程而是先从线程连接池中找到空闲的连接线程，然后分配，如果没有才创建新的线程。
Threads_running: 正在运行的连接

Threads_catched值不是无限大的，一般为32左右。

首先每个线程会分配栈空间。
其次mysql数据库会为每个连接分配连接缓冲区和结果缓冲区，也是要消耗时间的。
接着每次每个连接都会进行tcp3次握手和断开时的4次挥手，分配一些缓存之类的空间，

每个连接到MySQL服务器的线程都需要有自己的缓冲。甚至在线程空闲时，也大约需要立刻分配256K。
-- 它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约 read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size 大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块，例如 tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了。注意，这里需要考虑的不只有一点 -- 可能会分配多个同一种类型的缓存，例如用来处理子查询。一些特殊的查询的内存使用量可能更大 -- 如果在MyISAM表上做成批的插入时需要分配 bulk_insert_buffer_size 大小的内存。执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

连接池

数据库连接池技术的思想非常简单，将数据库连接作为对象存储在一个Vector对象中，一旦数据库连接建立后，不同的数据库访问请求就可以共享这些连接，这样，通过复用这些已经建立的数据库连接，可以克服上述缺点，极大地节省系统资源和时间。

连接池的操作： （1）建立数据库连接池对象（服务器启动）。 （2）按照事先指定的参数创建初始数量的数据库连接（即：空闲时的连接数）。 （3）对于一个数据库访问请求，直接从连接池中得到一个连接。如果数据库连接池对象中没有空闲的连接，且连接数没有达到最大限制，则创建一个新的数据库连接。 （4）存取数据库。 （5）关闭数据库连接，并非真正关闭，而是将其放入空闲队列中。如实际空闲连接数大于初始空闲连接数则释放连接。 （6）释放数据库连接池对象（服务器停止、维护期间，释放数据库连接池对象，并释放所有连接）。



评论列表

    mysql连接的两种方式
    TCP/IP连接全过程解析查看mysql连接数（线程数）