表達式索引

通常，SQL 索引會參考表格的欄位。但索引也可以在涉及表格欄位的表達式上形成。

舉例來說，考慮以下追蹤各種「帳戶」美元金額變化的表格

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
);
CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));

account_change 表格中的每個項目都會記錄存款或提款到帳戶。存款有正的「amt」，提款有負的「amt」。

acctchng_magnitude 索引在帳戶號碼 («acct_no») 和金額的絕對值上。此索引允許對帳戶變化的幅度進行有效率的查詢。例如，要列出所有變動金額超過 $100.00 的帳戶號碼 $xyz，可以說

SELECT * FROM account_change WHERE acct_no=$xyz AND abs(amt)>=10000;

或者，要依據遞減的幅度列出所有變更到特定帳戶 ($xyz) 的變化，可以寫成

SELECT * FROM account_change WHERE acct_no=$xyz
 ORDER BY abs(amt) DESC;

上述兩個範例查詢在沒有 acctchng_magnitude 索引的情況下也能正常運作。acctchng_magnitude 索引僅有助於查詢執行得更快，特別是在每個帳戶在表格中都有許多項目的資料庫上。

1. 如何使用表達式索引

使用 CREATE INDEX 陳述式建立新的索引在一個或多個表達式上，就像建立欄位索引一樣。唯一的不同是表達式被列為要編入索引的元素，而不是欄位名稱。

SQLite 查詢規劃器會考慮在 WHERE 子句或查詢的 ORDER BY 子句中出現索引表達式時使用索引，完全依照在 CREATE INDEX 陳述式中所寫的方式。查詢規劃器不執行代數。為了將 WHERE 子句約束和 ORDER BY 項目與索引相符，SQLite 要求表達式相同，除了空白變更等細微的語法差異。因此，如果您有

CREATE TABLE t2(x,y,z);
CREATE INDEX t2xy ON t2(x+y);

然後執行查詢

SELECT * FROM t2 WHERE y+x=22;

那麼索引不會被使用，因為 CREATE INDEX 陳述式 (x+y) 中的表達式與查詢中出現的表達式 (y+x) 不同。這兩個表達式在數學上可能相等，但 SQLite 查詢規劃器堅持它們必須相同，而不能只是相等。考慮這樣重寫查詢

SELECT * FROM t2 WHERE x+y=22;

這個第二個查詢可能會使用索引，因為現在 WHERE 子句 (x+y) 中的表達式與索引中的表達式完全相符。

2. 限制

出現在 CREATE INDEX 陳述式中的表達式有一些合理的限制

CREATE INDEX 陳述式中的表達式只能參照正在編製索引的表格的欄位，不能參照其他表格中的欄位。
CREATE INDEX 陳述式中的表達式可以包含函數呼叫，但只能呼叫輸出始終完全由其輸入參數決定的函數（又稱：確定性函數）。顯然，像 random() 這樣的函數在索引中無法正常運作。但像 sqlite_version() 這樣的函數，雖然在任何一個資料庫連線中都是常數，但並非在底層資料庫檔案的生命週期中都是常數，因此不能用於 CREATE INDEX 陳述式。
請注意，應用程式定義的 SQL 函數預設會被視為非確定性的，除非在註冊函數時使用 SQLITE_DETERMINISTIC 標記，否則不能用於 CREATE INDEX 陳述式。
CREATE INDEX 陳述式中的運算式不得使用子查詢。
運算式只能用在 CREATE INDEX 陳述式中，不能用在 UNIQUE 或 PRIMARY KEY 限制條件中，這些限制條件在 CREATE TABLE 陳述式中。

3. 相容性

在 SQLite 中索引運算式的功能已新增到版本 3.9.0（2015-10-14）。使用運算式索引的資料庫將無法由較早版本的 SQLite 使用。

此頁面最後修改於 2023-02-11 20:57:33 UTC