MySQL 8.0 慢查询诊断与索引优化 SOP

版本：v1.0 | 适用：MySQL 8.0.x / InnoDB | 编写：林望舒-131（数据工程 / 性能调优）

1. 背景与适用范围

本 SOP 面向日常维护 MySQL 8.0 + InnoDB 的 DBA 与后端工程师，覆盖「发现慢查询 → 定位根因 → 给出索引/SQL 优化方案 → 验证 → 预防复发」的完整闭环。适用场景包括但不限于：多表 JOIN + 子查询、范围扫描导致索引失效、大表 OFFSET 深翻页、函数包裹、隐式类型转换等典型慢查询类型。

不适用：复制延迟排查（需专门的复制拓扑排查 SOP）、存储引擎级调优（如 buffer pool 参数调优需独立文档）、分布式分库分表后的跨片查询优化。

2. 诊断流程（标准 6 步）

Step 1：确认慢查询存在并采样

-- 开启慢日志（若未开启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 0.5 秒阈值，生产可按需调整

-- 实时查看当前慢查询
SHOW PROCESSLIST;
-- 或用 performance_schema 抓取最近 100 条耗时 > 1s 的语句
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms,
       MAX_TIMER_WAIT/1000000000 AS max_ms
FROM performance_schema.events_statements_history_long
WHERE TIMER_WAIT/1000000000 > 1
ORDER BY MAX_TIMER_WAIT DESC LIMIT 100;

Step 2：离线聚合分析

# 用 pt-query-digest 聚合慢日志，按平均耗时排序
pt-query-digest --limit 50 --orderby avg_time \
  --group-by fingerprint /var/log/mysql/slow.log > digest_report.txt

Step 3：单条 EXPLAIN 定位

-- 基础 EXPLAIN
EXPLAIN SELECT ... ;
-- 详细 JSON（含 cost 估算、实际扫描行数、索引选择原因）
EXPLAIN FORMAT=JSON SELECT ...;

Step 4：确认 InnoDB 内部状态

SHOW ENGINE INNODB STATUS;
-- 重点看：TRANSACTIONS 段（长事务）、LOG 段（redo 刷写压力）、
-- BUFFER POOL AND MEMORY 段（命中率）、ROW OPERATIONS 段（锁等待）

Step 5：索引使用确认

-- 查看表上所有索引
SHOW INDEX FROM your_table;
-- 确认查询是否命中预期索引
EXPLAIN SELECT ... ;  -- 看 key 列

Step 6：修复 + 回归验证

修复后重跑 EXPLAIN 对比，确认：

• type 从 ALL/index 升级为 ref/range/const
• rows（估算扫描行数）下降一个数量级以上
• Extra 无 Using temporary / Using filesort（或已消除）
• 实际耗时（SELECT BENCHMARK 或应用侧 P99）下降 ≥ 50%

3. 典型场景（5 个，均可本地 docker 复现）

复现方式：docker run -d --name mysql8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8.0，然后 docker exec -it mysql8 mysql -uroot -proot 执行下方 DDL + INSERT。

场景 1：函数包裹导致索引失效

问题描述：对索引列使用函数（如 DATE(created_at)），B+ 树存储的是原始值，函数表达式无法利用索引有序性，退化为全表扫描。

复现 DDL + 数据：

CREATE TABLE t_order (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(64) NOT NULL,
  user_id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  INDEX idx_created (created_at),
  INDEX idx_user_created (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入 20 万行（本地复现用 2 万行即可观察 EXPLAIN 差异）
INSERT INTO t_order(order_no, user_id, created_at, amount, status)
SELECT CONCAT('ORD', seq), FLOOR(RAND()*10000),
       DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*365) DAY),
       ROUND(RAND()*10000, 2), FLOOR(RAND()*5)
FROM information_schema.columns a
CROSS JOIN (SELECT @rownum:=@rownum-1 AS seq FROM information_schema.columns b, (SELECT @rownum:=20000) r) c
LIMIT 20000;

慢 SQL（优化前）：

-- 全表扫描：idx_created 失效
SELECT COUNT(*) FROM t_order WHERE DATE(created_at) = '2026-10-20';
-- EXPLAIN: type=ALL, rows≈20000, Extra=Using where

修复方案 A（改写 SQL，推荐）：

-- 改为范围查询，命中 idx_created
SELECT COUNT(*) FROM t_order
WHERE created_at >= '2026-10-20 00:00:00'
  AND created_at < '2026-10-21 00:00:00';
-- EXPLAIN: type=range, key=idx_created, rows≈54, Extra=Using index condition

修复方案 B（生成列 + 索引，适用于频繁按天聚合）：

ALTER TABLE t_order ADD COLUMN created_date DATE GENERATED ALWAYS AS (DATE(created_at)) STORED,
ADD INDEX idx_created_date (created_date);
-- 之后 WHERE created_date = '2026-10-20' 可走索引

取舍说明：方案 A 无需 DDL、无额外存储，适合偶发查询；方案 B 适合每天高频按天统计（如报表），代价是每行多 3 字节 + 一个索引维护开销。若业务已按 user_id 维度查询，直接用 idx_user_created 更优。

场景 2：隐式类型转换导致索引失效

问题描述：索引列为 VARCHAR，查询条件用整数比较（或反之），MySQL 对索引列执行隐式 CAST，索引失效。

复现 DDL + 数据：

CREATE TABLE t_merchant (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  merchant_code VARCHAR(32) NOT NULL,
  name VARCHAR(128) NOT NULL,
  INDEX idx_merchant_code (merchant_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_merchant(merchant_code, name) VALUES
('M001','Alpha'),('M002','Beta'),('M003','Gamma'),
('M010','Delta'),('M100','Epsilon'),('M999','Zeta');

慢 SQL（优化前）：

-- merchant_code 是 VARCHAR，传入整数 10 → 隐式 CAST 全表扫描
SELECT * FROM t_merchant WHERE merchant_code = 10;
-- EXPLAIN: type=ALL, key=NULL, Extra=Using where

修复方案：

-- 修正类型：传字符串
SELECT * FROM t_merchant WHERE merchant_code = 'M010';
-- EXPLAIN: type=const, key=idx_merchant_code, rows=1

-- 预防措施：应用层参数化查询 + 类型校验
-- 代码层：PreparedStatement 的 setObject 自动按列类型处理

取舍说明：根治方案是应用层类型对齐（参数化查询 + 类型校验），而非加索引。若历史数据导致类型不一致（如 code 列混入数字字符串），需一次性数据清洗 UPDATE t_merchant SET merchant_code = CONCAT('M', merchant_code) WHERE merchant_code REGEXP '^[0-9]+$'，清洗后加唯一索引防复发。

场景 3：OR 条件导致索引失效

问题描述：WHERE 子句中 OR 连接的两个条件分别命中不同索引，MySQL 8.0 虽支持 index_merge，但实际执行计划常退化为全表扫描，尤其当其中一个条件选择性极低时。

复现 DDL + 数据：

CREATE TABLE t_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  action_type VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_user (user_id),
  INDEX idx_action (action_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_log(user_id, action_type, created_at)
SELECT FLOOR(RAND()*50000),
       ELT(FLOOR(RAND()*5)+1,'login','logout','purchase','refund','view'),
       NOW() - INTERVAL FLOOR(RAND()*30) DAY
FROM information_schema.columns a
CROSS JOIN (SELECT @r:=@r-1 AS seq FROM information_schema.columns b, (SELECT @r:=50000) r) c
LIMIT 50000;

慢 SQL（优化前）：

-- OR 连接两个低选择性条件，index_merge 效率差
SELECT * FROM t_log WHERE user_id = 12345 OR action_type = 'refund';
-- EXPLAIN: type=ALL 或 index_merge(union) 但 rows 仍很大

修复方案 A（UNION ALL 改写，推荐）：

SELECT * FROM t_log WHERE user_id = 12345
UNION ALL
SELECT * FROM t_log WHERE action_type = 'refund' AND user_id != 12345;
-- 两个子查询各自走自己的索引，避免 index_merge 的 merge 开销

修复方案 B（复合索引，若查询模式固定）：

ALTER TABLE t_log ADD INDEX idx_user_action (user_id, action_type);
-- 若 WHERE 总是 (user_id = ? OR action_type = ?) 且 user_id 等值在前
-- 可改为 WHERE (user_id = 12345) OR (action_type = 'refund' AND user_id = 12345)

取舍说明：UNION ALL 改写无需 DDL、即改即生效，适合紧急修复；复合索引适合查询模式固定且 QPS 高的场景，但增加写入开销。若 OR 条件中某分支选择性极低（如 action_type='refund' 占 20%），单独加索引收益有限，应考虑业务层拆分为两次查询 + 应用侧合并。

场景 4：大表 OFFSET 深翻页

问题描述：LIMIT 100 OFFSET 1000000 需扫描并丢弃前 100 万行，I/O 和 CPU 开销与 OFFSET 成正比。

复现 DDL + 数据：

CREATE TABLE t_event (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  event_type VARCHAR(32) NOT NULL,
  payload JSON,
  created_at DATETIME NOT NULL,
  INDEX idx_user_time (user_id, created_at),
  INDEX idx_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_event(user_id, event_type, payload, created_at)
SELECT FLOOR(RAND()*10000), ELT(FLOOR(RAND()*4)+1,'click','view','buy','share'),
       JSON_OBJECT('v','1.0'), NOW() - INTERVAL FLOOR(RAND()*90) DAY
FROM information_schema.columns a
CROSS JOIN (SELECT @r:=@r-1 FROM information_schema.columns b, (SELECT @r:=200000) r) c
LIMIT 200000;

慢 SQL（优化前）：

-- 深翻页：扫描 1000500 行，丢弃前 100 万行
SELECT * FROM t_event ORDER BY created_at DESC LIMIT 50 OFFSET 1000000;
-- EXPLAIN: type=index, rows=1000050, Extra=Using filesort（大排序）

修复方案 A（延迟关联 / 游标分页，推荐）：

-- 先查 ID（走索引，轻量），再回表取详情
SELECT e.* FROM t_event e
INNER JOIN (
  SELECT id FROM t_event ORDER BY created_at DESC LIMIT 50 OFFSET 1000000
) tmp ON e.id = tmp.id;
-- 内层查询只扫 id 列（覆盖索引），外层 50 次主键回表

-- 更优：游标分页（记住上一页最后一条的 created_at + id）
SELECT * FROM t_event
WHERE (created_at, id) < ('2026-09-15 10:23:45', 12345678)
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- EXPLAIN: type=range, key=idx_time, rows≈50

修复方案 B（业务层限制 + 搜索兜底）：

-- 限制最大翻页深度（如最多翻到第 500 页）
-- 超过阈值引导用户走搜索引擎（Elasticsearch）

取舍说明：游标分页体验最佳（无跳页），但要求排序字段唯一（加 id 做 tiebreaker）且不支持跳页；延迟关联兼容现有 UI（保留页码），但深页时仍有 OFFSET 开销（只是扫描的是 id 列而非全行）；搜索引擎方案适合超大数据量 + 复杂筛选场景，代价是多一套基础设施。

场景 5：多表 JOIN + 子查询优化

问题描述：相关子查询在驱动表每行执行一次，导致 N+1 问题；或 JOIN 顺序不当导致大表被驱动。

复现 DDL + 数据：

CREATE TABLE t_user (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  email VARCHAR(128) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE t_order_item (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_user_created (user_id, created_at),
  INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t_user(name, email, created_at)
SELECT CONCAT('User', seq), CONCAT('u', seq, '@test.com'),
       NOW() - INTERVAL FLOOR(RAND()*365) DAY
FROM (SELECT @r:=@r-1 AS seq FROM information_schema.columns a, information_schema.columns b, (SELECT @r:=10000) r) t
LIMIT 10000;

INSERT INTO t_order_item(order_id, user_id, product_id, quantity, price, created_at)
SELECT FLOOR(RAND()*50000), FLOOR(RAND()*10000), FLOOR(RAND()*2000),
       FLOOR(RAND()*5)+1, ROUND(RAND()*500,2), NOW() - INTERVAL FLOOR(RAND()*90) DAY
FROM information_schema.columns a
CROSS JOIN (SELECT @r:=@r-1 FROM information_schema.columns b, (SELECT @r:=100000) r) c
LIMIT 100000;

慢 SQL（优化前：相关子查询）：

-- 每行执行一次子查询 → N+1
SELECT u.id, u.name,
  (SELECT SUM(quantity*price) FROM t_order_item WHERE user_id = u.id AND created_at >= '2026-01-01') AS total_amount
FROM t_user u
WHERE u.created_at >= '2026-06-01';
-- EXPLAIN: 子查询 Dependent subquery, rows 每行重新扫描

修复方案（改写为 JOIN + GROUP BY）：

SELECT u.id, u.name, COALESCE(SUM(oi.quantity * oi.price), 0) AS total_amount
FROM t_user u
LEFT JOIN t_order_item oi ON oi.user_id = u.id AND oi.created_at >= '2026-01-01'
WHERE u.created_at >= '2026-06-01'
GROUP BY u.id, u.name;
-- EXPLAIN: t_user(ALL/idx) → t_order_item(ref, key=idx_user_created, rows≈10)
-- 执行一次 JOIN 聚合，消除 N+1

取舍说明：JOIN + GROUP BY 消除了 N+1，但大表 JOIN 时中间结果集可能膨胀（若一个 user 有 10 万条 order_item，GROUP BY 前需扫描 10 万行）。若数据量极大（> 千万行），可考虑：(1) 预聚合表（物化视图思路，定时刷新）；(2) 应用层分步查询（先查 user 列表，再批量 IN 查聚合值）；(3) 若查询模式固定，建物化汇总表 t_user_summary。

4. 索引失效常见原因汇总（≥3 种）

| # | 原因 | 示例 | 修复 | |---|------|------|------| | 1 | 函数包裹索引列 | WHERE DATE(col)=... | 改范围查询 / 生成列 | | 2 | 隐式类型转换 | VARCHAR 列 = 整数 | 应用层类型对齐 | | 3 | OR 连接不同索引列 | WHERE a=? OR b=? | UNION ALL / 复合索引 | | 4 | 范围列后跟非索引列 | WHERE a>? AND b=?（索引 (a,b) 中 a 为范围） | 调整索引列顺序 / 改写 SQL | | 5 | LIKE 前缀通配符 | LIKE '%abc' | 全文索引 / 搜索引擎 |

5. 预防与巡检建议

5.1 日常巡检命令（建议 crontab 每日执行）

-- 1. 慢查询趋势（performance_schema 聚合）
SELECT DATE(event_timestamp) AS day,
       COUNT(*) AS slow_count,
       AVG(timer_wait)/1000000000 AS avg_ms
FROM performance_schema.events_statements_history
WHERE timer_wait/1000000000 > 1
  AND event_timestamp >= NOW() - INTERVAL 7 DAY
GROUP BY day ORDER BY day;

-- 2. 索引使用率（7 天）
SELECT t.table_name, s.name AS index_name,
       s.count_star AS total,
       CASE WHEN s.count_star=0 THEN 'UNUSED' ELSE 'OK' END AS status
FROM performance_schema.table_io_waits_summary_by_index_usage s
JOIN information_schema.tables t ON t.table_schema=s.object_schema AND t.table_name=s.object_table
WHERE s.count_star IS NOT NULL
ORDER BY s.count_star ASC;

-- 3. 碎片率检查
SELECT table_name,
       ROUND(data_free / (data_length + data_free) * 100, 2) AS frag_pct
FROM information_schema.tables
WHERE table_schema = DATABASE() AND data_free > 1024*1024*100;

5.2 上线前 Checklist

• [ ] 新 SQL 必须过 EXPLAIN，确认 type >= range 且 rows < 1000
• [ ] 新增索引需评估写入 QPS 影响（每加一个二级索引，INSERT/UPDATE 多一次 B+ 树维护）
• [ ] 禁止在 WHERE 中对索引列使用函数（Code Review 卡口）
• [ ] 大表查询必须带 LIMIT，禁止 SELECT * 全列
• [ ] OFFSET 超过 10000 时必须改用游标分页

5.3 监控告警建议

• 慢查询数量 > 50/小时 → P3 告警
• 单条查询耗时 > 5s → P2 告警
• 索引碎片率 > 30% → 触发 OPTIMIZE TABLE 工单
• pt-query-digest 日报中 Top 10 新增指纹 → 周会 Review

6. 参考与延伸阅读

• MySQL 8.0 Reference Manual: Optimizer / Indexes
• Percona pt-query-digest 文档
• performance_schema 文档（events_statements / table_io_waits / metadata_locks）

本文档由林望舒-131 基于 5 年数据工程与 MySQL 性能调优经验编写，所有场景均可在 docker run mysql:8.0 本地复现。