跳至主要內容

索引设计原则

硅碳合伙人大约 14 分钟MySQL索引设计覆盖索引前缀索引

开篇：好的索引设计能让查询快 100 倍

同样一张千万级的订单表，一条查询语句加上合适的索引后从 3 秒降到 30 毫秒——这种 100 倍的提升在实际项目中很常见。

但反过来也成立：索引加错了，不仅不快，反而更慢——多了索引维护开销，优化器还可能被"带偏"，选了一个看起来有索引但实际效果更差的执行计划。

索引设计的核心原则只有一句话：让最常用的查询，用最少的 I/O 拿到结果。 下面我们围绕这句话展开。

一、索引创建的基本原则

1.1 什么列适合建索引？

不是所有列都值得建索引。以下场景优先考虑：

频繁出现在 WHERE 条件中的列。 这是最基本的理由。如果某列几乎不会被用于查询过滤，给它建索引纯粹浪费空间。

有唯一性约束的列。 唯一索引在保证数据完整性的同时，还能加速查询（直接定位到唯一的一行），一举两得。

经常用于 ORDER BY 和 GROUP BY 的列。 如果排序/分组字段有索引，MySQL 可以直接利用索引的有序性，避免额外的 filesort 操作（filesort 要在内存或磁盘上建临时排序区，开销不小）。

多表 JOIN 时的连接字段。 比如 ON a.user_id = b.user_id，给 user_id 建索引可以把 Nested Loop Join 的复杂度从 O(MN) 降到 O(MlogN)。另外，JOIN 涉及的表尽量不超过 3 张——每多加一张表就相当于嵌套一层循环。

UPDATE/DELETE 的 WHERE 条件列。 这些操作的第一步是"找到"目标行，索引能大幅加速这个定位过程。

区分度高的列。 性别只有男/女两个值，区分度极低，索引后大部分数据仍然不能被有效过滤。身份证号几乎每行不同，区分度极高，非常适合建索引。

区分度的量化计算

区分度 = COUNT(DISTINCT column) / COUNT(*)。值越接近 1，区分度越高。一般区分度 > 0.1 的列才值得考虑。

数据类型小的列优先。 INT（4字节）比 VARCHAR(255)（最长 255 字节）占空间小得多。索引列越短，一个数据页能放的 key 就越多，B+ 树就越矮，查询就越快。

经常出现在 DISTINCT 后面的列。 和 GROUP BY 类似，索引可以加速去重操作。

1.2 什么列不该建索引？

WHERE/ORDER BY/GROUP BY 用不到的列。 建了也不会被用到，白白浪费空间和维护开销。

数据量很小的表。 几百行的表，全表扫描也就是微秒级别的事，索引收益几乎为零。

大量重复数据的列。 性别、布尔值这类列（在数据均匀分布的情况下），索引后大部分数据仍然需要回表，优化器可能干脆不用索引。

特殊情况：数据倾斜

如果一个布尔字段"是否完成"，99% 是 TRUE，1% 是 FALSE。当你查 WHERE finished = FALSE 时，只需要扫描 1% 的数据，索引仍然有巨大价值。

这种场景在任务表中非常常见：state 字段只有 INIT 和 SUCCESS，绝大部分是 SUCCESS。扫描任务时只查 WHERE state = 'INIT'，索引可以过滤掉 99% 的数据。

所以"区分度低就不建索引"这个说法不完全准确——要看你查的是多数派还是少数派。

经常大批量更新的表。 写多读少的表，索引维护（插入时维护 B+ 树、可能触发页分裂）的代价可能超过查询收益。

无序的值不适合做主键索引。 UUID 等随机值会导致频繁页分裂。主键推荐自增 INT 或 BIGINT。

不再使用的索引要及时删除。 减少写操作的维护负担，释放磁盘空间，也让优化器少一个"选错"的选项。

1.3 联合索引的字段顺序

联合索引的字段顺序直接影响它的可用范围。设计时遵循三个原则：

  1. 把查询频率最高的列放最左边——因为最左前缀匹配，最左列能被最多的查询语句利用
  2. 把区分度高的列放前面——区分度高意味着过滤性好，能尽早缩小扫描范围
  3. 把范围查询的列放最后——范围条件（>、<、BETWEEN、LIKE 'xx%'）右边的列索引会失效

举个例子，有三个常见查询条件：status = 'active'、city = 'Beijing'、age > 25。如果 city 的区分度高于 status，索引应该这样建：

-- city 区分度高放前面，age 是范围条件放最后
CREATE INDEX idx_city_status_age ON users(city, status, age);

这样设计的效果：

  • WHERE city = 'Beijing' → 用到 city
  • WHERE city = 'Beijing' AND status = 'active' → 用到 city + status
  • WHERE city = 'Beijing' AND status = 'active' AND age > 25 → 全部用上
  • WHERE city = 'Beijing' AND age > 25 → 用到 city，age 做范围扫描

二、覆盖索引：避免回表的利器

2.1 核心概念

回表是影响查询性能的主要瓶颈之一——通过二级索引查到主键后，还要再到聚簇索引查完整行数据。如果能让索引"覆盖"查询需要的所有列，就不用回表了。

-- 有联合索引 idx_age_name(age, name)，id 是主键

-- 这条需要回表：SELECT * 要取所有列
SELECT * FROM student WHERE age = 20;

-- 这条不需要回表（覆盖索引）：id、age、name 都在索引里
SELECT id, age, name FROM student WHERE age = 20;

提示

为什么 id 也在索引里？因为 InnoDB 的二级索引叶子节点自动附带主键值。所以联合索引 (age, name) 的叶子实际存储的是 (age, name, id)。

EXPLAIN 中看到 Extra = Using index 就说明走了覆盖索引。

2.2 覆盖索引的两个好处

减少 I/O 次数。 不用再去聚簇索引查整行数据，省了一次 B+ 树遍历。如果二级索引命中 100 行，不用覆盖索引就要回表 100 次；有覆盖索引就省了这 100 次。

随机 I/O 变顺序 I/O。 二级索引本身是按 key 有序排列的，沿着索引扫描是顺序读取。而回表时要拿着各种 id 去聚簇索引查，这些 id 对应的数据页可能散落在磁盘各处，变成随机读取，慢得多。

2.3 一个反直觉的现象

你可能听说过"!= 会导致索引失效"。但如果查询刚好被覆盖索引覆盖了，即使条件是 !=，优化器也可能选择扫描索引树而不是全表扫描——因为索引树比完整数据表小得多。

-- 联合索引 idx_age_name(age, name)
-- 虽然 != 通常不走索引，但覆盖索引让扫索引树比扫全表更划算
EXPLAIN SELECT age, name FROM student WHERE age <> 20;
-- 可能显示 type=index, Extra=Using where; Using index

2.4 实践建议

  1. 不要用 SELECT *——精确指定列名是触发覆盖索引的前提
  2. 在设计联合索引时，考虑把 SELECT 中常用的列也加进来
  3. 如果一个高频查询只需要少量列，专门为它建一个覆盖索引，投入产出比非常高
  4. 使用覆盖索引是性能优化中成本最低、收益最高的手段之一

三、前缀索引：长字符串的妥协

3.1 为什么需要前缀索引？

有些字段特别长，比如邮箱地址、URL、家庭住址。把整个字段放进索引有两个问题：

  • 索引占用空间太大，每个 B+ 树节点能放的 key 变少，树变高
  • 索引维护成本也变大——插入一行就要在索引中写一个很长的 key

前缀索引就是只取字段的前 N 个字符来建索引，是一种用"精确度"换"空间和速度"的妥协方案。

-- 只索引 email 的前 10 个字符
CREATE INDEX idx_email ON users(email(10));

3.2 如何选择前缀长度？

关键指标是选择性（Selectivity）：

选择性 = COUNT(DISTINCT LEFT(column, N)) / COUNT(*)

选择性越接近 1，说明前缀长度 N 足以区分大部分数据。

实际操作方法——逐步增加 N，观察选择性何时趋于稳定：

SELECT
  COUNT(DISTINCT LEFT(email, 5))  / COUNT(*) AS sel_5,
  COUNT(DISTINCT LEFT(email, 7))  / COUNT(*) AS sel_7,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT email)           / COUNT(*) AS sel_full
FROM users;

假设结果是：

前缀长度选择性
50.30
70.85
100.95
完整字段0.98

那 7~10 之间就是合理的选择，具体取哪个看你对空间和精度的权衡。

3.3 前缀索引的局限

  1. 精确匹配能力下降。 前 N 个字符重复率高的话（比如身份证号前 6 位是地区码，同城的人都一样），索引几乎没用。

  2. 无法用于覆盖索引。 索引里只存了前缀，不是完整字段值，覆盖不了 SELECT 需要的列。

  3. 无法用于 ORDER BY。 前缀不等于完整值，排序结果会不正确，MySQL 会退化为 filesort。

  4. 前缀太短可能导致锁冲突加剧。 在 RR 隔离级别下，如果多条不同记录的前缀相同，InnoDB 可能锁住更多的索引范围。

提示

前缀索引的核心挑战是在空间节省和区分度之间找到平衡。太短没用，太长失去了前缀索引的意义。一定要用选择性公式来做数据驱动的决策，不要凭感觉。

四、索引设计实战案例

4.1 案例一：电商订单表

场景：一张千万级订单表，常见查询有：

  • 按用户查订单：WHERE user_id = ?
  • 按用户查某状态的订单：WHERE user_id = ? AND status = ?
  • 按用户查某时间段的订单：WHERE user_id = ? AND create_time BETWEEN ? AND ?

索引设计：

-- 一个联合索引搞定三种查询
-- user_id 在最左，区分度最高，三种查询都会用到
-- status 在中间（等值条件）
-- create_time 在最右（范围条件放最后）
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

效果分析：

  • 查询1：用到 user_id（最左前缀）
  • 查询2：用到 user_id + status（前两列）
  • 查询3：用到 user_id + create_time（跳过 status，但 user_id 仍有效）

一个联合索引就覆盖了三种查询模式，不需要建三个单列索引。

4.2 案例二：用户登录验证

场景：SELECT id, name, avatar FROM users WHERE email = ?，这是一个极高频的查询。

方案一：给 email 建普通索引。需要回表取 name 和 avatar。

方案二：给 email 建前缀索引 email(10)。索引更小，但同样需要回表，且精确匹配能力下降。

方案三：建联合索引 (email, name, avatar)。实现覆盖索引，无需回表。索引更大，但对于极高频查询，省掉回表带来的 I/O 收益远大于索引空间的开销。

高频查询优先考虑覆盖索引（方案三）。如果 avatar 是 TEXT 类型（很长），退而求其次用方案一。

4.3 案例三：COUNT 查询的优化

-- COUNT(*) 和 COUNT(1) 本质没区别，系统会自动选择最小的索引来统计
SELECT COUNT(*) FROM orders;

-- COUNT(字段) 只统计非 NULL 的行数
SELECT COUNT(status) FROM orders;

在 InnoDB 中，COUNT(*) 无法像 MyISAM 那样 O(1) 返回（因为 MVCC 下每个事务看到的行数可能不同），必须扫描索引。优化器会自动选择最小的二级索引来扫描——因为二级索引页只存 key+主键，比聚簇索引页（存整行数据）小得多。

4.4 EXISTS vs IN 的选择

-- A 表小、B 表大时，用 EXISTS
-- 相当于对 A 的每一行去 B 中做索引查找
SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE B.id = A.id);

-- B 表小、A 表大时，用 IN
-- 先把 B 的结果集算出来（小），再用 A 去匹配
SELECT * FROM A WHERE id IN (SELECT id FROM B);

核心原则就一句：小表驱动大表。谁的结果集小，谁就做外层循环（或子查询）。

4.5 LIMIT 1 的妙用

如果你确定结果集只有一条，加上 LIMIT 1 可以让 MySQL 找到第一条后就立即停止扫描：

-- 不加 LIMIT 1：找到一条匹配后还会继续扫，直到确认没有更多匹配
SELECT * FROM logs WHERE event = 'login' AND user_id = 123;

-- 加上 LIMIT 1：找到就停
SELECT * FROM logs WHERE event = 'login' AND user_id = 123 LIMIT 1;

但如果查询字段有唯一索引，本身就只会返回一条，加不加 LIMIT 1 没区别。

五、MySQL 8.0 索引新特性

5.1 降序索引

MySQL 8.0 之前只支持升序索引（即使你写 DESC 也会被忽略）。如果查询需要 ORDER BY a ASC, b DESC，只能在内存中做一次额外排序。

8.0 开始真正支持降序索引：

CREATE INDEX idx_a_b ON t(a ASC, b DESC);

对于 ORDER BY a ASC, b DESC 的查询，可以直接利用索引的有序性，避免 filesort。

5.2 隐藏索引

想删一个索引但又怕影响线上查询？8.0 之前只能硬删再硬加回来。8.0 引入了隐藏索引：

-- 把索引设为隐藏（优化器不再使用它，但索引仍然被维护）
ALTER TABLE t ALTER INDEX idx_name INVISIBLE;

-- 观察一段时间确认没有影响后，再真正删除
DROP INDEX idx_name ON t;

-- 如果有影响，随时恢复
ALTER TABLE t ALTER INDEX idx_name VISIBLE;

隐藏索引就像一个"安全网"，让你低风险地验证删除索引的影响。注意：主键不能被设为隐藏。

5.3 函数索引

传统规则是"对索引列使用函数会导致索引失效"。8.0 引入函数索引，可以直接对表达式建索引：

-- 对 YEAR(create_time) 建函数索引
CREATE INDEX idx_year ON orders ((YEAR(create_time)));

-- 这条查询现在可以走索引了
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

函数索引的限制：表达式必须是确定性的（同样的输入永远产生同样的输出）。NOW()、RAND()、UUID() 等非确定性函数不能用。

六、常见面试题精选

Q1：设计索引时要考虑哪些因素？

  1. 查询频率——只给高频查询涉及的列建索引
  2. 区分度——优先选区分度高的列（数据倾斜场景例外）
  3. 联合索引列顺序——最频繁的放最左，范围条件放最右
  4. 覆盖索引——尽量让 SELECT 涉及的列都在索引里
  5. 索引数量——每个索引都有维护成本，不是越多越好
  6. 索引长度——太长浪费空间，太短区分度不够，用选择性公式权衡
  7. 定期用 EXPLAIN 验证——数据量和分布变化后，索引的实际效果可能改变

Q2：联合索引是越多越好吗？

不是。每个索引的代价包括：占存储空间、拖慢写操作（维护 B+ 树）、增加页分裂概率、增加优化器选错索引的风险。

最佳实践：一个联合索引覆盖多种查询模式（通过最左前缀匹配），胜过多个单列索引。

Q3：什么是前缀索引？怎么确定前缀长度？

前缀索引只取字段前 N 个字符建索引，用于优化长字符串字段。用选择性公式确定 N：

SELECT COUNT(DISTINCT LEFT(col, N)) / COUNT(*) FROM table;

选择性接近完整字段的选择性时，N 就够了。注意前缀索引不支持覆盖索引和 ORDER BY。

Q4：怎么比较两个索引的好坏？

两种方法：

方法一：直接运行。 用 FORCE INDEX 指定不同索引分别执行 SQL，比较耗时。

SELECT * FROM t FORCE INDEX (idx_a) WHERE ...;
SELECT * FROM t FORCE INDEX (idx_b) WHERE ...;

方法二：看执行计划。 用 EXPLAIN 对比 type（ref > range > index > ALL）、rows（越少越好）、Extra（不应有 Using filesort / Using temporary）。

小结

原则一句话解释
高频列建索引WHERE/JOIN/ORDER BY 常用的列优先
区分度优先区分度高的列放前面，过滤性更好
范围条件放最右范围条件右边的列索引失效
覆盖索引让 SELECT 的列都在索引里，省掉回表
前缀索引长字符串只取前 N 个字符，用选择性公式确定 N
不要过多索引每个索引都有存储和维护成本
小表驱动大表EXISTS vs IN 的选择取决于表大小
定期验证用 EXPLAIN 检查索引是否被正确使用