文日小栈

1. 完整回答

索引是数据库中用于加快数据检索的数据结构，核心作用是减少查询时需要扫描的数据量，提高查询效率。

以 MySQL 的 InnoDB 存储引擎为例，索引主要采用 B+ 树。B+ 树是一种多路平衡搜索树，数据按照索引键有序组织，查询时从根节点逐层定位到叶子节点，可以高效支持等值查询、范围查询和排序。

InnoDB 的索引主要分为聚簇索引和二级索引。聚簇索引通常由主键构建，叶子节点保存完整的行数据；二级索引的叶子节点保存索引列和对应的主键值。如果通过二级索引查询的字段没有被索引覆盖，就需要根据主键再次查询聚簇索引，这个过程叫作回表。

实际开发中，索引通常建立在查询条件、关联条件、排序字段上。对于多条件查询，可以使用联合索引，并根据最左前缀原则设计字段顺序。通过覆盖索引，还可以减少回表操作。

不过，索引会占用额外存储空间，而且插入、更新、删除时都需要维护索引。索引也不保证一定被使用，优化器会根据数据量、选择性和访问成本决定执行方式。

因此，索引设计需要结合实际业务 SQL，通过 EXPLAIN 分析执行计划，在查询性能、存储空间和写入成本之间取得平衡。

2. 核心原理与关键概念

2.1 核心原理

（1）B+ 树的存储结构

InnoDB 的 B+ 树索引具有以下特点：

非叶子节点：主要保存索引键和子节点指针，负责导航。
叶子节点：保存实际的索引记录，并按照索引键有序排列。
叶子节点之间：通过链表连接，便于范围扫描。
多路平衡结构：每个节点可以包含多个索引键，树的高度通常较低，减少磁盘 I/O。

（2）索引查询流程

以通过二级索引查询用户信息为例：

SQL 查询 → 优化器选择索引 → 从 B+ 树根节点逐层定位 → 找到叶子节点中的索引记录 → 获取主键 → 必要时回表 → 返回结果。

其中，是否回表取决于查询所需的数据能否直接从索引中获得。

如果查询字段已经被二级索引覆盖，就可以直接返回结果，减少访问聚簇索引的开销。

（3）联合索引的匹配机制

假设存在联合索引 (a, b, c)，索引先按 a 排序，a 相同时按 b 排序，前两列都相同时再按 c 排序。

因此：

a = 1：可以利用索引定位。
a = 1 AND b = 2：可以继续缩小查找范围。
a = 1 AND b = 2 AND c > 3：可以利用连续等值条件和范围条件定位。
b = 2 AND c = 3：通常无法直接利用该联合索引进行高效的连续范围定位。

当某列使用范围条件后，后续列通常难以继续缩小连续扫描范围，但仍可能参与索引内过滤。

2.2 关键概念

概念	含义
聚簇索引	叶子节点保存完整行记录，InnoDB 通常使用主键构建
二级索引	叶子节点保存索引列和主键值
联合索引	对多个字段按照指定顺序建立的索引
最左前缀原则	联合索引通常从最左侧字段开始匹配，才能高效缩小查找范围
回表	通过二级索引获取主键后，再查询聚簇索引获取所需字段
覆盖索引	一个索引包含查询需要的全部字段，可以避免额外回表
索引下推（ICP）	在存储引擎层利用索引字段提前过滤记录，减少不必要的回表
选择性	查询条件能够过滤掉的数据比例，影响索引的使用价值

3. 高频面试追问

3.1 为什么 MySQL 使用 B+ 树作为索引结构？

B+ 树能够同时兼顾等值查询、范围查询和磁盘访问效率，非常适合数据库索引。

相比普通二叉搜索树，B+ 树是多路平衡树，一个节点能够保存多个键值，通常可以用较少的层级定位数据，从而减少磁盘 I/O。

相比传统 B 树，B+ 树主要在叶子节点保存实际记录，非叶子节点可以容纳更多用于导航的键，具有较大的分支数。

相比哈希索引，B+ 树中的数据有序，除了等值查询，还可以支持范围查询以及部分排序操作。

例如查询某个月的订单，B+ 树可以先定位到起始日期，再沿着叶子节点扫描后续记录。

所以，B+ 树的主要优势是树高较低、查询稳定，并且支持有序访问。

3.2 聚簇索引和二级索引有什么区别？为什么会产生回表？

两者最核心的区别是叶子节点保存的内容不同。

InnoDB 的聚簇索引叶子节点保存完整行数据，因此根据主键查询，可以直接定位并读取记录。

二级索引叶子节点保存索引列和主键值。如果查询还需要其他字段，就要根据主键再次访问聚簇索引，这就是回表。

例如用户表包含 id、name、age，在 name 上建立二级索引。

执行 SELECT * FROM user WHERE name = 'Tom' 时，会先通过 name 索引找到主键，再通过主键获取完整记录。

回表可能带来额外的随机 I/O，匹配数据越多，回表成本可能越高。因此可以通过合理设计覆盖索引、减少返回字段等方式优化。

3.3 什么是覆盖索引？它和索引下推有什么区别？

覆盖索引可以避免回表，索引下推可以减少不必要的回表次数。

覆盖索引指查询需要的字段都能从同一个索引中获取。

例如建立 (name, age) 联合索引，执行：

SELECT age FROM user WHERE name = 'Tom'

查询条件和返回字段都包含在索引中，可以直接读取二级索引记录。

索引下推则发生在查询仍然需要读取完整行数据的场景中。

例如：

SELECT * FROM user WHERE name LIKE 'T%' AND age = 20

在 (name, age) 索引上，数据库可以先定位符合 name 前缀的索引记录，再利用索引中的 age 过滤数据，最后对满足条件的记录回表。

因此，覆盖索引减少的是额外的数据读取，索引下推减少的是无效的数据读取。

3.4 联合索引为什么要遵循最左前缀原则？范围查询会导致索引失效吗？

最左前缀原则源于联合索引的多字段有序存储方式。范围查询通常只会限制后续字段继续缩小索引扫描范围的能力。

例如联合索引 (a, b, c)，数据按照 a → b → c 的顺序排列。

查询 a = 1 AND b = 2 AND c = 3，可以通过三个字段共同定位。

查询 a = 1 AND b > 2 AND c = 3，可以利用 a 和 b 定位范围，但由于这个范围内的 c 并非全局有序，通常无法继续利用 c 缩小连续扫描区间。

不过，c = 3 仍可能通过索引下推参与过滤。

另外，SQL 中 WHERE 条件的书写顺序通常不影响最左前缀匹配，优化器会分析和调整条件。

继续追问：缺少最左侧字段，联合索引就完全不能使用吗？

也不能绝对这么判断。MySQL 在满足特定条件时可以使用跳跃扫描（Skip Scan），也可能进行全索引扫描。不过，这些方式有使用条件和成本限制，通常优先设计符合主要查询模式的索引。

3.5 哪些情况容易导致索引无法有效使用？

常见原因包括无法利用索引有序性定位、联合索引匹配受限，以及优化器认为索引访问成本过高。

比较典型的情况有：

对索引列使用函数或计算：例如 YEAR(create_time) = 2026，普通 create_time 索引通常无法直接进行范围定位，可以改写成时间区间查询。
LIKE 以通配符开头：例如 LIKE '%abc'，通常无法利用普通 B+ 树索引高效定位前缀。
隐式类型转换：例如字符串字段与数字直接比较，可能导致无法利用索引进行查找。
联合索引缺少前导列：通常无法进行高效的连续范围定位。
查询匹配数据过多：优化器可能选择全表扫描，避免大量回表。

需要注意，OR、IN、IS NULL、!= 等条件并不必然导致索引失效，是否使用索引取决于具体条件、索引结构和执行计划。

另外，MySQL 8.0.13 及之后支持函数索引，特定表达式也可以建立索引。

3.6 联合索引的字段顺序应该怎么设计？

联合索引要根据实际查询模式设计，综合考虑等值条件、范围条件、排序需求和字段选择性。

通常会先考虑经常使用的等值查询字段，再考虑范围查询和排序字段，同时尽量控制索引宽度。

例如订单表经常执行以下查询：

WHERE user_id = ? AND status = ? ORDER BY create_time DESC

可以考虑建立 (user_id, status, create_time) 联合索引。

这样能够先定位指定用户和状态的订单，再按创建时间顺序读取数据，在适当的执行计划下减少额外排序。

但如果大量查询只根据 create_time 筛选，这个联合索引就未必合适。

所以，索引字段顺序需要结合真实 SQL 和数据分布验证。

继续追问：区分度最高的字段一定要放在最前面吗？

不一定。区分度是重要因素，但还要考虑查询条件的组合、排序需求和索引复用情况。尤其是多个等值条件同时出现时，不能仅根据区分度决定字段顺序。

3.7 为什么建立了索引，MySQL 仍然可能选择全表扫描？

因为 MySQL 优化器会比较不同执行方案的成本，使用索引不一定总是更快。

例如某张订单表有一百万条数据，其中九十万条状态都是已完成。

如果通过状态索引查询全部已完成订单，并且需要返回大量非索引字段，就可能产生大量回表操作。

这种情况下，顺序扫描整张表可能比通过二级索引反复回表更高效。

优化器会根据索引统计信息、估算扫描行数、查询条件和访问成本选择执行计划。

因此，遇到没有使用索引的情况，需要先检查选择性、返回字段、扫描行数和实际耗时，再决定是否调整索引。

3.8 如何通过 EXPLAIN 判断索引是否使用合理？

主要看实际选择的索引、访问方式、预计扫描行数，以及是否发生额外排序和回表。

常用字段包括：

字段	主要作用
`type`	表访问方式，常见有 `const`、`ref`、`range`、`index`、`ALL`
`possible_keys`	优化器可能考虑的索引
`key`	实际选择的索引
`key_len`	使用的索引键长度，有助于分析联合索引匹配情况
`rows`	预计需要检查的行数
`Extra`	展示覆盖索引、索引下推、额外排序等信息

其中：

Using index：通常表示使用覆盖索引读取所需字段。
Using index condition：表示使用了索引下推。
Using filesort：表示需要额外排序。
type = ALL：通常表示全表扫描。
type = index：表示扫描索引树，不代表一定高效。

EXPLAIN 提供的是执行计划及相关估算。如果需要验证实际扫描行数和耗时，可以使用 EXPLAIN ANALYZE。

分析索引效果时，不能只看有没有使用索引，更要看扫描量和实际执行成本。

3.9 为什么索引不能建立得越多越好？主键应该如何选择？

索引能够提升部分查询性能，但会增加存储和数据写入的成本。

每增加一个索引，都需要额外保存索引记录。插入、删除以及修改索引字段时，也需要维护相应的索引结构，可能涉及页面分裂、合并等操作。

对于 InnoDB，主键还会保存在二级索引记录中，因此主键越长，二级索引通常也越大。

所以，主键通常倾向于选择短小、唯一、稳定的字段。

在适合的业务中，可以使用自增整数主键。递增写入通常具有较好的局部性，有利于减少随机插入造成的页面分裂。

但分布式系统中还需要考虑主键生成方式、全局唯一性和写入并发等问题。

索引设计的核心是保留真正有价值的索引，减少冗余索引，同时兼顾读写性能。

3.10 实际项目中，如何优化一条因为索引问题导致的慢 SQL？

通常按照定位慢 SQL、分析执行计划、调整索引或查询方式、验证效果的顺序处理。

例如订单表数据量较大，用户经常按状态查询订单，并按照创建时间倒序分页。

首先，通过慢查询日志定位 SQL，再使用 EXPLAIN 检查是否存在全表扫描、大量回表或者额外排序。

如果原先只有 user_id 单列索引，可以结合查询条件考虑 (user_id, status, create_time DESC) 联合索引，使过滤和排序尽量利用同一个索引。

接着检查返回字段。如果只需要少量字段，可以评估覆盖索引，但需要权衡索引宽度。

如果使用深度分页，LIMIT offset, size 可能需要跳过大量记录，可以在业务允许的情况下改为基于上一页末尾排序键的游标分页。

最后，通过 EXPLAIN ANALYZE 和实际压测比较扫描行数、查询耗时及写入开销，确认优化是否有效。

4. 易混淆概念对比

对比概念	核心区别
聚簇索引 vs 二级索引	聚簇索引叶子节点保存完整行数据；二级索引保存索引列和主键，读取其他字段通常需要回表
主键索引 vs 唯一索引	主键要求唯一且非空，一张表只能有一个；唯一索引用于约束键值唯一，可以有多个，在 MySQL 中可空唯一索引通常允许多个 `NULL`
单列索引 vs 联合索引	单列索引针对一个字段；联合索引按多个字段组合排序，可以优化多条件查询及部分排序、分组操作
覆盖索引 vs 索引下推	覆盖索引让查询直接从索引中获取全部所需字段；索引下推在回表前利用索引字段过滤记录
B+ 树索引 vs 哈希索引	B+ 树具有有序性，支持等值、范围和部分排序操作；哈希索引主要适用于等值查找，不支持直接利用哈希顺序进行范围扫描

另外，索引类型与索引结构属于不同的分类维度。主键索引、唯一索引、普通索引主要描述约束或用途；B+ 树、哈希索引主要描述底层数据结构。

5. 总结

索引是数据库用于加快数据检索的数据结构，核心作用是减少扫描行数和数据访问成本。InnoDB 主要使用 B+ 树组织索引，利用多路平衡结构和有序叶子节点支持等值查询、范围查询及部分排序操作。聚簇索引保存完整行数据，二级索引保存索引列和主键值，因此查询可能涉及回表。实际开发中，可以通过联合索引、覆盖索引和索引下推提升查询效率，同时需要遵循索引的有序匹配规律。索引会带来额外的存储和写入成本，而且优化器不一定选择索引，所以最终需要结合业务 SQL、执行计划和实际耗时判断索引是否合理。