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等維護成本高的索引
• 在低峰期執行索引重建操作

五、注意事項

• 索引會占用額外的磁盤空間
• 索引會降低數據插入、更新和刪除的性能
• 不同索引類型適用于不同場景，需根據實際查詢需求選擇