SQLite 表达式索引的概念和作用_sqlite 新增references
赞
踩
文章目录
使用表达式索引优化查询性能
使用表达式索引实现业务约束
表达式索引限制
SQLite 版本支持
总结
表达式索引（Indexes On Expressions）是指基于某个表达式或者函数的值创建的索引，而不是基于表中字段创建的索引。表达式索引也称为函数索引（Function-Based Indexes）。表达式索引可以提高特定情况下的查询性能，或者实现业务约束；因此，本文给大家介绍一下如何使用 SQLite 中的表达式索引。
使用表达式索引优化查询性能
例如，以下是一个跟踪帐户金额变化的表：
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
);
account_change 表中的每行数据都记录了一个帐户的存款或取款，存款交易的amt 为正，取款交易的 amt 为负。通常我们需要对指定帐户的交易记录进行查询。例如，以下查询用于检索帐户 123 金额大于等于 10000 的所有交易流水：
SELECT *
FROM account_change
WHERE acct_no=123
AND abs(amt)>=10000;
以下查询返回了帐户 123 的所有交易流水，并且按照金额从大到小排序显示：
SELECT *
FROM account_change
WHERE acct_no=123
ORDER BY abs(amt) DESC;
如果没有索引，以上两个查询会随着数据量的增加越来越慢；因此，一般会基于 acct_no 字段创建一个索引。不过，利用表达式索引可以进一步提高查询的性能。例如，我们可以创建以下多列索引：
CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));
表达式的创建和普通索引类似，索引 acctchng_magnitude 基于帐户编号 acct_no 和交易金额的绝对值，abs 是一个函数。该索引可以提高以上两个查询的性能，我们可以查看语句的执行计划：
EXPLAIN QUERY PLAN
SELECT *
FROM account_change
WHERE acct_no=123
AND abs(amt)>=10000;
id|parent|notused|detail |
--|------|-------|-----------------------------------------------------------------------------------|
3| 0| 0|SEARCH TABLE account_change USING INDEX acctchng_magnitude (acct_no=? AND <expr>>?)|
如果查询的 WHERE 子句和 ORDER BY 中的表达式和索引表达式完全相同时，SQLite 查询计划器可以使用索引进行优化。查询计划器不会执行任何算术运算，例如以下表和索引：
CREATE TABLE t2(x,y,z);
CREATE INDEX t2xy ON t2(x+y);
对于以下查询语句：
EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE y+x=22;
id|parent|notused|detail |
--|------|-------|-------------|
2| 0| 0|SCAN TABLE t2|
SQLite 没有使用索引，因为查询条件中的表达式（y+x）和 CREATE INDEX 语句中的表达式（x+y）写法不同。虽然这两个表达式在数学上等价，但是 SQLite 无法执行这种算术转换。因此，我们需要将查询改写如下：
EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE x+y=22;
id|parent|notused|detail |
--|------|-------|-------------------------------------------|
3| 0| 0|SEARCH TABLE t2 USING INDEX t2xy (<expr>=?)|
