表达式索引

通常，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”）和金额的绝对值上。该索引允许人们对帐户更改的幅度进行有效查询。例如，要列出所有超过 $100.00 的帐号 $xyz 的更改，可以说：

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 语句中使用，不能在 CREATE TABLE语句中的UNIQUE或PRIMARY KEY约束中使用。

3.兼容性

SQLite 3.9.0 (2015-10-14) 版本添加了索引表达式的功能。在表达式上使用索引的数据库将无法被早期版本的 SQLite 使用。