MySQL 8.0 EXPLAIN ANALYZE 与性能画像：执行计划、成本与回归验证

本文面向 MySQL 8.0 的工程团队，目标是把“计划成本”与“真实耗时”打通，形成可复现、可回归的性能画像与优化闭环。

## 适用版本与前提

• EXPLAIN ANALYZE 支持：MySQL 8.0.18+（实际运行并返回每步耗时与行数）。
• 存储引擎：InnoDB（默认），示例依赖其索引与统计信息。
• 基础参数建议：
• `optimizer_switch` 使用默认，避免关闭影响生成计划判断。
• 开启 `performance_schema` 便于采集全局指标（可选）。

## 示例表与索引（可直接复现）

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_user_created (user_id, created_at),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;

-- 准备 10 万行数据（略）。确保两个组合索引均被命中于不同查询场景。

## EXPLAIN 与 EXPLAIN ANALYZE 的差异

• `EXPLAIN`：显示“计划”层面的访问类型（`type`）、估计行数（`rows`）、过滤率（`filtered`）、可能使用索引（`possible_keys`）等，不执行查询。
• `EXPLAIN ANALYZE`：实际执行查询，并返回每个算子（operator）的真实耗时、读取行数、回表次数与 buffer 命中情况（在 8.0 中以文本树形式呈现）。

示例对比：

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND created_at >= '2024-01-01' LIMIT 50;

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001 AND created_at >= '2024-01-01' LIMIT 50;

观察要点（可验证）：

• 计划估算行数（`rows`）与实际读取行数差距是否过大（统计信息失真信号）。
• `Using index`/`Using where` 的组合是否合理；是否出现 `filesort` 或 `range` 扫描导致额外成本。
• LIMIT 是否在上游就“剪枝”（减少下游回表与过滤工作）。

## 典型场景画像与优化

场景 A：等值 + 范围检索命中联合索引（优先走最左前缀）

EXPLAIN ANALYZE SELECT id, amount
FROM orders
WHERE user_id = 1001
  AND created_at BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY created_at DESC
LIMIT 100;

优化要点：

• 选择性高的列放在组合索引前部（如 `user_id, created_at`），避免产生 `filesort` 与多余回表。
• 若 ORDER BY 与索引顺序一致且覆盖列足够，可形成覆盖扫描，显著降低 I/O。

场景 B：状态聚合与过滤（可能触发范围扫描）

EXPLAIN ANALYZE SELECT status, COUNT(*)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY status;

优化要点：

• 针对 `status, created_at` 的联合索引可降低回表与范围扫描代价。
• 如需更优 COUNT 聚合，可评估物化统计或异步聚合表（业务允许前提下）。

## 统计信息与直方图（误差归因）

• 更新统计：`ANALYZE TABLE orders;`（更新索引基数与分布，验证前后计划是否变化）。
• 直方图（8.0）：可为非索引列建立分布估计，改善选择性判断。

-- 为 amount 建立直方图（示例）
ANALYZE TABLE orders UPDATE HISTOGRAM ON amount WITH 100 BUCKETS;
SHOW HISTOGRAM ON orders amount;

校验点：

• 重新执行 `EXPLAIN/EXPLAIN ANALYZE`，验证 `rows`/过滤率与实际读取行数的收敛情况。
• 如计划仍不稳定，检查数据倾斜与列相关性（联合索引列间强相关时，估算易失真）。

## 回归验证与基线建立

• 指标采集：记录关键查询的 `query_time`、`rows_examined`、返回行数与 `EXPLAIN ANALYZE` 文本摘要。
• 基线方法：
• 选取 3–5 个核心查询作为基准，建立“索引/统计/数据规模”三维度的回归用例。
• 每次发布前后对比 p95/p99 耗时与 `rows_examined` 是否回归到基线范围内。
• 工具协同：结合 `performance_schema`/慢查询日志与 `pt-query-digest` 汇总热点。

## 注意事项

• `EXPLAIN ANALYZE` 会实际执行查询，注意在生产环境评估代价与加 LIMIT/条件护栏。
• 统计信息更新可能改变计划，需绑定到发布/迁移流程，并进行上线前回归。
• 对跨分区或超大范围扫描的查询，优先评估归档/分区策略而非仅靠索引微调。

## 结语

通过将 EXPLAIN 的“计划视角”与 EXPLAIN ANALYZE 的“真实执行”打通，并结合统计信息与直方图的校准，可以把优化从“拍脑袋”转为“数据驱动”。用少量核心基准维持稳定的性能画像，是长期可维护的工程实践。