在 PostgreSQL 中,索引是一种用于提高数据库查询性能的数据结构。索引允许数据库系统快速定位到表中的特定行,而不必扫描整个表。
索引类型
PostgreSQL 多种类型索引,包括:
-
B-tree 索引:这是默认的索引类型,适用于大多数情况。B-tree 索引适用于范围查询和排序操作。创建语法:
CREATE INDEX idx_name ON table_name USING btree (column_name);
-
Hash 索引:适用于等值查询,但不支持范围查询。创建语法:
CREATE INDEX idx_name ON table_name USING hash (column_name);
-
GiST 索引:通用搜索树索引,适用于空间数据和全文搜索。创建语法:
CREATE INDEX idx_name ON table_name USING gist (column_name);
-
SP-GiST 索引:空间分区 GiST 索引,适用于非平衡数据结构。创建语法:
CREATE INDEX idx_name ON table_name USING spgist (column_name);
-
GIN 索引:通用倒排索引,适用于全文搜索和数组。创建语法:
CREATE INDEX idx_name ON table_name USING gin (column_name);
-
BRIN 索引:块范围索引,适用于大型表,通过存储数据块的摘要信息来提高性能。创建语法:
CREATE INDEX idx_name ON table_name USING brin (column_name);
创建索引
使用 CREATE INDEX
语句来创建索引。例如:
CREATE INDEX idx_name ON table_name (column_name);
多列索引
创建包含多个列的复合索引:
CREATE INDEX idx_name ON table_name (column1, column2);
唯一索引
唯一索引确保索引列中的值是唯一的:
CREATE UNIQUE INDEX idx_name ON table_name (column_name);
部分索引
部分索引只对表的一部分行进行索引:
CREATE INDEX idx_name ON table_name (column_name) WHERE condition;
索引维护
重建索引
使用 REINDEX
语句重建索引,以优化性能:
REINDEX INDEX idx_name;
删除索引
使用 DROP INDEX
语句删除索引:
DROP INDEX idx_name;