SQLite 索引：CREATE INDEX 的用法、时机与原理

索引到底是什么

索引是一个独立的数据结构，一棵有序的 B 树，它让 SQLite 能根据某一列的值找到行，而不必扫描整张表。没有索引时，像 WHERE email = 'rosa@example.com' 这样的查询会读取每一行并逐一检查。有了 email 上的索引，SQLite 大约只需 log(n) 步就能沿着树找到匹配项。

这种提速不是免费的。索引是被索引列的一份副本，外加指回原行的指针。每次 INSERT、每次 UPDATE 被索引的列，以及每次 DELETE，都要同时更新索引。磁盘占用会增加，写入吞吐量会略微下降。这笔交易是：在写入上付出一点，在读取上省下更多。

创建索引

基本语法：

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

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

命名约定：大多数团队使用 idx_<table>_<column>，一眼就能看出索引的用途。索引名必须在整个数据库中唯一，而不只是在这张表中唯一，这就是名字里要带上表名的原因。

要删除索引：

DROP INDEX idx_users_email;

索引纯粹是为性能搭的脚手架。删除索引永远不会影响你的数据，只会影响查询的速度。

唯一索引

唯一索引身兼两职：它既能加快查找，又能保证任意两行的索引值都不相同。

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

第三次插入会失败，报错 UNIQUE constraint failed: accounts.username。SQLite 已经会为 PRIMARY KEY 和 UNIQUE 列自动创建唯一索引，你会看到它们被命名为 sqlite_autoindex_<table>_<n>。只有当表本身没有声明这个约束时，你才需要写 CREATE UNIQUE INDEX。

查询规划器实际做了什么

添加了索引，并不保证 SQLite 一定会用它。查询规划器会为每条查询选择一种策略，你可以用 EXPLAIN QUERY PLAN 查看它的选择：

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

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

在输出中找 SEARCH ... USING INDEX idx_orders_customer：这表示索引正在被使用。如果你看到的是 SCAN orders，说明规划器认为全表扫描更便宜（在很小的表上这往往是对的），或者你的查询写法让它无法使用索引。后面会有一整篇文档讲解如何阅读这些查询计划。

什么时候索引不会被使用

索引有几个众所周知的盲区。下面每一种写法都会让 email 上的索引失效：

-- Function wraps the column
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Leading wildcard in LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Type mismatch forces a conversion
SELECT * FROM users WHERE email = 12345;

B 树是按 email 的原始值排序的，所以任何在查询时对这一列做变换的操作都会导致全表扫描。解决办法各不相同：存储已经规范化的数据（email_lower 列），使用表达式索引（CREATE INDEX idx ON users(lower(email))），或者用 SQLite 的全文搜索来做子字符串匹配。

覆盖索引

如果一个索引包含了查询需要的所有列，SQLite 不用访问表本身就能回答查询，这就是覆盖索引。诀窍是在索引定义中加入额外的列：

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

因为查询所需的两列都在索引中，SQLite 会报告 USING COVERING INDEX。不需要再去读取行。对于频繁读取的路径，覆盖索引是收益最高的优化之一，代价是索引更大。多列索引是一个单独的主题，下一篇文档会详细介绍。

列出和查看索引

有两种方法查看现有的索引：

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

这会列出数据库中的每个索引及其 CREATE 语句。对于单张表，PRAGMA index_list('products'); 只显示这张表的索引，PRAGMA index_info('idx_products_name'); 显示每个索引包含哪些列。以 sqlite_autoindex_ 开头的索引是为 PRIMARY KEY 或 UNIQUE 约束自动创建的，你无法删除它们。

什么时候不该添加索引

在下面几种情况下，添加索引反而会让事情更糟：

• 很小的表。 几百行的扫描只需几微秒。规划器很可能根本不理会这个索引，你却白白增加了写入开销。
• 写入频繁却很少查询的列。 每次写入都要更新每个索引。给几乎从不用来过滤的列建索引，纯粹是成本。
• 单独的低基数列。 在只有三种取值的 status 列上建索引，缩小不了多少范围。它作为复合索引的第二列，或者作为部分索引，仍然可能有帮助，但单独使用通常不值得。
• 已经被覆盖。 如果已经有 (a, b) 上的索引，就不需要再建 (a) 上的索引。只按 a 过滤的查询，SQLite 会使用复合索引的前导列。

对于“我该不该加这个索引？”，诚实的回答几乎总是：先试试，运行 EXPLAIN QUERY PLAN，用真实的数据测量，再做决定。

下一篇：复合索引

单列索引能覆盖很多场景，但真实的查询经常同时按多列过滤和排序。复合索引（建在 (a, b, c) 上的索引）就是为此而生的，而且列的顺序比人们想象的更重要。这就是下一页的内容。