GaussDB SQL查询与子查询：从入门到性能调优

Gauss松鼠会 2025-03-19

153

一、引言

GaussDB 是一款面向云原生设计的分布式关系型数据库，支持丰富的 SQL 功能。子查询（Subquery）作为 SQL 中强大的工具，能够帮助开发者灵活处理复杂业务逻辑。然而，在分布式场景下，子查询的性能表现和优化策略与传统单机数据库存在显著差异。本文将深入探讨 GaussDB 中子查询的语法特性、执行机制及优化方法。

二、基础语法与分类

子查询的基本形式
GaussDB 支持以下两种子查询语法：

-- 标量子查询（返回单个值）
SELECT name 
FROM users 
WHERE age = (SELECT AVG(age) FROM users WHERE city = 'Shanghai');

-- 表子查询（返回多行多列）
SELECT o.order_id, c.customer_name 
FROM orders o 
JOIN (
    SELECT customer_id, COUNT(*) AS order_count 
    FROM orders 
    GROUP BY customer_id 
    HAVING COUNT(*) > 5
) c ON o.customer_id = c.customer_id;

子查询的分类
类型描述
非关联子查询独立于外层查询执行，结果作为常量传递给外层。
关联子查询与外层查询通过 IN, EXISTS, NOT IN 等关键字关联，逐行处理。
嵌套子查询多层嵌套结构，需从内到外依次执行。

三、高级用法与技巧

IN/EXISTS 的性能差异
场景对比
IN 子查询：适用于外层结果集较小的场景，内部查询会被缓存。
EXISTS 子查询：适用于外层结果集较大但内部查询条件严格的场景，提前终止扫描。
示例优化

-- 低效写法：使用 IN（外层大表）
SELECT * 
FROM products p 
WHERE p.category_id IN (
    SELECT category_id 
    FROM categories c 
    WHERE c.region = 'Asia'
);

-- 优化写法：改用 EXISTS（外层小表）
SELECT * 
FROM categories c 
WHERE c.region = 'Asia' 
AND EXISTS (
    SELECT 1 
    FROM products p 
    WHERE p.category_id = c.category_id
);

WITH 子句（公共表表达式 CTE）
GaussDB 支持 CTE，可将复杂子查询模块化，提升代码可读性：

WITH sales_summary AS (
    SELECT product_id, SUM(revenue) AS total_sales 
    FROM sales 
    GROUP BY product_id
)
SELECT s.product_id, s.total_sales, 
       (SELECT AVG(sales.revenue) FROM sales WHERE product_id = s.product_id) AS avg_revenue
FROM sales_summary s;

窗口函数与子查询结合
GaussDB 支持在子查询中使用窗口函数，但需注意执行顺序：

-- 查询每个用户的销售额排名
SELECT user_id, revenue,
       RANK() OVER (ORDER BY revenue DESC) AS rank
FROM (
    SELECT user_id, SUM(amount) AS revenue 
    FROM orders 
    GROUP BY user_id
) sub;

四、分布式场景下的性能优化

子查询的执行计划分析
GaussDB 的 EXPLAIN 命令可帮助分析子查询的执行策略：

EXPLAIN SELECT * 
FROM employees e 
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department = 'Engineering'
);

避免全表扫描的优化策略
索引优化
确保子查询涉及的列有索引：

CREATE INDEX idx_salary ON employees(salary);

限制子查询结果集大小
使用 LIMIT 或 OFFSET 缩小返回数据量：

SELECT name 
FROM users 
WHERE id IN (
    SELECT user_id 
    FROM logs 
    WHERE action = 'login' 
    LIMIT 1000
);

利用物化视图预计算子查询结果
对于重复复杂的子查询，可创建物化视图缓存结果：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT product_id, SUM(revenue) AS total_sales 
FROM sales 
GROUP BY product_id;

-- 定期刷新数据
REFRESH MATERIALIZED VIEW mv_sales_summary;

五、实战案例

案例 1：电商用户行为分析
需求
统计购买过某商品且浏览过该商品详情页的用户数量。

实现方案

SELECT COUNT(DISTINCT user_id) 
FROM users u 
WHERE EXISTS (
    SELECT 1 
    FROM purchases p 
    WHERE p.user_id = u.id AND p.product_id = 1001
) 
AND EXISTS (
    SELECT 1 
    FROM page_views pv 
    WHERE pv.user_id = u.id AND pv.product_id = 1001
);

优化点：

使用 EXISTS 替代 IN 提高效率。
确保 purchases 和 page_views 表对 user_id 和 product_id 建立联合索引。

案例 2：金融风控中的实时交易监控
需求
查询过去 1 小时内交易金额超过阈值的用户，并关联其风险等级。