面试知识库
高 进阶

慢查询优化实战#

一句话答案#

慢查询排查四步链路：slow_query_log 发现 → EXPLAIN 定位 → PROFILE 量化耗时 → Optimizer Trace 理解优化器决策。不只是”加索引”。

核心要点

完整诊断链路#

Step 1: slow_query_log → 发现哪些 SQL 慢
Step 2: EXPLAIN → 看执行计划（走没走索引、扫了多少行）
Step 3: PROFILE → 量化每个阶段的耗时（是 IO 慢还是锁等待）
Step 4: Optimizer Trace → 理解优化器为什么选了这个计划
plaintext

Step 1：慢查询日志#

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未走索引也记录

-- 分析慢查询日志（命令行工具）
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
-- -s t: 按查询时间排序, -t 10: 取前 10 条
sql

Step 2：EXPLAIN 执行计划#

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
sql

关键字段：

字段关注点
typeALL（全表扫描）→ 必须优化；ref/eq_ref → 正常
keyNULL → 没走索引
rows预估扫描行数，越大越慢
ExtraUsing filesort（文件排序）/ Using temporary（临时表）→ 需优化
ExtraUsing index（覆盖索引）→ 好
ExtraUsing index condition → ICP（索引下推）

Step 3：PROFILE —— 量化各阶段耗时#

SET profiling = 1;
SELECT * FROM orders WHERE user_id = 100;   -- 执行目标 SQL
SHOW PROFILES;                               -- 查看所有已记录的查询
SHOW PROFILE FOR QUERY 1;                    -- 查看具体查询的各阶段耗时
sql

PROFILE 输出示例：

StatusDuration
starting0.000050
Opening tables0.000012
Sending data1.234567
Sorting result0.000080
end0.000005

诊断价值：

  • Sending data 耗时长 → 扫描了大量数据（索引问题或回表多）
  • Sorting result 耗时长 → 排序成本高（缺少合适的联合索引）
  • Waiting for table lock → 锁竞争问题
  • Creating tmp table → 临时表开销大

注意：PROFILE 在 MySQL 5.7+ 标记为 deprecated，推荐用 Performance Schema，但面试中 PROFILE 更常被问到。

Step 4：Optimizer Trace —— 理解优化器决策#

SET optimizer_trace = "enabled=on";
SELECT * FROM orders WHERE user_id = 100 AND create_time > '2026-01-01';
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
SET optimizer_trace = "enabled=off";
sql

看什么：

  • rows_estimation：优化器估算每个索引的扫描行数
  • chosen: true/false：优化器最终选了哪个索引、为什么放弃其他的
  • cost：每个执行计划的代价估算

典型场景： 明明有索引但优化器没用

  • Optimizer Trace 可能显示：全表扫描的 cost < 走索引的 cost
  • 原因：数据量小、选择性低（大部分行都满足条件）

常见优化手段#

问题优化方案
type=ALL 全表扫描加合适索引
回表次数多（查 SELECT * + 非覆盖索引）覆盖索引 / 避免 SELECT *
Using filesort联合索引覆盖 ORDER BY 列
Using temporary优化 GROUP BY / DISTINCT
深分页 LIMIT 100000, 10延迟关联 / 游标分页
子查询 IN (SELECT …)改写为 JOIN
函数操作索引列 WHERE YEAR(date)=2026改为范围查询 WHERE date >= ‘2026-01-01’
面试回答（2分钟版）

我排查慢查询有一套完整链路。首先开启 slow_query_log 发现问题 SQL，用 mysqldumpslow 按耗时排序找出 TOP N。然后用 EXPLAIN 分析执行计划，看 type 是否全表扫描、key 是否走了预期索引、rows 扫描了多少行、Extra 有没有 filesort 或临时表。如果 EXPLAIN 看不出瓶颈在哪，用 PROFILE 看各阶段耗时——比如 Sending data 阶段很长说明扫描了大量数据，Sorting 很长说明排序成本高。极端情况下用 Optimizer Trace 看优化器为什么选了某个执行计划，比如明明有索引却不走，Trace 会告诉你优化器认为全表扫描代价更低。优化手段包括加索引、覆盖索引避免回表、联合索引消除 filesort、深分页改用延迟关联等。

追问与易错

追问方向：

  • “EXPLAIN 和 PROFILE 的区别？”→ EXPLAIN 看计划（预估），PROFILE 看实际执行各阶段耗时
  • “为什么有索引但优化器不用？”→ 选择性低、数据量小时全表扫描更快，用 Optimizer Trace 确认
  • “大表加索引怎么不影响线上？”→ pt-online-schema-change / gh-ost，参见 大表DDL操作实战

易错点：

  • ❌ 只会用 EXPLAIN 就觉得够了——EXPLAIN 看不出锁等待和各阶段耗时
  • ❌ 加索引解决所有慢查询——索引过多影响写入性能，要权衡
  • ❌ “走了索引就不会慢”——回表太多照样慢，覆盖索引才是最优