小巧。快速。可靠。
任選三項。
部分索引
► 目錄

1. 簡介

部分索引是針對資料表子集列的索引。

在一般索引中，資料表中的每一列在索引中只有一個項目。在部分索引中，資料表中只有部分子集列有對應的索引項目。例如，部分索引可能會省略索引列為 NULL 的項目。明智地使用部分索引，可以縮小資料庫檔案大小，並改善查詢和寫入效能。

2. 建立部分索引

在一般 CREATE INDEX 語句的結尾加上 WHERE 子句，即可建立部分索引。

create-index-stmt

CREATE UNIQUE INDEX IF NOT EXISTS schema-name . index-name ON table-name ( indexed-column ) , WHERE expr

expr

indexed-column

結尾包含 WHERE 子句的任何索引都被視為部分索引。省略 WHERE 子句的索引（或在 CREATE TABLE 語句中由 UNIQUE 或 PRIMARY KEY 約束建立的索引）是一般完整索引。

WHERE 子句之後的表達式可能包含運算子、文字值和正在建立索引的表格中的欄位名稱。WHERE 子句不能包含子查詢、對其他表格的參照、非確定性函數或繫結參數。

只有 WHERE 子句評估為 true 的表格列會包含在索引中。如果 WHERE 子句表達式評估為 NULL 或對表格的某些列為 false，則這些列會從索引中省略。

部分索引的 WHERE 子句中參照的欄位可以是表格中的任何欄位，而不僅僅是碰巧建立索引的欄位。然而，部分索引的 WHERE 子句表達式通常是對正在建立索引的欄位進行的簡單表達式。以下是一個典型的範例

CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;

在上面的範例中，如果大多數的採購訂單沒有「父系」採購訂單，則大多數 parent_po 值會為 NULL。這表示只有採購訂單表格中的一小部分列會建立索引。因此，索引佔用的空間會少很多。而且，對原始採購訂單表格的變更會執行得更快，因為 po_parent 索引只需要針對 parent_po 不為 NULL 的那些例外列進行更新。但索引對於查詢仍然很有用。特別是，如果某人想要知道特定採購訂單「?1」的所有「子系」，則查詢會是 

SELECT po_num FROM purchaseorder WHERE parent_po=?1;

上面的查詢會使用 po_parent 索引來協助找出答案，因為 po_parent 索引包含所有相關列的項目。請注意，由於 po_parent 比完整索引小，因此查詢可能會執行得更快。

2.1. 唯一部分索引

部分索引定義可以包含 UNIQUE 關鍵字。如果包含，則 SQLite 要求在索引中的每個項目都是唯一的。這提供了一種機制，用於強制在表格中某些列的子集中執行唯一性。

例如，假設你有一個大型組織成員的資料庫，其中每個人都被分配到一個特定的「團隊」。每個團隊都有「領導者」，他也是該團隊的成員。該表格可能如下所示

CREATE TABLE person(
  person_id       INTEGER PRIMARY KEY,
  team_id         INTEGER REFERENCES team,
  is_team_leader  BOOLEAN,
  -- other fields elided
);

team_id 欄位無法唯一，因為通常同一個團隊有多人。由於每個團隊通常有多個非領導者，因此無法使 team_id 和 is_team_leader 的組合唯一。執行每個團隊一個領導者的解決方案是在 team_id 上建立唯一索引，但僅限於 is_team_leader 為 true 的那些條目

CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;

巧合的是，同一個索引對於找到特定團隊的團隊領導者也很有用

SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;

3. 使用部分索引的查詢

讓 X 成為部分索引的 WHERE 子句中的表達式，讓 W 成為使用已編制索引的表格的查詢的 WHERE 子句。然後，如果 W⇒X，則允許查詢使用部分索引，其中 ⇒ 運算子（通常發音為「暗示」）是邏輯運算子，等於「X 或非 W」。因此，確定部分索引是否可用於特定查詢會簡化為證明一階邏輯中的定理。

SQLite 沒有一個精密的定理證明器來確定 W⇒X。相反，SQLite 使用兩個簡單的規則來找出 W⇒X 為真的常見情況，並假設所有其他情況都為假。SQLite 使用的規則如下

  1. 如果 W 是 AND 連接的術語，而 X 是 OR 連接的術語，並且 W 的任何術語出現在 X 的術語中，則部分索引可用。

    例如，讓索引為 

    CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6;

    讓查詢為 

    SELECT * FROM tab1 WHERE b=6 AND a=7; -- uses partial index

    然後，索引可被查詢使用，因為「b=6」一詞同時出現在索引定義和查詢中。請記住：索引中的詞彙應以 OR 相連，而查詢中的詞彙應以 AND 相連。

    W 和 X 中的詞彙必須完全相符。SQLite 不會進行代數運算以嘗試讓它們看起來相同。「b=6」一詞不符合「b=3+3」、「b-6=0」或「b BETWEEN 6 AND 6」。只要索引中存在「b=6」，而查詢中存在「6=b」，則「b=6」將符合「6=b」。如果索引中出現「6=b」形式的詞彙，它將永遠不符合任何內容。

  2. 如果 X 中的詞彙為「z IS NOT NULL」形式，而 W 中的詞彙是「z」上的比較運算子（「IS」除外），則這些詞彙相符。

    範例：讓索引為 

    CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL;

    然後，任何在「c」欄位上使用運算子 =、<、>、<=、>=、<>、IN、LIKE 或 GLOB 的查詢都可用於部分索引，因為這些比較運算子僅在「c」不為 NULL 時才為真。因此，下列查詢可以使用部分索引 

    SELECT * FROM tab2 WHERE b=456 AND c<>0;  -- uses partial index

    但下一個查詢無法使用部分索引 

    SELECT * FROM tab2 WHERE b=456;  -- cannot use partial index

    後一個查詢無法使用部分索引，因為表格中可能存在 b=456 且 c 為 NULL 的列。但這些列不會出現在部分索引中。

這兩個規則描述了撰寫本文時（2013-08-01）SQLite 的查詢規劃器的運作方式。而且上述規則將永遠被遵守。然而，SQLite 的未來版本可能會納入更好的定理證明器，它可以找出 W⇒X 為真的其他案例，因此可能會找到更多部分索引有用的實例。

4. 支援版本

自 版本 3.8.0（2013-08-26）以來，SQLite 已支援部分索引。

包含部分索引的資料庫檔案無法由 3.8.0 之前的 SQLite 版本讀取或寫入。但是，由 SQLite 3.8.0 建立的資料庫檔案仍然可以由之前的版本讀取和寫入，只要其架構不包含任何部分索引即可。無法由舊版 SQLite 讀取的資料庫，只要對部分索引執行 DROP INDEX，即可讓其可讀取。