Indexes On Expressions

通常，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语句中出现的表达式有一定的合理限制：

1. CREATE INDEX语句中的表达式只能引用被索引表的列，而不能引用其他表中的列。

1. CREATE INDEX语句中的表达式可能包含函数调用，但仅限于其输出总是完全由其输入参数（又名：确定性函数）确定的函数。很显然，像random（）这样的函数在索引中不能很好地工作。但是，像sqlite_version（）这样的函数，尽管它们在任何一个数据库连接中都是不变的，但它们在底层数据库文件的整个生命周期中并不是不变的，因此可能不会用在CREATE INDEX语句中。

请注意，应用程序定义的SQL函数默认情况下被视为非确定性的，并且可能不会在CREATE INDEX语句中使用，除非在函数注册时使用SQLITE_DETERMINISTIC标志。

1. CREATE INDEX语句中的表达式可能不使用子查询。

1. 表达式只能在CREATE INDEX语句中使用，而不能在CREATE TABLE语句中的UNIQUE或PRIMARY KEY约束中使用。

3.兼容性

SQLite 版本3.9.0（2015-10-14）添加了对表达式进行索引的功能。对表达式使用索引的数据库不能用于早期版本的SQLite。

 SQLite在公共领域。