MySQLNotes

第 29 章：性能调优

zjc 于 2026-01-29 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 性能调优不是改参数比赛，而是围绕用户可感知指标，找到当前系统的主要约束，并用可验证的方式消除它。数据库慢只是系统慢的一个可能环节。

29.1 调优目标

先定义指标：

指标 示例
延迟 P95 / P99 订单创建 P99 < 200ms
吞吐 峰值 TPS 5000
错误率 数据库错误 < 0.01%
可用性 99.99%
主从延迟 P99 < 1s
慢查询数 每分钟 < 10
资源水位 CPU 常态 < 60%

没有目标和基线的优化，很容易变成随机修改。

29.2 分层定位

端到端耗时：

用户
  -> 前端 / 网关
  -> 应用逻辑
  -> 连接池
  -> MySQL
     -> SQL 执行
     -> InnoDB
     -> 操作系统
     -> 磁盘 / 网络

先回答：

  1. 是所有接口慢，还是少数接口慢？
  2. 是读慢、写慢，还是锁等待？
  3. 是持续慢，还是尖刺？
  4. 是主库慢，还是从库慢？
  5. 是 CPU、IO、锁、复制，还是连接数瓶颈？
  6. 从什么时候开始？
  7. 发布、流量、数据量、参数是否变化？

29.3 查询调优

优先处理：

  1. Top 10 耗时 SQL；
  2. Top 10 扫描行 SQL；
  3. Top 10 次数最多 SQL；
  4. 新增慢 SQL；
  5. 锁等待 SQL；
  6. 大事务；
  7. 报表 SQL。

流程：

1. 获取完整 SQL 和参数；
2. 查看执行计划；
3. 估算扫描行与返回行；
4. 检查索引和过滤条件；
5. 消除函数、隐式转换、SELECT *；
6. 优化深分页和临时表；
7. 验证结果一致；
8. 记录收益；

优先改 SQL 和索引，再考虑数据库参数。应用层错误调用一千次接口，数据库参数无法解决。

29.4 Schema 调优

检查：

  1. 主键是否趋势递增；
  2. 大字段是否拆分；
  3. 索引是否冗余；
  4. 唯一约束是否完整；
  5. 状态值是否收敛；
  6. 历史数据是否归档；
  7. 表是否长期增长无界；
  8. 字符集是否统一。

查看容量：

SELECT
  TABLE_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

大表治理往往比参数更能改变长期性能。

29.5 连接与线程

查看：

SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
SHOW PROCESSLIST;

常见问题：

现象 原因
连接暴涨 应用无连接池或重试风暴
大量 Sleep 连接未释放或超时过长
Threads_running 高 并发执行过多
Threads_cached 低 连接频繁新建
Connection refused 超过 max_connections 或主机 blocked

处理：

  1. 应用连接池设置合理；
  2. 服务熔断和退避；
  3. 事务缩短；
  4. 避免单请求并发打多条 SQL；
  5. 只读流量隔离；
  6. 拆分大查询；
  7. 监控连接等待。

29.6 内存与临时区

重要参数：

参数 作用 风险
innodb_buffer_pool_size 缓存数据页 过大导致 OOM
sort_buffer_size 排序内存 每会话可能分配
join_buffer_size JOIN 缓冲 每会话可能分配
read_buffer_size 顺序读缓冲 会话相关
tmp_table_size 内部临时表上限 过大消耗内存
max_heap_table_size 内存表上限 与 tmp_table_size 共同生效

不要盲目调大 sort/join buffer。它们可能按会话或执行阶段分配，并发高时放大内存压力。

查看临时表：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

29.7 InnoDB 关键参数

参数 调优方向
innodb_buffer_pool_size 根据专用内存设置
innodb_log_file_size / innodb_redo_log_capacity 控制 Redo 容量和写入抖动
innodb_flush_log_at_trx_commit 持久性与性能取舍
sync_binlog Binlog 持久化
innodb_io_capacity 匹配磁盘能力
innodb_flush_method 通常 O_DIRECT
innodb_page_size 初始化后不可改，需提前评估

核心主库不要为了压测分数放松 sync_binlog=1 和 innodb_flush_log_at_trx_commit=1。

29.8 写入热点

热点表现：

  1. 少数行锁等待高；
  2. TPS 不升但延迟升；
  3. 单表写入集中；
  4. 自增页或计数行集中；
  5. 活动库存、直播间、热门帖子异常。

处理：

  1. 合并写请求；
  2. 异步化；
  3. Redis 预扣或缓冲；
  4. 计数拆分为多行；
  5. 按时间分桶；
  6. 消息队列削峰；
  7. 业务限流；
  8. 拆分热点租户；
  9. 定期落库。

29.9 缓存策略

常见模式：

1. 读请求 -> Redis
2. 未命中 -> MySQL
3. 写 MySQL 成功
4. 删除或更新 Redis

注意事项：

  1. 缓存失效可能短暂不一致；
  2. 先写库后删缓存更常用；
  3. 缓存要有过期时间；
  4. 防穿透、防击穿、防雪崩；
  5. 热点 key 监控；
  6. 缓存只能降低读压力，不能替代索引；
  7. 对账修复不可少。

29.10 参数调优流程

1. 确认问题和目标；
2. 记录当前参数和指标；
3. 在预发压测；
4. 一次只改一个关键参数；
5. 观察延迟、吞吐、错误、资源；
6. 检查主从延迟；
7. 灰度生产；
8. 保留回滚配置；
9. 记录结论和适用条件；

严禁把网上“秒杀调优模板”直接套到核心库。

29.11 基准测试

常用工具：

  1. sysbench；
  2. mysqlslap；
  3. 业务回放；
  4. 影子库压测；
  5. 云厂商压测服务。

sysbench 准备：

sysbench oltp_read_write \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password='...' \
  --mysql-db=sbtest \
  --tables=10 \
  --table-size=1000000 \
  prepare

运行：

sysbench oltp_read_write \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password='...' \
  --mysql-db=sbtest \
  --tables=10 \
  --table-size=1000000 \
  --threads=64 \
  --time=300 \
  --report-interval=10 \
  run

清理：

sysbench oltp_read_write \
  --mysql-host=127.0.0.1 \
  --mysql-db=sbtest \
  cleanup

压测必须模拟真实读写比、数据分布、事务大小和连接模型。

29.12 常见反模式

反模式 后果
只看 QPS 忽略延迟和错误
盲目加索引 写入和 DDL 变慢
大事务 锁、Undo、复制延迟
所有读走从库 一致性问题
无连接池上限 连接风暴
用缓存掩盖全表扫描 缓存失效后雪崩
深分页 扫描大量行
一次改十个参数 无法归因
没有回滚 故障扩大
压测不预热 结论失真

本章小结

性能调优应从业务指标和执行计划开始，优先解决 SQL、索引、数据模型和热点问题，再评估连接、内存、InnoDB、磁盘和架构。每个优化都要有基线、验证和回滚。参数只是调优的一部分，不是银弹。

思考题

  1. 为什么 P99 延迟比平均值更适合描述用户体验？
  2. 如何判断慢在应用、数据库还是网络？
  3. 为什么不能盲目调大 sort buffer？
  4. 热点行更新有哪些拆分方案？
  5. 为一次上线设计完整的性能验证报告。