SELECT 的查询优化方案有哪些？

在实际 Java 项目开发中，PostgreSQL 的 SELECT 查询性能直接影响接口响应速度和数据库负载。即使 SQL 能查出正确结果，如果执行缓慢，依然会拖垮系统。

下面我将为你系统梳理 PostgreSQL SELECT 查询的常见优化方案，结合 真实开发场景 + 可落地的 SQL 示例 + 中文注释说明，帮助你写出高效、稳定的查询。

---

🚀 一、PostgreSQL SELECT 查询优化核心策略

优化方向	目标
✅ 索引优化	避免全表扫描，提升查询速度
✅ SQL 重写	改写低效 SQL，利用数据库优势
✅ 执行计划分析	定位性能瓶颈
✅ 分页优化	避免深度分页慢
✅ 统计信息更新	帮助优化器生成更好执行计划
✅ 表结构设计	从源头避免性能问题

---

✅ 二、优化方案与示例（含中文注释）

🔧 1. 【索引优化】为高频查询字段创建索引

❌ 问题：没有索引导致全表扫描

-- 查询订单（无索引时会 Seq Scan 全表扫描）
SELECT * FROM orders WHERE user_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

✅ 优化：创建 B-tree 索引

-- 为 user_id 创建索引（高频查询字段）
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 如果按状态 + 时间查询，创建复合索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

✅ 效果：Index Scan 替代 Seq Scan，查询从秒级降到毫秒级。

---

🔧 2. 【索引优化】为 JSONB 字段创建 GIN 索引

❌ 问题：JSONB 查询慢

-- 查询 profile 中昵称为 "张三" 的用户
SELECT * FROM users WHERE profile->>'nick' = '张三';
-- 无索引时极慢

✅ 优化：创建 GIN 索引

-- 为 JSONB 字段创建 GIN 索引，加速查询
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);

-- 或为特定字段创建表达式索引（更高效）
CREATE INDEX idx_users_nick ON users((profile->>'nick'));

✅ 适用场景：用户扩展属性、配置项、动态表单等。

---

🔧 3. 【SQL 重写】避免在 WHERE 中使用函数（破坏索引）

❌ 问题：函数包裹字段导致索引失效

-- ❌ 错误：DATE() 函数导致 created_at 索引无法使用
SELECT * FROM orders 
WHERE DATE(created_at) = '2024-06-01';
-- 执行计划：Seq Scan（全表扫描）

✅ 优化：改用范围查询

-- ✅ 正确：使用范围，可走索引
SELECT * FROM orders 
WHERE created_at >= '2024-06-01 00:00:00'
  AND created_at < '2024-06-02 00:00:00';

✅ 效果：Index Scan 成功使用 idx_orders_created_at。

---

🔧 4. 【分页优化】避免深度 OFFSET 分页

❌ 问题：OFFSET 越大越慢

-- ❌ 深度分页：跳过 50,000 条数据，性能极差
SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 10 OFFSET 50000;

✅ 优化：使用游标分页（Cursor-based Pagination）

-- ✅ 游标分页：基于上一页最后一条的 created_at
SELECT id, title, created_at 
FROM articles 
WHERE created_at < '2024-06-01 10:00:00'  -- 上一页最后一条的 created_at
ORDER BY created_at DESC 
LIMIT 10;

✅ 优势：始终使用索引，性能稳定，适合大数据量场景。

---

🔧 5. 【执行计划分析】使用 EXPLAIN ANALYZE 定位瓶颈

-- 分析慢查询的执行计划
EXPLAIN ANALYZE 
SELECT u.username, COUNT(o.id) 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.id 
ORDER BY COUNT(o.id) DESC 
LIMIT 10;

🔍 关注输出：

• Seq Scan → 是否应为 Index Scan？
• Nested Loop / Hash Join → 成本是否过高？
• Buffers → 是否大量磁盘读取？

✅ 建议：在 DataGrip 中开启 “Auto Explain” 功能，自动分析。

---

🔧 6. 【复合索引优化】按查询顺序创建复合索引

❌ 问题：索引顺序错误

-- 查询条件：status = 'paid' AND created_at > ?
-- 但索引是 (created_at, status) → 无法高效使用

✅ 优化：调整索引顺序

-- ✅ 正确：等值条件在前，范围在后
CREATE INDEX idx_orders_paid_recent ON orders(status, created_at DESC);

✅ 原则：

• 等值查询字段放前面（status = 'paid'）
• 范围查询字段放后面（created_at > ?）

---

🔧 7. 【避免 SELECT *】只查需要的字段

❌ 问题：传输大量无用数据

-- ❌ 查了 password_hash、profile 等不需要的字段
SELECT * FROM users WHERE username = 'zhangsan';

✅ 优化：明确指定字段

-- ✅ 只查接口需要的字段
SELECT id, username, email, created_at 
FROM users 
WHERE username = 'zhangsan';

✅ 效果：

• 减少网络传输
• 避免敏感字段泄露
• 提升缓存效率

---

🔧 8. 【统计信息更新】让优化器做出更好决策

-- 手动更新表的统计信息（帮助查询优化器选择更优执行计划）
ANALYZE users;
ANALYZE orders;

-- 或定期自动执行（通常 PostgreSQL 自动做，但大表可手动触发）

✅ 适用场景：大表数据批量导入后，或发现执行计划突然变差。

---

🔧 9. 【分区表】大数据量表按时间分区

-- 创建按月分区的订单表（适合日志、订单等大表）
CREATE TABLE orders (
    id SERIAL,
    user_id UUID,
    amount DECIMAL(10,2),
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_06 PARTITION OF orders
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

CREATE TABLE orders_2024_07 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');

✅ 效果：查询某月数据时，只扫描对应分区，极大提升性能。

---

🔧 10. 【物化视图】预计算复杂查询结果

-- 创建物化视图：用户订单统计（避免每次实时计算）
CREATE MATERIALIZED VIEW user_order_stats AS
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders 
WHERE status = 'paid'
GROUP BY user_id;

-- 刷新数据（可定时任务执行）
REFRESH MATERIALIZED VIEW user_order_stats;

✅ 适用场景：报表、后台统计页面，数据不要求实时。

---

📊 三、优化前后对比示例

优化前	优化后	效果
SELECT * FROM orders WHERE user_id = ?（无索引）	创建 idx_orders_user_id	从 1.2s → 10ms
OFFSET 10000 LIMIT 10	改为游标分页	从 800ms → 15ms
WHERE DATE(created_at) = ?	改为范围查询	从 Seq Scan → Index Scan
N+1 查询	改为 JOIN FETCH	101 次 → 1 次 SQL

---

✅ 四、推荐优化流程（Java 开发者可用）

1. 开启 SQL 日志（application.yml）
2. 发现慢接口 → 查日志定位 SQL
3. 用 EXPLAIN ANALYZE 分析执行计划
4. 检查是否缺少索引、是否 N+1、是否深度分页
5. 添加索引 / 重写 SQL / 改用游标分页
6. 验证性能提升
7. 提交代码 + 注释优化原因

---

🎁 五、附加建议

建议	说明
✅ 使用 pg_stat_statements 监控最耗时 SQL	CREATE EXTENSION pg_stat_statements;
✅ 生产环境避免 LIKE '%xxx%'	改用 pg_trgm + GIN 索引支持模糊查询
✅ 大表避免 COUNT(*)	改用近似统计或物化视图
✅ 定期 VACUUM ANALYZE	清理死元组，更新统计信息