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

电玩女神 2022-11-27 08:59 104阅读 0赞

1.应用场景

<table> 
 <tbody> 
 <tr> 
 <td>学习MySQL数据库连接原理和性能优化, 开发高性能程序.</td> 
 </tr> 
 </tbody> 
</table>

2.学习/操作

<table> 
 <tbody> 
 <tr> 
 <td> <h3>常见的 MySQL 客户端</h3> MySQL 服务端启动之后，就可以通过客户端建立与服务端的连接，然后发送查询/更新请求了。 我们可以通过 MySQL 安装目录&nbsp;<code>bin</code>&nbsp;目录下的&nbsp;<code>mysql</code>&nbsp;二进制文件在终端窗口通过命令行建立与 MySQL 服务端的连接， 也可以通过图形化客户端软件建立这种连接（比如 MySQL Workbench、Navicat For MySQL、DataGrip、TablePlus、Sequel Pro 等 ）。 参考：<a href="https://blog.csdn.net/william_n/article/details/80288057" rel="nofollow">https://blog.csdn.net/william_n/article/details/80288057</a>&nbsp; //&nbsp;DBMS - 可视化管理工具 &nbsp; 此外，我们在 PHP、Go、Python、Java 这些后端编程语言中使用的数据库 SDK 也是一种 MySQL 客户端，只不过这些 SDK 对数据库连接做了封装而已。 &nbsp; Note: SDK&nbsp;&nbsp;abbr. 软件开发工具包（Software Development Kit） &nbsp; 以上都是日常开发过程中经常打交道的 MySQL 客户端，这里，为了更接近底层原理，我们使用&nbsp;<code>mysql</code>&nbsp;命令建立与服务端的连接。 &nbsp; <h3>mysql 命令解析</h3> 我们可以通过如下命令格式建立数据库连接： 
 <blockquote> 
 mysql -h主机名 -P端口号 -u用户名 -p密码 
 注：如果 MySQL 服务端监听的端口号是默认的 3306 的话，可以省略端口号 
 （如果想要使用其他端口号，可以在启动服务端时通过命令行参数或者配置文件指定）。 
 </blockquote> 我们以默认的&nbsp;<code>root</code>&nbsp;用户为例，在终端窗口通过如下命令即可建立与本地 MySQL 服务端的连接：<img alt="-w838" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9xY2RuLnh1ZXl1YW5qdW4uY29tL3N0b3JhZ2UvdXBsb2Fkcy9pbWFnZXMvZ2FsbGVyeS8yMDIwLTA4L2ltYWdlLTE1OTczMzQyMDY1MTI0LmpwZw?x-oss-process=image/format,png"> <img alt="-w838" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9xY2RuLnh1ZXl1YW5qdW4uY29tL3N0b3JhZ2UvdXBsb2Fkcy9pbWFnZXMvZ2FsbGVyeS8yMDIwLTA4L2ltYWdlLTE1OTczMzQyMDY1MTI0LmpwZw?x-oss-process=image/format,png"> 这里我们通过&nbsp;<code>-h</code>&nbsp;指定 MySQL 服务端所在的主机 IP 地址，这里是本地 IP 地址&nbsp;<code>127.0.0.1</code>， 通过&nbsp;<code>-u</code>&nbsp;指定用户名是&nbsp;<code>root</code>， 通过&nbsp;<code>-p</code>&nbsp;指定对应的密码是&nbsp;<code>root</code>。 &nbsp; 虽然可以通过这种方式比较便捷地指定密码信息，但是如果是连接到生产环境 MySQL 服务器，则不建议这么做，因为这样会导致密码的泄露，取而代之地，可以通过如下这种交互式方式输入密码发起连接请求： <img alt="-w530" height="45" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9xY2RuLnh1ZXl1YW5qdW4uY29tL3N0b3JhZ2UvdXBsb2Fkcy9pbWFnZXMvZ2FsbGVyeS8yMDIwLTA4L2ltYWdlLTE1OTczMzQ4NDQ4ODU3LmpwZw?x-oss-process=image/format,png" width="578"> 这样一来，输入的密码信息就不可见了。 连接成功后，我们就可以通过这个交互式界面来操作 MySQL 数据库了。 &nbsp; 在进行下一步操作之前，我们先来看看 MySQL 客户端是如何建立与服务端的连接的。 &nbsp; <h3>数据库连接过程</h3> 不管是 MySQL 服务端还是客户端，本质上都是计算机的进程，所以这个连接过程和通过浏览器（HTTP 客户端）请求指定网站（HTTP 服务端）连接建立过程并无二致，都是进程之间的通信。只不过后者是 HTTP 请求，基于 HTTP 协议，而前者是 TCP 请求，基于 TCP 协议。 
 <blockquote> 
 注：虽然 MySQL 也支持 TCP 请求之外的其他连接建立方式，比如 Unix 套接字，但是我们日常使用的通常都是基于 TCP 连接的，这里就以此为例进行讲解。 
 </blockquote> 学院君在<a href="https://xueyuanjun.com/books/network-protocols/chapter/transport-layer-protocol" rel="nofollow">网络协议的传输层协议</a>中已经介绍过 TCP 通信的原理，既然是 TCP 请求，那么通信的双方 —— MySQL 客户端和服务端需要具备必要的进程信息：源 IP 地址、源端口号、目标 IP 地址、目标端口号，这样才能建立可靠连接，这里的目标 IP 地址就是 MySQL 服务端所在的 IP 地址，上面的示例是&nbsp;<code>127.0.0.1</code>，目标端口号就是 MySQL 服务端进程的端口号，默认是&nbsp;<code>3306</code>。 所以从这个角度来说，通过&nbsp;<code>mysql</code>&nbsp;命令建立与服务端的连接和通信，必须指定服务端主机（IP 地址或者主机名）和端口号（默认是 3306，可以省略，但如果不是 3306，则必须指定）。 在完成经典的 TCP 握手后，服务端就要开始认证你的身份，这个时候用的就是你输入的用户名和密码： &nbsp; A.如果用户名或密码不对，就会收到一个&nbsp;<code>Access denied for user</code>&nbsp;的错误，然后客户端程序结束执行：&nbsp;<img alt="-w864" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9xY2RuLnh1ZXl1YW5qdW4uY29tL3N0b3JhZ2UvdXBsb2Fkcy9pbWFnZXMvZ2FsbGVyeS8yMDIwLTA4L2ltYWdlLTE1OTczMzU5Mzg3MDIzLmpwZw?x-oss-process=image/format,png"> B.如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使对这个用户的权限做了修改，也不会影响已经存在连接的权限，只有新建的连接才会使用新的权限设置。 &nbsp; <h3>数据库连接状态</h3> 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在&nbsp;<code>show processlist</code>&nbsp;命令中看到它： <img alt="-w913" height="127" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9xY2RuLnh1ZXl1YW5qdW4uY29tL3N0b3JhZ2UvdXBsb2Fkcy9pbWFnZXMvZ2FsbGVyeS8yMDIwLTA4L2ltYWdlLTE1OTczMzYxMzU3MTIxLmpwZw?x-oss-process=image/format,png" width="698"> 其中的 Command 列显示为 Sleep 的行，表示一个空闲连接。 &nbsp; 客户端如果太长时间没动静，连接器就会自动将它断开，这个时间是由参数&nbsp;<code>wait_timeout</code>&nbsp;控制的，默认值是 28800 秒（也就是 8 小时）。 你可以通过&nbsp;<code>show global variables</code>&nbsp;命令查看这个配置值： 
 <blockquote> 
 &nbsp;show global variables like 'wait_timeout'; 
 </blockquote> <img alt="-w627" height="143" src="https://imgconvert.csdnimg.cn/aHR0cHM6Ly9xY2RuLnh1ZXl1YW5qdW4uY29tL3N0b3JhZ2UvdXBsb2Fkcy9pbWFnZXMvZ2FsbGVyeS8yMDIwLTA4L2ltYWdlLTE1OTczMzY0NTkyMjE1LmpwZw?x-oss-process=image/format,png" width="697"> 你可以通过 MySQL 配置文件修改这个默认配置（<code>mysqld</code>&nbsp;分组下配置即可）。 &nbsp; 如果连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：<code>Lost connection to MySQL server during query</code>。 &nbsp; <h3>长连接与短连接</h3> 在 MySQL 数据库中，还有长连接与短连接的概念： 
 <blockquote> 
 A.长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接，对应在 Web 应用中，就是后端程序与数据库之间的连接建立之后，就会一直重用这个连接（为了提升数据库并发性，可以建立一个数据库连接池）； 
 &nbsp; 
 B.短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个，对应在 Web 应用中，就是后端程序与数据库建立连接，完成查询/更新后，就断开连接，下次操作数据库再重连。 
 </blockquote> 这个和 HTTP 长连接和短连接的概念很像，并且显然，无论是 HTTP 还是 MySQL，使用长连接都能有效减少建立连接过程带来的损耗，进而提升性能。 &nbsp; 不过在 MySQL 中，使用长连接会有一定的副作用 —— 有些时候会导致 MySQL 占用内存涨得特别快。 这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源只有在连接断开的时候才会释放。 对于这个问题，通常有两种解决方案： 
 <blockquote> 
 A.定期断开长连接：使用一段时间，或者程序里判断执行过一个占用较大内存的查询后，主动断开连接； 
 &nbsp; 
 B.如果使用的是 MySQL 5.7 或者更高版本，可以在每次执行一个比较大的操作后，通过执行&nbsp;<code>mysql_reset_connection</code>&nbsp;来重新初始化连接资源。 
 </blockquote> 关于 MySQL 客户端与服务端的连接，以及连接状态和类型我们就简单介绍到这里. &nbsp; 下篇教程，我们更进一步，来探究一条 SQL 查询语句在 MySQL 客户端和服务端之间都经历了哪些曼妙之旅， 最后把查询结果返回给客户端的。 &nbsp; &nbsp; 后续补充 ... </td> 
 </tr> 
 </tbody> 
</table>

3.问题/补充

4.参考

<table> 
 <tbody> 
 <tr> 
 <td> <a href="https://xueyuanjun.com/post/21913" rel="nofollow">https://xueyuanjun.com/post/21913</a>&nbsp; //MySQL 数据库连接原理和性能优化 <a href="https://time.geekbang.org/column/article/102111" rel="nofollow">https://time.geekbang.org/column/article/102111</a>&nbsp; //05丨检索数据：你还在SELECT * 么？ </td> 
 </tr> 
 </tbody> 
</table>

后续补充

...