SQLite - 索引

SQLite - 索引：提升查询性能

索引是特殊的查找表，数据库搜索引擎使用它们来加快数据检索。索引就像书后的索引一样，充当表中数据的指针。当您需要查找特定主题时，您不会通读整本书；您会在索引中查找主题，索引会指向精确的页码。

在数据库中，索引有助于加速 SELECT 查询，特别是带有 WHERE 子句的查询。然而，这种速度是有代价的：索引会降低数据修改操作（如 INSERT、UPDATE 和 DELETE）的速度，因为索引本身也必须更新。您可以随时创建或删除索引，而不会影响表的数据。

理解何时使用索引

有效使用索引的关键在于理解应用程序的查询模式。您可以使用 EXPLAIN QUERY PLAN 命令来查看 SQLite 如何执行查询以及是否正在使用索引。

假设我们有一个用户表：

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 让我们通过用户名查找用户
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE username = 'alice';

输出可能如下：

QUERY PLAN
`--SCAN TABLE users

SCAN TABLE 意味着 SQLite 必须读取表中的每一行（即全表扫描）才能找到用户。这对于大型表来说效率低下。

CREATE INDEX 命令

CREATE INDEX 语句允许您在表的一个或多个列上构建索引。您需要提供索引的名称、适用的表以及要包含的列。

CREATE INDEX index_name ON table_name (column1, column2, ...);

让我们在 username 列上创建一个索引并重新运行查询计划：

CREATE INDEX idx_users_username ON users (username);

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE username = 'alice';

现在，输出将发生变化，表明正在使用索引：

QUERY PLAN
`--SEARCH TABLE users USING INDEX idx_users_username (username=?)

SEARCH TABLE USING INDEX 确认 SQLite 现在可以直接跳转到相关数据，这比以前快得多。

索引的类型

单列索引

在单个表列上创建的索引。这是最常见的索引类型。

CREATE INDEX idx_users_username ON users (username);

唯一索引

唯一索引确保索引列（或列组合）中的所有值都是唯一的。它既能提高性能，又能保证数据完整性。尝试插入重复值将导致错误。

-- 确保没有两个用户拥有相同的电子邮件地址。
CREATE UNIQUE INDEX uidx_users_email ON users (email);

复合（多列）索引

在表的两个或多个列上创建的索引。当您经常按列组合进行筛选时，这些索引非常有用。索引中列的顺序很重要。

-- 对于产品表，用于快速查找特定类别和价格范围内的产品。
CREATE INDEX idx_products_category_price ON products (category_id, price);

此索引对于按 category_id 或 category_id 和 price 组合进行筛选的查询最有效。对于仅按 price 筛选的查询效果较差。

高级索引：表达式索引和部分索引

现代 SQLite 版本支持更高级的索引策略。

• 表达式索引：您可以在表达式或函数上创建索引。这对于不区分大小写的搜索很有用。
• 部分索引：仅包含表中一部分行（由 WHERE 子句定义）的索引。如果您经常查询数据的特定子集，这可以节省空间并提高性能。

-- 用于不区分大小写的用户名查找的索引
CREATE INDEX idx_users_username_nocase ON users(LOWER(username));

-- 仅用于活跃、高价值订单的索引
CREATE INDEX idx_active_orders ON orders(order_date) WHERE status = 'active' AND amount > 1000;

隐式索引

隐式索引由 SQLite 自动创建，当您在表上定义 PRIMARY KEY 或 UNIQUE 约束时。对于我们的 users 表，SQLite 自动为 id 列（主键）和 email 列（唯一键）创建了索引。

您可以从 Schema 表中列出数据库中的所有索引（包括隐式索引）：

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

DROP INDEX 命令

要删除索引，请使用 DROP INDEX 命令。删除索引时请务必小心，因为它可能会降低查询性能。明智的做法是在删除索引前后测量性能。

DROP INDEX IF EXISTS idx_users_username;

使用 IF EXISTS 可以防止索引已被删除时出现错误。

何时应避免使用索引

尽管索引功能强大，但它们并非总是最佳解决方案。以下是何时应重新考虑添加索引的一些指导原则：

• 小型表： 对于只有几百行的表，全表扫描可能比先读取索引再获取数据的开销更快。
• 写入负载重的表： 如果表有频繁的 INSERT、UPDATE 或 DELETE 操作，维护每个索引的成本可能会变得很高，从而降低应用程序的速度。
• 低基数（Cardinality）列： 对唯一值很少的列（例如布尔型的“状态”列）进行索引通常没有帮助，因为索引的选择性不够高，无法显著缩小搜索范围。
• 很少查询的列： 不要为不经常在 WHERE 子句中使用的列添加索引。