数据库索引是面试中的高频考点。下面以 MySQL InnoDB 存储引擎为主线把索引的数据结构、功能分类以及几个容易踩坑的细节串起来。一、索引的分类索引的数据结构常见的有 Btree 和 Hash 等从功能上还可以划分主键索引、唯一索引、联合索引、前缀索引等。一按数据结构划分1. Hash 索引如果只是单纯判断值相等哈希算法确实非常快算出 key 的哈希值直接定位到桶。但数据库查询很少只有“等值”这一种形态范围查询、、BETWEEN、排序、最左前缀匹配都无法用哈希高效完成所以 InnoDB 的主角是 Btree。注意准确的说法要分两层。InnoDB不允许用户手动创建哈希索引从这个意义上说它“不支持哈希索引”。但 InnoDB 内部有一套自适应哈希索引Adaptive Hash Index, AHI当它发现某些索引值被频繁等值查询、且寻路路径较长时会在 Buffer Pool 中自动为这些热点页建立哈希索引加速等值查找。AHI 完全由 InnoDB 自调优建立DBA 无法手动指定“给某个字段建哈希索引”。此外AHI 在大量 LIKE、JOIN 场景下可能成为维护负担必要时可以手动关闭。2. Btree 索引Btree 是 InnoDB 的默认索引结构。主键索引聚簇索引和二级索引非聚簇索引都是 Btree。核心特征非叶子节点只存 key 和指针不存真正的数据行所有数据都集中在叶子节点叶子节点之间用双向链表连接因此范围查询只需要定位到起点再沿链表顺序扫描即可。查询过程大致如图所示查找索引值为 24 的记录从根节点进入24 落在 20 和 50 之间沿指针走到“30 节点”24 小于 30再走到叶子层(20, 24, 26)在叶子节点中定位到 24。为什么用 Btree 而不是二叉树InnoDB 以页Page为磁盘 I/O 的基本单位默认一页 16KB。二叉树每个节点最多两个子节点数据量大时树高会迅速增加每下降一层就意味着一次额外的磁盘 I/O。Btree 的非叶子节点只放 key 和指针一页能容纳的 key 数量扇出远大于二叉树树高可以压得很低。以 bigint 主键为例一页大约能放 1170 个 key如果一行数据 1KB一页叶子能放约 16 行三层 Btree 大约可以容纳 2000 万行级别日常查询通常只需 23 次 I/O。为什么不用 B 树B 树的非叶子节点也要存数据行。这意味着同样一页 16KBB 树能放的 key 数量比 Btree 少扇出更低、树更高同时 B 树的查找效率不稳定有的记录在非叶子层就命中有的必须走到叶子层。Btree 把数据全部下沉到叶子层每次查询都要走到叶子效率稳定非叶子节点只做索引扇出更高叶子节点的双向链表则让范围查询天然高效。二级索引的查询过程二级索引的叶子节点存放的是索引列的值 主键值而不是完整的行数据。用二级索引查到主键值后还需要拿这个主键值回到聚簇索引中再查一次才能得到完整行——这就是回表。回表是随机 I/O数据量大时往往是查询变慢的主要来源。3. 全文索引Fulltext全文索引主要服务于文本字段的关键词检索底层是倒排索引从“文档 → 单词”反过来建立“单词 → 文档/位置”的映射从而避免LIKE %关键词%这种无法使用 Btree 的全表扫描。使用方式MATCH(字段名) AGAINST(关键词);版本与中文注意点MySQL 5.6 之前只有 MyISAM 支持全文索引5.6 中 InnoDB 加入全文索引但不支持中文分词从 5.7.6 开始内置ngram 全文解析器才具备可用的中文分词能力。建全文索引时需要指定解析器例如FULLTEXT(title, body) WITH PARSER ngram如果没有 ngram 而直接对中文建全文索引分词效果会很不理想。二按功能划分主键索引、唯一索引、普通索引不再赘述重点说联合索引和前缀索引。1. 联合索引联合索引是把多个字段按顺序组合在一起建立的索引。它的关键规则是最左匹配原则查询条件必须从索引的最左列开始并且不能跳过中间的列否则无法完整利用索引。假设有联合索引idx_a_b_c(a, b, c)各种条件的索引使用情况如下查询条件索引使用情况原因a 1使用 a 列从最左列开始a 1 AND b 2使用 a、b 列连续匹配a 1 AND b 2 AND c 3使用 a、b、c 列完整匹配b 2 AND a 1使用 a、b 列优化器会自动调整等值条件的顺序a 1 AND c 3仅使用 a 列跳过 b 列c 列无法使用索引b 2 AND c 3完全无法使用索引缺少最左列 aa 1 AND b 2仅使用 a 列范围查询截断a 1 AND b 2 AND c 3使用 a、b 列范围查询后的 c 列无法使用容易被忽略的细节范围查询的截断效应。联合索引中一旦某一列使用了范围查询、该列右侧的所有列都无法继续用于索引定位。例如WHERE a 1 AND b 2 AND c 3中c不会走索引。设计联合索引时通常把等值条件放在前面、范围条件放在最后就是为了避免这种截断。不过有一个常见的“例外”和在某些执行计划下可能不会像、那样严格截断右侧列但这依赖于优化器的判断不应作为设计索引的依据。稳妥的做法仍然是范围列尽量放在联合索引的末尾。另一个容易混淆的点最左前缀原则不是一条“人为规定”而是Btree 按联合索引列顺序排序的自然结果。联合索引先按 a 排序a 相同再按 b 排序b 相同再按 c 排序跳过 a 直接查 bBtree 无法利用有序性定位只能放弃索引。2. 前缀索引前缀索引只取字段的前几个字符建立索引适用于CHAR、VARCHAR、BINARY、VARBINARY类型可以显著减小索引体积。注意前缀索引的代价是无法用于ORDER BY/GROUP BY的完整排序也无法作为覆盖索引使用因为索引中只保留了前缀不包含完整列值。选择前缀长度时需要保证足够的区分度例如用COUNT(DISTINCT LEFT(col, n)) / COUNT(*)来评估。二、其他需要注意的点覆盖索引避免回表的优化手段如果一条查询需要的所有列都已经包含在所使用的索引中就不需要回表这就是覆盖索引。最典型的做法是把查询涉及的列建成联合索引让二级索引的叶子节点直接“覆盖”查询需求。在EXPLAIN中覆盖索引的标志是Extra: Using index。注意Using index的含义是“没有回表索引自己就够了”而不是“用了索引”走了索引但回了表Extra可能什么都不显示。索引下推Index Condition Pushdown, ICPMySQL 5.6 引入的优化。在没有 ICP 时联合索引中无法用于定位的列条件例如被范围查询截断后的后续列条件只能在 Server 层回表之后再过滤有了 ICP引擎层会先用索引中已有的列做一次pre-filter减少不必要的回表次数。EXPLAIN中对应Extra: Using index condition。常见索引失效场景索引的本质是有序的数据结构一旦查询方式破坏了“有序性”或者优化器判断全表扫描代价更低索引就可能不被使用。常见场景包括对索引列做函数或运算WHERE YEAR(create_time) 2024、WHERE age 3 24索引列被处理后无法直接定位。隐式类型转换字符串类型的列用数字查询、或数字列用字符串查询可能触发隐式CAST。尤其要注意方向字符串列与数字比较时MySQL 可能将字符串列转为数字导致该列索引失效反过来数字列与字符串比较时转换方向不同结论可能不同不能一概而论应以EXPLAIN为准。LIKE以%开头LIKE %abc无法利用 Btree 的前缀有序性LIKE abc%则可以使用索引。违反最左前缀联合索引缺少最左列或中间跳列。OR连接了未建索引的列只要OR的一侧没有可用索引优化器可能直接放弃索引走全表扫描。优化器基于代价放弃索引即使写法“正确”当预估走索引需要大量回表、成本高于全表扫描时优化器仍可能选择全表扫描。这种情况需要通过EXPLAIN结合实际数据分布来判断而不是简单归因于“索引失效”。