跳到主要内容
约 9 分钟4 张卡

索引设计

索引设计是根据真实的查询负载,决定在哪些列上建索引、列按什么顺序排、要不要把返回列也放进去。索引不是越多越好:每多一个索引,写入时就多维护一棵树,读查询的收益要和这笔持续的写入成本放在一起比较。

评估一个索引方案,可以先问:查询条件能不能用上索引的有序性,索引过滤掉的数据够不够多?改完之后,还要用执行计划和实测证明它确实更好。下文以 MySQL 8.0 InnoDB 为准。

1. 面试核心​

先记住这几条,再读正文

2. 索引的作用​

索引用额外的空间和写入维护,换来读取时更小的扫描范围。下文都用这张用户表演示查询条件和候选索引:

CREATE TABLE users (
id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
status TINYINT NOT NULL,
created_at DATETIME NOT NULL
) ENGINE = InnoDB;
CREATE INDEX idx_users_email ON users(email);

有了 idx_users_email,按邮箱查用户时就不用扫全表。索引的基本成本见数据库索引。

3. 聚簇键与行定位​

InnoDB 的完整行存在聚簇索引里,二级索引只保存索引列和聚簇键,查到聚簇键后再回聚簇索引取整行。存储方式见聚簇索引。

4. B+ 树索引​

常规索引用 B+ 树组织:上层节点多路分叉,几次查找就能到达叶子;叶子按键值有序并相互链接,适合范围访问。结构与页面调整见 B+ 树。

5. 联合索引与最左前缀​

联合索引按定义的列顺序逐级排列:先按第一列,第一列相同再按第二列。所以只有从第一列开始连续给出的条件,才能圈出连续的扫描范围。范围、覆盖和 ICP 的推演见联合索引与覆盖索引。

6. 索引选择与查询条件​

建了索引不等于一定会用。优化器会估算每条候选访问路径的总成本,选最便宜的那条,而这些估算依赖统计信息和直方图。索引没被选中,通常是下面三种情况之一:查询条件让索引的有序性用不上,查询没有确定的起点,或者索引过滤不掉多少数据、回表成本反而更高。

6.1 函数条件与范围改写​

普通索引里存的是列的原始值,不是函数计算后的结果。假设 created_at 上有普通索引:

SELECT * FROM users WHERE YEAR(created_at) = 2026;

索引按 created_at 原始值排序,YEAR(created_at) 的结果并不在索引里,优化器没法用它定位。把条件改写成原始列上的范围,查找边界就直接对应到索引上了:

SELECT * FROM users
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';

如果工作负载确实需要按表达式查找,MySQL 8.0.13 起支持函数索引,可以显式建立:

CREATE INDEX idx_users_created_year ON users ((YEAR(created_at)));

这个索引保存的是表达式的计算结果,每次写入都要多算一次、多维护一棵树,这部分写入成本也要算进去。

6.2 前导通配符​

字符串索引按字符从左到右比较排序,就像字典按首字母排列。

SELECT * FROM users WHERE email LIKE '%@example.com';

前导 % 意味着开头可以是任何字符,以 @example.com 结尾的邮箱分散在索引各处,找不到扫描的起点。LIKE 'hello%' 则有固定的前缀,可以定位到以 hello 开头的那一段。如果按后缀检索很频繁,可以把域名单独存成一列,再给这一列建索引。

6.3 数据分布与选择性​

假设表有 100 万行,其中 90 万行满足 status=1。

SELECT * FROM users WHERE status = 1;

走 status 上的二级索引,要先找到 90 万个主键,再逐个回聚簇索引取完整行,这些访问分散在各个页上。直接扫描聚簇索引则是顺着叶子一页页读下去。优化器比较两条路径的总成本,很可能选择全表扫描。

不过「很可能」不等于一定,因为成本是按估算的行数算的。在 MySQL 8.4 上用 20 万行、status=1 占 90% 的表实测:优化器估计 status=1 只有约 10 万行,选了 status 索引,实际读了 18 万行,耗时 87ms,强制全表扫描只要 29ms。给 status 建直方图也没有改变估算,因为列上有索引时,优化器优先用索引下探(index dive)得到的行数。所以低选择性条件要看 EXPLAIN ANALYZE 的实际行数,必要时用 IGNORE INDEX 对比两条路径。

同一个索引对 status=0 却可能很有效,因为它只对应少量记录。所以验证索引时,测试参数既要有热点值,也要有低频值,只测一种会得出片面的结论。

7. 字符串前缀索引​

前缀索引只对字符串的前若干个字符建索引,用来缩小长字符串列的索引体积:

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

它节省了键空间,代价是前 20 个字符相同的记录在索引里区分不开,还要回表比较完整值。前缀越短,需要继续检查的候选记录越多。选长度时,可以比较前缀和完整值各自的去重比例,前缀的去重比例接近完整值时,再用实际扫描量验证。

因为索引里没有完整值,前缀索引还有三个限制:查询要返回完整 email 时它不能覆盖,按完整字符串排序时它不能单独提供顺序,建成唯一索引时约束的是前缀本身唯一。最后一点最容易踩坑:两个不同的邮箱只要前 20 个字符相同,第二个就插不进去。

8. 索引设计步骤​

设计索引要从具体查询出发。假设现在要查最近创建的 20 个正常用户:

CREATE INDEX idx_users_status_created ON users(status, created_at);
SELECT id, email FROM users
WHERE status = 1 AND created_at >= '2026-01-01'
ORDER BY created_at DESC LIMIT 20;

status = 1 固定了索引第一列,这一段记录按 created_at 排列,于是 created_at >= '2026-01-01' 对应一段连续区间,ORDER BY created_at DESC 也能直接从区间末尾倒序扫描,拿到 20 条就停。区间内不足 20 行时,扫到区间开头为止。

要不要把 email 也加进去做成覆盖索引?加了能省掉回表,但这条查询最多只回表 20 次,收益有限,而索引变宽的写入成本每次插入和更新都要付。这种情况下通常不值得。

这个索引只服务于「先按状态、再按时间」的查询。如果另有查询只按日期过滤,用不上 status 前缀,需要单独评估日期索引。几个等值列谁放前面,要看哪些查询需要复用这段前缀;列的基数高低只是参考因素之一。

9. 执行计划验证​

推断完要用执行计划验证,而且要用相同的 SQL 和实际的参数值:

EXPLAIN SELECT id, email FROM users
WHERE status = 1 AND created_at >= '2026-01-01'
ORDER BY created_at DESC LIMIT 20;

重点比较扫描行数、回表行数、有没有额外排序,以及响应时间。字段含义和实际计时的读法见执行计划。

9.1 慢查询验证步骤​

  1. 记录实际 SQL、参数、数据规模和调用频率。
  2. 用 EXPLAIN 确认索引、扫描范围和排序。
  3. 在受控环境用 EXPLAIN ANALYZE 记录实际统计。
  4. 调整 SQL 或索引后,按相同条件对照测试。
  5. 同时比较写入延迟、索引空间和其他高频查询。

计划变了不代表变好了,最后要用实际扫描量和延迟说话,并且热点参数和普通参数都要测到。

10. 工作负载与索引维护​

一个索引通常要服务一组查询,而不是一条。设计时把相关查询的过滤列、排序列、返回列和调用频率列出来,看哪些查询能复用同一段前缀,再比较读收益和写成本。

查询参数的类型也要和列一致。字符串列用数字去比较时,MySQL 会把列值转换成数字再比,而 '1'、'01'、'1abc' 这些不同的字符串都能转换成 1,索引里的顺序就对不上了,没法按等值定位。

索引的删除同样需要验证。不可见索引(invisible index)是 MySQL 8.0 提供的一种状态:优化器默认忽略它,但写入时照常维护,唯一约束也照常生效。想删一个索引时,可以先把它设为不可见,观察有没有查询变慢;有问题随时改回可见,不用重建。真正删除前,还要确认它没有承担唯一约束,并观察一个完整的业务周期,比如月底才跑的报表。

11. 常见问题​

11.1 冗余索引的判断​

普通索引 (a) 通常是冗余的,因为 (a,b) 的前缀已经能支持按 a 查找;但 (a) 更窄,删除前仍要比较两者的实际查询成本。唯一索引不能这样推:唯一索引 (a) 要求 a 本身不重复,唯一索引 (a,b) 只要求 a、b 组合不重复,允许相同的 a 搭配不同的 b,所以前者不能被后者替换。

11.2 低基数列的索引位置​

12. 面试题卡片​

13. 参考资料​

来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明