一句话总结
MySQL InnoDB 索引底层采用 B+树数据结构。主键索引（聚簇索引）的叶子节点存储完整行数据，二级索引的叶子节点存储主键值（需要回表）。联合索引遵循最左前缀原则，覆盖索引可避免回表。索引不是越多越好，每次写入都要维护索引，一般建议单表索引不超过 5 个。
初级理解
为什么用 B+树而不是二叉树/B树/哈希？
| 数据结构 | 优点 | 缺点 | MySQL 为什么不用 |
|---|
| 二叉树 | 简单 | 退化成链表，IO 次数多 | 树太高，磁盘 IO 太多 |
| B树 | 节点存数据，查询快 | 范围查询需中序遍历 | 范围查询效率低，非叶子存数据浪费空间 |
| 哈希 | 等值查询 O(1) | 不支持范围查询、排序 | 无法满足 ORDER BY、BETWEEN 等需求 |
| B+树 | 矮胖、叶子有序链表 | 等值查询略慢于哈希 | MySQL 默认选择 |
B+树结构特点
1. 非叶子节点只存索引（key），不存数据 → 一个节点能存更多 key → 树更矮 → IO 更少
2. 所有数据存在叶子节点，叶子节点之间用双向链表连接 → 范围查询只需遍历链表
3. 每个节点是一个磁盘页（默认 16KB），一次 IO 读一页
# 查看 InnoDB 页大小
SHOW VARIABLES LIKE 'innodb_page_size'; # 默认 16384 = 16KB
# 估算 3 层 B+树能存多少数据
# 假设：主键 bigint(8B) + 指针(6B) = 14B/条
# 非叶子节点：16KB / 14B ≈ 1170 个 key
# 叶子节点：假设一行 1KB，16KB / 1KB = 16 行
# 3 层 B+树：1170 × 1170 × 16 ≈ 2190 万行
# 所以千万级数据，B+树高度也就 3 层，只需 3 次 IO
一句话总结：B+树 = 矮胖 + 叶子有序链表，兼顾等值查询和范围查询，是磁盘存储的最优选择。
中级深入
聚簇索引 vs 非聚簇索引（二级索引）
| 对比维度 | 聚簇索引（主键索引） | 非聚簇索引（二级索引） |
|---|
| 叶子节点存储 | 完整行数据 | 主键值 |
| 数量 | 一个表只能有一个 | 可以有多个 |
| 默认创建 | 主键自动创建 | 手动创建 |
| 查询过程 | 直接找到数据 | 先找主键 → 再回表查数据 |
# 聚簇索引查询（主键查询，最快）
SELECT * FROM users WHERE id = 100;
# B+树定位 → 叶子节点直接拿到整行数据
# 二级索引查询（需要回表）
SELECT * FROM users WHERE name = '张三';
# 1. 在 name 索引的 B+树中找到 name='张三' → 拿到主键 id=100
# 2. 用 id=100 去主键索引的 B+树中查 → 拿到整行数据
# 这个过程叫"回表"，多一次 IO
覆盖索引 — 避免回表的利器
如果查询的列全部在索引中，就不需要回表，直接从索引拿到结果。
# 有联合索引 idx_name_age(name, age)
# 需要回表（SELECT * 包含不在索引中的列）
SELECT * FROM users WHERE name = '张三';
# Extra: NULL（需要回表）
# 覆盖索引（查询列全在索引中）
SELECT name, age FROM users WHERE name = '张三';
# Extra: Using index（覆盖索引，不回表）
# 覆盖索引 + 最左前缀
SELECT name, age FROM users WHERE name LIKE '张%';
# Extra: Using index（覆盖索引生效）
最左前缀原则
联合索引 (a, b, c) 相当于创建了三个索引：(a)、(a,b)、(a,b,c)。查询条件必须从最左列开始匹配。
# 联合索引 idx_abc(a, b, c)
# ✅ 走索引（从 a 开始）
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM t WHERE a = 1 AND c = 3; # a 走了索引，c 不走（中间断了）
# ❌ 不走索引（没从 a 开始）
SELECT * FROM t WHERE b = 2;
SELECT * FROM t WHERE c = 3;
SELECT * FROM t WHERE b = 2 AND c = 3;
# 范围查询"断"最左前缀
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;
# a 走索引，b 走索引（范围），c 不走（b 是范围，后面的列失效）
索引下推（ICP — Index Condition Pushdown）
MySQL 5.6+ 特性：将部分 WHERE 条件下推到存储引擎层过滤，减少回表次数。
# 联合索引 idx_name_age(name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
# 没有 ICP：存储引擎查出所有 name LIKE '张%' 的行 → 全部回表 → Server 层过滤 age=25
# 有 ICP：存储引擎查出 name LIKE '张%' 的行 → 在引擎层过滤 age=25 → 只回表符合条件的行
# EXPLAIN 中 Extra: Using index condition 表示 ICP 生效
中级要点：聚簇索引叶子存数据，二级索引叶子存主键；覆盖索引避免回表；联合索引遵循最左前缀；ICP 在引擎层过滤减少回表。
高级拓展
索引失效的 7 大场景
| 场景 | 示例 | 原因 |
|---|
| 1. LIKE 前导模糊 | LIKE '%张' | B+树无法定位前缀 |
| 2. 索引列上做运算 | WHERE age+1=20 | 索引存的是 age 原值 |
| 3. 隐式类型转换 | WHERE phone=13800138000（phone是varchar） | 字符串转数字，索引失效 |
| 4. OR 连接非索引列 | WHERE a=1 OR b=2（b无索引） | 需要全表扫描判断 b |
| 5. != 或 <> | WHERE status != 1 | 优化器认为走全表更快 |
| 6. IS NULL / IS NOT NULL | WHERE name IS NULL | 取决于数据分布 |
| 7. 联合索引不满足最左前缀 | WHERE b=1（跳过a） | 无法定位起始范围 |
EXPLAIN 关键字段解读
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age > 20;
# 关键字段：
# type: 访问类型（ALL < index < range < ref < eq_ref < const < system）
# ALL: 全表扫描（最差）
# index: 全索引扫描
# range: 索引范围扫描（>, <, BETWEEN, IN）
# ref: 非唯一索引等值匹配
# eq_ref: 唯一索引等值匹配（JOIN 时）
# const: 主键等值匹配（最优）
# key: 实际使用的索引
# key_len: 使用的索引长度（越短越好，说明用的列少）
# rows: 预估扫描行数（越少越好）
# Extra: 额外信息
# Using index: 覆盖索引（好）
# Using index condition: ICP（好）
# Using where: Server 层过滤
# Using filesort: 文件排序（需优化）
# Using temporary: 临时表（需优化）
索引设计最佳实践
1. 选择区分度高的列：如身份证号 > 性别，区分度 = count(distinct col)/count(*)
2. 联合索引把区分度高的列放前面：如 (status, user_id) 不如 (user_id, status)
3. 尽量使用覆盖索引：减少回表，尤其是分页查询
4. 避免过多索引：每个索引都要维护，写入性能下降，建议单表 ≤ 5 个
5. 字符串前缀索引：长字符串列只索引前 N 个字符
# 前缀索引
ALTER TABLE users ADD INDEX idx_email(email(10)); # 只索引 email 前 10 个字符
# 计算最佳前缀长度
SELECT COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8,
COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel12
FROM users;
# 选择区分度接近 1 的最小长度
实战场景
场景：慢查询优化实战
# 原始慢查询（2.3秒）
SELECT * FROM orders WHERE status = 'paid'
ORDER BY create_time DESC LIMIT 20;
# EXPLAIN 分析
EXPLAIN SELECT * FROM orders WHERE status = 'paid'
ORDER BY create_time DESC LIMIT 20;
# type: ALL, rows: 5000000, Extra: Using where; Using filesort
# 优化1：创建联合索引
ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);
# type: ref, rows: 50000, Extra: Using index condition
# 查询时间：2.3s → 0.05s
# 优化2：覆盖索引 + 延迟关联（深分页场景）
SELECT * FROM orders o
INNER JOIN (
SELECT id FROM orders
WHERE status = 'paid'
ORDER BY create_time DESC
LIMIT 100000, 20
) tmp ON o.id = tmp.id;
# 子查询走覆盖索引，只回表 20 次
面试模拟
面试官：为什么 MySQL 用 B+树而不用 B 树？
你：三个原因：1）B+树非叶子节点不存数据，一个节点能存更多 key，树更矮，IO 更少；2）B+树叶子节点有双向链表，范围查询只需遍历链表，B 树需要中序遍历；3）B+树查询效率稳定，每次都要到叶子节点，B 树可能在非叶子节点就命中。
面试官：什么是回表？怎么避免？
你：回表是指通过二级索引查到主键后，再去主键索引查完整数据的过程，多一次 IO。避免方法：1）使用覆盖索引，查询列全在索引中；2）只查需要的列，不用 SELECT *；3）使用索引下推 ICP 在引擎层过滤。
面试官：联合索引 (a,b,c)，WHERE a=1 AND c=3 走索引吗？
你：a 走索引，c 不走。因为最左前缀原则要求连续匹配，中间 b 断了，c 无法使用索引。但 MySQL 5.6+ 有索引下推 ICP，会在引擎层用 c=3 过滤，减少回表次数。