SQLite 表达式索引的概念和作用

文章目录

        使用表达式索引优化查询性能
        使用表达式索引实现业务约束
        表达式索引限制
        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 查询计划器可以使用索引进行优化。查询计划器不会执行任何算术运算，例如以下表和索引：

更多请见：http://www.mark-to-win.com/tutorial/51557.html