11.7. 部分索引

部分索引(partial index) 是建立在一個資料表的子集上的索引; 該子集是由一個條件資料表達式定義的(叫做部分索引的謂詞)。 該索引只包含資料表中那些滿足這個謂詞的行。

部分索引的主要動機是為了避免對普通數值建立索引, 因為如果一個在一個普通數值(那種占資料表中所有行超過幾個百分點的數值)上的查詢不會使用索引, 那麼就沒有在索引中保存這些(普通)行的必要。 這樣就可以減小索引的尺寸,這樣就可以提高那些真正使用索引的查詢的速度。 同時它也能提高許多資料表更新操作的速度,因為不是所有情況都需要更新索引。 Example 11-1 顯示了一個潛在的這方面應用的例子。

Example 11-1. 設置一個部分索引以排除普通數值

假設您在一個資料庫中儲存 web 伺服器的訪問日誌。 大多數訪問是從您的組織內部的 IP 地址範圍發起的, 但也有一小部分來自其它地方(比如那些透過撥號進行連線的僱員)。 如果您搜索的主要是來自外部訪問的 IP,那麼您可能就不需要對那些對應您的組織的子網的 IP 範圍進行索引。

假設資料表象下面這樣︰

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

要建立符合我們的例子的索引,使用象下面這樣的命令︰

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
    WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

一個可以使用這個索引的典型的查詢像這樣︰

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

一個不能使用這個索引的查詢可以是︰

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

我們透過觀察可以看出,這種類型的部分索引要求普通數值是可以預計的。 如果數值的分佈是固有的(來自應用自身的性質)並且是靜態的(不隨時間而改變), 那做到這一點(跟蹤普通數值)並不困難,但是如果普通數值只是因為一致的資料裝載, 那麼它可能就要花很多維護性工作。

另外一個可能是把那些典型的查詢工作不感興趣的數值排除在 索引之外;這個可能在 Example 11-2 裡顯示。 這個結果有與上面列出的同樣的優點,但是它完全拒絕了透過索引 訪問"不感興趣"的數值,即使索引掃瞄可能對那些資料 也有利。顯然,為這種情況設置部分索引需要非常仔細以及需要大量試驗。

Example 11-2. 設置一個部分索引以排除不感興趣的數值

如果您有一個資料表,包含已付款和未付款的定單,而未付款的定單只佔總資料表的一小部分並且它是經常使用的部分, 那麼您可以透過只在未付款定單上建立一個索引來改善性能。建立索引的命令看起來會像這樣︰

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

可能用到這個索引的查詢看起來像

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

不過,該索引也可以用於那些完全不涉及 order_nr 的查詢,比如,

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

這個查詢不像在 amount 字串上的部分索引那麼有效, 因為系統必須掃瞄整個索引。但是,如果未付款的定單相對較少, 那麼用這個部分索引找出未付款的定單將會更快些。

請注意下面這個查詢無法使用這個索引︰

SELECT * FROM orders WHERE order_nr = 3501;

定單 3501 可能是已付款也可能是未付款。

Example 11-2 還說明了建了索引的字串和在謂詞中使用的字串不必相配。 PostgreSQL 支援帶任意謂詞的部分索引,只要是只涉及被索引資料表的字串就行。 不過,我們要記住的是謂詞必須和那些希望從該索引中獲益的查詢中的條件相匹配。 準確說,只有在系統能夠識別出該查詢的WHERE條件在數學上蘊涵了該索引的謂詞時, 這個部分索引才能用於該查詢。 PostgreSQL 還沒有複雜的理論校定用於識別那些形式不同但數學上相等的謂詞。 (做這樣的理論校定不僅非常困難,而且在實際使用中也可能非常慢。) 系統可以識別簡單的不相等蘊涵,比如 "x < 1" 蘊涵 "x < 2"; 否則,謂詞條件必須準確匹配查詢的WHERE條件,要不然系統將無法識別該索引是可用的。

部分索引的第三種可能用途完全不要求索引在查詢中得到使用。 這裡的概念是在一個資料表的一個子集裡建立一個唯一索引, 如 Example 11-3 裡描述。 這樣就強制在滿足謂詞的行中的唯一性,而不用約束那些不需要唯一的行。

Example 11-3. 設置一個部分唯一索引

假設我們有一個描述測試輸出的資料表。我們希望確保在一定的目標和課題的組合中只有一個"成功"記錄, 但是可以有任意數量的"不成功"記錄。下面是實現方法︰

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

如果只有少數成功測試而有很多不成功測試,那麼這是一種非常有效的實現方法。

最後,部分索引也可以用於覆蓋系統選擇的查詢規劃。 可能會出現這樣的情況︰如果資料集的分佈是比較特定的形狀, 那麼會導致系統在不該使用索引的時候使用它。在這種情況下, 我們可以把索引設置為在違反(規律)的查詢中不可用。 通常 PostgreSQL 對索引的使用是會做合理的選擇的(比如,它在檢索普通數值的時候避免使用它, 因此前面的例子實際上只是節約了索引的尺寸,它並不要求避免索引的使用), 而如果出現了錯誤的規劃選擇那麼請提交一個臭蟲報告。

請記住一件事︰設置一個部分索引資料表示您至少和查詢規劃器知道的一樣多, 特別是您知道什麼場合裡索引是有效的。要形成這些只是要求富有經驗並且理解 PostgreSQL 裡的索引是如何運做的。 在大多數情況下,部分索引對普通索引的優勢並不太明顯。

更多有關部分索引的訊息可以在 部分索引的實例Partial indexing in POSTGRES: research project,和 Generalized Partial Indexes 獲得。