MySQL（二）：索引原理与优化

星辰逐梦2026/6/5大约 16 分钟

MySQL（二）：索引原理与优化

导语：索引是 MySQL 面试的第一重点。本篇覆盖 B+Tree 的选型理由、聚簇索引与回表、联合索引与最左前缀、覆盖索引与索引下推、索引失效的真实边界，以及唯一索引 / 普通索引的性能差异与索引选择性评估。共 14 题。

一、索引基础与结构

1. 什么是索引？为什么要建索引？有什么代价？

答：索引是一种用于快速查询和检索数据的数据结构，本质是一张「排好序的查找表」，相当于书的目录。

作用：

把「全表扫描 O(n)」变为「索引查找 O(log n)」，极大提升查询速度；
索引本身有序，还能加速 ORDER BY、GROUP BY 和连接查询（避免额外的排序步骤）。

代价：

占用磁盘空间：每个索引对应一棵 B+Tree，二级索引还要存主键；
降低写性能：INSERT/UPDATE/DELETE 需要同步维护所有相关索引（写放大）；
可能误导优化器：索引过多会让优化器估算成本时选错索引，且维护统计信息的开销上升。

经验：只为"高频被查询、过滤性强、不常改"的字段建索引；写多读少的字段、区分度极低的字段不宜建索引。

2. MySQL 索引为什么用 B+Tree，而不是 B-Tree、Hash 或红黑树？

答：这是索引部分最经典的"为什么"题，四个对手逐一对比：

vs B-Tree（B 树）

B 树的非叶节点也存数据，导致单页能放的键更少 → 树更高 → 磁盘 IO 更多；
B+Tree 非叶节点只存键，一页能放几百个键，树高仅 3~4 层就能支撑千万级数据；
B+Tree 的所有数据都在叶子节点，且叶子节点用双向链表串联 → 范围查询、排序、全表顺序扫描天然高效；B 树做范围查询要中序遍历、反复上下回溯。

vs 红黑树 / AVL

它们是二叉结构，每个节点只有 2 个分支。假设树高 h，则 2^h 个节点；千万级数据需要 h ≈ 24 层，每次查询要 24 次磁盘 IO；
B+Tree 是多路平衡树（一个节点几百个分支），树高只有 3~4 层，IO 次数极少。磁盘 IO 次数才是数据库性能的决定因素，这是核心论据。

vs Hash 索引

等值查询 O(1)，极快；
但不支持范围查询（>、BETWEEN）、不支持排序（无序）、不支持最左前缀模糊匹配（LIKE 'abc%'）、且存在哈希冲突（冲突多时退化）。

结论：B+Tree 在等值 + 范围 + 排序三类场景下都是优秀的，是通用场景的最优解。

补充：MEMORY 引擎默认用 Hash 索引，InnoDB 也提供自适应哈希索引（AHI） 作为 B+Tree 之上的自动加速层——但它是引擎内部自动维护的，用户无法手动指定某列用 Hash。

3. 聚簇索引（聚集索引）和二级索引有什么区别？

答：

维度	聚簇索引	二级索引（辅助索引 / 非聚簇）
叶子节点存什么	完整的行数据	索引列值 + 主键值
数量	每张表只能有一个	可以有多个
谁来做	默认是主键	手动创建的普通索引、唯一索引
查非索引列	一次查找即可拿到全部	需要回表（多一次查找）

InnoDB 选择聚簇索引的规则（优先级从高到低）：

若显式定义了主键 → 用主键做聚簇索引；
若无主键 → 选第一个非空的唯一索引；
若都没有 → InnoDB 隐式生成一个 6 字节的 row_id 作为聚簇索引（用户不可见）。

为什么叫"聚簇"：因为数据行本身就和索引组织在同一棵 B+Tree 里（数据即索引），所以 InnoDB 常被称作"索引组织表（Index Organized Table）"。

MyISAM 的区别：MyISAM 的索引文件（.MYI）与数据文件（.MYD）完全分离，无论主键索引还是普通索引，叶子节点存的都是"数据行的物理地址"，因此 MyISAM 没有聚簇索引的概念——这也是它查询要多一次"按地址取行"的原因。

重要推论：所有二级索引的叶子都存主键值，所以主键越长，所有二级索引就越大。这就是"主键要尽量短"的根本原因（见第 9 题）。

4. 什么是「回表」？如何避免回表？

答：通过二级索引查到主键值后，还需要拿主键去聚簇索引再查一次才能取到完整行数据，这次"再查一次聚簇索引"的过程就叫回表（额外一次 B+Tree 查找 + 可能的磁盘 IO）。

回表为何昂贵：两步查找本身还好，真正昂贵的是第二步的磁盘随机 IO——因为主键值通常是离散的，回表可能在不同的数据页之间来回跳。

避免方式：

使用覆盖索引（首选）：让查询所需的所有列都包含在索引中，直接在索引上返回，无需回表（见第 6 题）；
把高频查询条件与返回字段一起建成联合索引，让它"顺便"覆盖查询；
减少 SELECT *——它几乎必定导致回表。

量化示例：SELECT name FROM user WHERE age = 20，若只有 age 单列索引，需要"查 age 索引拿主键 → 回表取 name"；若建 (age, name) 联合索引，则 Using index 直接返回，零回表。

5. 什么是联合索引？最左前缀原则是什么？

答： 联合索引（复合索引）是对多个字段建立的一个索引，如 INDEX idx(a, b, c)，底层仍然是一棵 B+Tree，但排序规则是"先按 a 排，a 相同的按 b 排，b 相同的按 c 排"。

最左前缀原则：查询条件必须从索引最左列开始、且连续使用，才能命中索引。

查询条件	能否用到索引	说明
`WHERE a=1`	✅	用到 `a`
`WHERE a=1 AND b=2`	✅	用到 `a, b`
`WHERE a=1 AND b=2 AND c=3`	✅	全命中
`WHERE b=2`	❌	缺 `a`，整体无序，索引失效
`WHERE b=2 AND c=3`	❌	同上
`WHERE a=1 AND c=3`	⚠️ 部分	只用到 `a`（`b` 断了，`c` 无法定位）
`WHERE a=1 AND b>2 AND c=3`	⚠️ 部分	`a`、`b` 生效；范围查询后的列无法再用索引定位

两个高频坑：

范围查询会"截断"后续列：一旦 b 用了 >、<、BETWEEN，c 就无法再用于索引定位（但仍可能用于 ICP 过滤）；
MySQL 8.0 的 Index Skip Scan：在特定条件下可以让"缺少最左列"的查询也用上索引（优化器判断跳过 a 的代价更低时），但它依赖数据分布（a 的区分度要很低），不能当作通用手段，面试中可作为加分点提及。

为什么建联合索引而不是多个单列索引：多个字段共用一棵树省空间；且索引合并（index merge）的效果通常不如一个设计良好的联合索引稳定。

6. 什么是覆盖索引？有什么好处？

答：当要查询的所有列都包含在某个索引中时，存储引擎只需遍历该索引即可返回结果，不必回表，这个索引就叫覆盖索引（注意：覆盖索引是"查询与索引的关系"，不是一种索引类型）。

判断方法：EXPLAIN 的 Extra 列出现 Using index 即表示命中覆盖索引。

好处：

减少一次回表（省掉一次 B+Tree 查找与随机 IO）；
二级索引本身比聚簇索引小得多（只存索引列 + 主键），更可能常驻 Buffer Pool；
对于范围查询，还能避免大量随机 IO。

-- 索引：INDEX idx_name_age (name, age)
SELECT name, age FROM user WHERE name = '张三';       -- Using index（覆盖）
SELECT name, age, email FROM user WHERE name = '张三'; -- 需回表取 email

实践技巧：为高频查询"定制"联合索引时，把 SELECT 需要的列（而不是 SELECT *）追加到索引末尾，就能把它变成覆盖索引。这是最常用的索引优化手段之一。

7. 什么是索引下推（Index Condition Pushdown, ICP）？

答： ICP 是 MySQL 5.6 引入的优化：把 WHERE 中能用索引字段判断的部分，下推到存储引擎层，在遍历索引的过程中就完成过滤，而不是把每一行都取到 Server 层再过滤。

没有 ICP 时：引擎按索引找到记录 → 全部回表取完整行 → 交给 Server 层用 WHERE 过滤 → 大量无用的回表。

有 ICP 时：引擎在遍历索引时，先用索引中已有的列做过滤，不符合的直接跳过，根本不去回表。

-- 索引：INDEX idx_name_age (name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 20;

由于 LIKE '张%' 是范围查询，age 无法用于索引定位（违背"范围后截断"规则）；
但 age 在索引里，ICP 可以在引擎层直接用 age = 20 过滤，只对真正命中的行回表。

判断方法：EXPLAIN 的 Extra 出现 Using index condition。

区分三个 Extra 标记（高频易混）：
Using index → 覆盖索引（不用回表）；
Using index condition → 索引下推（在引擎层用索引列过滤，仍可能回表）；
Using where → Server 层过滤（可能没用到索引过滤）。

二、索引设计与失效

8. 哪些字段适合建索引？如何选择？

答： 优先建索引的字段：

频繁出现在 WHERE、JOIN ON、ORDER BY、GROUP BY 中的字段；
区分度高（选择性高）的字段——如用户 ID、订单号；SELECT COUNT(DISTINCT col) / COUNT(*) 越接近 1 越好；
外键列（连接查询频繁）；
需要唯一性约束的字段（用唯一索引顺便加速查询）。

不宜建索引：

区分度极低：如性别、状态标志（只有几个值），优化器往往直接放弃；
频繁更新的字段：每次更新都要维护所有相关索引；
NULL 值很多的字段（统计信息失真，且 IS NULL 的选择性判断可能不利）；
过长的字段：可用前缀索引（见第 11 题）；
很少出现在查询条件里的字段。

组合优于拆分：多个字段经常一起查询时，建一个联合索引通常比建多个单列索引效果更好（省空间、避免索引合并的不确定性）。

9. 为什么建议主键尽量短且自增（有序）？

答：两个要求各有原因：

1）为什么"短"——因为所有二级索引都存主键

二级索引的叶子存的是"索引列值 + 主键值"；
主键越长 → 每个二级索引的每条记录都更长 → 同一页能放的索引项更少 → 树更高、IO 更多；同时 Buffer Pool 能缓存的总索引项变少；
对比：INT（4 字节）vs UUID（36 字节，或 BINARY(16) 16 字节），差距会放大到所有二级索引上。

2）为什么"自增有序"——因为聚簇索引按主键组织

自增主键让新记录永远追加到 B+Tree 最右侧的页，只在写满时顺序新开页，几乎不触发页分裂；
若用 UUID 等无序主键，插入点随机落在各页中间，会频繁触发页分裂，产生大量随机 IO、页内碎片、写放大（见《MySQL（一）》第 8 题）；
顺带的好处：范围查询（按时间、按 ID 分页）也更连续。

结论句式："短"是为了让所有二级索引变小，"有序"是为了让写入不触发页分裂——这两点合起来，就是"自增 INT/BIGINT 做主键"的最佳实践依据。
例外：分库分表或需要对外隐藏 ID 时，通常换成趋势递增的雪花 ID（仍近似有序，兼顾了写入性能），而不是纯随机 UUID。

10. 索引失效的常见场景有哪些？

答：先明确一个前提："索引失效"本质是优化器基于成本估算，认为"用索引反而更慢"，因此选择全表扫描。真正"用不了索引"的情况其实只有少数几种。

场景	示例	原因
对索引列用函数/表达式	`WHERE YEAR(create_time) = 2024`	B+Tree 按列原值有序，函数后无序，无法定位 → 改成 `WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'`
隐式类型转换	`phone` 是 `varchar`，却写 `WHERE phone = 13800000000`	MySQL 会把列转成数字（而非把参数转成字符串），列被"函数化" → 写 `WHERE phone = '13800000000'`
前导模糊查询	`WHERE name LIKE '%明'`	前导 `%` 无法定位有序区间；`LIKE '张%'` 可以用索引
`OR` 连接了非索引列	`WHERE idx_col = 1 OR no_idx_col = 2`	OR 两侧都要能走索引才行，否则整体退化为全表扫描
违背最左前缀	联合索引 `(a,b)` 查 `WHERE b = 2`	见第 5 题
优化器判断全表更快	表数据量很小，或需要返回的行占比很高（如超过 20%~30%）	回表代价高于全表扫描，这是"统计信息"与"成本模型"决定的，不是语法问题

关于 !=、<>、NOT IN、IS NULL、IS NOT NULL 的正确说法（原文此处有误，需要纠正）：

这些条件并不会"天然导致索引失效"。MySQL 官方文档明确说明 IS NULL 可以使用索引。
它们看起来"失效"的真实原因是：这类条件的过滤性太差（例如 != 通常在大部分行都成立、IS NULL 在 NULL 很少时命中行数很多），优化器算下来"走索引 + 大量回表"比"直接全表扫描"更贵，于是主动放弃索引——这是成本决策，不是"语法限制"。
所以更准确的表述是："这些条件容易让优化器放弃索引，而非一定失效"。验证方式仍然是用 EXPLAIN 看 key 是否为 NULL、type 是否为 ALL。

11. 什么是前缀索引？

答： 前缀索引只对字符串的前 N 个字符建索引：ALTER TABLE t ADD INDEX idx_email(email(10))。

作用：大幅节省索引空间，从而让同样的内存能缓存更多索引项、减少 IO。适用于长字符串（邮箱、URL、地址、UUID）。

关键：如何选择 N？ 原则是"取能维持足够区分度的最短前缀"：

-- 逐步增大前缀长度，观察区分度（越接近全列的 COUNT(DISTINCT) 越好）
SELECT COUNT(DISTINCT email) / COUNT(*) AS full_selectivity FROM t;
SELECT COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) FROM t;   -- 试 N=6
SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) FROM t;   -- 试 N=8

三个局限（务必记住）：

无法用于 ORDER BY / GROUP BY——前缀索引不保证前缀相同的前缀外字符的顺序；
无法构成覆盖索引——索引里没有完整列值，SELECT 该列仍需回表；
无法精确定位——索引只缩小范围，仍需回表后再比较完整值（Using where）。

变通方案：若既要省空间又要覆盖，可考虑把长字段的哈希值（如 CRC32/MD5 前 8 位）单独存一列并建索引，用等值查询走哈希列。

12. 唯一索引和普通索引有什么区别？性能上有差异吗？

答：

功能差异：唯一索引保证列值（或列组合值）唯一，插入/更新时会校验约束；普通索引不校验。

性能差异要分"读"和"写"两面看：

操作	唯一索引	普通索引
查询（等值）	找到第一条匹配即可返回（确定唯一）	找到第一条后还要继续向后找，直到不满足条件（InnoDB 里因为页内有序，这个代价极小）
插入/更新	必须读索引页校验唯一性，这次读盘无法避免	可以使用 Change Buffer：索引页不在内存时先记账、延迟合并，把随机 IO 合并成顺序 IO

结论：

查询性能差异极小（几乎可忽略）；
写性能上普通索引更优——因为普通索引能利用 Change Buffer（见《MySQL（一）》第 6 题），而唯一索引每次写入都要真实读盘校验；
所以如果业务上能保证唯一性，用普通索引写入会更快；但如果需要数据库层面强约束（防重复），必须用唯一索引——这是一个"正确性 vs 性能"的取舍。

经典面试题延伸："业务代码已经做了防重，还需要唯一索引吗？"——需要。唯一索引是最后一道防线，能防住并发场景下的重复写入（"先查后插"本身不是原子的），也防住人为的脏数据。

13. 什么是索引选择性（区分度）？如何评估索引的好坏？

答： 索引选择性（Selectivity）衡量"索引能过滤掉多少数据"，公式：

选择性 = COUNT(DISTINCT col) / COUNT(*)      -- 越接近 1 越好

接近 1：值几乎不重复，等值查询能迅速缩小到极少行 → 好索引（如主键、订单号、用户 ID）；
接近 0：值高度重复 → 坏索引（如性别只有 0/1，选择性 0.5；状态字段若 99% 是同一个值，选择性仅 0.01）。

更贴近实战的指标：基数（Cardinality）——优化器估算的"索引中不同值的个数"。

SHOW INDEX FROM t;                    -- 查看 Cardinality
ANALYZE TABLE t;                      -- 手动重新收集统计信息

为什么基数会失真：

基数是采样估算的（innodb_stats_persistent_sample_pages 默认 20 页）；
数据大量增删后统计信息会偏离实际，导致优化器选错索引；
解决手段：ANALYZE TABLE 重新采样、调整采样页数、或在极端情况下用 FORCE INDEX 强制指定。

联合索引的选择性怎么算：看组合的选择性 COUNT(DISTINCT a, b) / COUNT(*)。一个实用经验是把选择性高的列放在联合索引的左边（更利于快速缩小范围）；但如果某列有等值查询需求，则优先保证它能被"最左"命中。

14. 索引是不是越多越好？如何做索引优化？

答： 不是。 索引的收益在"读"，代价在"写"和"空间"，数量过多会整体拖慢系统。

索引过多的三个危害：

写放大：每次 DML 都要维护所有相关索引（一次插入可能改 5~6 棵树）；
空间与内存浪费：索引占磁盘，也挤占 Buffer Pool 的宝贵内存；
误导优化器：候选索引越多，成本估算越容易出错（"选错索引"），且解析优化的时间变长。

索引优化清单：

手段	说明
用联合索引替代多个单列索引	省空间、命中更稳定；注意列顺序（等值列在前、范围列在后、排序列次之）
删掉无用/冗余索引	如已有 `(a, b)`，则单列 `(a)` 是冗余的
定制覆盖索引	把高频查询的返回列追加到索引末尾，消除回表
控制索引数量	单表索引一般不超过 5~6 个
用 `pt-index-usage` 等工具	基于慢日志统计"哪些索引从未被使用"，据此清理
定期 `ANALYZE TABLE`	保持统计信息新鲜，避免优化器选错索引
避免函数/隐式转换破坏索引	见第 10 题

一个判断索引是否冗余的规则：若索引 A(a, b) 已存在，那么 B(a) 就是冗余的（最左前缀完全覆盖）；但 C(b) 不是冗余的（缺最左列）。清理索引时优先删"最左前缀被其他索引覆盖"的那个。