侧边栏壁纸
  • 累计撰写 244 篇文章
  • 累计创建 16 个标签
  • 累计收到 0 条评论
隐藏侧边栏

MySQL 数据库连接原理和性能优化

kaixindeken
2021-04-26 / 0 评论 / 0 点赞 / 307 阅读 / 2,091 字

常见的 MySQL 客户端

MySQL 服务端启动之后,就可以通过客户端建立与服务端的连接,然后发送查询/更新请求了。

我们可以通过 MySQL 安装目录 bin 目录下的 mysql 二进制文件在终端窗口通过命令行建立与 MySQL 服务端的连接,也可以通过图形化客户端软件建立这种连接(比如 MySQL Workbench、Navicat For MySQL、DataGrip、TablePlus、Sequel Pro 等),此外,我们在 PHP、Go、Python、Java 这些后端编程语言中使用的数据库 SDK 也是一种 MySQL 客户端,只不过这些 SDK 对数据库连接做了封装而已。

数据库连接过程

不管是 MySQL 服务端还是客户端,本质上都是计算机的进程,所以这个连接过程和通过浏览器(HTTP 客户端)请求指定网站(HTTP 服务端)连接建立过程并无二致,都是进程之间的通信。只不过后者是 HTTP 请求,基于 HTTP 协议,而前者是 TCP 请求,基于 TCP 协议。

注:虽然 MySQL 也支持 TCP 请求之外的其他连接建立方式,比如 Unix 套接字,但是我们日常使用的通常都是基于 TCP 连接的,这里就以此为例进行讲解。

既然是 TCP 请求,那么通信的双方 —— MySQL 客户端和服务端需要具备必要的进程信息:源 IP 地址、源端口号、目标 IP 地址、目标端口号,这样才能建立可靠连接,这里的目标 IP 地址就是 MySQL 服务端所在的 IP 地址。

所以从这个角度来说,通过 mysql 命令建立与服务端的连接和通信,必须指定服务端主机(IP 地址或者主机名)和端口号(默认是 3306,可以省略,但如果不是 3306,则必须指定)。

在完成经典的 TCP 握手后,服务端就要开始认证你的身份。

  • 如果用户名或密码不对,就会收到一个 Access denied for user 的错误,然后客户端程序结束执行。

  • 如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限,之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。这就意味着,一个用户成功建立连接后,即使对这个用户的权限做了修改,也不会影响已经存在连接的权限,只有新建的连接才会使用新的权限设置。

数据库连接状态

连接完成后,如果你没有后续的动作,这个连接就处于空闲状态,你可以在 show processlist 命令中看到它:

1.jpeg

其中的 Command 列显示为 Sleep 的行,表示一个空闲连接。

客户端如果太长时间没动静,连接器就会自动将它断开,这个时间是由参数 wait_timeout 控制的,默认值是 28800 秒(也就是 8 小时)。你可以通过 show global variables 命令查看这个配置值:

1.jpeg

你可以通过 MySQL 配置文件修改这个默认配置(mysqld 分组下配置即可)。

如果连接被断开之后,客户端再次发送请求的话,就会收到一个错误提醒:Lost connection to MySQL server during query。

长连接与短连接

在 MySQL 数据库中,还有长连接与短连接的概念:

  • 长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接,对应在 Web 应用中,就是后端程序与数据库之间的连接建立之后,就会一直重用这个连接(为了提升数据库并发性,可以建立一个数据库连接池);
  • 短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个,对应在 Web 应用中,就是后端程序与数据库建立连接,完成查询/更新后,就断开连接,下次操作数据库再重连。
    这个和 HTTP 长连接和短连接的概念很像,并且显然,无论是 HTTP 还是 MySQL,使用长连接都能有效减少建立连接过程带来的损耗,进而提升性能。

不过在 MySQL 中,使用长连接会有一定的副作用 —— 有些时候会导致 MySQL 占用内存涨得特别快。这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的,这些资源只有在连接断开的时候才会释放。对于这个问题,通常有两种解决方案:

  • 定期断开长连接:使用一段时间,或者程序里判断执行过一个占用较大内存的查询后,主动断开连接;
  • 如果使用的是 MySQL 5.7 或者更高版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。
0

评论区