CREATE INDEX · PostgreSQL 中文文檔 9.3

# CREATE INDEX ## Name CREATE INDEX?--?創建一個索引 ## Synopsis ``` CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ _name_ ] ON _table_name_ [ USING _method_ ] ( { _column_name_ | ( _expression_ ) } [ COLLATE _collation_ ] [ _opclass_ ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( _storage_parameter_ = _value_ [, ... ] ) ] [ TABLESPACE _tablespace_name_ ] [ WHERE _predicate_ ] ``` ## 描述 `CREATE INDEX` 在一個指定表或者物化視圖的指定列上創建一個索引,索引主要用來提高數據庫的效率(盡管不合理的使用將導致較慢的效率) 索引的鍵字是用列名來聲明的,或者在括號里面寫一個表達式 .如果索引支持多列索引,我們也可以指定多個字段. 一個索引域可以是一個使用表的一個或多個字段進行計算的表達式。這個特性可以快速訪問一些基礎數據的變形.例如,一個在`upper(col)`上計算的索引允許`WHERE upper(col) = 'JIM'`字句使用這個索引. PostgreSQL提供的索引類型包括B-tree, hash, GiST, SP-GiST, 和 GIN.用戶也可以自己定義索引類型,但這是相當復雜的. 當使用`WHERE`字句,將會創建一個_partial index_.一個部分索引是僅包括表的一部分記錄的索引,通常是比表中其他部分更有用的記錄. 例如,如果你有一個包含了記賬和不記賬的訂單,不記帳的訂單只占了表的一小部分,并且是經常使用的部分.你可以在這部分上建立一個索引來提高效率.另一個應用是使用`WHERE` 和`UNIQUE` 來強制保證表的一個子集的唯一性.更多的信息見:[Section 11.8](#calibre_link-958) `WHERE` 字句的表達式可以使用底層表的列,并且可以是用它的所有列,而不僅僅建立索引的列.目前子查詢和聚合表達式時禁止出現在`WHERE`字句的,這個限制同樣適用于索引. 在索引定義中使用的所有函數和操作符必須是"immutable",即他們的結果依賴與他們的參數,而不受任何外部的數據的影響(例如另一個表的內容,或當前時間).這個限制保證了索引的定義完整性.在一個索引或`WHERE`子句中使用用戶自定義的函數時,確保在定義這些函數時標記他們是不可變的. ## Parameters `UNIQUE` 當索引被創建時使系統檢查表的重復值(如果已經有數據),并且當有數據插入時檢查唯一性.當插入數據或更新數據導致重復記錄時將會產生一個錯誤. `CONCURRENTLY` 當使用這個選項時, PostgreSQL在生成索引時將不會在表上加任何鎖阻止并發的插入,更新,刪除.而標準的建立一個索引將產生一個阻止寫(不包括讀)的鎖直到索引建立完畢.當使用這個選項時有一些問題需要注意,詳見:[_并發建立索引_](#calibre_link-1899). `_name_` 被創建索引的名字.這里不需要包括模式的名字,索引總是在同一個模式中作為其父表創建的.如果忽略這個選項,PostgreSQL基于父表的名字和建立索引的列選擇一個合適的名字. `_table_name_` 要建立索引的表名(可能有模式修飾) `_method_` 建立索引使用的方法名字. 可選的方法有`btree`, `hash`, `gist`, `spgist` and `gin`.默認方法是`btree`. `_column_name_` 表中要建立索引的列名. `_expression_` 基于表中一列或多列的表達式.在上面的語法中,表達式必須在園括號中.如果表達式有函數調用的格式則圓括號可以省略. `_collation_` 索引使用排序方式的名字,默認索引使用創建索引列中聲明的排序方式,創建索引的表達式結果的排序方式.對于使用指定排序方式的表達式查詢,索引使用指定的排序是高效的. `_opclass_` 操作符類的名字,詳細內容見下面. `ASC` 指定升序排序(默認). `DESC` 指定降序排序. `NULLS FIRST` 指定null排在非null值前面,在 `DESC` 中時默認的. `NULLS LAST` 指定null排在非null值后面, 默認在 `DESC`沒有指定. `_storage_parameter_` index-method-specific 存儲參數的名字.詳細見:[_索引存儲參數_](#calibre_link-2312). `_tablespace_name_` 在哪個表空間建立索引,如果不指定,將使用[default_tablespace](#calibre_link-90) ,或者臨時表的索引將使用[temp_tablespaces](#calibre_link-91) . `_predicate_` 部分索引的約束表達式. ### 索引存儲參數可選的`WITH`子句指定了索引的_storage parameters_.每個索引方式有它自己的存儲參數.B-tree, hash, GiST and SP-GiST索引都可以帶這個參數. `FILLFACTOR` 填充因子是一個索引實際數據的百分比,它決定索引方法占用頁的空間的比率.對于B-trees,在索引創建時葉子頁填充這個百分比的數據.其余的用來擴展索引(添加一個新的最大的鍵值).如果接下來頁占用100%,它將會分頁,這將導致索引效率的逐漸下降.B-tree使用一個90做為默認填充比,但是可以選擇10到100的任何值.如果是一個靜態表,填充比使用100將會最大的減小索引所占的物理空間.但是對于有大量更新的表一個較小的填充比將會盡可能的減少分頁.其他的索引方法使用填充比是不同的但是大致方法是相同; 方法之間的默認填充比不是一致的. GiST索引額外接受這個參數: `BUFFERING` 在使用[Section 55.3.1](#calibre_link-2313)建立索引時決定是否緩存建立的方法.使用`OFF` 關閉這個功能,`ON` 打開這個功能. 使用`AUTO` 它初始化時是關閉的,但是當索引的達到[effective_cache_size](#calibre_link-1748)將會打開.默認使用`AUTO`. GIN索引接受一個不同的參數: `FASTUPDATE` 這個設置用來控制在 [Section 57.3.1](#calibre_link-751)中描述的快速更新技術.它是一個布爾類型參數:`ON`使能快速更新,`OFF`關閉這個功能.(在[Section 18.1](#calibre_link-1149)中描述了允許可選的拼寫 `ON` and `OFF`.)默認是`ON`. > **Note:** 通過`ALTER INDEX`關閉`FASTUPDATE`防止將來數據插入到待建立的索引記錄中,但是它自己不寫之前的記錄到磁盤.你可以使用`VACUUM` 表來確保待建立索引空. ### 并發建立索引建立一個索引將影響正常的數據庫操作.一般的 PostgreSQL鎖住建立索引的表防止寫，然后通過掃描表來建立整個索引.其他的事物可以讀表,但是插入,更新,刪除操作將被鎖住直到索引建立完成.如果這是一個線上的生成庫將會有較嚴重的影響.非常大的表將使用數個小時來建立索引,即使一個較小的表,對于生產庫也會在一個不可接受的時間內鎖住該表的寫操作. PostgreSQL支持建立索引時不鎖寫操作.這個方法通過`CREATE INDEX`時指定`CONCURRENTLY`選項,當使用這個選項時, PostgreSQL必須掃描表2次,它必須等待所有的將要使用該索引的事物結束.所以這個方法比標準的建立索引需要更多的工作并且花費更多的時間.然而,在建立索引的時候它允許正常的操作所以對于生產環境它是非常有用的.當然在建立索引時額外的CPU和I/o開銷可能會降低其他操作的效率. 在并發創建索引中，索引在一個事物中進入系統表，然后兩表掃描發生在另外的事物中．在第二遍掃描時任何活躍的事物（在該表上）將會阻塞并發索引的建立直到事物完成．甚至在第二遍表掃描時事物僅僅涉及到該表．并發建立索引使用[Section 47.59](#calibre_link-723)中的方法等待每一個發生的事物完成．當掃描表時發生了問題，例如一個唯一索引違反了唯一性，`CREATE INDEX`將會失敗并且留下一個 "invalid"索引．在查詢時這個索引將會被忽略因為他是不完整的；然而它仍然會增加更新的開銷．psql`\d`命令將列出帶 `INVALID`的索引． ``` postgres=# \d tab Table "public.tab" Column | Type | Modifiers --------+---------+----------- col | integer | Indexes: "idx" btree (col) INVALID ``` 這種情況推薦的恢復方法是刪除掉索引并且再次執行`CREATE INDEX CONCURRENTLY`. （另一種重建索引的方法是使用`REINDEX`.然而，因為`REINDEX`不支持并發建立索引，所以這種方式可用性不高．）另一個缺點是當并發建立唯一索引第二遍掃描表后唯一性將會約束其他事物．這意味著在索引建立完成前在其他查詢中違反了該索引將會報違反約束錯誤，即使最后索引建立失敗．如果在第二遍掃描發生了錯誤，在后續的操作中"invalid"索引也會（在相關列上）保持該唯一性．支持建立并發索引和部分索引．在這些語句中出現錯誤將會產生和前面違反唯一性約束相似的錯誤．標準建立索引允許并行的執行其他標準的建立索引語句，但是在一個表上一次僅可以有一個并發建立索引語句．在這兩種情況下，建立索引的同時不允許有其他模式類型的修改．另一個不同點是 `CREATE INDEX`可以在一個阻塞的事物里執行，而 `CREATE INDEX CONCURRENTLY`不可以． ## Notes 在[Chapter 11](#calibre_link-715)中描述了什么時候會使用索引，什么時候不使用索引，以及在什么情況下索引是有用的． | **Caution** | |:--- | | 哈希索引不記錄到WAL日志中，所以當系統崩潰有未寫入磁盤的數據時哈希索引需要使用`REINDEX`重新建立．哈希索引的變化不能在恢復一個基礎備份后通過流復制或者文件復制來重寫．所以接下來在查詢中使用他們將會給出錯誤的結果．因為這些原因使用哈希索引是有阻礙的． | 現在只有B-tree, GiST 和 GIN支持多列索引．默認支持最多32列．（當編譯PostgreSQL時可以改變這個限制）．現在只有B-tree支持唯一索引．對于索引的每一列可以指定一個_operator class_．_operator class_標識了索引那一列的使用的操作符．例如一個B-tree索引在一個四字節整數上可以使用`int4_ops`；這個操作符類包括四字節整數的比較函數．實際上對于列上的數據類型默認的操作符類時足夠用的．操作符類主要用于一些有多種排序的數據．例如，我們想按照絕對值或者實數部分排序一個復數．我們能通過定義兩個操作符類然后當建立索引時選擇合適的類．詳細的操作符類信息請參考：[Section 11.9](#calibre_link-82)和[Section 35.14](#calibre_link-54). 索引（現在只有B-tree）中支持有序掃描的字句中可以制定`ASC`, `DESC`, `NULLS FIRST`, 和 `NULLS LAST`修改索引的排序方式．因為一個有序的索引能被從前向后或者從后向前掃描．建立一個單列的`DESC`索引是沒有意義的．因為在一個標準的索引是已經排序的．在創建多列索引時使用這些選項值來匹配一些復合查詢的排序請求，例如`SELECT ... ORDER BY x ASC, y DESC`.如果你在查詢中需要支持 "nulls sort low"特性`NULLS`選項是很有用的，而不是默認的"nulls sort high", 對于大多數索引，創建索引的速度依賴于[maintenance_work_mem](#calibre_link-1150)的設置．只要你沒有使他超過可得到內存而進入swap分區，較大的值將會減少索引創建的時間．對于創建哈希索引的時間與[effective_cache_size](#calibre_link-1748)值相關,PostgreSQL將使用兩種不同的哈希索引創建方法，主要取決于索引的大小比`effective_cache_size`大還是小．為了得到較好的結果，確保這個參數的設置考慮到可用內存，并且注意`maintenance_work_mem`和`effective_cache_size`的和小于其他程序所需要的內存空間．使用 [DROP INDEX](#calibre_link-602)刪除一個索引． PostgreSQL之前的版本有一個R-tree索引方法．這個方法已經被刪除因為它對于GiST索引沒有明顯的優勢．如果指定`USING rtree`，`CREATE INDEX`會將他翻譯為`USING gist`，去轉換老的數據庫版本到GiST. ## Examples 在表`films`的`title`列上建立B-tree索引： ``` CREATE UNIQUE INDEX title_idx ON films (title); ``` 允許大小寫無關查詢在 `lower(title)`表達式上建立索引： ``` CREATE INDEX ON films ((lower(title))); ``` (在這個例子中我們可以選在忽略索引名字，系統將給出一個名字，典型的是`films_lower_idx`.) 創建一個使用非默認排序方式的索引： ``` CREATE INDEX title_idx_german ON films (title COLLATE "de_DE"); ``` 創建一個null值的非默認排序的索引： ``` CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST); ``` 創建一個非默認填充因子的索引： ``` CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70); ``` 創建一個將快速更新關閉的GIN索引： ``` CREATE INDEX gin_idx ON documents_table USING gin (locations) WITH (fastupdate = off); ``` 創建一個在`films`的`code`列的索引，并且使索引建立在`indexspace`表空間中： ``` CREATE INDEX code_idx ON films (code) TABLESPACE indexspace; ``` 在一個點屬性上創建GiST索引，讓我們在轉換函數的結果上高效的使用box操作符： ``` CREATE INDEX pointloc ON points USING gist (box(location,location)); SELECT * FROM points WHERE box(location,location) && '(0,0),(1,1)'::box; ``` 創建一個在表上不加寫鎖的索引： ``` CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity); ``` ## Compatibility `CREATE INDEX`是PostgreSQL語言的擴展．在SQL標準中沒有索引的規定． ## See Also [ALTER INDEX](#calibre_link-612), [DROP INDEX](#calibre_link-602)