索引是加速搜尋引擎檢索資料的一種特殊表查詢。簡單地說，索引是一個指向表中資料的指標。

分類

B-tree：最常用的索引，B-tree 索引適合處理等值查詢和範圍查詢
Hash：只能處理簡單的等只查詢
GiST：不是一種單獨的索引型別，而是一種架構，可以在這種架構上實現很多不同的索引策略。GiST 索引定義的特定運算子可以用於特定索引策略。PostgreSQL 的標準釋出中包含了用於二維幾何資料型別的 GiST 運算子類。比如，一個圖形包含另一個圖形的運算子“@>”，一個圖形在另一個圖形的左邊沒有重疊的運算子“<<”，等等
SP-GiST：SP-GiST 是“space-partitioned GiST”的縮寫，即空間分割槽 GiST 索引。主要是透過一些新的索引演算法提高 GiST 索引再某個情況下的效能。
GIN：反轉索引，它可以處理包含多個鍵的值，如陣列等。與 GiST 類似，GIN 支援使用者定義的索引策略，可透過定義 GIN 索引的特定運算子型別實現不同的功能。比如，它支援包含運算子“@>”、被包含運算子“<@”、相等運算子“=”、重疊運算子“&&”，等等。

CREATE

語法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

CONCURRENTLY：併發建立索引。當該選項被使用時，PostgreSQL 會執行表的兩次掃描，因此該方法需要更長一些的時間來建立索引

單列索引

單列索引是一個只基於表的一個列上建立的索引，基本語法如下：

CREATE INDEX index_name ON table_name (column_name);

組合索引

組合索引是基於表的多列上建立的索引，基本語法如下：

CREATE INDEX index_name ON table_name (column1_name, column2_name);

不管是單列索引還是組合索引，該索引必須是在 WHERE 子句的過濾條件中使用非常頻繁的列。

唯一索引

使用唯一索引不僅是為了效能，同時也為了資料的完整性。唯一索引不允許任何重複的值插入到表中。基本語法如下：

CREATE UNIQUE INDEX index_name ON table_name (column_name);

區域性索引

區域性索引是在表的子集上構建的索引；子集由一個條件表示式上定義。索引只包含滿足條件的行。基礎語法如下：

CREATE INDEX index_name ON table_name (conditional_expression);

隱式索引

隱式索引是在建立物件時，由資料庫伺服器自動建立的索引。索引自動建立為主鍵約束和唯一約束。

檢視

\d：檢視錶下的索引

testdb=# \d company
                  Table "public.company"
 Column  |     Type      | Collation | Nullable | Default 
---------+---------------+-----------+----------+---------
 id      | integer       |           | not null | 
 name    | text          |           | not null | 
 age     | integer       |           | not null | 
 address | character(50) |           |          | 
 salary  | real          |           |          | 
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

\di：檢視所有索引

runoobdb=# \di
                    List of relations
 Schema |      Name       | Type  |  Owner   |   Table    
--------+-----------------+-------+----------+------------
 public | company_pkey    | index | postgres | company
 public | department_pkey | index | postgres | department
 public | salary_index    | index | postgres | company
(3 rows)

SELECT

select * from pg_indexes where tablename ='table_name';

刪除索引

DROP INDEX index_name;

修改索引

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEX name RESET ( storage_parameter [, ... ] )

準則

建立

主鍵自動建立唯一索引；
頻繁作為查詢條件的欄位應該建立索引；
查詢中與其他表有關聯的欄位，例如外來鍵關係；
高併發的情況下一般選擇複合索引；
查詢中排序的欄位建立索引將大大提高排序的速度（索引就是排序加快速查詢）；
查詢中統計或者分組的欄位；
儲存空間固定的欄位更適合選作索引的關鍵字，例：與 text 型別的欄位相比， char 型別的欄位較為適合選作索引關鍵字

不建立

索引不應該使用在較小的表上。
索引不應該使用在有頻繁的大批次的更新或插入操作的表上。
索引不應該使用在含有大量的 NULL 值的列上。
索引不應該使用在頻繁操作的列上。
經常增刪改的表，不需要建立索引；
表中包含大量重複資料，不需要建立索引，例如性別欄位，只有男女，不適合建立索引；

常見失效原因

沒有查詢條件，或者查詢條件沒有建立索引
在查詢條件上沒有使用引導列
查詢的數量是大表的大部分，應該是 30％以上
使用內部函式導致索引失效，例：
```
select * from test where round(id)=1
```
表記錄較少
隱式轉換導致索引失效，例如：表的欄位 tu_mdn 定義為 varchar2(20)，但在查詢時把該欄位作為 number 型別以 where 條件傳給資料庫：

錯誤的例子：

select * from test where tu_mdn=13333333333;

正確的例子：

select * from test where tu_mdn='13333333333';

索引列進行運算導致索引失效，運算包括(+，-，*，/，! 等)，例：
```
select * from test where round(id)=1
```
like "%_" 百分號在前
單獨引用複合索引裡非第一位置的索引列
B-tree索引 is null不會走,is not null會走,點陣圖索引 is null,is not null 都會走
併發建立索引過程中，如果被強行取消，可能會留下一個無效的索引，這個索引仍然會導致更新變慢。如果所建立的是一個唯一索引，這個無效的索引還會導致插入重複值失敗。手工刪除重建即可修復

線上重新建立索引

CREATE INDEX CONCURRENTLY ON <index_name> USING btree(id);

PostgreSQL：INDEX

分類