跳到主要内容

数据库索引

数据库索引用额外的数据结构换取更小的读取范围，适合加速稳定且有选择性的查询路径。

1. 索引减少需要读取的数据页​

没有可用索引时，数据库通常需要扫描表中的大量记录，再逐行判断条件。索引按一个或多个键组织记录位置，使执行引擎可以先定位到目标键或键值范围，再读取相关数据。

索引优化的对象不只是比较次数。数据库数据通常位于磁盘或缓冲池的数据页中，减少随机读取、扫描页数、排序和回表次数，才会转化为实际延迟下降。

索引同时带来成本：

  • 占用额外存储与缓冲池空间。
  • INSERT、UPDATE 和 DELETE 需要维护相关索引。
  • 页分裂、日志写入和缓存压力会增加。
  • 多个相似索引会提高优化器选择和运维成本。

因此，索引要围绕真实查询创建，不能按“常用字段”批量添加。

2. InnoDB 常规索引使用 B+ 树​

InnoDB 的聚簇索引和普通二级索引使用 B+ 树。内部页保存用于导航的键与子页指针，叶子页按键值顺序保存记录，并通过相邻页形成有序范围。

B+ 树适合数据库页的原因包括：

  • 一个页面可以容纳大量导航项，树高通常较低。
  • 等值查询可以从根页逐层定位到叶子页。
  • 范围查询定位起点后，可以继续顺序读取相邻叶子页。
  • 插入和删除通过分裂、合并等操作维持平衡。

哈希索引能够支持等值定位，但没有键值顺序，不能直接用于范围和排序。全文搜索常用倒排索引；空间查询还会使用 R-tree 等结构。索引类型应由访问模式决定。

3. 索引名称描述不同维度​

常见名称并不都处在同一分类维度：

  • 主键索引：保证主键唯一且非空；在 InnoDB 中通常也是聚簇索引。
  • 唯一索引：限制索引键不能重复，是否允许多个 NULL 由数据库语义决定。
  • 普通索引：不附加唯一性约束。
  • 联合索引：索引键由多列组成，按定义顺序逐级排列。
  • 聚簇索引：叶子记录直接包含完整行数据。
  • 二级索引：InnoDB 叶子记录包含二级索引列和聚簇索引键。
  • 覆盖索引：当前查询需要的列都能从某个索引获得，是查询与索引之间的关系。

一个索引可以同时是唯一索引、联合索引和二级索引；一个查询也可能恰好被它覆盖。

4. 二级索引查询可能需要回表​

假设 users 表的主键是 id，并为 email 创建二级索引：

CREATE INDEX idx_users_email ON users(email);

查询完整用户记录时，InnoDB 先在二级索引中找到 email 对应的主键，再用主键访问聚簇索引：

SELECT * FROM users WHERE email = '[email protected]';

第二次访问称为回表。如果查询只需要 id 与 email，二级索引已经包含全部结果列，可以直接返回：

SELECT id, email FROM users WHERE email = '[email protected]';

覆盖查询能减少回表，但把大量列加入索引会增加存储和写入成本。需要结合查询频率、结果行数和字段宽度判断。

5. 联合索引按照定义顺序排列​

联合索引 (status, created_at) 先按 status 排列，在 status 相同的范围内再按 created_at 排列。因此下面的条件可以形成连续查找范围：

WHERE status = 1
  AND created_at >= '2026-01-01'

只有 created_at 条件时，每个 status 分组都可能包含目标记录，传统的最左前缀无法直接给出一个连续起点。

WHERE 中条件的书写顺序不会改变这个结论。优化器可以重排 a = 1 AND b = 2 与 b = 2 AND a = 1；真正影响访问路径的是索引列顺序、比较方式和数据分布。

范围条件也不会让索引整体失效。对于 (a, b, c)，a = 1 AND b > 10 AND c = 5 通常可以用 a 与 b 确定扫描范围；c 可能继续参与索引条件下推或覆盖，但通常不能缩短由 b 范围确定的边界。

6. 优化器按成本选择访问路径​

创建索引只提供一条候选路径，MySQL 会根据统计信息估算读取页数、结果行数、回表与排序成本。以下情况可能无法形成有效索引范围，或让全表扫描更便宜：

  1. 对索引列应用函数，而没有匹配的函数索引。
  2. 字符串条件以通配符开头，例如 LIKE '%example.com'。
  3. 联合索引缺少必要的左侧列。
  4. 隐式类型转换或排序规则转换改变了可比较方式。
  5. 条件选择性很低，二级索引会命中并回表读取大部分记录。
  6. OR 的某个分支没有合适索引，合并多条路径的成本过高。

范围查询、!= 和 IS NULL 都可能使用索引，不能按操作符直接判定“索引失效”。应通过 EXPLAIN 查看实际访问方式、选中索引和预计扫描行数，再在安全环境使用 EXPLAIN ANALYZE 对照实测。

7. 从查询反推索引​

设计索引时依次检查：

  1. 这条查询的过滤、关联、排序和分页条件是什么。
  2. 等值条件与范围条件如何排列，能否同时支持排序。
  3. 预计返回多少行，数据分布是否倾斜。
  4. 是否值得加入少量结果列减少高频回表。
  5. 新索引是否与现有索引重复，写入成本能否接受。
  6. 上线后用慢查询、执行计划和业务延迟验证收益。

索引应服务一组明确访问路径。查询模式变化后，旧索引也要重新评估和清理。

8. 常见问题​

8.1 索引越多，查询就越快吗​

不会。更多索引只会增加候选路径，不能保证更优计划，还会增加写放大、磁盘和缓冲池压力。应保留能稳定改善重要查询的索引。

8.2 EXPLAIN 中出现索引名就说明查询很快吗​

不能。type = index 可能表示全索引扫描，rows 很大也说明仍要读取大量记录。还要看回表、额外排序、临时表和实际执行时间。

9. 面试题​

9.1 MySQL 索引如何工作，哪些情况会让优化器不使用索引​

出现公司：美团

考察重点

  • B+ 树、数据页与聚簇/二级索引。
  • 联合索引、最左前缀、回表与覆盖查询。
  • 成本估算、数据分布和执行计划验证。

相关内容：第 1 节“索引减少需要读取的数据页”至第 7 节“从查询反推索引”。

参考回答

InnoDB 常规索引使用 B+ 树，以有序键减少需要读取的数据页。聚簇索引叶子包含完整行，二级索引叶子包含二级键和主键；查询缺少结果列时要用主键回表。联合索引按定义顺序排列，能否定位取决于是否形成连续前缀，与 WHERE 条件书写顺序无关。

索引只是候选路径。函数表达式、左侧通配符、缺少联合索引左侧列、类型转换等可能无法形成有效范围；低选择性或大量回表也可能让全表扫描更便宜。范围条件本身不会让索引失效。最终要用 EXPLAIN 查看访问方式、key 和 rows，并在接近真实的数据上比较实际扫描量与耗时。

参考资料
  1. MySQL 8.4：Optimization and Indexes
  2. MySQL 8.4：Clustered and Secondary Indexes
  3. MySQL 8.4：Multiple-Column Indexes
  4. MySQL 8.4：EXPLAIN