[TOC] ## PostgreSQL 索引類型 PostgreSQL 支持幾種不同類型的索引：B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN。 ## B-tree 索引 每當索引列涉及使用以下運算符之一的比較時，PostgreSQL 查詢規劃器將考慮使用 B-tree 索引： * `<` * `<=` * `=` * `>=` * `BETWEEN` * `IN` * `IS NULL` * `IS NOT NULL` 以下語句也會使用 B-tree 索引 ``` column_name LIKE 'foo%' column_name LKE 'bar%' column_name ~ '^foo' ``` > `~` 是正則匹配 ## 哈希索引 使用 `=` 為查詢條件時,使用 哈希索引 語法: ``` CREATE INDEX index_name ON table_name USING HASH (indexed_column); ``` ### GIN 索引 / GIST 索引 (array,hstore,json,全文等排序) - GIN 索引是“倒排索引”。它很適合索引那些復雜的值(比如 array, hstore, json 和 range)。 - 倒排索引中為每一個組成值都包含一個單獨的項，它可以高效地處理測試指定組成值是否存在的查詢。 - 當您將多個值存儲在單個列中時，GIN 索引最有用。 - 當構建全文索引時,gin和gist的區別就是 **gin查詢更快， 但是構建速度可能會慢一點。 而 gist 的構建速度快， 查詢會慢一點**。一般建議預計數據量不大時可以使用gist索引， 如果預計數據量很大請直接上gin。 ### BRIN 索引 - BRIN 表示塊范圍索引（Block Range Indexes），存儲有關存放在一個表的連續物理塊范圍上的值摘要信息。BRIN 是 PostgreSQL 9.5 版本新增的索引類型。與 B-tree 索引相比，BRIN 更小，維護成本更低。 - BRIN 則是一種塊范圍索引，其中每個塊的最小值和最大值被存儲在索引中。BRIN 在數據表中根據某個列的值進行分區，并對每個分區進行索引。當查詢只需要訪問一部分數據時，BRIN 能夠比 B-tree 更快地定位匹配的塊，并將不匹配的塊排除在查詢之外，從而提高查詢性能。 - **B-tree 適用于需要經常修改的數據集合，而 BRIN 適用于靜態數據集合**，即數據集合不經常修改。由于 BRIN 索引可以更快地定位到所需數據的塊，因此，當數據量非常大時，BRIN 索引查詢可能會比 B-tree 索引更快。 示例 ``` CREATE TABLE my_table ( id SERIAL PRIMARY KEY, timestamp_column TIMESTAMP, value_column INT ); CREATE INDEX my_brin_index ON my_table USING brin (timestamp_column); -- 范圍查詢 SELECT * FROM my_table WHERE timestamp_column BETWEEN '2022-01-01' AND '2022-01-31'; -- 部分列匹配 SELECT * FROM my_table WHERE timestamp_column >= '2022-01-01'; // BRIN索引不需要像B樹索引那樣頻繁地維護 REINDEX INDEX my_brin_index; ``` ### SP-GiST 索引 - SP-GiST 代表空間分區的 GiST。SP-GiST 支持分區搜索樹，有助于開發各種不同的非平衡數據結構。 - SP-GiST 索引最適用于具有自然聚類元素但也不是均衡樹的數據，例如 GIS、多媒體、電話路由和 IP 路由。 ### 部分索引 PostgreSQL 允許您創建部分索引。部分索引由助于減少索引大小，因為它沒有為列中的所有數據創建索引，它只為符合條件的行中的數據建立索引。 如查詢時候,一般只會增對某一個字段的值進行查詢 如 ``` SELECT * FROM customer WHERE active = 0; ``` 則可以創建部份索引,只針對 `active=0`,創建索引 ``` CREATE INDEX idx_customer_inactive ON customer(active) WHERE active = 0; ``` ### 重建索引 當索引被破壞時,可重建索引 語法 ``` REINDEX [ ( VERBOSE ) ] [ ( CONCURRENTLY [ boolean ] ) ] [ ( TABLESPACE new_tablespace ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name; ```` 說明： * `VERBOSE`關鍵字是可選的。如果提供此關鍵字，重建索引時將會顯示進度。 * `CONCURRENTLY`關鍵是可選的。它指示 PostgreSQL 在重建索引時，不需要阻止任何表上的操作。 * `TABLESPACE new_tablespace`是可選的。它指示 PostgreSQL 在新的表空間重建索引。 * `{ INDEX | TABLE | SCHEMA | DATABASE | SYSTEM }`用來指定重建的索引對象。 * `INDEX`: 重建指定的索引。 * `TABLE`: 重建指定的表中的所有的索引。 * `SCHEMA`: 重建指定的 Schema 中的所有的索引。 * `DATABASE`: 重建指定的數據庫中的所有的索引。 * `SYSTEM`: 重建指定的數據庫的系統目錄中的所有的索引。 * `name`指定對象的名字。 #### 示例 重建單個索引 ``` REINDEX INDEX index_name; ``` 重建表中所有索引 ``` REINDEX TABLE table_name; ``` 重建架構中所有索引 ``` REINDEX SCHEMA schema_name; ``` 重新數據庫所有索引 ``` REINDEX DATABASE database_name; ```