知识点深化 · 数据库 · 索引
索引与 B+ 树：聚簇/非聚簇、覆盖索引、最左前缀
索引是数据库提速的关键：它像书的目录，不用翻全表就能定位数据。InnoDB 索引用 B+ 树，叶子节点存数据或主键，且叶子间用链表相连。这一页把聚簇索引、回表、覆盖索引、最左前缀讲透。
① 小白第一课怎么学（4 步走，约 60 分钟）
别急着背代码，先按这四步建立直觉：
1看图建立直觉（10 分钟）
读②③：把 B+ 树想成分层目录。
2记索引类型（15 分钟）
读④：聚簇/二级/覆盖、最左前缀。
3手推索引（20 分钟）
精读⑤。
4刷题纠错（15 分钟）
做⑦⑩，错题回⑥。
本课小目标学完你要能：① 说清 B+ 树为什么快；② 区分聚簇/二级索引；③ 解释最左前缀和回表。
② 一图看懂：索引与 B+ 树全地图
读法：中心是索引，上 B+ 树结构，右索引类型，下是最左前缀与易错。
③ 本质直觉：索引就是数据库的"目录"，B+ 树让查找像翻字典
没有索引：查一条数据要从第一行扫到最后一行（全表扫描 O(n)）。有了索引：像字典按拼音目录翻，几跳就定位 O(log n)。
B+ 树：多路平衡树。非叶子节点只存索引键做导航，真正的数据都在叶子节点，且叶子之间用双向链表相连——范围查询特别快。树高通常 3~4 层，查一行只需 3~4 次磁盘 IO。
聚簇索引：InnoDB 一张表只有一个，叶子节点直接存整行数据（主键索引）。二级索引：叶子存主键值，查到后还要拿主键再去聚簇索引查一次——这叫回表。
为什么用 B+ 树不用二叉树数据库是磁盘 IO，B+ 树矮胖（扇出大、3~4 层），一次节点读就读很多键，磁盘 IO 次数远低于二叉树。
④ 完整体系与对比表
聚簇 vs 二级索引
| 对比 | 聚簇索引（主键） | 二级索引 |
|---|
| 叶子存 | 整行数据 | 主键值 |
| 一张表几个 | 1 个 | 多个 |
| 查到后 | 直接拿到行 | 要回表查主键索引 |
最左前缀原则
联合索引 (a,b,c) 按从左到右匹配：能用于 a、a,b、a,b,c。跳过 a 直接用 b、或中间断了（如 a 后用了范围），后面列就用不上索引。
覆盖索引若查询的列都在索引里（如 select a,b where a=?），直接从索引返回，免回表，EXPLAIN 显示 Using index
索引失效场景
① 对索引列用函数/运算（where date(create_time)=...）；② 隐式类型转换（字符串字段用数字查）；③ 前导模糊 like '%xx'；④ 联合索引没走最左列；⑤ OR 两边有一边没索引。
⑤ 用法场景与典型例题
例1（回表）二级索引查到主键后还要做什么？
叶子存的是主键。
拿到主键值，再去聚簇索引（主键 B+ 树）查整行——这就是回表。若查询列都在二级索引里则免回表。
例2（最左前缀）联合索引 (a,b,c)，where b=1 能用吗？
跳过最左列 a。
不能，违反最左前缀。where a=1 and b=2 能用；where b=2 and c=3 用不上。
例3（覆盖索引）索引 (name,age)，select name,age where name='x' 要回表吗？
查询列都在索引里。
name、age 都在联合索引中，直接从索引返回，免回表。EXPLAIN Extra 显示 Using index。
做题心法看到回表想覆盖索引；看到联合索引想最左前缀；慢查询先 EXPLAIN 看 type 和 key。
⑥ 高频错误诊断（4 条）
错误1：以为索引越多越好索引占空间、拖慢写入（增删改要维护 B+ 树），不是越多越好。
错误2：对索引列做函数where YEAR(create_time)=2024 会让索引失效，改成范围查 create_time between。
错误3：前导模糊 like '%xx'前导通配符无法定位 B+ 树，索引失效；like 'xx%' 可以。
错误4：隐式类型转换phone 是 varchar，where phone=13800000000（数字）会触发转换导致索引失效。
⑦ 考点真题演练（4 题）
考点分布
| 考法 | 出题形式 | 应对 |
|---|
| B+树 | 为什么快 | 矮胖少 IO，叶子链表 |
| 聚簇/二级 | 叶子存什么 | 整行/主键 |
| 最左前缀 | 联合索引匹配规则 | 从左列开始 |
| 覆盖索引 | 什么免回表 | 查询列都在索引里 |
真题基础1. InnoDB 聚簇索引的叶子节点存储的是？
真题中档2. 联合索引 (a,b,c)，下列哪个查询能用到索引？
真题中档3. 二级索引查到主键后还要再查聚簇索引，这叫？
真题拔高4. 下列哪种情况索引会失效？
⑧ 必背知识点卡
B+树：矮胖，非叶导航叶子存数据 3~4 层
叶子：双向链表相连 范围查快
聚簇索引：叶子=整行，一表一个 主键
二级索引：叶子=主键，要回表
最左前缀：(a,b,c) 从 a 开始 断了后面失效
覆盖索引：查询列都在索引里 免回表
失效：函数/隐式转换/前导模糊
⑨ 应用输出：优化一个慢查询 SQL
场景：订单表 500 万行，select * from orders where user_id=? and status=? 很慢。
① EXPLAIN：发现 type=ALL 全表扫描，没走索引。
② 建联合索引：(user_id, status)，符合最左前缀。
③ 避免 select *：只查需要的列，若正好是索引列就走覆盖索引免回表。
④ 重测：type 变成 ref，rows 从 500 万降到几百。
⑤ 避免：别对 user_id 用函数、别前导模糊。
口述思路合上书说："慢查询先 EXPLAIN，建联合索引贴合 where，少 select * 走覆盖。"
⑩ 分层练习（基础 + 中档 + 拔高）
▍基础 6 题
基础4什么是回表？
二级索引查到主键再去聚簇索引查整行。
基础5联合索引最左前缀？
从最左列开始连续匹配。
▍中档 6 题
中档7为什么 B+ 树比二叉树适合数据库？
矮胖扇出大，磁盘 IO 次数少。
中档8where name like '张%' 走索引吗？
走，后导通配符不影响定位。
中档9一张表一定要有聚簇索引吗？
InnoDB 一定有，没主键会选唯一非空列或隐藏主键。
中档10索引下推 ICP 是什么？
把 where 条件下推到存储引擎层过滤，减少回表次数。
中档11主键用自增还是 UUID？
自增主键插入顺序写满页少分裂；UUID 随机导致页分裂碎片多。
中档12为什么索引能加速 order by？
B+ 树叶子有序，避免 filesort。
▍拔高 6 题
拔高13什么是索引页分裂？
插入无序主键时，页满了要拆分，影响写入性能。
拔高14联合索引 (a,b)，where a>1 order by b 能用上索引排序吗？
a 是范围查询，b 无法利用索引有序，会 filesort。
拔高15为什么不建议在小表建索引？
小表全表扫描比走索引还快，索引徒增维护成本。
拔高16什么是自适应哈希索引？
InnoDB 对频繁访问的 B+ 树页自动建哈希，加速等值查。
拔高17索引下推关闭和开启差别？
关闭时 server 层逐行回表；开启时引擎层先过滤再回表。
拔高18前缀索引适用？
长字符串列取前 N 个字符建索引，省空间，但不能用于 order by/覆盖。
⑪ 记忆口诀 + 7 天复习计划
三句口诀① B+ 树矮胖叶子链表，聚簇存整行二级存主键。② 联合索引最左前缀，覆盖索引免回表。③ 函数模糊隐式转，索引失效慢查询。
| 天 | 任务 | 自检 |
|---|
| 第 1 天 | 读②③④，画 B+ 树 | 叶子结构对 |
| 第 2 天 | 背索引类型 + 基础 1-6 | 聚簇二级分清 |
| 第 3 天 | 做中档 7-12，写最左前缀 | 匹配规则对 |
| 第 4 天 | 做拔高 13-18，理解页分裂 | 能讲清 |
| 第 5 天 | 做⑦真题 4 题 | 限时每题 2 分钟 |
| 第 6-7 天 | 合上书默写索引失效场景 | 不看资料全默对 |
← 返回软件技术总览