[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; ```