Ubuntu PostgreSQL索引优化实践

一、索引类型选择

• B-Tree索引：适用于等值查询、范围查询和排序，是默认索引类型，如CREATE INDEX idx_email ON users(email)。
• GIN索引：用于JSONB、数组、全文搜索等复杂数据类型，如CREATE INDEX idx_json ON products USING GIN(to_tsvector('english', description))。
• BRIN索引：适合大表的范围查询（如时间序列数据），索引体积小，如CREATE INDEX idx_time ON logs USING BRIN(timestamp)。
• 部分索引：仅对满足条件的数据建索引，减少冗余，如CREATE INDEX idx_active ON orders(user_id) WHERE status = 'active'。

二、索引创建与管理

• 复合索引：多列组合索引，列顺序按筛选度从高到低排列，避免冗余，如CREATE INDEX idx_status_category ON orders(status, category_id, order_date)。
• 覆盖索引：通过INCLUDE子句包含查询所需列，避免回表，如CREATE INDEX idx_covering ON orders(customer_id) INCLUDE(order_date, total_amount)。
• 维护索引：定期用REINDEX重建索引，或用VACUUM FULL清理碎片，可通过pg_stat_user_indexes监控索引使用率，删除未使用的索引。

三、查询优化配合

• 分析执行计划：用EXPLAIN ANALYZE查看索引是否被使用，避免全表扫描，如EXPLAIN SELECT * FROM users WHERE email = '[e-mail]'。
• 优化查询语句：避免SELECT *，使用LIMIT分页，将子查询改写为JOIN，如SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'shipped'。

四、配置与维护

• 调整配置参数：在postgresql.conf中设置shared_buffers（建议为物理内存25%-40%）、work_mem（排序/哈希内存，通常64MB-1GB）、maintenance_work_mem（索引维护内存，建议1GB-4GB）。
• 使用连接池：通过PgBouncer管理连接，减少连接开销，设置max_connections为合理值（通常不超过500）。

五、工具与监控

• 慢查询日志：在postgresql.conf中设置log_min_duration_statement记录慢查询，分析优化。
• 系统监控：用pg_stat_activity查看活跃连接，pg_stat_statements分析查询频率和耗时。