PostgreSQL 查询性能优化实战

背景

最近负责的一个项目遇到了性能瓶颈，某个核心接口的响应时间达到了 3 秒以上。经过分析，发现问题出在数据库查询上。

问题分析

慢查询定位

首先使用 PostgreSQL 的慢查询日志定位问题 SQL：

SELECT * FROM orders 
WHERE user_id = ? 
AND status = 'completed' 
AND created_at > ? 
ORDER BY created_at DESC 
LIMIT 20;

EXPLAIN 分析

使用 EXPLAIN ANALYZE 查看执行计划：

EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'completed' 
AND created_at > '2024-01-01' 
ORDER BY created_at DESC 
LIMIT 20;

结果显示：

全表扫描（Seq Scan）

过滤了大量不匹配的行

排序操作耗时较长

优化方案

1. 创建复合索引

CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at DESC);

2. 使用覆盖索引

如果只需要特定字段，可以创建覆盖索引：

CREATE INDEX idx_orders_covering 
ON orders(user_id, status, created_at DESC) 
INCLUDE (order_no, amount);

3. 分区表

对于大数据量的表，可以考虑按时间分区：

CREATE TABLE orders (
  id serial,
  user_id int,
  status varchar(20),
  created_at timestamp
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_q1 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

优化效果

| 指标 | 优化前 | 优化后 | 提升 |

|------|--------|--------|------|

| 查询时间 | 3.2s | 50ms | 64x |

| 扫描行数 | 500万 | 200 | 25000x |

| 内存使用 | 高 | 低 | - |

其他优化技巧

1. 避免 SELECT *

只查询需要的字段，减少数据传输和内存使用。

2. 合理使用 LIMIT

对于只需要部分数据的场景，使用 LIMIT 限制返回行数。

3. 批量操作

使用批量 INSERT/UPDATE 代替单条操作。

4. 连接池

合理配置连接池大小，避免连接耗尽。

总结

数据库优化是一个系统工程，需要：

**监控**：持续监控慢查询

**分析**：使用 EXPLAIN 分析执行计划

**索引**：合理创建和使用索引

**测试**：在测试环境验证优化效果

记住，没有一劳永逸的优化方案，需要根据实际业务场景持续调整。