索引原理与优化

问题

MySQL 索引的底层数据结构是什么？聚簇索引和非聚簇索引有什么区别？什么是覆盖索引、最左前缀原则？常见的索引失效场景有哪些？

答案

为什么使用 B+ 树

MySQL（InnoDB）使用 B+ 树作为默认索引结构。选择 B+ 树而非其他数据结构的原因：

数据结构	缺点	为什么不选
哈希表	仅支持等值查询，不支持范围查询和排序	无法满足 >, <, BETWEEN, ORDER BY
二叉搜索树	可能退化为链表，O(n)	不平衡时效率差
AVL / 红黑树	树高 O(log n)，数据量大时树太高	1000 万数据需 ~23 次 I/O
B 树	数据存在所有节点，不利于范围查询	范围查询需要回溯，且节点更大
B+ 树	✅ 树矮胖、叶子有序链表、范围查询高效	MySQL 首选 ✅

B+ 树的关键特性

B+ 树的关键特点：

1. 非叶子节点只存索引 key，不存数据，一个页能存更多 key → 树更矮
2. 叶子节点存储所有数据，且通过双向链表连接 → 范围查询只需顺序遍历
3. 树高通常 3~4 层：假设每页 16KB，主键为 bigint(8B)，指针 6B，那么一个非叶子节点可存 16KB/(8+6)B ≈ 1170 个指针。3 层即可存储 1170 × 1170 × 16 ≈ 2000 万行数据，只需 3 次磁盘 I/O

索引分类

按数据组织方式

类型	叶子节点存储	数量	说明
聚簇索引	完整行数据	每表仅一个	即主键索引，数据按主键顺序物理存储
二级索引	索引列值 + 主键	可以多个	查询需回表（通过主键再查聚簇索引）

聚簇索引的选择

1. 有主键 → 主键就是聚簇索引
2. 无主键但有唯一非空索引 → 第一个唯一非空索引作为聚簇索引
3. 都没有 → InnoDB 自动生成隐藏的 ROW_ID 作为聚簇索引

按字段特性

索引类型	关键字	说明
主键索引	PRIMARY KEY	唯一且非空，自动创建聚簇索引
唯一索引	UNIQUE	列值唯一，允许 NULL
普通索引	INDEX / KEY	无约束
前缀索引	INDEX(col(n))	对字符串前 n 个字符建索引
全文索引	FULLTEXT	全文检索

按字段个数

类型	示例	说明
单列索引	INDEX idx_name(name)	单个字段
联合索引	INDEX idx_name_age(name, age)	多个字段组合

回表与覆盖索引

回表（Table Lookup）：通过二级索引找到主键值后，再到聚簇索引中查找完整行数据。

发生回表

-- 假设有索引 idx_name(name)
SELECT * FROM user WHERE name = '张三';
-- 1. 在 idx_name 中找到 name='张三' 的主键 id=5
-- 2. 回表：用 id=5 到聚簇索引查找完整行数据

覆盖索引（Covering Index）：查询的字段全部在索引中，无需回表。

覆盖索引，不需要回表

-- 假设有联合索引 idx_name_age(name, age)
SELECT name, age FROM user WHERE name = '张三';
-- 索引 idx_name_age 已包含 name 和 age，直接返回
-- EXPLAIN 中 Extra 列显示 Using index

性能优化要点

尽量使用覆盖索引避免回表，这是 SQL 优化中最常用的技巧之一。在 EXPLAIN 结果中，Extra 列出现 Using index 说明使用了覆盖索引。

最左前缀原则

联合索引遵循最左前缀匹配原则：查询从联合索引的最左列开始匹配，遇到范围查询（>, <, BETWEEN, LIKE）后停止匹配。

假设有联合索引 idx_a_b_c(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 b = 2 AND c = 3	❌ 不使用	缺少最左列 a
WHERE a = 1 AND c = 3	✅ 使用 a	跳过了 b，只用到 a
WHERE a = 1 AND b > 2 AND c = 3	✅ 使用 a, b	b 是范围查询，c 无法使用
WHERE a = 1 ORDER BY b	✅ 使用 a, b	ORDER BY 也可以利用索引

MySQL 8.0 索引跳跃扫描

MySQL 8.0.13+ 引入了 Index Skip Scan，在某些情况下即使缺少最左列也能使用索引（当最左列基数很小时）。但不应依赖此优化，设计索引时仍应遵循最左前缀原则。

索引下推（ICP）

Index Condition Pushdown（ICP） 是 MySQL 5.6 引入的优化：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引下推示例

-- 联合索引 idx_name_age(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 25;

-- 无 ICP：在 idx_name_age 中找到所有 name LIKE '张%' 的记录，全部回表后再过滤 age
-- 有 ICP：在 idx_name_age 中找到 name LIKE '张%' 后，直接在索引层判断 age=25，
--         过滤后只对满足条件的记录回表

EXPLAIN 中 Extra 列显示 Using index condition 表示使用了索引下推。

常见索引失效场景

场景	示例	原因
对索引列使用函数	WHERE YEAR(create_time) = 2024	函数破坏了索引的有序性
对索引列做运算	WHERE id + 1 = 10	表达式计算无法匹配索引
隐式类型转换	WHERE phone = 13800138000（phone 是 varchar）	相当于 CAST(phone AS signed)
LIKE 以 % 开头	WHERE name LIKE '%三'	无法利用最左前缀
OR 条件部分无索引	WHERE a = 1 OR b = 2（b 无索引）	优化器可能放弃索引改为全表扫描
联合索引不满足最左前缀	WHERE b = 2（索引 a, b, c）	跳过了最左列
NOT IN, NOT EXISTS	某些情况	优化器可能选择全表扫描
IS NOT NULL	某些情况	取决于数据分布和优化器判断

隐式类型转换

这是最容易被忽视的索引失效场景。当字段类型为 varchar 但查询条件传入数字时，MySQL 会对字段做类型转换函数，导致索引失效。务必保证查询参数类型与字段类型一致。

索引设计原则

1. 为 WHERE、ORDER BY、JOIN 的字段建索引
2. 使用联合索引代替多个单列索引（一个查询通常只用一个索引）
3. 高区分度的列放在联合索引左侧（如 user_id 优于 status）
4. 利用覆盖索引避免回表
5. 使用前缀索引减少索引大小（适用于长字符串）
6. 控制索引数量：索引会增加写操作开销（INSERT/UPDATE/DELETE 需维护索引）
7. 主键建议使用自增 bigint：保证聚簇索引的有序插入，减少页分裂

---

常见面试问题

Q1: 为什么 MySQL 选择 B+ 树而不是 B 树做索引？

答案：

三个关键区别：

1. B+ 树非叶子节点不存数据，只存索引 key，同一页能容纳更多 key，树更矮，磁盘 I/O 更少
2. B+ 树叶子节点用双向链表连接，范围查询只需找到起始位置后顺序遍历，而 B 树需要中序遍历整棵树
3. B+ 树查询性能更稳定：所有数据都在叶子节点，查询路径长度一致（都是从根到叶），而 B 树的数据分布在各层节点

Q2: 聚簇索引和非聚簇索引的区别？

答案：

• 聚簇索引：叶子节点存储完整行数据，数据按索引顺序物理存储。InnoDB 每张表有且仅有一个聚簇索引（通常是主键）。查询主键时直接获得数据，不需要回表。
• 非聚簇索引（二级索引）：叶子节点存储索引列值 + 主键值。查询时如果需要的字段不在索引中，需要回表——用主键值到聚簇索引中再查一次。

核心要记住：聚簇索引 = 数据即索引，非聚簇索引查非覆盖字段需要回表。

Q3: 什么是回表？如何优化？

答案：

回表：通过二级索引查到主键后，再用主键去聚簇索引中查找完整行数据的过程。回表意味着多一次 B+ 树查询，对性能有影响。

优化方式：

1. 覆盖索引：调整索引或查询字段，使所需字段都在索引中，避免回表
2. 索引下推（ICP）：在索引层提前过滤，减少回表次数（MySQL 自动优化）
3. 减少 SELECT *：只查需要的字段，更容易命中覆盖索引

Q4: 联合索引 (a, b, c)，WHERE a = 1 AND c = 3 能用到索引吗？

答案：

可以用到索引，但只用到 a 列。因为最左前缀原则要求从左到右连续匹配，跳过了 b，所以 c 无法利用索引排序直接定位。

执行过程：

1. 通过索引定位到 a = 1 的所有记录
2. 对这些记录逐个检查 c = 3（如果 c 在索引中，可通过 ICP 在索引层过滤；否则需要回表后过滤）

优化建议：如果 a + c 的查询很频繁，可以建立 idx_a_c(a, c) 或调整联合索引顺序。

Q5: 为什么建议主键使用自增 ID？

答案：

1. 减少页分裂：自增主键保证新记录总是追加到 B+ 树最后，不会插入到中间导致现有页分裂
2. 插入性能高：顺序写入，减少随机 I/O
3. 二级索引更小：主键存储在所有二级索引的叶子节点中，整型主键占用空间小（bigint 8 字节），而 UUID 占 36 字节

UUID 作为主键的问题：无序插入导致频繁页分裂、主键体积大导致二级索引膨胀、范围查询效率低。

如果业务需要 UUID（如分布式环境），可以考虑 有序 UUID（如 UUIDv7）或 雪花算法 ID。

Q6: 前缀索引是什么？有什么限制？

答案：

前缀索引是对字符串字段的前 n 个字符建立索引，减少索引大小：

-- 对 email 前 6 个字符建索引
ALTER TABLE user ADD INDEX idx_email(email(6));

选择前缀长度：通过计算区分度来确定合适的前缀长度：

-- 计算不同前缀长度的区分度
SELECT
  COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel4,
  COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS sel6,
  COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8,
  COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM user;
-- 选择区分度接近完整字段的最短前缀

限制：前缀索引无法用作覆盖索引（因为索引中只有部分值，必须回表验证完整值），也无法用于 ORDER BY 和 GROUP BY。

相关链接

• MySQL 官方文档 - InnoDB Indexes
• MySQL 官方文档 - 多列索引
• MySQL 官方文档 - Index Condition Pushdown
• MySQL 官方文档 - EXPLAIN 输出