### [PostgreSQL學習手冊(索引)](http://www.cnblogs.com/stephen-liu74/archive/2012/05/09/2298182.html) Posted on?2012-05-09 08:40?[Stephen_Liu](http://www.cnblogs.com/stephen-liu74/)?閱讀(1510) 評論(0)?[編輯](http://www.cnblogs.com/stephen-liu74/admin/EditPosts.aspx?postid=2298182)?[收藏](http://www.cnblogs.com/stephen-liu74/archive/2012/05/09/2298182.html#)? **一、索引的類型：** ?? ?PostgreSQL提供了多　　種索引類型：B-Tree、Hash、GiST和GIN，由于它們使用了不同的算法，因此每種索引類型都有其適合的查詢類型，缺省時，CREATE INDEX命令將創建B-Tree索引。 ?? ? ????1. B-Tree: ?? ?CREATE TABLE test1 ( ?? ???? id integer, ?? ???? content varchar ?? ?); **?? ?CREATE INDEX**?test1_id_index?**ON**?test1 (id);???? ?? ?B-Tree索引主要用于等于和范圍查詢，特別是當索引列包含操作符" <、<=、=、>=和>"作為查詢條件時，PostgreSQL的查詢規劃器都會考慮使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查詢中，PostgreSQL也可以使用B-Tree索引。然而對于基于模式匹配操作符的查詢，如LIKE、ILIKE、~和 ~*，僅當模式存在一個常量，且該常量位于模式字符串的開頭時，如col LIKE 'foo%'或col ~ '^foo'，索引才會生效，否則將會執行全表掃描，如：col LIKE '%bar'。? ?? ? ????2. Hash： ?? ?**CREATE INDEX**?name?**ON**?table**?USING hash**?(column); ?? ?散列(Hash)索引只能處理簡單的等于比較。當索引列使用等于操作符進行比較時，查詢規劃器會考慮使用散列索引。 ?? ?這里需要額外說明的是，PostgreSQL散列索引的性能不比B-Tree索引強，但是散列索引的尺寸和構造時間則更差。另外，由于散列索引操作目前沒有記錄WAL日志，因此一旦發生了數據庫崩潰，我們將不得不用REINDEX重建散列索引。 ?? ? ?? ?3. GiST： ?? ?GiST索引不是一種單獨的索引類型，而是一種架構，可以在該架構上實現很多不同的索引策略。從而可以使GiST索引根據不同的索引策略，而使用特定的操作符類型。? ?? ? ?? ?4. GIN： ?? ?GIN索引是反轉索引，它可以處理包含多個鍵的值(比如數組)。與GiST類似，GIN同樣支持用戶定義的索引策略，從而可以使GIN索引根據不同的索引策略，而使用特定的操作符類型。作為示例，PostgreSQL的標準發布中包含了用于一維數組的GIN操作符類型，如：<@、@>、=、&&等。 **二、復合索引：** ?? ?PostgreSQL中的索引可以定義在數據表的多個字段上，如： *?? ?CREATE TABLE test2 (* *?? ??? ?major int,* *?? ??? ?minor int,* *?? ??? ?name varchar* *?? ?}* *?? ?CREATE INDEX test2_mm_idx ON test2 (major, minor);* ?? ?在當前的版本中，只有B-tree、GiST和GIN支持復合索引，其中最多可以聲明32個字段。 ?? ?1. B-Tree類型的復合索引： ?? ?在B-Tree類型的復合索引中，該索引字段的任意子集均可用于查詢條件，不過，只有當復合索引中的第一個索引字段(最左邊)被包含其中時，才可以獲得最高效率。 ?? ? ?? ?2. GiST類型的復合索引： ?? ?在GiST類型的復合索引中，只有當第一個索引字段被包含在查詢條件中時，才能決定該查詢會掃描多少索引數據，而其他索引字段上的條件只是會限制索引返回的條目。假如第一個索引字段上的大多數數據都有相同的鍵值，那么此時應用GiST索引就會比較低效。? ?? ?3. GIN類型的復合索引： ?? ?與B-Tree和GiST索引不同的是，GIN復合索引不會受到查詢條件中使用了哪些索引字段子集的影響，無論是哪種組合，都會得到相同的效率。 ??? 使用復合索引應該謹慎。在大多數情況下，單一字段上的索引就已經足夠了，并且還節約時間和空間。除非表的使用模式非常固定，否則超過三個字段的索引幾乎沒什么用處。? **三、組合多個索引：** ?? ?PostgreSQL可以在查詢時組合多個索引(包括同一索引的多次使用)，來處理單個索引掃描不能實現的場合。與此同時，系統還可以在多個索引掃描之間組成AND和OR的條件。比如，一個類似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查詢，可以被分解成四個獨立的基于x字段索引的掃描，每個掃描使用一個查詢子句，之后再將這些掃描結果OR在一起并生成最終的結果。另外一個例子是，如果我們在x和y上分別存在獨立的索引，那么一個類似WHERE x = 5 AND y = 6的查詢，就會分別基于這兩個字段的索引進行掃描，之后再將各自掃描的結果進行AND操作并生成最終的結果行。 ?? ?為了組合多個索引，系統掃描每個需要的索引，然后在內存里組織一個BITMAP，它將給出索引掃描出的數據在數據表中的物理位置。然后，再根據查詢的需要，把這些位圖進行AND或者OR的操作并得出最終的BITMAP。最后，檢索數據表并返回數據行。表的數據行是按照物理順序進行訪問的，因為這是位圖的布局，這就意味著任何原來的索引的排序都將消失。如果查詢中有ORDER BY子句，那么還將會有一個額外的排序步驟。因為這個原因，以及每個額外的索引掃描都會增加額外的時間，這樣規劃器有時候就會選擇使用簡單的索引掃描，即使有多個索引可用也會如此。 ? ?? ???? **四、唯一索引：** ?? ?目前，只有B-Tree索引可以被聲明為唯一索引。 ?? ?**CREATE UNIQUE INDEX**?name?**ON**?table (column [, ...]); ?? ?如果索引聲明為唯一索引，那么就不允許出現多個索引值相同的行。我們認為NULL值相互間不相等。 ???? **五、表達式索引：** ?? ?表達式索引主要用于在查詢條件中存在基于某個字段的函數或表達式的結果與其他值進行比較的情況，如： ?? ?*SELECT * FROM test1 WHERE lower(col1) = 'value';* ?? ?此時，如果我們僅僅是在col1字段上建立索引，那么該查詢在執行時一定不會使用該索引，而是直接進行全表掃描。如果該表的數據量較大，那么執行該查詢也將會需要很長時間。解決該問題的辦法非常簡單，在test1表上建立基于col1字段的表達式索引，如： ?? ?*CREATE INDEX test1_lower_col1_idx ON test1?**(lower(col1))**;* ?? ?如果我們把該索引聲明為UNIQUE，那么它會禁止創建那種col1數值只是大小寫有區別的數據行，以及col1數值完全相同的數據行。因此，在表達式上的索引可以用于強制那些無法定義為簡單唯一約束的約束。現在讓我們再看一個應用表達式索引的例子。 ?? ?*SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';* ?? ?和上面的例子一樣，盡管我們可能會為first_name和last_name分別創建獨立索引，或者是基于這兩個字段的復合索引，在執行該查詢語句時，這些索引均不會被使用，該查詢能夠使用的索引只有我們下面創建的表達式索引。 ?? ?*CREATE INDEX people_names ON people?**((first_name || ' ' || last_name))**;* ?? ?CREATE INDEX命令的語法通常要求在索引表達式周圍書寫圓括弧，就像我們在第二個例子里顯示的那樣。如果表達式只是一個函數調用，那么可以省略，就像我們在第一個例子里顯示的那樣。 ?? ?從索引維護的角度來看，索引表達式要相對低效一些，因為在插入數據或者更新數據的時候，都必須為該行計算表達式的結果，并將該結果直接存儲到索引里。然而在查詢時，PostgreSQL就會把它們看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于簡單索引的查詢。通常而言，我們只是應該在檢索速度比插入和更新速度更重要的場景下使用表達式索引。? ?? ? **六、部分索引：** ?? ?部分索引**(partial index)**是建立在一個表的子集上的索引，而該子集是由一個條件表達式定義的(叫做部分索引的謂詞)。該索引只包含表中那些滿足這個謂詞的行。 ??? 由于不是在所有的情況下都需要更新索引，因此部分索引會提高數據插入和數據更新的效率。然而又因為部分索引比普通索引要小，因此可以更好的提高確實需要索引部分的查詢效率。見以下三個示例： ?? ?1. 索引字段和謂詞條件字段一致： ***?? ?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';* ?? ?2. 索引字段和謂詞條件字段不一致： ?? ?PostgreSQL支持帶任意謂詞的部分索引，唯一的約束是謂詞的字段也要來自于同樣的數據表。注意，如果你希望你的查詢語句能夠用到部分索引，那么就要求該查詢語句的條件部分必須和部分索引的謂詞完全匹配。 準確說，只有在PostgreSQL能夠識別出該查詢的WHERE條件在數學上涵蓋了該索引的謂詞時，這個部分索引才能被用于該查詢。 ??***??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;* ?? ?那么對于如下查詢呢？ ?? ?*SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;* ?? ?這個查詢將不像上面那個查詢這么高效，畢竟查詢的條件語句中沒有用到索引字段，然而查詢條件"billed is not true"卻和部分索引的謂詞完全匹配，因此PostgreSQL將掃描整個索引。這樣只有在索引數據相對較少的情況下，該查詢才能更有效一些。 ?? ?下面的查詢將不會用到部分索引。 ?? ?*SELECT * FROM orders WHERE order_nr = 3501;* ?? ? ?? ?3. 數據表子集的唯一性約束： *?? ?CREATE TABLE tests (* *?? ???? subject text,* *?? ???? target text,* *?? ???? success boolean,* *?? ???? ...* *?? ?);* ***?? ?CREATE UNIQUE INDEX**?tests_success_constraint?**ON**?tests(subject, target)?**WHERE**?success;* ?? ?該部分索引將只會對success字段值為true的數據進行唯一性約束。在實際的應用中，如果成功的數據較少，而不成功的數據較多時，該實現方法將會非常高效。 ????? **七、檢查索引的使用：** ?? ?見以下四條建議： ?? ?1. 總是先運行ANALYZE。 ?? ?該命令將會收集表中數值分布狀況的統計。在估算一個查詢返回的行數時需要這個信息，而規劃器則需要這個行數以便給每個可能的查詢規劃賦予真實的開銷值。如果缺乏任何真實的統計信息，那么就會使用一些缺省數值，這樣肯定是不準確的。因此，如果還沒有運行ANALYZE就檢查一個索引的使用狀況，那將會是一次失敗的檢查。? ?? ?2. 使用真實的數據做實驗。 ?? ?用測試數據填充數據表，那么該表的索引將只會基于測試數據來評估該如何使用索引，而不是對所有的數據都如此使用。比如從100000行中選1000行，規劃器可能會考慮使用索引，那么如果從100行中選1行就很難說也會使用索引了。因為100行的數據很可能是存儲在一個磁盤頁面中，然而沒有任何查詢規劃能比通過順序訪問一個磁盤頁面更加高效了。與此同時，在模擬測試數據時也要注意，如果這些數據是非常相似的數據、完全隨機的數據，或按照排序順序插入的數據，都會令統計信息偏離實際數據應該具有的特征。?? ? ?? ?3. 如果索引沒有得到使用，那么在測試中強制它的使用也許會有些價值。有一些運行時參數可以關閉各種各樣的查詢規劃。 ?? ?4. 強制使用索引用法將會導致兩種可能：一是系統選擇是正確的，使用索引實際上并不合適，二是查詢計劃的開銷計算并不能反映現實情況。這樣你就應該對使用和不使用索引的查詢進行計時，這個時候EXPLAIN ANALYZE命令就很有用了。 分類:?[PostgreSQL](http://www.cnblogs.com/stephen-liu74/category/343171.html)