SQLite - INDEXED BY 子句

INDEXED BY 子句是 SQLite 特有的非标准 SQL 功能，它充当一个查询规划器提示。它指示 SQLite 在查询表时使用特定的索引，从而覆盖查询规划器的默认选择。

相反，NOT INDEXED 子句指示 SQLite 避免为表使用任何索引，强制进行全表扫描。即使使用 NOT INDEXED，INTEGER PRIMARY KEY 仍可能用于查找。

注意： SQLite 的查询规划器经过高度优化。在大多数情况下，它会自动选择最佳索引。使用 INDEXED BY 强制使用索引应作为最后手段，仅在你确有证据表明规划器做出了次优选择时才使用。

语法

该子句紧跟在 SELECT、DELETE 或 UPDATE 语句中的表名之后。

SELECT ... FROM table_name INDEXED BY index_name WHERE ...;

SELECT ... FROM table_name NOT INDEXED WHERE ...;

如果指定的 index_name 不存在或无法用于查询，该语句将无法准备。

示例场景

考虑一个 CUSTOMERS 表，它有两个索引：一个在 country 列上，一个在 last_active_date 列上。

CREATE TABLE CUSTOMERS (
  id INTEGER PRIMARY KEY,
  name TEXT,
  country TEXT,
  last_active_date DATE
);

CREATE INDEX idx_country ON CUSTOMERS(country);
CREATE INDEX idx_last_active ON CUSTOMERS(last_active_date);

-- （假设此表填充了数百万行数据）

现在，考虑一个查询，用于查找来自一个非常普遍的国家的高度活跃客户。

SELECT name FROM CUSTOMERS
WHERE country = 'USA' AND last_active_date > '2023-01-01';

查询规划器可能会选择 idx_country，因为 country = 'USA' 条件很简单。然而，如果 90% 的客户都来自“美国”，那么这个索引的选择性不高。使用 idx_last_active 会更有效率。你可以强制执行此选择：

SELECT name FROM CUSTOMERS INDEXED BY idx_last_active
WHERE country = 'USA' AND last_active_date > '2023-01-01';

何时考虑使用它

性能调优： 当你使用 EXPLAIN QUERY PLAN 并确定查询规划器对于一个关键查询持续选择效率较低的索引时。
复杂查询： 在包含多个连接和 WHERE 子句的非常复杂的查询中，规划器的统计分析可能不完善。
确保一致性： 为了锁定特定的查询计划以获得可预测的性能，尽管这可能会使你的代码变得脆弱。

风险与缺点

脆弱性： 你的查询现在与特定的索引名称绑定。如果在重构期间索引被重命名或删除，查询将中断。
次优性能： 如果数据分布随时间变化，你强制选择的索引可能会比规划器的自动选择更差。
可读性降低： 它为你的查询增加了非标准语法，使其他开发者更难理解。