数据库索引
数据库索引用额外的数据结构缩小读取范围。它还为排序和取列提供有序访问路径。本文讲解 MySQL 8.0 InnoDB 的索引分类、读取成本和优化器选择。
1. 面试核心
先记住这几条，再读正文- B+ 树访问。 B+ 树通过高扇出减少导航层数，有序叶子支持范围扫描。卡 · B+ 树索引
- 主键宽度。 InnoDB 的二级索引携带聚簇键，主键越宽，二级索引的空间成本越高。卡 · 聚簇键的选择与维护代价
- 联合键顺序。 联合索引按定义的列顺序排列，
WHERE条件的书写顺序独立于索引键顺序。 - 成本选择。 MySQL 按扫描、回表和排序成本选择访问路径。二级索引选择性低且回表量大时，全表扫描也能成为最优计划。卡 · B+ 树索引
- 执行计划验证。
EXPLAIN的rows是估算扫描量。EXPLAIN ANALYZE实际执行语句，提供行数、循环次数与耗时。卡 · B+ 树索引
2. 索引收益与维护成本
没有可用索引时，数据库需要扫描表中的记录，再逐行判断条件。索引按一个或多个键组织记录位置。执行引擎先定位到目标键或键值范围，再读取相关数据。
数据库以磁盘或缓冲池中的数据页组织数据。索引要减少随机读取、扫描页数、排序或回表次数。这些收益才会转化为实际延迟下降。
索引同时带来成本：
- 索引占用额外存储与缓冲池空间。
INSERT、UPDATE和DELETE需要维护相关索引。- 页分裂、日志写入和缓存压力会增加。
- 多个相似索引会提高优化器选择和运维成本。
因此，索引要围绕真实查询及其读取收益创建。
3. B+ 树索引
InnoDB 的聚簇索引和普通二级索引使用 B+ 树。高扇出减少导航层数，有序叶子支持范围扫描。结构与再平衡机制见 B+ 树。
4. 索引分类
索引名称分别表达约束、列组成和存储结构：
- 主键索引：它保证主键唯一且非空。在 InnoDB 中，它承担聚簇索引。
- 唯一索引：它约束非 NULL 键值唯一。MySQL 8.0 允许多个
NULL。 - 普通索引：它不附加唯一性约束。
- 联合索引：索引键由多列组成，按定义顺序逐级排列。
- 聚簇索引：叶子记录直接包含完整行数据。
- 二级索引：InnoDB 叶子记录包含二级索引列和聚簇索引键。
- 覆盖索引：它包含当前查询需要的所有列。覆盖描述的是查询与索引之间的关系。
一个索引可以同时是唯一索引、联合索引和二级索引；所需列齐全的查询可以被它覆盖。
5. 回表与覆盖索引
InnoDB 二级索引查询缺失列时，按聚簇键回表。覆盖查询可省去取列回表。查询示例与 MVCC 边界见 联合索引与覆盖索引。
6. 联合索引的列顺序
联合索引按定义的列顺序逐级排列。等值前缀和范围条件共同确定扫描区间。具体推演见 联合索引与覆盖索引。
7. 访问路径与成本估算
创建索引只提供一条候选路径。MySQL 会根据统计信息估算读取页数和结果行数。它还会估算回表与排序成本。以下因素会影响范围构造或全表扫描与索引访问的成本比较：
- 查询对索引列应用了函数，却没有匹配的函数索引。
- 字符串条件以通配符开头，例如
LIKE '%example.com'。 - 联合索引缺少必要的左侧列。
- 隐式类型转换或排序规则转换改变了比较方式。
- 条件选择性很低，二级索引会命中并回表读取大部分记录。
OR的某个分支没有合适索引，合并多条路径的成本过高。
范围查询、!= 和 IS NULL 都具备使用索引的条件。最终选择由数据分布和访问成本决定。应通过 EXPLAIN 查看访问方式、选中索引和预计扫描行数。再在安全环境使用 EXPLAIN ANALYZE 对照实测。
8. 索引设计依据
索引设计依据过滤、关联、排序、返回列与调用频率。完整审查步骤和写入成本评估见 索引设计。
9. 聚簇键的选择与维护代价
InnoDB 优先选择主键作为聚簇键。没有主键时，它选择第一个全部列非空的唯一索引。若仍不存在，它使用隐藏行 ID。键宽会放大所有二级索引的开销，选择机制和维护成本见 聚簇索引。
10. 执行计划的判断层次
| 证据 | 能说明什么 | 解读边界 |
|---|---|---|
possible_keys | 候选查找索引 | 一定使用这些索引 |
key 与 type | 选中的索引及访问方式 | 命中索引就很快；index 表示全索引扫描 |
rows、filtered | 优化器估计的扫描量与过滤比例 | 等于实际行数；统计信息有估算误差 |
Using index | 查询有覆盖访问条件 | 所有并发情况下绝不访问聚簇记录 |
Using index condition | 用索引列先过滤再决定是否取完整行 | 已经覆盖全部返回列 |
Using filesort | 排序不能完全由当前索引顺序提供 | 一定落盘排序 |
EXPLAIN ANALYZE 会实际执行受支持的语句。它报告迭代器的行数、循环次数和时间。因此，应在可控数据量与负载下使用它。索引已被使用但仍慢时，先检查扫描与返回行数的差距。再检查回表次数、排序、锁等待和缓存状态。完整字段解释见 执行计划。
11. 常见问题
11.1 索引数量与维护成本
增加索引会增加候选路径。它也会增加写放大、磁盘和缓冲池压力。只有新增路径成本更低时，查询才会受益。应保留能稳定改善重要查询的索引。
11.2 索引访问与查询耗时
索引名说明选中的访问路径；type = index 表示全索引扫描，rows 很大也说明仍要读取大量记录。还要看回表、额外排序、临时表和实际执行时间。
12 · 面试题卡片
本页题目与参考回答
12.1 MySQL 索引如何工作，哪些情况会让优化器不使用索引
考察重点
- 理解B+ 树、数据页与聚簇/二级索引。
- 理解联合索引、最左前缀、回表与覆盖查询。
- 理解成本估算、数据分布和执行计划验证。
相关内容：B+ 树索引、访问路径与成本估算、执行计划的判断层次。
参考回答
InnoDB 常规索引使用 B+ 树，以有序键减少需要读取的数据页。
聚簇索引叶子包含完整行，二级索引叶子包含二级键和主键；查询缺少结果列时要用主键回表。联合索引按定义顺序排列。能否定位取决于是否形成连续前缀，与 WHERE 条件书写顺序无关。
索引只是候选路径。函数条件缺少对应表达式索引时，会妨碍范围构造。左侧通配符、缺少必要左侧列也会妨碍范围构造。不利的类型转换也有同样影响。低选择性加大量回表会提高二级索引成本。范围条件本身不会让索引失效。最终要用 EXPLAIN 查看访问方式、key 和 rows。再用接近真实的数据比较实际扫描量与耗时。
12.2 B+ 树为什么适合数据库索引，哈希索引能完全替代它吗？
考察重点
- 理解页、扇出与树高。
- 理解等值、范围和排序的访问差异。
相关内容：B+ 树索引。
参考回答
数据库以页读写，B+ 树内部页集中保存导航项。
较高扇出有利于减少定位时访问的层数。叶子有序，定位后可以继续范围扫描。哈希适合等值定位，但不直接保留键顺序。因此，它无法通用地替代范围、排序访问。缓存、键宽、选择性和回表还会改变实际成本。
12.3 InnoDB 没有主键时怎样组织数据，为什么宽主键会增加索引成本？
考察重点
- 理解聚簇键选择顺序。
- 说明二级索引携带聚簇键的作用。
相关内容：聚簇键的选择与维护代价。
参考回答
InnoDB 没有主键时，先选第一个全列非空的唯一索引；仍不存在时，使用隐藏键组织聚簇索引。
二级索引携带聚簇键来定位完整行，因此较宽的主键会让每条二级索引记录更大，增加空间占用并减少可缓存的记录数。主键设计需要同时考虑稳定性、大小、插入分布和唯一性要求。