数据库索引应该如何设计?联合索引、覆盖索引与查询优化

"为什么加了索引还是很慢""明明建了索引,EXPLAIN 却显示全表扫描""索引建了一堆,写入反而更慢了"——这些是后端和运维最常遇到的性能困惑。索引不是"建得越多越好"的银弹,它是一门关于取舍的设计:用空间和写入性能,换查询性能。建得好,查询从几秒降到几毫秒;建得不好,不仅没用,还会拖慢写入、浪费磁盘、让优化器选错执行计划。

这篇文章从索引的底层结构讲起,逐层展开到实战:先理解 B+ 树索引为什么能加速查询、什么是回表;再重点讲联合索引的最左前缀原则和覆盖索引这两个最容易考也最实用的点;然后给出用 EXPLAIN 分析执行计划、判断索引有没有被用上的方法;最后落到"索引选择性怎么算、什么时候不该建索引、大表加索引怎么避免锁表"。文中所有 SQL 都是真实可执行的,涉及的 ALTER TABLE、DROP INDEX 等操作会给出风险提示和回滚方式。

需要说明:本文以 MySQL(InnoDB)为主线,辅以 PostgreSQL 的差异说明。索引设计的结论要结合真实数据分布和查询模式来定,如果对某条 SQL 的执行效果不确定,请在你的数据量和数据分布上用 EXPLAIN 实测,而不要照搬经验。

一、为什么索引能加速:B+ 树与回表

数据库索引最常用的结构是 B+ 树。它把数据按索引列的值排序,组织成一棵矮胖的多叉树:根节点、中间节点只存键值和指针,叶子节点存键值和数据位置,叶子之间用链表相连。这种结构带来两个好处:查找的树高很低(几千万行数据通常只有 3~4 层,经验值,即查任意一条记录只需几次磁盘 IO),以及支持范围查询(叶子链表让 BETWEEN、ORDER BY、> 等区间扫描很高效)。

要理解索引的效果,必须先理解一个关键概念:回表(回表查询 / random access)。在 InnoDB 中,二级索引(普通索引)的叶子节点存的是索引列的值 + 主键值,并不包含整行数据。所以用二级索引查非索引列时,过程是:

  1. 先在二级索引树里找到满足条件的主键值;
  2. 再拿主键值回到主键索引(聚簇索引)树里查出完整行。

这两步就是"回表"。回表意味着额外的随机 IO,是索引查询变慢的常见原因。如果能做到不用回表,性能会显著提升——这就引出了后面要讲的"覆盖索引"。

对比一下有无索引的差别就很直观:对一张 1000 万行、没有索引的表做 WHERE user_id = 12345,MySQL 只能逐行扫描(全表扫描),磁盘 IO 以百万次计;有了 user_id 的索引,通过 B+ 树几次 IO 就能定位。这个差距是数量级的。

二、联合索引与最左前缀原则

实际业务里,查询条件往往不止一个字段,这时就要用联合索引(复合索引)。联合索引是把多个列组合成一个索引,比如 INDEX idx(a, b, c)。它的 B+ 树是按 a、再按 b、再按 c 的顺序排序的。这就引出了最有用的规则——最左前缀原则。

简单说:联合索引 (a, b, c) 能被有效利用的前提是,查询条件从最左边的列开始、连续匹配。可以用"电话簿"来类比:电话簿按"姓、名"排序,你能快速找到"姓张"的人,也能找到"姓张名三"的人,但没法直接跳过"姓"去查"所有名叫三的人"。

具体的可用情况:

查询条件 对索引 (a,b,c) 的利用 说明
a = ? 用 a 用到最左列
a = ? AND b = ? 用 a、b 连续前缀
a = ? AND b = ? AND c = ? 用 a、b、c 全用上
b = ? 用不上 跳过最左列
a = ? AND c = ? 用 a,c 用不上排序 b 缺失,c 只能后续过滤
a > ? AND b = ? 用 a 做范围,b 用不上 范围条件后,后面列失效
a = ? ORDER BY b 用 a,排序可利用 b 前缀匹配 + 排序优化

实操验证(MySQL):

-- 建表
CREATE TABLE t_order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  KEY idx_user_status_created (user_id, status, created_at)
) ENGINE=InnoDB;

-- 走索引:条件从最左列连续匹配
EXPLAIN SELECT * FROM t_order WHERE user_id = 100 AND status = 1;

-- 走索引且能利用 created_at 做范围
EXPLAIN SELECT * FROM t_order
WHERE user_id = 100 AND status = 1 AND created_at >= '2027-01-01';

-- 用不上(跳过 user_id)
EXPLAIN SELECT * FROM t_order WHERE status = 1;

-- user_id 用上,status 因为中间断了而无法有效利用
EXPLAIN SELECT * FROM t_order WHERE user_id = 100 AND created_at >= '2027-01-01';

联合索引的列顺序设计原则(这是设计的核心):

  1. 等值条件列放前面,范围条件列放后面。因为范围条件一旦出现,其后的列就难以用于索引查找。
  2. 区分度(选择性)高的列尽量靠前,能更快缩小扫描范围。
  3. 把 ORDER BY / GROUP BY 的列接在等值列后面,可能省掉排序操作。
  4. 考虑覆盖索引,把查询需要的列都放进去,避免回表。

三、覆盖索引:能不回表就不回表

覆盖索引(covering index) 指的是:一个查询需要的所有列,都能从索引里直接取到,不需要回表查主键索引。这是提升查询性能最有效的手段之一。

举例:SELECT user_id, status FROM t_order WHERE user_id = 100,如果索引是 (user_id, status),那么索引里已经有 user_id 和 status 两个字段的值,MySQL 直接读索引即可返回结果,无需回表。

-- 覆盖索引示例:查询列都在索引里
EXPLAIN SELECT user_id, status FROM t_order WHERE user_id = 100;
-- Extra 列会显示 "Using index",表示用到了覆盖索引,没有回表

-- 对比:多查了 amount(不在索引里)就会回表
EXPLAIN SELECT user_id, status, amount FROM t_order WHERE user_id = 100;
-- Extra 里没有 "Using index",因为需要回表取 amount

在 MySQL 8.0 之前,InnoDB 的二级索引并不支持真正的"索引跳过扫描"等高级特性,但覆盖索引一直有效。EXPLAIN 输出里,Extra 列出现 Using index 就说明命中了覆盖索引——这是判读执行计划时最该认识的几个标记之一。

常见能改造成覆盖索引的场景:列表页查询只查少数字段、统计类查询只查聚合前的列、关联查询中先把驱动表的少量列取出来。但也要注意后果:把很多列塞进索引会让索引变得又大又宽,写入更慢、占用更多空间。覆盖索引要权衡,不是把所有列都塞进去。

四、用 EXPLAIN 判断索引是否生效

设计完索引,必须用 EXPLAIN 实测,别靠猜。重点看这几列:

列 关注点 好/坏信号
type 访问类型 好:const/eq_ref/ref/range;差:ALL(全表扫描)、index(全索引扫描)
possible_keys 可能用到的索引 为空说明没有可用索引
key 实际使用的索引 为空 = 没走索引
key_len 用到的索引长度 越短通常越好,也能看出用了索引的哪几列
rows 预估扫描行数 越小越好
filtered 过滤后剩余比例 越大越好
Extra 附加信息 Using index 好;Using filesort/Using temporary 要优化

实战示例:

-- 分析一条 SQL
EXPLAIN SELECT id, amount FROM t_order
WHERE user_id = 100 AND status = 1 AND created_at >= '2027-01-01'
ORDER BY created_at DESC
LIMIT 20\G

-- MySQL 8.0 可用 EXPLAIN ANALYZE,会真正执行并给出实际耗时
EXPLAIN ANALYZE SELECT ... ;

-- PostgreSQL 对应命令
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

从 Extra 里能读出很多信息:

  • Using index:命中覆盖索引,很好。
  • Using where:在存储引擎返回结果后还要过滤,可能索引没完全覆盖。
  • Using filesort:需要额外排序,考虑让索引覆盖 ORDER BY。
  • Using temporary:用了临时表,常见于 GROUP BY、DISTINCT,考虑加索引或改写。
  • Using index condition:MySQL 5.6+ 的索引下推(ICP),是好事。

一个典型的优化:WHERE user_id=? ORDER BY created_at DESC LIMIT 20,如果索引是 (user_id, created_at),就能既过滤又排序,避免 filesort;如果索引只是 (user_id),就会先过滤再 filesort,数据量大时很慢。

五、索引设计与维护的实战要点

1. 索引选择性要算清楚。 选择性 = 不重复值数量 / 总行数,越接近 1 越好。选择性低的列(如性别、状态只有几个值)单独建索引意义不大。

-- 计算某列的选择性(经验值参考)
SELECT 
  COUNT(DISTINCT user_id) / COUNT(*) AS sel_user_id,
  COUNT(DISTINCT status) / COUNT(*) AS sel_status
FROM t_order;

-- 选择性 > 0.1(经验值)通常值得单独建索引;接近 0.01 的列单独索引收益很小
-- 更准确的办法是看 MySQL 的统计信息
SHOW INDEX FROM t_order;

2. 别建冗余和重复索引。 (a, b) 和 (a) 同时存在时,(a) 是冗余的(被 (a,b) 的最左前缀覆盖),应该删掉。多余的索引会拖慢写入。找出重复索引:

-- pt-duplicate-key-checker 可自动检查冗余索引(Percona Toolkit)
pt-duplicate-key-checker --host=127.0.0.1 --user=root --ask-pass

3. 大表加索引要避免长时间锁表。 MySQL 8.0 的 InnoDB 支持在线 DDL,加二级索引通常不阻塞写,但仍有开销:

-- 8.0 加索引(默认 ONLINE,但仍建议低峰执行)
ALTER TABLE t_order ADD INDEX idx_user_status_created (user_id, status, created_at);

-- 5.7 可显式指定算法(减少锁)
ALTER TABLE t_order ADD INDEX idx_x (user_id), ALGORITHM=INPLACE, LOCK=NONE;

-- 查看建索引进度(8.0)
SELECT * FROM performance_schema.events_stages_current;

-- PostgreSQL 建索引用并发方式,避免锁表
CREATE INDEX CONCURRENTLY idx_user_status_created
ON t_order (user_id, status, created_at);

风险提示:给大表加/删索引会消耗大量 IO 和 CPU,可能持续数分钟到数小时(取决于表大小,经验值)。务必:低峰执行;提前确认磁盘余量;先备份或用快照;MySQL 5.7 用 ALGORITHM=INPLACE, LOCK=NONE 尽量在线;PostgreSQL 用 CREATE INDEX CONCURRENTLY(注意它不能在事务块里执行,且中途失败会留下 invalid 索引,需要 DROP INDEX 清理)。删索引的回滚方式是重新创建,务必留好创建语句。

4. 避免让索引失效的写法。 以下写法常导致索引失效,要尽量避免:

写法 问题 改写建议
WHERE DATE(created_at) = '2027-01-01' 对索引列做函数运算 改成范围:created_at >= '2027-01-01' AND created_at < '2027-01-02'
WHERE user_id + 1 = 101 列参与算术 移项到右侧:user_id = 100
WHERE name LIKE '%abc' 前导通配符无法用索引 尽量 abc%;或全文检索
WHERE status != 1 不等于通常不走索引 根据实际取值改写
WHERE a = 1 OR b = 2 OR 连接不同列可能失效 考虑拆成 UNION,或建 (a)、(b) 各自索引
隐式类型转换 WHERE varchar_col = 123 类型不一致导致转换 保持类型一致,= '123'

六、什么时候不该建索引

索引有成本,以下几种情况要克制:

  • 表很小(几百行以内):全表扫描比走索引还快,索引意义不大。
  • 写多读少的表:索引会拖慢 INSERT/UPDATE/DELETE,如果几乎不查就不值得。
  • 选择性极低的列:如性别、是否删除标记,单独建索引几乎没用(但可作为联合索引的前缀或后缀参与覆盖)。
  • 频繁大量更新的列:更新会导致索引维护开销,非必要不加。
  • 一次性查询:临时需求用一次性导出解决,不要为它永久建索引。

反过来,该建索引的信号是:这条 SQL 频繁执行、扫描行数大、出现在慢查询日志里、且选择性够好。

常见问题

Q1:为什么我建了索引还是全表扫描?
常见原因:条件跳过了联合索引的最左列;对索引列用了函数或运算;LIKE '%x' 前导通配符;类型隐式转换;或者优化器根据统计信息判断走索引更慢。用 EXPLAIN 看 key 和 type 就能确认。

Q2:联合索引的列顺序到底怎么定?
原则是:等值条件列在前,范围条件列在后;选择性高的列尽量靠前;把排序/分组列接在等值列后面;兼顾覆盖索引。最终以 EXPLAIN 实测为准,别教条。

Q3:覆盖索引是不是把要查的列都放进索引就好?
不是。索引越宽,占空间越大、写入越慢。覆盖索引适合"查询列少且稳定"的场景,不要为了覆盖把所有列都塞进去,要权衡读收益和写成本。

Q4:索引越多查询越快吗?
不是。每个索引都要在写入时维护,索引越多,INSERT/UPDATE/DELETE 越慢,也会占用更多内存和磁盘。要精简索引,删掉冗余和用不上的。

Q5:大表在线加索引一定不锁表吗?
MySQL 8.0 加二级索引一般支持在线(不阻塞 DML),但会占用资源、可能耗时很久;加主键、改列类型等仍可能锁表。PostgreSQL 用 CREATE INDEX CONCURRENTLY 可以避免长期锁表。无论如何,低峰操作、留备份、能回滚都是前提。

总结

  • 索引本质是 B+ 树,理解"回表"是理解索引性能的基础;能用覆盖索引避免回表,是提速的关键手段。
  • 联合索引遵循最左前缀原则,设计时按"等值列在前、范围列在后、选择性与排序列兼顾"的顺序排列。
  • 用 EXPLAIN 看 type/key/Extra 判断索引是否生效,Using index 是覆盖索引的好信号,filesort/temporary 是要优化的信号。
  • 索引有成本:选择性低、表小、写多读少、频繁更新的场景要谨慎;删除冗余索引能减轻写入负担。
  • 大表加/删索引属于高风险操作,务必低峰、留备份、用在线 DDL(MySQL LOCK=NONE、PG CONCURRENTLY),并准备回滚。