PostgreSQL DQL（数据查询语言）深度详解文档

以下是专为 Java 后端开发者（尤其是使用 Spring Boot 的团队）撰写的 PostgreSQL DQL（数据查询语言）深度详解文档，全面覆盖定义、作用、核心语法、高级特性、性能优化、企业级最佳实践与避坑指南，全部采用清晰中文注释式结构，可直接作为团队查询规范、代码审查标准、性能优化手册与培训教材。

---

🔍 PostgreSQL DQL（数据查询语言）深度详解文档

—— 数据的“侦察兵”与系统性能的命脉

适用对象：Java 开发、后端工程师、测试工程师、技术负责人、DBA
目标：系统掌握 PostgreSQL DQL 的所有核心与高级能力，统一团队查询标准，杜绝“慢查询”“全表扫描”“N+1 问题”，实现精准、高效、可维护的数据查询。

---

一、DQL 是什么？（Definition）

DQL（Data Query Language，数据查询语言）是用于从数据库中检索数据的 SQL 子集。
它只读取数据，不修改任何内容，核心语句只有一个：
✅ SELECT

✅ 核心作用：

作用	说明
获取业务数据	查询用户、订单、商品、配置等核心实体
聚合分析	统计销售额、活跃用户、转化率等业务指标
复杂关联	多表 JOIN、子查询、嵌套逻辑
数据加工	字段计算、JSON 解析、全文检索、条件转换
支撑报表与 API	所有前端展示、微服务接口、BI 系统的数据来源

💡 关键认知：

• 90% 的数据库性能问题，源于 DQL 写法不当。
• 一条 SELECT * FROM users 在百万级表中可能耗时 5 秒，
  而 SELECT id, username, email FROM users WHERE status = 'active' 只需 10ms。
• DQL 不是“查出数据就行”，而是“查得准、查得快、查得稳”。

---

二、DQL 包含哪些内容？（Core Components）

虽然 DQL 只有 SELECT 一个关键字，但其语法结构极其丰富，包含以下核心组件：

组件	作用	企业级重要性
✅ SELECT	指定返回字段	必须显式指定，禁止 SELECT *
✅ FROM	指定数据源表	多表 JOIN 是性能重灾区
✅ WHERE	过滤条件	必须有索引支持，否则全表扫描
✅ GROUP BY	分组聚合	用于统计报表、分组分析
✅ HAVING	分组后过滤	比 WHERE 更晚执行，作用于聚合结果
✅ ORDER BY	排序	必须有索引支持，避免内存排序
✅ LIMIT / OFFSET	分页	所有 API 查询必须分页
✅ JOIN	表关联	内连接、左连接、子查询，高频使用
✅ CTE（WITH）	公共表表达式	提升复杂查询可读性，推荐使用
✅ 窗口函数	ROW_NUMBER, RANK, SUM() OVER()	替代 Java 循环，大幅提升性能
✅ JSONB 查询	->, ->>, @>, ?	企业级动态配置查询核心
✅ 全文检索	to_tsvector, to_tsquery, @@	替代 LIKE '%xxx%'，性能提升百倍
✅ 子查询	相关子查询、非相关子查询	灵活但需警惕性能陷阱

---

三、DQL 核心语法详解与企业级标准示例（带中文注释）

以下所有示例均为生产环境推荐写法，遵循 PostgreSQL 最佳实践与 Java 团队协作规范。

---

✅ 1. SELECT —— 显式指定字段，禁止 SELECT *

🚫 错误写法（生产禁用）：

SELECT * FROM users;  -- ❌ 禁止！返回所有字段，包括 password_hash、metadata

✅ 推荐写法：

-- 📌 示例1：查询用户基本信息（API 常用）
SELECT 
    id, 
    username, 
    email, 
    status, 
    created_at
FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 0;

-- ✅ 说明：
-- - 只返回前端需要的字段
-- - 避免传输无用数据（如密码哈希、JSON 配置）
-- - 明确字段顺序，便于前端映射

✅ 团队规范：

• 所有查询必须显式列出字段名，禁止 SELECT *。
• Java 中使用 @Query 或 @EntityGraph 明确指定投影字段。
• 使用 @JsonView 或 DTO 映射查询结果，避免暴露实体内部字段。

---

✅ 2. WHERE —— 精准过滤，必须有索引

🚫 错误写法：

SELECT * FROM users WHERE email LIKE '%@company.com';  -- ❌ 无法使用索引，全表扫描
SELECT * FROM users WHERE status != 'deleted';         -- ❌ 不等于条件，索引失效

✅ 推荐写法：

-- 📌 示例1：精确匹配（推荐）
SELECT id, username, email FROM users WHERE email = 'alice@company.com';

-- 📌 示例2：前缀匹配（可走索引）
SELECT id, username, email FROM users WHERE email LIKE 'alice%';

-- 📌 示例3：枚举状态查询（必须建索引）
SELECT * FROM users WHERE status = 'active';  -- ✅ status 字段必须有索引

-- 📌 示例4：时间范围查询（必须有索引）
SELECT * FROM orders 
WHERE created_at >= '2025-01-01' 
  AND created_at < '2025-02-01'
ORDER BY created_at DESC;

-- 📌 示例5：JSONB 路径查询（必须用 GIN 索引）
SELECT username, metadata->>'theme' AS theme
FROM users 
WHERE metadata @> '{"notifications": {"email": true}}';  -- ✅ 包含查询，高效！

-- 📌 示例6：数组包含查询（必须用 GIN 索引）
-- 假设 users 表有 tags TEXT[] 字段
SELECT username, tags 
FROM users 
WHERE tags @> '{admin, vip}'::TEXT[];  -- ✅ 包含所有标签

✅ 团队规范：

• 所有 WHERE 条件字段必须创建索引。
• 禁止 !=、NOT IN、NOT LIKE、IS NULL 作为主要过滤条件（索引失效）。
• JSONB 查询必须使用 @>、->>、?，禁止 LIKE。
• 使用 EXPLAIN ANALYZE 验证查询是否命中索引。

---

✅ 3. JOIN —— 多表关联，必须使用显式 JOIN

🚫 错误写法（旧式逗号连接）：

SELECT u.name, o.order_no FROM users u, orders o WHERE u.id = o.user_id;  -- ❌ 不推荐

✅ 推荐写法：

-- 📌 示例1：内连接（INNER JOIN）—— 只返回有订单的用户
SELECT 
    u.username,
    u.email,
    o.order_no,
    o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id  -- ✅ 显式 JOIN，清晰关联关系
WHERE o.status = 'paid';

-- 📌 示例2：左连接（LEFT JOIN）—— 所有用户，即使无订单
SELECT 
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'  -- ✅ 条件写在 ON，非 WHERE
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

-- ✅ 说明：ON 中的 o.status = 'paid' 保证左连接时只关联已支付订单
-- ❌ 错误写法：WHERE o.status = 'paid' 会把无订单用户过滤掉

-- 📌 示例3：多表关联（订单 + 商品 + 用户）
SELECT 
    o.order_no,
    u.username,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

✅ 团队规范：

• 所有 JOIN 必须使用 INNER JOIN / LEFT JOIN，禁止逗号连接。
• ON 条件写关联字段，WHERE 写业务过滤。
• 避免 N+1 查询：一个 API 请求，只能有一次数据库查询。
• 使用 EXPLAIN 检查 JOIN 顺序，确保小表驱动大表。

---

✅ 4. GROUP BY + HAVING —— 聚合分析，替代 Java 循环

🚫 错误写法（Java 中循环聚合）：

List<Order> orders = orderRepository.findAll();
Map<String, Double> totalByUser = new HashMap<>();
for (Order o : orders) {
    totalByUser.merge(o.getUserId(), o.getAmount(), Double::sum);
}

✅ 推荐写法（SQL 一次性聚合）：

-- 📌 示例1：按用户统计订单数与总金额
SELECT 
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_value
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.id, u.username  -- ✅ GROUP BY 必须包含所有非聚合字段
HAVING SUM(o.total_amount) > 1000  -- ✅ HAVING 过滤聚合结果（金额 > 1000）
ORDER BY total_spent DESC;

-- 📌 示例2：统计每月销售额（时间聚合）
SELECT 
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS revenue,
    COUNT(DISTINCT o.user_id) AS new_customers
FROM orders o
WHERE o.status = 'paid'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;

-- 📌 示例3：JSONB 聚合（统计不同主题的用户数）
SELECT 
    metadata->>'theme' AS theme,
    COUNT(*) AS user_count
FROM users 
WHERE metadata ? 'theme'  -- ✅ 存在 theme 键
GROUP BY metadata->>'theme'
ORDER BY user_count DESC;

✅ 团队规范：

• 所有聚合查询必须用 GROUP BY，禁止在 Java 中循环聚合。
• HAVING 用于过滤聚合结果，WHERE 用于过滤原始行。
• COUNT(*)、SUM()、AVG() 必须配合 GROUP BY 使用。
• 时间聚合使用 DATE_TRUNC，不要用 TO_CHAR（无法排序）。

---

✅ 5. ORDER BY + LIMIT / OFFSET —— 分页查询，API 标准

🚫 错误写法：

SELECT * FROM users;  -- ❌ 无分页，数据量大时直接崩溃
SELECT * FROM users ORDER BY id;  -- ❌ 无 LIMIT，前端无法加载

✅ 推荐写法：

-- 📌 示例1：标准分页（第1页，每页10条）
SELECT 
    id, 
    username, 
    email, 
    created_at
FROM users 
WHERE status = 'active'
ORDER BY created_at DESC  -- ✅ 排序字段必须有索引
LIMIT 10 OFFSET 0;

-- 📌 示例2：第3页（每页10条）
SELECT 
    id, 
    username, 
    email, 
    created_at
FROM users 
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;  -- ✅ OFFSET = (页码 - 1) * 每页数量

-- 📌 示例3：使用游标分页（大数据量推荐，避免 OFFSET 性能下降）
-- 假设按 created_at 分页，传入上一页最后一条记录的时间
SELECT 
    id, 
    username, 
    email, 
    created_at
FROM users 
WHERE status = 'active'
  AND created_at < '2025-10-17 10:00:00'  -- ✅ 游标：小于上一页最后时间
ORDER BY created_at DESC
LIMIT 10;

✅ 团队规范：

• 所有 API 查询必须分页，默认 LIMIT 20，最大 LIMIT 100。
• ORDER BY 字段必须有索引，否则排序在内存中进行，性能极差。
• 大数据量（>10万）禁用 OFFSET，改用游标分页（Cursor-based Pagination）。
• Java 中使用 Spring Data JPA 的 Pageable 或 MyBatis 的 RowBounds。

---

✅ 6. CTE（WITH）—— 复杂查询的“分步拆解器”

✅ 核心价值：将复杂查询拆成多个逻辑块，提升可读性、可维护性、可测试性。

-- 📌 示例：查询消费最高的前10个用户及其订单详情
WITH top_users AS (
    -- 第一步：找出消费总额最高的前10用户
    SELECT 
        u.id AS user_id,
        u.username,
        SUM(o.total_amount) AS total_spent
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.status = 'paid'
    GROUP BY u.id, u.username
    ORDER BY total_spent DESC
    LIMIT 10
),
user_orders AS (
    -- 第二步：获取这些用户的全部订单
    SELECT 
        o.user_id,
        o.order_no,
        o.total_amount,
        o.created_at
    FROM orders o
    JOIN top_users tu ON o.user_id = tu.user_id
    WHERE o.status = 'paid'
)
-- 第三步：关联用户信息，返回最终结果
SELECT 
    tu.username,
    tu.total_spent,
    uo.order_no,
    uo.total_amount AS order_amount,
    uo.created_at AS order_date
FROM top_users tu
JOIN user_orders uo ON tu.user_id = uo.user_id
ORDER BY tu.total_spent DESC, uo.created_at DESC;

✅ 团队规范：

• 所有超过 3 表 JOIN 或含聚合的查询，必须使用 CTE 拆解。
• CTE 可嵌套，可多次引用，逻辑清晰。
• 使用 CTE 的查询，可独立测试每个子查询，便于调试。
• PostgreSQL 会自动优化 CTE，性能不劣于子查询。

---

✅ 7. 窗口函数（Window Functions）—— 替代 Java 循环的神器

✅ 作用：在结果集中做分组内排序、排名、累计、移动平均，无需 Java 循环。

-- 📌 示例1：每个用户的订单按金额排序（排名）
SELECT 
    u.username,
    o.order_no,
    o.total_amount,
    ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.total_amount DESC) AS rank_in_user,  -- ✅ 用户内排名
    RANK() OVER (ORDER BY o.total_amount DESC) AS global_rank  -- ✅ 全局排名
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY global_rank;

-- 📌 示例2：累计消费金额（滚动总和）
SELECT 
    u.username,
    o.created_at,
    o.total_amount,
    SUM(o.total_amount) OVER (
        PARTITION BY u.id 
        ORDER BY o.created_at 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY u.username, o.created_at;

-- 📌 示例3：查询每个部门最新入职员工（TOP N 每组）
WITH ranked_employees AS (
    SELECT 
        name,
        dept_id,
        hire_date,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY hire_date DESC) AS rn
    FROM employees
)
SELECT name, dept_id, hire_date
FROM ranked_employees
WHERE rn = 1;  -- ✅ 每个部门只取最新1人

-- 📌 示例4：移动平均（过去3天平均销售额）
SELECT 
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_avg_3days
FROM (
    SELECT 
        DATE(created_at) AS order_date,
        SUM(total_amount) AS daily_revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY DATE(created_at)
) daily
ORDER BY order_date;

✅ 团队规范：

• 所有“分组内排序”、“排行榜”、“累计统计”必须用窗口函数。
• 禁止在 Java 中用 List.sort()、Map 累加，性能差、内存高。
• PARTITION BY 是分组，ORDER BY 是排序，ROWS BETWEEN 是窗口范围。
• 窗口函数是 PostgreSQL 的核心竞争力，Java 团队必须掌握。

---

✅ 8. JSONB 查询 —— 动态配置的查询引擎

✅ 企业级场景：用户偏好、商品属性、API 配置、通知设置。

-- 📌 示例1：查询包含特定配置的用户
SELECT username, metadata->>'theme' AS theme
FROM users 
WHERE metadata @> '{"notifications": {"email": true}}';  -- ✅ 包含键值对

-- 📌 示例2：查询 JSONB 中某个键是否存在
SELECT username, metadata
FROM users 
WHERE metadata ? 'fontSize';  -- ✅ 存在 key 'fontSize'

-- 📌 示例3：提取嵌套字段
SELECT 
    username,
    metadata->'settings'->>'theme' AS theme,
    metadata->'settings'->'notifications'->>'email' AS email_notify
FROM users 
WHERE metadata->'settings' ? 'theme';

-- 📌 示例4：模糊匹配 JSONB 中的字符串值
SELECT username
FROM users 
WHERE metadata->>'email' ILIKE '%@company.com';  -- ✅ 忽略大小写匹配

-- 📌 示例5：聚合 JSONB 字段（统计不同语言用户数）
SELECT 
    metadata->>'language' AS lang,
    COUNT(*) AS user_count
FROM users 
WHERE metadata ? 'language'
GROUP BY metadata->>'language'
ORDER BY user_count DESC;

✅ 团队规范：

• 所有动态字段统一用 JSONB，禁止新增字段。
• 必须为 JSONB 字段建立 GIN 索引：CREATE INDEX idx_users_metadata ON users USING GIN (metadata);
• 查询使用 @>、?、->>，禁止用 LIKE 或 CONTAINS。
• Java 中使用 ObjectMapper 或 JsonNode 映射，避免手动解析。

---

✅ 9. 全文检索（Full-Text Search）—— 替代 LIKE '%xxx%'

🚫 错误写法：

SELECT title FROM articles WHERE content LIKE '%spring boot%';  -- ❌ 慢如蜗牛

✅ 推荐写法：

-- 📌 示例1：创建全文索引（必须！）
CREATE INDEX idx_articles_content_ts ON articles USING GIN (to_tsvector('english', content));

-- 📌 示例2：使用全文检索（高效！）
SELECT 
    title,
    ts_headline('english', content, to_tsquery('english', 'spring & boot')) AS snippet
FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'spring & boot');

-- ✅ 说明：
-- - `to_tsvector`：将文本转为词项向量
-- - `to_tsquery`：将查询词转为布尔查询
-- - `@@`：匹配操作符
-- - `ts_headline`：高亮匹配片段（用于前端展示）

-- 📌 示例3：支持中文（需安装 zhparser 插件）
CREATE INDEX idx_articles_content_zh ON articles USING GIN (to_tsvector('zhparser', content));

SELECT title 
FROM articles 
WHERE to_tsvector('zhparser', content) @@ to_tsquery('zhparser', 'Spring & Boot');

✅ 团队规范：

• 所有“模糊搜索”（商品名、文章标题、用户备注）必须用全文检索。
• 禁止使用 LIKE '%xxx%'，性能差 100 倍以上。
• 使用 tsvector + tsquery + GIN 索引组合。
• 支持中文需安装 zhparser 插件（阿里云 RDS 已内置）。

---

四、DQL 企业级最佳实践与避坑指南（Java 开发者必看）

项目	推荐做法	禁止行为
✅ 字段选择	显式列出字段，禁止 SELECT *	SELECT *
✅ 过滤条件	WHERE 字段必须有索引	无索引的 LIKE、!=、IS NULL
✅ JOIN	使用显式 JOIN，避免逗号连接	FROM a, b WHERE a.id = b.id
✅ 分页	必须使用 LIMIT + OFFSET，大数据量用游标分页	无分页、OFFSET > 10000
✅ 聚合	用 GROUP BY + SUM/COUNT，禁止 Java 循环	Java 中循环聚合
✅ 复杂查询	使用 CTE 拆解逻辑，提升可读性	单条超长 SQL
✅ 排序	ORDER BY 字段必须有索引	无索引排序
✅ JSONB	使用 @>、->>、?，建 GIN 索引	LIKE '%xxx%'、不建索引
✅ 全文搜索	使用 to_tsvector + @@ + GIN 索引	LIKE '%xxx%'
✅ 性能验证	所有查询必须用 EXPLAIN ANALYZE 检查	不检查执行计划
✅ 事务隔离	查询不加锁，使用 READ COMMITTED	误用 FOR UPDATE
✅ API 层	查询结果映射到 DTO，不返回 Entity	直接返回 JPA 实体

---

五、实战场景：用户中心查询最佳实践（完整链路）

场景：前端请求“活跃用户列表 + 最近订单 + 消费排名”

-- ✅ 一次查询，完成所有需求（Java 一次调用）
WITH active_users AS (
    SELECT id, username, email, created_at
    FROM users 
    WHERE status = 'active'
),
user_orders AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent,
        MAX(created_at) AS last_order_at
    FROM orders 
    WHERE status = 'paid'
    GROUP BY user_id
),
ranked_users AS (
    SELECT 
        au.id,
        au.username,
        au.email,
        COALESCE(uo.order_count, 0) AS order_count,
        COALESCE(uo.total_spent, 0) AS total_spent,
        uo.last_order_at,
        ROW_NUMBER() OVER (ORDER BY uo.total_spent DESC NULLS LAST) AS rank
    FROM active_users au
    LEFT JOIN user_orders uo ON au.id = uo.user_id
)
SELECT 
    id,
    username,
    email,
    order_count,
    total_spent,
    last_order_at,
    rank
FROM ranked_users
WHERE rank <= 50  -- ✅ 只取前50名
ORDER BY rank
LIMIT 20 OFFSET 0;

✅ Java 代码对应：

@Query(nativeQuery = true, value = """
    WITH active_users AS (...) 
    SELECT ... FROM ranked_users WHERE rank <= 50 ORDER BY rank LIMIT :limit OFFSET :offset
""")
Page<UserRankingDTO> findTopActiveUsers(Pageable pageable);

💡 价值：

• 一次 SQL 查询，完成 5 个业务逻辑。
• 避免 N+1 查询、避免 Java 内存聚合、避免多次数据库访问。
• 性能提升 10x，代码简洁，易于维护。

---

六、附录：DQL 审查清单（团队 Code Review 必查项）

检查项	是否通过	说明
✅ 是否显式列出字段？	☐	禁止 SELECT *
✅ WHERE 条件是否有索引？	☐	用 EXPLAIN ANALYZE 验证
✅ 是否使用 LIMIT 和 OFFSET？	☐	所有 API 必须分页
✅ 是否使用 ORDER BY？	☐	排序字段必须有索引
✅ 是否使用 JOIN 而非逗号？	☐	明确关联关系
✅ 是否使用 CTE 拆解复杂逻辑？	☐	超过 3 表 JOIN 必须用
✅ 是否使用窗口函数？	☐	排行榜、累计统计必须用
✅ JSONB 查询是否用 @>、->>？	☐	禁止 LIKE
✅ 是否使用全文检索？	☐	搜索功能必须用 to_tsvector
✅ 是否避免 SELECT COUNT(*) 做分页？	☐	分页用 LIMIT，总数用独立查询
✅ 是否避免 OFFSET > 10000？	☐	大偏移用游标分页

---

七、总结：DQL 的三条铁律

铁律	说明
🔍 1. 所有查询必须显式指定字段，禁止 SELECT *	减少网络传输，提升性能，增强可维护性
🚀 2. 所有过滤、排序、关联字段必须有索引	无索引 = 全表扫描 = 系统雪崩
🧠 3. 所有聚合、排名、复杂逻辑必须用 SQL 实现，禁止 Java 循环	数据库是高性能计算引擎，不是数据搬运工

✅ 终极建议：
让数据库做它最擅长的事：快速、精准、安全地返回数据。
让 Java 做它最擅长的事：协调服务、处理业务、返回 API。

---

📌 下一步行动建议：

1. 将本文档作为团队《DQL 编码规范》核心章节，加入代码审查清单。
2. 在 GitLab CI 中集成 pganalyze 或 pg_stat_statements，自动监控慢查询。
3. 组织一次“DQL 性能优化实战”培训，用真实慢查询案例（如 LIKE '%xxx%'）现场演示优化过程。
4. 为所有 API 查询编写“查询性能测试用例”，使用 TestContainers + JMeter 压测。