返回首页

23｜慢查询与监控分析

2026-06-18 00:11 Mysql 20 阅读 #运维与高可用

从日志采集到定位优化

适用版本：MySQL 8.0.x

慢查询问题几乎是每个 MySQL 使用者都会遇到的运维主题。很多场景里，业务方给到的反馈并不直接：

  • 页面偶尔卡顿；
  • 接口高峰期超时；
  • 数据库 CPU 升高；
  • 主从延迟突然变大；
  • 某些 SQL 以前很快，现在越来越慢。

这时候，如果没有一套稳定的慢查询分析与监控方法，排查过程就很容易陷入“凭感觉优化”。而在生产环境中，真正靠谱的方式应该是：

先采集，后观察；先定位，后优化；先证据，后结论。

这篇文章就从 MySQL 8.0 运维视角，系统梳理慢查询日志、执行计划、性能视图和监控指标的配合使用方式。

一、问题背景

为什么慢查询问题总是反复出现？常见原因主要有这几类：

  1. SQL 写法不合理：条件不走索引、范围过大、排序和分组代价高。
  2. 索引设计不合适：缺失索引、联合索引顺序不对、冗余索引过多。
  3. 数据量增长：原来几十万行时正常，增长到几千万行后性能恶化。
  4. 负载变化：高峰期并发上来后，锁等待、I/O、CPU 竞争加剧。
  5. 系统层面瓶颈：磁盘、内存、网络、刷盘参数、复制延迟等影响数据库表现。

不少同学在排查时容易一上来就改 SQL，但实际上慢查询只是“现象”，根因可能出现在：

  • 执行计划变化
  • 统计信息不准确
  • Buffer Pool 命中率下降
  • 临时表和排序落盘
  • 行锁冲突
  • 主库写入压力过大

因此，慢查询分析不能只看一条 SQL，而要把 SQL、索引、执行计划、监控指标放在一起看。

二、先建立一个完整分析框架

排查慢查询，建议遵循下面这条主线：

  1. 发现问题：通过慢查询日志、监控报警、业务反馈发现异常。
  2. 确认范围：是单条 SQL 变慢，还是整体数据库负载升高。
  3. 抓取证据：拿到 SQL 文本、执行时长、扫描行数、执行次数。
  4. 分析执行路径：看 EXPLAIN、EXPLAIN ANALYZE、索引命中情况。
  5. 结合系统指标：CPU、I/O、连接数、锁等待、临时表、排序次数。
  6. 制定优化动作：改 SQL、补索引、拆热点、控流、调整参数。
  7. 回归验证：确认优化后时延、资源使用、稳定性是否改善。

这个流程看起来不复杂，但真正难的是“每一步都要有数据支撑”。

三、方法与方案：先打开慢查询观测能力

方案一：开启慢查询日志

慢查询日志是最基础、也是最实用的入口。

查看当前配置：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

临时开启示例：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

说明：

  • slow_query_log=ON：开启慢查询日志。
  • long_query_time=1：执行时间超过 1 秒的 SQL 记入日志。
  • log_queries_not_using_indexes=ON：未使用索引的语句也记录下来。

在配置文件中持久化时，通常会写成：

[mysqld]
slow_query_log=ON
long_query_time=1
log_queries_not_using_indexes=ON

方案二：配合 performance_schema 与 sys schema

MySQL 8.0 的 performance_schema 和 sys 库提供了更结构化的性能视角，适合做聚合分析。

例如查看最耗时的语句摘要：

SELECT *
FROM sys.statement_analysis
ORDER BY avg_latency DESC
LIMIT 10;

或者查看总耗时高的语句：

SELECT digest,
       exec_count,
       avg_latency,
       max_latency,
       rows_examined,
       rows_sent
FROM sys.statement_analysis
ORDER BY sum_latency DESC
LIMIT 10;

相比只看日志，这类视图的优点是：

  • 可以做聚合统计
  • 更容易找出高频热点 SQL
  • 能看平均耗时、总耗时、扫描行数等指标

四、慢查询日志到底怎么看

1. 关注的不只是 Query_time

慢日志中，常见字段包括：

  • Query_time：总耗时
  • Lock_time：等待锁的时间
  • Rows_examined：扫描行数
  • Rows_sent：返回行数

一个常见误判是：只看到 Query_time 高，就认为 SQL 写得差。

实际上要结合其他字段判断：

  • Lock_time 高：可能是锁等待，不一定是 SQL 本身慢。
  • Rows_examined 很大、Rows_sent 很小：通常意味着过滤效率差，索引可能有问题。
  • SQL 执行单次不慢，但次数极多：累计总耗时仍可能很高。

2. 优先找“高频”和“高总耗时”SQL

单次执行 5 秒的 SQL 固然值得关注，但如果某条 SQL 每秒执行几百次、每次 100ms，它对整体系统的压力可能更大。

所以分析时建议分三类看：

  1. 单次极慢：影响用户请求体验。
  2. 高频中慢：消耗大量 CPU 与 I/O。
  3. 偶发尖刺：可能与锁、抖动、计划变化有关。

3. 不要只盯着 SELECT

更新类语句同样会产生严重问题，例如：

  • 大范围 UPDATE
  • 无索引 DELETE
  • 批量 INSERT 造成刷盘压力
  • DDL 导致元数据锁等待

生产环境里，“慢查询”并不只等于“慢 SELECT”。

五、实战步骤：从一条慢 SQL 开始分析

假设我们发现这样一条 SQL 在高峰期经常超过 2 秒：

SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 10001
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

步骤 1：先看执行计划

EXPLAIN SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 10001
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

关注字段：

  • type：访问类型，是否从 ALL 提升到 range/ref/const
  • key：是否命中预期索引
  • rows：预计扫描行数
  • Extra：是否出现 Using filesort、Using temporary

如果看到：

  • type = ALL
  • key = NULL
  • Using filesort

基本可以判断：这条 SQL 缺失合适的联合索引。

步骤 2：用 EXPLAIN ANALYZE 看实际执行

MySQL 8.0 提供了更实用的工具：

EXPLAIN ANALYZE
SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 10001
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

它会给出更接近实际执行过程的信息，比如：

  • 各节点真实耗时
  • 实际扫描行数
  • 估算与实际偏差

如果优化器估算很乐观，但实际扫描量很大，往往说明统计信息或索引设计存在问题。

步骤 3：补充合适索引

针对上面的 SQL，更合理的索引通常是：

ALTER TABLE orders
ADD INDEX idx_user_status_created_at (user_id, status, created_at DESC);

为什么不是只建 user_id 单列索引？

因为查询条件和排序都需要被考虑进去：

  • WHERE user_id = ?
  • AND status = ?
  • ORDER BY created_at DESC

如果索引设计合理，MySQL 能更高效地定位数据并减少额外排序。

步骤 4：重新验证

索引加完后，再次执行：

EXPLAIN ANALYZE
SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 10001
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

重点比较：

  • 扫描行数是否明显下降
  • 是否不再出现全表扫描
  • 总耗时是否下降
  • CPU 和 I/O 指标是否改善

六、监控分析时要重点关注哪些指标

慢查询分析不能脱离监控，下面这些指标最值得建立长期观察。

1. QPS / TPS

  • QPS：每秒查询量
  • TPS：每秒事务量

用途：判断是业务流量变大，还是单条 SQL 性能变差。

2. 连接数与活跃线程

关注：

  • 当前连接数
  • 活跃线程数
  • 等待连接数
  • 连接创建速率

如果连接数陡增，可能是上游重试、连接池失控，或数据库响应变慢导致连接堆积。

3. Buffer Pool 命中率

InnoDB Buffer Pool 是最核心的内存缓存区之一。

如果命中率下降，磁盘读压力往往会上升，慢查询会明显增多。

4. 临时表与排序落盘

重点关注：

  • 内存临时表数量
  • 磁盘临时表数量
  • Sort merge passes

如果大量查询发生临时表落盘或文件排序，性能通常会抖动明显。

5. 锁等待

慢 SQL 不一定慢在执行本身，也可能慢在等待：

  • 行锁等待
  • 元数据锁（MDL）等待
  • 事务长时间未提交

例如在线 DDL、长事务、批量更新，都会把普通查询拖慢。

6. 主从延迟

在主从架构中，一条重 SQL 可能不仅拖慢主库，还会在从库回放时放大问题，造成复制延迟。

所以慢查询问题通常也要结合：

SHOW REPLICA STATUS\G

一起看。

七、用系统视图快速定位热点问题

1. 查看语句摘要

SELECT query,
       exec_count,
       total_latency,
       avg_latency,
       rows_examined,
       rows_sent
FROM sys.x$statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

适合找：

  • 总耗时最高的 SQL
  • 高频热点 SQL
  • 扫描量异常大的 SQL

2. 查看表 I/O 热点

SELECT *
FROM sys.schema_table_statistics
ORDER BY rows_fetched DESC
LIMIT 10;

适合找：

  • 哪些表访问最频繁
  • 是否存在热点大表
  • 某张表是否突然成为瓶颈

3. 查看索引使用情况

SELECT *
FROM sys.schema_index_statistics
ORDER BY rows_selected DESC
LIMIT 10;

这类视图适合辅助判断：

  • 常用索引有哪些
  • 是否存在长期几乎不用的索引
  • 是否需要清理冗余索引

八、常见优化方法总结

方法一：补充或调整索引

最常见也最有效，但要遵循两个原则：

  1. 索引为查询服务，不是越多越好。
  2. 联合索引顺序要结合过滤条件、排序、选择性来设计。

方法二：缩小扫描范围

例如：

  • 避免不必要的全表扫描
  • 给分页增加更合理条件
  • 避免一次查太多历史数据
  • 用归档表拆分冷数据

方法三：减少回表与排序代价

如果查询字段很多、过滤又不精准，MySQL 可能需要大量回表。

有时可以通过：

  • 合理的覆盖索引
  • 更小的结果集
  • 更明确的过滤条件

来降低成本。

方法四：处理长事务和锁冲突

有些 SQL 慢，并不是因为它执行笨，而是它被别人堵住了。

排查时要看：

  • 是否存在未提交的大事务
  • 是否有批量更新长期占锁
  • 是否做了高风险 DDL
  • 是否出现元数据锁等待

方法五：业务分流与读写拆分

如果热点查询过于集中，单纯调 SQL 不一定够。这时还要考虑：

  • 读写分离
  • 缓存前置
  • 热点数据下沉
  • 分库分表或归档拆分

九、常见误区

误区 1：把 long_query_time 设得越低越好

阈值太低会记录大量“并不真的有问题”的 SQL，噪音非常大。实际中应该结合业务特点设置，比如 0.5 秒、1 秒或 2 秒，而不是盲目追求极低阈值。

误区 2：看到 Using filesort 就一定有问题

Using filesort 不是绝对错误，要看数据量、频率和代价。如果结果集很小、调用频率也低，未必值得专门优化。

误区 3：只根据 EXPLAIN 结果下结论

EXPLAIN 是估算，不是实际执行。MySQL 8.0 下更建议结合 EXPLAIN ANALYZE 看真实行为。

误区 4：索引越多越安全

索引会增加：

  • 写入成本
  • 存储成本
  • 优化器选择复杂度

过多索引不但不能提升性能，反而可能拖慢写入并增加维护负担。

误区 5：慢查询一定是数据库问题

有时候根因在应用侧，例如：

  • N+1 查询
  • 大量重复请求
  • 没有分页
  • 连接池参数不合理
  • 高峰流量突刺

因此必须把数据库现象和业务调用链一起看。

十、一个可落地的生产排查流程

当线上反馈“数据库变慢”时，可以按下面顺序执行：

第一步：确认是整体慢还是个别 SQL 慢

看监控：

  • QPS / TPS
  • CPU / IOPS
  • 活跃线程数
  • 连接数
  • 慢查询数量

第二步：抓取最近热点 SQL

来源包括：

  • 慢查询日志
  • sys.statement_analysis
  • 应用侧 APM 或接口追踪

第三步：对热点 SQL 做执行计划分析

重点看：

  • 是否走索引
  • 扫描行数是否过大
  • 是否存在排序、临时表、回表

第四步：检查锁与事务

确认是否存在：

  • 长事务
  • 锁等待
  • DDL 阻塞
  • 复制线程卡住

第五步：制定最小风险优化动作

优先级通常建议是：

  1. 改查询条件或分页方式
  2. 加索引或调整索引顺序
  3. 降低热点访问压力
  4. 调整数据库参数
  5. 评估架构层扩容或拆分

第六步：灰度验证并持续观察

优化后不要只看一两分钟，而要持续关注：

  • 平均耗时
  • P95 / P99
  • CPU 和 I/O 变化
  • 慢日志数量趋势
  • 主从延迟

十一、小结

MySQL 慢查询与监控分析，本质上是在做一件事：

通过数据证据，把“感觉很慢”转化成“知道哪里慢、为什么慢、应该怎么改”。

你可以把本文内容总结成五个抓手：

  1. 先开观测：慢查询日志、performance_schema、sys 视图要可用。
  2. 先看热点：别只盯单条最慢 SQL，也要看高频、高总耗时语句。
  3. 结合执行计划：EXPLAIN 和 EXPLAIN ANALYZE 是定位核心工具。
  4. 结合系统指标：CPU、I/O、连接、锁等待、主从延迟一起看。
  5. 优化后要验证：性能优化不是“改完就结束”，而是要看结果是否稳定。

如果你刚开始建立 MySQL 监控体系，建议优先落实三件事：

  • 开启并保留慢查询日志；
  • 养成用 EXPLAIN ANALYZE 分析 SQL 的习惯；
  • 为核心实例建立连接数、活跃线程、慢查询数量、主从延迟等基础监控。

把这些基础工作做扎实，后续处理慢查询问题就会从“临时救火”逐步变成“可重复的排查流程”。

📝 版权声明：本文为原创技术博客，转载请注明出处。

如文章中存在错误或不准确之处，欢迎在评论区指正，感谢您的阅读与支持！

上一篇

22｜MySQL 备份与恢复
下一篇

24 | MySQL 高可用方案概览