数据库原理与选型 / 系统课时
单列索引与复合索引
单列索引与复合索引
索引不是“建得越多越快”,而是要匹配真实查询。本文用同一张表创建单列索引和复合索引,验证等值查找与排序如何利用索引,并给出最左前缀、列顺序和选择性判断的实用规则。
学习目标
- 能根据查询条件选择单列索引或复合索引
- 理解复合索引的最左前缀与排序利用条件
- 能避免只按“选择性最高”机械排列索引列
从查询模式决定索引形态
单列索引适合稳定地按一个字段定位数据,例如用邮箱查用户。复合索引把多个列按固定顺序组织,适合同时承担过滤、排序或覆盖查询。设计时应先写出高频 SQL,再反推索引,而不是从表结构猜索引。
| 查询模式 | 更合适的索引 |
|---|---|
WHERE email = ? |
(email) |
WHERE tenant_id = ? ORDER BY created_at DESC |
(tenant_id, created_at DESC) |
WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC |
(tenant_id, status, created_at DESC) |
创建并验证两类索引
下面的示例可直接在 SQLite 中执行。两个索引分别服务于邮箱等值查找和租户内按时间倒序读取。
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INTEGER PRIMARY KEY,
tenant_id INTEGER NOT NULL,
email TEXT NOT NULL,
status TEXT NOT NULL,
created_at TEXT NOT NULL
);
INSERT INTO users (tenant_id, email, status, created_at) VALUES
(1, 'a@example.com', 'active', '2026-09-01 09:00:00'),
(1, 'b@example.com', 'active', '2026-09-01 10:00:00'),
(2, 'c@example.com', 'disabled', '2026-09-01 11:00:00');
CREATE INDEX idx_users_email
ON users(email);
CREATE INDEX idx_users_tenant_created
ON users(tenant_id, created_at DESC);
EXPLAIN QUERY PLAN
SELECT id, email, status, created_at
FROM users
WHERE email = 'b@example.com';
SELECT id, email, status, created_at
FROM users
WHERE email = 'b@example.com';
EXPLAIN QUERY PLAN
SELECT id, email, status, created_at
FROM users
WHERE tenant_id = 1
ORDER BY created_at DESC;
SELECT id, email, status, created_at
FROM users
WHERE tenant_id = 1
ORDER BY created_at DESC;预期输出
查询计划的数字编号和展示格式会随 SQLite 版本或客户端不同;下面只保留稳定的 detail 含义与结果行。
SEARCH users USING INDEX idx_users_email (email=?)
2|b@example.com|active|2026-09-01 10:00:00
SEARCH users USING INDEX idx_users_tenant_created (tenant_id=?)
2|b@example.com|active|2026-09-01 10:00:00
1|a@example.com|active|2026-09-01 09:00:00第二个计划没有出现 USE TEMP B-TREE FOR ORDER BY,说明 (tenant_id, created_at DESC) 在限定租户后还能直接提供所需顺序,避免额外排序。
理解最左前缀与排序利用
把复合索引想成按列从左到右排序的有序结构。对于 (tenant_id, created_at DESC),查询只给 tenant_id 时可以快速定位这一段记录;在这段记录内部,created_at 已按降序排列,所以相同的排序方向可以直接复用索引。
若索引是 (tenant_id, status, created_at),常规规则是先从最左列连续匹配。只按 status 查询通常不能获得同样直接的前缀定位能力。SQLite 在特定统计信息和数据分布下可能使用 skip-scan,因此“最左匹配”应当理解为主要设计原则,而不是优化器绝不会突破的硬语法限制。
范围条件也会影响后续列的利用。例如索引为 (tenant_id, created_at) 时,tenant_id = ? AND created_at >= ? 很合适;一旦前部列进入范围扫描,后续列通常就难以继续作为同等级的定位条件。
排列列顺序时不要只看选择性
“选择性高的列一定放最前面”过于简单。列顺序首先要匹配最常见的过滤前缀,其次考虑范围条件和 ORDER BY。例如多租户列表页经常按 tenant_id 过滤并按时间排序,即使 tenant_id 的选择性不如 email,它仍应位于该复合索引的首列。
选择性更适合作为成本判断因素:低选择性的布尔列单独建索引常常收益有限,但它放在一个已由高频等值条件限定的复合索引中,可能仍然有效。最终应通过 EXPLAIN QUERY PLAN 和真实数据量下的耗时验证。
控制索引数量与维护成本
每增加一个索引,INSERT、UPDATE、DELETE 都需要同步维护更多 B-tree,同时占用额外磁盘空间。对写入频繁的表,应优先保留能覆盖高频查询的少量索引,并定期检查长期未使用或功能重叠的索引。
当一个复合索引完整以前缀形式覆盖某个普通单列索引,且后者没有 UNIQUE 等独立约束价值时,可以评估是否删除重复索引;删除前必须重新检查关键查询计划。索引设计的目标不是追求数量,而是让最常见的访问路径以最小总体成本完成。