索引与查询性能

这一课你将能做到

1. 亲手给 10 万行的表建索引，用 EXPLAIN 对比建前建后的执行计划差异
2. 建联合索引并用实验验证「最左前缀原则」，说出三类索引失效写法
3. 说出 B+ 树、聚簇索引、回表、覆盖索引这几个高频面试词的准确含义

前置：第一课建库脚本已跑（本课会把 student 表撑到 10 万行，结束给复原提示）。本课在 sql_practice 执行。

为什么要学

数据到 10 万行，没有索引的查询就是整表逐行翻。接口慢、面试问索引、DBA 让你加索引——这一课把三件事一次打通：什么时候该建索引、怎么建联合索引、怎么用 EXPLAIN 判断查询走没走索引。EXPLAIN 不是 DBA 专属，后端工程师上线前自己跑一遍，是最便宜的性能保险。

先建立图像

没有索引：整表扫描                有索引：B+ 树目录
┌──────────────┐                  根节点 → 分支 → 叶子
│ row1 ✗       │                  3~4 层就能定位 10 万行中的 1 行
│ row2 ✗       │                  叶子层还是双向链表 → 范围查询顺着扫
│ …            │
│ row99999 ✓   │  ← 翻完才知道    索引列有序 + 树结构 = 二分定位
└──────────────┘

两个必须建立的概念：

• 聚簇索引：InnoDB 的主键索引，叶子节点直接存整行数据——表本身就是按主键组织的一棵 B+ 树
• 二级索引：其他索引，叶子存的是「索引列 + 主键值」，查到之后还要拿主键回聚簇索引取整行，这一步叫回表

跟着做

步骤 1：把 student 表撑到 10 万行

用 MySQL 8 的递归 CTE 造数，一段 SQL 搞定：

SET SESSION cte_max_recursion_depth = 1000000;   -- 默认只允许递归 1000 层

INSERT INTO student (name, email, city, vip, balance)
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT CONCAT('user', n),
       CONCAT('user', n, '@example.com'),
       ELT(n % 3 + 1, '深圳', '杭州', '北京'),
       n % 2,
       ROUND(RAND() * 200, 2)
FROM seq;

SELECT COUNT(*) FROM student;   -- 100007

步骤 2：先看没有索引时查询长什么样

EXPLAIN SELECT * FROM student WHERE city = '深圳' AND vip = 1;

重点看输出里这几列（格式化展示，数值是近似值）：

type: ALL          ← 全表扫描！
key:  NULL         ← 没用任何索引
rows: 57000        ← 预估要扫的行数
Extra: Using where

type 的好坏梯度（从差到好）：ALL < index < range < ref < eq_ref < const。线上查询出现 ALL 且行数大，基本就是要优化的信号。

步骤 3：建索引，再看一次

CREATE INDEX idx_student_city ON student(city);

EXPLAIN SELECT * FROM student WHERE city = '深圳' AND vip = 1;
-- type: ref, key: idx_student_city, rows: ~34000

从「扫 5.7 万」到「按索引直接定位深圳的 3.4 万行」。但 vip 的过滤仍发生在取回数据之后——这正好引出联合索引。

步骤 4：联合索引与最左前缀原则

CREATE INDEX idx_student_city_vip ON student(city, vip);
DROP INDEX idx_student_city ON student;   -- 联合索引能覆盖它，单列的留着纯属浪费

-- 场景 A：等值查询两列 → 完整命中
EXPLAIN SELECT * FROM student WHERE city = '深圳' AND vip = 1;
-- type: ref, key: idx_student_city_vip, ref: const,const, rows: ~17000

-- 场景 B：只有 city（联合索引的最左列）→ 也能用
EXPLAIN SELECT * FROM student WHERE city = '深圳';
-- key: idx_student_city_vip ✓

-- 场景 C：只有 vip（跳过最左列）→ 索引用不上
EXPLAIN SELECT * FROM student WHERE vip = 1;
-- type: ALL, key: NULL ✗

这就是最左前缀原则：联合索引 (city, vip) 是先按 city 排序、city 相同再按 vip 排序——跳过 city 单查 vip，数据对 vip 来说是乱序的，树结构帮不上忙。建联合索引时，把最常单独查询的列放最左。

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

EXPLAIN SELECT city, vip FROM student WHERE city = '深圳' AND vip = 1;
-- Extra: Using index   ← 覆盖索引生效！

SELECT 的列（city、vip）全在索引里，不用回聚簇索引取整行，速度再上一档。对比 SELECT *：多要一列 balance 就得回表，覆盖立刻失效——这就是第 2 课说「别 SELECT *」的性能根源。

步骤 6：三类索引失效现场（都用 email 的唯一索引验证）

-- ① 前导 % 的 LIKE：失效
EXPLAIN SELECT * FROM student WHERE email LIKE '%50000@example.com';
-- type: ALL ✗
EXPLAIN SELECT * FROM student WHERE email LIKE 'user5%';
-- type: range ✓（前缀确定，能走索引）

-- ② 列上套函数：失效
EXPLAIN SELECT * FROM student WHERE SUBSTRING(email, 1, 4) = 'user';
-- type: ALL ✗（对每行的 email 做函数运算，索引里的 email 是原值，对不上）

-- ③ 隐式类型转换：失效
EXPLAIN SELECT * FROM student WHERE email = 50000;
-- type: ALL ✗（email 是 VARCHAR，与数字比较时 MySQL 对列做 CAST，
--           等价于给列套了函数。传字符串 'user50000@example.com' 则 type: const）

唯一索引的等值查询是最高档：EXPLAIN SELECT * FROM student WHERE email = 'user50000@example.com'; → type: const，一步定位。

步骤 7：本站 schema 里的索引实例

打开 backend/src/main/resources/schema.sql，看 cf_lesson：

UNIQUE KEY uk_track_slug (track_id, slug),        -- 同一课程下 slug 唯一
INDEX idx_track_order (track_id, order_index),    -- 「取某课程的课时并按顺序排」一条索引全覆盖
INDEX idx_status (status),

idx_track_order 正是联合索引思路的落地：课程详情页的请求模式永远是「按 track_id 查 + 按 order_index 排」，一个索引同时服务 WHERE 和 ORDER BY，连 filesort 都省了。

步骤 8：复原练习库

DROP DATABASE IF EXISTS sql_practice;
-- 回第一课重跑建库脚本

讲清楚要点

1. 索引 = 用写入和空间换读取：每建一个索引，INSERT/UPDATE 都要多维护一棵 B+ 树；不是越多越好。判断标准是查询模式：一张表的索引应该能数出「服务了哪几条查询」。
2. 最左前缀原则：联合索引 (a, b, c) 服务 a / a+b / a+b+c 的查询，服务不了 b、c 开头的。等值列放前、范围列放后是通用经验。
3. 回表与覆盖索引：二级索引命中后拿主键回聚簇索引取整行是回表；查询列全在索引内则免回表（Extra: Using index）。高频列表查询尽量设计成覆盖索引。
4. EXPLAIN 四个必看列：type（访问方式，拒绝 ALL）、key（实际用的索引）、rows（预估扫描行数，估算值）、Extra（Using index 好事；Using filesort、Using temporary 警惕）。改变 SQL 或索引后重跑 EXPLAIN 对比，这是优化的基本循环。
5. 失效三巨头：前导 % 的 LIKE、列上套函数/运算、隐式类型转换（字符串列传数字）。根源相同——索引里存的是列的原值，你对列做了加工就匹配不上。
6. 主键递增很重要：自增 id 顺序追加，B+ 树只写最右页；随机主键（如 UUID）插到任意页，频繁页分裂 + 缓存命中率低。这回答了第 7 课「为什么主键要自增」。

你可能会懵的地方

• 为什么用 B+ 树不用哈希表或红黑树？ 哈希只支持等值，范围查询和排序废掉；红黑树/二叉树太瘦高，10 万行要十几层，每层一次磁盘 IO；B+ 树矮胖（千万行也就 3~4 层）且叶子链表天然支持范围扫描。

• EXPLAIN 的 rows 是精确值吗？ 是基于统计信息的估算，数量级可信、个位数不可信。统计信息陈旧时 ANALYZE TABLE 表名; 刷新。

• 建了索引为什么还是慢？ 依次排查：写法失效（步骤 6 三类）；数据量太小优化器觉得全表扫更快；回表太多（改覆盖索引）；排序没吃到索引（Using filesort）；深分页 OFFSET 巨大。

• 唯一索引和普通索引选哪个？ 业务上本来就唯一的列（email、slug）必须唯一索引——数据库兜底防重。普通等值查询列普通索引。两者查询性能几乎一样（唯一索引命中即停）。

• 索引会不会拖垮写入？ 会，但多数业务读远多于写，收益远大于代价。真正要警惕的是「一张表建了十几个单列索引」——按查询模式合并成少量联合索引才是正解。

错法对照

错法	问题	正解
每列单独建一个索引	写入全拖慢，优化器难选	按查询模式建联合索引
LIKE '%x' 当搜索	索引失效全表扫	前缀 LIKE 'x%'，或全文索引/ES
WHERE DATE(created_at) = '2026-08-01'	函数包裹失效	>= '2026-08-01 00:00:00' AND < '2026-08-02 00:00:00'
WHERE email = 50000（列是 VARCHAR）	隐式转换失效	参数类型与列类型对齐
线上 SQL 凭感觉上	慢查询全靠用户发现	上线前 EXPLAIN，盯 type/key/rows
索引建完不维护	统计信息陈旧选错计划	大批量导入后 ANALYZE TABLE

卡点急救

1. 错误 3699（cte 递归超限）：SET SESSION cte_max_recursion_depth = 1000000; 再跑造数。
2. EXPLAIN 看不懂：只抓四件事——type 是不是 ALL、key 是不是 NULL、rows 是不是巨大、Extra 有没有 filesort/temporary。抓完再回头查语法细节。
3. 明明有索引却没用：对照步骤 6 查失效写法；再 ANALYZE TABLE 刷新统计；小表全表扫可能反而是优化器的正确选择。
4. 查询还是慢：看 Extra 里 Using filesort（排序没走索引，把排序列加进联合索引）和深分页问题（改游标分页 WHERE id > ? LIMIT n）。
5. 10 万行表想删掉：别逐行 DELETE，直接 DROP DATABASE 后重跑第一课脚本。

面试常问

• 为什么 InnoDB 用 B+ 树？ 矮胖少 IO（千万行 3~4 层）；叶子节点双向链表支持高效范围查询与排序；非叶子节点只存键不存数据，单页容纳更多键，扇出更大。对比：哈希不支持范围，二叉树太高。

• 聚簇索引和二级索引的区别？什么是回表？ 聚簇索引=主键索引，叶子存整行，表即索引；二级索引叶子存「索引列+主键」，命中后需回聚簇索引取整行。查询列被索引完全覆盖时可避免回表。

• 什么是最左前缀原则？ 联合索引按定义列顺序排序，查询条件必须从最左列开始连续匹配才能用上索引；(a,b,c) 支持 a、a+b、a+b+c，不支持跳过 a。等值放前、范围放后。

• 说几个索引失效的场景。 前导 % 的 LIKE、列上函数或运算、隐式类型转换、隐式字符集转换、联合索引不满足最左。每条都能用 EXPLAIN 验证。

• 什么是覆盖索引？ 查询所需列全部包含在某个索引中，无需回表，EXPLAIN 的 Extra 显示 Using index。高频查询按覆盖索引设计是常规优化手段。

自测三问

1. 联合索引 (city, vip) 能服务哪几类 WHERE 组合？不能服务哪几类？
2. 写出 WHERE DATE(created_at) = '2026-08-01' 的索引友好改写。
3. EXPLAIN 里 type、key、rows、Extra 各看什么？出现 Using filesort 说明什么？

小结一句话

索引是有序的 B+ 树目录：按查询模式建联合索引、守住最左前缀、避开失效三巨头，上线前用 EXPLAIN 的 type/key/rows/Extra 验证——优化靠证据，不靠感觉。

延伸

• MySQL EXPLAIN 输出格式 / InnoDB 索引结构
• 下一课：事务基础——多条写语句怎么保证「要么全成、要么全不做」。