SQLite 表達(dá)式索引的概念和作用

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目錄

        使用表達(dá)式索引優(yōu)化查詢性能
        使用表達(dá)式索引實(shí)現(xiàn)業(yè)務(wù)約束
        表達(dá)式索引限制
        SQLite 版本支持
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。

表達(dá)式索引（Indexes On Expressions）是指基于某個表達(dá)式或者函數(shù)的值創(chuàng)建的索引，而不是基于表中字段創(chuàng)建的索引。表達(dá)式索引也稱為函數(shù)索引（Function-Based Indexes）。表達(dá)式索引可以提高特定情況下的查詢性能，或者實(shí)現(xiàn)業(yè)務(wù)約束；因此，本文給大家介紹一下如何使用 SQLite 中的表達(dá)式索引。

如果覺得文章有用，歡迎評論??、點(diǎn)贊??、推薦??
使用表達(dá)式索引優(yōu)化查詢性能

例如，以下是一個跟蹤帳戶金額變化的表：

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 表中的每行數(shù)據(jù)都記錄了一個帳戶的存款或取款，存款交易的amt 為正，取款交易的 amt 為負(fù)。通常我們需要對指定帳戶的交易記錄進(jìn)行查詢。例如，以下查詢用于檢索帳戶 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;



如果沒有索引，以上兩個查詢會隨著數(shù)據(jù)量的增加越來越慢；因此，一般會基于 acct_no 字段創(chuàng)建一個索引。不過，利用表達(dá)式索引可以進(jìn)一步提高查詢的性能。例如，我們可以創(chuàng)建以下多列索引：

CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));



表達(dá)式的創(chuàng)建和普通索引類似，索引 acctchng_magnitude 基于帳戶編號 acct_no 和交易金額的絕對值，abs 是一個函數(shù)。該索引可以提高以上兩個查詢的性能，我們可以查看語句的執(zhí)行計劃：

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 中的表達(dá)式和索引表達(dá)式完全相同時，SQLite 查詢計劃器可以使用索引進(jìn)行優(yōu)化。查詢計劃器不會執(zhí)行任何算術(shù)運(yùn)算，例如以下表和索引：

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àn)椴樵儣l件中的表達(dá)式（y+x）和 CREATE INDEX 語句中的表達(dá)式（x+y）寫法不同。雖然這兩個表達(dá)式在數(shù)學(xué)上等價，但是 SQLite 無法執(zhí)行這種算術(shù)轉(zhuǎn)換。因此，我們需要將查詢改寫如下：

EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE x+y=22;
id|parent|notused|detail                                     |
--|------|-------|-------------------------------------------|
 3|     0|      0|SEARCH TABLE t2 USING INDEX t2xy (<expr>=?)|



以上查詢條件中的表達(dá)式和索引表達(dá)式完全相同，因此可以使用索引。
使用表達(dá)式索引實(shí)現(xiàn)業(yè)務(wù)約束

除了可以優(yōu)化查詢性能之外，表達(dá)式索引還可以實(shí)現(xiàn)某些業(yè)務(wù)約束。例如，用戶注冊時的電子郵箱地址通常不區(qū)分大小寫，同時要求每個郵箱只能注冊一次，也是就存在唯一性約束。我們創(chuàng)建以下示例表：

CREATE TABLE users(id integer PRIMARY KEY, email varchar(100) not null, password text not null);


如果直接基于 email 字段創(chuàng)建一個唯一約束，無法真正實(shí)現(xiàn)上述業(yè)務(wù)需求，因?yàn)榇笮懖煌泥]件地址會被看做不同的數(shù)據(jù)。為此，我們可以創(chuàng)建一個表達(dá)式索引：

CREATE UNIQUE INDEX users_email ON users(lower(email));



索引表達(dá)式中的 lower 函數(shù)將 email 統(tǒng)一轉(zhuǎn)換為小寫，然后創(chuàng)建唯一索引。我們來驗(yàn)證一下數(shù)據(jù)插入：

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')



當(dāng)然，我們也可以使用 upper 函數(shù)替代 lower 函數(shù)實(shí)現(xiàn)相同的功能。

另外，當(dāng)我們查詢用戶的時候，也可以使用相同的表達(dá)式索引實(shí)現(xiàn)優(yōu)化。例如：

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



表達(dá)式索引限制

SQLite 表達(dá)式索引存在以下合理的限制：

    CREATE INDEX 語句中的表達(dá)式只能引用當(dāng)前表中的字段，而不能引用其他表中的字段。

    CREATE INDEX 語句中的表達(dá)式可以包含函數(shù)調(diào)用，但是只能使用確定性函數(shù)，也就是返回結(jié)果完全由輸入?yún)?shù)決定的函數(shù)。顯然，類似于 random() 的函數(shù)不是確定性函數(shù)，無法用于索引。另外，類似于 sqlite_version() 的函數(shù)雖然對于任何數(shù)據(jù)庫連接都返回常量值，但是在整個數(shù)據(jù)庫文件的生命周期內(nèi)可能會返回不同的值，因此也不能用于索引。

    默認(rèn)情況下，應(yīng)用程序定義的 SQL 函數(shù)不確定性函數(shù)，無法用于表達(dá)式索引，除非注冊函數(shù)時指定了 SQLITE_DETERMINISTIC 標(biāo)志。

    CREATE INDEX 語句中的表達(dá)式不能使用子查詢。

    表達(dá)式只能用于 CREATE INDEX 語句，不能用于 CREATE TABLE 語句中的 UNIQUE 或者 PRIMARY KEY 約束。

SQLite 版本支持

SQLite 3.9.0（2015-10-14）開始支持表達(dá)式索引，包含表達(dá)式索引的數(shù)據(jù)庫無法在更早版本的 SQLite 中使用。
總結(jié)

利用表達(dá)式索引針對查詢條件或者排序操作中使用了表達(dá)式或者函數(shù)的 SQL 語句進(jìn)行優(yōu)化，而不需要修改應(yīng)用程序中的業(yè)務(wù)邏輯；另外，表達(dá)式索引也可以實(shí)現(xiàn)業(yè)務(wù)約束，例如不區(qū)分大小寫的電子郵件地址唯一約束。本文介紹了 SQLite 中的表達(dá)式索引的使用方法和相關(guān)限制。

你知道表達(dá)式索引或者函數(shù)索引還有哪些應(yīng)用案例嗎？歡迎留言討論！