11.8. 部分索引 · PostgreSQL 中文文檔 9.3

# 11.8\. 部分索引 _部分索引_是建立在一個表的子集上的索引；該子集是由一個條件表達式定義的(叫做部分索引的_謂詞_)。該索引只包含表中那些滿足這個謂詞的行。部分索引是一個特殊的特性，但是在某些場合很有用。部分索引的主要動機是為了避免對普通數值(大量重復的數值)建立索引。因為在普通數值上的查詢就算使用索引也沒什么好處，那么還不如從索引中剔除這些大量重復的行。這樣可以減小索引尺寸，提高那些真正使用索引的查詢的速度。同時它也能提高更新操作的速度，因為不是所有情況都需要更新索引。[Example 11-1](#calibre_link-960) 顯示了一個潛在的這方面應用的例子。 **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](#calibre_link-961)里顯示，它把不感興趣的數值排除在索引之外。這個結果有與上面列出的同樣的優點，但是它完全拒絕了通過索引訪問"不感興趣"的數值，即使索引掃描可能對那些數據也有利。顯然，為這種情況設置部分索引需要非常仔細并且需要大量試驗。 **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](#calibre_link-961)還說明了建了索引的字段和謂詞中的字段不必相配。 PostgreSQL支持帶任意謂詞的部分索引，只要只涉及被索引表的字段就行。不過，我們要記住的是謂詞必須和那些希望從該索引中獲益的查詢條件相匹配。準確說，只有在系統能夠識別出該查詢的`WHERE`條件在數學上蘊涵了該索引的謂詞時，這個部分索引才能用于該查詢。 PostgreSQL還沒有智能到可以完全識別那些形式不同但數學上相等的謂詞。做到這樣不僅非常困難，而且在實際使用中也可能非常慢。系統可以識別簡單的不相等蘊涵，比如"x < 1"蘊涵"x < 2"；否則，謂詞條件必須準確匹配查詢的 `WHERE`條件，不然系統將無法識別該索引是可用的。匹配發生在查詢規劃期間，而不是運行期間。因此，參數化的查詢子句必定不會使用部分索引。例如，一個預先寫好的、帶有參數的查詢可能指定了"x < ?"，它不可能對所有可能的參數值都蘊涵"x < 2"。部分索引的第三種用途是禁止在查詢中使用索引。如[Example 11-3](#calibre_link-962)所示，這里的概念是在表的子集里創建唯一索引。這樣就強制在滿足謂詞的行中保持唯一性，而并不約束那些不需要唯一的行。 **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的索引是如何運作的。在大多數情況下，部分索引對普通索引的優勢并不太明顯。更多有關部分索引的信息可以在[](#calibre_link-963) [部分索引實例](http://db.cs.berkeley.edu/papers/ERL-M89-17.pdf) , [_POSTGRES中部分索引:研究計劃_](#calibre_link-964), 和 [_Generalized Partial Indexes_ ](#calibre_link-965)[(cached version)](http://citeseer.ist.psu.edu/seshadri95generalized.html) 獲得。