索引设计
索引设计根据查询负载选择列、顺序和覆盖范围。本文负责 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);
索引用额外空间和写入维护换取更小的读取范围。基本成本见数据库索引。
3. 聚簇键与行定位
InnoDB 用聚簇键定位整行。存储方式见聚簇索引。
4. B+ 树索引
B+ 树用多路导航和有序叶子支持范围访问。结构与页面调整见 B+ 树。
5. 联合索引与最左前缀
联合索引按定义的列顺序逐级排列。范围、覆盖和 ICP 的推演见联合索引与覆盖索引。
6. 索引选择与查询条件
优化器比较候选访问路径的总成本。扫描范围过大时，二级索引还需大量回表。全表扫描就有成本优势。统计信息与直方图影响这些估算。
6.1 函数条件与范围改写
普通日期索引保存原始日期值。
SELECT * FROM users WHERE YEAR(created_at) = 2026;
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 前导通配符
字符串 B+ 树按字符前缀排列。
SELECT * FROM users WHERE email LIKE '%@example.com';
前导 % 缺少固定起点，无法形成普通前缀范围。LIKE 'hello%' 则可按固定前缀定位。后缀检索频繁时，可单独存储域名并为域名建索引。
6.3 数据分布与选择性
假设表有 100 万行，其中 90 万行满足 status=1。
SELECT * FROM users WHERE status = 1;
二级索引方案需定位 90 万个主键，再取完整行。直接扫描聚簇索引则沿叶子读取。优化器会比较两条路径的总成本。
status=0 对应较少记录，同一索引有不同收益。因此，测试参数要包含热点值和低频值。
7. 字符串前缀索引
长字符串可仅对前若干字符建索引。
CREATE INDEX idx_users_email_prefix ON users(email(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 固定后，该前缀内按 created_at 排列。索引可倒序扫描，取得 20 个匹配项后停止。范围内不足 20 行时，扫描到范围末尾。
加入 email 可形成覆盖索引，减少取列回表。但当前查询只需返回至多 20 行。扩大索引换来的读收益，要与持续写入成本比较。
其他查询若只按日期过滤，需要单独评估日期索引。等值列顺序还取决于哪些查询需要复用前缀。列基数只是设计输入之一。
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 慢查询验证步骤
- 记录实际 SQL、参数、数据规模和调用频率。
- 用 EXPLAIN 确认索引、扫描范围和排序。
- 在受控环境用
EXPLAIN ANALYZE记录实际统计。 - 调整 SQL 或索引后，按相同条件对照测试。
- 同时比较写入延迟、索引空间和其他高频查询。
计划改变后，仍需确认实际扫描量与延迟改善。热点参数与普通参数都应覆盖。
10. 工作负载与索引维护
候选索引应覆盖一组真实查询。记录各查询的过滤、排序、返回列和频率。再比较前缀复用率与读写成本。
字符串索引列应使用字符串参数。用数字比较字符串列时，会发生类型转换。多个字符串能转换为同一数值，妨碍索引等值定位。
MySQL 8.0 默认忽略不可见索引。它仍承担写入维护，唯一约束也继续生效。可先将候选删除索引设为不可见，观察查询退化。删除前还需核对约束和完整业务周期。
11. 常见问题
11.1 冗余索引的判断
普通索引 (a) 的查询前缀可被 (a,b) 覆盖。删除前仍需比较索引宽度和实际查询成本。唯一索引 (a) 与唯一索引 (a,b) 约束不同。后者允许相同 a 搭配不同 b，因此不能直接替换。
11.2 低基数列的索引位置
低基数等值列也可作为联合索引前缀。例如状态固定后按时间取前 20 行。是否合适取决于过滤与排序组合，以及其他查询的复用。
12 · 面试题卡片
本页题目与参考回答
12.1 给定一条慢 SQL，怎样用 EXPLAIN 判断索引是否有效
考察重点
- 真实 SQL、参数、数据分布和访问路径。
type、key、rows、filtered与Extra。- 联合索引、范围、回表和实测验证。
参考回答
索引是否有效，要看它是否减少了实际扫描、回表和排序成本，而不能只看 key 是否非空。
先取得真实 SQL、绑定参数和数据分布，用 EXPLAIN 核对实际 key、估计 rows、覆盖和下推情况。再在受控环境用 EXPLAIN ANALYZE 检查实际统计。
调整后比较同一负载下的扫描量与延迟，并检查写入成本和其他高频查询，确认整体收益。
12.2 如何为等值、范围和排序同时存在的查询设计联合索引？
考察重点
- 键顺序与可复用前缀
- 读写成本和实际计划
参考回答
等值列固定前缀，范围列承接扫描与兼容的排序。
例如 (status,created_at) 适合按状态取最近记录。覆盖列是否加入，取决于回表收益和索引宽度。其他查询的前缀复用需求也会影响列顺序。
12.3 把索引设为不可见，是否会停止维护它并取消唯一约束？
考察重点
- 优化器可用性
- 写入维护与唯一性
相关内容：工作负载与索引维护。
参考回答
不可见索引仍被维护，唯一约束也仍然生效。
它只退出优化器的默认候选范围。测试查询退化后，还需核对约束用途再决定删除。
12.4 邮箱前缀索引能覆盖完整邮箱查询并保证完整值唯一吗？
考察重点：前缀选择性、覆盖能力和唯一性。
相关内容：字符串前缀索引。
参考回答
前缀索引只保存部分邮箱，返回完整值仍需取行。
普通前缀索引没有唯一性约束。唯一前缀索引要求前缀唯一，比完整邮箱唯一更严格。不同邮箱若共享相同前缀，也会被拒绝。