一、起源:从"感觉慢"到"可度量"
数据库性能问题的外在表现通常只有一句话描述,例如"接口变慢""高峰期卡顿""偶发超时"。这类描述无法直接指向原因,也无法判断改动是否有效。
常见表现形式:
- 响应时间变长:单条查询或事务耗时上升,接口的 P95、P99 延迟抬升,平均值变化不明显
- 吞吐下降:单位时间内完成的事务数减少,队列积压,连接被占满
- 间歇性抖动:指标在特定时间点出现尖峰,例如整点任务、备份开始、批量导入期间
- 局部恶化:只有部分 SQL 或部分表变慢,实例整体指标正常
- 报错型表现:获取连接超时、锁等待超时、连接数达到上限后拒绝新连接
这些表现之间的差异对应不同根因。响应时间上升可能来自执行计划变化,吞吐下降可能来自锁竞争,抖动可能来自刷盘与检查点,报错型表现则更多来自资源上限被触达。仅凭现象描述无法区分,需要把现象落到具体指标上。
从"感觉慢"到"可度量"需要具备三个条件:
- 有明确的指标定义与采集位置,知道每个指标从哪一层读取
- 有可对比的基线,否则无法判断当前值是否异常,基线与压测方法见 性能测试与基线 第二节
- 有明确的判定顺序,避免在定位完成前先改参数
二、原理
2.1 性能指标的分层
MySQL 的性能指标分布在四层,层与层之间存在明确的因果方向:上层现象由下层资源状态决定,下层异常通常先在存储引擎层与主机层留下痕迹。
| 层次 | 主要指标 | 采集位置 |
|---|---|---|
| 应用层 | 响应时间、吞吐(QPS、TPS)、错误率、连接等待时间 | 应用监控、连接池指标 |
| SQL 层 | 执行次数、扫描行数、返回行数、排序与临时表次数 | 慢查询日志、performance_schema、sys 库 |
| 存储引擎层 | 缓冲池命中率、脏页比例、日志等待、行锁等待、页读写次数 | SHOW ENGINE INNODB STATUS、performance_schema |
| 主机层 | CPU 使用率与等待、内存与 swap、磁盘 IOPS 与延迟与利用率、网络 | 主机监控、系统工具 |
层间的因果关系:
- 应用层的响应时间上升,向上可归因于 SQL 执行耗时上升、锁等待、连接获取等待三类
- SQL 执行耗时的上升,可进一步归因于扫描行数增加、执行计划退化、临时表与排序落盘
- 扫描行数与内存命中率相关,命中率低会把压力传导到存储引擎层的页读取
- 存储引擎层的页读取最终落到主机层的磁盘 IO,若磁盘延迟上升,则前面的每一层都会被放大
- 反向的链路同样成立:磁盘饱和会造成刷脏变慢、脏页积压、写入延迟上升,最终表现为应用层超时
分析时以应用层现象为入口,逐层向下确认,而不是直接在某一层调整参数。
2.2 瓶颈的常见分类
| 类型 | 典型特征 | 主要观测点 |
|---|---|---|
| 慢 SQL 与缺索引 | 特定语句耗时高、扫描行数远大于返回行数 | 慢查询日志、执行计划、rows_examined |
| 锁等待 | 并发下延迟升高、部分会话处于等待状态 | 行锁等待、元数据锁等待、事务持锁时间 |
| 连接与线程耗尽 | 获取连接失败、活动连接数接近上限 | Threads_connected、Threads_running、连接池指标 |
| 缓冲池不足 | 命中率下降、磁盘读次数上升、读延迟敏感 | 缓冲池使用量、页读取速率、磁盘读指标 |
| 磁盘 IO 饱和 | IO 利用率接近满、写入延迟上升、写入堆积 | 磁盘利用率、队列长度、等待时间 |
| 刷脏与检查点 | 周期性抖动、写入出现停顿 | 脏页比例、检查点频率、日志等待 |
| 复制延迟 | 从库数据滞后、从库重放线程 CPU 高 | 复制位点差、重放速率 |
| 大事务与批量操作 | 单次操作耗时数百毫秒以上、undo 增长、锁持有时间长 | 事务持续时间、undo 表空间、受影响行数 |
- 上述类型常同时出现:慢 SQL 会放大锁持有时间,锁等待会拖长事务,长事务会增加 undo 与刷脏压力
- 区分主因与次生现象是定位的关键。例如锁等待是现象,导致等待的长事务才是根因
2.3 性能模式与 sys 库
performance_schema 是 MySQL 内置的运行时采集组件,采集范围覆盖语句、等待事件、锁、内存、复制与文件 IO。
采集范围与开销:
- 按消费者(consumer)与插桩(instrument)维度的开关控制采集范围,未开启的消费者不产生数据
- 开销与开启的采集项数量相关,全量开启会增加 CPU 消耗与内存占用,生产环境通常按需开启
- 关键消费者包括语句摘要、等待事件、阶段事件、事务与锁;语句采集有长度限制,超长语句会被截断
开关与核查:
-- 查看主要消费者开关状态
SELECT NAME, ENABLED FROM performance_schema.setup_consumers;
-- 查看插桩是否启用,可按名称筛选关注的事件
SELECT NAME, ENABLED, TIMED FROM performance_schema.setup_instruments
WHERE NAME LIKE 'statement/%' LIMIT 20;
-- 按执行时间排序的语句摘要,从总体开销出发定位
SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s,
ROUND(AVG_TIMER_WAIT/1e9, 3) AS avg_ms,
SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
sys 库是对 performance_schema 与 information_schema 的封装,把常见问题整理为可直接查询的视图,省去多表关联。
| 视图 | 用途 |
|---|---|
sys.statement_analysis | 按总延迟排序的语句,含平均延迟与扫描行数 |
sys.statements_with_full_table_scans | 全表扫描的语句清单 |
sys.statements_with_temp_tables | 使用临时表的语句 |
sys.statements_with_sorting | 触发排序的语句 |
sys.schema_unused_indexes | 未见使用记录的索引,用于评估冗余索引 |
sys.innodb_buffer_stats_by_table | 按表统计缓冲池占用 |
sys.innodb_lock_waits | 当前锁等待关系与阻塞源 |
sys.processlist | 增强的活动会话视图,含当前执行的语句与等待 |
sys.session | 当前会话的资源使用汇总 |
sys.memory_by_thread_by_current_bytes | 按线程统计内存占用 |
分析维度与对应入口:
- 按 SQL 维度:
sys.statement_analysis、events_statements_summary_by_digest,关注总延迟贡献而非单次最大值,一条执行频率高的普通语句可能比偶发的慢语句消耗更多资源 - 按表维度:
sys.innodb_buffer_stats_by_table、performance_schema.table_io_waits_summary_by_table,用于识别热点表与访问方式 - 按等待事件维度:
performance_schema.events_waits_summary_global_by_event_name,用于判断时间消耗在锁、IO 还是其他等待上
2.4 慢查询日志
慢查询日志记录执行时间超过 long_query_time 的语句,是 SQL 层最直接的数据来源,配置项见 MySQL 服务配置 的慢查询分区。
阈值与采样设置:
long_query_time建议从 1 秒起步,配合min_examined_row_limit过滤扫描行数过少的语句,避免输出量过大log_queries_not_using_indexes会记录所有未走索引的语句,在数据量小的表上会产生大量低价值记录,配合限流参数使用- 阈值调整应在业务低峰执行,并用一段时间的数据量评估输出规模,避免日志目录被写满
- 采样不适合用随机丢弃实现,更可行的方式是把阈值与扫描行数门槛组合,聚焦高代价语句
日志内容的判读:
| 字段 | 含义 | 判读要点 |
|---|---|---|
Query_time | 语句执行耗时 | 与业务超时阈值对照 |
Lock_time | 等待锁的时间 | 接近 Query_time 时,瓶颈在锁而非执行效率 |
Rows_sent | 返回给客户端的行数 | 与业务预期比对,过大提示缺少分页 |
Rows_examined | 扫描的行数 | 远大于 Rows_sent 提示索引选择性差或未使用索引 |
Rows_affected | 受影响行数 | 写语句中异常偏大提示条件范围过宽 |
Rows_examined与Rows_sent的比值是判断索引有效性的直观依据,比值大说明扫描了大量无用行Lock_time偏高但Rows_examined正常时,问题在并发与事务设计,而不是索引- 日志中记录的语句可能被截断,长语句需结合
performance_schema的摘要信息补齐
聚合分析方法:
# 统计出现次数最多的语句类型
mysqldumpslow -s c -t 20 /var/log/mysql/mysql-slow.log
# 按总耗时排序,定位累计消耗最大的语句
mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log
# 按平均扫描行数排序,聚焦扫描效率最差的语句
pt-query-digest --order-by Query_time:sum \
--limit 20 /var/log/mysql/mysql-slow.log
- 聚合时应同时看总耗时与单次耗时两个排序维度,两者对应的优化优先级不同
- 日志轮转与归档需与慢日志输出量匹配,避免单文件无限增长
2.5 连接与线程
连接相关参数决定实例能同时接受多少客户端,以及每个连接占用多少资源。
| 参数或指标 | 含义 | 关注点 |
|---|---|---|
max_connections | 允许的最大连接数 | 与连接池上限总和对照,配置见 MySQL 服务配置 |
thread_cache_size | 线程缓存数量 | 连接建立与销毁频繁时提高命中,减少线程创建开销 |
Threads_connected | 当前连接数 | 持续接近上限即存在耗尽风险 |
Threads_running | 正在执行的线程数 | 超过 CPU 核数若干倍时,说明并发在执行而非排队 |
Threads_created | 创建过的线程总数 | 增长过快说明线程缓存无效,或连接未复用 |
Aborted_connects | 连接失败次数 | 持续增长提示网络、认证或连接数上限问题 |
wait_timeout | 空闲连接超时 | 与连接池的空闲回收时间配合,避免服务端先断开导致报错 |
连接池与数据库连接数的关系:
- 数据库侧的有效并发由连接池的活动连接数决定,而不是池中配置的上限。池上限之和超过
max_connections时,池之间会互相挤占 - 池参数中的最大连接数、最小空闲连接、获取连接超时、空闲回收时间需要与服务端的
wait_timeout、max_connections一起取值 - 池的空闲回收时间应小于服务端的
wait_timeout,否则池认为可用而服务端已断开,产生连接失效报错 - 连接数上限提升需要评估实例内存:每个连接都有独立的会话缓冲区,连接数翻倍会放大内存占用
连接暴涨的表现与来源:
- 表现:
Threads_connected阶梯式上升、Threads_running高企、应用侧报获取连接超时、实例 CPU 与内存同步上升 - 常见来源:连接泄漏使连接不归还或未关闭;上游重试风暴把失败请求放大;某个慢 SQL 占住连接使池耗尽;实例假死时客户端持续重连
- 连接泄漏的识别依据是连接数与请求量的关系:请求量稳定而连接数持续上升,且连接的空闲时间分布出现长尾
2.6 内存与缓冲
MySQL 的内存可分为全局共享与按会话分配两类,二者在同一实例上争夺物理内存。
| 类型 | 主要组成 | 分配方式 |
|---|---|---|
| 全局共享 | InnoDB 缓冲池、日志缓冲区、表缓存、表定义缓存 | 启动或动态设置时一次性占用 |
| 按会话 | 排序缓冲、连接缓冲、连接结果缓冲、随机读缓冲、临时表 | 每个会话按需分配,并发时成倍放大 |
| 按操作 | 排序与连接使用的临时缓冲、临时表内存 | 单条语句内按需分配 |
innodb_buffer_pool_size 是影响读性能最直接的内存参数:
- 缓冲池缓存数据页与索引页,命中时无需访问磁盘。命中率在数据量大于内存时无法无限提升,其判读需要与工作集大小结合,而不是追求高位数值
- 命中率的分母包含逻辑读,若实例存在大量全表扫描,命中率可能因为反复读取相同页而虚高,需与磁盘读次数一同判断
- 缓冲池的调整会触发页面换出与换入,改变缓冲池大小属于对实例有显著影响的操作
内存不足时的表现:
- 主机层出现 swap 使用上升,数据库进程的部分内存被换出,延迟大幅波动
- 按会话分配的缓冲被高并发放大后,出现内存分配失败或实例被系统 OOM 终止
- 临时表由内存转落盘,临时表磁盘读写增加,写入延迟上升
- undo 表空间与历史版本占用增长,进一步挤压可用内存
2.7 刷盘与 IO
写入路径的落盘行为决定写入延迟与数据安全性的取舍。
- 脏页产生:数据页在缓冲池中被修改后成为脏页,需要在检查点或刷脏时机写入磁盘
- 刷脏速率:由刷脏相关参数与系统负载共同决定,刷脏跟不上写入速度时脏页比例上升,检查点被迫更激进,出现写入停顿
- 检查点:日志空间有限,检查点推进速度决定已用日志空间能否回收。日志空间不足会使写入阻塞在日志等待上
- redo 写入:按事务提交时点落盘,落盘策略影响数据安全性与写入延迟
- binlog 写入:同样涉及落盘策略,且与复制延迟直接相关
与主机 IO 指标的对照方法:
| 数据库侧现象 | 主机侧对应指标 | 结论方向 |
|---|---|---|
| 数据页读取速率上升、命中率下降 | 磁盘读 IOPS 上升、读延迟上升 | 缓冲池不足或工作集增大 |
| 脏页比例上升、刷脏速率不足 | 磁盘写利用率接近满、写队列变长 | 磁盘写入能力成为瓶颈 |
| 日志等待增加、提交延迟上升 | 磁盘同步写入延迟上升 | 日志所在介质的写入延迟过高 |
| 周期性写入停顿 | 磁盘利用率出现锯齿状尖峰 | 检查点与刷脏集中触发 |
页面缓存与直接 IO 的差异、脏页回写机制与磁盘观测工具的用法见 页缓存与 IO 性能 第二、三节;磁盘健康与性能指标见 磁盘存储与文件系统操作 第九节。
innodb_flush_method决定数据文件是否绕过操作系统页缓存,配置不当会出现数据被缓存两次,实际可用内存减少;Buffer Pool 的缓存与淘汰、脏页刷回与 redo 循环写的机制见 mysql: InnoDB 存储引擎- 日志文件与数据文件放在同一物理介质时,数据刷脏与日志写入互相争抢,IO 延迟叠加
- 主机侧的 IO 指标反映的是整个系统的写入,包含数据库以外的进程,定位时需要排除其他占用者
三、实现
3.1 诊断顺序
诊断遵循从现象向下定位的顺序,先确认瓶颈所在层次,再进入对应层的细节。避免先调整参数的做法,原因是参数变更缺乏针对性,且会改变现场使后续定位失去参照。
排查顺序与常用命令:
- 确认现象范围:是全部请求变慢还是部分语句变慢,是持续还是间歇,起点在什么时间
- 查看当前活动会话,判断是否存在堆积与等待
-- 当前连接与运行线程数
SHOW STATUS LIKE 'Threads_%';
-- 活动会话的增强视图:状态、耗时、SQL、等待
SELECT * FROM sys.processlist ORDER BY time DESC LIMIT 20;
-- 处于锁等待的会话及阻塞源
SELECT * FROM sys.innodb_lock_waits;
- 查看存储引擎层状态,判断是否卡在锁、IO 或刷脏
-- InnoDB 整体状态:事务、锁、日志、缓冲池
SHOW ENGINE INNODB STATUS\G
-- 缓冲池读写请求与命中情况
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 数据页与日志的读写等待
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/io/innodb%' OR EVENT_NAME LIKE 'wait/io/file/%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 15;
- 回到 SQL 层,按总延迟排序定位高消耗语句
-- 按总延迟排序的语句摘要
SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
-- 全表扫描占比高的语句
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
- 对定位到的语句查看执行计划,
EXPLAIN的用法见 使用命令 第九节,各列的逐项判读与索引失效的常见情形见 mysql: 索引与执行计划 - 与主机层指标对照,确认是否存在磁盘、CPU、内存或网络的资源约束
- 形成结论:明确瓶颈层次、触发条件、影响范围,再进入优化
常用命令组合与判读顺序:
SHOW FULL PROCESSLIST与sys.processlist用于快速看全貌,前者兼容性好,后者信息更全SHOW ENGINE INNODB STATUS中的事务段、锁等待段与日志段分别对应事务、锁与刷盘三个方向- 状态变量中的累计值需要连续采样两次求差,单点值无法反映变化速率
高危:KILL 终止会话或事务会中断客户端正在执行的操作,对处在写事务中的会话会触发回滚,回滚大事务可能比原操作耗时更长并持续占用 IO。执行前应确认会话来源与业务影响,优先通过应用侧停止任务或设置事务超时。回滚方式:已终止的会话不可恢复,需要由应用重试;确认误杀时应立即通知业务方并核对数据一致性。
3.2 慢 SQL 分析与优化闭环
闭环由定位、核对执行计划、改写与索引调整、验证回归四步构成。
定位:
- 从慢查询日志或
sys.statement_analysis中选出目标语句,记录其执行次数、平均耗时、扫描行数与返回行数 - 优先处理总耗时贡献大的语句,而非单次耗时最大的语句
执行计划核对:
-- 查看优化器选择的访问方式与预估行数
EXPLAIN SELECT o.id, o.total FROM orders o
WHERE o.user_id = 1001 AND o.created_at > '2026-09-01';
-- 实际执行情况:返回行数、扫描行数、实际耗时
EXPLAIN ANALYZE SELECT o.id, o.total FROM orders o
WHERE o.user_id = 1001 AND o.created_at > '2026-09-01';
- 核对重点:访问类型、使用的索引、预估行数与实际行数的偏差、是否出现临时表与文件排序
- 预估行数与实际行数差异大时,统计信息可能过期,需要先更新统计信息再评估
EXPLAIN ANALYZE会真实执行语句,对写语句与高代价语句应谨慎使用
改写与索引调整方向:
| 问题 | 调整方向 |
|---|---|
| 未使用索引或索引选择性差 | 按查询条件与排序字段设计组合索引,注意最左前缀 |
| 扫描行数远超返回行数 | 增加过滤性更强的条件,或调整索引列顺序 |
| 回表次数多 | 使用覆盖索引,把查询列纳入索引 |
| 排序与分组落盘 | 使排序字段与索引顺序一致,减少排序行数 |
| 深分页 | 用基于游标的翻页替代大偏移量分页 |
| 子查询被反复执行 | 改写为连接,或分步查询在应用层合并 |
| 大范围更新或删除 | 拆分为按主键区间的分批操作,控制单事务规模 |
- 新增索引会占用空间并增加写入成本,索引数量与写入性能之间存在取舍,冗余索引清单可用
sys.schema_unused_indexes辅助判断 - 索引变更属于 DDL,涉及表锁与在线变更工具的选择,应按变更流程评审
验证与回归:
- 在压测或预发环境用相同数据量与并发复现,对比修改前后的耗时与扫描行数
- 上线后重新采集慢查询日志与语句摘要,确认目标语句的耗时下降且未引入新的高消耗语句
- 回归对比的方法与判定标准见 性能测试与基线 第 3.5 节
3.3 锁与并发问题诊断
锁相关的等待表现为并发下延迟上升、部分事务长时间不返回。事务隔离级别与锁行为的基础概念可结合 MySQL 服务配置 中的隔离级别参数理解,隔离级别、MVCC、加锁规则以及死锁与锁等待的处置流程见 mysql: 事务与锁。
诊断入口:
-- 当前锁等待关系:等待方、阻塞方、涉及对象
SELECT * FROM sys.innodb_lock_waits\G
-- 未提交事务及其持锁时长
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;
-- 元数据锁等待:通常由未提交的 DDL 或长事务持有
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';
-- 当前所有线程的锁等待事件
SELECT * FROM sys.innodb_lock_waits WHERE wait_age > '00:00:05';
常见锁问题与处置:
| 现象 | 常见原因 | 处置方向 |
|---|---|---|
| 行锁等待时间增长 | 长事务、更新顺序不一致、缺少合适索引导致锁范围扩大 | 缩短事务、统一加锁顺序、为条件列建索引减少扫描与锁定行数 |
| 元数据锁等待 | 长事务持有表锁、未提交的 DDL、备份与 DDL 并发 | 定位持有者并在确认影响后终止,把 DDL 安排到低峰 |
| 间隙锁等待 | 可重复读隔离级别下的范围条件、非唯一索引扫描 | 评估隔离级别,或调整条件与索引使扫描范围收窄 |
| 自增锁等待 | 并发插入受自增锁模式限制 | 使用交错自增锁模式,评估主键可连续性要求 |
| 死锁 | 多事务加锁顺序不一致 | 按错误日志中的死锁信息统一访问顺序,重试机制兜底 |
- 死锁发生后 InnoDB 会主动回滚其中一个事务,应用侧需要有重试逻辑,否则请求直接失败
- 锁等待的根因往往是事务过长,诊断时应同时关注事务持有时长与单事务内的语句数量
- 记录并保留锁等待与死锁事件的现场信息,便于按模式归类而不是逐次应急
3.4 连接与线程调优
取值依据:
max_connections依据应用实例数与单实例连接池上限之和确定,并预留管理连接与会话空间,通常取两者之和的 1.2 至 1.5 倍thread_cache_size依据连接建立速率确定:连接频繁建立与释放时提高该值,使线程复用替代线程创建- 提高
max_connections需要同步评估内存,按会话缓冲区大小乘以新增连接数估算增量占用 - 空闲连接超时参数的取值应大于业务中最长的一次空闲间隔,避免连接在事务间隙被服务端回收
连接池与数据库参数的配合:
| 连接池参数 | 配合的数据库参数 | 配合原则 |
|---|---|---|
| 最大连接数 | max_connections | 所有池上限之和不超过数据库上限 |
| 最小空闲连接 | thread_cache_size | 稳定持有一部分连接以复用线程 |
| 获取连接超时 | wait_timeout、net_read_timeout | 客户端超时早于服务端超时,便于快速失败并给出明确错误 |
| 空闲回收时间 | wait_timeout | 回收时间小于服务端超时,避免使用已被关闭的连接 |
| 连接有效性检测 | 无直接对应参数 | 取出连接时做轻量校验,避免失效连接引发请求失败 |
连接泄漏的识别与处置:
- 依据连接数与请求速率的关系判断,请求速率稳定而连接数持续上升即为泄漏迹象
- 从数据库侧按来源主机与账号统计连接分布,缩小到具体应用实例
-- 按来源主机与用户统计连接数
SELECT SUBSTRING_INDEX(HOST, ':', 1) AS host, USER, COUNT(*) AS conns
FROM information_schema.processlist GROUP BY host, USER ORDER BY conns DESC;
-- 长时间空闲且未执行语句的连接
SELECT id, user, host, command, time, state
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 600 ORDER BY time DESC;
- 处置优先级为修复应用侧连接获取与释放逻辑,而不是在数据库侧扩大上限
- 排查阶段可临时收紧空闲超时以回收连接,但会改变现场,需评估对业务的影响
高危:修改 max_connections、wait_timeout 等连接参数会影响所有在线会话,收紧空闲超时可能使业务在低峰后被强制断开重连。使用 SET PERSIST 写入的参数在重启后依然生效,变更前需记录原值并明确回滚命令。回滚方式:用 SET PERSIST 写回原值,或删除持久化变量后重启实例,重启前应确认重启窗口。
3.5 内存与刷盘调优
缓冲池调整的时机与幅度:
- 调整时机:命中率与磁盘读速率长期偏高、热点数据工作集明显大于缓冲池、主机存在充足空闲内存
- 调整幅度:单次调整不宜过大,因缓冲池扩容会触发页面换入,实例可能产生额外 IO;缩容会触发页面换出
- MySQL 5.7 起支持在线调整缓冲池大小,调整过程按分片逐步进行,对业务影响相对可控,但高负载下仍会引发延迟波动
- 调整后需观察命中率、磁盘读速率与主机内存水位的变化,确认收益与副作用
实例多缓冲池:
- 缓冲池分片的目的是减少并发访问缓冲池时的内部争用,每个分片有独立的锁与链表
- 分片数量通常在实例内存较大、并发写入高时提升,每片容量不足时收益有限
- 分片数量在实例运行期间调整需要重启,属于重启类变更,需按变更流程安排
刷脏相关参数的取舍与副作用:
| 参数方向 | 调大的效果 | 副作用 |
|---|---|---|
| 刷脏速率相关参数 | 单次刷脏力度更大,脏页比例更易受控 | 瞬时 IO 峰值升高,可能影响业务查询 |
| 日志文件容量 | 检查点频率降低,写入停顿减少 | 崩溃恢复时间变长,磁盘空间占用增加 |
| 日志缓冲区 | 大事务写入时减少日志刷盘次数 | 内存占用增加,崩溃时未落盘量增加 |
| 提交落盘策略 | 减少每次提交的落盘等待,写入吞吐提升 | 崩溃时可能丢失最近若干事务,与 RPO 目标冲突 |
- 提交落盘策略涉及数据安全性与性能的直接取舍,调整前需与业务方确认可接受的数据丢失范围
- 刷脏相关参数调整后观察重点是脏页比例、检查点频率与磁盘写入延迟,判断是否出现新的尖峰
高危:修改缓冲池大小、日志文件容量与落盘策略会对实例稳定性与数据安全性产生直接影响,日志文件容量的变更需要重启实例。变更前应在预发环境验证,记录参数原值与生效方式,并确认回滚路径。变更属于持久化配置的,需按 变更与发布管理 走评审与窗口安排,回滚方式为恢复原配置并重启,重启前需完成一次可用备份。
3.6 压测与基线
压测目的:在可控负载下测量实例的处理能力,为容量评估、参数变更与版本升级提供对比依据。压测类型、指标定义与瓶颈定位顺序的通用方法见 性能测试与基线 第 2.2 与 3.6 节。
sysbench 压测方式:
# 准备测试数据:表数量、表大小与线程数按目标规模设定
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=bench \
--mysql-password=... --mysql-db=sbtest \
--tables=16 --table-size=1000000 --threads=16 prepare
# OLTP 读写混合:衡量综合处理能力
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=bench \
--mysql-password=... --mysql-db=sbtest \
--tables=16 --table-size=1000000 --threads=64 --time=300 \
--report-interval=10 run
# 只读场景:衡量读能力与缓冲池效果
sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-user=bench \
--mysql-password=... --mysql-db=sbtest \
--tables=16 --table-size=1000000 --threads=64 --time=300 run
# 只写场景:衡量写入与刷盘能力
sysbench oltp_write_only --mysql-host=127.0.0.1 --mysql-user=bench \
--mysql-password=... --mysql-db=sbtest \
--tables=16 --table-size=1000000 --threads=64 --time=300 run
# 清理测试数据
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=bench \
--mysql-password=... --mysql-db=sbtest --tables=16 cleanup
场景选择:
| 场景 | 适用目的 | 关注指标 |
|---|---|---|
| 只读 | 评估读能力、缓冲池效果、索引设计 | 查询 QPS、响应时间分位值 |
| 只写 | 评估写入与刷盘能力、日志策略影响 | 写入 TPS、提交延迟、磁盘写延迟 |
| 读写混合 | 评估综合能力与并发上限 | TPS、锁等待、CPU 与 IO 利用率 |
| 递增并发 | 寻找吞吐拐点与资源饱和点 | 各并发档位的吞吐与延迟曲线 |
- 压测实例应与生产实例隔离,避免压测流量影响线上
- 测试数据规模需与生产工作集匹配,数据量过小时全部命中缓冲池,测得的结论不成立
- 递增并发压测用于找拐点:吞吐不再上升而延迟快速上升的并发档位即为实际承载上限
基线采集与归档:
- 基线包含硬件与版本信息、参数配置、数据规模、压测场景与并发、吞吐与延迟指标、主机资源指标
- 采集应在实例状态稳定后进行,预热阶段的数据单独标注不纳入基线
- 每次变更或升级后重新采集并按同一格式归档,形成可比对的时间序列
回归对比与结论表达:
- 对比同一场景、同一并发、同一数据规模下的指标,说明变化比例与置信情况
- 结论应指出发生变化的关键指标与可能原因,例如吞吐上升但 P99 延迟同时上升,说明提升以延迟波动为代价
- 回归判定标准与常见误区见 性能测试与基线 第 3.5 与 3.8 节
3.7 参数调优方法论
调优的顺序与约束:
- 先定位再调整:确认瓶颈层次与量化差距后再选择参数,未定位前的调整会让现场变化并掩盖真实原因
- 一次只改一个变量:多参数同时调整无法归因,出现异常时也难以回滚到中间状态
- 变更前后对照:用同一压测场景或同一时间窗口的同类指标对比,避免用不同负载下的数据比较
- 明确生效方式:区分仅会话生效、全局动态生效与重启生效,动态生效的改动在重启后可能被配置文件覆盖
生效方式对照:
| 变更方式 | 生效范围 | 重启后 | 适用场景 |
|---|---|---|---|
SET SESSION | 当前会话 | 失效 | 单会话临时调整 |
SET GLOBAL | 新会话及后续全局行为 | 失效 | 临时验证效果 |
SET PERSIST | 全局 | 保留 | MySQL 8.0 持久化调整,需记录原值 |
| 修改配置文件 | 全局 | 保留 | 需要长期生效的配置 |
| 重启实例 | 全部 | 保留 | 仅重启生效的参数 |
SET PERSIST写入的值优先于配置文件,出现"改了配置文件却不生效"的情况时需要同时检查持久化变量- 运行时调整与配置文件不一致会造成配置漂移,长期维护需要在变更后同步配置文件
回滚方案与变更评审:
- 每次变更前记录参数原值、生效方式与回滚命令,回滚命令应能直接执行而不需要现场推导
- 涉及重启、数据安全性或全局影响的变更按 变更与发布管理 分级评审,明确影响范围、验证方式与回滚时限
- 变更后按固定间隔采集指标,确认收益并在约定观察期内保留回滚窗口,观察期结束后更新配置文档
3.8 监控与告警衔接
数据库侧关键指标与阈值建议:
| 指标 | 含义 | 阈值建议方向 |
|---|---|---|
Threads_connected 与上限比 | 连接使用率 | 持续超过 80% 关注,接近上限告警 |
Threads_running | 并发执行线程数 | 显著高于 CPU 核数并持续时关注 |
| 活动会话堆积与最长执行时间 | 是否存在长事务或阻塞 | 超过业务超时阈值告警 |
| 缓冲池命中率与磁盘读速率 | 读效率 | 命中率下降配合磁盘读上升时告警 |
| 脏页比例 | 刷脏压力 | 持续高位提示写入瓶颈 |
| 行锁等待与元数据锁等待 | 并发冲突 | 出现等待超过设定时长即告警 |
| 复制延迟 | 从库滞后 | 超过业务可容忍延迟告警 |
| 慢查询数量与总耗时 | SQL 层效率 | 与历史同期比较,突增时告警 |
| 磁盘使用率与增长速率 | 容量 | 阈值早于写满时间点触发 |
与主机侧监控的分工:
- 主机侧负责资源层指标:CPU、内存、swap、磁盘 IO 与容量、网络,指标类型与采集模型见 监控与告警 第 2.1 与 2.2 节
- 数据库侧负责服务层指标:连接、线程、缓冲池、锁等待、复制延迟、慢查询,通过导出器采集为指标
- 两侧指标的关联是定位的关键:数据库侧的写入延迟上升需要与主机侧磁盘写延迟对照,才能区分是参数问题还是介质能力问题
- 告警分级与路由、抑制与静默的配置原则见 监控与告警 第 2.6 与 3.3 节
巡检项设计:
- 巡检覆盖趋势性与配置性问题,与告警处理突发故障的分工见 巡检与健康检查 第 1.3 节
- 数据库侧巡检项建议包含:连接使用率、活动会话与长事务、主从延迟与复制错误、锁等待统计、慢查询数量趋势、表与索引的空间与碎片、统计信息新鲜度、备份与 binlog 归档状态
- 巡检结果按周期归档并做趋势对比,单点值与基线的偏离比绝对值更有意义
- 巡检判据需要与业务特征结合,避免套用统一阈值造成误判
3.9 典型性能问题案例
突发 IO 升高:
- 表现:磁盘写利用率与写延迟同时上升,写入 TPS 下降,应用侧出现超时
- 定位:先确认是否为数据库进程引起,按会话与语句定位到具体的批量写操作;区分数据文件写入与日志文件写入
- 常见原因:批量导入或大范围更新、检查点集中推进、备份任务与业务重叠、其他进程占用磁盘
- 处置:暂停或限速批量任务,把备份移出业务高峰,必要时临时提高刷脏力度以加快脏页消化
周期性抖动:
- 表现:固定时间点出现延迟尖峰,其余时间正常
- 定位:把尖峰时间与计划任务、备份、日志轮转、统计信息更新、批量作业的时间表对照
- 常见原因:集中刷脏与检查点、定时任务叠加、缓存批量失效后回源、连接池定时回收
- 处置:把任务在时间上分散,错开备份与批量作业,评估刷脏参数使写入压力更均匀
连接数打满:
- 表现:
Threads_connected达到上限,新连接被拒绝或获取连接超时 - 定位:区分连接泄漏与请求堆积,按来源主机与账号统计连接分布,观察连接数与请求速率的关系
- 常见原因:应用未释放连接、上游重试风暴、慢 SQL 占住连接、实例响应变慢导致客户端重连
- 处置:应用侧修复连接管理,临时回收长期空闲连接,同时定位并解决占住连接的慢 SQL
慢 SQL 拖垮实例:
- 表现:实例 CPU 或 IO 被打满,其他语句普遍变慢
- 定位:按总延迟排序的语句摘要定位主要贡献者,配合执行计划确认退化原因
- 常见原因:统计信息过期导致执行计划变化、数据量增长使原索引失效、缺少分页的大范围查询
- 处置:恢复或固定执行计划、补充合适索引、限制单次查询范围,并在应用侧加入超时与限流
大事务导致的复制延迟:
- 表现:主库写入正常,从库延迟持续增长,从库重放线程 CPU 高但应用速度慢
- 定位:核对从库重放位点与主库位点差距,检查主库是否存在长事务或大批量操作,复制机制见 主从同步原理 第 2.7 节
- 处置:把大批量写操作拆分为小批次提交,评估并行复制配置,避免在复制压力大的时段执行批量作业
批量导入引发的压力:
- 表现:导入期间实例写入压力陡增,缓冲池与日志压力上升,业务查询变慢
- 定位:确认导入语句的单事务规模、提交频率与并发度,对照主机侧 IO 指标
- 处置:控制批次大小与并发数,降低导入频率,把导入安排在业务低峰,必要时在从库或独立实例执行
四、总结
- 性能问题需要从现象落到分层指标,应用、SQL、存储引擎与主机四层之间存在明确的因果关系,定位依赖逐层对照
- 常见瓶颈包括慢 SQL 与缺索引、锁等待、连接耗尽、缓冲池不足、磁盘饱和、刷脏与检查点、复制延迟与大事务,诊断任务是把现象归到其中一类
performance_schema提供细粒度采集但会带来开销,sys库以视图封装常见问题,分析需按 SQL、表、等待事件三个维度展开- 慢查询日志的判读重点在锁定时间、扫描行数与返回行数的关系,聚合分析应同时看总耗时与单次耗时
- 连接问题的根源通常在连接池参数配合与应用侧连接管理,扩大数据库上限只是缓解手段
- 缓冲池命中率需结合工作集与磁盘读速率判断,内存不足会先通过 swap、临时表落盘与分配失败表现出来
- 刷盘与日志策略直接关联写入延迟与数据安全性,调整属于需要评审与回滚预案的变更
- 诊断顺序应先确认现象范围与活动会话,再进入存储引擎与 SQL 层,最后与主机指标对照,避免先改参数
- 慢 SQL 优化遵循定位、核对执行计划、改写与索引调整、验证回归的闭环,索引调整需权衡写入成本
- 参数调优遵循先定位、一次一个变量、变更前后对照、明确生效方式与回滚方案的原则,并按变更流程评审
- 压测与基线为所有优化提供判定依据,场景需覆盖只读、只写与读写混合,结论应基于同条件下的回归对比
- 监控与巡检分别负责突发故障与趋势问题,数据库侧与主机侧指标需联合判读才能区分参数问题与资源瓶颈