1 引言
天津南大通用数据技术股份有限公司（简称：GBASE）成立于2004年，注册资金1.88亿元。公司自成立以来始终坚持自主创新，产品的核心技术及底层代码自主可控，构建了覆盖数据管理全生命周期，包括分析型、事务型、分布式事务型、云原生数据仓库等全技术栈的数据产品体系及服务解决方案。
南大通用自主研发的GBase系列数据库产品及服务范围覆盖全国33个省级行政区域。为金融、电信、政务、能源交通、国防军工等百余个行业上万家用户提供产品和服务，建立节点超过10万个，管理数据总量超过500PB，并远销美国、巴西、墨西哥等五十余个国家及地区。
GBase 8s是天津南大通用数据技术股份有限公司自主研发的、成熟稳定的基于共享存储的数据库集群，拥有自主知识产权。产品达到安全数据库四级标准（国际B2），支持国密算法，支持SQL92/99、ODBC、JDBC、ADO.NET、GCI(OCI/OCCI)、Python接口等国际数据库规范和开发接口。支持集中式部署、共享存储高可用部署、两地三中心高可用部署，具备高容量、高并发、高性能等特性。
GBase 8s适用于OLTP 应用场景，包括金融、电信行业的关键核心业务系统，安全、党政、国防等行业对信息安全性有较高要求的信息系统，以及大型企业的经营类、管理类信息系统，能够提供7*24小时不间断运行处理能力，在80%以上场景中可以替代国际主流数据库。
1.1 产品定位
南大通用安全数据库管理系统（简称 GBase 8s）V8.8，支持行存、列存、内存等多种存储模式，支持单机、集中式部署。GBase 8s V8.8 具备极高性能、极高可用、极致安全、高兼容性等特性，适用于大并发、大数据量、以联机事务处理为主的交易型场景；以及交易和分析并重的 HTAP 场景等。为企业提供安全、稳定、可靠的数据存储和管理服务。
- 极高性能：GBase 8s依托多核架构的并发控制技术，结合鲲鹏硬件优化，并在内核关键结构上采用了Numa-Aware的数据结构，提供集群极高性能能力。
- 极高可用：GBase8s主备式形态提供主备同步、异步以及级联备机多种部署方式。业务保持连续在线，数据永不丢失。
- 极致弹性：GBase 8s支持在线扩缩容，计算存储分离的架构能够保证集群计算和存储能力随节点数的增加可以线性提升。
- 极致安全：GBase 8s支持全密态计算能力、支持访问控制、加密认证、数据库审计、动态脱敏等安全特性，提供全方位端到端的数据安全保护。
- 高兼容性：GBase8s支持标准SQL语法，完全兼容PostgreSQL语法，高度兼容Oracle语法
1.2 应用场景
1 互联网业务
随着互联网业务，尤其是移动互联网业务的飞速发展，互联网应用对海量数据下数据库的并发量、吞吐量等性能要求越来越高。并且，为了支撑秒杀等特定场景的极端性能需求，自动伸缩也成为互联网应用的必要需求。
- GBase 8s的弹性伸缩能力，可以根据业务需求进行在线扩容缩容，完美应对极端业务场景的性能需求。
- 灰度发布的特性，为互联网业务频繁上下线提供了有力支撑。随着云计算和虚拟化技术的不断深入发展，政企业务上云成为主流的架构选择。
- GBase 8s支持物理机、容器、私有云、公有云多种部署方式，为政企业务上云提供了灵活的选择。
- 支持支持行存、列存、内存三种存储模式，支持单机、主备部署形态，满足用户各种业务需求。
- GBase 8s支持数据传输加密、存储加密，对云上数据安全提供了有力的保证
1.3 技术特点
作为一款关系型数据库产品，GBase 8s具有计算存储分离、灵活的部署方式、在线扩容缩容、在线升级、数据高可用、高安全性、异地多活、数据高效加载、集群备份恢复、易维护、标准化、兼容国产生态等技术特点。
- 灵活的部署方式 :用户可以根据自身IT基础设施建设情况，结合性能、便捷、安全等需求的综合考量，选择将GBase 8s部署在不同的环境。GBase 8s支持物理机部署、虚拟机部署、容器部署、私有云部署和公有云部署。
- 多存储模式：GBase 8s 支持多种存储模式以满足不同场景的业务需求：
- 行存储引擎：主要面向OLTP场景设计，例如订货、发货、银行交易系统；
- 列存储引擎：主要面向OLAP场景设计，例如数据统计报表分析系统；
- 内存引擎：主要面向极致性能场景设计，例如银行风控场景。
- 数据高可用:GBase 8s 通过冗余机制来保证集群的高可用特性，多个数据副本之间采用主从复制的方式来保证数据的一致性。GBase 8s支持同步、异步和半同步复制模式，让用户可以在高可用和高性能之间寻求平衡。GBase 8s支持故障的自动恢复，无需人工干预。
- 安全性:
- GBase 8s 提供完善的用户、角色、权限控制策略，提高数据库集群的安全性；
- 支持详尽的审计日志，可配置灵活的审计策略，记录数据库中与数据库操作相关的所有日志，也可以通过图形化的监视工具实现审计管理；
- 支持多种加密策略：支持列级、表级，以及库级加密；
- 支持主流加密函数，如 AES、MD5、SHA1、SHA等国际标准算法，以及SM3、SM4等国密算法。
- 异地多活:
- GBase 8s 提供异地多活的部署方式，满足对高可用有严苛要求的业务需求。采用异地多活的高可用部署方式，可以达到RPO=0，RTO秒级的高可用等级，提供机房级容灾能力和城市级容灾能力。
- 数据高效加载:GBase 8s 基于策略的数据加载模式，能够充分发挥所有节点的计算能力，保证数据在加载速度和访问性能之间的平衡。
- 易维护：提供图形化的部署配置、运维监控、数据同步、备份恢复等运维功能，降低运维难度，提升运维效率。
- 标准化：支持SQL92、SQL99、SQL2003 ANSI/ISO 标准，支持 ODBC、JDBC、ADO.NET 等接口规范；支持 C API、Python API等接口。
- 多种生态支持：GBase 8s 数据库具有丰富的周边生态支持：
- 支持强大的地理信息系统（GIS）。GBase8s支持PostGis插件，可以有效支持空间地理数据存储，作为空间数据库，高效管理空间数据、数据测量和几何拓扑分析。
- 支持非关系数据类型JSON。
- 支持ForeignDataWrappers（FDW）。GBase 8s通过FDW支持用户通过SQL访问数据库集群之外的数据。FDW功能提供一套编程接口，用户可以进行插件式的二次开发，建立外部数据源和数据库间的数据通道。当前GBase 8s支持oracle_fdw、mysql_fdw、postgres_fdw，以及非关系型数据库redis_fdw、mongodb_fdw，大数据hive_fdw、hdfs_fdw等。通过FDW，GBase 8s能够访问已有的多个数据源的数据。
- 兼容国产生态。GBase8s兼容飞腾、鲲鹏、海光等国产CPU；兼容麒麟、统信等国产操作系统；兼容基于浪潮、泰山、华三、曙光等国产CPU的服务器。
1.1 Gbase8s数据库的基本概念
GBase 8s 是天津南大通用数据技术股份有限公司自主研发的、成熟稳定的企业级国产交易型数据库，拥有自主知识产权，具备高容量、高并发、高性能等特性，适用于金融、电信等关键核心业务系统及对信息安全性要求较高的行业。 以下从多个方面详细阐述其基本概念：
1.1.1 核心定位与特性
- 企业级交易型数据库：GBase 8s 专为处理高并发、高吞吐量的在线事务处理（OLTP）场景设计，能够满足金融、电信等行业对数据一致性、实时性和可靠性的严苛要求。
- 自主可控：作为国产数据库，GBase 8s 拥有完全自主的知识产权，支持国密算法，达到国标第四级安全标准（国际 B2 级），确保数据主权和安全可控。
- 高性能与高并发：通过多进程多线程架构、基于共享内存的优化设计，以及高效的物理/逻辑日志管理机制，GBase 8s 能够实现百 TB 级数据处理能力，支持每秒数万级并发事务处理。
1.1.2 技术架构与关键组件
多进程多线程架构：
- 类似 Oracle 的内存架构，采用基于共享内存（Shared Memory）的“VP”（Virtual Processor）进程/线程结构管理。
- 通过共享内存段（包括常驻内存段、Buffer Pool 缓存池段、虚拟内存段、消息段）实现高效的数据缓存和进程间通信。
存储结构：
- 物理存储层级：块（最大 4TB）→段→页（多数 UNIX 平台为 2KB），块用于分配大容量磁盘空间，页为最小读写单元。
- 日志管理：
- 物理日志：记录数据页修改前的“前映像”，支持异常掉电恢复和事务回滚（类似 Oracle 的 undo 日志）。
- 逻辑日志：记录所有数据操作，用于主备复制和故障恢复（类似 Oracle 的 redo 日志）。
- 特殊存储空间：
- 临时数据库空间：存储临时表，不记录日志，服务器关闭后内容自动清除。
- BLOB Space：存储 TEXT/BYTE 类型的大对象，数据直接写入磁盘，不经过共享内存。
- 智能大对象空间：支持 CLOB/BLOB 及用户定义类型，数据写入物理/逻辑日志，可恢复。
高可用与灾备方案：
- 共享存储集群（SSC）：通过共享磁盘实现计算节点高可用，数据单副本存储，支持最多 128 个节点，故障切换时间小于 1 分钟。
- 高可用集群（HAC）：基于逻辑日志复制技术，支持同城容灾（一主一从）和异地灾备（一主多备），提供同步、半同步、异步三种模式，故障时自动切换。
- 两地三中心部署：结合 SSC 和 HAC，实现跨地域的数据冗余和业务连续性保障。
1.4 Gbase8a技术架构演进
GBase 8s 作为一款企业级国产交易型数据库，其技术架构经历了持续演进，以适应现代硬件环境和业务场景的高要求。以下从架构基础、存储引擎、高可用集群、部署灵活性等维度，详细阐述其技术架构的演进过程：
1.4.1 架构基础：从单点到分布式扩展
初始架构：
- GBase 8s 最初采用基于共享内存（Shared Memory）的多进程多线程架构，与 Oracle 的内存架构相似。
- 通过“VP”（Virtual Processor）进程/线程结构管理，实现高效的进程间通信和资源调度。
- 物理日志和逻辑日志的存储管理机制，确保数据的一致性和可恢复性。
分布式扩展：
- 随着业务规模的增长，GBase 8s 逐步引入分布式架构，支持多节点集群部署。
- 共享存储集群（SSC）采用共享磁盘方式实现计算节点高可用，数据仅存储一份，有效利用硬件资源，避免数据重复存储问题。
- SSC 集群中各节点均能对外提供服务，适合写少读多、对读性能有线性扩展需求的场景，集群节点数最多可达 128 个。
1.4.2 存储引擎：从传统到高性能的革新
传统存储引擎：
- 早期版本采用基于磁盘的存储引擎，依赖物理日志和逻辑日志实现数据的一致性和恢复。
- 通过页（Page）作为最小读写单元，在多数 UNIX 平台上页大小为 2KB，实现数据的物理存储和访问。
新一代存储引擎：
- 结合多核、高性能 IO 和大内存的现代硬件环境，GBase 8s 发布新一代存储引擎，重构事务处理机制和系统底座。
- 提供高性能多版本控制（MVCC），实现历史版本内存化（HUB 引擎），读写操作分离，提升多版本查询性能。
- 动态 GC 机制自动回收无效版本，避免长事务导致的资源泄漏，并实现 COMMITTED READ、REPEATABLE READ 隔离级别的版本访问。
- 通过全局时间戳同步，主从节点基于统一提交时间戳协调数据可见性，确保从节点查询结果严格匹配主节点的事务一致性状态。
1.4.3 高可用集群：从同城到异地的全面覆盖
同城高可用集群（HAC）：
- 采用逻辑日志复制技术实现节点高可用，支持一主一备的部署方式。
- 主节点执行读写业务的同时，备节点可承担查询、报表等读业务，提高系统整体性能。
- 支持同步、半同步和异步三种日志模式，适应不同网络环境和业务需求。
- 故障切换时间在 30 秒内完成，确保业务连续性。
异地灾备集群（RHAC）：
- 针对地震、海啸等重大自然灾害，GBase 8s 提供异地灾备集群方案。
- 采用异步传输机制，主节点负责接受和处理交易，并把交易的逻辑日志源源不断地发送到目标节点。
- 备用节点在重做逻辑日志之后向主节点返回位置信息，确保数据的一致性和完整性。
- RHAC 集群最多支持 256 个辅节点，安装部署简单快捷，且无须额外付费。
两地三中心部署：
- 结合 SSC、HAC 和 RHAC 集群，GBase 8s 提供两地三中心高可用解决方案。
- 同城部署 SSC 集群实现高可用和负载均衡，异地部署 RHAC 集群实现灾备和业务连续性保障。
- 应用程序通过连接管理器（CM）访问数据库集群，实现故障自动发现和转移，确保业务的高可用性。
1.4.4 部署灵活性：从单机到云端的全面支持
单机部署：
- GBase 8s 支持单机部署模式，适用于小型业务系统或开发测试环境。
- 单机部署简单快捷，易于管理和维护。
集群部署：
- 支持共享存储集群（SSC）、主备集群（HAC/RHAC）等多种集群部署方式。
- 集群部署提高系统可用性和性能，满足大型业务系统的需求。
云端部署：
- 随着云计算的普及，GBase 8s 提供云端部署方案，支持容器化部署和自动化运维。
- 通过 Kubernetes 等平台实现快速扩容和资源调度，降低运维成本和提高服务效率。
2 Gbase8s SQL编程入门
2.1 GBase 8s SQL 编程基础
SQL 语法特点：
- 支持标准 SQL92/99 语法，兼容 Oracle、MySQL 等数据库的常用语法。
- 提供丰富的数据类型（如 INT、CHAR、VARCHAR、DATE、CLOB/BLOB 等）。
- 支持事务控制（COMMIT/ROLLBACK）、存储过程、触发器等高级功能。
开发工具：
- dbaccess：GBase 8s 自带的命令行工具，适合快速执行 SQL 语句。
- Database Workbench：图形化工具，支持表设计、SQL 编写和调试。
- JDBC/ODBC：通过编程语言（如 Java、Python）连接数据库。
2.2 常用 SQL 语句示例
1. 数据库和表操作
-- 创建数据库
CREATE DATABASE test_db WITH BUFFERED LOG;
-- 连接到数据库
DATABASE test_db;
-- 创建表
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
salary DECIMAL(10,2),
hire_date DATE,
department VARCHAR(30)
);
-- 查看表结构
DESCRIBE employees;2. 数据操作
-- 插入数据
INSERT INTO employees (name, age, salary, hire_date, department)
VALUES ('张三', 30, 8000.00, '2020-01-15', '研发部');
-- 批量插入
INSERT INTO employees (name, age, salary, hire_date, department)
VALUES
('李四', 28, 7500.00, '2021-03-10', '市场部'),
('王五', 35, 9000.00, '2019-05-20', '研发部');
-- 查询数据
SELECT * FROM employees;
SELECT name, salary FROM employees WHERE department = '研发部';
-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department = '研发部';
-- 删除数据
DELETE FROM employees WHERE emp_id = 1;3. 高级查询
-- 排序和限制
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5;
-- 分组和聚合
SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
-- 多表连接
CREATE TABLE departments (
dept_id SERIAL PRIMARY KEY,
dept_name VARCHAR(30),
location VARCHAR(50)
);
INSERT INTO departments (dept_name, location) VALUES ('研发部', '北京'), ('市场部', '上海');
SELECT e.name, e.salary, d.dept_name, d.location
FROM employees e
JOIN departments d ON e.department = d.dept_name;4. 事务控制
-- 事务示例
BEGIN WORK;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A001';
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B002';
COMMIT WORK;
-- 如果出现错误则回滚
BEGIN WORK;
INSERT INTO orders (order_id, customer_id, amount) VALUES (1001, 'C001', 500);
-- 模拟错误（如违反约束）
-- 如果出错，执行 ROLLBACK WORK;
COMMIT WORK;5. 存储过程和函数
-- 创建存储过程
CREATE PROCEDURE raise_salary(dept_name VARCHAR(30), percent DECIMAL(5,2))
DEFINE avg_sal DECIMAL(10,2);
SELECT AVG(salary) INTO avg_sal FROM employees WHERE department = dept_name;
IF avg_sal < 10000 THEN
UPDATE employees SET salary = salary * (1 + percent/100)
WHERE department = dept_name;
LET avg_sal = avg_sal * (1 + percent/100);
END IF;
PRINT "部门 " || dept_name || " 调薪后平均工资: " || avg_sal;
END PROCEDURE;
-- 执行存储过程
EXECUTE PROCEDURE raise_salary('研发部', 10);2.3 实用技巧和注意事项
注释：使用
--进行单行注释，/* */进行多行注释。数据类型转换：使用
CAST或::操作符，如SELECT CAST(salary AS INT) FROM employees;。分页查询：GBase 8s 支持
LIMIT和OFFSET实现分页，如SELECT * FROM employees LIMIT 10 OFFSET 20;。性能优化：
- 为常用查询条件创建索引。
- 避免在 WHERE 子句中对字段使用函数，如
WHERE YEAR(hire_date) = 2020会导致索引失效。 - 使用
EXPLAIN分析查询计划。
错误处理：在存储过程中使用
ON EXCEPTION捕获异常，如：
CREATE PROCEDURE safe_update(id INT, new_salary DECIMAL(10,2))
ON EXCEPTION IN (-206) -- 表不存在错误
PRINT "错误: 表不存在";
END EXCEPTION;
UPDATE employees SET salary = new_salary WHERE emp_id = id;
END PROCEDURE;3 Gbase8s SQL编程进阶
3.1 函数
3.1.1 窗口函数
1. 排名函数（Ranking Functions）
| 函数 | 说明 |
|---|---|
RANK() |
计算排名，相同值有相同排名，后续排名跳过（如 1,2,2,4） |
DENSE_RANK() |
计算排名，相同值有相同排名，后续排名不跳过（如 1,2,2,3） |
ROW_NUMBER() |
计算行号，相同值也不同排名（如 1,2,3,4） |
NTILE(n) |
将数据分成 n 组，返回组号 |
- (1)
RANK()、DENSE_RANK()、ROW_NUMBER()
-- 创建测试表
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
salesperson VARCHAR(50),
region VARCHAR(20),
amount DECIMAL(10,2),
sale_date DATE
);
-- 插入测试数据
INSERT INTO sales (salesperson, region, amount, sale_date) VALUES
('张三', '北京', 5000, '2023-01-01'),
('李四', '上海', 6000, '2023-01-02'),
('王五', '北京', 5000, '2023-01-03'),
('赵六', '上海', 7000, '2023-01-04'),
('钱七', '广州', 4000, '2023-01-05');
-- 计算销售额排名（RANK、DENSE_RANK、ROW_NUMBER）
SELECT
salesperson,
region,
amount,
RANK() OVER (ORDER BY amount DESC) AS rank,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM sales;输出结果
salesperson | region | amount | rank | dense_rank | row_num
------------|--------|--------|------|------------|--------
赵六 | 上海 | 7000 | 1 | 1 | 1
李四 | 上海 | 6000 | 2 | 2 | 2
张三 | 北京 | 5000 | 3 | 3 | 3
王五 | 北京 | 5000 | 3 | 3 | 4
钱七 | 广州 | 4000 | 5 | 4 | 5(2)
NTILE(n)（分组排名）
-- 将销售员分成 3 组（按销售额）
SELECT
salesperson,
amount,
NTILE(3) OVER (ORDER BY amount DESC) AS quartile
FROM sales;输出结果：
salesperson | amount | quartile
------------|--------|---------
赵六 | 7000 | 1
李四 | 6000 | 1
张三 | 5000 | 2
王五 | 5000 | 2
钱七 | 4000 | 32. 聚合窗口函数（Aggregate Window Functions）
| 函数 | 说明 |
|---|---|
SUM() OVER() |
计算累计和 |
AVG() OVER() |
计算移动平均 |
COUNT() OVER() |
计算行数 |
MAX() OVER() |
计算最大值 |
MIN() OVER() |
计算最小值 |
(1)
SUM() OVER()（累计求和）
-- 计算每个销售员的累计销售额（按日期排序）
SELECT
sale_date,
salesperson,
amount,
SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS running_total
FROM sales;输出结果：
sale_date | salesperson | amount | running_total
-----------|-------------|--------|--------------
2023-01-01 | 张三 | 5000 | 5000
2023-01-03 | 张三 | 5000 | 10000
2023-01-02 | 李四 | 6000 | 6000
2023-01-04 | 赵六 | 7000 | 7000
2023-01-05 | 钱七 | 4000 | 4000(2) AVG() OVER()（移动平均）
-- 计算 3 天移动平均销售额
SELECT
sale_date,
amount,
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM sales;输出结果：
sale_date | amount | moving_avg
-----------|--------|-----------
2023-01-01 | 5000 | 5500 -- (5000 + 6000) / 2
2023-01-02 | 6000 | 5333.33 -- (5000 + 6000 + 5000) / 3
2023-01-03 | 5000 | 6000 -- (6000 + 5000 + 7000) / 3
2023-01-04 | 7000 | 5500 -- (5000 + 7000 + 4000) / 3
2023-01-05 | 4000 | 5500 -- (7000 + 4000) / 23. 取值函数示例
| 函数 | 说明 |
|---|---|
FIRST_VALUE(expr) |
返回窗口第一行的值 |
LAST_VALUE(expr) |
返回窗口最后一行的值 |
LAG(expr, offset) |
返回当前行之前的第 offset 行的值 |
LEAD(expr, offset) |
返回当前行之后的第 offset 行的值 |
NTH_VALUE(expr, n) |
返回窗口第 n 行的值 |
(1) FIRST_VALUE()、LAST_VALUE()
-- 查询每个地区的最高和最低销售额
SELECT
region,
salesperson,
amount,
FIRST_VALUE(salesperson) OVER (PARTITION BY region ORDER BY amount DESC) AS highest_salesperson,
LAST_VALUE(salesperson) OVER (PARTITION BY region ORDER BY amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salesperson
FROM sales;输出结果：
region | salesperson | amount | highest_salesperson | lowest_salesperson
-------|-------------|--------|---------------------|-------------------
北京 | 张三 | 5000 | 张三 | 王五
北京 | 王五 | 5000 | 张三 | 王五
上海 | 李四 | 6000 | 赵六 | 李四
上海 | 赵六 | 7000 | 赵六 | 李四
广州 | 钱七 | 4000 | 钱七 | 钱七(2) LAG()、LEAD()
-- 查询销售额与前一名、后一名的差异
SELECT
salesperson,
amount,
LAG(amount, 1) OVER (ORDER BY amount DESC) AS prev_amount,
LEAD(amount, 1) OVER (ORDER BY amount DESC) AS next_amount,
amount - LAG(amount, 1) OVER (ORDER BY amount DESC) AS diff_from_prev
FROM sales;输出结果：
salesperson | amount | prev_amount | next_amount | diff_from_prev
------------|--------|-------------|-------------|---------------
赵六 | 7000 | NULL | 6000 | NULL
李四 | 6000 | 7000 | 5000 | -1000
张三 | 5000 | 6000 | 5000 | -1000
王五 | 5000 | 5000 | 4000 | 0
钱七 | 4000 | 5000 | NULL | -10003.1.2 高级聚合函数
1 LISTAGG函数
函数语法
LISTAGG(expression [, delimiter]) WITHIN GROUP (ORDER BY sort_expression)参数说明：
expression：要连接的列或表达式delimiter：可选参数，用于分隔各个值的字符串，默认为 NULL（无分隔符）sort_expression：指定连接结果的排序方式
2 基本用法示例
示例1：简单连接
-- 将员工表中的所有员工姓名连接成一个字符串
SELECT LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS all_employees
FROM emp;示例2：分组连接
-- 按部门分组，将每个部门的员工姓名连接起来
SELECT deptno,
LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS department_employees
FROM emp
GROUP BY deptno;3 高级用法示例
示例1：使用不同的分隔符
-- 使用分号和空格作为分隔符
SELECT deptno,
LISTAGG(ename, '; ') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;示例2：多列连接
-- 连接员工姓名和职位
SELECT deptno,
LISTAGG(ename || ' (' || job || ')', ', ')
WITHIN GROUP (ORDER BY ename) AS employee_details
FROM emp
GROUP BY deptno;示例3：结合其他聚合函数
-- 统计每个部门的员工数量和员工列表
SELECT deptno,
COUNT(*) AS employee_count,
LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list
FROM emp
GROUP BY deptno;- 注意事项
LISTAGG 函数的结果字符串长度有限制，如果连接后的字符串超过最大长度，会报错
对于 NULL 值，LISTAGG 会忽略它们
在 GBase 8s 中，LISTAGG 的语法可能与 Oracle 略有不同
3.1.3 字符串处理函数
1 示例
-- REGEXP_LIKE 正则匹配
SELECT ename FROM emp WHERE REGEXP_LIKE(ename, '^S.*n$');
-- REGEXP_REPLACE 正则替换
SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA',
'[[:space:]]+,[[:space:]]+', ', ') AS formatted_address;
-- REGEXP_INSTR 正则位置查找
SELECT REGEXP_INSTR('The price is $100.99', '[0-9]+\.[0-9]{2}') AS price_position;
-- MD5 哈希函数
SELECT MD5('password123') AS hashed_password;
-- SHA256 安全哈希
SELECT SHA256('sensitive data') AS secure_hash;3.1.4 日期时间处理
1 示例
-- 日期运算
SELECT CURRENT - INTERVAL(7) DAY TO DAY AS one_week_ago FROM sysmaster:sysdual;
-- 日期格式化扩展
SELECT TO_CHAR(CURRENT, 'YYYY-MM-DD HH24:MI:SS.FF3') AS precise_timestamp;
-- 时区转换
SELECT NEW TIME(CURRENT, 'GMT', 'EST') AS est_time;
-- 工作日计算
SELECT DAYS(DATE('2023-12-25') - DATE('2023-12-01')) AS days_between;
-- 复杂日期提取
SELECT EXTEND(CURRENT, YEAR TO YEAR) AS current_year,
EXTEND(CURRENT, MONTH TO MONTH) AS current_month,
EXTEND(CURRENT, DAY TO DAY) AS current_day;3.1.5 JSON处理函数
-- 创建包含JSON的表
CREATE TABLE json_data (
id SERIAL PRIMARY KEY,
data JSON
);
-- 插入JSON数据
INSERT INTO json_data (data) VALUES ('{"name":"John", "age":30, "city":"New York"}');
-- JSON字段提取
SELECT
data::json::text AS full_json,
data::json->>'name' AS name,
data::json->>'age' AS age,
data::json#>>'{address,street}' AS street
FROM json_data;
-- JSON路径查询
SELECT JSON_PATH_QUERY(data, '$.name') AS name_value FROM json_data;
-- JSON数组处理
INSERT INTO json_data (data) VALUES ('{"employees": ["John", "Jane", "Mike"]}');
SELECT
j.data::json#>>'{employees}' AS employees_array,
JSON_PATH_QUERY_ARRAY(j.data, '$.employees[*]') AS employee_names
FROM json_data j
WHERE j.data::json::text LIKE '%employees%';3.1.6 系统与诊断函数
-- 数据库信息
SELECT DBINFO('version', 'full') AS gbase_version;
-- 会话信息
SELECT DBINFO('sessionid') AS session_id,
DBINFO('utc_to_datetime', SESSIONTIME) AS login_time;
-- 表空间使用情况
SELECT
DBINFO('dbspace', dbsname) AS dbspace_id,
dbsname,
nfree/1024 AS free_space_kb,
nsize/1024 AS total_space_kb
FROM sysmaster:sysdbspaces;
-- 执行计划分析
SET EXPLAIN ON;
SELECT * FROM emp WHERE deptno = 10;
SET EXPLAIN OFF;
-- 性能统计
SELECT * FROM sysmaster:sysprofile ORDER BY counter DESC LIMIT 10;3.1.7 过程化SQL扩展
-- DO 语句执行匿名代码块
DO $$
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count FROM emp WHERE sal > 5000;
RAISE NOTICE 'There are % employees with salary > 5000', v_count;
END;
$$;
-- 动态SQL执行
CREATE PROCEDURE update_salary(p_deptno INT, p_percent DECIMAL(5,2))
DEFINE v_sql VARCHAR(255);
LET v_sql = 'UPDATE emp SET sal = sal * (1 + ' || p_percent || '/100) WHERE deptno = ' || p_deptno;
EXECUTE IMMEDIATE v_sql;
END PROCEDURE;
-- 异常处理
CREATE PROCEDURE safe_delete(p_empno INT)
DEFINE err_no INTEGER;
DEFINE err_msg VARCHAR(255);
ON EXCEPTION IN (-746) -- 约束违反错误
LET err_no = DBINFO('sqlca.sqlcode');
LET err_msg = 'Cannot delete employee ' || p_empno ||
' - referenced in other tables';
RAISE EXCEPTION err_no, err_msg;
END EXCEPTION WITH RESUME;
DELETE FROM emp WHERE empno = p_empno;
END PROCEDURE;3.2 递归查询
3.2.1 递归查询语法
WITH RECURSIVE cte_name AS (
-- 基础查询（非递归部分）
SELECT columns FROM table_name WHERE initial_condition
UNION [ALL]
-- 递归部分
SELECT columns FROM table_name
JOIN cte_name ON recursive_condition
WHERE stop_condition
)
SELECT * FROM cte_name;关键点
WITH RECURSIVE：声明递归 CTE。- 基础查询：递归的起点（如根节点）。
UNION [ALL]：合并基础查询和递归结果。- 递归部分：引用 CTE 自身，通过 JOIN 或子查询实现递归。
- 终止条件：避免无限循环（如
WHERE level < 10）。
3.2.2 递归查询示例
示例 1：员工层级关系（上下级查询）
假设有员工表 employees，包含 emp_id、name 和 manager_id（上级 ID）。
-- 创建测试表
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(50),
manager_id INT,
salary DECIMAL(10,2)
);
-- 插入测试数据
INSERT INTO employees (emp_id, name, manager_id, salary) VALUES
(1, '张总', NULL, 30000),
(2, '李经理', 1, 20000),
(3, '王主管', 2, 15000),
(4, '赵员工', 3, 10000),
(5, '钱员工', 3, 12000),
(6, '孙经理', 1, 18000),
(7, '周主管', 6, 14000);
-- 递归查询：从CEO开始，展示所有下属层级
WITH RECURSIVE emp_hierarchy AS (
-- 基础查询：选择顶层管理者（无上级）
SELECT
emp_id,
name,
manager_id,
salary,
1 AS level,
CAST(name AS VARCHAR(1000)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归查询：查找下属
SELECT
e.emp_id,
e.name,
e.manager_id,
e.salary,
h.level + 1,
h.path || ' -> ' || e.name AS path
FROM employees e
JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT emp_id, name, manager_id, salary, level, path
FROM emp_hierarchy
ORDER BY path;输出结果：
emp_id | name | manager_id | salary | level | path
-------|--------|------------|--------|-------|-------------------------
1 | 张总 | NULL | 30000 | 1 | 张总
2 | 李经理 | 1 | 20000 | 2 | 张总 -> 李经理
3 | 王主管 | 2 | 15000 | 3 | 张总 -> 李经理 -> 王主管
4 | 赵员工 | 3 | 10000 | 4 | 张总 -> 李经理 -> 王主管 -> 赵员工
5 | 钱员工 | 3 | 12000 | 4 | 张总 -> 李经理 -> 王主管 -> 钱员工
6 | 孙经理 | 1 | 18000 | 2 | 张总 -> 孙经理
7 | 周主管 | 6 | 14000 | 3 | 张总 -> 孙经理 -> 周主管示例 2：计算路径总薪资（聚合递归结果）
-- 递归查询：计算每个员工的薪资路径总和
WITH RECURSIVE emp_salary_path AS (
-- 基础查询：顶层管理者
SELECT
emp_id,
name,
manager_id,
salary,
salary AS total_salary,
CAST(name AS VARCHAR(1000)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归查询：累加下属薪资
SELECT
e.emp_id,
e.name,
e.manager_id,
e.salary,
p.total_salary + e.salary,
p.path || ' -> ' || e.name AS path
FROM employees e
JOIN emp_salary_path p ON e.manager_id = p.emp_id
)
SELECT emp_id, name, salary, total_salary, path
FROM emp_salary_path
ORDER BY total_salary DESC;输出结果：
emp_id | name | salary | total_salary | path
-------|--------|--------|--------------|-------------------------
1 | 张总 | 30000 | 30000 | 张总
6 | 孙经理 | 18000 | 48000 | 张总 -> 孙经理
7 | 周主管 | 14000 | 62000 | 张总 -> 孙经理 -> 周主管
2 | 李经理 | 20000 | 50000 | 张总 -> 李经理
3 | 王主管 | 15000 | 65000 | 张总 -> 李经理 -> 王主管
4 | 赵员工 | 10000 | 75000 | 张总 -> 李经理 -> 王主管 -> 赵员工
5 | 钱员工 | 12000 | 77000 | 张总 -> 李经理 -> 王主管 -> 钱员工示例 3：查找所有下属（指定起始员工）
-- 递归查询：查找李经理的所有下属（包括间接下属）
WITH RECURSIVE subordinates AS (
-- 基础查询：李经理本人
SELECT emp_id, name, manager_id
FROM employees
WHERE name = '李经理'
UNION ALL
-- 递归查询：下属
SELECT e.emp_id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT emp_id, name, manager_id
FROM subordinates
WHERE name != '李经理'; -- 排除李经理自己输出结果：
emp_id | name | manager_id
-------|--------|-----------
3 | 王主管 | 2
4 | 赵员工 | 3
5 | 钱员工 | 3示例 4：限制递归深度（避免循环引用）
如果数据中存在循环引用（如 A 的经理是 B，B 的经理又是 A），需通过 level 限制递归深度。
-- 递归查询：限制最多 5 层
WITH RECURSIVE safe_hierarchy AS (
SELECT emp_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id, h.level + 1
FROM employees e
JOIN safe_hierarchy h ON e.manager_id = h.emp_id
WHERE h.level < 5 -- 限制递归深度
)
SELECT * FROM safe_hierarchy;- 递归查询的注意事项
- 避免循环引用：确保数据无环，或通过
level限制递归深度。 - 性能优化：递归查询可能消耗大量资源，建议对连接字段（如
manager_id）建立索引。 UNION ALLvsUNION：UNION ALL保留重复行，性能更高。UNION会去重，但可能影响性能。
3.3 存储过程与触发器高级编程
3.3.1 存储过程
3.3.1.1 基本存储过程
-- 创建简单存储过程
CREATE PROCEDURE increase_salary(p_deptno INT, p_percent DECIMAL(5,2))
DEFINE v_avg_sal DECIMAL(10,2);
-- 获取部门当前平均工资
SELECT AVG(sal) INTO v_avg_sal
FROM emp
WHERE deptno = p_deptno;
-- 更新薪资
UPDATE emp
SET sal = sal * (1 + p_percent/100)
WHERE deptno = p_deptno;
-- 输出信息
PRINT 'Department ' || p_deptno ||
' salary increased by ' || p_percent ||
'%. Original average salary: ' || v_avg_sal;
END PROCEDURE;
-- 执行存储过程
EXECUTE PROCEDURE increase_salary(10, 5.5);
-- 带输出参数的存储过程
CREATE PROCEDURE get_employee_stats(p_deptno INT,
OUT p_count INT,
OUT p_avg_sal DECIMAL(10,2),
OUT p_max_sal DECIMAL(10,2))
SELECT COUNT(*), AVG(sal), MAX(sal)
INTO p_count, p_avg_sal, p_max_sal
FROM emp
WHERE deptno = p_deptno;
END PROCEDURE;
-- 调用带输出参数的存储过程
DEFINE v_count INT;
DEFINE v_avg DECIMAL(10,2);
DEFINE v_max DECIMAL(10,2);
EXECUTE PROCEDURE get_employee_stats(20, v_count, v_avg, v_max);
PRINT 'Dept 20 Stats - Count: ' || v_count ||
' Avg Salary: ' || v_avg ||
' Max Salary: ' || v_max;3.3.1.2 条件逻辑与异常处理
-- 带条件逻辑的存储过程
CREATE PROCEDURE transfer_budget(p_from_dept INT, p_to_dept INT, p_amount DECIMAL(12,2))
DEFINE v_from_balance DECIMAL(12,2);
-- 检查参数有效性
IF p_amount <= 0 THEN
RAISE EXCEPTION -746, 0, 'Transfer amount must be positive';
RETURN;
END IF;
-- 获取转出部门预算
SELECT budget INTO v_from_balance
FROM dept
WHERE deptno = p_from_dept;
-- 检查余额是否充足
IF v_from_balance IS NULL THEN
RAISE EXCEPTION -746, 0, 'Source department not found';
ELSIF v_from_balance < p_amount THEN
RAISE EXCEPTION -746, 0, 'Insufficient budget in source department';
ELSE
-- 执行转账
UPDATE dept
SET budget = budget - p_amount
WHERE deptno = p_from_dept;
UPDATE dept
SET budget = budget + p_amount
WHERE deptno = p_to_dept;
PRINT 'Successfully transferred ' || p_amount ||
' from dept ' || p_from_dept ||
' to dept ' || p_to_dept;
END IF;
END PROCEDURE;
-- 带异常处理的存储过程
CREATE PROCEDURE process_order(p_custno INT, p_amount DECIMAL(12,2))
DEFINE v_credit_limit DECIMAL(12,2);
DEFINE v_new_balance DECIMAL(12,2);
-- 开始事务
BEGIN WORK;
-- 设置异常处理
ON EXCEPTION IN (-243, -268) -- 唯一约束违反或外键约束
ROLLBACK WORK;
RAISE EXCEPTION -746, 0, 'Order processing failed: constraint violation';
END EXCEPTION WITH RESUME;
-- 获取客户信用额度
SELECT credit_limit INTO v_credit_limit
FROM customers
WHERE custno = p_custno;
IF v_credit_limit IS NULL THEN
RAISE EXCEPTION -746, 0, 'Customer not found';
END IF;
-- 计算新余额
LET v_new_balance = v_credit_limit - p_amount;
IF v_new_balance < 0 THEN
RAISE EXCEPTION -746, 0, 'Credit limit exceeded';
END IF;
-- 更新信用额度
UPDATE customers
SET credit_limit = v_new_balance
WHERE custno = p_custno;
-- 创建订单
INSERT INTO orders (orderno, custno, order_date, amount)
VALUES (GET_ORDER_SEQ(), p_custno, TODAY, p_amount);
-- 提交事务
COMMIT WORK;
PRINT 'Order processed successfully for customer ' || p_custno;
END PROCEDURE;3.3.1.3 游标处理
-- 带游标的存储过程
CREATE PROCEDURE generate_salary_report(p_deptno INT)
DEFINE v_total_salary DECIMAL(12,2) = 0;
DEFINE v_emp_count INT = 0;
-- 定义游标
DEFINE emp_cursor CURSOR FOR
SELECT empno, ename, sal
FROM emp
WHERE deptno = p_deptno
ORDER BY sal DESC;
-- 打开游标
OPEN emp_cursor;
-- 开始事务
BEGIN WORK;
-- 创建临时报表表
CREATE TEMP TABLE temp_salary_report (
empno INT,
ename VARCHAR(50),
salary DECIMAL(10,2),
report_date DATE DEFAULT TODAY
);
-- 循环处理游标数据
FOREACH
FETCH emp_cursor INTO v_empno, v_ename, v_salary;
-- 插入到临时表
INSERT INTO temp_salary_report (empno, ename, salary)
VALUES (v_empno, v_ename, v_salary);
-- 累加统计
LET v_total_salary = v_total_salary + v_salary;
LET v_emp_count = v_emp_count + 1;
END FOREACH;
-- 关闭游标
CLOSE emp_cursor;
-- 输出统计信息
PRINT 'Department ' || p_deptno || ' Salary Report:';
PRINT 'Total Employees: ' || v_emp_count;
PRINT 'Total Salary: ' || v_total_salary;
PRINT 'Average Salary: ' || (v_total_salary / DECODE(v_emp_count, 0, 1, v_emp_count));
-- 提交事务
COMMIT WORK;
END PROCEDURE;3.3.2 触发器 (Triggers)
3.3.2.1 基本触发器
-- 创建审计表
CREATE TABLE emp_audit (
audit_id SERIAL PRIMARY KEY,
empno INT,
changed_by VARCHAR(30),
change_time DATETIME YEAR TO SECOND,
change_type CHAR(1), -- I=Insert, U=Update, D=Delete
old_sal DECIMAL(10,2),
new_sal DECIMAL(10,2)
);
-- 插入后触发器
CREATE TRIGGER trg_emp_insert INSERT ON emp
REFERENCING NEW AS new_row
FOR EACH ROW
(
INSERT INTO emp_audit (empno, changed_by, change_time, change_type, new_sal)
VALUES (new_row.empno, USER, CURRENT, 'I', new_row.sal)
);
-- 更新前触发器
CREATE TRIGGER trg_emp_update UPDATE OF sal ON emp
REFERENCING OLD AS old_row NEW AS new_row
FOR EACH ROW
(
INSERT INTO emp_audit (empno, changed_by, change_time, change_type,
old_sal, new_sal)
VALUES (new_row.empno, USER, CURRENT, 'U',
old_row.sal, new_row.sal)
);
-- 删除前触发器
CREATE TRIGGER trg_emp_delete DELETE ON emp
REFERENCING OLD AS old_row
FOR EACH ROW
(
INSERT INTO emp_audit (empno, changed_by, change_time, change_type, old_sal)
VALUES (old_row.empno, USER, CURRENT, 'D', old_row.sal)
);3.3.2.2 复杂触发器示例
-- 薪资变更控制触发器
CREATE TRIGGER trg_salary_check UPDATE OF sal ON emp
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
WHEN (new.sal > old.sal * 1.5) -- 涨幅超过50%需要审批
(
-- 检查是否有审批记录
DEFINE v_approved INT;
SELECT COUNT(*) INTO v_approved
FROM salary_approvals
WHERE empno = new.empno
AND approval_date >= TODAY - INTERVAL(30) DAY TO DAY;
-- 如果没有审批记录，则回滚事务
IF v_approved = 0 THEN
RAISE EXCEPTION -746, 0,
'Salary increase超过50%需要审批，请先提交审批申请';
END IF;
);
-- 部门预算控制触发器
CREATE TRIGGER trg_dept_budget UPDATE ON emp
REFERENCING NEW AS new_row
FOR EACH ROW
WHEN (new_row.sal <> OLD.sal OR new_row.deptno <> OLD.deptno)
(
DEFINE v_dept_budget DECIMAL(12,2);
DEFINE v_dept_salary DECIMAL(12,2);
-- 获取部门预算
SELECT budget INTO v_dept_budget
FROM dept
WHERE deptno = new_row.deptno;
-- 计算部门当前薪资总额
SELECT SUM(sal) INTO v_dept_salary
FROM emp
WHERE deptno = new_row.deptno;
-- 计算薪资变化
LET v_dept_salary = v_dept_salary - NVL(OLD.sal, 0) + NVL(new_row.sal, 0);
-- 检查预算
IF v_dept_salary > v_dept_budget THEN
RAISE EXCEPTION -746, 0,
'操作会导致部门薪资总额 ' || v_dept_salary ||
' 超过预算 ' || v_dept_budget;
END IF;
);3.3.2.3 事务性触发器
-- 订单处理完整事务触发器
CREATE TRIGGER trg_order_process INSERT ON orders
REFERENCING NEW AS new_order
FOR EACH ROW
(
-- 开始事务
BEGIN WORK;
-- 定义异常处理
ON EXCEPTION IN (-243, -268, -746)
ROLLBACK WORK;
RAISE EXCEPTION; -- 重新抛出异常
END EXCEPTION WITH RESUME;
-- 1. 更新客户未结订单总额
UPDATE customers
SET outstanding_amount = outstanding_amount + new_order.amount
WHERE custno = new_order.custno;
-- 2. 检查信用额度
DEFINE v_credit_limit DECIMAL(12,2);
DEFINE v_outstanding DECIMAL(12,2);
SELECT credit_limit, outstanding_amount
INTO v_credit_limit, v_outstanding
FROM customers
WHERE custno = new_order.custno;
IF v_outstanding > v_credit_limit THEN
RAISE EXCEPTION -746, 0,
'添加订单会超过客户信用额度: ' || v_outstanding ||
' > ' || v_credit_limit;
END IF;
-- 3. 更新产品库存
DEFINE v_qty INT;
DEFINE v_product_id INT;
-- 假设订单项存储在order_items表中
FOREACH
SELECT product_id, SUM(quantity) INTO v_product_id, v_qty
FROM order_items
WHERE orderno = new_order.orderno
GROUP BY product_id;
UPDATE products
SET stock = stock - v_qty
WHERE product_id = v_product_id;
IF (stock - v_qty) < 0 THEN
RAISE EXCEPTION -746, 0,
'产品 ' || v_product_id || ' 库存不足';
END IF;
END FOREACH;
-- 4. 记录订单状态
INSERT INTO order_status (orderno, status, status_date)
VALUES (new_order.orderno, 'PROCESSING', CURRENT);
-- 提交事务
COMMIT WORK;
);3.3.3 注意事项
命名规范：
- 存储过程：
proc_<功能描述>，如proc_calculate_monthly_bonus - 触发器：
trg_<表名>_<操作>_<目的>，如trg_emp_update_salary_audit
- 存储过程：
错误处理：
- 总是包含异常处理
- 使用有意义的错误代码和消息
- 考虑事务回滚
性能考虑：
- 避免在触发器中执行复杂逻辑
- 减少游标使用，尽量使用基于集合的操作
- 注意触发器递归调用风险
3.4 分区表（Partitioning）
GBase 8s（基于Informix）支持三种分区策略：范围分区(RANGE)、列表分区(LIST)和哈希分区(HASH)。分区表可以显著提高大数据量表的查询性能和管理效率。
3.4.1 分区表基础
1. 创建分区表的基本语法
CREATE TABLE partitioned_table (
column1 datatype,
column2 datatype,
...
)
PARTITION BY RANGE|LIST|HASH (partition_key_column)
[PARTITIONS num_partitions]
[IN databspace1, databspace2, ...];2. 分区表的优势
- 提高查询性能（分区裁剪）
- 简化数据管理（可单独操作某个分区）
- 提高可用性（单个分区故障不影响其他分区）
- 均衡I/O负载
3.4.2 范围分区(RANGE Partitioning)
范围分区基于列值的范围将数据分配到不同分区，适用于日期、数值等连续型数据。
1. 基本范围分区示例
-- 按日期范围分区（销售数据按年分区）
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
product_id INT,
sale_date DATE,
amount DECIMAL(12,2),
customer_id INT
)
PARTITION BY RANGE (sale_date)
(
PARTITION sales_2020 VALUES LESS THAN DATE('2021-01-01') IN dbspace1,
PARTITION sales_2021 VALUES LESS THAN DATE('2022-01-01') IN dbspace2,
PARTITION sales_2022 VALUES LESS THAN DATE('2023-01-01') IN dbspace3,
PARTITION sales_future VALUES LESS THAN MAXVALUE IN dbspace4
);2. 多列范围分区
-- 按产品和日期范围分区
CREATE TABLE inventory (
product_id INT,
warehouse_id INT,
quantity INT,
record_date DATE,
PRIMARY KEY (product_id, warehouse_id, record_date)
)
PARTITION BY RANGE (record_date, product_id)
(
PARTITION inv_2022_low VALUES LESS THAN (DATE('2023-01-01'), 1000) IN dbspace1,
PARTITION inv_2022_high VALUES LESS THAN (DATE('2023-01-01'), MAXVALUE) IN dbspace2,
PARTITION inv_2023 VALUES LESS THAN (MAXVALUE, MAXVALUE) IN dbspace3
);3. 添加新范围分区
-- 为销售表添加2023年分区
ALTER TABLE sales ADD PARTITION
PARTITION sales_2023 VALUES LESS THAN DATE('2024-01-01') IN dbspace5;3.4.3 列表分区(LIST Partitioning)
列表分区基于离散值列表将数据分配到不同分区，适用于分类数据。
1. 基本列表分区示例
-- 按地区列表分区（客户表）
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100),
region VARCHAR(20),
credit_limit DECIMAL(12,2)
)
PARTITION BY LIST (region)
(
PARTITION cust_east VALUES IN ('NY', 'NJ', 'CT', 'MA') IN dbspace1,
PARTITION cust_west VALUES IN ('CA', 'WA', 'OR', 'NV') IN dbspace2,
PARTITION cust_central VALUES IN ('TX', 'IL', 'OH', 'MI') IN dbspace3,
PARTITION cust_other VALUES IN (DEFAULT) IN dbspace4
);2. 多列列表分区
-- 按产品和客户类型分区
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
product_id INT,
customer_type CHAR(1), -- 'R'=零售, 'W'=批发, 'I'=内部
order_date DATE,
amount DECIMAL(12,2)
)
PARTITION BY LIST (customer_type, product_id)
(
PARTITION ord_retail_low VALUES IN (('R', x) FOR x IN (1,2,3)) IN dbspace1,
PARTITION ord_retail_high VALUES IN (('R', x) FOR x IN (4,5,6)) IN dbspace2,
PARTITION ord_wholesale VALUES IN (('W', ANY)) IN dbspace3,
PARTITION ord_internal VALUES IN (('I', ANY)) IN dbspace4,
PARTITION ord_other VALUES IN (DEFAULT) IN dbspace5
);3. 修改列表分区
-- 添加新的地区分区
ALTER TABLE customers ADD PARTITION
PARTITION cust_south VALUES IN ('FL', 'GA', 'NC', 'SC') IN dbspace5;
-- 从分区中移除特定值（GBase 8s不直接支持，需重建分区）3.4.4 哈希分区(HASH Partitioning)
哈希分区基于哈希函数均匀分布数据，适用于均匀分布负载的场景。
1. 基本哈希分区示例
-- 哈希分区用户表（均匀分布）
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
registration_date DATE,
last_login DATETIME YEAR TO SECOND
)
PARTITION BY HASH (user_id)
PARTITIONS 8 IN dbspace1, dbspace2, dbspace3, dbspace4;2. 指定分区存储位置
-- 明确指定每个哈希分区的存储位置
CREATE TABLE sensor_data (
sensor_id INT,
reading_time DATETIME YEAR TO FRACTION(5),
value DECIMAL(10,3),
status CHAR(1)
)
PARTITION BY HASH (sensor_id)
PARTITIONS 4
(
PARTITION hash_part1 IN dbspace1,
PARTITION hash_part2 IN dbspace2,
PARTITION hash_part3 IN dbspace3,
PARTITION hash_part4 IN dbspace4
);3. 修改哈希分区数量
-- 增加哈希分区数量（需要重建表）
-- 步骤1：创建临时表
CREATE TABLE sensor_data_temp AS SELECT * FROM sensor_data WHERE 1=0;
-- 步骤2：删除原表
DROP TABLE sensor_data;
-- 步骤3：创建新分区表
CREATE TABLE sensor_data (
sensor_id INT,
reading_time DATETIME YEAR TO FRACTION(5),
value DECIMAL(10,3),
status CHAR(1)
)
PARTITION BY HASH (sensor_id)
PARTITIONS 8 IN dbspace1, dbspace2, dbspace3, dbspace4, dbspace5, dbspace6, dbspace7, dbspace8;
-- 步骤4：从临时表导入数据
INSERT INTO sensor_data SELECT * FROM sensor_data_temp;
-- 步骤5：删除临时表
DROP TABLE sensor_data_temp;3.4.5 高级分区策略
1. 复合分区（子分区）
-- 范围-哈希复合分区（先按日期范围，再按产品ID哈希）
CREATE TABLE sales_composite (
sale_id SERIAL,
product_id INT,
sale_date DATE,
amount DECIMAL(12,2),
customer_id INT
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (product_id)
SUBPARTITIONS 4
(
PARTITION sales_2020 VALUES LESS THAN DATE('2021-01-01') IN dbspace1,
PARTITION sales_2021 VALUES LESS THAN DATE('2022-01-01') IN dbspace2,
PARTITION sales_2022 VALUES LESS THAN DATE('2023-01-01') IN dbspace3,
PARTITION sales_future VALUES LESS THAN MAXVALUE IN dbspace4
);2. 间隔分区（自动创建新分区）
-- 创建间隔分区表（GBase 8s不直接支持，但可通过触发器模拟）
CREATE TABLE log_data (
log_id SERIAL,
log_time DATETIME YEAR TO FRACTION(5),
message VARCHAR(255),
severity INT
)
PARTITION BY RANGE (log_time)
(
PARTITION log_202301 VALUES LESS THAN DATETIME(2023-02-01 00:00:00) YEAR TO FRACTION(5) IN dbspace1,
PARTITION log_future VALUES LESS THAN MAXVALUE IN dbspace2
);
-- 创建触发器自动添加新分区（简化示例）
CREATE TRIGGER trg_log_partition INSERT ON log_data
REFERENCING NEW AS new_row
FOR EACH ROW
WHEN (new_row.log_time >= DATETIME(2023-02-01 00:00:00) YEAR TO FRACTION(5))
(
-- 实际应用中这里应该有更复杂的逻辑来检测是否需要新分区
-- 以下是简化处理
ALTER TABLE log_data ADD PARTITION
PARTITION log_202302 VALUES LESS THAN DATETIME(2023-03-01 00:00:00) YEAR TO FRACTION(5) IN dbspace3;
);3.4.6 分区表管理操作
1. 分区表维护
-- 查看分区信息
SELECT tabname, partnum, partition, low_bound, high_bound
FROM syspartitions
WHERE tabname = 'sales';
-- 重组分区（重建分区索引）
ALTER TABLE sales REORGANIZE PARTITION sales_2020;
-- 截断分区（快速删除分区数据）
ALTER TABLE sales TRUNCATE PARTITION sales_2020;2. 分区表索引
-- 在分区表上创建本地分区索引（每个分区单独索引）
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;
-- 创建全局索引（跨所有分区）
CREATE INDEX idx_sales_amount ON sales(amount) GLOBAL;
-- 为特定分区创建索引
CREATE INDEX idx_sales_2021_product ON sales(product_id)
IN dbspace_idx WHERE sale_date >= DATE('2021-01-01') AND sale_date < DATE('2022-01-01');3. 分区表查询优化
-- 分区裁剪查询（只扫描相关分区）
SELECT * FROM sales
WHERE sale_date BETWEEN DATE('2021-06-01') AND DATE('2021-12-31');
-- 强制扫描特定分区
SELECT * FROM sales:PARTITION(sales_2020);
-- 多分区查询
SELECT * FROM sales:PARTITION(sales_2020, sales_2021);3.5 物化视图
物化视图(Materialized View)是GBase 8s中一种重要的性能优化工具，它通过预计算和存储复杂查询结果来加速数据检索。与普通视图不同，物化视图实际存储数据，可以显著提高复杂聚合查询的响应速度。
3.5.1 物化视图基础
1. 物化视图概念
物化视图是预先计算并存储的查询结果集，具有以下特点：
- 存储实际数据，而不仅仅是定义
- 需要定期刷新以保持与基表数据一致
- 特别适合复杂聚合查询和报表生成
- 可以创建索引以提高访问速度
2. 创建物化视图的基本语法
CREATE MATERIALIZED VIEW mv_name
[ (column_name [, ...]) ]
[ IN dbspace_name ]
[ DISTRIBUTED | ROUND ROBIN ]
[ REFRESH [FAST | COMPLETE | FORCE] ]
[ ON [COMMIT | DEMAND] | START (date_time) [NEXT date_time] ]
[ ENABLE | DISABLE ]
[ WITH REDUCED REDUNDANCY ]
AS subquery;3.5.2 创建物化视图示例
1. 基本物化视图创建
-- 创建销售汇总物化视图（按产品和地区汇总）
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT
p.product_id,
p.product_name,
c.region,
SUM(s.amount) AS total_sales,
COUNT(*) AS transaction_count,
AVG(s.amount) AS avg_sale
FROM
sales s
JOIN products p ON s.product_id = p.product_id
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY
p.product_id, p.product_name, c.region;2. 带索引的物化视图
-- 创建带索引的物化视图（按时间范围分析）
CREATE MATERIALIZED VIEW mv_time_analysis
IN dbspace1
REFRESH FAST START (DATE('2023-01-01')) NEXT INTERVAL(1) DAY
AS
SELECT
TRUNC(sale_date, 'DDD') AS day,
TRUNC(sale_date, 'WW') AS week,
TRUNC(sale_date, 'MM') AS month,
SUM(amount) AS daily_sales,
SUM(SUM(amount)) OVER (PARTITION BY TRUNC(sale_date, 'MM')) AS monthly_sales
FROM
sales
WHERE
sale_date >= DATE('2023-01-01') AND sale_date < DATE('2024-01-01')
GROUP BY
TRUNC(sale_date, 'DDD'), TRUNC(sale_date, 'WW'), TRUNC(sale_date, 'MM');
-- 为物化视图创建索引
CREATE INDEX idx_mv_time_day ON mv_time_analysis(day);
CREATE INDEX idx_mv_time_month ON mv_time_analysis(month);3. 增量刷新物化视图
-- 创建支持增量刷新的物化视图（需要创建物化视图日志）
-- 第一步：为基表创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON sales
WITH PRIMARY KEY, ROWID (sale_id, product_id, customer_id, amount, sale_date)
INCLUDING NEW VALUES;
-- 第二步：创建增量刷新的物化视图
CREATE MATERIALIZED VIEW mv_customer_purchases
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
customer_id,
product_id,
COUNT(*) AS purchase_count,
SUM(amount) AS total_spent,
MAX(sale_date) AS last_purchase_date
FROM
sales
GROUP BY
customer_id, product_id;3.5.3 物化视图刷新策略
1. 手动刷新
-- 完全刷新物化视图
REFRESH MATERIALIZED VIEW mv_sales_summary;
-- 快速刷新（如果支持）
REFRESH FAST MATERIALIZED VIEW mv_customer_purchases;2. 自动刷新计划
-- 创建每天凌晨2点自动刷新的物化视图
CREATE MATERIALIZED VIEW mv_daily_report
REFRESH COMPLETE START (DATE('2023-01-01') + TIME('02:00:00'))
NEXT INTERVAL(1) DAY
AS
SELECT
TRUNC(CURRENT, 'DDD') AS report_date,
COUNT(DISTINCT customer_id) AS active_customers,
SUM(CASE WHEN amount > 1000 THEN 1 ELSE 0 END) AS big_transactions,
SUM(amount) AS total_revenue
FROM
sales
WHERE
sale_date >= TRUNC(CURRENT, 'DDD') - INTERVAL(1) DAY
AND sale_date < TRUNC(CURRENT, 'DDD');3. 事务提交时刷新
-- 创建在基表事务提交时自动刷新的物化视图
CREATE MATERIALIZED VIEW mv_realtime_inventory
REFRESH FAST ON COMMIT
AS
SELECT
product_id,
warehouse_id,
SUM(quantity) AS current_stock,
SUM(reserved) AS reserved_stock
FROM
inventory
GROUP BY
product_id, warehouse_id;3.5.4 物化视图高级特性
1. 查询重写优化
-- 启用查询重写功能（让优化器自动用物化视图替换查询）
ALTER MATERIALIZED VIEW mv_sales_summary ENABLE QUERY REWRITE;
-- 测试查询重写（优化器可能用物化视图替代子查询）
EXPLAIN SELECT
p.product_name,
SUM(s.amount) AS total
FROM
sales s
JOIN products p ON s.product_id = p.product_id
WHERE
s.sale_date BETWEEN DATE('2023-01-01') AND DATE('2023-12-31')
GROUP BY
p.product_name;2. 复合物化视图
-- 创建多层物化视图（物化视图基于其他物化视图）
-- 基础物化视图
CREATE MATERIALIZED VIEW mv_product_sales
REFRESH COMPLETE ON DEMAND
AS
SELECT
product_id,
SUM(amount) AS product_revenue,
COUNT(*) AS sales_count
FROM
sales
GROUP BY
product_id;
-- 基于基础物化视图的复合物化视图
CREATE MATERIALIZED VIEW mv_product_category_sales
REFRESH COMPLETE AFTER mv_product_sales
AS
SELECT
p.category_id,
c.category_name,
SUM(ps.product_revenue) AS category_revenue,
AVG(ps.product_revenue) AS avg_product_revenue
FROM
mv_product_sales ps
JOIN products p ON ps.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY
p.category_id, c.category_name;3. 分区物化视图
-- 创建分区物化视图（适用于大数据量）
CREATE MATERIALIZED VIEW mv_partitioned_sales
PARTITION BY RANGE (sale_year)
(
PARTITION sales_2020 VALUES LESS THAN (2021) IN dbspace1,
PARTITION sales_2021 VALUES LESS THAN (2022) IN dbspace2,
PARTITION sales_2022 VALUES LESS THAN (2023) IN dbspace3,
PARTITION sales_future VALUES LESS THAN MAXVALUE IN dbspace4
)
REFRESH COMPLETE ON DEMAND
AS
SELECT
EXTRACT(YEAR FROM sale_date) AS sale_year,
product_id,
SUM(amount) AS yearly_sales
FROM
sales
GROUP BY
EXTRACT(YEAR FROM sale_date), product_id;3.6 性能调优
3.6.1 索引优化
- 使用
EXPLAIN分析查询计划。 - 避免在索引列上使用函数（如
WHERE YEAR(hire_date) = 2023会导致索引失效）。
-- 查看执行计划
EXPLAIN SELECT * FROM employees WHERE department = '研发部';
-- 创建复合索引
CREATE INDEX idx_dept_salary ON employees(department, salary);3.6.2 批量操作优化
- 使用
LOAD FROM高效导入数据。 - 批量更新时减少事务提交次数。
-- 批量导入数据
LOAD FROM "employees.unl" DELIMITER '|' INSERT INTO employees;
-- 批量更新（每 1000 条提交一次）
BEGIN WORK;
FOR i = 1 TO 10000
UPDATE products SET stock = stock - 1 WHERE product_id = i;
IF MOD(i, 1000) = 0 THEN
COMMIT WORK;
BEGIN WORK;
END IF;
END FOR;
COMMIT WORK;3.6.3 数据库监控
- 使用
onstat命令监控数据库状态。 - 查询
sysmaster数据库获取性能指标。
-- 查看当前会话和锁信息
SELECT * FROM sysmaster:syssessions;
SELECT * FROM sysmaster:syslocks;