我要评论前言
在实际项目开发中，很多业务场景下都需要考虑接口的性能要求，追求高并发、高吞吐量。那么对于此类问题如何入手呢？关注作者，不迷路。本节内容主要介绍在数据库db方面的优化，以mysql数据库为例。
关于db的优化，主要有以下几个方面的优化：
| db优化的层面 | 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数据库的优化配置，主要从数据库缓存池的大小设置、数据库最大连接数的设置、数据库表索引的设置着手。以上几个方面的优化配置能够明显提升数据库的性能。通过配合jemeter压测工具来观察优化的结果。本地服务器是14核40g，使用的数据库是mysql8.0。
正文
- 压测前准备
- 压测默认缓冲池大小128m，默认数据库连接数200，用户数据一条，应用的数据库连接池最小10，最大设置为50。并发数500、1000、5000、10000
| 并发数 | 吞吐率qps | 平均响应时间 |
|---|---|---|
| 500 | 4756.1/sec | 103ms |
| 1000 | 4374.9/sec | 224ms |
| 2000 | 4512.6/sec | 429ms |
| 5000 | 3925.9/sec | 1237ms |
- 压测默认缓冲池大小128m，默认数据库连接数200，用户数据100万，应用的数据库连接池最小10，最大设置为50。并发数500、1000、5000、10000
| 并发数 | 吞吐率qps | 平均响应时间 |
|---|---|---|
| 500 | 11.4/sec | 28157ms |
| 1000 | 11.4/sec | 36718ms |
| 2000 | 11.4/sec | 31768ms |
| 5000 | 11.4/sec | 33850ms |
- 优化一：将用户的查询字段增加索引，压测的默认配置保持不变。压测默认缓冲池大小128m，默认数据库连接数200，用户数据100万，应用的数据库连接池最小10，最大设置为50。并发数500、1000、5000、10000
| 并发数 | 吞吐率qps | 平均响应时间 |
|---|---|---|
| 500 | 4416/sec | 111ms |
| 1000 | 4671/sec | 210ms |
| 2000 | 4615/sec | 401ms |
| 5000 | 4204/sec | 1145ms |
- 优化二：增加数据库最大连接数以及应用的数据库连接池个数。将用户的查询字段索引去掉（尽量排除缓存干扰），压测的默认配置保持不变。压测默认缓冲池大小128m，默认数据库连接数改为1000，用户数据100万，应用的数据库连接池最小10，最大设置改为200。并发数500、1000、5000、10000
| 并发数 | 吞吐率qps | 平均响应时间 |
|---|---|---|
| 500 | 14.8/sec | 25159ms |
| 1000 | 17.4/sec | 28160ms |
| 2000 | 17.6/sec | 32575ms |
| 5000 | 43.2/sec | 7423ms |
- 优化三：在优化二的基础上增加数据库缓冲池的大小为10g。将用户的查询字段索引去掉（尽量排除缓存干扰），压测的默认配置保持不变。数据库连接数改为1000，用户数据100万，应用的数据库连接池最小10，最大设置改为200。并发数500、1000、5000、10000
| 并发数 | 吞吐率qps | 平均响应时间 |
|---|---|---|
| 500 | 13.9/sec | 23497ms |
| 1000 | 17.3/sec | 22212ms |
| 2000 | 17.3/sec | 20856ms |
| 5000 | 230/sec | 1196ms |
- 终极优化：数据库查询字段添加索引，缓冲池大小设置为10g,数据库连接数设置为1000，应用连接池个数设置为200，并发数500、1000、5000、10000压测
| 并发数 | 吞吐率qps | 平均响应时间 |
|---|---|---|
| 500 | 4455/sec | 109ms |
| 1000 | 4524/sec | 222ms |
| 2000 | 4463/sec | 421ms |
| 5000 | 4397/sec | 1170ms |
结语
通过压测实验，我们可以明确的一点是，数据库的性能瓶颈主要来源于数据库的io操作，有效的减少数据库的io操作次数是优化数据库访问性能的关键，有效的创建索引、增加缓冲池大小、以及设置一定比例的数据库连接，能够有效的减少数据库的io操作，从而提高数据库的访问性能。一般的建议，数据库的缓冲池大小innodb_buffer_pool_size设置为总内存的0.75，数据库最大连接数据设置为内存/12582080个。
发表评论