MySQL 慢查询诊断与 EXPLAIN ANALYZE 实战指南

本文面向 MySQL 5.7/8.0 用户，围绕“如何定位与优化慢查询”给出从日志采集、执行计划分析到索引与参数调优的完整路径，所有参数与命令均为可验证与可复现实操。

## 适用版本与前提

• 支持版本：MySQL 5.7/8.0（`EXPLAIN ANALYZE` 需 8.0.18+）。
• 性能采集建议：启用 `performance_schema`（8.0 默认启用）。
• 依赖工具（任选其一）：`mysqldumpslow`（官方脚本）或 `pt-query-digest`（Percona Toolkit）。

## 开启慢查询日志（可动态生效）

在生产环境建议按“先表后文件”的原则，仅启用文件输出，避免在高并发下写表带来的额外开销。

-- 设置为文件输出
SET GLOBAL log_output = 'FILE';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设定阈值（单位：秒），建议先从 1s 起步
SET GLOBAL long_query_time = 1;

-- 自定义日志文件位置（可选，视部署环境而定）
-- SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

验证：执行一条超过阈值的 SQL，检查慢日志文件是否新增记录；如需长期持久化配置，写入 `my.cnf` 并重启生效。

## 收集与初步分析

• `mysqldumpslow`：按“出现次数/平均时间”等维度汇总慢日志。

mysqldumpslow -s t -t 20 /path/to/slow.log   # 按时间排序，取 Top 20
mysqldumpslow -s c -t 20 /path/to/slow.log   # 按次数排序，取 Top 20

• `pt-query-digest`：更细粒度的指纹聚合与指标统计，便于对比优化前后效果。

pt-query-digest /path/to/slow.log > report.txt

## 构造示例表与索引

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  KEY idx_status_created (status, created_at),
  KEY idx_user_created (user_id, created_at),
  KEY idx_user_status_created (user_id, status, created_at)
) ENGINE=InnoDB;

INSERT INTO orders VALUES
  (1, 1001, 1, 99.00, '2024-01-01 10:00:00'),
  (2, 1001, 2, 19.00, '2024-01-01 11:00:00'),
  (3, 1002, 1, 29.00, '2024-01-01 12:00:00');

## 执行计划：EXPLAIN 与 EXPLAIN ANALYZE

场景 1：仅按 `status` + 时间范围检索（可能触发较大扫描与回表）。

EXPLAIN SELECT id, amount
FROM orders
WHERE status = 1
  AND created_at BETWEEN '2024-01-01 09:00:00' AND '2024-01-01 13:00:00';

观察要点：`type`（访问类型，期望 `range`/`ref` 而不是 `ALL`）、`rows`（估算行数）、`key`/`key_len`（命中索引与前缀长度）、`Extra`（是否出现 `Using where`/`Using filesort`/`Using temporary`）。

场景 2：8.0 采用 `EXPLAIN ANALYZE` 获取真实执行路径与耗时。

EXPLAIN ANALYZE SELECT id, amount
FROM orders
WHERE user_id = 1001
  AND status = 1
  AND created_at BETWEEN '2024-01-01 09:00:00' AND '2024-01-01 13:00:00';

解读：`EXPLAIN ANALYZE` 会实际执行查询并返回每个算子（如 range scan、filter）的耗时与行数，优先确保命中复合索引 `idx_user_status_created`，减少回表与不必要的排序。

## 写法与索引优化策略

• 复合索引的“最左前缀”原则：将过滤选择性最高的列放在前（如 `user_id` → `status` → `created_at`）。
• 避免函数包裹与隐式转换：如 `DATE(created_at) = '2024-01-01'` 会导致无法使用索引，建议使用范围条件。
• 覆盖索引：只查询索引包含的列可减少回表（如查询 `status, created_at` 时命中 `idx_status_created`）。
• 降低排序与临时表：尽量让 `ORDER BY` 与索引顺序一致；需要分页时优先使用“延续条件 + 主键”方式。

## 利用 performance_schema 与 sys 库定位热点

MySQL 5.7/8.0 提供 `performance_schema` 与 `sys` 辅助视图，可快速定位高频或慢速 SQL。

-- Top SQL 指纹（摘要）
SELECT *
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 20;

-- 表级读写统计（sys）
SELECT *
FROM sys.schema_table_statistics
ORDER BY rows_read DESC
LIMIT 20;

## 参数建议（按场景取舍）

• `innodb_buffer_pool_size`：专用实例建议分配至物理内存的 60%–80%；容器/多实例场景需结合可用内存与页抖动风险保守设置。
• `innodb_flush_log_at_trx_commit`：
• `1`（默认）：每次提交同步刷盘，最强一致性，写入更稳但开销更高。
• `2`：每次提交写入 OS 缓存，按周期刷盘，牺牲少量持久性换取吞吐提升。
• `max_connections`：结合连接池与并发特征设置，避免打满导致排队与拒绝；连接暴涨通常是慢查询的“症状”，根因在于 SQL 本身与索引设计。

## 注意事项（版本差异与弃用特性）

• MySQL 8.0 移除了查询缓存（Query Cache），请勿依赖该特性做性能优化。
• `SHOW PROFILE` 在 MySQL 8.0 已移除，建议使用 `EXPLAIN ANALYZE` 与 `performance_schema` 进行分析。
• 慢日志的表输出（`log_output='TABLE'`）仅用于短期诊断，不建议在高并发生产场景长期开启。

## 上线前检查清单

• 慢日志是否开启且阈值合理（先 1s，再逐步压低）？
• Top 指纹是否完成执行计划分析与归因（索引/写法/数据分布）？
• 索引是否命中预期、避免函数包裹与隐式类型转换？
• 关键报表/接口是否具备 `EXPLAIN ANALYZE` 基线数据以便回归对比？
• 参数调整是否经过压测与观察（buffer pool、flush 策略）？

## 结语

慢查询优化的本质是“可度量、可解释、可验证”。以慢日志定位、以执行计划归因、以索引与写法落地，再辅以适度的参数调优，才能稳定地把性能瓶颈转化为可持续的吞吐提升。