个人中心
退出
SQLite 表达式索引的概念和作用
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
使用表达式索引优化查询性能
使用表达式索引实现业务约束
表达式索引限制
SQLite 版本支持
总结
大家好，我是只谈技术不剪发的 Tony 老师。
表达式索引（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>=?)|
以上查询条件中的表达式和索引表达式完全相同，因此可以使用索引。
使用表达式索引实现业务约束
除了可以优化查询性能之外，表达式索引还可以实现某些业务约束。例如，用户注册时的电子邮箱地址通常不区分大小写，同时要求每个邮箱只能注册一次，也是就存在唯一性约束。我们创建以下示例表：
CREATE TABLE users(id integer PRIMARY KEY, email varchar(100) not null, password text not null);
如果直接基于 email 字段创建一个唯一约束，无法真正实现上述业务需求，因为大小写不同的邮件地址会被看做不同的数据。为此，我们可以创建一个表达式索引：
CREATE UNIQUE INDEX users_email ON users(lower(email));
索引表达式中的 lower 函数将 email 统一转换为小写，然后创建唯一索引。我们来验证一下数据插入：
INSERT INTO users(email, password) VALUES ('abc@test.com', 'e10adc3949ba59abbe56e057f20f883e');
INSERT INTO users(email, password) VALUES ('ABC@test.com', 'c33367701511b4f6020ec61ded352059');
SQL 错误 [19]: [SQLITE_CONSTRAINT] Abort due to constraint violation (UNIQUE constraint failed: index 'users_email')
当然，我们也可以使用 upper 函数替代 lower 函数实现相同的功能。
另外，当我们查询用户的时候，也可以使用相同的表达式索引实现优化。例如：
EXPLAIN QUERY PLAN
SELECT *
FROM users
WHERE lower(email) = lower('ABC@test.com');
id|parent|notused|detail |
--|------|-------|-----------------------------------------------------|
3| 0| 0|SEARCH TABLE users USING INDEX users_email (<expr>=?)|
表达式索引限制
SQLite 表达式索引存在以下合理的限制：
CREATE INDEX 语句中的表达式只能引用当前表中的字段，而不能引用其他表中的字段。
CREATE INDEX 语句中的表达式可以包含函数调用，但是只能使用确定性函数，也就是返回结果完全由输入参数决定的函数。显然，类似于 random() 的函数不是确定性函数，无法用于索引。另外，类似于 sqlite_version() 的函数虽然对于任何数据库连接都返回常量值，但是在整个数据库文件的生命周期内可能会返回不同的值，因此也不能用于索引。
默认情况下，应用程序定义的 SQL 函数不确定性函数，无法用于表达式索引，除非注册函数时指定了 SQLITE_DETERMINISTIC 标志。
CREATE INDEX 语句中的表达式不能使用子查询。
表达式只能用于 CREATE INDEX 语句，不能用于 CREATE TABLE 语句中的 UNIQUE 或者 PRIMARY KEY 约束。
SQLite 版本支持
SQLite 3.9.0（2015-10-14）开始支持表达式索引，包含表达式索引的数据库无法在更早版本的 SQLite 中使用。
总结
利用表达式索引针对查询条件或者排序操作中使用了表达式或者函数的 SQL 语句进行优化，而不需要修改应用程序中的业务逻辑；另外，表达式索引也可以实现业务约束，例如不区分大小写的电子邮件地址唯一约束。本文介绍了 SQLite 中的表达式索引的使用方法和相关限制。
你知道表达式索引或者函数索引还有哪些应用案例吗？欢迎留言讨论！
分类导航