【性能优化】MySQL 数据库连接原理和性能优化 - 学习/实践

1.应用场景

学习MySQL数据库连接原理和性能优化, 开发高性能程序.

2.学习/操作

1. 文档阅读

MySQL 数据库连接原理和性能优化 - 高性能 MySQL 实战 | Laravel 学院

05丨检索数据:你还在SELECT * 么?-极客时间

2. 整理输出

2.1 常见的 MySQL 客户端

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

我们可以通过 MySQL 安装目录 bin 目录下的 mysql 二进制文件在终端窗口通过命令行建立与 MySQL 服务端的连接,

也可以通过图形化客户端软件建立这种连接(比如 MySQL Workbench、Navicat For MySQL、DataGrip、TablePlus、Sequel Pro 等 )。

参考:DBMS - GUI/可视化管理工具_穿素白衫的少年的博客-CSDN博客_dbms下载  // DBMS - 可视化管理工具

此外,我们在 PHP、Go、Python、Java 这些后端编程语言中使用的数据库 SDK 也是一种 MySQL 客户端,只不过这些 SDK 对数据库连接做了封装而已。

Note:

SDK  abbr. 软件开发工具包(Software Development Kit)

以上都是日常开发过程中经常打交道的 MySQL 客户端,这里,为了更接近底层原理,我们使用 mysql 命令建立与服务端的连接。

2.2 MySQL 命令解析

我们可以通过如下命令格式建立数据库连接:

mysql -h主机名 -P端口号 -u用户名 -p密码


注:如果 MySQL 服务端监听的端口号是默认的 3306 的话,可以省略端口号

(如果想要使用其他端口号,可以在启动服务端时通过命令行参数或者配置文件指定)。

我们以默认的 root 用户为例,在终端窗口通过如下命令即可建立与本地 MySQL 服务端的连接:-w838

-w838

这里我们通过 -h 指定 MySQL 服务端所在的主机 IP 地址,这里是本地 IP 地址 127.0.0.1

通过 -u 指定用户名是 root

通过 -p 指定对应的密码是 root

虽然可以通过这种方式比较便捷地指定密码信息,但是如果是连接到生产环境 MySQL 服务器,则不建议这么做,因为这样会导致密码的泄露,取而代之地,可以通过如下这种交互式方式输入密码发起连接请求:

-w530

这样一来,输入的密码信息就不可见了。

连接成功后,我们就可以通过这个交互式界面来操作 MySQL 数据库了。

在进行下一步操作之前,我们先来看看 MySQL 客户端是如何建立与服务端的连接的。

2.3 数据库连接过程

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

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

学院君在网络协议的传输层协议中已经介绍过 TCP 通信的原理,既然是 TCP 请求,那么通信的双方 —— MySQL 客户端和服务端需要具备必要的进程信息:源 IP 地址、源端口号、目标 IP 地址、目标端口号,这样才能建立可靠连接,这里的目标 IP 地址就是 MySQL 服务端所在的 IP 地址,上面的示例是 127.0.0.1,目标端口号就是 MySQL 服务端进程的端口号,默认是 3306

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

在完成经典的 TCP 握手后,服务端就要开始认证你的身份,这个时候用的就是你输入的用户名和密码:

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

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

2.4 数据库连接状态

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

-w913

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

客户端如果太长时间没动静,连接器就会自动将它断开,这个时间是由参数 wait_timeout 控制的,默认值是 28800 秒(也就是 8 小时)。

你可以通过 show global variables 命令查看这个配置值:

 show global variables like 'wait_timeout';

-w627

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

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

2.5 长连接与短连接

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

A.长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接,对应在 Web 应用中,就是后端程序与数据库之间的连接建立之后,就会一直重用这个连接为了提升数据库并发性,可以建立一个数据库连接池);

B.短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个,对应在 Web 应用中,就是后端程序与数据库建立连接,完成查询/更新后,就断开连接,下次操作数据库再重连。

这个和 HTTP 长连接和短连接的概念很像,并且显然,无论是 HTTP 还是 MySQL,使用长连接都能有效减少建立连接过程带来的损耗,进而提升性能。

不过在 MySQL 中,使用长连接会有一定的副作用 —— 有些时候会导致 MySQL 占用内存涨得特别快。

这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的,这些资源只有在连接断开的时候才会释放。

对于这个问题,通常有两种解决方案:

A.定期断开长连接:使用一段时间,或者程序里判断执行过一个占用较大内存的查询后,主动断开连接;

B.如果使用的是 MySQL 5.7 或者更高版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。

关于 MySQL 客户端与服务端的连接,以及连接状态和类型我们就简单介绍到这里.

下篇教程,我们更进一步,来探究一条 SQL 查询语句在 MySQL 客户端和服务端之间都经历了哪些曼妙之旅,

最后把查询结果返回给客户端的。

后续补充

...

3.问题/补充

TBD

4.参考

MySQL 数据库连接原理和性能优化 - 高性能 MySQL 实战 | Laravel 学院

05丨检索数据:你还在SELECT * 么?-极客时间

后续补充

...

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值