MySQL性能调优参数调优、连接池、缓冲区与压测作者黒漂技术佬适用读者MySQL配置都默认值、慢查询改了SQL但还是慢的同学关联场景售货柜高峰期数据库卡顿排查、工控历史数据查询优化一、性能调优的四个层次新手调优只会改SQL老手知道从四个层次下手从上到下投入产出比递减层次1SQL层 → 优化慢SQL、加索引、避免SELECT * 收益最大 层次2参数层 → 调InnoDB缓冲池、连接数、刷盘策略 层次3架构层 → 读写分离、分库分表、加缓存 层次4硬件层 → 换SSD、加内存、升级CPU黄金法则先改SQL再调参数再上架构最后烧钱换硬件。直接跳到硬件层是耍流氓——配置都没调好就花钱钱白花。二、核心参数调优2.1 innodb_buffer_pool_size缓冲池最重要InnoDB缓冲池是内存里缓存数据页和索引页的区域。所有读写都要先经过缓冲池——这是MySQL性能的生命线。查询流程 SELECT * FROM product WHERE id1001 1. 先查缓冲池有没有id1001的数据页 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_sizeredo log大小redo log是WAL机制的核心事务提交先写redo log。redo log文件太小会频繁切换影响性能。[mysqld] # MySQL 5.7合并配置 innodb_redo_log_capacity 2G # 总容量2G8.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_connections200~500注意max_connections不是越大越好。每个连接要占内存默认线程栈256KB~1MB5000连接就吃几个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合称双1innodb_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-idletruetest-on-borrowfalse是黄金组合空闲时检测保活借出时不检测省开销。四、慢查询监控和优化流程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 Time2.5s (3750s) Lock0.0s (0s) Rows1000.0 (1500000) SELECT * FROM orders WHERE create_time 2024-01-01 AND statusPAID 解读 - 执行1500次每次2.5秒总耗时3750秒 - 每次返回1000行 - 这是优化重点4.3 优化流程慢查询优化五步法 1. EXPLAIN看执行计划 EXPLAIN SELECT ... \G 关注 type、key、rows、Extra 2. 看有没有走索引 typeALL 全表扫描 → 必须加索引 typeref/range 走索引 → OK 3. 看索引是否合理 keyNULL → 没用索引 key有值但rows很大 → 索引区分度低 4. 看Extra有没有坏词 Using filesort → 文件排序要优化ORDER BY Using temporary → 临时表要优化GROUP BY Using index → 覆盖索引很好 5. 改SQL或加索引 - 加合适索引 - 避免 SELECT * - 拆分大SQL - 改写为等价高效写法-- 看执行计划EXPLAINSELECT*FROMordersWHEREcreate_time2024-01-01ANDstatusPAID;-- 优化加复合索引ALTERTABLEordersADDINDEXidx_time_status(create_time,status);-- 再看执行计划EXPLAINSELECT*FROMordersWHEREcreate_time2024-01-01ANDstatusPAID;-- typerange, keyidx_time_status, rows大幅下降流程化的关键定期每周跑慢查询分析而不是等用户报障才查。售货柜高峰期的慢SQL平时就埋着高峰一压才暴露。五、MySQL监控指标5.1 四个核心指标指标含义告警阈值QPS每秒查询数看基线突增突降都查TPS每秒事务数看基线连接数活跃连接/总连接活跃80%总连接告警缓冲池命中率数据页缓存命中比95%告警5.2 查询命令-- 查看当前连接数SHOWSTATUSLIKEThreads%;-- Threads_connected: 当前连接数-- Threads_running: 活跃执行中的线程数-- 查看QPS和TPS需要算差值SHOWGLOBALSTATUSLIKEQuestions;SHOWGLOBALSTATUSLIKECom_commit;SHOWGLOBALSTATUSLIKECom_rollback;-- QPS (Questions_后 - Questions_前) / 时间间隔-- TPS (Com_commit Com_rollback的差值) / 时间间隔-- 缓冲池命中率SHOWSTATUSLIKEInnodb_buffer_pool_read%;-- Innodb_buffer_pool_reads: 物理磁盘读次数-- Innodb_buffer_pool_read_requests: 总读请求-- 命中率 1 - reads/requests5.3 监控工具常用监控栈 1. Prometheus mysqld_exporter Grafana - 开源免费指标全 - 配置告警规则 2. PMMPercona Monitoring and Management - Percona官方MySQL专版 - 开箱即用 3. 阿里云/腾讯云RDS自带监控 - 云数据库用云监控Grafana看板核心图表QPS/TPS趋势、慢查询数、连接数、缓冲池命中率、复制延迟。这五张图能覆盖80%的MySQL健康度。六、性能压测工具sysbench6.1 安装# CentOSyuminstallsysbench# Ubuntuaptinstallsysbench6.2 准备数据# 准备测试数据10张表每表100万行sysbench /usr/share/sysbench/oltp_read_write.lua\--mysql-host127.0.0.1\--mysql-port3306\--mysql-userroot\--mysql-passwordxxx\--mysql-dbtest\--tables10\--table-size1000000\prepare6.3 压测# 读写在混压测64并发60秒sysbench /usr/share/sysbench/oltp_read_write.lua\--mysql-host127.0.0.1\--mysql-port3306\--mysql-userroot\--mysql-passwordxxx\--mysql-dbtest\--tables10\--table-size1000000\--threads64\--time60\--report-interval10\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 percentile95%请求的延迟比平均值更能反映体验。P95100ms算及格50ms算优秀压测场景 1. 调参前压一次记录基线 2. 调一个参数比如buffer_pool_size 3. 压测对比看TPS/P95变化 4. 有效则保留无效则回滚 例 - buffer_pool从2G→8G - TPS从1500→250067% - P95从80ms→35ms-56% → 这个调优有效保留压测核心原则一次只改一个变量。同时改多个参数无法判断哪个起的作用。改完压一次对比基线再决定保不保留。七、总结概念一句话调优层次SQL层→参数层→架构层→硬件层从上到下innodb_buffer_pool_size单机设物理内存70%最重要的参数innodb_flush_log_at_trx_commit1最安全0/2换性能max_connections按并发估算不是越大越好连接池HikariCP小而精10~20Druid带监控慢查询流程开慢日志→EXPLAIN→加索引→改写SQL监控指标QPS/TPS/连接数/缓冲池命中率sysbench标准压测工具调参前压基线对比性能调优是持续工程不是一次性活。监控→发现慢点→改SQL/调参数→压测验证→上线观察这个循环要常态化。下一篇整理项目高频问题把前面学的串起来。