1. 基本语法
CREATE INDEX index_name ON table_name (column_name);
示例：
CREATE INDEX idx_users_email ON users (email);
2. 创建多列索引
可以在多个列上创建索引，以优化涉及这些列的查询。
CREATE INDEX index_name ON table_name (column1, column2, ...);
示例：
CREATE INDEX idx_users_name_email ON users (first_name, last_name, email);
3. 创建唯一索引
唯一索引确保索引列的值是唯一的。
CREATE UNIQUE INDEX index_name ON table_name (column_name);
示例：
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
4. 创建部分索引
部分索引只对满足特定条件的行创建索引，可以减少索引大小并提高性能。
CREATE INDEX index_name ON table_name (column_name) WHERE condition;
示例：
CREATE INDEX idx_users_active ON users (email) WHERE is_active = true;
5. 创建表达式索引
表达式索引基于列的计算结果创建索引。
CREATE INDEX index_name ON table_name (expression);
示例：
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
6. 创建并发索引
在创建索引时，默认会锁定表以防止数据修改。使用 CONCURRENTLY 选项可以在不锁定表的情况下创建索引，但速度较慢。
CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);
示例：
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
7. 创建全文索引
全文索引用于优化文本搜索查询。
CREATE INDEX index_name ON table_name USING GIN (column_name gin_trgm_ops);
示例：
CREATE INDEX idx_users_description ON users USING GIN (description gin_trgm_ops);
8. 创建空间索引（GiST）
GiST 索引用于优化空间数据查询。
CREATE INDEX index_name ON table_name USING GIST (column_name);
示例：
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);
9. 创建哈希索引
哈希索引适用于等值查询，但不支持范围查询。
CREATE INDEX index_name ON table_name USING HASH (column_name);
示例：
CREATE INDEX idx_users_id_hash ON users USING HASH (id);
10. 创建 BRIN 索引
BRIN 索引适用于按顺序存储的大表，可以减少索引大小。
CREATE INDEX index_name ON table_name USING BRIN (column_name);
示例：
CREATE INDEX idx_logs_timestamp_brin ON logs USING BRIN (timestamp);
11. 删除索引
如果不再需要索引，可以使用以下命令删除索引：
示例：
DROP INDEX idx_users_email;
12. 查看索引
可以使用以下命令查看表的索引：
或者查询系统表：
SELECT
indexname,
indexdef
FROM
pg_indexes
WHERE
tablename = 'table_name';
13. 索引类型对比
| 索引类型 | 适用场景 | 优点 | 缺点 |
|---|
| B-tree | 等值查询、范围查询、排序 | 通用性强，支持多种查询 | 索引较大 |
| Hash | 等值查询 | 查询速度快 | 不支持范围查询 |
| GiST | 空间数据、全文搜索 | 支持复杂数据类型 | 性能可能不如 B-tree |
| GIN | 全文搜索、数组查询 | 支持多值查询 | 索引较大 |
| BRIN | 按顺序存储的大表 | 索引小，适合大数据量 | 查询性能可能不如 B-tree |
14. 索引设计建议
选择合适的列：为经常用于查询条件、排序或连接的列创建索引。
避免过度索引：索引会增加写操作的开销，因此不要为所有列都创建索引。
使用部分索引：如果查询只涉及部分数据，可以使用部分索引减少索引大小。
监控索引性能：定期检查索引的使用情况，删除未使用的索引。
测试和优化：在生产环境中创建索引前，先在测试环境中验证其效果。
通过合理创建和使用索引，可以显著提升 PostgreSQL 数据库的查询性能。
评论