SQLite 部分索引：CREATE INDEX ... WHERE 用法

部分索引只覆盖一部分行

普通索引为表中的每一行都保存一个条目。部分索引只为符合你在创建时给出的 WHERE 子句的行保存条目。索引更小，要遍历的页更少，那些不涉及被索引部分的插入和更新也都省了工作。

语法就是普通的 CREATE INDEX 后面加一个 WHERE：

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

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending 只包含 status = 'pending' 的行的条目。已发货、已取消和已退款的订单根本不在里面。如果你的 orders 表 95% 都是历史数据，而你主要查询未完成的订单，那么索引就能小 20 倍，查询速度却一样。

查询规划器什么时候真正会用它

只有当 SQLite 能够证明你的查询限定在索引覆盖的那些行之内时，部分索引才能被使用。最清楚的做法是在查询里重复索引的 WHERE 子句：

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

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

查询计划里应该会出现 USING INDEX idx_orders_pending。把查询中的 status = 'pending' 去掉，规划器就会退回到全表扫描，因为它没办法知道这个查询是否仍在被索引的子集之内。

经验法则是：查询的 WHERE 必须能推出索引的 WHERE。对同一列、同一个值做等值比较是最安全、最明显的情况。不等式和 OR 就复杂多了，请用 EXPLAIN QUERY PLAN 检查。

为什么值得用：三大好处

部分索引物有所值的三个具体理由：

1. 占用磁盘更少。 只存储匹配的行。对于“表中只有 1% 是热点数据”的场景，索引大约只有完整索引的 1%。
2. 写入更便宜。 只有当行符合过滤条件时，插入和更新才会碰到索引。在上面的表里插入一条 status = 'shipped' 的记录，完全不会碰到 idx_orders_pending。
3. 查找速度不变。 B 树查找的耗时与索引大小呈对数关系。索引更小，查找会稍快一点，但更大的收益在于周边的一切：更少的缓存未命中，更少的 I/O。

如果某一列的数据分布高度倾斜，大多数行都是同一个值，而你只关心少数其他值，那就是部分索引的教科书式用例。

部分唯一索引（杀手级功能）

普通的 UNIQUE 约束适用于每一行。一旦引入软删除，这就成了问题：

-- Fails: there are two rows with email = 'a@x.com', even though one is deleted.
CREATE UNIQUE INDEX idx_users_email ON users(email);

部分唯一索引让你只对真正重要的那些行强制唯一：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- soft-deleted
    ('a@x.com', '2026-01-05'),  -- soft-deleted again
    ('a@x.com', NULL);          -- one live row, allowed

SELECT email, deleted_at FROM users;

三行数据，邮箱相同，却没有违反约束，因为只有 deleted_at IS NULL 的那一行参与唯一性检查。试着再插入一条邮箱相同的有效记录，SQLite 就会报 UNIQUE constraint failed。

这种模式随处可见：每位客户只有一个有效订阅，每个用户只有一个主地址，每个订单只有一张未结发票。部分唯一索引可以直接表达这些规则。

围绕 NULL 建索引

NULL 和索引之间的相互作用有些古怪。一个常见的需求是“完全忽略 NULL”：比如你有一个稀疏的 external_id 列，大多数行都是 NULL，但有值的那些必须唯一：

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

两个 NULL 可以和平共处；EXT-001 和 EXT-002 这两行则保证唯一。索引也更小，因为 NULL 行根本不会被存储，所以即使表变大，按 external_id 查找依然很快。

过滤条件能引用什么

部分索引的 WHERE 子句限制很多。它可以引用：

• 被索引的表中的列。
• 字面量常量。
• 少数确定性的内置函数。

它不能引用：

• 其他表。
• 子查询。
• 非确定性函数，比如 random() 或 CURRENT_TIMESTAMP。
• 参数或变量。

这是有道理的：SQLite 必须在每次插入和更新行时计算这个过滤条件，而且结果必须稳定。所以下面这样是可以的：

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

但 WHERE created_at > date('now') 就不行：date('now') 会随时间变化，被索引的行集合就会在 SQLite 不知情的情况下发生变化。

一套自检流程

添加部分索引时，按这三项逐一检查：

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. Does the planner pick the index?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. Does it ignore queries outside the filter?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. Does it skip queries missing the filter entirely?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

查询 1 应该使用 idx_jobs_runnable。查询 2 和 3 应该退回到扫描（如果你有别的索引，也可能用那个）。如果规划器在你没预料到的查询上选了这个部分索引，就再读一遍过滤条件，它可能比你以为的范围更宽。

什么时候不该用

部分索引是把锋利的工具。以下情况可以不用：

• 过滤条件匹配了表中的大部分行。 如果“活跃”的行占了 90%，部分索引就只是多绕了几步的普通索引。直接给那一列建索引就好。
• 你的查询没有原样包含过滤条件。 如果你的代码使用 ORM，生成的是 WHERE status IN (?, ?, ?)，或者动态计算过滤条件，规划器往往认不出它们是匹配的。用 EXPLAIN QUERY PLAN 测试，不要想当然。
• 热点子集会随时间变化。 给“最近 30 天的订单”建部分索引听起来很诱人，但没法表达出来，因为过滤条件必须是确定性的。你得重建索引，或者换一种表结构（单独建一张 recent_orders 表，或者加一个每晚翻转的 archived 布尔列）。

当过滤条件稳定、而且只匹配一张大表中的一小部分时，部分索引是你在 SQLite 中能做的回报最高的调优手段之一。

下一篇：读懂查询计划

这一页大量依赖 EXPLAIN QUERY PLAN 来确认索引确实被用上了。这个工具值得单独用一页来讲：如何读懂它的输出、各个关键字是什么意思，以及如何分辨顺利的索引查找和偷偷摸摸的全表扫描。这就是下一页的内容。