(一)高并发压力测试调优篇——MYSQL数据库的调优

(一)高并发压力测试调优篇——MYSQL数据库的调优

引用

CSDN

1.

https://blog.csdn.net/yprufeng/article/details/140347634

在高并发场景下，数据库性能优化是确保系统稳定运行的关键。本文通过详细的实验和数据，展示了如何通过查询优化、索引优化、配置优化等手段提升MySQL数据库的性能。实验结果表明，合理的索引设计和缓冲池配置能够显著提高数据库的吞吐量和响应速度。

前言

在实际项目开发中，很多业务场景下都需要考虑接口的性能要求，追求高并发、高吞吐量。那么对于此类问题如何入手呢？本节内容主要介绍在数据库db方面的优化，以mysql数据库为例。

关于db的优化，主要有以下几个方面的优化：

• 查询优化

• 使用EXPLAIN: 分析查询执行计划，找出瓶颈。

• 避免直接使用SELECT *: 明确指定需要的列，减少数据传输量。

• 使用JOIN替代子查询: JOIN通常比子查询更高效。

• 减少全表扫描: 尽可能使用索引。

• 优化子查询: 使用JOIN或存在子句（EXISTS）替换嵌套子查询。

• 避免使用LIKE 'prefix%': 这种形式的LIKE语句会导致全表扫描，除非前缀索引可用。

• 索引优化

• 创建合适的索引: 索引应基于查询中最常使用的列。

• 使用覆盖索引: 索引中包含所有需要查询的列，避免回表查询。

• 避免过多索引: 过多索引会增加写操作的成本。

• 定期分析和优化索引: 使用ANALYZE TABLE和OPTIMIZE TABLE命令。

• 配置优化

• 调整my.cnf/my.ini配置文件: 根据服务器硬件调整参数。

• InnoDB Buffer Pool: 设置为物理内存的60%-80%。

• Query Cache: MySQL 8.0已弃用，但在之前的版本中，合理配置可以提高性能。

• Max Connections: 设置合理的连接数，避免资源浪费。

• Thread Cache Size: 减少线程创建和销毁的开销。

• 存储引擎选择

• InnoDB: 支持事务，行级锁定，适合写密集型应用。

• MyISAM: 不支持事务，但读取速度快，适合读密集型应用。

• MEMORY: 用于临时表和小数据集的快速存储。

• 硬件优化

• 足够的CPU: 处理复杂的查询和并发请求。

• 充足的内存: 特别是用于InnoDB的Buffer Pool。

• 高速存储: SSD比HDD快得多，减少I/O延迟。

• 网络优化: 高速网络接口，减少网络延迟。

• 架构优化

• 读写分离: 主从复制，将读操作分散到从服务器。

• 分区: 将大表分割成更小的部分，提高查询效率。

• 集群: 使用MySQL Cluster或Galera Cluster提供高可用性和负载均衡。

• 定期维护

• 定期检查表: 使用CHECK TABLE命令检测表损坏。

• 定期优化表: 使用OPTIMIZE TABLE命令整理碎片。

• 定期备份: 避免数据丢失

• 监控与调整

• 性能监控: 使用slow query log，performance_schema等工具监控性能。

• 定期调整: 根据监控结果调整配置和策略。

• 应用层优化

• 减少不必要的查询: 缓存结果，避免重复查询。

• 优化应用程序代码: 减少数据库交互次数，使用批处理。

• 安全与合规

• 数据加密: 保护敏感数据。

• 权限管理: 最小权限原则，限制不必要的数据库访问。

本节内容主要针对单个MySQL数据库的优化配置，主要从数据库缓存池的大小设置、数据库最大连接数的设置、数据库表索引的设置着手。这些优化配置能够明显提升数据库的性能。通过配合JMeter压测工具来观察优化的结果。本地服务器是14核40G，使用的数据库是MySQL 8.0。

正文

压测前准备

1. 创建一个数据库，一张用户表tps_user

CREATE TABLE `tps_user` (
  `id` bigint NOT NULL COMMENT '主键',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='tps-用户表';

2. 使用Spring Boot工程创建一个压测接口，根据用户名查询用户信息

3. 数据库的默认参数配置

# 默认缓冲池大小： 128M
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
# 默认最大线程连接数： 200
SHOW VARIABLES LIKE 'max_connections';
# 活跃的连接数
SHOW STATUS LIKE 'Threads_connected';
# 查看所有的线程
SHOW PROCESSLIST;

基准测试

• 压测默认缓冲池大小128M，默认数据库连接数200，用户数据一条，应用的数据库连接池最小10，最大设置为50。并发数500、1000、5000、10000

结论：单条数据压测，吞吐量4000左右，不具有参考价值，因为一条数据，默认查询的是缓冲池的数据，查询会走数据库缓存，吞吐量会很高。同时我们也可以看到，随着并发数据的上升，吞吐量基本差别不大，但是响应时间明显会增大。

100万数据压测

• 压测默认缓冲池大小128M，默认数据库连接数200，用户数据100万，应用的数据库连接池最小10，最大设置为50。并发数500、1000、5000、10000
• 生成100万用户测试数据

结论：100万用户数据压测，吞吐量只有11左右，可以明显的看到吞吐量急剧下降，而且随着压测时间持续时间的增长，响应时间也在逐渐变大。随着并发数的增大，吞吐率和平均响应时间差别不大，数据基本不会走缓冲，基本都要经过数据库的IO读写操作。数据库此时遇到了瓶颈。

优化一：增加索引

• 将用户的查询字段增加索引，压测的默认配置保持不变。压测默认缓冲池大小128M，默认数据库连接数200，用户数据100万，应用的数据库连接池最小10，最大设置为50。并发数500、1000、5000、10000
• 增加查询字段的索引

结论：100万用户数据压测，增加索引后，吞吐量达到4500左右，与不创建索引天差地别，平均响应时间也变为毫秒级，且随着并发访问增大而平均响应时间也增大。为什么吞吐量会变大是因为创建索引后，数据不用全表逐行扫描，能够很快的定位数据的位置，减少数据库IO操作。如果缓存中存在索引数据，则可以完全不用IO操作，直接内存中查询；如果缓存中不存在，且是覆盖索引，则需要一次IO操作；如果缓存中不存在，且不是覆盖索引，则需要俩次IO,一次索引查询，一次用于回表读取数据行；如果是范围查询，则可能需要多次IO操作。由此可见，对于查询操作而言，对于合理的索引创建能够大幅度提高数据库的访问性能。

优化二：增加数据库连接数

• 增加数据库最大连接数以及应用的数据库连接池个数。将用户的查询字段索引去掉（尽量排除缓存干扰），压测的默认配置保持不变。压测默认缓冲池大小128M，默认数据库连接数改为1000，用户数据100万，应用的数据库连接池最小10，最大设置改为200。并发数500、1000、5000、10000

结论：100万用户数据压测，不加索引，只通过增加数据库连接数，从结果来看增大数据库连接数，以及数据库连接池的个数，并不能提升数据库的吞吐能力，而且随着并发的提高，出现了错误。

优化三：增加缓冲池大小

• 在优化二的基础上增加数据库缓冲池的大小为10G。将用户的查询字段索引去掉（尽量排除缓存干扰），压测的默认配置保持不变。数据库连接数改为1000，用户数据100万，应用的数据库连接池最小10，最大设置改为200。并发数500、1000、5000、10000

结论：100万用户数据压测，不加索引，只通过增加数据库连接数和缓冲池的大小。从结果来看增大数据库连接数、缓冲池大小以及数据库连接池的个数，并不能提升数据库的吞吐能力，而且随着并发的提高，出现了错误。这是因为数据库的查询操作还是会走大量的IO操作，导致吞吐量基本没什么变化。

终极优化：综合优化

• 数据库查询字段添加索引，缓冲池大小设置为10G,数据库连接数设置为1000，应用连接池个数设置为200，并发数500、1000、5000、10000压测

结论：100万用户数据压测，吞吐量能达到4000左右，响应时间基本1秒以内，随着并发请求增大，响应时间也变慢。

结语

通过压测实验，我们可以明确的一点是，数据库的性能瓶颈主要来源于数据库的IO操作，有效的减少数据库的IO操作次数是优化数据库访问性能的关键，有效的创建索引、增加缓冲池大小、以及设置一定比例的数据库连接，能够有效的减少数据库的IO操作，从而提高数据库的访问性能。一般的建议，数据库的缓冲池大小innodb_buffer_pool_size设置为总内存的0.75，数据库最大连接数据设置为内存/12582080个。