PostgreSQL中文學習手冊(索引)

一、索引的類型：

    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複合索引不會受到查詢條件中使用了哪些索引欄位子集的影響，無論是哪種組合，都會得到相同的效率。

    使用複合索引應該謹慎。在大多數情況下，單一欄位上的索引就已經足夠了，並且還節約時間和空間。除非表的使用模式非常固定，否則超過三個欄位的索引幾乎沒什麼用處。

1 2 3 下一頁