【MySQL】性能调优（一）：配置。连接配置优化

淩亂°似流年 2022-11-22 00:28 236阅读 0赞

我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的动作又是由很多个环节组成的，每个环节都会消耗时间，我们在[【MySQL】查询sql的执行过程及MySQL架构分析][MySQL_sql_MySQL]分析过了。我们要减少查询所消耗的时间，就要从每一个环节入手。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]

> MySQL 性能调优系列：
> 
> 1.  [【MySQL】性能调优（一）：配置。连接配置优化][MySQL]
> 2.  [【MySQL】性能调优（二）：表结构。建表及存储引擎几点建议][MySQL 1]
> 3.  [【MySQL】性能调优（三）：SQL。慢查询日志及SQL优化建议][MySQL_SQL_SQL]
> 4.  [【MySQL】性能调优（四）：SQL。EXPLAIN 执行计划详解][MySQL_SQL_EXPLAIN]
> 5.  [【MySQL】性能调优（五）：架构。集群及分库分表][MySQL 2]
> 6.  [【MySQL】性能调优（六）：业务层。减轻数据库压力][MySQL 3]

第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题？

## 配置优化 ##

在连接这个环节，有可能出现服务端连接数不够导致应用程序获取不到连接的问题。比如报了一个 Mysql: error1040: Too many connections 的错误。

我们可以从两个方面来解决连接数不够的问题：

### 1.服务端：配置连接数 ###

从服务端来说，我们可以增加服务端的可用连接数。如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：

*  修改配置参数增加可用连接数，修改max\_connections的大小：

show variables like 'max_connections'; -- 查看最大连接数（默认1000）
    set GLOBAL MAX_CONNECTIONS = 1200;-- 修改最大连接数（这是个全局变量），当有多个应用连接的时候

*  或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。

show global variables like 'wait_timeout'; -- 及时释放不活动的连接，注意不要释放连接池还在使用的连接

### 2.客户端：使用连接池 ###

从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行SQL都创建一个新的连接，应该怎么做？这个时候我们可以引入连接池，实现连接的重用。

**问题一**：我们可以在哪些层面使用连接池？

*  ORM 框架层面（MyBatis自带了一个连接池）
 *  或者使用专用的连接池工具（阿里的Druid、Spring Boot 2.x版本默认的连接池Hikari、老牌的DBCP和C3P0）

**问题二**：当客户端改成从连接池获取连接之后，连接池的大小应该怎么设置呢？

大家可能会有一个误解，觉得连接池的最大连接数越大越好，这样在高并发的情况下客户端可以获取的连接数更多，不需要排队。实际情况并不是这样。连接池并不是越大越好，只要维护一定数量大小的连接池，其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。Druid 的默认最大连接池大小是8。Hikari 的默认最大连接池大小是10。

**问题三**：为什么连接池默认值都是这么小呢？

在 Hikari 的[github文档][github]中，给出了一个PostgreSQL数据库建议的设置连接池大小的公式： 它的建议是机器核数乘以2加1。也就是说，4 核的机器，连接池维护9个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。这里面还有一个减少连接池大小实现提升并发度和吞吐量的案例。

**问题四**：为什么有的情况下，减少连接数反而会提升吞吐量呢？为什么建议设置的连接池大小要跟CPU的核数相关呢？

每一个连接，服务端都需要创建一个线程去处理它。连接数越多，服务端创建的线程数就会越多。

**问题五**：CPU是怎么同时执行远远超过它的核数大小的任务的？

时间片。上下文切换。而CPU的核数是有限的，频繁的上下文切换会造成比较大的性能开销。

> 我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括CPU、内存、磁盘、网络。
> 
>   
> 在不同的硬件环境下，操作系统和MySQL的参数的配置是不同的，没有标准的配置。在[前面的文章][Link 1]里也提到了很多MySQL和InnoDB的配置参数，包括各种开关和数值的配置，大多数参数都提供了一个默认值，比如默认的buffer\_pool\_size，默认的页大小，InnoDB并发线程数等等。这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义的情况下再去修改它。修改配置的工作一般由专业的DBA完成。
> 
>   
> 至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的CPU型号这些，更不是我们开发人员关注的重点，这个就不做过多的介绍了。如果想要了解一些特定的参数的含义，[官网][Link 2]有一份系统的参数列表可以参考。

除了合理设置服务端的连接数和客户端的连接池大小之外，我们还有哪些减少客户端跟数据库服务端的连接数的方案呢？我们可以引入缓存。

[MySQL_sql_MySQL]: https://blog.csdn.net/weixin_43935927/article/details/109324057
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/f5f54461ce0d49408b2cfbde0ca74a45.png
[MySQL]: https://blog.csdn.net/weixin_43935927/article/details/109468716
[MySQL 1]: https://blog.csdn.net/weixin_43935927/article/details/109490207
[MySQL_SQL_SQL]: https://blog.csdn.net/weixin_43935927/article/details/109472365
[MySQL_SQL_EXPLAIN]: https://blog.csdn.net/weixin_43935927/article/details/109489478
[MySQL 2]: https://blog.csdn.net/weixin_43935927/article/details/114002683
[MySQL 3]: https://blog.csdn.net/weixin_43935927/article/details/114005283
[github]: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
[Link 1]: https://blog.csdn.net/weixin_43935927/category_10513244.html
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html