索引
→ 返回 数据库基础
索引是数据库在存储数据之外额外维护的数据结构，让查询从全表扫描 O(n) 降低到 O(log n)，以写入/存储开销换取查询速度。
为什么用 B+ 树
| 数据结构 | 查找 | 范围查询 | 磁盘 IO | 问题 |
|---|---|---|---|---|
| 哈希表 | O(1) | ❌ 不支持 | 差（无序） | 无法做 >, <, BETWEEN, ORDER BY |
| 二叉搜索树 | O(log n) | ✅ | 差（树高随数据量增长，最坏退化成链表） | — |
| B 树 | O(log n) | 一般 | 中（内节点存数据，每个节点 IO 只能查一条） | — |
| B+ 树 | O(log n) | ✅ 极佳 | 最优（内节点只存 key，叶节点存数据+双向链表） | InnoDB 的选择 |
B+ 树关键特性：
[30 | 60] ← 内节点，只存 key，不存数据行
/ | \
[10|20] [40|50] [70|80] ← 内节点
/ \
[10→20→30→40→50→60→70→80→...] ← 叶节点，存完整行/主键，双向链表连接
- 所有数据行（或主键）在叶节点，查找路径固定为 O(log n)
- 叶节点双向链表，支持范围扫描不需要回溯
- 内节点只存 key，单个页能放更多 key，树高更低（通常 3~4 层可覆盖千万行）
聚簇索引 vs 非聚簇索引
聚簇索引（Clustered Index）
B+ 树叶节点直接存完整的数据行，表只有一个聚簇索引：
InnoDB：主键就是聚簇索引（无主键时用唯一非空列，再无则自动生成隐藏 row_id）
叶节点：[主键值 | 完整行数据]
主键选用自增整数的原因：新行总是追加到最右叶节点，不会引起页分裂；UUID 等随机值会导致频繁页分裂，写入性能差。
非聚簇索引（Secondary Index / 二级索引）
叶节点存的是索引列值 + 主键值，不含完整行：
idx_email 叶节点：[email 值 | user_id（主键）]
回表：通过二级索引找到主键后，再用主键去聚簇索引查完整行，需要两次 B+ 树查找。
覆盖索引
查询所需的列全部包含在索引中，无需回表：
-- 索引：idx_name_age(name, age)
-- 以下查询不需要回表（name 和 age 都在索引里）
SELECT name, age FROM users WHERE name = 'Alice';
-- EXPLAIN 中 Extra 列显示：Using index ← 覆盖索引设计高频查询时，有意识地让查询列被索引覆盖，可大幅减少 IO。
联合索引与最左前缀原则
联合索引 (a, b, c) 在 B+ 树中按 a → b → c 顺序排列：
先按 a 排序 → a 相同时按 b 排序 → a、b 相同时按 c 排序
最左前缀规则：查询条件必须从索引最左列开始，中间不能跳跃：
| 查询 | 是否走索引 |
|---|---|
WHERE a = 1 | ✅ 用到 a |
WHERE a = 1 AND b = 2 | ✅ 用到 a、b |
WHERE a = 1 AND b = 2 AND c = 3 | ✅ 用到 a、b、c |
WHERE b = 2 | ❌ 跳过了 a |
WHERE a = 1 AND c = 3 | ⚠️ 只用到 a（跳过 b，c 失效） |
WHERE a > 1 AND b = 2 | ⚠️ a 范围查询后，b 失效 |
索引下推（ICP，Index Condition Pushdown）：MySQL 5.6+，对于联合索引中 b 的过滤条件，在存储引擎层过滤（而不是回表后再过滤），减少回表次数。
索引失效场景
| 场景 | 示例 | 原因 |
|---|---|---|
| 对索引列使用函数 | WHERE YEAR(created_at) = 2024 | 破坏了 B+ 树的有序性，需改为 BETWEEN 范围 |
| 隐式类型转换 | WHERE phone = 13800138000（phone 是 VARCHAR） | MySQL 对字符串做了数值转换，等同于加函数 |
LIKE 前缀通配符 | WHERE name LIKE '%Alice' | 无法利用 B+ 树前缀有序性 |
OR 连接非索引列 | WHERE id = 1 OR name = 'x'（name 无索引） | 任一条件无法用索引就全表扫描 |
!= / NOT IN / NOT EXISTS | — | 取反操作通常导致全表扫描（数据量小时优化器可能仍走索引） |
| 联合索引不满足最左前缀 | 见上表 | — |
| 索引列参与运算 | WHERE age + 1 = 18 | 改为 WHERE age = 17 |
EXPLAIN 执行计划解读
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';| 字段 | 重点值 | 含义 |
|---|---|---|
type | ALL → index → range → ref → eq_ref → const | 访问方式，越靠右越好；ALL = 全表扫描 |
key | 索引名 / NULL | 实际使用的索引 |
key_len | 数值 | 使用索引的字节长度（可判断联合索引用了几列） |
rows | 数值 | 预估扫描行数，越小越好 |
Extra | 见下 | 额外信息，关键信号 |
Extra 常见值：
| Extra 值 | 含义 |
|---|---|
Using index | 覆盖索引，无需回表 ✅ |
Using where | 存储引擎返回后，Server 层再过滤 |
Using filesort | 需要额外排序（无法利用索引顺序），应考虑索引优化 ⚠️ |
Using temporary | 使用临时表（常见于 GROUP BY、DISTINCT），需优化 ⚠️ |
Using index condition | 索引下推（ICP） |
NULL（无 Extra） | 正常回表查询 |
索引设计原则
- 高选择性列优先建索引：
status（3 个值）选择性低，user_id（唯一）选择性高 - 频繁出现在 WHERE / JOIN ON / ORDER BY 的列考虑建索引
- 联合索引列顺序：等值条件列放前，范围条件列放后（
(a, b)wherea=x AND b>y） - 覆盖索引优化高频查询：把 SELECT 中的列加入索引（宽索引）
- 索引不是越多越好：每个索引占空间，写操作需维护所有索引
- 避免冗余索引：
(a)已存在则(a, b)已包含它，单独的(a)索引可删除 - 大文本列用前缀索引：
INDEX idx_bio(bio(20))只索引前 20 字符
常见索引类型（MySQL）
| 类型 | 语法 | 说明 |
|---|---|---|
| 普通索引 | INDEX idx_name(col) | 无约束，仅加速查询 |
| 唯一索引 | UNIQUE INDEX idx_email(email) | 保证唯一，允许 NULL |
| 主键索引 | PRIMARY KEY (id) | 非空 + 唯一，聚簇索引 |
| 联合索引 | INDEX idx_ab(a, b) | 多列组合 |
| 全文索引 | FULLTEXT INDEX idx_ft(content) | 文本搜索，MATCH...AGAINST |
| 前缀索引 | INDEX idx_pre(email(10)) | 减小索引体积 |