性能优化

这一课你将能做到

1. 读懂 EXPLAIN 的 type、key、rows、Extra 四个关键列，判断一条 SQL 是「走索引」还是「全表扫描」
2. 亲手复现三大索引失效场景（函数包裹、前导模糊、隐式转换）并逐一改写成索引友好写法
3. 按最左前缀原则设计联合索引，并背出可复用的慢 SQL 排查五步法

为什么要学

功能写完只是上线的前一半，跑得快才是后一半。一条全表扫描的 SQL 在 12 行的练习库里无感，在 1200 万行的生产表上就是拖垮整个服务的导火索。慢 SQL 优化是后端面试的绝对高频区：「索引什么时候失效」「EXPLAIN 重点看什么」「线上 SQL 突然变慢你怎么排查」——这三问的答案质量基本决定了数据库项的评分。

人话：索引是一本书的目录，EXPLAIN 是「监考老师看你翻书的方式」。全表扫描 = 从第一页翻到最后一页；走索引 = 直接翻目录跳页。优化就是让每条查询都学会「翻目录」。

先建立图像

EXPLAIN 里 type 列的性能阶梯（从好到坏）：

const ─► eq_ref ─► ref ─► range ─► index ─► ALL
主键/唯一等值  JOIN被驱动表命中  普通索引等值  索引范围扫  扫全索引  扫全表
   ★★★★★         ★★★★          ★★★        ★★       ★        ☠

联合索引 idx_user_status(user_id, status) 的树结构：
(user_id, status) 按序排列：
(1,'accepted') (1,'wrong_answer') (2,'accepted') (2,'accepted') (5,'accepted') ...
  └─ 等值 user_id 可以定位段，段内 status 有序 ─┘
  └─ 但只给 status 不给 user_id？前面乱序，没法二分 ─┘ ← 最左前缀

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT 恢复初始数据

步骤 1：基线——一条全表扫描的 SQL

EXPLAIN
SELECT * FROM cf_submission
WHERE status = 'accepted' AND runtime_ms < 50;

关注四列（本库仅 12 行，rows 是估算值，思路最重要）：

type	key	rows	Extra
ALL	NULL	12	Using where

type=ALL 就是全表扫描——status 和 runtime_ms 上都没有索引，只能逐行判断。

步骤 2：建立联合索引并观察变化

CREATE INDEX idx_user_status ON cf_submission (user_id, status);

EXPLAIN
SELECT * FROM cf_submission
WHERE user_id = 1 AND status = 'accepted';

预期：type 从 ALL 变成 ref，key=idx_user_status，rows 大幅缩小。等值条件命中联合索引前两列，走的是「定位 + 顺序读」。

步骤 3：最左前缀——索引在但用不上「定位」

EXPLAIN
SELECT * FROM cf_submission
WHERE status = 'accepted';
-- type=index（扫全索引，好于 ALL 但仍是全扫）：联合索引跳过了最左列 user_id，
-- 无法二分定位，只能把索引整体扫一遍

结论：联合索引 (user_id, status) 只服务「带 user_id 的查询」。查询习惯决定列序，不能反过来。

步骤 4：索引失效三连（面试背诵点，逐一实测）

-- ① 函数包裹索引列
CREATE INDEX idx_sub_time ON cf_submission (submitted_at);

EXPLAIN SELECT * FROM cf_submission WHERE DATE(submitted_at) = '2025-06-01';
-- type=ALL：对每行先算函数，索引失效

EXPLAIN SELECT * FROM cf_submission
WHERE submitted_at >= '2025-06-01' AND submitted_at < '2025-06-02';
-- type=range：改写成范围条件，索引复活

-- ② 前导模糊
EXPLAIN SELECT * FROM cf_user WHERE username LIKE '%in';
-- type=ALL：以 % 开头无法定位前缀
EXPLAIN SELECT * FROM cf_user WHERE username LIKE 'li%';
-- type=range：后缀通配可以走 uk_username 的前缀定位

-- ③ 隐式类型转换（字符列 vs 数字）
EXPLAIN SELECT * FROM cf_problem WHERE slug = 1;
-- slug 是 VARCHAR，数字 1 被转换，索引失效（type=ALL/index）
EXPLAIN SELECT * FROM cf_problem WHERE slug = 'two-sum';
-- type=const：唯一索引等值，最优级

步骤 5：覆盖索引——不回表

EXPLAIN
SELECT user_id, status FROM cf_submission
WHERE user_id = 1 AND status = 'accepted';
-- Extra：Using index ← 所需列全在索引里，不用回基表

EXPLAIN
SELECT * FROM cf_submission
WHERE user_id = 1 AND status = 'accepted';
-- 无 Using index：要拿 runtime_ms 等列，需按主键回表再取

「查什么列就尽量让索引带上什么列」是高频查询的最后一块拼图，但列多会撑大索引，按 Top 查询取舍。

步骤 6：ORDER BY 与分页

-- cf_practice_record 已有 idx_user_time(user_id, practiced_at)
EXPLAIN
SELECT * FROM cf_practice_record
WHERE user_id = 1
ORDER BY practiced_at DESC
LIMIT 3;
-- type=ref 且 Extra 无 Using filesort：索引本身就是 (user_id, practiced_at) 有序，
-- 直接逆序取 3 条

-- 深分页两种写法（生产表上差异是数量级的）：
SELECT id FROM cf_submission ORDER BY id LIMIT 5 OFFSET 0;    -- 快
SELECT id FROM cf_submission ORDER BY id LIMIT 100000, 5;     -- 慢：先取 100005 行再扔掉
-- 键集分页（记住上一页最后一个 id）：
SELECT id FROM cf_submission WHERE id > 12 ORDER BY id LIMIT 5;  -- 恒定快

步骤 7：慢 SQL 排查五步法（背下来）

-- ① 打开慢日志（本地练习可直接执行；云数据库在控制台改参数）
SHOW VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;

-- ② 捞出慢 SQL 后，EXPLAIN 看扫描方式
EXPLAIN SELECT ...;

-- ③ 诊断：ALL？filesort？temporary？rows 估算离谱？
-- ④ 处方：改写 SQL / 补联合索引 / 覆盖索引
-- ⑤ 复查：再 EXPLAIN + 实测耗时，必要时 ANALYZE TABLE 更新统计信息
ANALYZE TABLE cf_submission;

讲清楚要点

1. EXPLAIN 关键列背诵表：type（访问类型，见阶梯图）、key（实际用上的索引，NULL 等于没用）、rows（估算扫描行数，越小越好）、Extra（Using index=覆盖索引不回表；Using index condition=索引下推；Using where=服务层过滤；Using filesort=额外排序；Using temporary=临时表）。id/select_type 看子查询结构，filtered 看过滤比例。
2. 回表与覆盖索引是一对核心概念：二级索引的叶子节点只存「索引列 + 主键」，要别的列就得拿主键回基表再查一次。高频查询把所需列并入索引（覆盖）能省掉全部回表。
3. 联合索引列序三原则：等值条件列在前、排序列跟随其后、范围条件列最后（范围之后的列无法继续参与定位与排序）。(user_id, status)、(user_id, practiced_at) 都是按这个顺序设计的。
4. 索引失效五场景（面试清单）：函数/运算包裹索引列；LIKE 以 % 开头；字符列与数字比较发生隐式转换；违反最左前缀；OR 两侧混有无索引列（优化器可能放弃走索引改全扫）。IS NULL / IN 不在失效之列——别背错。
5. rows 是估算不是事实：优化器靠统计信息估算，长期写入后可能失真，ANALYZE TABLE 刷新；「明明有索引却不走」的第一嫌疑就是统计过期，其次才是写法。
6. 索引不是越多越好：每个索引都是一棵要维护的 B+ 树，INSERT/UPDATE/DELETE 都要同步写，还占空间、干扰优化器选择。定期清理无用的冗余索引（如 (a) 与 (a,b) 并存时前者多半冗余）。

你可能会懵的地方

• 低选择性列（如 status 只有两个值）为什么单独建索引没用？ 走索引要「定位 + 回表」，命中一半的表时回表代价超过全表扫描，优化器干脆选 ALL。低选择性列的正确用法是并入联合索引做过滤段，而不是单列索引。

• key_len 怎么读？ 表示索引用到的「字节宽度」，可以判断联合索引用了几列：INT UNSIGNED 4 字节，VARCHAR(50) utf8mb4 约 50×4+2=202 字节，可空列再加 1 字节 NULL 标记。key_len 只覆盖到第一列就说明只用了第一列。

• 为什么加了索引查询反而没变快？ 三种常见原因：写法导致失效（回看步骤 4）；区分度太低优化器弃用；回表行数太多。诊断顺序：EXPLAIN 看 type/key/Extra → 改写 → 还不行就考虑覆盖索引或缓存。

• ORDER BY 什么时候能免掉 filesort？ 排序列与 WHERE 用到的索引顺序一致时。WHERE user_id=1 ORDER BY practiced_at 命中 (user_id, practiced_at) 直接读出即有序；范围列之后的排序（WHERE user_id=1 AND status='a' ORDER BY created_at）超出了索引的有序范围，就会 filesort。

• LIMIT OFFSET 到底慢在哪？ OFFSET 100000 意味着真的要「取出并丢弃」10 万行（回表 10 万次）。键集分页 WHERE id > 上页末尾 每页都是一次索引定位，成本恒定，代价是只能顺序翻页、跳页要另想办法。

错法对照

错法	问题	正解
SELECT * everywhere	回表 + 无法覆盖索引 + 传输浪费	只 SELECT 需要的列
WHERE DATE(t)=CURDATE()	索引失效	t >= 今日0点 AND t < 明日0点
WHERE a=? OR b=?（b 无索引）	优化器退化为全表扫	OR 两侧都要可走索引，或拆成 UNION
LIMIT 100000, 20 深翻页	扫描+丢弃十万行	键集分页 WHERE id > last_id LIMIT 20
每个 WHERE 列都单独建一个索引	写放大、优化器难选	按查询模式设计少量联合索引

卡点急救

1. 建了索引还是 type=ALL：先核对写法（函数包裹/隐式转换/最左前缀）；再 ANALYZE TABLE 刷新统计；本地验证可用 SELECT ... FORCE INDEX(索引名) 强制对比，但生产别轻易 FORCE，根因多半在写法。
2. 慢日志开了却没记录：long_query_time 是 GLOBAL 改的、对新连接生效；确认 slow_query_log=ON、log_output 指向的位置（FILE/TABLE，TABLE 时查 mysql.slow_log）。
3. Extra 出现 Using filesort/temporary：说明有额外排序或临时表。先看排序列能否并入现有索引；聚合 + 排序组合的临时表属常见现象，小结果集可接受，大结果集要动索引或拆查询。

面试常问

• 索引失效的场景有哪些？ 背五条：函数/运算包裹列、前导 % 模糊、隐式类型转换（字符列配数字）、违反最左前缀、OR 混合无索引列。追问时补一句「IS NULL 和 IN 不失效」展示准确性。

• EXPLAIN 你重点看什么？ type 判断扫描等级（ref 以上合格，ALL 要警惕）、key 确认命中索引、rows 看扫描量、Extra 看 Using index（覆盖）/filesort/temporary。能对着业务 SQL 说出「哪里慢、怎么改」才是完整答案。

• 线上一条 SQL 突然从 50ms 变 3s，你的排查流程？ 五步法：看监控确认是 SQL 层慢 → EXPLAIN 对比执行计划是否变化（统计信息/索引被删）→ 检查锁等待（INNODB_TRX）与硬件/IO → 数据量是否到了索引失效阈值 → 改写 SQL/补索引/加缓存，改完回归验证。答出「先对比执行计划变化」是资深信号。

自测三问

1. WHERE runtime_ms + 10 < 100 为什么走不了 runtime_ms 的索引？怎么改写？
2. 覆盖索引的判断依据是什么？EXPLAIN 里对应哪个标记？
3. 联合索引 (a, b, c)，以下哪些查询能完整用到索引：a=?、b=?、a=? AND c=?、a=? AND b=? ORDER BY c？

小结一句话

性能优化的路径固定：EXPLAIN 判断扫描方式 → 用最左前缀与覆盖索引设计/修补索引 → 消灭函数包裹、前导模糊、隐式转换等失效写法 → 深分页换键集——五步法背下来直接上生产。

延伸

• MySQL 8.0 Reference Manual — EXPLAIN Output Format
• MySQL 8.0 Reference Manual — Optimization
• 下一课：综合练习——把 11 课的 DDL、聚合、窗口、事务、优化串成一次完整实战。