1. 问题背景：一条慢SQL引发的性能危机
上周排查生产环境性能问题时，发现一条看似简单的订单查询SQL竟耗时5秒以上：
SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed' ORDER BY create_time DESC LIMIT 20;这个查询在orders表（约2000万条记录）中按用户ID和状态筛选后，根据创建时间排序返回最近20条记录。EXPLAIN分析显示数据库进行了全表扫描，没有有效利用索引。对于高频访问的订单查询接口，这种性能表现完全不可接受。
关键现象：执行计划中出现"Seq Scan"（顺序扫描）和"Sort"（排序）这两个性能杀手
2. 初步优化：基础索引策略
2.1 创建复合索引
最直接的优化方案是为WHERE条件和ORDER BY字段创建复合索引：
CREATE INDEX idx_orders_user_status_time ON orders(user_id, status, create_time);这个B-tree索引按照查询条件的顺序构建：
- 先按user_id快速定位用户记录
- 再按status过滤已完成订单
- 最后按create_time排序
优化后执行时间降至800ms，但仍不理想。EXPLAIN显示虽然使用了索引，但出现了"Bitmap Heap Scan"——说明索引过滤后仍需回表查询完整数据。
2.2 包含索引优化
通过INCLUDE子句将查询所需的所有字段包含在索引中，避免回表：
CREATE INDEX idx_orders_covering ON orders(user_id, status, create_time) INCLUDE (order_no, amount, product_id);此时执行计划显示"Index Only Scan"，时间降至300ms。但索引体积增大了40%，写入性能受到影响。
3. 深度优化：排序算法调优
3.1 排序内存参数调整
PostgreSQL的work_mem参数控制排序操作的内存使用量。默认4MB的设置可能导致大数据量排序使用临时文件：
-- 会话级调整 SET work_mem = '32MB'; -- 或在postgresql.conf中设置 work_mem = 32MB调整后执行时间降至180ms。但需注意：
- 该参数是每个操作的内存限额，不是全局限制
- 并发量大时需要合理控制，避免内存耗尽
3.2 排序策略优化
通过调整enable_sort参数可以强制使用索引排序：
SET enable_sort = off; -- 强制使用索引排序配合我们的覆盖索引，执行时间进一步降至120ms。但这种方法属于"暴力优化"，可能影响其他查询。
4. 终极方案：部分索引与条件优化
4.1 创建部分索引
由于我们只查询status='completed'的订单，可以创建条件索引：
CREATE INDEX idx_orders_user_completed_time ON orders(user_id, create_time) WHERE status = 'completed';这个更精简的索引使查询时间稳定在100ms左右，且索引体积只有之前方案的60%。
4.2 查询重写技巧
原始查询可以优化为：
SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed' ORDER BY create_time DESC LIMIT 20 OFFSET 0; -- 明确分页参数同时建议应用层使用游标分页而非OFFSET，避免深分页问题：
-- 第一页 SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed' AND create_time <= NOW() ORDER BY create_time DESC LIMIT 20; -- 后续页（使用上一页最后一条记录的create_time） SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed' AND create_time < '2023-07-20 15:30:45' -- 上一页最后一条的时间 ORDER BY create_time DESC LIMIT 20;5. 生产环境验证与监控
5.1 执行计划分析
优化后的EXPLAIN ANALYZE输出示例：
Limit (cost=0.42..8.44 rows=20 width=48) (actual time=0.025..0.101 rows=20 loops=1) -> Index Scan Backward using idx_orders_user_completed_time on orders (cost=0.42..2004.42 rows=5000 width=48) (actual time=0.024..0.098 rows=20 loops=1) Index Cond: (user_id = 12345) Filter: (status = 'completed') Planning Time: 0.102 ms Execution Time: 0.121 ms5.2 长期性能监控
建议配置pg_stat_statements扩展监控SQL性能：
CREATE EXTENSION pg_stat_statements; -- 查询最慢的SQL SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;6. 扩展思考：不同场景下的优化策略
6.1 分页查询的优化
对于深度分页（如第100页），传统的LIMIT/OFFSET效率极低。替代方案：
-- 使用CTE先快速定位ID WITH temp AS ( SELECT id FROM orders WHERE user_id = 12345 AND status = 'completed' ORDER BY create_time DESC LIMIT 20 OFFSET 2000 -- 第101页 ) SELECT o.* FROM orders o JOIN temp t ON o.id = t.id;6.2 多列排序场景
当需要按多个字段排序时（如先按金额再按时间）：
CREATE INDEX idx_orders_user_status_amount_time ON orders(user_id, status, amount, create_time); -- 查询语句 SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed' ORDER BY amount DESC, create_time DESC LIMIT 20;6.3 分区表优化
对于超大规模订单表（亿级以上），可以考虑按时间范围分区：
CREATE TABLE orders ( id BIGSERIAL, user_id BIGINT, status TEXT, create_time TIMESTAMPTZ, ... ) PARTITION BY RANGE (create_time); -- 创建月度分区 CREATE TABLE orders_202307 PARTITION OF orders FOR VALUES FROM ('2023-07-01') TO ('2023-08-01');7. 避坑指南：常见误区与教训
过度索引陷阱：每个新索引都会增加写入开销。监控发现我们的写入TPS从1500降至900后，最终只保留了部分索引方案。
统计信息过时：执行计划异常时，记得先更新统计信息：
ANALYZE orders;JIT编译干扰：对于简单查询，JIT编译反而增加开销：
SET jit = off;连接池设置不当：发现应用连接池的prepared statement缓存导致执行计划无法及时更新，调整为每100次执行重新prepare。
索引膨胀问题：长时间运行后索引可能膨胀，需要定期维护：
REINDEX INDEX CONCURRENTLY idx_orders_user_completed_time;
经过这轮优化，我们不仅解决了这个具体案例，还建立了一套完整的SQL优化流程：从执行计划分析、索引设计、参数调整到长期监控。记住，没有放之四海而皆准的优化方案，必须结合具体业务场景和数据特征持续调优。
