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 查询计划器可以使用索引进行优化。查询计划器不会执行任何算术运算，例如以下表和索引：

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>=?)|

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