MySQL 索引原理与优化

什么是索引？它有哪些优缺点？

回答重点

索引是帮助存储引擎快速定位记录的数据结构。它用额外的空间和写入维护成本，换取更少的数据扫描、排序或回表开销。

主要收益包括：

• 加速等值、范围和前缀匹配查询。
• 在索引顺序与查询要求一致时，减少额外排序。
• 通过唯一索引保证数据唯一性。
• 通过覆盖索引直接返回所需列。

主要成本包括：

• 占用磁盘和缓冲池空间。
• 插入、删除、更新时需要同步维护相关索引。
• 索引过多会扩大写放大、统计信息和优化器选择成本。
• 不符合查询模式的索引即使存在，也可能无法使用或收益很低。

索引不是把查询复杂度机械地从 O(n) 降为某个固定值。实际代价还取决于树高、缓存命中率、返回行数、回表次数和数据分布。

MySQL 索引可以如何分类？

回答重点

可以从不同维度理解：

• 按数据结构：B+ 树索引、哈希索引、全文索引和空间索引。
• 按存储组织：InnoDB 聚簇索引和二级索引。
• 按约束语义：主键索引、唯一索引和普通索引。
• 按列数：单列索引和联合索引。
• 按索引内容：完整列索引、前缀索引和函数索引。

不同存储引擎支持的类型不同。InnoDB 普通索引默认使用 B+ 树，并在内部提供自适应哈希索引；MEMORY 引擎支持显式选择 HASH 或 BTREE。

CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  nickname VARCHAR(100) NOT NULL,
  UNIQUE KEY uk_users_email (email),
  KEY idx_users_nickname_prefix (nickname(20))
);

InnoDB 的 B+ 树索引是如何组织的？

回答重点

B+ 树是一棵多路、平衡、有序的搜索树：

• 非叶子节点主要保存索引键和子页指针，用于导航。
• 所有记录最终都在同一层叶子节点中找到，因此访问路径较稳定。
• 叶子页按键值顺序组织，并通过双向链表相连，便于范围扫描和正反向遍历。
• 节点对应 InnoDB 数据页，默认页大小通常为 16 KiB。
• 插入和删除后，存储引擎通过页分裂、页合并或重组维持树结构。

聚簇索引叶子节点保存完整行记录；二级索引叶子节点保存二级索引列和对应的聚簇键。

不能把“访问一层节点”直接等同于“一次物理磁盘 I/O”。常用的索引页可能已经位于 Buffer Pool 中，实际 I/O 次数取决于缓存命中情况。

为什么 InnoDB 选择 B+ 树，而不是 B 树、二叉树、哈希表或跳表？

回答重点

B+ 树适合数据库页式存储和通用查询：

• 与 B 树相比，非叶子节点不保存完整行，同样大小的数据页能容纳更多导航键，树通常更矮；叶子页相连也更适合范围扫描。
• 与二叉搜索树相比，一个节点能容纳大量分支，可显著减少访问层数。
• 与哈希表相比，B+ 树不仅支持等值查询，还支持范围、排序和最左前缀匹配。
• 跳表也支持范围查询，但多层指针结构的空间局部性和页利用方式通常不如 B+ 树契合磁盘页模型。

这不代表 B+ 树在所有场景都最快。纯内存、纯等值查询可能更适合哈希结构；数据库选择 B+ 树，是因为它在多种访问模式下表现稳定。

定位到 B+ 树叶子页后，InnoDB 如何查找具体记录？

回答重点

一个数据页内包含多条按索引键组织的记录。记录之间通过链表维护逻辑顺序，同时页目录把记录分成若干组，每个槽指向一组中的边界记录。

页内查找大致分两步：

1. 对页目录中的槽做二分查找，定位目标可能所在的记录组。
2. 在组内沿记录链表做少量比较，找到目标记录或确认不存在。

因此，B+ 树负责定位数据页，页目录负责缩小页内范围，并不是到达叶子页后再从头线性扫描整个页面。

哈希索引适合什么场景？

回答重点

哈希索引适合以完整键等值查询为主、基本不需要排序和范围扫描的场景。它根据哈希值定位桶，平均查找成本低，但存在以下限制：

• 不保留键的有序性，不能直接支持范围查询和 ORDER BY。
• 通常不能利用联合键的最左前缀。
• 哈希冲突后仍需比较和查找，不能把性能绝对描述为 O(1)。

MEMORY 引擎默认使用哈希索引，也可显式创建 B 树索引。InnoDB 不支持用户显式创建普通哈希索引，但可能根据热点访问在内存中自动维护自适应哈希索引，它不是持久化索引，也不应作为表结构设计的一部分。

聚簇索引和二级索引有什么区别？

回答重点

在 InnoDB 中：

• 聚簇索引的叶子节点保存完整行，一张表只能有一份这样的数据组织，因此只能有一个聚簇索引。
• 二级索引的叶子节点保存二级索引键和对应的主键值，一张表可以有多个二级索引。
• 通过二级索引查询完整行时，通常先取得主键，再访问聚簇索引，这就是回表。
• 二级索引已经包含查询所需列时，可以直接返回结果，形成覆盖索引。

InnoDB 选择聚簇键的顺序是：

1. 显式主键。
2. 第一个所有列都为 NOT NULL 的唯一索引。
3. 自动生成的隐藏聚簇键。

因此，“InnoDB 表没有主键”只表示用户没有显式声明主键，不代表底层没有聚簇索引。

更新或插入数据时，索引会发生什么变化？

回答重点

插入一行时，InnoDB 需要向聚簇索引和所有相关二级索引写入索引项。目标页有足够空间时可直接插入；空间不足时可能发生页分裂，并更新父节点。不是每次插入都会分裂。

更新索引列会改变键值顺序，通常需要删除旧索引项并插入新索引项。只更新非索引列时，不需要重新排列该二级索引，但聚簇记录的长度变化仍可能引起页内重组或溢出页调整。

删除记录通常先做删除标记，再由后台清理。长期随机写入可能造成页利用率下降和碎片，但不能仅凭“有碎片”就定期重建所有索引，应结合实际空间、扫描效率和维护成本判断。

扩展知识

对非唯一二级索引的部分变更，InnoDB 可以使用 change buffer 延迟合并，减少随机 I/O。唯一索引需要立即检查冲突，适用条件不同。

主键应该使用自增 ID 还是 UUID？

回答重点

InnoDB 主键会成为聚簇键，并被每个二级索引引用，因此通常希望它短、稳定、唯一，并尽量保持递增。

自增整数的优点是占用小、比较快、插入位置通常靠近索引尾部，可减少随机页访问和页分裂。缺点是分库分表时需要额外的全局 ID 方案，连续值也可能暴露业务规模。

随机 UUID，尤其是以 36 字符字符串保存的 UUID，键更长且插入位置离散，会放大聚簇索引和全部二级索引，并增加随机写入与页分裂概率。

这不是“任何场景都必须自增”的结论。分布式系统可以选择雪花类 ID、号段服务、UUIDv7 等近似有序标识，并用 BINARY(16) 等紧凑格式存储 UUID。还要评估高并发顺序写入造成的尾页热点。

联合索引的底层排序和最左匹配原则是什么？

回答重点

联合索引 (a, b, c) 按字典序排列：先比较 a，a 相同时比较 b，再相同时比较 c。因此只有从最左列开始形成连续前缀，才能直接缩小 B+ 树的搜索范围。

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);

以下条件可以使用连续前缀：

WHERE user_id = 10;
WHERE user_id = 10 AND status = 'paid';
WHERE user_id = 10 AND status = 'paid' AND created_at >= '2026-01-01';

仅按 status 或 created_at 查询，通常无法用该索引直接定位连续范围。WHERE 中条件的书写顺序不影响最左匹配，优化器会按语义分析条件。

扩展知识

MySQL 8.0 在特定条件下可能使用 skip scan 跳过缺失的首列，但它受基数和成本限制，不能替代按核心查询模式设计索引。

联合索引遇到范围条件后，后续列还能使用吗？

回答重点

以索引 (a, b, c) 为例：

WHERE a = 1 AND b > 10 AND c = 3;

a 和 b 可以共同确定索引扫描范围；b 是第一个非等值范围条件，c 通常不能继续缩小这段 B+ 树扫描区间。

但“不能继续构造索引范围”不等于 c 完全无用。若满足索引条件下推（Index Condition Pushdown，ICP）的条件，存储引擎可在读取二级索引项时先判断 c = 3，过滤后再回表，从而减少回表次数。EXPLAIN 的 Extra 可能显示 Using index condition。

如果查询是：

WHERE a = 1 AND b = 10 AND c > 3;

则 a、b 的等值条件和 c 的范围条件都能参与确定扫描范围。

哪些写法容易导致索引无法有效使用？

回答重点

常见情况包括：

• 缺少联合索引的最左前缀。
• 对普通 B+ 树索引使用左模糊匹配，如 LIKE '%keyword'。
• 在索引列上做函数或表达式计算，且没有匹配的函数索引。
• 隐式类型转换作用在索引列上，例如字符列与数字参数比较。
• OR 的某个分支没有可用索引，导致整体扫描成本过高。
• 条件命中大量数据，回表代价高，优化器主动选择全表扫描。

-- The indexed column is wrapped in a function.
WHERE DATE(created_at) = '2026-09-30';

-- A regular index on created_at can provide a range scan.
WHERE created_at >= '2026-09-30 00:00:00'
  AND created_at < '2026-10-01 00:00:00';

更准确的说法是“无法把条件转换成有效索引范围”或“优化器认为走索引成本更高”，而不是把所有情况统称为索引失效。IS NULL、!=、NOT IN 等条件并非绝对不能用索引，最终要看数据分布和执行计划。

什么是回表和覆盖索引？

回答重点

使用 InnoDB 二级索引时，叶子节点只包含二级索引列和主键。如果查询还需要其他列，就要拿主键再次查聚簇索引，这一步叫回表。

如果一个索引已经包含过滤、连接和返回所需的全部列，存储引擎可以直接从索引得到结果，不再访问聚簇记录，这叫覆盖索引。

CREATE INDEX idx_employees_name_age_department
ON employees(name, age, department);

SELECT name, age, department
FROM employees
WHERE name = 'Alice';

上面的查询可能使用覆盖索引。覆盖与否由具体查询决定，不是某个索引的永久属性；改成 SELECT * 后通常需要回表。

扩展知识

EXPLAIN 的 Extra 显示 Using index 通常表示覆盖索引。Using index condition 表示索引条件下推，两者不是同一个概念。

单列索引和联合索引都可用时，MySQL 会选择哪个？

回答重点

优化器根据成本选择访问路径，不按固定优先级选择“单列”或“联合”索引。成本估算会考虑：

• 索引选择性和预计扫描行数。
• 是否覆盖查询以及预计回表次数。
• 索引键长度、页数量和缓存情况。
• 排序、分组和连接成本。
• 统计信息与直方图。

例如同时存在 (a) 和 (a, b) 时，查询 SELECT a, b FROM t WHERE a = ? 可能偏向 (a, b)，因为它覆盖查询；只查询 a 时，较小的单列索引也可能更便宜。应查看 EXPLAIN，不要假设优化器一定选择列数更多的索引。

低区分度字段适合建索引吗？

回答重点

不能只根据不同值数量下结论。性别、状态等字段区分度低，如果某个值命中表中大部分行且查询需要很多非索引列，走二级索引会产生大量随机回表，优化器通常更倾向全表扫描。

但低区分度字段在以下场景仍可能有价值：

• 数据分布严重倾斜，查询的是占比很小的值。
• 与租户、时间等列组成符合查询模式的联合索引。
• 索引能够覆盖查询，不需要大量回表。
• 用于唯一访问路径之外的排序或分组。

可用下面的 SQL 观察整体区分度，但还要结合具体值的分布：

SELECT
  COUNT(DISTINCT status) / COUNT(*) AS selectivity
FROM orders;

不存在适用于所有表的固定“命中比例阈值”。优化器会综合页数、行宽、缓存和统计信息估算成本。

应该为哪些字段建立索引？

回答重点

优先围绕稳定、高频且代价高的查询设计索引：

• 需要唯一性约束的业务键。
• 经常出现在 WHERE、JOIN ... ON 中并能有效缩小结果集的列。
• 经常用于 ORDER BY、GROUP BY，且顺序能与过滤条件共同匹配的列。
• 可以组成覆盖索引、显著减少回表的少量返回列。

联合索引的列顺序首先服从查询模式，而不是机械地按区分度排序。通常先安排等值匹配列，再考虑范围、排序和分组需求；当多种顺序都满足访问模式时，再比较选择性与复用价值。

不应盲目为以下内容单独建索引：

• 查询从不使用的列。
• 数据量很小、全表扫描已经足够便宜的表。
• 更新频繁但查询收益很低的列。
• 已被联合索引左前缀覆盖的冗余索引。

什么是前缀索引？如何选择前缀长度？

回答重点

前缀索引只索引字符串开头的一部分，可以减小索引项，使单页容纳更多记录：

CREATE INDEX idx_users_email_prefix
ON users(email(20));

选择长度时，要在索引体积和区分能力之间权衡。可以比较不同前缀的选择性：

SELECT
  COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS p8,
  COUNT(DISTINCT LEFT(email, 16)) / COUNT(*) AS p16,
  COUNT(DISTINCT LEFT(email, 24)) / COUNT(*) AS p24
FROM users;

前缀索引只保存部分值，命中后可能还要读取原值确认，通常不能覆盖完整列，也难以完整满足该列的排序和分组需求。若字段本身不长，或完整值需要唯一约束、覆盖查询，完整索引可能更合适。

如何系统地排查和优化索引问题？

回答重点

先确认慢在哪里，再调整索引：

1. 从慢查询日志或监控中找到真实 SQL、耗时、扫描行数和调用频率。
2. 使用 EXPLAIN 查看访问类型、候选索引、实际索引、估算行数、过滤比例和额外操作。
3. MySQL 8.0 可使用 EXPLAIN ANALYZE 对比估算行数与实际行数，但它会真正执行语句。
4. 检查谓词是否可构造索引范围，联合索引顺序是否匹配过滤、排序和分组。
5. 评估返回行数和回表成本，必要时使用覆盖索引或缩小查询列。
6. 检查重复、冗余和长期未使用的索引，并评估删除后的写入收益与查询风险。
7. 数据分布变化较大时更新统计信息，再验证执行计划。

EXPLAIN ANALYZE
SELECT id, created_at
FROM orders
WHERE user_id = 1001
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

优化后要在接近生产的数据量与分布下复测读写性能。索引提示只能作为定位或短期兜底手段，优先修正索引设计、统计信息和 SQL 结构。