2. 首页 > 国产数据库教程 > openGauss教程 > 正文

opengauss教程FG035-openGauss集合运算与分页优化

教程整理：风哥教程  |  更新时间：2026-04-11  |  教程分类：openGauss教程  |  文档学习：20

内容简介

本篇文章详细介绍openGauss数据库的集合运算与分页优化，包括集合运算的语法、类型、使用场景以及分页查询的优化方法。风哥教程参考opengauss官方文档SQL参考手册和性能调优指南。

集合运算和分页查询是SQL中常用的操作，掌握它们的使用方法和优化技巧对于提高数据库查询性能至关重要。通过本文的学习，您将掌握openGauss集合运算和分页查询的最佳实践。

本文通过实战案例，详细讲解集合运算的使用方法、分页查询的实现方式以及相关的性能优化策略，帮助您在生产环境中高效使用这些功能。

目录大纲

Part01-基础概念与理论知识

1.1 集合运算概述

集合运算是指对两个或多个结果集进行操作，返回一个新的结果集。openGauss支持的集合运算包括UNION、UNION ALL、INTERSECT和EXCEPT。

集合运算的主要特点：

  • 操作对象是结果集，而不是表
  • 要求参与运算的结果集具有相同的列数和数据类型
  • 可以组合多个查询的结果
  • 可以用于数据合并、去重、比较等场景

1.2 集合运算类型

openGauss支持的集合运算类型包括：

  1. UNION：合并两个结果集，并去除重复行
  2. UNION ALL：合并两个结果集，保留重复行
  3. INTERSECT：返回两个结果集的交集
  4. EXCEPT：返回第一个结果集减去第二个结果集的差集

1.3 分页查询概述

分页查询是指将查询结果分成若干页，每次只返回一页的数据。openGauss支持使用LIMIT和OFFSET子句实现分页查询。

分页查询的主要特点：

  • 减少网络传输数据量
  • 提高查询响应速度
  • 改善用户体验
  • 降低数据库负载

Part02-生产环境规划与建议

2.1 集合运算使用场景

集合运算适用于以下场景：

  • 数据合并：合并多个表或查询的结果
  • 数据去重：去除重复数据
  • 数据比较：比较两个结果集的差异
  • 数据筛选：根据多个条件筛选数据
  • 报表生成：生成包含多个数据源的报表

2.2 分页查询使用场景

分页查询适用于以下场景：

    风哥提示：

  • Web应用：分页显示数据
  • 大数据量查询：避免一次性返回过多数据
  • 后台管理系统：分页展示管理数据
  • 数据导出：分批导出数据
  • API接口：限制返回数据量

2.3 性能影响因素

影响集合运算和分页查询性能的主要因素包括：

  • 数据量：数据量越大，集合运算和分页查询的开销越大
  • 索引：合适的索引可以提高查询性能
  • 排序操作：ORDER BY子句会增加排序开销
  • 集合运算类型：UNION ALL比UNION更高效，因为它不需要去重
  • OFFSET值：OFFSET值越大，分页查询的性能越差

Part03-生产环境项目实施方案

3.1 集合运算优化策略

集合运算的优化策略包括：

  • 使用UNION ALL代替UNION：如果不需要去重，使用UNION ALL可以提高性能
  • 优化子查询：确保集合运算中的子查询有合适的索引
  • 限制结果集大小：使用LIMIT子句限制返回结果的数量
  • 避免复杂的集合运算：对于复杂的集合运算，考虑使用临时表
  • 合理使用索引：为集合运算中的条件列创建合适的索引

学习交流加群风哥微信: itpux-com

3.2 分页查询优化策略

分页查询的优化策略包括：

  • 使用索引覆盖：为ORDER BY列和WHERE条件列创建索引
  • 使用ROW_NUMBER()函数：对于复杂的分页查询，使用ROW_NUMBER()函数可能更高效
  • 避免大OFFSET：大OFFSET会导致数据库扫描大量数据，性能较差
  • 使用键集分页：使用上一页的最后一条记录的键值作为下一页的起始条件
  • 合理设置页面大小：根据业务需求设置合适的页面大小

3.3 复杂查询处理方法

处理复杂查询的方法包括：

  • 分解复杂查询：将复杂查询分解为多个简单查询
  • 使用临时表：将中间结果存储在临时表中
  • 使用CTE：使用CTE提高查询的可读性和性能
  • 优化索引：为查询中的列创建合适的索引
  • 使用并行查询：对于大型查询，考虑使用并行查询

Part04-生产案例与实战讲解

4.1 集合运算实战

UNION操作

— 创建测试表
fgedudb=> CREATE TABLE fgedu_employee_it (
emp_id INTEGER PRIMARY KEY,
emp_name VARCHAR(50),
department VARCHAR(50),
salary INTEGER
);

CREATE TABLE

学习交流加群风哥QQ113257174
— 插入数据
fgedudb=> INSERT INTO fgedu_employee_it VALUES
(1, ‘张三’, ‘技术部’, 8800),
(2, ‘李四’, ‘市场部’, 6000),
(3, ‘王五’, ‘财务部’, 9000);

INSERT 0 3

— 创建另一个测试表
fgedudb=> CREATE TABLE fgedu_employee_sales (
emp_id INTEGER PRIMARY KEY,
emp_name VARCHAR(50),
department VARCHAR(50),
salary INTEGER
);

CREATE TABLE

— 插入数据
fgedudb=> INSERT INTO fgedu_employee_sales VALUES
(2, ‘李四’, ‘市场部’, 6000),
(4, ‘赵六’, ‘销售部’, 7500),
(5, ‘孙七’, ‘销售部’, 6800);

INSERT 0 3

— 使用UNION合并两个表的数据（去重）
fgedudb=> SELECT * FROM fgedu_employee_it
UNION
SELECT * FROM fgedu_employee_sales;

emp_id | emp_name | department | salary
——–+———-+————+——–更多视频教程www.fgedu.net.cn
1 | 张三 | 技术部 | 8800
2 | 李四 | 市场部 | 6000
3 | 王五 | 财务部 | 9000
4 | 赵六 | 销售部 | 7500
5 | 孙七 | 销售部 | 6800
(5 rows)

UNION ALL操作

— 使用UNION ALL合并两个表的数据（保留重复）
fgedudb=> SELECT * FROM fgedu_employee_it
UNION ALL
SELECT * FROM fgedu_employee_sales;

emp_id | emp_name | department | salary
——–+———-+————+——–
1 | 张三 | 技术部 | 8800
2 | 李四 | 市场部 | 6000
3 | 王五 | 财务部 | 9000
2 | 李四 | 市场部 | 6000
4 | 赵六 | 销售部 | 7500
5 | 孙七 | 销售部 | 6800
(6 rows)

INTERSECT操作

— 使用INTERSECT获取两个表的交集
fgedudb=> SELECT * FROM fgedu_employee_it
INTERSECT
SELECT * FROM fgedu_employee_sales;

emp_id | emp_name | department | salary
——–+———-+————+——–
2 | 李四 | 市场部 | 6000
(1 row)

更多学习教程公众号风哥教程itpux_com

EXCEPT操作

— 使用EXCEPT获取第一个表减去第二个表的差集
fgedudb=> SELECT * FROM fgedu_employee_it
EXCEPT
SELECT * FROM fgedu_employee_sales;

emp_id | emp_name | department | salary
——–+———-+————+——–
1 | 张三 | 技术部 | 8800
3 | 王五 | 财务部 | 9000
(2 rows)

4.2 分页查询实战

基本分页查询

— 分页查询员工数据，每页10条，第1页
fgedudb=> SELECT emp_id, emp_name, department, salary
FROM fgedu_employee
ORDER BY emp_id
LIMIT 10 OFFSET 0;

emp_id | emp_name | department | salary
——–+———-+————+——–
1 | 张三 | 技术部 | 8800
2 | 李四 | 市场部 | 6000from DB视频:www.itpux.com
3 | 王五 | 财务部 | 9000
4 | 赵六 | 技术部 | 8025
5 | 孙七 | 市场部 | 6500
6 | 周八 | 技术部 | 7350
7 | 吴九 | 市场部 | 6800
8 | 郑十 | 财务部 | 8500
9 | 王十一 | 技术部 | 7560
10 | 赵十二 | 市场部 | 6900
(10 rows)

— 分页查询员工数据，每页10条，第2页
fgedudb=> SELECT emp_id, emp_name, department, salary
FROM fgedu_employee
ORDER BY emp_id
LIMIT 10 OFFSET 10;

emp_id | emp_name | department | salary
——–+———-+————+——–
11 | 孙十三 | 技术部 | 7665
12 | 李十四 | 市场部 | 6700
13 | 周十五 | 财务部 | 8600
14 | 吴十六 | 技术部 | 7245
(4 rows)

使用ROW_NUMBER()函数进行分页

— 使用ROW_NUMBER()函数进行分页
fgedudb=> SELECT * FROM (
SELECT emp_id, emp_name, department, salary,
ROW_NUMBER() OVER (ORDER BY emp_id) AS row_num
FROM fgedu_employee
) AS t
WHERE row_num BETWEEN 11 AND 20;

emp_id | emp_name | department | salary | row_num
——–+———-+————+——–+———+
11 | 孙十三 | 技术部 | 7665 | 11
12 | 李十四 | 市场部 | 6700 | 12
13 | 周十五 | 财务部 | 8600 | 13
14 | 吴十六 | 技术部 | 7245 | 14
(4 rows)

使用键集分页

— 第一页数据
fgedudb=> SELECT emp_id, emp_name, department, salary
FROM fgedu_employee
ORDER BY emp_id
LIMIT 10;

emp_id | emp_name | department | salary
——–+———-+————+——–
1 | 张三 | 技术部 | 8800
2 | 李四 | 市场部 | 6000
3 | 王五 | 财务部 | 9000
4 | 赵六 | 技术部 | 8025
5 | 孙七 | 市场部 | 6500
6 | 周八 | 技术部 | 7350
7 | 吴九 | 市场部 | 6800
8 | 郑十 | 财务部 | 8500
9 | 王十一 | 技术部 | 7560
10 | 赵十二 | 市场部 | 6900
(10 rows)

— 第二页数据（使用上一页的最后一个emp_id作为条件）
fgedudb=> SELECT emp_id, emp_name, department, salary
FROM fgedu_employee
WHERE emp_id > 10
ORDER BY emp_id
LIMIT 10;

emp_id | emp_name | department | salary
——–+———-+————+——–
11 | 孙十三 | 技术部 | 7665
12 | 李十四 | 市场部 | 6700
13 | 周十五 | 财务部 | 8600
14 | 吴十六 | 技术部 | 7245
(4 rows)

4.3 集合运算与分页查询组合实战

— 集合运算与分页查询组合
fgedudb=> SELECT * FROM (
SELECT emp_id, emp_name, department, salary
FROM fgedu_employee_it
UNION ALL
SELECT emp_id, emp_name, department, salary
FROM fgedu_employee_sales
ORDER BY emp_id
LIMIT 10 OFFSET 0
) AS t;

emp_id | emp_name | department | salary
——–+———-+————+——–
1 | 张三 | 技术部 | 8800
2 | 李四 | 市场部 | 6000
2 | 李四 | 市场部 | 6000
3 | 王五 | 财务部 | 9000
4 | 赵六 | 销售部 | 7500
5 | 孙七 | 销售部 | 6800
(6 rows)

4.4 性能对比测试

UNION vs UNION ALL性能对比

— 测试UNION性能
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee_it
UNION
SELECT * FROM fgedu_employee_sales;

QUERY PLAN
—————————————————————————————————————————–
Sort (cost=45.75..45.77 rows=5 width=36) (actual time=0.062..0.063 rows=5 loops=1)
Sort Key: fgedu_employee_it.emp_id, fgedu_employee_it.emp_name, fgedu_employee_it.department, fgedu_employee_it.salary
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=45.63..45.68 rows=5 width=36) (actual time=0.045..0.047 rows=5 loops=1)
Group Key: fgedu_employee_it.emp_id, fgedu_employee_it.emp_name, fgedu_employee_it.department, fgedu_employee_it.salary
-> Append (cost=0.00..45.50 rows=6 width=36) (actual time=0.010..0.025 rows=6 loops=1)
-> Seq Scan on fgedu_employee_it (cost=0.00..22.75 rows=3 width=36) (actual time=0.010..0.012 rows=3 loops=1)
-> Seq Scan on fgedu_employee_sales (cost=0.00..22.75 rows=3 width=36) (actual time=0.008..0.010 rows=3 loops=1)
Planning Time: 0.112 ms
Execution Time: 0.094 ms
(10 rows)

— 测试UNION ALL性能
fgedudb=> EXPLAIN ANALYZE
SELECT * FROM fgedu_employee_it
UNION ALL
SELECT * FROM fgedu_employee_sales;

QUERY PLAN
———————————————————————————————————-
Append (cost=0.00..45.50 rows=6 width=36) (actual time=0.010..0.023 rows=6 loops=1)
-> Seq Scan on fgedu_employee_it (cost=0.00..22.75 rows=3 width=36) (actual time=0.010..0.012 rows=3 loops=1)
-> Seq Scan on fgedu_employee_sales (cost=0.00..22.75 rows=3 width=36) (actual time=0.008..0.010 rows=3 loops=1)
Planning Time: 0.054 ms
Execution Time: 0.050 ms
(5 rows)

传统分页 vs 键集分页性能对比

— 测试传统分页性能（大OFFSET）
fgedudb=> EXPLAIN ANALYZE
SELECT emp_id, emp_name, department, salary
FROM fgedu_employee
ORDER BY emp_id
LIMIT 10 OFFSET 100000;

QUERY PLAN
————————————————————————————————————————
Limit (cost=1178.44..1178.46 rows=10 width=36) (actual time=1.234..1.236 rows=0 loops=1)
-> Index Only Scan using fgedu_employee_pkey on fgedu_employee (cost=0.29..1178.46 rows=14 width=36) (actual time=0.014..0.023 rows=14 loops=1)
Heap Fetches: 0
Planning Time: 0.080 ms
Execution Time: 1.250 ms
(5 rows)

— 测试键集分页性能
fgedudb=> EXPLAIN ANALYZE
SELECT emp_id, emp_name, department, salary
FROM fgedu_employee
WHERE emp_id > 100000
ORDER BY emp_id
LIMIT 10;

QUERY PLAN
————————————————————————————————————————
Limit (cost=0.29..22.79 rows=10 width=36) (actual time=0.012..0.013 rows=0 loops=1)
-> Index Only Scan using fgedu_employee_pkey on fgedu_employee (cost=0.29..22.79 rows=14 width=36) (actual time=0.012..0.013 rows=0 loops=1)
Index Cond: (emp_id > 100000)
Heap Fetches: 0
Planning Time: 0.073 ms
Execution Time: 0.030 ms
(5 rows)

Part05-风哥经验总结与分享

5.1 集合运算最佳实践

  • 优先使用UNION ALL：如果不需要去重，使用UNION ALL可以提高性能
  • 优化子查询：确保集合运算中的子查询有合适的索引
  • 限制结果集大小：使用LIMIT子句限制返回结果的数量
  • 避免复杂的集合运算：对于复杂的集合运算，考虑使用临时表
  • 合理使用索引：为集合运算中的条件列创建合适的索引
  • 注意数据类型兼容性：确保参与集合运算的结果集具有相同的列数和数据类型

5.2 分页查询最佳实践

  • 使用索引覆盖：为ORDER BY列和WHERE条件列创建索引
  • 避免大OFFSET：大OFFSET会导致数据库扫描大量数据，性能较差
  • 使用键集分页：对于大表，使用键集分页可以显著提高性能
  • 合理设置页面大小：根据业务需求设置合适的页面大小
  • 使用ROW_NUMBER()函数：对于复杂的分页查询，使用ROW_NUMBER()函数可能更高效
  • 考虑缓存：对于频繁访问的数据，考虑使用缓存

5.3 生产环境使用建议

  • 监控查询性能：使用数据库监控工具，监控包含集合运算和分页查询的慢查询
  • 定期分析执行计划：了解集合运算和分页查询的执行情况，找出优化空间
  • 优化热点查询：重点优化频繁执行的包含集合运算和分页查询的查询
  • 合理设计表结构：根据查询需求设计表结构，避免复杂的集合运算
  • 定期维护数据库：执行VACUUM、ANALYZE等操作，保持数据库健康状态
  • 使用物化视图：对于频繁执行的复杂查询，使用物化视图

风哥提示：在生产环境中，集合运算和分页查询是常用的操作，但需要合理使用。要根据数据量和性能要求，选择合适的集合运算类型和分页查询方法，并进行适当的优化。

在使用分页查询时，要注意避免大OFFSET，风哥提示：对于大表，使用键集分页可以显著提高性能。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息