PostgreSQL
上一頁		下一頁

第七章. 索引和鍵字

內容
鍵字
部分索引

索引主要用來提高資料庫性能。它們應該定義在那些常用於做重複查詢的資格條件的表的列（或者表的字段）上。對索引的不當使用會導致性能的下降，因為更新和插入時間在索引出現時都增加了。

可以定義兩種型態的索引：

一是 值索引（value index），索引的鍵字域聲明為字段名﹔一個字段可能還有一個關聯的運算符號表。一個運算符號表用於聲明該運算符號被用於某一指定索引。例如，對四字節整數的 btree 索引會使用int4_ops 表﹔這個運算符號表包括用於四字節整數的比較函數。預設運算符號表是用於該字段型態的合適的運算符號表。
一是 函數索引（functional index），它是定義在一個用戶定義函數的結果上的，這個用戶定義函數對某一個表的一個或多個字段進行操作。這些函數索引可以用於獲得對那些基於運算符號的數據的快速訪問，這些數據通常需要做一些轉換，轉換成基本數據型態來使用。

Postgres 為從索引提供 btree，rtree 和 hash（散列）訪問模式。btree 訪問模式是一個 Lehman-Yao 高並發 btrees 的實現。rtree 訪問模式用 Guttman 的二分算法實現了標準的 rtrees。hash（散列）訪問模式是 Litwin 的線性散列的一個實現。我們單獨的列出這些所用的算法是要表明所有這些訪問模式都是完全動態的並且不必進行周期性的最佳化（例如，像靜態散列算法常見的那樣）。

當一個索引了的字段涉及到使用： <，<=，=，>=，> 之一進行比較時，Postgres 的查詢最佳化器將考慮在掃描中使用 btree 索引。

Postgres 裡兩種 box （方形）表都支援對 box 數據型態的索引。兩者的區別是 bigbox_ops 把方形的坐標按比例縮小，以避免在對非常大的浮點數坐標做乘法，加法和減法時出現浮點例外。如果你的方形所在的範圍的大小是 20,000 單位的平面或更大，你應該用 bigbox_ops。poly_ops 運算符號表支援 polygon 型態數據的 rtree 索引。

當一個索引了的字段涉及到使用： <<，&<，&>，>>，@，~=，&& 之一進行比較時，Postgres 的查詢最佳化器將考慮在掃描中使用 rtree 索引。

當一個索引了的字段涉及到使用 = 進行比較時，Postgres 的查詢最佳化器將考慮在掃描中使用散列（hash）索引。

目前，只有 BTREE 訪問模式支援多字段索引。最多可以聲明 7 的關鍵字。

使用 DROP INDEX 刪除一個索引。

int24_ops 運算符號表在為 int2 型態的數據構建索引並且與查詢資格條件裡的 int4 數據做比較時很有用。類似的，int42_ops 支援對要和查詢裡的 int2 數據做比較的 int4 數據進行索引。

下面的選擇列表返回所有 ops_names：

SELECT am.amname AS acc_name,
       opc.opcname AS ops_name,
       opr.oprname AS ops_comp
    FROM pg_am am, pg_amop amop,
         pg_opclass opc, pg_operator opr
    WHERE amop.amopid = am.oid AND
          amop.amopclaid = opc.oid AND
          amop.amopopr = opr.oid
    ORDER BY acc_name, ops_name, ops_comp

關鍵字

作者：由 Herouth Maoz 寫作。這些最早出現在 1998-03-02 用戶郵件列表裡關於"主鍵（PRIMARY KEY）和唯一約束（UNIQUE constraints）有什麼異同"問題的解答。

Subject: Re: [QUESTIONS] PRIMARY KEY | UNIQUE

        What's the difference between:

              PRIMARY KEY(fields,...) and
              UNIQUE (fields,...)

       - Is this an alias?
       - If PRIMARY KEY is already unique, then why
         is there another kind of key named UNIQUE?

主鍵是用於標識某特定行的字段。例如，身份証（社會安全號）標識一個人。

一個簡單的由字段組成的 UNIQUE 與行標識毫無關系。它只是一個完整性約束。例如，我收集了一些互聯網鏈接。每一個互聯網鏈接用一個唯一的數字標識，也就是主鍵，這個鍵用於表中。

但是，我的應用要求每套集合還要有一個唯一的名稱。因為這樣做的話一個想更改一個互聯網鏈接集合的"人"就可以找出該集合。畢竟，如果你有兩個集合都叫"生命科學"，一個被標識成24433，另一個是29882，而24433那個是你要找的，這樣找要比從擁有唯一集合名稱的組中找難得多。

所以，用戶用名稱來選擇集合。因此，對這個資料庫我們確信名稱是唯一的。但是資料庫中沒有其他表與鏈接集合表通過鏈接集合名稱相關。因此這樣做很效率很底。

另外，盡管是唯一的，組名稱實際上並不定義集合！例如，如果某人決定將集合名從"生命科學"改為"生物學"，該集合仍然是同一集合，只是名字不同而已。只要名稱仍然是唯一的即可。

所以：

主鍵：
- 用於標識某行而且與之相關。
- 是不可能（或很難）更新。
- 不應該允許空（NULL）。
唯一域/字段：
- 用於作為訪問某行的可選手段。
- 只要唯一就可以更新。
- 可以為空（NULLs）。

那麼為什麼標準 SQL 語法裡沒有非唯一鍵的顯式定義呢？這是因為索引是與具體實現相關的。SQL 不定義實現，只定義資料庫內數據間的關系， Postgres的確允許非唯一索引，但是用於增強 SQL 鍵字的索引總是唯一的。

因此，你可以通過任何表中的列的組合來查詢該表，不管你是否在這些列上建立了索引。索引只是每個 RDBMS 的實現提供給你的一個工具，以便令常用的查詢可以更有效的進行。有些 RDBMS 可能會給你另外一些工具，例如在主存裡保留一個鍵值。它們有一些特殊的命令，例如

CREATE MEMSTORE ON <table> COLUMNS <cols>

（這不是一個實際的命令,只是一個例子）。

實際上，當你創建一個主鍵或一個域/字段的唯一組合時，在SQL 聲明中沒有任何地方提到創建了索引，用該鍵對數據的檢索也不會比一次順序搜索更高效！

所以，如果你想用一個並不唯一的字段組合作為第二個鍵字，你實際上不用聲明任何東西-只要用那個組合檢索就行了。但是，如果你想令檢索更高效一些，你就不得不依賴你的RDBMS 提供者給你的工具－不管是索引還是我想像的那種 MEMSTORE 命令或是一個智能的 RDBMS，它在你還沒有意識到你常用一套特定的鍵字組合來查詢時就給你創建了索引... （它從經驗中學來）。

上一頁	首頁	下一頁
UNION 查詢	開頭	部分索引