表达式索引

通常，SQL 索引引用表中的列。但索引也可以在涉及表列的表达式上形成。

例如，考虑以下跟踪各种“账户”的美元金额变化的表格

CREATE TABLE account_change(
  chng_id INTEGER PRIMARY KEY,
  acct_no INTEGER REFERENCES account,
  location INTEGER REFERENCES locations,
  amt INTEGER,  -- in cents
  authority TEXT,
  comment TEXT
);
CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));

account_change 表中的每个条目都记录了对账户的存款或取款。存款有正的“amt”，取款有负的“amt”。

acctchng_magnitude 索引是在账户号 ("acct_no") 和金额的绝对值上。此索引允许您对账户变化的幅度进行高效查询。例如，要列出对账户号 $xyz 的所有超过 $100.00 的变化，您可以说

SELECT * FROM account_change WHERE acct_no=$xyz AND abs(amt)>=10000;

或者，要按降序幅度列出对某个特定账户 ($xyz) 的所有变化，您可以编写

SELECT * FROM account_change WHERE acct_no=$xyz
 ORDER BY abs(amt) DESC;

以上两个示例查询在没有 acctchng_magnitude 索引的情况下也可以正常工作。acctchng_magnitude 索引仅仅帮助查询运行得更快，尤其是在表中每个账户都有很多条目的数据库中。

1. 如何在表达式中使用索引

使用 CREATE INDEX 语句在对一个或多个表达式创建新索引，就像您在列上创建索引一样。唯一的区别是表达式被列为要索引的元素，而不是列名。

当索引的表达式出现在查询的 WHERE 子句或 ORDER BY 子句中时，SQLite 查询规划器会考虑使用表达式上的索引，完全与它在 CREATE INDEX 语句中的写法相同。查询规划器不做代数运算。为了将 WHERE 子句约束和 ORDER BY 项与索引匹配，SQLite 要求表达式相同，除了诸如空格变化之类的细微语法差异。因此，如果您有

CREATE TABLE t2(x,y,z);
CREATE INDEX t2xy ON t2(x+y);

然后您运行查询

SELECT * FROM t2 WHERE y+x=22;

那么索引将不会被使用，因为 CREATE INDEX 语句上的表达式 (x+y) 与它在查询中出现的表达式 (y+x) 不相同。这两个表达式在数学上可能是等价的，但 SQLite 查询规划器坚持要求它们相同，而不仅仅是等价。考虑这样重写查询

SELECT * FROM t2 WHERE x+y=22;

此第二个查询可能会使用索引，因为现在 WHERE 子句中的表达式 (x+y) 与索引中的表达式完全匹配。

2. 限制

对出现在 CREATE INDEX 语句中的表达式有一些合理的限制

CREATE INDEX 语句中的表达式只能引用要索引的表的列，不能引用其他表的列。
CREATE INDEX 语句中的表达式可以包含函数调用，但只能调用其输出始终由其输入参数完全决定的函数（又称：确定性函数）。显然，像 random() 这样的函数在索引中不会很好地工作。但诸如 sqlite_version() 之类的函数，尽管它们在任何一个数据库连接中都是常量，但在底层数据库文件的整个生命周期中却不是常量，因此不能在 CREATE INDEX 语句中使用。
请注意，应用程序定义的 SQL 函数默认情况下被认为是非确定性的，不能在 CREATE INDEX 语句中使用，除非在注册函数时使用 SQLITE_DETERMINISTIC 标志。
CREATE INDEX 语句中的表达式不能使用子查询。
表达式只能在 CREATE INDEX 语句中使用，不能在 UNIQUE 或 PRIMARY KEY 约束中使用，也不能在 CREATE TABLE 语句中使用。

3. 兼容性

索引表达式功能是在版本 3.9.0（2015-10-14）中添加到 SQLite 的。使用索引表达式的数据库将无法被早期版本的 SQLite 使用。

本页上次修改时间为 2023-02-11 20:57:33 UTC