MySQL 数据库性能如何优化？指标、瓶颈定位与容量测试

性能优化不是调参数，而是建基线

我参与过的 MySQL 性能问题排查中，超过七成的案例在最初阶段都没有有效的性能基线。团队往往等到系统响应变慢才去查慢查询日志，然后凭经验调整几个参数，重启实例，问题暂时消失，但根源并未排除。

这种做法在业务量低时可行，一旦流量增长，瓶颈会以更严重的形态复现。真正的性能优化应该从三个层面建立工作流：指标采集形成基线、压测暴露极限、定位瓶颈后做针对性调整。

本文围绕这个流程展开，不罗列所有 MySQL 参数，而是讲清楚每个环节该做什么、怎么做、什么时候做，以及如何判断做完了。

指标采集：先知道系统在干什么

没有数据就没有优化。MySQL 性能优化的第一步是建立持续、可回溯的指标采集体系。采集什么、怎么采、采多久，决定了后续分析的可靠性。

必须采集的核心指标

按优先级排列，以下五类指标必须覆盖：

• **连接与线程状态**：Threads_connected、Threads_running、Threads_created。Threads_running 持续高于 CPU 核心数 2-3 倍时，通常意味着有锁等待或慢查询积压。
• **查询吞吐与延迟**：Questions、Queries、Slow_queries。Slow_queries 的绝对值不重要，趋势上升才值得关注。同时记录 QPS（每秒查询数）和 TPS（每秒事务数）的波动曲线。
• **InnoDB 引擎关键指标**：Innodb_rows_read、Innodb_rows_inserted、Innodb_buffer_pool_read_requests 与 Innodb_buffer_pool_reads。后者比例持续超过 1% 说明缓冲池命中率不足，需评估内存是否够用。
• **临时对象与排序**：Created_tmp_disk_tables、Created_tmp_tables、Sort_merge_passes。磁盘临时表占比过高，通常指向 SQL 缺少合适索引或 sort_buffer_size 过小。
• **锁与死锁**：Innodb_row_lock_current_waits、Innodb_deadlocks。死锁次数只要不为零就应记录现场，不是所有死锁都能被应用层重试机制消化。

采集工具选型

生产环境不建议用通用监控系统偶尔拉取 SHOW STATUS，那样采样间隔太长，容易错过瞬态峰值。

• **Prometheus + mysqld_exporter**：这是目前最成熟的开源方案。mysqld_exporter 默认采集 200+ 指标，采样间隔建议设为 5 到 10 秒。配合 Grafana 的 MySQL 官方仪表盘模板，可以直接看到连接数、QPS、缓冲池命中率的时间序列图。
• **Percona Monitoring and Management (PMM)**：如果团队不想从头搭建，PMM 开箱即用，内置 Query Analytics 功能，能按查询指纹聚合延迟和扫描行数。缺点是对资源消耗略高，中小型实例可以接受。
• **自定义采集脚本**：对于合规要求严格的场景，可以用 Python 脚本定期执行 SHOW GLOBAL STATUS 和 SHOW ENGINE INNODB STATUS，结果写入时序数据库。脚本必须注意连接池复用，避免采集本身成为负担。

无论选哪种方案，采集周期应覆盖一个完整的业务周期。电商系统至少要跑满一周，包含周末和促销时段。SaaS 系统要覆盖月末结算高峰期。采集不到峰值数据，基线就没有意义。

容量测试：用压测摸清极限

指标采集回答的是"系统当前在做什么"，容量测试回答的是"系统还能扛多少"。很多企业等到线上出现 5xx 才去扩容，根源在于从未做过压测。

压测工具选择

• **sysbench**：适合做基准测试。可以控制线程数、表数量、数据量，测试 OLTP 模式下的最大吞吐。建议先做只读压测，再做读写混合压测，最后做写密集压测。每次压测前必须重启 MySQL 并清空缓冲池，否则结果不可比。
• **mysqlslap**：MySQL 自带的压测工具，适合模拟并发登录场景。但功能有限，不适合复杂负载模拟。
• **JMeter 或 Gatling**：如果需要模拟真实应用层 SQL 模式，用这两个工具录制业务 SQL 回放。注意回放时要替换参数化变量，否则所有请求命中同一行数据，压测结果失真。

压测流程

1. **准备数据**：数据量应为生产预估容量的 1.5 到 2 倍。表结构、索引、存储引擎必须与生产一致。
2. **预热**：压测前执行全表扫描或全索引扫描，使缓冲池填满热数据。不预热直接压测，结果反映的是磁盘 I/O 瓶颈而非真实负载能力。
3. **阶梯加压**：从 10 个并发线程开始，每次增加 10 个线程，持续 3 分钟，记录 QPS 和响应延迟的 99 分位值。当 QPS 不再随并发数增加而上升，或者延迟 99 分位值超过 500 毫秒时，即为当前配置下的瓶颈点。
4. **记录瓶颈配置**：记录此时的 CPU 使用率、磁盘 I/O 利用率、InnoDB 缓冲池命中率、连接数。这些数据就是当前实例的容量基线。

容量基线的价值

容量基线不是一次性的。每次变更参数、升级硬件、修改表结构后，都应重新压测并更新基线。有了基线，团队在线上出现性能问题时能快速判断：是配置退化、数据增长还是异常流量导致。

瓶颈定位：从现象到根因

指标采集和压测给出了"哪里慢了"的信号，瓶颈定位要回答"为什么慢"。常见瓶颈分为四层：SQL 层、锁层、内存层和 I/O 层。

SQL 层：慢查询与执行计划

慢查询日志是最直接的入口。开启方式：

```
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1; -- 100 毫秒
SET GLOBAL log_queries_not_using_indexes = ON;
```

生产环境 long_query_time 建议设为 0.1 到 0.5 秒。设得太小日志量过大，设得太大容易漏掉潜在问题。分析慢查询日志推荐用 pt-query-digest，它能按查询指纹聚合，输出总耗时、平均耗时、扫描行数、返回行数。

拿到慢查询后，用 EXPLAIN 查看执行计划，重点关注：

• **type 列**：ALL 或 index 通常意味着全表扫描或全索引扫描，需要加合适的索引。range 或 ref 是正常水平。const 或 eq_ref 是理想状态。
• **rows 列**：估计扫描行数。如果 rows 比实际返回行数大两个数量级以上，说明索引选择性差或查询条件无法有效利用索引。
• **Extra 列**：出现 Using filesort 或 Using temporary 时，意味着 MySQL 在内存或磁盘上做了额外排序或临时表操作，需要调整排序缓冲区或优化 SQL 写法。

一个常见的误区是认为加了索引就解决问题。索引能加速查询，但会拖慢写入。如果一个表的写入 QPS 超过 2000，索引数超过 5 个，写入时的 B+ 树维护成本就会显著上升。此时需要平衡读写比例，或者考虑使用覆盖索引减少回表。

锁层：等待与死锁

锁等待是 MySQL 性能问题的隐形杀手。表现是 Threads_running 很高但 CPU 使用率很低，因为线程都在等锁。

定位锁等待的常用方法：

• **SHOW PROCESSLIST**：查看处于 Waiting for table metadata lock 或 Waiting for row lock 的线程。前者通常是因为长事务未提交，后者是因为并发写冲突。
• **information_schema.INNODB_TRX 与 INNODB_LOCK_WAITS**：可以查到哪个事务在等哪个事务的锁。联合查询可以定位到阻塞源头。
• **pt-deadlock-logger**：Percona Toolkit 中的工具，持续监控死锁并记录现场。对于偶发死锁，人工排查几乎不可能，必须靠工具持续采集。

解决锁等待，优先考虑减少事务持续时间。检查应用层代码，看是否在事务中执行了外部 API 调用或耗时的计算逻辑。事务应该只包裹必要的数据库操作，其他逻辑放到事务之外。

内存层：缓冲池与临时表

InnoDB 缓冲池（innodb_buffer_pool_size）是 MySQL 最大的内存使用者，通常建议设为物理内存的 60% 到 75%。但这是经验值，实际配置要看数据量和工作集大小。

判断缓冲池是否够用，看两个指标：

• **Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests**：比值超过 1% 时，说明有 1% 的读取请求需要从磁盘加载，此时增加缓冲池大小或优化查询模式都有收益。
• **Innodb_buffer_pool_wait_free**：这个值持续增长，说明缓冲池刷新线程跟不上写入速度，需要增大 innodb_io_capacity 或升级磁盘。

临时表方面，Created_tmp_disk_tables 占 Created_tmp_tables 的比例超过 10% 时，应检查是否有 GROUP BY 或 DISTINCT 操作缺少索引，或者 tmp_table_size 和 max_heap_table_size 设置过小。注意这两个参数同时生效，取较小值。

I/O 层：磁盘与日志

I/O 瓶颈在云数据库场景下最常见。表现是磁盘 iowait 高、QPS 低、查询延迟抖动大。

排查 I/O 瓶颈时，关注两个日志系统：

• **Redo log**：innodb_log_file_size 太小会导致日志切换频繁，触发检查点刷脏，造成 I/O 尖刺。建议设置为缓冲池大小的 25% 到 50%。8.0 版本开始支持自动调整，但手动设置更可控。
• **Binlog**：binlog 写入是顺序 I/O，但如果 sync_binlog 设为 1，每次事务提交都要 fsync，写入密集场景下会成为瓶颈。可以设为 0 或 N（如 100），但会牺牲崩溃恢复时的数据完整性。需要业务方确认可接受的数据丢失窗口。

磁盘选型方面，NVMe SSD 是当前性价比最高的选择。SATA SSD 在随机读写密集场景下延迟会高出 3 到 5 倍。HDD 已经不适合任何在线业务 MySQL 实例，即使只做归档库，查询延迟也难控制。

参数调优：只调确认有问题的参数

网上流传的"MySQL 性能优化 30 条"中，大部分参数在默认配置下已经够用。盲目调参不仅没有收益，还可能引入新问题。调优的原则是：只调压测或监控确认有瓶颈的参数。

以下是我在多个项目中验证过的调优路径，按优先级排列：

连接与线程

• **max_connections**：默认 151 对于大多数应用偏低。建议设为 500 到 1000，但必须配合 max_user_connections 防止个别用户耗尽连接池。注意连接数不是越大越好，每个连接消耗线程栈内存，连接数超过 2000 时上下文切换成本会急剧上升。
• **thread_cache_size**：设为 8 到 64 即可，太大没有意义。主要作用是减少线程创建销毁的开销。

InnoDB 核心

• **innodb_buffer_pool_size**：如前述，60% 到 75% 物理内存。如果实例是独占的，可以设到 80%。注意预留内存给操作系统和文件系统缓存。
• **innodb_log_file_size**：建议 1GB 到 4GB。日志文件越大，崩溃恢复时间越长，但写入性能越稳定。8.0 版本中日志文件的总大小由 innodb_redo_log_capacity 控制，默认 100MB，生产环境建议至少设为 2GB。
• **innodb_flush_log_at_trx_commit**：默认 1 最安全。如果业务允许 1 秒内的数据丢失，可以设为 2，写入性能提升 30% 到 50%。
• **innodb_io_capacity**：默认 200，对于 SSD 太低。建议设为 2000 到 5000，具体取决于磁盘的 IOPS 能力。设置过高会导致刷脏线程占用过多 CPU。

查询与排序

• **sort_buffer_size**：默认 256KB，对于复杂排序可能不足。但不要设得太大，因为这是每个连接独立分配。建议设为 2MB 到 8MB，配合监控 Sort_merge_passes 调整。如果 Sort_merge_passes 持续大于 0，再逐步增大。
• **join_buffer_size**：默认 256KB，对于没有索引的 JOIN 操作，增大此值可以减少磁盘临时表。但同样要注意每个连接都分配，建议不超过 4MB。

查询缓存（8.0 之前版本）

查询缓存（query_cache_type）在 MySQL 8.0 中已被移除。在 5.7 及之前版本中，除非业务是典型的读多写少且表更新频率极低，否则建议关闭查询缓存。它的失效机制在高并发写入场景下反而变成全局锁，拖慢性能。

什么时候做优化：按信号触发，而非定期

性能优化不是年度任务，而是信号驱动的工作。以下是明确的触发信号：

• **响应延迟 99 分位值超过业务 SLA 阈值**：例如用户端要求 API 响应在 200 毫秒以内，数据库查询延迟 99 分位值超过 100 毫秒就应启动排查。
• **慢查询数量趋势上升**：不是看绝对值，而是看 7 天环比。如果慢查询数量连续 3 天增长超过 20%，说明有 SQL 退化或数据量增长导致计划变更。
• **CPU 使用率持续超过 80%**：除非是纯计算密集型场景，否则 CPU 高通常意味着查询效率低。
• **磁盘 I/O 利用率持续超过 70%**：此时 I/O 等待已经开始影响查询延迟。
• **版本升级或架构变更后**：任何数据库版本升级、操作系统更新、磁盘更换后，都应重新压测并更新基线。

没有上述信号时，不要做无目的的调参。默认配置在多数场景下是经过 MySQL 团队大量测试的，贸然改动可能得不偿失。

成本与周期：优化投入的合理预期

性能优化的成本和周期取决于问题类型和团队经验。以下给出定性判断，具体数字因环境而异。

成本构成

• **监控与采集工具搭建**：如果使用 Prometheus + Grafana 开源方案，硬件成本极低，一台 4C8G 的虚拟机即可承载 50 个实例的监控。人力成本主要是初次搭建和仪表盘配置，有经验的 DBA 或运维工程师大约需要 2 到 3 个工作日。
• **压测环境搭建**：需要一台与生产配置相同或相近的压测机。如果复用生产实例的从库做压测，必须确保从库不承载读流量，且压测数据不影响主库数据。成本主要是机器费用，按云厂商按量计费模式，一次压测 2 到 4 小时，费用在几十到几百元之间。
• **参数调优与代码修改**：SQL 优化通常由开发工程师完成，单个慢查询的优化工时在 0.5 到 2 天之间。参数调优由 DBA 完成，调整一个参数并验证效果通常需要 0.5 天。

周期预期

• **快速见效的场景**：缺少索引导致的慢查询，加索引后当天即可看到延迟下降。缓冲池太小导致的高磁盘读取，增大后重启实例，效果立竿见影。
• **中等周期的场景**：锁等待和死锁问题，需要应用层修改事务逻辑，加上测试和灰度发布，周期在 1 到 2 周。
• **长周期的场景**：容量基线建立和压测常态化，需要 1 到 2 个月才能形成团队习惯。硬件升级或架构拆分，周期在 1 到 3 个月不等。

风险与边界：优化不是万能的

性能优化有明确的边界，认清边界比掌握技巧更重要。

• **参数调优无法弥补架构缺陷**：如果一个表的数据量超过 5 亿行且没有分区，任何参数调整都无法让全表扫描变快。此时需要做分库分表或引入搜索引擎。
• **压测结果不等于线上表现**：压测模拟的是理想负载，线上流量有突发性和不均匀性。压测得到的 QPS 上限应打 70% 折扣作为生产容量预警线。
• **优化可能引入新问题**：增大缓冲池会占用更多内存，可能导致操作系统 OOM。增大连接数会增加上下文切换。每个参数调整都应在灰度环境中验证。
• **版本差异不可忽视**：MySQL 5.7 与 8.0 在优化器行为、锁机制、数据字典实现上有显著差异。网上找到的 5.7 调优经验，在 8.0 上可能完全不适用。始终以官方文档和当前版本的实际测试为准。

从指标到决策的闭环

在 SystemDo 参与的一个企业级项目中，客户的生产 MySQL 实例每天凌晨都会出现 30 分钟的响应尖峰。团队花了一周搭建监控，发现尖峰时刻的磁盘 I/O 利用率达到 95%，而 CPU 使用率只有 20%。进一步排查发现是定时任务在凌晨执行大量数据归档操作，与 binlog 刷盘高峰重叠。解决方案不是调参数，而是将归档任务错开 2 小时执行。问题解决后，响应延迟 99 分位值从 800 毫秒降到 80 毫秒。

这个案例说明，性能优化的本质是建立从指标到根因再到决策的闭环。没有基线，你无法判断问题是否真的存在；没有压测，你不知道系统还有多少余量；没有定位，你只能盲目试错。

对于正在规划数据库性能优化的团队，我的建议是：先花两周时间把指标采集和压测流程跑通，建立容量基线。在此基础上，每次变更都对照基线做验证。这套工作流一旦形成，MySQL 性能问题就不再是"玄学"，而是一个可测量、可复现、可解决的工程问题。