?
本文檔使用 PHP中文網(wǎng)手冊 發(fā)布
CREATE INDEX
Name
CREATE INDEX?--?定義一個(gè)新索引Synopsis
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]
描述
CREATE INDEX在指定的表上創(chuàng)建一個(gè)索引。 索引主要用來提高數(shù)據(jù)庫性能。但是如果不恰當(dāng)?shù)氖褂脤?dǎo)致性能的下降。
索引的鍵字字段是以字段名的方式聲明的，或者是可選的寫在一個(gè)圓括弧 里面的表達(dá)式。如果索引方式支持多字段索引，那么也可以聲明多個(gè)字段。
索引字段可以是一個(gè)使用一個(gè)或多個(gè)字段值進(jìn)行計(jì)算的表達(dá)式。 這個(gè)特性可用于獲取對(duì)基本數(shù)據(jù)的某種變形的快速訪問。 比如，一個(gè)在upper(col)上的函數(shù)索引將允許 WHERE upper(col) = 'JIM'子句使用索引。
PostgreSQL為從索引提供B-tree, hash, GiST, GIN 索引方法。用戶也可以定義它們自己的索引方法，但這個(gè)工作相當(dāng)復(fù)雜。
如果出現(xiàn)了WHERE子句，則創(chuàng)建一個(gè)部分索引 。部分索引是一個(gè)只包含表的一部分記錄的索引， 通常是該表中比其它部分?jǐn)?shù)據(jù)更有用的部分。比如，如果你有一個(gè)表， 里面包含已記賬和未記賬的定單，未記賬的定單只占表的一小部分而且這部分 是最常用的部分，那么你就可以通過只在未記賬部分創(chuàng)建一個(gè)索引來改善性能。 另外一個(gè)可能的用途是使用帶有UNIQUE的WHERE 強(qiáng)制一個(gè)表的某個(gè)子集的唯一性。 參閱Section 11.8獲取更多信息。
WHERE子句里的表達(dá)式只能引用下層表的字段， 它可以使用所有字段，而不僅僅是被索引的字段。目前，子查詢和 聚集表達(dá)式也不能出現(xiàn)在WHERE子句里。
索引定義里的所有函數(shù)和操作符都必須是"immutable"(不變的)， 也就是說，它們的結(jié)果必須只能依賴于它們的輸入?yún)?shù)， 而不能依賴任何外部的影響(比如另外一個(gè)表的內(nèi)容或者當(dāng)前時(shí)間)。 這個(gè)限制可以確保該索引的行為是定義良好的。 要在一個(gè)索引上或WHERE中使用用戶定義函數(shù)， 請把它標(biāo)記為immutable函數(shù)。
參數(shù)
- UNIQUE
令系統(tǒng)在創(chuàng)建索引時(shí)(如果數(shù)據(jù)已經(jīng)存在)和每次添加數(shù)據(jù)時(shí)檢測表中 是否有重復(fù)值。 如果插入或更新的值會(huì)導(dǎo)致重復(fù)的記錄時(shí)將生成一個(gè)錯(cuò)誤。
- CONCURRENTLY
使用該選項(xiàng)后，PostgreSQL將在創(chuàng)建索引的過程中不在表上持有任何防止插入、 更改、刪除的寫入鎖；否則將持有寫入鎖直到創(chuàng)建完成才釋放。使用這個(gè)選項(xiàng)時(shí)有幾個(gè)注意點(diǎn)，— 參見Building Indexes Concurrently并行創(chuàng)建索引。
- name
要?jiǎng)?chuàng)建的索引名，不能包含模式名。索引總是在同一個(gè)模式中作為父表創(chuàng)建的。 如果名稱被忽略，PostgreSQL會(huì)基于父表的名稱和索引列 名稱來選擇合適的名稱。
- table
要索引的表名(可能有模式修飾)
- method
要使用的索引方法的名字。可選的名字是btree(缺省)， hash，gist和gin
- column
表的列/字段名
- expression
一個(gè)基于該表的一個(gè)或多個(gè)字段的表達(dá)式。這個(gè)表達(dá)式通常必須帶著圓括 弧包圍寫出，如語法中顯示那樣。不過，如果表達(dá)式有函數(shù)調(diào)用的形式， 那么圓括弧可以省略。
- opclass
一個(gè)關(guān)聯(lián)的操作符類。參閱下文獲取細(xì)節(jié)。
- ASC
指定按升序排序（這是默認(rèn)的）。
- DESC
指定按降序排序。
- NULLS FIRST
指定空值在排序中排在非空值之前。當(dāng)DESC聲明后這些是默認(rèn)的。
- NULLS LAST
指定空值在排序中排在非空值之后。當(dāng)DESC未聲明時(shí)這是 默認(rèn)的。
- storage_parameter
索引方法特定的存儲(chǔ)參數(shù)的名字。 參閱索引存儲(chǔ)參數(shù)獲取細(xì)節(jié)。
- tablespace
-
在表空間中可創(chuàng)建索引。若未聲明，需參考default_tablespace 或者參考temp_tablespaces獲取臨時(shí)表索引方面的信息。
創(chuàng)建索引的表空間。如果沒有聲明，則使用default_tablespace， 或者temp_tablespaces臨時(shí)索引表。
- predicate
為一個(gè)部分索引定義約束表達(dá)式
索引存儲(chǔ)參數(shù)
可選的WITH子句為索引聲明storage parameters。 每個(gè)索引方法有自己一套允許的存儲(chǔ)參數(shù)。B-tree，hash和GiST索引方法都接受 下面這個(gè)單獨(dú)的參數(shù)：
- FILLFACTOR
一個(gè)索引的填充因子(fillfactor)是一個(gè)百分比， 它表示創(chuàng)建索引時(shí)每個(gè)索引頁的數(shù)據(jù)填充率。對(duì)于B-trees來說， 意味著在創(chuàng)建索引時(shí)葉子頁將按照此百分比填充數(shù)據(jù)， 在右側(cè)(添加新的最大的鍵值)擴(kuò)展索引時(shí)同樣也按照此百分比填充數(shù)據(jù)。如果后來某個(gè)頁被完全填滿， 那么該頁將被分割，從而導(dǎo)致索引性能退化。B-trees默認(rèn)的填充因子是90 ， 但是有效的整數(shù)取值范圍是10到100 。對(duì)于靜態(tài)的不會(huì)發(fā)生改變的表， 最佳值100可以讓索引的物理體積最小，但是對(duì)于不斷增長的表，較小的填充因子更合適， 因?yàn)檫@將盡可能減少對(duì)頁的分割。其它索引方法對(duì)填充因子的理解與此類似， 但是其默認(rèn)值各不相同。
GIN索引接受一個(gè)不同的參數(shù)：
- FASTUPDATE
-
這個(gè)設(shè)置控制Section 53.3.1中描述的快速更新技術(shù)。 是一個(gè)布爾型參數(shù)：ON啟用快速更新，OFF禁用它。 （ON和OFF的可選拼法允許像Section 18.1 中描述的一樣。）默認(rèn)是ON。
Note: 通過ALTER INDEX關(guān)閉FASTUPDATE可以阻止未來的 插入進(jìn)入到掛起索引條目列表但自身不會(huì)劃掉先前的條目。你可能想要 VACUUM然后確保掛起列表為空。
Building Indexes Concurrently并行創(chuàng)建索引
創(chuàng)建索引的過程會(huì)對(duì)數(shù)據(jù)庫的常規(guī)操作性能有不利影響。通常， 在創(chuàng)建索引的時(shí)候PostgreSQL會(huì)鎖定表以防止寫入， 然后對(duì)表做一次完整掃描以完成索引的創(chuàng)建。在此過程中其他事務(wù)仍然可以讀取表， 但是插入、更新、刪除將被一直阻塞到索引創(chuàng)建完畢。 這樣做對(duì)于處于活躍狀態(tài)的數(shù)據(jù)庫可能會(huì)產(chǎn)生嚴(yán)重的性能影響。 因?yàn)槟承┖艽蟮谋砜赡苄枰獢?shù)個(gè)小時(shí)的時(shí)間來建立索引， 而且即使對(duì)于生產(chǎn)中正在使用的較小的表，這種阻塞通常也是不可接受的。
PostgreSQL支持在沒有鎖定出寫道的情況下建立索引。 通過聲明CREATE INDEX的CONCURRENTLY選項(xiàng)來調(diào)用 該方法。當(dāng)該選項(xiàng)被使用，PostgreSQL必須執(zhí)行表的兩次掃描， 另外它必須等待所有可能會(huì)試用索引來中止的現(xiàn)有事務(wù)。因此該方法需要比需要長一些 時(shí)間來完成建立和使用的索引需要更多的總工作量。然而，盡管允許常規(guī)操作在索引 建立時(shí)繼續(xù)下去，這個(gè)方法對(duì)于在生產(chǎn)環(huán)境添加新索引是非常有效的。索引創(chuàng)建強(qiáng)加的 額外CPU和I/O負(fù)載可能放慢其他操作。
PostgreSQL允許在CREATE INDEX創(chuàng)建索引時(shí)使用CONCURRENTLY選項(xiàng)指定不鎖定表。 這樣PostgreSQL就必須對(duì)表掃描兩次，并且必須等待所有潛在 使用索引終止正在執(zhí)行的事務(wù)完成。 這種方法增加了總體工作量并且可能需要非常長的時(shí)間才能完成索引的創(chuàng)建。 然而由于這種方法允許在創(chuàng)建索引的同時(shí)進(jìn)行常規(guī)操作， 因此這種方法適合于在運(yùn)行過程中添加新索引。當(dāng)然， 創(chuàng)建索引所需要的額外CPU和I/O開銷仍然會(huì)對(duì)其它操作有不利影響。
在一個(gè)并發(fā)索引構(gòu)建中，該指數(shù)實(shí)際上是進(jìn)入一個(gè)事務(wù)中的系統(tǒng)目錄，然后這 兩個(gè)表掃描就會(huì)發(fā)生不再第二和第三個(gè)表中。 如果在對(duì)表進(jìn)行第二次掃描的時(shí)候出現(xiàn)了問題，比如在唯一索引字段上出現(xiàn)了重復(fù)值， CREATE INDEX命令將會(huì)失敗并遺留下一個(gè)"非法"的索引。 這個(gè)"非法"索引將被忽略，因?yàn)樗赡苁遣煌暾模? 然而它還是會(huì)消耗更新開銷。psql\d 命令將會(huì)報(bào)告這樣一個(gè)索引作為INVALID:
postgres=# \d tab
Table "public.tab"
Column | Type | Modifiers
--------+---------+-----------
col | integer |
Indexes:
"idx" btree (col) INVALID
在這種情況下推薦刪除該索引然后嘗試重新執(zhí)行CREATE INDEX CONCURRENTLY命令， 另一種可能的方法是使用REINDEX重建索引。 由于REINDEX不支持并行創(chuàng)建索引，因此可能不是一個(gè)好方法。
對(duì)于并行創(chuàng)建唯一索引還有一個(gè)警告：在開始對(duì)表進(jìn)行第二次掃描的時(shí)候， 已經(jīng)在其他事務(wù)上強(qiáng)制進(jìn)行唯一約束了。 這意味著在索引創(chuàng)建完畢之前，如果有其它違反唯一約束的行為那么將會(huì)報(bào)錯(cuò)， 甚至在索引最終創(chuàng)建失敗的情況下也是如此。同樣，如果在第二次掃描的過程中發(fā)生錯(cuò)誤， 生成的"非法"索引仍將在隨后強(qiáng)制執(zhí)行唯一約束的檢查。
并行創(chuàng)建表達(dá)式索引和部分索引也是可以的。 在表達(dá)式求值過程中發(fā)生的錯(cuò)誤同樣也會(huì)在唯一約束索引上導(dǎo)致類似前面描述過的 行為。
在創(chuàng)建普通索引的同時(shí)還允許在同一張表上創(chuàng)建其他普通索引， 但是在一張表上只能進(jìn)行一個(gè)并行索引的創(chuàng)建。在此兩種情況下， 都不允許同時(shí)對(duì)表所在的模式進(jìn)行修改。另一個(gè)差異是CREATE INDEX可以放在一個(gè)事務(wù)塊中執(zhí)行， 但CREATE INDEX CONCURRENTLY不可以。
注意
參閱Chapter 11獲取有關(guān)何時(shí)使用索引、何時(shí)不使用索引， 以及索引在哪種情況下是有用的信息。
目前，只有B-tree和GiST索引方法支持多字段索引。 缺省時(shí)最多可以聲明32個(gè)鍵字(可以在編譯PostgreSQL時(shí)修改)。 目前只有B-tree支持唯一索引。
可以為索引的每個(gè)列/字段聲明一個(gè)操作符類標(biāo)識(shí)將要被該索引用于該列/字段的操作符。 例如，一個(gè)四字節(jié)整數(shù)的B-tree索引將使用int4_ops表； 這個(gè)操作符類包括四字節(jié)整數(shù)的比較函數(shù)。實(shí)際上，該域的數(shù)據(jù)類型的缺省操作符類一般就足夠了。 某些數(shù)據(jù)類型有操作符類的原因是它們可能有多個(gè)有意義的順序。 例如，復(fù)數(shù)類型可能以絕對(duì)值或者實(shí)部排序。可以通過為該數(shù)據(jù)類型定義兩個(gè)操作符類， 然后在建立索引的時(shí)候選擇合適的表來實(shí)現(xiàn)。有關(guān)操作符類更多的信息在Section 11.9和Section 35.14里。
對(duì)于那些支持命令掃描的索引方法（目前，只有B-tree），可選子句ASC, DESC, NULLS FIRST, 和/或NULLS LAST可以 被指定來需該索引的掃描順序。因?yàn)橐粋€(gè)順序掃描可以向前或者向后掃描，創(chuàng)建一個(gè) 單列排序順序已經(jīng)有常規(guī)索引的DESC索引— 通常是沒用的。 這些選項(xiàng)的值是可以創(chuàng)建匹配混合排序查詢請求的排序次序的多列索引的，例如 SELECT ... ORDER BY x ASC, y DESC。 在依靠索引來避免排序步驟的查詢中，若您需要支持"nulls sort low" 那么NULLS選項(xiàng)就很有用，而不是默認(rèn)的"nulls sort high"。
對(duì)于大部分索引方法，創(chuàng)建一個(gè)索引的速度依靠maintenance_work_mem 的設(shè)置。更大的值將減少創(chuàng)建索引所需的時(shí)間，只要您不把它設(shè)為大于真正可用的 內(nèi)存數(shù)量，這會(huì)驅(qū)動(dòng)機(jī)器進(jìn)入交換。對(duì)于哈希索引， effective_cache_size還與索引創(chuàng)建時(shí)間有關(guān)系： PostgreSQL將會(huì)使用兩個(gè)不同哈希創(chuàng)建方法中的一個(gè)， 這取決與估計(jì)索引大小是大于還是小于effective_cache_size。 為獲得最佳效果，確保這個(gè)參數(shù)也被設(shè)置為可用內(nèi)存的反射，并且要注意 maintenance_work_mem和effective_cache_size的和 是小于機(jī)器的RAM，無論其他程序需要多少空間。
使用DROP INDEX刪除一個(gè)索引。
早先的PostgreSQL版本還有一個(gè)R-tree索引方法。 因?yàn)樗⒉槐菺iST方法優(yōu)秀， 因此現(xiàn)在已經(jīng)被刪除了。如果指定了USING rtree的話， CREATE INDEX將把它當(dāng)作USING gist看待，并將老的 索引轉(zhuǎn)化為GiST索引。
例子
在表films上的title字段上創(chuàng)建一個(gè) B-tree索引：
CREATE UNIQUE INDEX title_idx ON films (title);
在表達(dá)式lower(title)上創(chuàng)建一個(gè)索引以允許高效的大小寫無關(guān)搜索：
CREATE INDEX ON films ((lower(title)));
（在這個(gè)例子中我們已經(jīng)選擇忽略索引名稱，因此系統(tǒng)將選擇一個(gè)名稱， 典型的是films_lower_idx。）
為了創(chuàng)建一個(gè)空的非默認(rèn)排序順序索引：
CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
使用非默認(rèn)的填充因子創(chuàng)建索引：
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
為了創(chuàng)建一個(gè)禁用的快速更新GIN索引：
CREATE INDEX gin_idx ON documents_table USING gin (locations) WITH (fastupdate = off);
在表films的code字段上創(chuàng)建一個(gè)索引， 并且讓索引位于表空間indexspace內(nèi)：
CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;
為了創(chuàng)建一個(gè)在一個(gè)點(diǎn)屬性的GiST索引，所以,我們能夠有效地使用箱操作符， 以轉(zhuǎn)換函數(shù)為結(jié)果：
CREATE INDEX pointloc
ON points USING gist (box(location,location));
SELECT * FROM points
WHERE box(location,location) && '(0,0),(1,1)'::box;
在不鎖定表的情況下創(chuàng)建索引：
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
兼容性
CREATE INDEX是PostgreSQL 語言擴(kuò)展。在SQL標(biāo)準(zhǔn)中沒有這個(gè)命令。