数据库性能测试中出现CPU飙升至100%、内存占满或磁盘IO接近短板，一般说明存在资源配置不当、慢SQL堆积或架构设计缺陷。

一、定位短板点在哪儿

先执行系统命令和数据库状态查询，避免盲目调参：

资源类型 观测标准 紧急命令/语句

CPU 高，us 用户态高 ，命令：top -H -p <pid> 找高负载线程；SHOW PROCESSLIST找运行中的长事务

内存高，物理内存耗尽、SWAP 使用，命令：free -h；查看innodb_buffer_pool_size 占比

磁盘IO高，iowait不断 > 30%，命令：iostat -x 1 观察await和 %util

连接数爆满，Threads_connected 触顶，命令：SHOW VARIABLES LIKE 'max_connections'；SHOW STATUS LIKE 'Threads%'

第一步止损动作：如果因慢SQL堵塞导致连接池满，执行KILL <thread_id> 杀掉阻塞源头，优先恢复服务。

二、SQL索引层优化

测试环境往往因为数据量突然增大而暴露全表扫描。

1. 抓取慢查询日志分析

临时开启慢日志（不影响生产镜像测试）：

sql

SET GLOBAL slow_query_log = ON;

SET GLOBAL long_query_time = 0.1;  -- 压测期间设置极低阈值

使用 pt-query-digest 或 mysqldumpslow 找出执行次数最多、扫描行数最大的 SQL。

2. 索引法则

避免回表：高频查询尽量使用包括索引（Using index）。

消灭隐式转换：WHERE varchar_col = 123 会导致索引失效，CPU飙升。

联合索引顺序：按照等值在前、范围在后的原则，如 (status, create_time) 不能写成 (create_time, status)。

3. 大事务和锁冲突

压测脚本中的FOR UPDATE或频繁写同一行（热点行更新）会导致CPU空转和锁等待。建议：将大 UPDATE/DELETE 拆分为每次 1000 行的批处理，中间 sleep(0.1) 释放锁。

三、MySQL参数调优针对测试环境配置过低

测试环境一般是生产环境的缩配版，默认参数值偏大导致内存溢出或刷盘频繁。

1. InnoDB 缓冲池（内存过高/过低的重点）

过高OOM：innodb_buffer_pool_size 超过物理内存 75%，且未留够系统层内存，导致SWAP抖动，CPU瞬间打满。

调整公式：innodb_buffer_pool_size = 总物理内存 * 60%（如果同机部署应用，需降至 50%）。

2. 日志刷盘方法（降低磁盘 IO 压力）

测试环境允许少量数据丢失，以换取性能：

sql

SET GLOBAL innodb_flush_log_at_trx_commit = 2;      -- 每秒刷盘，非实时

SET GLOBAL sync_binlog = 0;                         -- 交给 OS 异步刷盘

注意：测试结束后必须恢复为双1配置。

3. 临时表空间配置

压测中如果频繁出现Using temporary且磁盘IO高，增大内存临时表容量：

sql

SET GLOBAL tmp_table_size = 64M;

SET GLOBAL max_heap_table_size = 64M;

四、架构和测试方法调整

如果优化后资源占用仍高于 90%，需考虑硬扩展或测试方法降级。

1. 读写分离和缓存前置

将压测流量中的纯查询接口指向只读从库。

对于热点商品信息、配置类数据，开启 Redis 缓存，禁止SQL透过。

2. 连接池瘦身

测试工具的JDBC连接池（如 HikariCP maximumPoolSize）设置过大（超过 200），数据库维护线程上下文切换开销极大。建议压测并发线程数 : 数据库连接数 = 10 : 1。

3. 测试数据清理和预热

表数据量太大（上亿）但无对应分区表，即使走索引 CPU 也会高。建议：对时间流水表按月分区。

每次压测前执行 ANALYZE TABLE 更新统计信息，避免选错索引。

遇到数据库资源占用过高，依次排查 SQL 执行计划（Explain）→ 锁等待（InnoDB Status）→ 内存参数 → 磁盘 IO 压力，绝大多数性能短板都能在前两步解决。