個人中心
退出
SQLite 表達式索引的概念和作用
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數據庫管理與開發(fā)經驗，目前在一家全球性的金融公司從事數據庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
使用表達式索引優(yōu)化查詢性能
使用表達式索引實現業(yè)務約束
表達式索引限制
SQLite 版本支持
總結
大家好，我是只談技術不剪發(fā)的 Tony 老師。
表達式索引（Indexes On Expressions）是指基于某個表達式或者函數的值創(chuàng)建的索引，而不是基于表中字段創(chuàng)建的索引。表達式索引也稱為函數索引（Function-Based Indexes）。表達式索引可以提高特定情況下的查詢性能，或者實現業(yè)務約束；因此，本文給大家介紹一下如何使用 SQLite 中的表達式索引。
如果覺得文章有用，歡迎評論??、點贊??、推薦??
使用表達式索引優(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 表中的每行數據都記錄了一個帳戶的存款或取款，存款交易的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 字段創(chuàng)建一個索引。不過，利用表達式索引可以進一步提高查詢的性能。例如，我們可以創(chuàng)建以下多列索引：
CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));
表達式的創(chuàng)建和普通索引類似，索引 acctchng_magnitude 基于帳戶編號 acct_no 和交易金額的絕對值，abs 是一個函數。該索引可以提高以上兩個查詢的性能，我們可以查看語句的執(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 中的表達式和索引表達式完全相同時，SQLite 查詢計劃器可以使用索引進行優(yōu)化。查詢計劃器不會執(zhí)行任何算術運算，例如以下表和索引：
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 無法執(zhí)行這種算術轉換。因此，我們需要將查詢改寫如下：
EXPLAIN QUERY PLAN
SELECT * FROM t2 WHERE x+y=22;
id|parent|notused|detail |
--|------|-------|-------------------------------------------|
3| 0| 0|SEARCH TABLE t2 USING INDEX t2xy (<expr>=?)|
以上查詢條件中的表達式和索引表達式完全相同，因此可以使用索引。
使用表達式索引實現業(yè)務約束
除了可以優(yōu)化查詢性能之外，表達式索引還可以實現某些業(yè)務約束。例如，用戶注冊時的電子郵箱地址通常不區(qū)分大小寫，同時要求每個郵箱只能注冊一次，也是就存在唯一性約束。我們創(chuàng)建以下示例表：
CREATE TABLE users(id integer PRIMARY KEY, email varchar(100) not null, password text not null);
如果直接基于 email 字段創(chuàng)建一個唯一約束，無法真正實現上述業(yè)務需求，因為大小寫不同的郵件地址會被看做不同的數據。為此，我們可以創(chuàng)建一個表達式索引：
CREATE UNIQUE INDEX users_email ON users(lower(email));
索引表達式中的 lower 函數將 email 統(tǒng)一轉換為小寫，然后創(chuàng)建唯一索引。我們來驗證一下數據插入：
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 函數實現相同的功能。
另外，當我們查詢用戶的時候，也可以使用相同的表達式索引實現優(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>=?)|
表達式索引限制
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 語句進行優(yōu)化，而不需要修改應用程序中的業(yè)務邏輯；另外，表達式索引也可以實現業(yè)務約束，例如不區(qū)分大小寫的電子郵件地址唯一約束。本文介紹了 SQLite 中的表達式索引的使用方法和相關限制。
你知道表達式索引或者函數索引還有哪些應用案例嗎？歡迎留言討論！
分類導航