MySQL性能调优：参数调优、连接池、缓冲区与压测
作者：黒漂技术佬
适用读者：MySQL配置都默认值、慢查询改了SQL但还是慢的同学
关联场景：售货柜高峰期数据库卡顿排查、工控历史数据查询优化
一、性能调优的四个层次
新手调优只会改SQL，老手知道从四个层次下手，从上到下投入产出比递减：
层次1：SQL层 → 优化慢SQL、加索引、避免SELECT * （收益最大） 层次2：参数层 → 调InnoDB缓冲池、连接数、刷盘策略 层次3：架构层 → 读写分离、分库分表、加缓存 层次4：硬件层 → 换SSD、加内存、升级CPU黄金法则：先改SQL，再调参数，再上架构，最后烧钱换硬件。直接跳到硬件层是耍流氓——配置都没调好就花钱，钱白花。
二、核心参数调优
2.1 innodb_buffer_pool_size：缓冲池（最重要）
InnoDB缓冲池是内存里缓存数据页和索引页的区域。所有读写都要先经过缓冲池——这是MySQL性能的生命线。
查询流程： SELECT * FROM product WHERE id=1001 1. 先查缓冲池有没有id=1001的数据页 2. 有 → 直接返回（内存操作，快） 3. 没有 → 从磁盘读数据页到缓冲池，再返回（慢）调优建议：
# 单机MySQL专用服务器，缓冲池设物理内存的70%~80% [mysqld] innodb_buffer_pool_size = 8G # 假设机器16G内存 # 多实例机器，缓冲池设内存的50% innodb_buffer_pool_size = 4G # 16G内存跑两个实例为什么是70%？剩下30%要留给操作系统、连接线程、临时表、查询缓存等。设太大可能触发OOM。
缓冲池命中率监控：
-- 查看缓冲池状态SHOWENGINEINNODBSTATUS\G-- 关注：-- Buffer pool hit rate: 999 / 1000 （99.9%，很好）-- 低于95%就要考虑加内存或优化查询-- 也可以从information_schema看SELECT(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100AShit_rate_pctFROMinformation_schema.innodb_buffer_pool_stats;经验：缓冲池命中率低于99%就该警觉。售货柜高峰期如果命中率从99.9%掉到95%，要么是缓冲池太小，要么是有大表全表扫描把热数据挤走了。
2.2 innodb_log_file_size：redo log大小
redo log是WAL机制的核心，事务提交先写redo log。redo log文件太小会频繁切换，影响性能。
[mysqld] # MySQL 5.7+合并配置 innodb_redo_log_capacity = 2G # 总容量2G（8.0.30+） # 旧版本： # innodb_log_file_size = 1G # innodb_log_files_in_group = 2怎么判断大小合适：
看redo log刷盘频率： - 每秒刷好几次 → 太小，加大 - 几十分钟刷一次 → 太大，可以减小释放磁盘 - 几分钟刷一次 → 合适SHOWENGINEINNODBSTATUS\G-- 关注Log sequence number和Log flushed up to的差值-- 差值大说明redo log写入快于刷盘2.3 max_connections：最大连接数
[mysqld] max_connections = 500 # 默认151，生产太小怎么估算：
售货柜场景： - 5000台柜子，每台平均2秒一个查询 - 瞬时并发 = 5000 / 2 = 2500 QPS - 单个查询耗时10ms → 并发连接 = 2500 × 0.01 = 25 加业务峰值系数3 → 75连接够用 留余量 → max_connections=200~500注意：max_connections不是越大越好。每个连接要占内存（默认线程栈256KB~1MB），5000连接就吃几个G内存。应用侧配合连接池控制才是正解。
2.4 innodb_flush_log_at_trx_commit：刷盘策略
控制redo log什么时候刷到磁盘。这是性能vs安全的开关。
[mysqld] innodb_flush_log_at_trx_commit = 1| 值 | 行为 | 性能 | 安全 |
|---|---|---|---|
| 0 | 每秒刷盘，事务提交不等刷盘 | 最高 | 可能丢1秒数据 |
| 1 | 每次提交都刷盘 | 最低 | 不丢数据（默认） |
| 2 | 每次提交写OS Cache，每秒刷盘 | 中 | OS崩溃可能丢1秒 |
选型： - 售货柜订单（钱相关）→ 用1，不能丢 - 售货柜操作日志（不关键）→ 用2，性能换少量风险 - 传感器温度上报（高频但不重要）→ 用0关键参数还有个
sync_binlog，控制binlog刷盘。和innodb_flush_log_at_trx_commit合称"双1"：innodb_flush_log_at_trx_commit = 1 sync_binlog = 1双1是金融级配置。非关键场景可以适当放宽换性能。
2.5 其他常用参数
[mysqld] # IO线程数（SSD可以调大） innodb_read_io_threads = 8 innodb_write_io_threads = 8 # 并发线程数（0=不限制） innodb_thread_concurrency = 0 # 脏页刷盘比例（75%开始积极刷） innodb_max_dirty_pages_pct = 75 # 临时表大小 tmp_table_size = 256M max_heap_table_size = 256M # 排序缓冲（每个连接） sort_buffer_size = 4M # 死锁检测（高并发热点写可关） innodb_deadlock_detect = ON三、连接池配置：HikariCP和Druid
应用和MySQL之间必须有连接池，避免每次请求都建连接。连接池配置不当，要么连接不够用，要么连接太多打爆MySQL。
3.1 HikariCP配置
spring:datasource:hikari:maximum-pool-size:20# 最大连接数minimum-idle:10# 最小空闲连接connection-timeout:30000# 获取连接超时30秒max-lifetime:1800000# 连接最长存活30分钟idle-timeout:600000# 空闲10分钟回收leak-detection-threshold:60000# 连接泄漏检测60秒最大连接数估算公式：
参考公式：connections = (2 * core_count * effective_utilization) 或更保守：core_count * 2 + 磁盘数 实际靠压测调整： - CPU打满 → 减少连接数（减少线程切换开销） - IO等待多 → 增加连接数（让CPU等IO时有活干）错误示范：看到慢就把maximum-pool-size开到100。连接太多会导致MySQL线程切换开销激增，反而更慢。HikariCP作者建议：小而精，从10~20开始。
3.2 Druid配置
国内用得多的连接池，有监控和SQL防火墙功能。
spring:datasource:druid:initial-size:5min-idle:5max-active:20max-wait:60000# 获取连接超时time-between-eviction-runs-millis:60000# 检测间隔min-evictable-idle-time-millis:300000# 最小空闲时间validation-query:SELECT 1test-while-idle:true# 空闲时检测test-on-borrow:false# 借出时不检测（性能）test-on-return:falsefilters:stat,wall# 开启统计和SQL防火墙// Druid监控页：/druid/index.html// 能看到：// - 慢SQL列表// - SQL执行次数统计// - 连接池活跃/空闲数// - SQL防火墙拦截记录Druid的
test-while-idle=true+test-on-borrow=false是黄金组合：空闲时检测保活，借出时不检测省开销。
四、慢查询监控和优化流程
4.1 开启慢查询日志
[mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 0.5 # 超过0.5秒记录 log_queries_not_using_indexes = ON # 未用索引的也记4.2 分析慢查询
# 用mysqldumpslow汇总分析mysqldumpslow-st-t10/var/log/mysql/slow.log# -s t 按总时间排序# -t 10 取前10条输出示例： Count: 1500 Time=2.5s (3750s) Lock=0.0s (0s) Rows=1000.0 (1500000) SELECT * FROM orders WHERE create_time > '2024-01-01' AND status='PAID' 解读： - 执行1500次，每次2.5秒，总耗时3750秒 - 每次返回1000行 - 这是优化重点4.3 优化流程
慢查询优化五步法： 1. EXPLAIN看执行计划 EXPLAIN SELECT ... \G 关注 type、key、rows、Extra 2. 看有没有走索引 type=ALL 全表扫描 → 必须加索引 type=ref/range 走索引 → OK 3. 看索引是否合理 key=NULL → 没用索引 key有值但rows很大 → 索引区分度低 4. 看Extra有没有"坏词" Using filesort → 文件排序，要优化ORDER BY Using temporary → 临时表，要优化GROUP BY Using index → 覆盖索引，很好 5. 改SQL或加索引 - 加合适索引 - 避免 SELECT * - 拆分大SQL - 改写为等价高效写法-- 看执行计划EXPLAINSELECT*FROMordersWHEREcreate_time>'2024-01-01'ANDstatus='PAID';-- 优化：加复合索引ALTERTABLEordersADDINDEXidx_time_status(create_time,status);-- 再看执行计划EXPLAINSELECT*FROMordersWHEREcreate_time>'2024-01-01'ANDstatus='PAID';-- type=range, key=idx_time_status, rows大幅下降流程化的关键：定期（每周）跑慢查询分析，而不是等用户报障才查。售货柜高峰期的慢SQL，平时就埋着，高峰一压才暴露。
五、MySQL监控指标
5.1 四个核心指标
| 指标 | 含义 | 告警阈值 |
|---|---|---|
| QPS | 每秒查询数 | 看基线，突增突降都查 |
| TPS | 每秒事务数 | 看基线 |
| 连接数 | 活跃连接/总连接 | 活跃>80%总连接告警 |
| 缓冲池命中率 | 数据页缓存命中比 | <95%告警 |
5.2 查询命令
-- 查看当前连接数SHOWSTATUSLIKE'Threads%';-- Threads_connected: 当前连接数-- Threads_running: 活跃执行中的线程数-- 查看QPS和TPS（需要算差值）SHOWGLOBALSTATUSLIKE'Questions';SHOWGLOBALSTATUSLIKE'Com_commit';SHOWGLOBALSTATUSLIKE'Com_rollback';-- QPS = (Questions_后 - Questions_前) / 时间间隔-- TPS = (Com_commit + Com_rollback的差值) / 时间间隔-- 缓冲池命中率SHOWSTATUSLIKE'Innodb_buffer_pool_read%';-- Innodb_buffer_pool_reads: 物理磁盘读次数-- Innodb_buffer_pool_read_requests: 总读请求-- 命中率 = 1 - reads/requests5.3 监控工具
常用监控栈： 1. Prometheus + mysqld_exporter + Grafana - 开源免费，指标全 - 配置告警规则 2. PMM（Percona Monitoring and Management） - Percona官方，MySQL专版 - 开箱即用 3. 阿里云/腾讯云RDS自带监控 - 云数据库用云监控Grafana看板核心图表：QPS/TPS趋势、慢查询数、连接数、缓冲池命中率、复制延迟。这五张图能覆盖80%的MySQL健康度。
六、性能压测工具：sysbench
6.1 安装
# CentOSyuminstallsysbench# Ubuntuaptinstallsysbench6.2 准备数据
# 准备测试数据，10张表，每表100万行sysbench /usr/share/sysbench/oltp_read_write.lua\--mysql-host=127.0.0.1\--mysql-port=3306\--mysql-user=root\--mysql-password=xxx\--mysql-db=test\--tables=10\--table-size=1000000\prepare6.3 压测
# 读写在混压测，64并发，60秒sysbench /usr/share/sysbench/oltp_read_write.lua\--mysql-host=127.0.0.1\--mysql-port=3306\--mysql-user=root\--mysql-password=xxx\--mysql-db=test\--tables=10\--table-size=1000000\--threads=64\--time=60\--report-interval=10\run6.4 结果解读
SQL statistics: queries performed: read: 1854234 → 总读次数 write: 530048 → 总写次数 other: 265024 → 其他（COMMIT等） total: 2649306 → 总查询 transactions: 132512 (2208.39 per sec.) → TPS queries: 2649306 (44168.45 per sec.) → QPS ignored errors: 0 reconnects: 0 Throughput: events/s (eps): 2208.39 → 每秒事务 Latency (ms): min: 2.34 → 最小延迟 avg: 28.98 → 平均延迟 max: 142.50 → 最大延迟 95th percentile: 65.30 → 95分位延迟关键指标：
- TPS：每秒事务数，越高越好
- QPS：每秒查询数
- 95th percentile：95%请求的延迟，比平均值更能反映体验。P95<100ms算及格，<50ms算优秀
压测场景： 1. 调参前压一次，记录基线 2. 调一个参数（比如buffer_pool_size） 3. 压测对比，看TPS/P95变化 4. 有效则保留，无效则回滚 例： - buffer_pool从2G→8G - TPS从1500→2500（+67%） - P95从80ms→35ms（-56%） → 这个调优有效，保留压测核心原则：一次只改一个变量。同时改多个参数，无法判断哪个起的作用。改完压一次，对比基线，再决定保不保留。
七、总结
| 概念 | 一句话 |
|---|---|
| 调优层次 | SQL层→参数层→架构层→硬件层，从上到下 |
| innodb_buffer_pool_size | 单机设物理内存70%，最重要的参数 |
| innodb_flush_log_at_trx_commit | 1=最安全，0/2=换性能 |
| max_connections | 按并发估算，不是越大越好 |
| 连接池 | HikariCP小而精10~20，Druid带监控 |
| 慢查询流程 | 开慢日志→EXPLAIN→加索引→改写SQL |
| 监控指标 | QPS/TPS/连接数/缓冲池命中率 |
| sysbench | 标准压测工具，调参前压基线对比 |
性能调优是持续工程，不是一次性活。监控→发现慢点→改SQL/调参数→压测验证→上线观察，这个循环要常态化。下一篇整理项目高频问题，把前面学的串起来。
