SQLite 复合索引：列顺序与最左前缀原则

一个索引，多个列

复合索引也叫多列索引，是建立在两个或更多列上的单个索引。创建时按顺序列出这些列即可：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    total REAL NOT NULL
);

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

索引 idx_orders_customer_status 中的条目先按 customer_id 排序，同一个客户内部再按 status 排序。排序方式就是全部的关键，复合索引的其他特性都由它而来。

心智模型：排好序的电话簿

想象一本老式电话簿。条目按姓排序，同姓的再按名排序。这正是 (last_name, first_name) 上的索引的样子。

有些查找很快，有些则不然：

• “找出所有姓 Patel 的人”：很快，所有 Patel 都挨在一起。
• “找 Priya Patel”：很快，先跳到 Patel，再往下找到 Priya。
• “找出所有叫 Priya 的人”：很慢，你得翻遍每一页，因为叫 Priya 的人分散在各个姓里。

SQLite 的复合索引也是这样工作的。第一列是主排序键；第二列只对第一列值相同的条目排序。

最左前缀原则

只有当 WHERE 子句约束的是索引列的“最左前缀”时，SQLite 才能用复合索引执行查询。对于 (a, b, c) 上的索引：

• 过滤 a：会用到索引。
• 过滤 a 和 b：会用到索引。
• 过滤 a、b 和 c：会用到索引。
• 只过滤 b、只过滤 c，或者过滤 b 和 c：不会用到索引。

你可以直接用 EXPLAIN QUERY PLAN 验证：

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

第一个查询计划显示 SEARCH events USING INDEX idx_events_user_kind_time。第二个则退回到 SCAN events：只过滤 kind 跳过了排在最前面的 user_id 列，所以这个索引对该查询毫无用处。

列顺序是一个设计决策

既然最左前缀这么重要，CREATE INDEX 里列的排列顺序就是一个实打实的选择，而不只是写法风格。有两条经验法则：

1. 把最常用来过滤的列放在最前面。 这一列能让索引适用于最多的查询。
2. 等值列放在范围列前面。 SQLite 可以先用 = 在索引中精确定位，再用 <、> 或 BETWEEN 扫描一段连续范围，但范围扫描只能用在最后一个被使用的列上。

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Good: equality on region first, range on sold_at second.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

查询计划显示 SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?)。SQLite 直接跳到 region = 'EU'，然后沿日期范围向后走。如果把列顺序换成 (sold_at, region)，同一条查询就得扫描日期范围内的每一行，并逐行重新检查 region。

复合索引与多个单列索引

一个常见的问题：应该在 (a, b) 上建一个索引，还是在 a 和 b 上分别建两个索引？

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Option A: one composite index.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Option B (alternative): two single-column indexes.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

对于组合过滤，复合索引更快：SQLite 直接走到匹配的 (project_id, state) 条目。如果是两个单列索引，SQLite 通常只挑其中一个来缩小行的范围，再对每个匹配的行检查另一列。它有时能把两个索引取交集，但当这些列是“一起”查询时，复合索引才是更干净的方案。

如果 project_id 和 state 也会被单独查询，你可能两种都需要：复合索引用于组合过滤，再加一个 state 上的单列索引，给只按它过滤的查询用。

覆盖索引

当一个索引包含了查询需要的每一列（过滤用的列和选出的列）时，SQLite 完全不用访问表就能得出结果。这就是覆盖索引，也是查询能达到的最快速度。

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'first'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'rush');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

查询计划显示 USING COVERING INDEX idx_invoices_cover。查询直接从索引中读出 issued_at 和 total，用不到 notes 和 id，所以表本身根本不会被打开。如果某条查询一直在跑，单纯为了覆盖它而往复合索引里加一列，是很划算的交换。

复合 UNIQUE 约束

复合索引还能保证多列组合的唯一性。当单独哪一列都不唯一、但组合必须唯一时，这很有用：

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ok, different course
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- fails: pair repeats

第三条插入会报错 UNIQUE constraint failed: enrollments.student_id, enrollments.course_id。索引中已经有同样的组合，所以 SQLite 拒绝了这条重复数据。

值得注意的陷阱

• 非前导列之间的 OR 会让索引失效。 对于 (a, b) 上的索引，WHERE a = 1 OR b = 2 通常完全用不上它，因为 SQLite 需要分别考虑两个分支。
• 在索引列上套函数会让索引失效。 WHERE lower(email) = 'x' 用不上 email 上的索引。改为给表达式建索引，或者在插入时就把数据规范化。
• 索引不是免费的。 每次 INSERT、UPDATE（更新了索引列时）和 DELETE 都要更新每一个索引。在写入频繁的表上建三个复合索引，写入开销可能主要都花在它们身上。
• 建好索引后运行 ANALYZE。 SQLite 的查询规划器靠 ANALYZE 收集的统计信息在候选索引之间做选择。没有这些统计信息，它只能退回到启发式规则，而这些规则并不总是最优的。

实用工作流程

优化一条慢查询时，流程通常是这样的：

1. 对这条查询运行 EXPLAIN QUERY PLAN，看看 SQLite 现在是怎么执行的。
2. 如果它在全表扫描，就看看 WHERE 子句：等值条件是哪一列？范围条件是哪一列？选出了哪些列？
3. 建一个复合索引，等值列在前、范围列在后；如果覆盖能带来好处，再把选出的列追加到后面。
4. 运行 ANALYZE。
5. 再次运行 EXPLAIN QUERY PLAN，确认计划变了，并且用上了索引。
6. 在有代表性的数据上，对比查询优化前后的耗时。

跳过第 6 步要自担风险。一个在查询计划里“看起来”没问题的索引，在实际中仍可能更慢，比如表很小，或者规划器选了另一条路径。

下一篇：部分索引

复合索引覆盖表中的每一行。但很多时候，真正重要的只是一小部分行：未关闭的工单、尚未处理的任务、没有删除的记录。“部分”索引让你只给这些行建索引，把一个 WHERE 子句直接写进索引本身。这就是下一页的内容。