CentOS PostgreSQL索引使用指南

一、索引类型及适用场景

二、创建索引

1. 基本语法

-- 普通索引
CREATE INDEX index_name ON table_name [USING method] (column_name [ASC|DESC] [NULLS FIRST|NULLS LAST]);

-- 复合索引（多列）
CREATE INDEX index_name ON table_name [USING method] (column1, column2, ...);

-- 唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 部分索引（仅对满足条件的行建索引）
CREATE INDEX index_name ON table_name [USING method] (column_name) WHERE condition;

-- 包含索引（避免回表查询）
CREATE INDEX index_name ON table_name [USING method] (column1) INCLUDE (column2);

2. 示例

-- 为用户表的email字段创建普通索引
CREATE INDEX idx_users_email ON users(email);

-- 为订单表创建复合索引（按用户ID和订单时间查询）
CREATE INDEX idx_orders_user_time ON orders(user_id, order_time DESC);

-- 为JSONB字段创建GIN索引（全文搜索）
CREATE INDEX idx_products_json ON products USING GIN(to_tsvector('english', description));

三、索引管理

1. 查看索引

-- 查看表的索引信息
SELECT * FROM pg_indexes WHERE tablename = 'table_name';

-- 使用psql命令行工具查看
\di+ table_name

2. 删除索引

DROP INDEX [IF EXISTS] index_name;

3. 维护索引

-- 重建索引（解决碎片问题）
REINDEX INDEX index_name;

-- 分析表（更新统计信息，帮助优化器选择索引）
ANALYZE table_name;

四、性能优化技巧

1. 索引设计原则

• 仅为高频查询字段创建索引，避免过度索引
• 复合索引中，将高选择性列放在前面
• 考虑使用部分索引减少索引大小

2. 查询优化

• 使用EXPLAIN ANALYZE分析查询计划，确认是否使用索引
• 避免在索引列上使用函数或表达式，可能导致索引失效
• 对于大表，考虑使用BRIN索引减少扫描范围

3. 维护策略

• 定期监控索引使用情况，删除未使用的索引
• 对于频繁更新的表，谨慎使用GIN/GiST等维护成本高的索引
• 在低峰期执行索引重建操作

五、注意事项

• 索引会占用额外的磁盘空间
• 索引会降低数据插入、更新和删除的性能
• 不同索引类型适用于不同场景，需根据实际查询需求选择