# 步骤1：分析查询需求
— 分析常用查询
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

# 步骤2：创建合适的索引
— 创建B-tree索引
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

— 创建复合索引
CREATE INDEX idx_fgedu_orders_customer_date ON fgedu_orders(customer_id, order_date);

— 创建GIN索引
CREATE INDEX idx_fgedu_fgedus_tags ON fgedu_fgedus USING GIN(tags);

— 创建BRIN索引
CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders USING BRIN(order_date);

# 步骤3：验证索引使用
EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE email = ‘fgedu@fgedu.net.cn’;
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

# 结果示例
QUERY PLAN
——————————————————————–
Index Scan using idx_fgedu_fgedus_email on fgedu_fgedus (cost=0.29..8.31 rows=1 width=100)
Index Cond: (email = ‘fgedu@fgedu.net.cn’)
Execution Time: 0.123 ms

# 步骤1：查看索引状态
SELECT
indexrelid::regclass AS index_name,
indisvalid,
indisready,
indisprimary,
indisunique
FROM pg_index
WHERE indrelid = ‘fgedu_fgedus’::regclass;

# 步骤2：重建索引
REINDEX INDEX idx_fgedu_fgedus_email;

# 步骤3：分析索引使用情况
SELECT
indexrelid::regclass AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_fgedus’;

# 步骤4：清理未使用的索引
— 识别未使用的索引
SELECT
indexrelid::regclass AS index_name,
idx_scan
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_fgedus’ AND idx_scan = 0;

— 删除未使用的索引
DROP INDEX IF EXISTS idx_fgedu_fgedus_unused;

# B-tree索引
– 适用场景：等值查询、范围查询、排序操作
– 示例：CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus(email);

# Hash索引
– 适用场景：等值查询
– 示例：CREATE INDEX idx_fgedu_fgedus_id ON fgedu_fgedus USING HASH(id);

# GIN索引
– 适用场景：数组、JSON、全文搜索
– 示例：CREATE INDEX idx_fgedu_fgedus_tags ON fgedu_fgedus USING GIN(tags);

# BRIN索引
– 适用场景：大型表的范围查询
– 示例：CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders USING BRIN(order_date);

# Bloom索引
– 适用场景：多列等值查询
– 示例：CREATE INDEX idx_fgedu_fgedus_multi ON fgedu_fgedus USING bloom(fgeduname, email);

# 复合索引设计
– 最左前缀原则：复合索引的列顺序很重要
– 高频查询列放在前面
– 示例：CREATE INDEX idx_fgedu_orders_customer_date ON fgedu_orders(customer_id, order_date);

# 部分索引设计
– 只索引满足特定条件的行
– 减少索引大小
– 示例：CREATE INDEX idx_fgedu_fgedus_active ON fgedu_fgedus(email) WHERE active = true;

# 表达式索引设计
– 对表达式结果建立索引
– 加速表达式查询
– 示例：CREATE INDEX idx_fgedu_fgedus_lower_email ON fgedu_fgedus(LOWER(email));

# 覆盖索引设计
– 包含查询所需的所有列
– 实现仅索引扫描
– 示例：CREATE INDEX idx_fgedu_fgedus_email_fgeduname ON fgedu_fgedus(email, fgeduname);

# 调整索引扫描成本
random_page_cost = 1.1 # 对于SSD存储，可适当减小
seq_page_cost = 1.0 # 顺序页面访问成本

# 调整索引扫描类型
enable_indexscan = on # 启用索引扫描
enable_bitmapscan = on # 启用位图扫描
enable_indexonlyscan = on # 启用仅索引扫描

# 示例：索引参数调优
— 调整成本参数
ALTER SYSTEM SET random_page_cost = ‘1.1’;

— 启用仅索引扫描
ALTER SYSTEM SET enable_indexonlyscan = ‘on’;

— 验证配置
SHOW random_page_cost;
SHOW enable_indexonlyscan;

# 定期重建索引
– 对于频繁更新的表，定期重建索引
– 减少索引碎片
– 示例：REINDEX INDEX idx_fgedu_fgedus_email;

# 定期分析表
– 更新统计信息，提高索引使用效率
– 示例：ANALYZE fgedu_fgedus;

# 监控索引使用情况
– 识别未使用的索引
– 优化索引策略
– 示例：
SELECT
indexrelid::regclass AS index_name,
idx_scan
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_fgedus’;

# 清理未使用的索引
– 减少存储空间
– 提高写入性能
– 示例：DROP INDEX IF EXISTS idx_fgedu_fgedus_unused;