性能测试 —— 数据库的连接池和主从同步和分表分区

本文链接：https://blog.csdn.net/lzf_hlh/article/details/148830165

一、数据库的调优（库层面）

1、数据库连接池

1、介绍：数据库连接池(Database Connection Pool)是一种用于管理数据库连接的技术，它通过预先创建并维护一组数据库连接来提高应用程序的性能和可扩展性。

2、创建、管理、关闭数据库连接池

创建：般在系统初始化时，连接池会根据系统配置建立，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。
管理：是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其管理策略如下：
1. 客户请求数据库连接时，首先查看连接池中是否有空闲连接
  
  * 如果存在空闲连接，则将连接分配给客户使用；
  
  * 如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，
  
  - 如果没达到就重新创建一个连接给请求的客户；
  
  - 如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户
2. 当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。
3. 该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。
关闭：当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反。

2、线程池的特点

1、有无数据库连接池的对比

有连接池
一个请求建立了连接完成查询后不会释放掉，而是进入连接池保存；比如连接池就让其存活5条连接，当一个请求过来了，先去连接池里查看一下是否有空闲的连接，有的话就直接复用这个连接然后去进行查询操作；用完后也不释放，直接放回到连接池里作为空闲的状态等待其他的请求来占用。这样就避免了频繁的建立和释放连接带来的性能损耗。
无连接池

一个数据的查询请求需要建立一个数据库的连接，连接建立成功后才会执行查询操作并返回查询结果后释放这个连接；这个不断地建立连接和释放连接的过程，对数据库的性能损耗就比较大，所以就引入了连接池的概念

总结：这样之后数据库的连接都是通过连接池进行管理的，对数据库的连接数量在入口处就做了一个限制，不是用户来控制其大小的，而是由系统自己来控制的，这样就起到一个对后端数据库服务器的很好的保护作用。所以基本上连接后端数据库服务都是通过连接池控制的。

2、数据库建立连接的过程

2、不使用数据库连接池的步骤 VS 使用连接池的步骤：

不使用数据库连接池

1、 TCP建立连接的三次握手

2、MySQL认证的三次握手

3、真正的SQL执行

4、MySQL的关闭

5、TCP的四次握手关闭

可以看到这是开销比较大的

使用数据库连接池
- 第一次访问的时候需要建立连接，但是之后的访问，均会复用之前创建的连接，直接执行SQL语句

3、不使用连接池 VS 使用连接池的优缺点

不使用数据库连接池的缺点：
- 网络IO较多
- 数据库的负载较高
- 响应时间较长及QPS较低
- 应用频繁的创建连接和关闭连接，导致临时对象较多，GC频繁
使用数据库连接池的优点：
- 减少了网络开销
- 系统的性能会有一个实质的提升：包括降低RT，提高QPS和TPS，降低负载等
- 减少了频繁的创建连接和关闭连接的开销，减少了内存的消耗

3、连接池的主要参数

使用连接池时，要配置以下常见的参数：

初试连接数（initialSize)：连接池启动时创建的初始化连接数量
最大连接数（maxActive）：连接池能申请的最大连接数,如果数据库连接请求超过次数,后面的数据库连接请求将被加入到等待队列中
最大空闲连接数（maxIdle）：连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制最大空闲时间
最小空闲连接数（minIdle）：连接池中最小的空闲的连接数，低于这个数量会创建新的连接。该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的;但是不能太大。
maxWait :最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待

4、MySQL连接池的参数查看

1、Mysql数据库的配置参数获取，可以通过执行sql: “show variables”：

连接池相关的参数主要包括max_connections、max_user_connections、wait_timeout和interactive_timeout。
- max_connections表示MySQL服务器所能支持的最大并发连接数
- max_user_connections表示每个用户能够同时建立的最大连接数
- wait_timeout表示连接在空闲状态多长时间后被自动关闭
- interactive_timeout表示连接在非空闲状态多长时间后被自动关闭

2、其中一个参数 max_connections就是MySQL的最大连接数，是我们最需要关注的：

可以过'show variables like "%conn%"'通配符查看当前状态的连接数量的大小；
show global status like 'max_used_connections'; 查看目前使用的连接数量的大小；
一般会使用默认的连接数做性能测试，如果出现了问题再调高验证。
如果服务器的并发连接请求量比较大，建议调高此值以增加并行连接数量，当然这建立在机器能支撑的情况下，因为连接数越多就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

3、数据库连接不够用的错误提示（在压测的返回结果中查看）

出现5xx系列的错误
mysql：Error preloading the connection pool
Cannot create PoolableConnectionFactory
ERROR 1040: Too many connections

5、实战演练

1、设置最大连接数为10，使用命令：set global max_connections=10。是临时调整的，如果数据付服务重启那么会恢复默认的数值

2、使用jmeter发起压测

3、针对这个性能场景进行分析

1、阶梯压测的时候，前面不报错，但是TPS没有随着并发用户增加而增加，而且此时CPU很高

2、结合top命令查看资源情况：

CPU的表现： cpu占用率 si会很高，软中断很高，说明再被迫等待中断了操作；
结合上一步，如果等待时间很长，但不报错，也会导致TPS上不去【没有随着并发用户数增加而增加TPS，甚至会出现下降】，然后不报错。这个也有可能是连接池不够的问题引起的

3、结合vmstat命令查看

r很多，等待运行的进程数，高 r 值可能意味着系统正忙于处理其他任务，无法及时响应新的请求。就是在等待连接池释放。
并且cs 和in也有比较多的数据：说明中断和上下文切换有发生

此时再继续增加用户数，那么就会报错了： too many connection，那么再查看一下数据库的最大连接数，如下，可以看出远大于10了，因此会报错

4、针对这个性能场景的优化提升：

1、数据库配置连接数不够，我们就需要适当的调整数据库连接池的大小：改大最大连接数

修改方法：通过SQL修改max_connections值 set global max_connections=256;
- 【最好根据项目咨询开发+ DBA，并且多次压测的验证取一个恰当的值：不报错连接池，内存不会消耗太多】
注意：这种是临时修改, 数据库重启后就会失效，修改配置文件里的配置并重启服务器可以永久生效：
- docker cp centos7_mysql57:/etc/mysql/mysql.conf.d/mysqld.cnf $PWD #从容器里拉取到本地
- vi修改这个文件： max_connections = 256
- docker cp mysqld.cnf centos7_mysql57:/etc/mysql/mysql.conf.d/mysqld.cnf #从本地推送到容器里替换原来的配
sql设置临时值可以先用来性能调试，发现可以解决性能问题【寻找到的一个恰当的值】，再修改配置文件并重启永久生效。

2、修改完后再运行脚本，检查是否依然报错

不存在其他的性能瓶颈：数据库连接池调整大了之后，TPS应该是可以上去的，不会再报错：Too many connections。

如果不是docker数据库，而是直接安装的数据库：编辑MySQL配置文件（通常是my.cnf或my.ini）。

* 在文件中找到名为"max_connections"的参数。

* 修改该参数的值为所需的连接池大小。

* 保存并关闭配置文件。

* 重启MySQL服务，使更改生效。

总结：

max_connections 修改这个参数是控制数据库的连接池大小的，但是数据库的最大连接池数量不可无限大。【太大会消耗内存资源取平衡值】
如果因为连接数不够，一般请求下会出现5xxx系列以及 “too many connection” 报错信息 + TPS上不去 + CPU比较高【si】的+RT比较高；
在性能测试时，看到上面这样的错误，基本就确定是数据库连接数不够用。通过适当的修改数据库的连接池做一定的优化

衍生问题：TPS上不去的问题原因总结：【常见的面试题：TPS上不去的原因+怎么优化瓶颈】

1）客户端瓶颈：单机压力上不去【端口不够用】，需要用分布式解决；

2）网络瓶颈：网络带宽不够；优化网络带宽和网卡，减少网络传输的数据量和大小

3）CPU资源不足：增加CPU或者优化代码逻辑

4）内存资源不足：增大内存和优化代码

5）JVM内存不足或内存溢出：增加堆内存大小，优化代码逻辑

6）线程池不够：适当增加线程池

7）数据库瓶颈：慢查询的问题，数据库连接池不够，优化索引和SQL，以及增加数据库连接数

二、数据库的工作原理

1、数据库的组成

数据库的组成部分：连接层，服务层，引擎层，存储层；每一层负责的职责如图所示：

连接层：连接数据库的类型，IP、端口、账号密码等，建立数据库的连接，提供数据库的连接池。

服务层：写SQL脚本的, 判断客户端传过来的数据信息，做权限判断、脚本检查、脚本解析和脚本优化

引擎层：把sql语句转化为数据库可执行的内容；并会查一下是否命中缓存，是的话就不需要你从存储层拿数据了，性能效率会更高；

存储层: 真正的执行是在磁盘上操作的，所以需要存储层驱动磁盘操作，比如磁盘的数据读写和数据变更等。存储层的问题会体现出来IO很高，SQL语句大量读取磁盘。

性能测试工程师能做的数据库层面的性能调优；其他由研发来做

三、数据库主从同步【读写分离】

1、主从同步的概念 - 数据库集群

如果业务数据库只有一个单台数据库服务器，故障了就直接项目业务瘫痪了，这样就会造成非常大的损失，特别是对那些业务比较繁忙的系统，中间的损失都是以s级别来计算的。为了避免这样的问题，很多公司都会在数据库这一层做主从同步。

主从同步

新增1-多个节点去做数据库的备份，主节点主要对外提供服务，但是从节点会自动同步主节点的数据。只要主节点有数据变更，不管是增删改，都会及时同步到从节点数据。
如果下次主节点挂了，就会自动切换到从节点上，如果就算有损失的话也是1s左右；而且有些公司做的好的话可以做到无感的，立马切换不会对用户有任何损失。
所以，主从同步其实是一个数据库的集群概念，在项目环境部署的时候是非常有必要的。数据至少会有一份冗余和备份，这样提高了容灾性和可靠性

很多公司现在用的服务器都是阿里云这种云服务器，所以主从同步、数据快照等的工作可以托管给云平台做，节省部署和维护的成本；自己搭建的话需要考虑切换的时候数据丢失和业务中断不可用的等风险和成本。

2、主从同步的原理

日志同步和再次执行日志SQL脚本来实现同步的过程

master将改变【update delete insert等】记录到二进制日志中【binary log -blog】，可以理解为sql语句
slaver将master的binary log events拷贝到它的中继日志（relay log）
salver重做中继日志中的事件（events），将改变反映它自己的数据；重执行sql语句,同步操作数据

注意：容易出现的问题：因为网络因素+服务器配置+mysql配置问题等引起的主从同步延时，就会发生从节点的数据跟业务数据不一致性。

2、读写分离 - 提高数据库的读性能

因为数据库的操作主要就是读和写，所以就是希望读的性能不要影响写的性能，写的性能也不要影响读的性能，会做用两台机器来把读和写分开做。一台机器专门用来读数据，一台机器专门用来写数据。这种就叫做读写分离。

写的机器数据有变更，立马会同步到读的机器上，所以对数据同步及时性要求高，不能有延迟太高【2-3s就挺高的】。所以，机器要降低延迟。
因为服务器的功能不一样，所以针对不同的业务操作的服务器的配置也不一样，可以合理分配资源，节约成本。
- 比如写的操作业务特别多，那么写的节点服务器就配置好一些；
- 如果是读取数据非常多的业务，那么就读的机器配置高一些，写的配置低一些，比如磁盘弄大些即可。
读写分离一般会搭配主从同步一起做。

问题1：读数据应是主数据库还是从数据库？

主从数据库的数据流向：只能从主 >> 从，不能返过来；如果要对数据库的数据变更只能变更主数据库，然后同步到从数据库；
所以写操作是主数据库，读操作是从数据库。主数据库的数据变更了需要及时同步到从数据库，不然读取数据会有差异。【导致数据不一致引起的bug】
因此从数据库读数据操作要远远高于数据变更操作。-- 需要磁盘比较大加快读数据能力；

问题2：主从数据库的备份对于性能有什么价值？

单独使用了不同硬盘的读IO性能和写io的性能：因为不同的硬盘单独做读操作或者写操作，这样磁盘性能要比同一个磁盘同时做读写要好一些，所以读写分离对项目性能的提升是非常明显的。
而且还可以使用读IO性能好一些的做从数据库，写IO性能好一些的做主数据库，合理分配资源。
不过会增加公司设备资源投入，一定程度上增加成本，所以要综合性能情况合理分配。【如果项目的数据量不是很大其实没有必要】

问题3：有了主从同步数据库，就实现了读写分离了吗？

不是的。需要配合代码实现，在代码里进行数据库数据变更时使用主数据库；
代码中要获取数据时，可以从主/从两个数据库中来获取，当然一般我们从从数据库中读取数据。

3、主从数据库的实战

1、创建2个数据库

docker run -itd --name mysql-1 -p 4406:3306 -e MYSQL_ROOT_PASSWORD=123456789 daocloud.io/mysql:5.7

docker run -itd --name mysql-2 -p 4506:3306 -e MYSQL_ROOT_PASSWORD=123456789 daocloud.io/mysql:5.7

2、配置主从数据库：把数据库的id设置一下：id小的会成为主数据， id大的会成为从数据库。

1、docker cp mysql-1:/etc/mysql/mysql.conf.d/mysqld.cnf $PWD # 容器的把数据库的配置文件拉取获取到本地虚拟机【因为容器里不方便编辑，没有vi/vim命令】

- $PWD ：宿主机器的当前路径

2、vim mysqld.cnf # 编辑文件

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

server-id =100

log-bin =mysql-bin

#log-error = /var/log/mysql/error.log

# By default we only accept connections from localhost

#bind-address = 127.0.0.1

# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0

3、拷贝到第一个dokcer容器里：docker cp mysqld.cnf mysql-1:/etc/mysql/mysql.conf.d/mysqld.cnf #第一个容器就成

-----------------------以上已经完成mysql-1主数据库的配置，开始配置从数据库-------------------------

4、vim mysqld.cnf # 编辑文件

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

server-id =101

log-bin =mysql-bin

#log-error = /var/log/mysql/error.log

# By default we only accept connections from localhost

#bind-address = 127.0.0.1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

5、拷贝到第二个dokcer容器里：docker cp mysqld.cnf mysql-2:/etc/mysql/mysql.conf.d/mysqld.cnf # 第二个容器就成为了从数据库

6、重启两个数据库的容器： docker restart mysql-1 mysql-2 【重新加载数据库的配置文件】

为了主数据库

3、远程连接工具navicat可以连上这两个数据库。在从数据库中，配置主数据库信息，这样从数据库才能和主数据库进行数据同步。

- 复制以下内容到navicat的sql执行：【如果有多个从数据库都可以配置主数据库】

CHANGE MASTER TO

MASTER_HOST='192.168.61.156', -- 宿主机ip 【这个地址要改成你们自己的虚拟机地址】

MASTER_PORT=4406, -- mysql-master映射到宿主机的端口

MASTER_USER='root',

MASTER_PASSWORD='123456789';

START SLAVE; -- 启动从数据库

· show slave status; --检查主从之间通信是否正常，结果检查以下2个配置：

Slave_IO_Running 和 Slave_SQL_Running 两个都要是 Yes的状态：
Slave_SQL_Running 为Yes，日志文件才会被回放执行，才能运行SQL

4、完成以上的配置以后，主从配置就完成了！然后只要在主数据库中做任何变动，从数据库中都会完整的同步：

主数据库里添加一个数据库，从数据库刷新就会同步过来
主数据库里一个表里添加一个字段和索引，从数据库刷新就会同步过来
主数据库的增删改都是可以直接同步到从数据库的；【注意：从数据库的变更不会同步到主数据库的】

四、数据库的分表分区

1、分表分库

单表和库数据量太大的时候，就需要查分表和库。拆分方法有几种：

业务拆分：比如电商项目有用户模块商品模块，订单模块等，分别放到不同的数据库或者表；【如果分成不同表可以实现，那么就不用分库；否则分库】

垂直拆分：是指对原始表的列进行拆分，根据一定的拆分规则把一张表的列拆分成多张表。如：把原始表中不常用的列拆分到一张表，把一些存储大数据量的字符拆分到一张表，把经常一起使用的列拆分到一张表，......等等。这样拆分的多张表，每张表都可以有自己的存储引擎、索引等，使用灵活，性能也可以得到提升。
- 表中很多列，不同列组合成为一张新的表：表字段变少，行数不变
- 原来的表在磁盘上的文件变化。文件变小了，加快了数据的操作速度。
水平拆分【一致性哈希算法】：最容易理解的是指对原始表的行进行拆分，根据一定的拆分规则，把原先存在一张表的数据，分表存储到多张表中。如根据id取模，hash值、按日期月份...等等。原本存储在一张表中数据，被分开存储在不同的表中，大大降低了单表的数据量
- 比如按照日期拆分： 2025年第一季度数据存到一张表，第二季度的数据第二张表.......
- 比如根据userid取模，分成3个表就userid% 3 的结果就是在第几张表里。
- 根据行来进行拆分，一张表某些行，被拆到另外表，表字段不变，行数变少; 表在磁盘上的存储的文件行业是变小的，操作数据变快的