索引（Index）可以用於提高資料庫的查詢效能；但是索引也需要進行讀寫，同時還會佔用更多的儲存空間；因此瞭解並適當利用索引對於資料庫的最佳化至關重要。本篇我們就來介紹如何高效地使用 PostgreSQL 索引。
索引簡介
假設存在以下資料表：
CREATE TABLE test ( id integer, name text ); insert into test select v,'val:'||v from generate_series(1, 10000000) v;
我們經常需要使用類似以下的查詢返回結果：
SELECT name FROM test WHERE id = 10000;
如果沒有索引，資料庫需要掃描整個表才能找到相應的資料。利用EXPLAIN命令可以看到資料庫的執行計劃，也就是 PostgreSQL 執行 SQL 語句的具體步驟：
explain analyze SELECT name FROM test WHERE id = 10000; QUERY PLAN | ------------------------------------------------------------------------------------------------------------------------| Gather (cost=1000.00..107137.70 rows=1 width=11) (actual time=50.266..12082.777 rows=1 loops=1) | Workers Planned: 2 | Workers Launched: 2 | -> Parallel Seq Scan on test (cost=0.00..106137.60 rows=1 width=11) (actual time=7674.992..11553.964 rows=0 loops=3)| Filter: (id = 10000) | Rows Removed by Filter: 3333333 | Planning Time: 16.480 ms | Execution Time: 12093.016 ms |
Parallel Seq Scan 表示並行順序掃描，執行消耗了 12 s；由於表中有包含大量資料，而查詢只返回一行資料，顯然這種方法效率很低。
此時，如果在 id 列上存在索引，則可以透過索引快速找到匹配的結果。我們先建立一個索引：
CREATE INDEX test_id_index ON test (id);
建立索引需要消耗一定的時間。然後再次檢視資料庫的執行計劃：
explain analyze SELECT name FROM test WHERE id = 10000; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------| Index Scan using test_id_index on test (cost=0.43..8.45 rows=1 width=11) (actual time=20.410..20.412 rows=1 loops=1)| Index Cond: (id = 10000) | Planning Time: 14.989 ms | Execution Time: 20.521 ms |
Index Scan 表示索引掃描，執行消耗了 20 ms；這種方式類似於圖書最後的關鍵字索引，讀者可以相對快速地瀏覽索引並翻到適當的頁面，而不必閱讀整本書來找到感興趣的內容。
索引不僅僅能夠最佳化查詢語句，某些包含WHERE條件的UPDATE、DELETE語句也可以利用索引提高效能，因為修改資料的前提是找到資料。
此外，索引也可以用於最佳化連線查詢，基於連線條件中的欄位建立索引可以提高連線查詢的效能。索引甚至還能最佳化分組或者排序操作，因為索引自身是按照順序進行組織儲存的。
另一方面，系統維護索引需要付出一定的代價，從而增加資料修改操作的負擔。所以，我們需要合理建立索引，一般只為經常使用到的欄位建立索引。就像圖書一樣，不可能為書中的每個關鍵字都建立一個索引。
索引型別
PostgreSQL 提高了多種索引型別：B-樹、雜湊、GiST、SP-GiST、GIN 以及 BRIN 索引。每種索引基於不同的儲存結構和演算法，用於最佳化不同型別的查詢。預設情況下，PostgreSQL 建立 B-樹索引，因為它適合大部分情況下的查詢。
B-樹索引
B-樹是一個自平衡樹（self-balancing tree），按照順序儲存資料，支援對數時間複雜度（O(logN)）的搜尋、插入、刪除和順序訪問。舉例來說，假如 100 條資料時需要 1 次磁碟 I/O，也就是說 N 等於 100；10000 條資料時只需要 2 次 I/O，1 億資料時只需要 4 次 I/O。
對於索引列上的以下比較運算子，PostgreSQL 最佳化器都會考慮使用 B-樹索引：
<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL
另外，如果模式匹配運算子LIKE和~中模式的開頭不是萬用字元，最佳化器也可以使用 B-樹索引，例如：
col LIKE 'foo%' col ~ '^foo'
對於不區分大小的的ILIKE和~*運算子，如果匹配的模式以非字母的字元（不受大小寫轉換影響）開頭，也可以使用 B-樹索引。
B-樹索引還可以用於最佳化排序操作，例如：
SELECT col1, col2 FROM t WHERE col1 BETWEEN 100 AND 200 ORDER BY col1;
col1 上的索引不僅能夠最佳化查詢條件，也可以避免額外的排序操作；因為基於該索引訪問時本身就是按照排序返回結果。
雜湊索引
雜湊索引（Hash index）只能用於簡單的等值查詢（=），也就是說索引欄位被用於等號條件判斷。因為對資料進行雜湊運算之後不再保留原來的大小關係。
建立雜湊索引需要使用HASH關鍵字：
CREATE INDEX index_name
ON table_name USING HASH (column_name);
CREATE INDEX語句用於建立索引，USING子句指定索引的型別，具體參考下文。
GiST 索引
GiST 代表通用搜尋樹（Generalized Search Tree），GiST 索引單個索引型別，而是一種支援不同索引策略的框架。GiST 索引常見的用途包括幾何資料的索引和全文搜尋。GiST 索引也可以用於最佳化“最近鄰”搜尋，例如：
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
該語句用於查詢距離某個目標地點最近的 10 個地方。
SP-GiST 索引
SP-GiST 代表空間分割槽 GiST，主要用於 GIS、多媒體、電話路由以及 IP 路由等資料的索引。
與 GiST 類似，SP-GiST 也支援“最近鄰”搜尋。
GIN 索引
GIN 代表廣義倒排索引（generalized inverted indexes），主要用於單個欄位中包含多個值的資料，例如 hstore、array、jsonb 以及 range 資料型別。一個倒排索引為每個元素值都建立一個單獨的索引項，可以有效地查詢某個特定元素值是否存在。Google、百度這種搜尋引擎利用的就是倒排索引。
BRIN 索引
BRIN 代表塊區間索引（block range indexes），儲存了連續物理範圍區間內的資料摘要資訊。BRIN 也相比於 B-樹索引要小很多，維護也更容易。對於不進行水平分割槽就無法使用 B-樹索引的超大型表，可以考慮 BRIN。
BRIN 通常用於具有線性排序順序的欄位，例如訂單表的建立日期。
建立索引
PostgreSQL 使用CREATE INDEX語句建立新的索引：
CREATE INDEX index_name ON table_name
[USING method]
(column_name [ASC | DESC] [NULLS FIRST | NULLS LAST]);
其中：
-
index_name 是索引的名稱，table_name 是表的名稱；
-
method 表示索引的型別，例如 btree、hash、gist、spgist、gin 或者 brin。預設為 btree；
-
column_name 是欄位名，
ASC表示升序排序（預設值），DESC表示降序索引； -
NULLS FIRST和NULLS LAST表示索引中空值的排列順序，升序索引時預設為NULLS LAST，降序索引時預設為NULLS FIRST。
如果我們經常使用 name 欄位作為查詢條件，可以為 test 表建立以下索引：
CREATE INDEX test_name_index ON test (name);
建立索引之後，最佳化器會自動選擇是否使用索引，例如：
explain analyze SELECT * FROM test WHERE name IS NULL; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------| Index Scan using test_name_index on test (cost=0.43..5.77 rows=1 width=15) (actual time=0.036..0.037 rows=0 loops=1)| Index Cond: (name IS NULL) | Planning Time: 1.067 ms | Execution Time: 0.048 ms |
基於索引欄位的IS NULL運算子同樣可以利用索引進行最佳化。
唯一索引
在建立索引時，可以使用UNIQUE關鍵字指定唯一索引：
CREATE UNIQUE INDEX index_name
ON table_name (column_name [ASC | DESC] [NULLS FIRST | NULLS LAST]);
對於主鍵和唯一約束，PostgreSQL 會自動建立一個唯一索引，從而確保唯一性。
多列索引
PostgreSQL 支援基於多個欄位的索引，也就是多列索引（複合索引）。預設情況下，一個多列索引最多可以使用 32 個欄位。只有B-樹、GIST、GIN 和 BRIN 索引支援多列索引。
CREATE [UNIQUE] INDEX index_name ON table_name
[USING method]
(column1 [ASC | DESC] [NULLS FIRST | NULLS LAST], ...);
對於多列索引，應該將最常作為查詢條件使用的欄位放在左邊，較少使用的欄位放在右邊。例如，基於（c1, c2, c3）建立的索引可以最佳化以下查詢：
WHERE c1 = v1 and c2 = v2 and c3 = v3; WHERE c1 = v1 and c2 = v2; WHERE c1 = v1;
但是以下查詢無法使用該索引：
WHERE c2 = v2; WHERE c3 = v3; WHERE c2 = v2 and c3 = v3;
對於多列唯一索引，欄位的組合值不能重複；但是如果某個欄位是空值，其他欄位可以出現重複值。
函式索引
函式索引，也叫表示式索引，是指基於某個函式或者表示式的值建立的索引。PostgreSQL 中建立函式索引的語法如下：
CREATE [UNIQUE] INDEX index_name
ON table_name (expression);
expression 是基於欄位的表示式或者函式。
以下查詢在 name 欄位上使用了 upper 函式：
explain analyze SELECT * FROM test WHERE upper(name) ='VAL:10000'; QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------| Gather (cost=1000.00..122556.19 rows=50001 width=15) (actual time=18.629..7310.422 rows=1 loops=1) | Workers Planned: 2 | Workers Launched: 2 | -> Parallel Seq Scan on test (cost=0.00..116556.09 rows=20834 width=15) (actual time=4746.266..7171.452 rows=0 loops=3)| Filter: (upper(name) = 'VAL:10000'::text) | Rows Removed by Filter: 3333333 | Planning Time: 0.100 ms | Execution Time: 7310.444 ms |
雖然 name 欄位上存在索引 test_name_index，但是函式會導致最佳化器無法使用該索引。為了最佳化這種不分割槽大小寫的查詢語句，可以基於 name 欄位建立一個函式索引：
drop index test_name_index;
create index test_name_index on test(upper(name));
再次檢視該語句的執行計劃：
explain analyze SELECT * FROM test WHERE upper(name) ='VAL:10000'; QUERY PLAN | -------------------------------------------------------------------------------------------------------------------------------| Bitmap Heap Scan on test (cost=1159.93..57095.47 rows=50000 width=15) (actual time=17.046..17.047 rows=1 loops=1) | Recheck Cond: (upper(name) = 'VAL:10000'::text) | Heap Blocks: exact=1 | -> Bitmap Index Scan on test_name_index (cost=0.00..1147.43 rows=50000 width=0) (actual time=17.032..17.032 rows=1 loops=1)| Index Cond: (upper(name) = 'VAL:10000'::text) | Planning Time: 1.985 ms | Execution Time: 17.080 ms |
函式索引的維護成本比較高，因為插入和更新時都需要進行函式計算。
部分索引
部分索引（partial index）是隻針對表中部分資料行建立的索引，透過一個WHERE子句指定需要索引的行。例如，對於訂單表 orders，絕大部的訂單都處於完成狀態；我們只需要針對未完成的訂單進行查詢跟蹤，可以建立一個部分索引：
create table orders(order_id int primary key, order_ts timestamp, finished boolean); create index orders_unfinished_index on orders (order_id) where finished is not true;
該索引只包含了未完成的訂單 id，比直接基於 finished 欄位建立的索引小很多。它可以用於最佳化未完成訂單的查詢：
explain analyze select order_id from orders where finished is not true; QUERY PLAN | --------------------------------------------------------------------------------------------------------------------------------| Bitmap Heap Scan on orders (cost=4.38..24.33 rows=995 width=4) (actual time=0.010..0.010 rows=0 loops=1) | Recheck Cond: (finished IS NOT TRUE) | -> Bitmap Index Scan on orders_unfinished_index (cost=0.00..4.13 rows=995 width=0) (actual time=0.004..0.004 rows=0 loops=1)| Planning Time: 0.130 ms | Execution Time: 0.049 ms |
覆蓋索引
PostgreSQL 中的索引都屬於二級索引，意味著索引和資料是分開儲存的。因此透過索引查詢資料即需要訪問索引，又需要訪問表，而表的訪問是隨機 I/O。
為了解決這個效能問題，PostgreSQL 支援 Index-Only 掃描，只需要訪問索引的資料就能獲得需要的結果，而不需要再次訪問表中的資料。例如：
CREATE TABLE t (a int, b int, c int); CREATE UNIQUE INDEX idx_t_ab ON t USING btree (a, b) INCLUDE (c);
以上語句基於欄位 a 和 b 建立了多列索引，同時利用INCLUDE在索引的葉子節點儲存了欄位 c 的值。以下查詢可以利用 Index-Only 掃描：
explain analyze select a, b, c from t where a = 100 and b = 200; QUERY PLAN | ----------------------------------------------------------------------------------------------------------------| Index Only Scan using idx_t_ab on t (cost=0.15..8.17 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=1)| Index Cond: ((a = 100) AND (b = 200)) | Heap Fetches: 0 | Planning Time: 0.078 ms | Execution Time: 0.021 ms |
以上查詢只返回索引欄位（a、b）和覆蓋的欄位（c），可以僅透過掃描索引即可返回結果。
B-樹索引支援 Index-Only 掃描，GiST 和 SP-GiST 索引支援某些運算子的 Index-Only 掃描，其他索引不支援這種方式。
檢視索引
PostgreSQL 提供了一個關於索引的檢視 pg_indexes，可以用於檢視索引的資訊：
select * from pg_indexes where tablename = 'test'; schemaname|tablename|indexname |tablespace|indexdef | ----------|---------|---------------|----------|---------------------------------------------------------------------| public |test |test_id_index | |CREATE INDEX test_id_index ON public.test USING btree (id) | public |test |test_name_index| |CREATE INDEX test_name_index ON public.test USING btree (upper(name))|
該檢視包含的欄位依次為：模式名、表名、索引名、表空間以及索引的定義語句。
如果使用 psql 客戶端連線，可以使用\d table_name命令檢視錶的結構，包括表中的索引資訊。
維護索引
PostgreSQL 提供了一些修改和重建索引的方法：
ALTER INDEX index_name RENAME TO new_name;
ALTER INDEX index_name SET TABLESPACE tablespace_name;
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } index_name;
兩個ALTER INDEX語句分別用於重新命名索引和移動索引到其他表空間；REINDEX用於重建索引資料，支援不同級別的索引重建。
另外，索引被建立之後，系統會在修改資料的同時自動更新索引。不過，我們需要定期執行ANALYZE命令更新資料庫的統計資訊，以便最佳化器能夠合理使用索引。
刪除索引
如果需要刪除一個已有的索引，可以使用以下命令：
DROP INDEX index_name [ CASCADE | RESTRICT ];
CASCADE 表示級聯刪除其他依賴該索引的物件；RESTRICT 表示如果存在依賴於該索引的物件，將會拒絕刪除操作。預設為 RESTRICT。
我們可以使用以下語句刪除 test 上的索引：
DROP INDEX test_id_index, test_name_index;
轉載自：https://zhuanlan.zhihu.com/p/457860359