KingbaseES数据库概念.pdf

KingbaseES 数据库概念 金仓数据库管理系统 KingbaseES 文档版本：V9（V009R001C001B0024） 发布日期：2023 年 10 月 12 日 北京人大金仓信息技术股份有限公司 目 目 录 录 第 1 章 前言 1 1.1 适用读者 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 相关文档 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 手册约定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 第 2 章 KingbaseES 数据库简介 2.1 金仓数据库管理系统简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 KingbaseES 能力结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 数据库概念 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.2.1 数据库 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.2.2 数据库管理系统 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.2.3 关系型数据库 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.1.1 2.2 3 第 3 章 数据访问 3.1 6 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 SQL 简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 3.1.1.1 SQL 数据访问 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 3.1.1.2 SQL 标准 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 SQL 语句概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 3.1.2.1 数据定义语言 (DDL) 语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3.1.2.2 数据操纵语言 (DML) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3.1.2.2.1 SELECT 语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 3.1.2.2.2 连接 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 3.1.2.2.3 子查询 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 3.1.2.2.4 事务控制语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 3.1.2.2.5 会话控制语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.1.2.2.6 系统控制语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.1.2.2.7 更改服务器参数语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 3.1.2.2.8 嵌入式的 SQL 语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 优化器概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 优化器的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 3.1.1 3.1.2 3.1.3 3.1.3.1 I 目 录 优化器的组成 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 3.1.3.2.1 逻辑优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 3.1.3.2.2 物理优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 3.1.3.3 访问路径 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.1.3.4 优化器统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.1.3.5 hint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 SQL 处理概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 SQL 处理的阶段 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 3.1.4.1.1 SQL 解析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 3.1.4.1.2 SQL 优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 3.1.4.1.3 SQL 执行 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 DML 和 DDL 处理之间的区别 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 PLSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.2.1 服务器端编程简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.2.2 PL/SQL 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 PL/SQL 子程序 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 3.2.2.1.1 PL/SQL 子程序的优点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 3.2.2.1.2 PL/SQL 子程序的创建 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 3.2.2.1.3 PL/SQL 子程序的执行 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 PL/SQL 包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 3.2.2.2.1 PL/SQL 包的优点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 3.2.2.2.2 PL/SQL 包的创建 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 3.2.2.2.3 PL/SQL 包的执行 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 3.2.2.3 PL/SQL 匿名块 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 3.2.2.4 PL/SQL 语言结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 3.2.2.5 PL/SQL 集合和记录 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 3.2.2.5.1 集合 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 3.2.2.5.2 记录 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 PL/SQL 的运行 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 触发器概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 3.2.3.1 触发器的优势 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.2.3.2 触发器类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.2.3.3 触发时间 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.2.3.4 触发器的创建 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 3.2.3.5 触发器的内部执行 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.2.3.6 触发器的存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.1.3.2 3.1.4 3.1.4.1 3.1.4.2 3.2 3.2.2.1 3.2.2.2 3.2.2.6 3.2.3 第 4 章 存储结构 4.1 30 物理存储结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 4.1.1 物理存储结构简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 4.1.2 数据文件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 4.1.3 控制文件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 II 目 录 日志文件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 WAL 日志 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 4.1.4.1.1 WAL 的优点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 4.1.4.1.2 WAL 配置 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 4.1.4.2 事务日志 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 4.1.4.3 在线日志 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 配置文件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 逻辑存储结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 4.2.1 逻辑存储结构简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 4.2.2 表空间 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 4.2.3 段 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 4.2.4 数据块 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 4.1.4 4.1.4.1 4.1.5 4.2 第 5 章 事务 37 5.1 事务的概念 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 5.2 事务的特性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 5.3 数据库对事务的管理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 5.4 并发控制 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 事务隔离 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 5.4.1.1 读已提交隔离级别 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 5.4.1.2 可重复读隔离级别 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 5.4.1.3 可序列化隔离级别 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 显式锁定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 5.4.2.1 表级锁 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 5.4.2.2 行级锁 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 5.4.2.3 页级锁 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 5.4.2.4 死锁 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 5.4.2.5 咨询锁 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 5.4.2.6 锁定和索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 5.4.1 5.4.2 第 6 章 数据库实例体系结构 52 6.1 实例结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 6.2 进程结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 6.3 内存结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 第 7 章 数据库对象管理 7.1 56 表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 模式对象简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 7.1.1.1 模式对象类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 7.1.1.2 模式对象存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 7.1.1.3 模式对象依赖关系 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 7.1.1.4 模式和管理员 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 表概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 7.1.1 7.1.2 III 目 录 列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 7.1.2.1.1 虚拟列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 7.1.2.1.2 隐藏列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 7.1.2.2 行 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 7.1.2.3 示例：CREATE TABLE 和 ALTER TABLE 语句 . . . . . . . . . . . . . . . . . . . . 59 7.1.2.4 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 7.1.2.4.1 字符数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 7.1.2.4.2 数值数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 7.1.2.4.2.1 数字类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 7.1.2.4.2.2 浮点数类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 日期时间数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 7.1.2.4.3.1 DATE 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 7.1.2.4.3.2 TIMESTAMP 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . 62 格式化模型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 7.1.2.5 完整性约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 7.1.2.6 表储存 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 7.1.2.6.1 堆组织表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 7.1.2.6.2 行存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 7.1.2.6.3 空值存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 表压缩 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 临时表概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 7.1.3.1 临时表的用途 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 7.1.3.2 临时表的创建 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 外部表概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 7.1.4.1 外部表的用途 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 7.1.4.2 外部表的创建 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 索引概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 7.2.1.1 索引的优缺点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 7.2.1.2 键和列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 7.2.1.3 组合索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 7.2.1.4 唯一索引和非唯一索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 7.2.1.5 索引类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 7.2.1.6 索引的维护 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 7.2.1.7 索引存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 B 树索引概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 7.2.2.1 B 树索引中不同类型的块 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 7.2.2.2 索引扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 7.2.2.2.1 全索引扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 7.2.2.2.2 仅索引扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 7.2.2.2.3 范围索引扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 7.2.2.2.4 表达式索引扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 7.1.2.1 7.1.2.4.3 7.1.2.4.4 7.1.2.7 7.1.3 7.1.4 7.2 7.2.1 7.2.2 IV 目 录 7.2.2.3 升序和降序索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 7.2.2.4 索引压缩 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 函数索引概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 7.2.3.1 函数索引的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 7.2.3.2 函数索引的优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 分区、视图和其他模式对象 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 分区概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 分区特征 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 7.3.1.1.1 分区键 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 7.3.1.1.2 分区类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 7.3.1.1.2.1 范围分区 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 7.3.1.1.2.2 间隔分区 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 7.3.1.1.2.3 列表分区 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 7.3.1.1.2.4 哈希分区 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 7.3.1.1.2.5 组合分区 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 分区表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 表分区文件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 分区索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 本地分区索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 7.3.1.3.1.1 本地前缀索引和局部非前缀索引 . . . . . . . . . . . . . . . . . . . . . 82 7.3.1.3.1.2 本地分区索引存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 全局分区索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 视图概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 视图特征 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 7.3.2.1.1 视图的数据操作 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 7.3.2.1.2 视图的数据访问 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 物化视图概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 7.3.3.1 物化视图的特征 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 7.3.3.2 物化视图的刷新方法 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 序列概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 7.3.4.1 序列特征 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 7.3.4.2 序列的并发访问 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 同义词概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 数据完整性约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 数据完整性简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 7.4.1.1 保证数据完整性的方式 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 7.4.1.2 完整性约束的优势 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 完整性约束的类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 7.4.2.1 NOT NULL 约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 7.4.2.2 UNIQUE 约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 7.4.2.3 PRIMARY KEY 约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 7.4.2.4 FOREIGN KEY 约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 7.2.3 7.3 7.3.1 7.3.1.1 7.3.1.2 7.3.1.2.1 7.3.1.3 7.3.1.3.1 7.3.1.3.2 7.3.2 7.3.2.1 7.3.3 7.3.4 7.3.5 7.4 7.4.1 7.4.2 V 目 录 7.4.2.4.1 自引用完整性约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 7.4.2.4.2 空值和外键 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 7.4.2.4.3 父键的删除和更新 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 7.4.2.4.4 索引和外键 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 Check 约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 完整性约束的状态 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 7.4.3.1 数据检查 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 7.4.3.2 检查约束的有效性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 7.4.3.2.1 不可延迟约束 (NOT DEFERRABLE) . . . . . . . . . . . . . . . . . . . . . . 97 7.4.3.2.2 可延迟约束 (DEFERRABLE) . . . . . . . . . . . . . . . . . . . . . . . . . . 97 约束检查的示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 7.4.2.5 7.4.3 7.4.3.3 7.5 数据字典和动态性能视图 7.5.1 7.5.2 7.5.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 数据字典概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 7.5.1.1 数据字典的内容 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 7.5.1.2 数据字典的存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 7.5.1.3 数据字典的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 7.5.1.3.1 数据字典缓存 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 7.5.1.3.2 其他程序与数据字典 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 动态性能视图概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 7.5.2.1 动态性能视图的内容 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 7.5.2.2 动态性能视图的存储 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 数据库对象的元数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 版权声明 102 服务周期承诺 103 VI 第 1 章 前言 1 第 章 前言 KingbaseES 数据库概念中描述了 KingbaseES 数据库管理系统的架构和基本概念。 前言部分包含以下主题： • 适用读者 • 相关文档 • 手册约定 1.1 适用读者 KingbaseES 数据库概念面向所有使用 KingbaseES 的用户，主要是数据库管理员和应用程序开发人员。 1.2 相关文档 KingbaseES 数据库概念描述基本的概念性原理，对应的细节和操作方式在以下手册中： • 《KingbaseES 数据库参考手册》 • 《KingbaseES 数据库开发指南》 1.3 手册约定 本文档中可能出现“注意、提示、警告、另请参阅”等标志，它们所代表的含义如下： 注意: 用于突出重要/关键信息、最佳实践等。 1 第 1 章 前言 提示: 用于突出小窍门、捷径等。 警告: 用于传递设备或环境安全警示信息，若不避免，可能会导致设备损坏、数据丢失、设备性能降低或其 它不可预知的结果。 另请参阅: 用于突出参考、参阅等。 以下程序代码书写约定适用于本文档： 符号 说明 [] 表示包含一个或多个可选项。不需要输入中括号本身。 {} 表示包含两个以上（含两个）的候选，必须在其中选取一个。不需要输入花括号本身。 | 分割中括号或者花括号中的两个或两个以上选项。不需要输入“|”本身。 ... 表示其之前的元素可以被重复。 斜体 表示占位符或者需要提供特定值的变量。 大写 表示系统提供的元素，以便与用户定义的元素相互区分。除出现在方括号中的元素外，应当按 照顺序逐字输入。当然，部分元素在系统中是大小写不敏感的，因此用户可以根据系统说明以 小写形式输入。 小写 表示由用户提供的元素。 2 第 2 章 KINGBASEES 数据库简介 2 第 章 KingbaseES 数据库简介 本部分包含以下主题： • 金仓数据库管理系统简介 • 数据库概念 2.1 金仓数据库管理系统简介 金仓数据库管理系统 [简称:KingbaseES] 是一种关系型数据库，以下主要介绍数据库概念和 KingbaseES 的能力 结构。 2.1.1 KingbaseES 能力结构 KingbaseES 整体能力结构如图所示： 3 第 2 章 KINGBASEES 数据库简介 图 2.1.1: KingbaseES 能力结构图 2.2 数据库概念 2.2.1 数据库 数据库实际包括两层概念： 一方面，数据库是一个实体，它是能够合理保管数据的“仓库”，用户在该仓库中存放要管理的事务数据，” 数 据” 和” 库” 两个概念结合成为数据库。 另一方面，数据库是数据管理的一种方法和技术，它能更合适的组织数据、更方便的维护数据、更严格的控制数 据和更有效的利用数据。 2.2.2 数据库管理系统 数据库管理系统是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据 库进行统一的管理和控制，以保证数据库的安全性和完整性。 4 第 2 章 KINGBASEES 数据库简介 2.2.3 关系型数据库 关系型数据库将复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作 几乎全部建立在一个或者多个关系表格上，通过这些关联的表格分类、合并、连接或选取等运算实现数据的管理。 KingbaseES 属于典型的商用关系型数据库管理系统。 关系型数据库特点： 关系型数据库在存储数据时实际上是采用一张二维表（类似 Word 及 Excel 中表格）。通过 SQL 结构化 查询语言来存取、管理关系型数据库的数据。关系型数据库在保持数据安全和数据一致性方面很强，遵循 ACID 理论。 5 第3章 数据访问 3 第 章 数据访问 简要介绍数据访问 本部分包含以下主题： • SQL • PLSQL 3.1 SQL 本章概述结构化查询语言 (SQL) 以及 KingbaseES 数据库如何处理 SQL 语句。 本章包括以下主题： • SQL 简介 • SQL 语句概述 • 优化器概述 • SQL 处理概述 3.1.1 SQL 简介 SQL 是基于集合的高级别声明性计算机语言，所有程序和用户都使用它来访问数据库中的数据。虽然某些 KingbaseES 图形化工具和应用程序隐藏了 SQL 的使用，但实际上所有的后台数据库操作都是使用 SQL 执行的。 SQL 提供一个对关系数据库如 KingbaseES 数据库的接口。SQL 可以执行以下任务： • 创建、替换、改变、和删除对象 • 插入、更新、和删除表行 • 查询数据 • 控制对数据库及其对象的访问 6 第3章 数据访问 • 保证数据库的一致性和完整性 SQL 可以交互地使用即将语句手动地输入到程序，也可以嵌入到不同的语言（如 C 或 Java）编写的程序内。 3.1.1.1 SQL 数据访问 目前，计算机语言分两类派别：非过程化的声明性语言，它描述应该做什么；过程化语言，如 C + + 和 Java， 它描述如何做。SQL 是声明性的语言，用户指定他们想要的结果，而不必说明如何得到它。数据库的工作是生成一 个过程，来操作数据和检索请求的结果。SQL 的声明使您能够在逻辑层面使用数据，仅当您想要处理该数据时，才 需要关注其实现细节。 数据库用一个单一步骤检索满足 WHERE 条件的所有行。这些行可以作为一个整体传递给用户，或者其他 SQL 语句和应用程序。您不需要一行一行地处理，您也不需要知道行是如何被物理存储和检索的。所有 SQL 语句都使用 优化器来处理，优化器会确定访问指定数据最有效的方式。KingbaseES 数据库还支持其它多种技术，可以使优化器 更好地执行其工作。 3.1.1.2 SQL 标准 KingbaseES 遵循行业认可的标准。被行业认可的委员会包括美国国家标准协会 (ANSI) 和国际组织的标准化 (ISO)。ANSI 和 ISO/IEC 都已规定 SQL 作为关系数据库的标准语言。 1986 年 10 月，美国国家标准协会 (ANSI) 将 SQL 作为关系数据库管理系统的标准语言，制定了 ANSI SOL 标 准。随后，国际标准化组织 ISO 也颁布了 ISO SQL 国际标准。随着数据库技术的发展，后续推出了一系列的 SQL 标准，如 SQL-89，SQL-92，SQL:1999，SQL:2003，SQL:2016 等。中国的标准化机构也参考国际标准制定了一系列 的数据库标准。 目前，KingbaseES 的 SQL 语言遵循 SQL:1992 入门级和过渡级标准、SQL:1999 和 SQL:2003 的核心级标准、 SQL:2016 最新标准，并在此基础上进行了适当的扩充。没有特殊说明情况下，KingbaseES 数据库中的语句都遵循 SQL 标准。KingbaseES 数据库提供了一些其他语句工具，KSQL 和数据库对象管理工具，使您能够在 KingbaseES 数据库上运行任何 ANSI/ISO 标准 SQL 语句，以及其他语句或函数。 3.1.2 SQL 语句概述 在 KingbaseES 数据库中执行的所有操作都是使用 SQL 语句来执行的。SQL 语句是一种计算机指令，它包含标 识符、参数、变量、名称、数据类型、和 SQL 的保留字。 SQL 语句必须是一个完整的 SQL 句子，例如： SELECT orderid, customerid,orderdate FROM orders; KingbaseES 数据库仅运行完整的 SQL 语句。像执行下面这样的语句将报错: SELECT orderid; SQL 错误 [42703]: 错误：字段“orderid”不存在 7 第3章 3.1.2.1 数据访问 数据定义语言 (DDL) 语句 数据定义语言 (DDL) 语句定义、更改、和删除模式对象。DDL 能够更改对象的属性，而无需更改访问该对象的 应用程序。您还可以在当数据库用户正在数据库中执行操作时，使用 DDL 更改对象的结构。 DDL 语句可以执行以下任务： • 创建、更改、和删除模式对象和其他数据库结构，包括数据库本身和数据库用户。大部分的 DDL 语句以关键字 CREATE、ALTER、或 DROP 开头。 • 删除模式对象中的所有数据，而不删除这些对象的结构（TRUNCATE）。 • 授予和撤消权限和角色（GRANT，REVOKE）。 • 打开和关闭审核选项（AUDIT，NOAUDIT）。 • 将注释添加到数据字典（COMMENT）。 如下例子使用 DDL 语句来创建 orders 表，插入数据，再使用 DDL 更改表结构、授予和撤消用户在该表上的权 限，然后删除表: CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, orderdate DATE ); INSERT INTO orders VALUES(100,'KINGBASE','2000-01-01'); ALTER TABLE orders ADD COLUMN employeeid INT4; GRANT INSERT ON orders TO PUBLIC; REVOKE INSERT ON orders FROM PUBLIC; DROP TABLE orders; 在数据库执行 DDL 语句之前会立即执行一个隐式提交，之后立即执行一个提交或回滚。再上面的示例中，INSERT 语句后面跟了一个 ALTER TABLE 语句，因此数据库会提交这个 INSERT 语句。如果 ALTER TABLE 语句 成功，则数据库提交此语句；否则，数据库回滚此语句。在任一情况下, 这个 INSERT 语句都是已经提交的。 3.1.2.2 数据操纵语言 (DML) 数据操纵语言 (DML) 语句查询或操作模式对象中的数据。DDL 语句可以更改对象的结构，而 DML 语句使您能 够查询或更改其内容。例如，ALTER TABLE 更改一个的表的结构，而 INSERT 向表中添加一个或多个行。 DML 语句是最经常使用的 SQL 语句，它可以执行以下任务： • 从一个或多个表或视图，检索或获取数据（SELECT）。 • 通过指定一个列值列表，或使用一个子查询，来选择和操作现有数据，以将新的数据行添加到表中（INSERT）。 8 第3章 数据访问 • 更改表或视图中的现有行中的值（UPDATE）。 • 更新或有条件地将行插入到表或视图（MERGE）。 • 从表或视图中删除行（DELETE）。 • 查看 SQL 语句的执行计划（EXPLAIN PLAN）。 • 锁定一个表或视图，临时限制其他用户的访问（LOCK TABLE）。 下面的示例使用 DML 来查询 orders 表, 插入数据行、更新然后将其删除： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); SELECT * FROM orders; INSERT INTO orders(orderid, customerid, employeeid, orderdate) VALUES(101,'RDJC',1,'1999-03-20'); UPDATE orders SET employeeid=2 WHERE orderid=101; DELETE FROM orders WHERE orderid=101; 一个事务是一个逻辑工作单元的 DML 语句的集合。例如，一个学生选课事务可能涉及三个独立的操作：学生查 询课程计划、学生添加选课、课表中添加学生记录。与 DDL 的语句不同的是，DML 语句不会隐式提交当前事务。 3.1.2.2.1 SELECT 语句 查询是一个从表或视图中检索数据的操作。 SELECT 是你唯一可用于查询数据的 SQL 语句。从执行 SELECT 语句检索到的数据集被称为结果集。 下表显示了在一个 SELECT 语句中两个必需的关键字，和两个常见的关键字。该表也说明了这些关键字在 SELECT 语句中的功能。 9 第3章 数据访问 表 3.1.1: SELECT 语句 关键字 必需 描述 功能 SELECT 是 指定应在结果中显示哪些列。投影会生成表中所包含列 映射 的一个子集。表达式是一个或多个值、操作符、和 SQL 函数的组合。经过计算后最终得到一个值。出现在 SELECT 关键字之后，和在 FROM 子句之前的表达式列 表，叫做选择列表 FROM 是 指定应从哪些或表视图中检索数据。 联接 WHERE 否 指 定 一 个 条 件 来 筛 选 行， 以 生 成 表 中 的 行 的 一 个 子 选择 集。 条 件 指 定 一 个 或 多 个 表 达 式 和 逻 辑 （布 尔） 运 算 符 的 组 合， 并 返 回 一 个 值 （TRUE、FALSE、 或 UNKNOWN）。 ORDERBY 否 指定行应以何种顺序显示。 排序 3.1.2.2.2 连接 连接是联合两个或多个表、视图或物化视图中的行的查询。例如，将 customers 表与 orders 表联接 (FROM 子 句），只选择满足指定条件的行（WHERE 子句），并使用投影从两个列中检索数据（SELECT）。该 SQL 语句的 示例输出如下所示： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); INSERT INTO orders VALUES(101,'RDJC',1,'1999-03-20'); CREATE TABLE customers ( customerid VARCHAR(10) CONSTRAINT pk_customers PRIMARY KEY, companyname VARCHAR(30) NOT NULL, city VARCHAR(20) ); INSERT INTO customers VALUES('RDJC','Bejing Renda Jingcang','BEIJING'); SELECT orderid,orderdate,companyname FROM orders，customers WHERE customers.customerid = orders.customerid 10 第3章 数据访问 AND customers.customerid='RDJC'; orderid |orderdate |companyname | --------+-----------+--------------------------+ 101 |1999-03-20 |Bejing Renda Jingcang | 大多数连接在 FROM 子句或者 WHERE 子句中都至少有一个连接条件，用于比较不同表的两个列。数据库匹配 成对的行，每对包含来自不同表的行，选择这些行连接条件计算结果为 TRUE 的。优化器基于连接条件、索引和任 何可用的表统计信息，来确定数据库中联接表的顺序。 联接类型包括以下： • 内连接 内连接是两个或更多表的联接，只返回满足连接条件的行。例如，如果连接条件是 customers.customerid = orders.customerid，则不满足此条件的行不被返回。 • 外连接 外连接返回所有满足联接条件的行，也返回一个表中不满足与另一个表的连接条件的行。 左外连接的结果始终返回了左表的所有记录，即使联接条件不匹配右表的记录。如果右表匹配的行不存在，那么 右表列返回 null 值。例如，如果存在订单表中的客户编号，在客户表中无记录，orders(左表) 和 customers(右表) 的 左外连接检索订单表中的所有行，即使客户表中没有行满足连接条件。 右外连接的结果始终返回右表的所有记录，即使联接条件不匹配左表的记录。如果左表匹配的行不存在，那么左 表列返回 null 值。例如，如果存在客户表中的客户编号，在订单表中无记录，客户表 (左表) 和订单表 (右表) 的右外 连接检索客户表中的所有行，即使订单表中没有行满足连接条件。 完全外连接是左外连接和右外连接的组合。 • 笛卡儿积 如果两个表联接查询中的没有联接条件，则数据库返回其笛卡儿积。一个表中的每一行与另一个表中的每一行联 合。例如，如果 orders 表有 800 行，而 customers 表有 500 行，则其笛卡儿积包含 800x500 行。笛卡儿积是很少用 到的。 3.1.2.2.3 子查询 子查询是嵌套在另一个 SQL 语句中的 SELECT 语句。当您必须执行多个查询来解决一个问题时，子查询非常有 用。一个语句每个查询部分称为一个查询块。例如，在如下查询中，括号内的子查询是内部查询块： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); 11 第3章 数据访问 INSERT INTO orders VALUES(101,'RDJC',1,'1999-03-20'); CREATE TABLE customers ( customerid VARCHAR(10)CONSTRAINT pk_customers PRIMARY KEY, companyname VARCHAR(30) NOT NULL, city VARCHAR(20) ); INSERT INTO customers VALUES('RDJC','Bejing Renda Jingcang','BEIJING'); SELECT orderid, customerid, employeeid, orderdate FROM orders WHERE customerid IN (SELECT customerid FROM customers WHERE city='BEIJING' ); 子查询 SELECT 语句检索客户所在城市为北京的客户编号。这些客户编号需要在外部查询块中用到，外部查询 基于子查询提供客户编号来检索订单记录。 SQL 语句的结构不会强制数据库必须首先执行内部查询。数据库可能会重写整个查询，始终由优化器确定用于 检索请求行的最佳步骤序列。 3.1.2.2.4 事务控制语句 事务控制语句管理 DML 语句所做的更改，和将多个 DML 语句按事务分组。 事务控制语句可以执行以下任务： • 使一个事务的更改持久化（COMMIT）。 • 撤消在一个事务中自事务开始以来的更改（ROLLBACK），或自一个保存点以来的更改 (ROLLBACK TO SAVEPOINT)。保存点是在一个事务上下文中由用户声明的中间标记。 • 设置一个你可以回滚到的点（SAVEPOINT）。 • 设定一个事务的特性（SET TRANSACTION）。 • 指定是在每个 DML 语句之后、还是在事务时提交后，执行可延迟完整性约束检查（SET CONSTRAINT）。 下面示例创建一个保存点，然后回滚事务到保存点。该事务将向 orders 表插入 orderid 为 100 的值，但不会插入 102 的值。 CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); BEGIN; INSERT INTO orders(orderid,customerid,orderdate) VALUES(100,'KINGBASE','2000-01-01'); 12 第3章 数据访问 SAVEPOINT my_savepoint; INSERT INTO orders(orderid,customerid,orderdate) VALUES(102,'KINGBASE','2000-03-01'); ROLLBACK TO SAVEPOINT my_savepoint; COMMIT; 3.1.2.2.5 会话控制语句 会话控制语句动态地管理用户会话的属性。会话是数据库实例内存中的一个逻辑实体，表示登录到数据库中的一 个当前用户的状态。会话从该用户通过数据库验证开始，一直持续到用户断开连接或退出数据库应用程序。 会话控制语句能执行以下任务： • 通过执行一项专门的功能来改变当前会话属性（SET SESSION）。 • 启用和禁用角色，它是一组用于当前会话的权限（SET ROLE）。 例如：下面的语句将会话的时区设置为意大利： SET TIME ZONE 'Europe/Rome'; 3.1.2.2.6 系统控制语句 系统控制语句更改数据库实例的属性。KingbaseES 数据库通过执行一些系统函数来实现系统控制，默认情况下 这些函数只能被超级用户使用。它使您能够更改系统设置，例如，终止一个会话、切换日志文件和重载系统配置文 件，如下表所示： 表 3.1.2: 系统控制语句 名称 返回类型 描述 sys_cancel_backend(pidint) boolean 取消一个后端的当前查询。如果调用角色是被取 消后端的拥有者角色的成员或者调用角色已经被 授予 sys_signal_backend，这也是允许的，不过 只有超级用户才能取消超级用户的后端。 sys_reload_conf() boolean 导致服务器进程重载它们的配置文件 sys_rotate_logfile() boolean 切换服务器的日志文件 sys_terminate_backend(pidint) boolean 中 止 一 个 后 端。 如 果 调 用 角 色 是 被 取 消 后 端 的拥有者角色的成员或者调用角色已经被授予 sys_signal_backend，这也是允许的，不过只有 超级用户才能取消超级用户的后端。 13 第3章 数据访问 3.1.2.2.7 更改服务器参数语句 ALTER SYSTEM 被用来在整个数据库集簇范围内更改服务器配置参数。它比传统的手动编辑 kingbase.conf 文 件的方法更方便。ALTER SYSTEM 会把给出的参数设置写入到 kingbase.auto.conf 文件中，该文件会随着 kingbase.conf 一起被读入。把一个参数设置为 DEFAULT 或者使用 RESET 变体可以把该配置项从 kingbase.auto.conf 文件中移除。使用 RESET ALL 可以移除所有这类配置项。只有超级用户能够使用 ALTER SYSTEM。由于这个命 令直接作用于文件系统并且不能被回滚，不允许在一个事务块或者函数中使用它。 例如，设置 wal_level： ALTER SYSTEM SET wal_level = replica; 3.1.2.2.8 嵌入式的 SQL 语句 嵌入式的 SQL 语句将 DDL、DML 和事务控制语句混入过程化语言程序中。嵌入式的 SQL 是一种在您的过程化 语言应用程序中使用 SQL 的方法。另一种方法是使用一个程序 API，如开放式数据库连接 (ODBC) 或 Java 数据库 连接 (JDBC) 。 嵌入式的 SQL 语句可以执行以下任务： • 定义、分配、和释放游标（DECLARE CURSOR、OPEN、CLOSE）。 • 指定一个数据库，并连接到该数据库（DECLARE DATABASE、CONNECT）。 • 分配变量名称（DECLARE STATEMENT）。 • 初始化描述符（DESCRIB）。 • 指定如何处理错误和警告 (WHENEVER)。 • 分析并运行 SQL 语句（PREPARE、EXECUTE、EXECUTE IMMEDIATE）。 • 从数据库中检索数据（FETCH）。 3.1.3 优化器概述 数据库使用优化器来优化 SQL 语句，尝试为 SQL 语句产生最优的执行计划。 3.1.3.1 优化器的使用 优化器生成执行计划来描述执行方法和步骤。优化器通过考虑几个信息来源来确定哪种执行计划是最有效的。例 如，优化器考虑查询条件、可用的访问路径、系统收集的统计信息、以及 SQL 语句中的 HINT 等。 要执行一个 DML 语句，可以选择的方法通常是多样的，比如单表扫描可以用全表扫描或者索引扫描；两表连 接的方式可以用嵌套连接/哈希连接/排序合并连接；多表的连接顺序也可以是不同的，这些不同的方法都是可以返回 DML 语句的相同结果的，但是不同方法的效率是不同的，而优化器负责选出一个效率最高的方法，也就是最优执行 计划。 14 第3章 数据访问 在确定 SQL 语句的最佳执行计划时，优化器将执行以下操作： • 逻辑优化：基于规则做等价的语句转换，例如子查询提升、条件化简、Group By 键值消除等。 • 物理优化：做代价估算/路径选择等操作，选出一个代价最低的执行路径。 • 根据代价最低的执行路径，生成最优的执行计划。 优化器生成处理一个查询的几乎所有可能的方法，并给生成的执行计划中的每个步骤估算一个代价，最低代价的 计划被选择为要执行的查询计划。 3.1.3.2 优化器的组成 优化器包含两个主要阶段：逻辑优化和物理优化。优化器的输入是一个已解析的查询树，输出是查询的执行计 划。这两个阶段的主要作用： 1. 逻辑优化是基于规则的优化（Rule Based Optimization），它通过找出 SQL 语句的等价变换形式，使得 SQL 的执行更为高效。 2. 物理优化是基于代价的优化（Cost Based Optimization），它通过代价计算，选出代价最低的路径。 3.1.3.2.1 逻辑优化 逻辑优化是建立在关系代数基础上的优化，通过对关系代数表达式进行逻辑上的等价变化，可能会获得执行性能 更好的等式，从而达到提高查询性能的目的，优化器在逻辑优化阶段的主要任务，是根据关系代数的等价变换规则找 出更高效的变换形式。例如子查询提升、内联化通用表达式、UNION ALL 集合的展平等操作。 3.1.3.2.2 物理优化 物理路径指示优化器以何种方式实现逻辑操作，同一个逻辑操作可以有多种物理实现方式，如对表 a 的扫描可以 有全表扫描，索引扫描等，物理优化基于代价计算选择最优的路径。 代价计算会生成三种类型的数据： • 选择率 这项数据表示基于某个条件筛选出的数据占总数据集的比例。比如 name 列的选择率依赖某个查询条件，比如 name=‘张三’。 • 基数 这项数据表示估算结果集的行数。 • 代价 这项数据表示执行该路径所需要的资源大小。查询优化器会使用磁盘 I/O、CPU 使用率、和内存使用情况作为 因子来参与代价计算。如果统计信息可用，则做代价计算时使用它们来计算上述数据。统计信息可以提高测量的精确 程度。 15 第3章 3.1.3.3 数据访问 访问路径 访问路径是查询中用来检索行的技术。例如，使用索引的查询与不使用索引的查询具有不同的访问路径。通常， 索引访问路径最适合检索表行中的一小部分子集语句，而完全扫描则对访问表中的大部分表更有效。 数据库可以使用几个不同的访问路径从表中检索数据，如下所示： • 全表扫描 这种类型的扫描从一个表读取所有行，并滤掉那些不符合选择条件的行。数据库顺序扫描所有数据块。 • 索引扫描 索引扫描在索引中搜索 SQL 语句访问的索引列值。如果该语句仅访问已被索引的列，则数据库直接从索引读取 索引的列值。 优化器对访问路径的选择，基于语句的所有可用的访问路径，和使用每个访问路径或其组合的估算代价。 3.1.3.4 优化器统计 优化器统计信息是描述有关数据库和数据库中的对象的详细信息的数据集合。统计准确描述数据存储和分布，优 化器用它们来做代价估算从而评估访问路径的效率。优化器统计信息包括： • 表统计 这包括行数、块数、和平均行长等。 • 列统计 这包括非重复值数目、空值数目、和数据的分布。 • 索引统计 这包括叶块数目和索引类型。 KingbaseES 优化器依赖于表的统计信息来为查询生成最佳执行计划。这些统计信息由 ANALYZE 命令收集，它 除了直接被调用之外还可以作为 VACUUM 命令的一个可选步骤被调用。拥有准确的统计信息很重要，否则缺少统计 信息会导致代价估算不准从而产生较差的执行计划，而较差的执行计划可能会降低数据库的性能。 自动清理守护进程如果被启用，当一个表的内容变化很多时，它将自动发出 ANALYZE 命令。不过，管理员可 能更喜欢依靠手动的 ANALYZE 操作，特别是如果知道一个表的更新不会影响应用程序使用的列的统计信息。守护 进程严格地按照一个被插入或更新行数的函数来计划 ANALYZE，但它无法判断每次更新统计信息对应用业务是否都 有意义。 3.1.3.5 hint hint 是 SQL 语句中的注释，作为给优化器的一个指示。有时应用程序的设计者比优化器更了解一个特定的应 用程序的数据的详细信息，他可以选择一个运行 SQL 语句的更有效方法。应用程序设计者可以在 SQL 语句中使用 hint，来指定该语句应该如何运行。在 KingbaseES 数据库启用 hint 支持，需要在配置文件中设置 enable_hint = on。 16 第3章 数据访问 通过示例来说明 hint 使用，如下面的所示，查询 orders 表时没使用 hint 时的执行计划： EXPLAIN ANALYZE SELECT * FROM orders WHERE orderid=10250; QUERY PLAN | -----------------------------------------------------------------------------------------------------------------+ Index Scan using pk_orders on orders (cost=0.15..8.17 rows=1 width=40) (actual time=0.017..0.018 rows=1 loops=1) | Index Cond: (orderid = 10250) | Planning Time: 0.084 ms | Execution Time: 0.039 ms | 通过上面执行计划可以看到，优化器默认使用的是索引扫描 index scan。但我们知道 orderid 为 10250 的数据行 位于前几行，所以想强制使用顺序扫描，就可以启用 hint 将指令传递给优化器，如下所示： EXPLAIN ANALYZE SELECT * FROM orders WHERE orderid=10250; QUERY PLAN | -----------------------------------------------------------------------------------------------+ Seq Scan on orders (cost=0.00..24.88 rows=1 width=0) (actual time=0.019..0.020 rows=1 loops=1) | Filter: (orderid = 10250) | Rows Removed by Filter: 4 | Planning Time: 0.157 ms | Execution Time: 0.039 ms | 3.1.4 SQL 处理概述 本节说明了数据库处理创建对象的 DDL 语句、修改数据的 DML 语句、和检索数据的查询语句等的处理方式。 3.1.4.1 SQL 处理的阶段 SQL 处理的步骤分为解析、优化和执行。数据库可能会忽略某些步骤，这取决于具体的语句。 3.1.4.1.1 SQL 解析 SQL 处理的第一阶段是解析。这一阶段涉及将 SQL 语句的各个部分分离为可由其他例程处理的数据结构。 当应用程序发出 SQL 语句时，该应用程序向数据库发出一个解析调用，以准备执行该语句。解析调用会打开或 创建一个游标，它是一个对特定于会话的私有 SQL 区的句柄，其中包含了已分析的 SQL 语句和其他处理信息。 17 第3章 数据访问 在解析调用期间，数据库会执行以下检查： • 语法检查 • 语义检查 前面的检查确定在语句执行之前可以发现的错误。但一些错误不能通过解析来捕获。例如，数据库在数据转换过 程中可能会遇到死锁或错误，但这仅在语句执行中才会发生。 3.1.4.1.2 SQL 优化 查询优化是选择执行 SQL 语句的最有效手段的过程。数据库对查询的优化基于对正在访问的实际数据收集的统 计信息。优化器使用行数、数据集大小、和其他因素，来生成各种可能的执行计划，并为每个计划做代价估算。数据 库会使用具有最低代价的计划。数据库对每个唯一的 DML 语句必须至少执行一次解析，并在解析期间执行优化。 DDL 永远不会被优化，除非它包括需要优化的 DML 组件，如子查询。 3.1.4.1.3 SQL 执行 SQL 执行器执行由优化器生成的执行计划，执行计划是一个树状结构，因此执行器递归的处理每一个计划节点 来提取它所需要的行结果集，执行器本质上是按请求执行的管道机制，即从顶层节点（根节点）每次向其子节点发出 一个获取元素的请求，其子节点收到该请求后递归的向它自己的子节点发出请求，而最终的叶子节点收到该请求后， 会去真正读取表数据，然后每次向上层节点返回一行或多行数据。 举例说明： EXPLAIN SELECT * FROM t1 ORDER BY name; QUERY PLAN | ----------------------------------------------------------------+ Sort (cost=1181.12..1216.12 rows=14000 width=11) Sort Key: name -> Seq Scan on t1 | | (cost=0.00..217.00 rows=14000 width=11) | 例子中的顶层节点为 Sort，它向其子节点（SeqScan 节点）发出要数据的请求，其子节点为叶子节点，收到请求 后去读 t1 的数据表中的数据，每次获取一行数据后向上层返回，Sort 节点在获取到所有的 t1 的数据后，进行排序操 作，最终将排序后的结果发送给客户端。 在执行期间，如果数据不在内存中，则数据库将数据从磁盘读入内存。数据库还会取出确保数据完整性所需的所 有锁，并记录 SQL 执行期间所做的任何更改。 3.1.4.2 DML 和 DDL 处理之间的区别 KingbaseES 数据库对 DDL 的处理不同于 DML。例如，在创建表时，数据库并不会优化 CREATE TABLE 语 句。相反，数据库只是解析该 DDL 语句并执行该命令。与 DDL 不同，大多数 DML 语句都有查询组件, 在查询中， 游标的执行将查询生成的行放置到结果集中。 18 第3章 数据访问 数据库可以一次获取一行或分组的结果集行。在 fetch 中，数据库选择行，如果查询请求，则对行进行排序。每 次后续获取都将检索结果的另一行，直到获取最后一行为止。 3.2 PLSQL 本章包括以下主题： • 服务器端编程简介 • PL/SQL 概述 • PL/SQL 包 • 触发器概述 3.2.1 服务器端编程简介 在 SQL 之类的非过程化语言中，指定的是要操作的数据集，而不是要执行什么操作或该操作应如何执行。 在过程化语言程序中，大多数语句的执行取决于其前面或后面的语句及控制结构，如循环或条件分支，而在 SQL 中是没有的。 通过示例来解释过程化语言与非过程化语言之间的区别，假设使用 SQL 语句查询 orders 表，SELECT 语句只 是请求数据，但并不会在数据上应用程序。但是，假设您希望应用程序能够分析 orders 表的数据，来计算产品销售 量，分析某一段时间每个员工的销售情况，来计算员工的奖金。并将结果生成一份绩效表。这就需要使用条件逻辑和 程序流控制的过程化数据库开发程序，可以使用的开发方法如下所示： • 使用客户端编程，将 SQL 语句嵌入到由过程化语言如 C、C++、或 Java 等编写的应用程序中 可以将 SQL 语句置入到源代码中，并在编译之前将其提交给预编译器或 Java 翻译器。或者，您可以消除预编译 步骤，使用如 Java 数据库连接 (JDBC) 或 Oracle 调用接口（OCI）这样的 API ，使应用程序能够与数据库进行交 互。 • 使用服务器端编程，开发可以直接在数据库使用的程序 应用程序可以显式地调用 PL/SQL。您还可以创建一个触发器，它是一个存储在数据库中的命名程序单元，在响 应指定的事件时调用。 服务器端编程的主要好处，内置于数据库的功能可以在任意位置部署。由数据库而不是由应用程序确定在给定的 操作系统上执行任务的最佳方式。此外，通过在服务器上集中应用程序处理，子程序增加可伸缩性，使客户端能够重 用代码。由于子程序调用快速而高效，仅仅一个调用就可以启动一个计算密集型的存储子程序，减少了网络流量。 在 KingbaseES 数据库中可以使用 PLSQL 语言，PL/SQL 是数据库针对 SQL 的过程化扩展。PL/SQL 与数据 库集成，支持所有 KingbaseES SQL 语句、函数、和数据类型。由数据库 API 编写的应用程序，可以调用 PL/SQL 存储子程序，或发送 PL/SQL 代码块到数据库以被执行。 19 第3章 数据访问 3.2.2 PL/SQL 概述 PL/SQL 提供了一种服务器端存储的过程化语言，它易于使用并与 SQL 无缝集成，而且可移植。您可以使用 PL/SQL 程序单元的过程化模式对象，来访问和操作数据库数据。PL/SQL 程序单元一般分为如下几类： • PL/SQL 子程序，它存储在数据库中，可以通过应用程序按名称调用。当你创建一个子程序时，数据库解析该 子程序，并将其已解析的表示形式存储在数据库中。您可以将一个子程序声明为一个过程或函数。 • PL/ SQL 匿名块，它出现在您的应用程序中，没有名字，也不存储在数据库中。在很多的应用程序中，PL/ SQL 块可以出现在任何 SQL 语句可以出现的地方。 KingbaseES 数据库中嵌入了 PL/SQL 编译器和解释器，给与开发人员一个在客户端和服务器上一致和有效的开 发模式。此外，PL/SQL 存储过程可从多种数据库客户端调用，比如 Pro*C、JDBC 和 ODBC 等等。 3.2.2.1 PL/SQL 子程序 PL/SQL 子程序是一个命名的 PL/SQL 块，允许调用方提供参数（可以是输入参数、输出参数、或输入输出参 数值）。子程序解决某一具体问题，或执行相关的任务，并作为模块化的、可维护的数据库应用程序的构造块。子程 序是一个过程或函数。过程和函数基本相同，区别在于函数始终向调用者返回单个值，而过程不返回任何值。在本节 中的过程一词，可能代表过程或函数。 3.2.2.1.1 PL/SQL 子程序的优点 服务器端编程相对于客户端编程有很多的优点，如下所示： • 提高性能 相比发出一个个 SQL 语句，或将整个 PL/SQL 块的文本发送到数据库中，应用程序必须通过网络发送的信息的 量是很小的，因为信息只发送一次，并随后在使用时才调用它。已编译的过程在数据库中是随时可用的，所以在执行 时不需要编译。如果过程已在缓存池中，则数据库不需要从磁盘检索它，就可以立即开始执行。 • 内存分配 因为过程利用了数据库的共享内存功能，对于多个用户执行的同一过程，数据库只须将该过程加载一次到内存 中。在多个用户间共享代码，使应用程序对数据库内存的要求大幅减少。 • 提高生产率 过程增加了开发生产率。通过围绕一组通用的过程来设计应用程序，可以避免冗余编码。例如，您可以编写过 程，来批量处理表中的数据行。任何应用程序可以调用这些过程，而无需重写 SQL 语句。如果数据管理方法改变 了，则只须修改过程，而不必修改使用过程的应用程序。过程也许是实现代码重用的最好方式。因为连接到数据库的 任何客户端应用程序，无论它是用何种语言编写的，都可以调用过程，过程提供了在所有环境中最大的代码重用。 • 完整性 过程能改进您的应用程序完整性和一致性。通过围绕一组公用的过程开发应用程序，您可以减少编码错误的可 能性。例如，您可以测试一个子程序，来保证它将返回一个准确的结果，验证后，可以在任意数量的应用程序中重用 它，而无需重新测试。如果该过程所引用的数据结构改变了，则只须重新编译该过程。调用该过程的应用程序不需要 修改。 20 第3章 数据访问 • 安全性 过程有助于强制执行数据安全。指定了 AUTHID DEFINER 的过程按其所有者的权限来执行，而不是按当前用 户的权限。因此，您可以通过让用户只通过按定义者权限运行的过程和函数来访问数据，以限制用户执行的数据库操 作。例如，您可以只授予用户访问更新表的过程的权限，而不授予其访问表本身的权限。当用户调用该过程时，它按 其所有者的权限运行。只有有权限运行该过程的用户可以调用该过程，不能以除此之外的任何其他方式操作表数据。 3.2.2.1.2 PL/SQL 子程序的创建 独立存储的子程序是在模式级别使用 CREATE PROCEDURE 或 CREATE FUNCTION 语句创建的一个子程 序。在一个包中定义的子程序称为包子程序，并作为包的一部分。数据库将子程序作为模式对象存储在数据字典中。 每个子程序有一个规范，包括一些参数说明和一个程序体。 示例 3-2-1 创建存储过程 创建独立 PL/SQL 过程 p_insert_orders 。该过程向 orders 表中插入数据行： \set SQLTERM ; CREATE SEQUENCE orders_orderid_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999 START WITH 1 NOCACHE NOCYCLE; CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); \set SQLTERM / CREATE PROCEDURE p_insert_orders (p_customerid VARCHAR(5)) AS BEGIN INSERT INTO orders(orderid,customerid,orderdate) VALUES(NEXTVAL('orders_orderid_SEQ'),'KINGBASE', SYSDATE); END; / 21 第3章 数据访问 3.2.2.1.3 PL/SQL 子程序的执行 用户可以以多种方式交互式地执行子程序，如下所示： • 使用 KingbaseES 交互式工具，如 KSQL 或 KingbaseES 数据库对象管理工具。 • 在一个数据库应用程序代码中显式调用它。 • 在另一个 PL/SQL 过程代码中显式调用它。 有权限的用户可以通过 KSQL 或 KingbaseES 数据库对象管理工具执行示例 3-2-1 创建的 p_insert_orders 过 程，向 orders 表插入一行数据： CALL p_insert_orders ('RDJC'); 存储过程依赖于在其主体中引用的对象。数据库自动跟踪和管理这些依赖。例如，p_insert_orders 过程引用了 orders 表，如果您更改了此表的定义且影响到该过程，则该过程必须重新编译以验证它仍然工作正常。通常，数据库 自动管理这种依赖关系。 3.2.2.2 PL/SQL 包 PL/SQL 包是一组相关子程序，与其使用的游标和变量一起，作为一个单元存储在数据库中。打包的子程序可以 由应用程序或用户显式调用。Kingbase 数据库提供了一些内置包，开发应用程序时可以直接使用 Kingbase 内置包中 的过程与函数，既可以简化应用开发的复杂性，又能提高程序的运行性能。 3.2.2.2.1 PL/SQL 包的优点 PL/SQL 包为应用程序开发人员提供了许多好处，其优点如下： • 模块化 通过使用包可以把逻辑上相关的变量、函数等封装成为一个 PL/SQL 模块。每个包都易于理解，包与包之间的 接口也简单明了，定义也很方便，这样非常有利于应用开发。 • 应用程序设计简单 当设计一个应用程序时，最初需要的是包头的接口信息。可以先编写包规范代码并编译，而不需要定义包体；包 体可以直到需要完成应用程序时再定义。 • 功能增强 包中变量和游标在一个会话中会一直存在，可以被当前会话中的包函数和包存储过程共享，可以在普通的存储过 程和函数中使用包中全局的变量、游标、包的存储过程和包的函数。可以在整个事务中保持对数据的访问，而不必存 储在数据库中。 • 高效性 第一次调用包函数或包存储过程时，整个包被加载到内存，以后再访问该包的对象时，不需要再进行 I/O 操作。 对包的授权同时将包中所有的对象授权。 22 第3章 数据访问 3.2.2.2.2 PL/SQL 包的创建 分两个部分创建包：包头和包体。包头声明包的所有过程结构，而包体定义包的所有过程结构。 示例 3-2-2 创建包 如下所示，创建 PKG_orders 包头的语句的一部分，封装了用于管理订单表的几个子程序。包的每个部分用不同 的语句创建。 CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); \set SQLTERM / CREATE PACKAGE pkg_orders AS PROCEDURE p_insert_orders (P_customerid VARCHAR(5)); PROCEDURE p_orders_customers_qry(p_orderid INT,p_customerid VARCHAR(5)); FUNCTION f_order_details_extended (f_orderid INT,f_productid INT) RETURN NUMBER; FUNCTION f_order_subtotals(f_orderid INT,f_date_from DATE,f_date_to DATE)RETURN NUMBER; END PKG_orders; / CREATE PACKAGE BODY pkg_orders AS PROCEDURE p_insert_orders (p_customerid VARCHAR(5)) AS BEGIN INSERT INTO orders(orderid,customerid,orderdate) VALUES(NEXTVAL('orders_orderid_SEQ'),'KINGBASE', SYSDATE); END; PROCEDURE p_orders_customers_qry(p_orderid INT,p_customerid VARCHAR(5)) AS BEGIN NULL; END; FUNCTION f_order_details_extended (f_orderid INT,f_productid INT) RETURN NUMBER AS BEGIN NULL; END; FUNCTION f_order_subtotals(f_orderid INT,f_date_from DATE,f_date_to DATE)RETURN NUMBER AS BEGIN NULL; END; END; 23 第3章 数据访问 / 这个包头一共定义了四个过程和函数，对这个包有权限的用户都可以调用包中这些过程对象。CREATE PACKAGE BODY 命令定义在规范中声明的过程对象。包体必须在和包头相同的模式中创建。在创建包之后，您就可以开 发应用程序，并在其中调用这些公共过程或函数，或抛出包中的任何公共异常。 3.2.2.2.3 PL/SQL 包的执行 您可以从过程、应用程序和 KingbaseES 工具引用包内容。数据库应用程序在必要时显式调用包内的过程。在取 得对包的授权后，用户可以显式运行其中的任何过程。例如，可以使用 KSQL 连接数据库，然后运行示例 3-2-2 中包 PKG_orders 的存储过程： CALL pkg_orders.p_insert_orders ('RDJC'); 3.2.2.3 PL/SQL 匿名块 匿名块是一个未命名的、非持久化的 PL/SQL 单元。匿名块的典型用途包括： • 启动调用子程序和包结构元素 • 隔离异常处理 • 通过在其他 PL/SQL 块中嵌套代码来管理控制 匿名块没有存储子程序的代码重用优势。下表总结了这两类程序单元的之间的差异。 表 3.2.1: 匿名块和子程序的差异 功能 匿名块 子程序 是否指定了一个名字 否 是 是否每次使用时都要重新编译 否 否 是否存储在数据库中 否 是 是否可由其它应用程序调用 否 是 是否能返回绑定变量值 是 是 是否能返回函数值 否 是 是否能接受参数 否 是 匿名块包括一个可选的声明部分、一个可执行部分、和一个或多个可选的异常处理程序。 示例 3-2-3 创建匿名块 查询 orders 表的总行数，储存到一个变量中，并打印： 24 第3章 数据访问 CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); INSERT INTO orders(orderid,customerid,orderdate) VALUES(100,'KINGBASE','2000-01-01'); INSERT INTO orders(orderid,customerid,orderdate) VALUES(102,'KINGBASE','2000-03-01'); \set SQLTERM / DECLARE V_COUNT INT; BEGIN SELECT COUNT(*) INTO V_COUNT FROM orders; RAISE NOTICE '%',V_COUNT; END; / KingbaseES 数据库编译 PL/SQL 匿名块，并将其放在缓存池中，但它不会在数据库中存储其源代码或已编译版 本，不能在当前运行实例之外能重复使用。与过程不同的是，一个匿名块在它每次加载到内存中时都要被重新编译。 3.2.2.4 PL/SQL 语言结构 PL/SQL 块可以包括各种不同的 PL/SQL 语言结构。这些结构包括如下： • 变量和常量 您可以在存储过程、函数、或包内声明这些结构。在你需要在 SQL 或 PL/SQL 语句中捕获或提供一个值时，您 可以使用变量或常量。 • 游标 您可以在存储过程、函数、或包中显式声明一个游标，以便对 KingbaseES 数据库数据进行记录行的处理。PL/ SQL 也可以隐式声明游标。 • 异常 PL/SQL 允许您显式处理异常，即在 PL/SQL 代码的处理过程中抛出的内部的或用户定义的错误。 PL/SQL 可以运行动态 SQL 语句，动态 SQL 句被存储为字符串，可以是手工输入的或在运行时由其程序生成。 这种技术使您能够创建通用过程。例如，您可以创建一个过程，用于操作一个直到运行时才知道其名称的表。 3.2.2.5 PL/SQL 集合和记录 许多编程技术都使用集合类型，如数组、列表、嵌套表、集合等。为在数据库应用程序中支持这些技术，KingbaseES 数据库提供了集合和记录数据类型。 25 第3章 数据访问 3.2.2.5.1 集合 集合是一组有序的、具有相同类型的元素。KingbaseES 数据库提供了 TABLE 和 VARRAY 数据类型，使您能 够声明关联数组（Associative Array）、嵌套表（Nested Table）和可变数组（Varray）。 每个元素都有一个唯一的下标，以确定其在该集合中的位置。若要创建一个集合，你得首先定义一个集合类型， 然后声明一个该类型的变量。 集合的工作原理与第三代编程语言中的数组类似。此外，集合可以作为参数传递。所以，你可以使用他们在数据 库中的表、客户端应用程序、和存储子程序等之间，移入或移出数据列。 3.2.2.5.2 记录 记录 (RECORD) 是一种复合变量，可以存储不同类型的数据值，类似于 C、C++、或 Java 中的结构类型。记 录可用于容纳来自多个表行，或多个表行中的某些列的数据。这些项的类型不同，但逻辑上相关。记录为每个项都包 含了一个字段，让你可以视其为一个逻辑单元。 您可以使用%ROWTYPE 或者%TYPE 属性来声明一条记录，以表示一个表行或从游标读取的行。 3.2.2.6 PL/SQL 的运行 PL/SQL 支持解释执行。解释执行是将源代码逐条转换成目标代码，在运行时解释一条，执行一条的过程。 PL/SQL 单元存储在数据库中。当应用程序调用存储过程时，数据库会将已编译的程序单元加载到系统缓存区 中。PL/SQL 解释器和 SQL 语句执行器共同处理过程中的语句。 3.2.3 触发器概述 数据库触发器是一个已编译的存储程序单元，使用 PL/SQL 编写，数据库在某些情况下会自动调用。每当发生 下列操作之一时，触发器就会触发： 1. 由任何用户在一个特定的表或视图上，发出 DML 语句 DML 语句修改模式对象中的数据。例如，插入和删除行即是 DML 操作。 2. 由任何用户或特定用户发出 DDL 语句 DDL 语句定义模式对象。例如，创建表并添加一列即是 DDL 操作。 3. 数据库事件 用户登录或注销、错误、和数据库启动或关闭，是触发器可以调用的事件。 触发器是模式对象，类似于子程序；但其调用方法不同。子程序由用户、应用程序、或触发器显式运行。而触发 器是在触发的事件发生时由数据库隐式调用的。 26 第3章 3.2.3.1 数据访问 触发器的优势 正确使用触发器使您能够更高效的使用数据库应用程序。您可以使用触发器执行以下任务： • 自动生成派生列中的值 • 阻止无效事务 • 提供审计和事件日志记录 • 记录有关访问表的信息 您可以使用触发器强制执行对所有客户端应用程序都适用的底层业务规则。例如，几个应用程序都可能要访问 orders 表。如果此表上的某个触发器可以确保插入数据的格式，则不需要在每个客户端中重复这种业务逻辑。因为该 触发器不能被应用程序绕过，触发器中的业务逻辑会被自动应用。 您可以使用触发器或完整性约束，来定义和执行任何类型的完整性规则。但是，当不能使用一个完整性约束来 定义业务规则时，可以使用触发器来强制执行这种复杂的业务规则。但过度使用触发器，可能导致复杂的相互依赖 关系，在一个大的应用程序中可能很难维护。当调用触发器时，在其触发器操作中的 SQL 语句可能会触发其他触发 器，导致级联触发，可能会产生意想不到的结果。 3.2.3.2 触发器类型 触发器可以根据其触发方式和他们所执行的操作类型进行分类。KingbaseES 数据库支持以下类型的触发器： • 行级触发器 行触发器在表每次受到触发语句影响时触发。如果触发语句未影响任何行，则行触发器不会运行。 • 语句级触发器 语句触发器针对触发语句只触发一次，无论触发语句影响的行数。 • INSTEAD OF 触发器 数据库会触发 INSTEAD OF 触发器，而不是执行触发语句。这些触发器可用于透明地修改不能直接通过 DML 语句修改的视图。 • 事件触发器 KingbaseES 中的事件触发器对数据库来说是全局的，并且可以捕捉 DDL 事件。可以用任何事件触发器支持的 过程语言编写事件触发。只要一个与事件触发器相关的事件在事件触发器所在的数据库中发生，该事件触发器就会被 触发。事件触发器可分为以下几类： • 系统事件触发器可以由如数据库实例启动和关闭或错误消息之类的事件引起。 • 用户事件触发器会因为与用户登录和注销、DDL 语句、和 DML 语句等相关的事件而激发。 3.2.3.3 触发时间 您可以定义触发时间来决定触发器操作是要在触发语句之前运行，还是之后运行。可以选择如下的触发时间： 27 第3章 数据访问 • 触发语句之前 • 在受触发语句影响的每行之前 • 在受触发语句影响的每行之后 • 触发语句之后 对语句级和行级触发器，BEFORE 触发器可以增强安全性，并在对数据库进行更改之前启用业务规则。而 AFTER 触发器则对日志记录操作很有用。复杂触发器可以在多个时间点触发。复杂触发器提供一种编程方法，使你可 以在多个不同的时间点，对共享的数据实施操作。 3.2.3.4 触发器的创建 使用 CREATE TRIGGER 语句创建或替换数据库触发器。PL/SQL 触发器有以下基本组件： • 触发器名称 在相同的表中，触发器的名称必须是唯一的。 • 触发事件或语句 触发事件或语句是导致触发器被调用的 SQL 语句、数据库事件、或用户事件 • 触发器时间 触发程序的动作时间，可以是 BEFORE、AFTER 或 INSTEAD OF。 • 触发的操作 触发的操作是包含 SQL 语句和代码的过程，当发出一个触发语句、且触发器限制的计算结果为 true 时将被运 行。 示例 3-2-4 一个 PLSQL 触发器函数 这个例子的触发器实现以下功能：任何时候一个行在表中被插入或更新时，当前用户名和时间也会被标记在该行 中。并且它会检查给出了一个雇员的姓名以及薪水是一个正值。 --创建表 CREATE TABLE emp ( empname TEXT, salary INTEGER, last_date TIMESTAMP, last_user TEXT ); \set SQLTERM / --创建函数 CREATE FUNCTION emp_stamp() RETURNS TRIGGER AS $emp_stamp$ BEGIN 28 第3章 数据访问 -- 检查给出了 empname 以及 salary IF NEW.empname IS NULL THEN RAISE EXCEPTION 'empname CANNOT BE NULL'; END IF; IF NEW.salary IS NULL THEN RAISE EXCEPTION '% CANNOT HAVE NULL salary', NEW.empname; END IF; -- 检查工资小于 0 的异常情况 IF NEW.salary < 0 THEN RAISE EXCEPTION '% CANNOT HAVE A NEGATIVE salary', NEW.empname; END IF; -- 记录工资单的变化 NEW.last_date := CURRENT_TIMESTAMP; NEW.last_user := CURRENT_USER; RETURN NEW; END; $emp_stamp$ LANGUAGE PLSQL; --创建触发器 CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE FUNCTION emp_stamp(); / 3.2.3.5 触发器的内部执行 KingbaseES 数据库在内部使用与执行子程序相同的步骤来执行触发器。唯一的细微区别在于，如果用户有权运 行触发语句，则他有权触发该触发器。除了这个区别之外，数据库使用与存储子程序相同的方式来验证和运行触发 器。 3.2.3.6 触发器的存储 与 PL/ SQL 存储过程类似，KingbaseES 数据库将触发器以已编译形式存储在数据库模式中。当 CREATE TRIGGER 语句提交后，其编译后的 PL/SQL 代码被存储在数据库中，并重新加载到内存中。 29 第4章 存储结构 4 第 章 存储结构 本部分描写 KingbaseES 数据库的基本结构架构，包括物理存储结构和逻辑存储结构。 本部分包含以下章节： • 物理存储结构 • 逻辑存储结构 4.1 物理存储结构 • 物理存储结构简介 • 数据文件 • 控制文件 • 日志文件 • 配置文件 4.1.1 物理存储结构简介 KingbaseES 数据库初始化过程会创建一个数据库实例。KingbaseES 创建数据库实例时会自动创建数据库：security、template0、template1 和 test。每个数据库中可以包含多个数据库对象，如表、索引、序列等。 KingbaseES 数据库实例管理的所有数据在物理上都以操作系统文件的方式存放在磁盘上。 物理数据库结构可以在操作系统级别上查看。 一般而言，数据库集簇所使用的配置和数据文件都被一起存储在集簇的数据目录里，常用的目录名为 data。由 不同数据库实例所管理的多个集簇可以在同一台机器上共存。 data 目录包含几个子目录以及一些控制文件，如表”data 目录下的必要内容” 所示。除此之外，kingbase.conf、 sys_hba.conf 和 sys_ident.conf 等通常都存储在 data 中，但也可以将其存放在其他地方，如表”data 目录下的可选内 容” 所示。 30 第4章 存储结构 表 4.1.1: data 目录下的必要内容 项 类型 描述 SYS_VERSION 其他 一个包含 KingbaseES 主版本号的文件 base 数据文件 包含每个数据库对应的子目录，记录每个数据库内对象 的持久化数据或临时数据。 current_logfiles 控制文件 记录当前被日志收集器写入的日志文件的文件 global 数据文件及控制文件 包含集簇范围的表的子目录，比如 sys_database。此 外，sys_control 文件也存储在此目录下，该文件记录着 数据集簇标识符及版本、检查点信息、块大小等信息 sys_commit_ts 日志文件 包含事务提交时间戳数据的子目录 sys_csnlog 日志文件 包含事务提交序列号和子事务状态数据的子目录 sys_dynshmem 其他 包含被动态共享内存子系统所使用的文件的子目录 sys_logical 控制文件 包含用于逻辑复制的状态数据的子目录 sys_multixact 日志文件 包含多事务（multi-t ransaction）状态数据的子目录 （用于共享的行锁） sys_notify 日志文件 包含 LISTEN/NOTIFY 状态数据的子目录 sys_replslot 其他 包含复制槽数据的子目录 sys_serial 日志文件 包含已提交的可序列化事务信息的子目录 sys_snapshots 其他 包含导出的快照的子目录 sys_stat 其他 包含用于统计子系统的永久文件的子目录 sys_stat_tmp 其他 包含用于统计信息子系统的临时文件的子目录 sys_tblspc 其他 包含指向表空间目录的符号链接的子目录，该符号链接 所指向的表空间目录中存储了所属该表空间的数据文 件。 sys_twophase 日志文件 包含用于预备事务状态文件的子目录 sys_wal 日志文件 包含 WAL（预写日志）文件的子目录 sys_xact 日志文件 包含事务提交状态数据的子目录 见续表 31 第4章 存储结构 表 4.1.1 – 续表 项 类型 描述 kingbase.auto.conf 配置文件 一个用于存储由 ALTER SYSTEM 设置的配置参数的文 件 kingbase.opts 控制文件 一个记录服务器最后一次启动时使用的命令行参数的文 件 kingbase.pid 控制文件 一 个 锁 文 件， 记 录 着 当 前 的 kingbase （PID）、 集 簇 数 据 目 录 路 径、kingbase 进程 ID 启动时间 戳、端口号、Unix 域套接字目录路径（Windows 上为 空）、第一个可用的 listen_address （IP 地址或者 *， 或者为空表示不在 TCP 上监听）以及共享内存段 ID （服务器关闭后该文件不存在） 表 4.1.2: data 目录下的可选内容 项 类型 描述 kingbase.conf 配置文件 一个用于存储用户自设置的配置参数的文件 sys_hba.conf 配置文件 一个用于配置客户端认证方式的文件 sys_ident.conf 配置文件 一个用于配置客户端认证所需的用户名映射的文件 sys_log 日志文件 包含数据库在线日志文件的子目录 4.1.2 数据文件 在 KingbaseES 数据库中，数据文件被组织成一个个页面（Page），页面大小为 8k。对数据文件的 I/O 操作都 是以页面为单位。 数据文件包含数据和对象，例如表、索引、存储过程和视图。 对于实例里的每个数据库，在 data/base 目录里都有一个子目录对应，子目录的名字为该数据库在 sys_database 里的 OID。这个子目录是该数据库所有数据文件的缺省位置；特别值得一提的是，该数据库的系统目录存储在此。 在该目录下，每个表和索引都存储在独立的文件里。对于普通关系，这些文件以表或索引的 filenode 号命名，它 可以在 sys_class.relfilenode 中找到。但是对于临时关系，文件名的形式为 tBBB_FFF，其中 BBB 是创建该文件 的后台会话的后台 ID，FFF 是文件节点号。对于所有情况下，每个表和索引，在其主文件（或者说主分支）之外， 都有一个空闲空间映射分支，它存储关系中可用空闲空间的信息。空闲空间映射存储在一个文件中，该文件以节点号 加上后缀 _fsm 命名。此外，每个表还有一个可见性映射分支，存储在一个后缀为 _vm 的文件中，它用于跟踪哪些 页面已知含有非死亡元组。不被日志记录的表和索引还有第三个分支，即初始化分支，它存储在后缀为 _init 的分支 中。 32 第4章 注意: 存储结构 请注意，虽然一个表的文件节点通常和它的 OID 相匹配，但实际上并不必须如此；有些操作，比如 TRUN- CATE、REINDEX、CLUSTER 以及某些形式的 ALTER TABLE，都可以改变文件节点而同时保留 OID。我们不 应该假设文件节点和表 OID 相同。此外，对于包含 sys_class 本身在内的特定系统目录，其 sys_class.relfilenode 包 含 0。这些目录的实际文件节点号被存储在一个低层数据结构中，并且可以使用 sys_relation_filenode() 函数获取。 在表或者索引超过 1GB 之后，它就被划分成 1G 大小的段。第一个段的文件名和文件节点相同；随后的段被命 名为 filenode.1、filenode.2 等等。这样的安排避免了在某些有文件大小限制的平台上的问题（实际上，1GB 只是默认 的段尺寸。段尺寸可以在编译 KingbaseES 时使用配置选项--with-segsize 进行调整）。原则上，空闲空间映射和可见 性映射分支也可以要求多个段，但实际上这很少发生。 4.1.3 控制文件 KingbaseES 服务器使用的控制信息记录在 data 目录及子目录下的控制文件中。控制文件和对应内容见以上 data 目录下的文件列表中类型为控制文件的行。 4.1.4 日志文件 日志文件记录数据库的历史操作信息, 包含恢复数据库中的所有事务所需的信息。在 KingbaseES 中，日志文件 主要有 WAL 日志、事务日志和在线日志三类。 4.1.4.1 WAL 日志 预写式日志（Write-Ahead Logging（WAL））是保证数据完整性、实现事务日志的一种标准方法。WAL 的中心 思想是对数据文件的修改（它们是表和索引的载体）当且仅当只能发生在这些修改已经被记录在日志中之后，即在描 述这些变化的日志被刷到持久存储以后。如果遵循这种过程，将不需要在每个事务提交时刷写数据页面到磁盘，因为 知道在发生崩溃时可以使用日志来恢复数据库：任何还没有被应用到数据页面的改变可以根据其日志记录重做。 预写式日志记录在 sys_wal 中。 4.1.4.1.1 WAL 的优点 使用 WAL 显著减少了磁盘写的次数。因为只有日志文件需要被刷出到磁盘以保证事务被提交。而被事务改变的 每一个数据文件不必被刷出。日志文件被按照顺序写入，因此同步日志的代价要远小于刷写数据页面的代价。在处理 很多影响数据存储不同部分的小事务的服务器上这一点尤其明显。 使用 WAL 能够保证数据页的完整性。 提供数据库在线备份和恢复的可能。通过归档的 WAL 文件，可以支持恢复到手头的 WAL 文件包含的任意时 刻：只需要简单地安装以前的数据库的物理备份，然后重放 WAl 到自己希望的时间。另外，物理备份还不必是数据 库状态的一个即时快照--如果它是花了一段时间制作的话，因为 WAL 日志的重放将修复任何内部的不一致。 33 第4章 存储结构 4.1.4.1.2 WAL 配置 在 KingbaseES 中，主要通过设置 kingbase.conf 文件中相关参数来配置 WAL。部分 WAL 相关的参数还会影响 数据库的性能，参考服务器配置可获取有关服务器配置的一般信息。 4.1.4.2 事务日志 在 KingbaseES 中，事务的状态及可见性等信息记录在事务日志文件中，只有通过事务日志信息才能从数据文 件中得到有效的数据。因此事务日志对数据一致性是十分重要的。主要的事务日志文件有 sys_xact、sys_csnlog、 sys_multixact 等。 4.1.4.3 在线日志 在 KingbaseES 中，用户的 SQL 操作以及数据库的运行中的事件会以文本的形式记录在在线日志中，用于使用 户可以了解和分析数据库当前状态，并分析可能产生的异常。在线日志文件默认记录在 sys_log 中，并可以存放在用 户指定的其他地方。 4.1.5 配置文件 KingbaseES 主服务器的配置主要通过修改配置文件 kingbase.conf 完成。由 ALTER SYSTEM 修改的配置参数 会被数据库服务器自动记录在 kingbase.auto.conf 中。 配置文件 sys_hba.conf 和 sys_ident.conf 主要用于控制客户端认证。 4.2 逻辑存储结构 • 逻辑存储结构简介 • 表空间 • 段 • 数据块 4.2.1 逻辑存储结构简介 KingbaseES 数据库管理和组织数据库中的所有对象的逻辑空间，并将其映射到对应的物理文件中。 KingbaseES 数据库中管理和组织数据的逻辑结构单元有数据块、段和表空间。在每一个数据库内部存在着若干 个表空间，所有的数据库内部对象分别存放在这些表空间中。每个表空间中的关系又被划分为若干个段，每个段中有 若干个数据块存储实际的数据。 34 第4章 存储结构 下图展示了物理存储结构和逻辑存储结构的对应关系。 4.2.2 表空间 表空间允许在文件系统里定义那些代表数据库对象的文件存放的位置。一旦创建了表空间，那么就可以在创建数 据库对象的时候引用它。一个数据库可以有一个或多个表空间，创建数据库时自动创建系统表空间，并为缺省的默认 表空间。一个表空间只隶属于一个数据库，只有在创建了数据库之后才能创建属于它的表空间。 隶属于一个数据库的表空间用于存储该数据库的数据库对象。在创建数据库对象时可以使用 TABLESPACE 子 句指明该对象所使用的表空间；没有给出 TABLESPACE 子句，则这些对象使用缺省表空间。 用户可以通过 CREATE TABLESPACE 语句来创建自己的表空间。对已经存在的表空间，用户可以通过 DROP TABLESPACE 语句来将其删除。 KingbaseES 中的表空间允许数据库管理员在文件系统中定义用来存放表示数据库对象的文件的位置。一旦被创 建，表空间就可以在创建数据库对象时通过名称引用。 通过使用表空间，管理员可以控制一个 KingbaseES 安装的磁盘布局。这么做至少有两个用处。首先，如果初始 化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不 同的分区上，直到系统可以被重新配置。 其次，表空间允许管理员根据数据库对象的使用模式来优化性能。例如，一个很频繁使用的索引可以被放在非常 快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的 表可以存储在一个便宜但比较慢的磁盘系统上。 35 第4章 存储结构 注意: 即便是位于主要的 KingbaseES 数据目录之外，表空间也是数据库集簇的一部分。并且不能被视作数据文件的 一个自治集合。它们依赖于包含在主数据目录中的元数据，并且因此不能被附加到一个不同的数据库集簇或者单独备 份。类似地，如果丢失一个表空间（文件删除、磁盘失效等），数据库集簇可能会变成不可读或者无法启动。把一个 表空间放在一个临时文件系统（如一个内存虚拟盘）上会带来整个集簇的可靠性风险。 4.2.3 段 KingbaseES 中表或索引等对象都被记录在表空间中，并被拆分成若干个段存储。 一个段对应了一个物理文件，并只存储一个关系的部分数据。段内部会被划分为若干个数据块进行数据的管理和 组织。 4.2.4 数据块 KingbaseES 中管理数据的最小单元叫做数据块，或者也可以称为页面。一个数据块是最小的 IO 单元，即每次 读入或写出数据只能以数据块做为单位。数据库中一个数据块的大小通常是 8KB，并可以在初始化数据库实例时被 指定。该大小需要是操作系统数据块大小的整数倍。 36 第 5 章 事务 5 第 章 事务 同大多数的商业数据库一样，KingbaseES 数据库也引入了事务的概念。本章介绍 KingbaseES 中的事务以及事 务的管理。. 本部分包含以下主题： • 事务的概念 • 事务的特性 • 数据库对事务的管理 • 并发控制 5.1 事务的概念 数据库中的事务是一个独立的逻辑单元，它包括了一个或多个 SQL 语句。数据库工作的基本单位是事务，事务 是不能被拆分的。一个事务执行的结果只可能有两个：事务内的语句都成功执行，事务执行成功，即事务提交；或事 务执行失败，本事务不对数据造成任何影响，即事务回滚。为了更形象的描述事务，举一个简单的例子：银行数据库 如何处理客户转账。一个客户有这样的要求：把一笔钱从 A 帐户转到 B 帐户。这个要求可以拆分为三个独立的部 分： • 帐户 A 划走这笔钱 • 帐户 B 增加这笔钱 • 记录这笔交易 整个交易过程数据库确保最终达到如下两种状态中的一个： • 三条语句都成功执行，达到转账目的并且做交易记录。整个事务提交，数据应用到数据库。 • 其中一条语句执行过程发生错误，比如 A 帐户资金不足，B 帐户不存在，或者在整个交易过程中发生硬件故 障，数据库停机。整个事务回滚，执行成功的语句被撤销，执行失败的语句不对数据造成影响，还未执行的语 句不再执行。数据回滚到此事务未开始的状态。无论什么原因, 整个业务逻辑不允许出现诸如帐户 A 上的钱被 划走，而却没有到达帐户 B，或者这个交易没有被记录这样的中间结果，整个交易必须是原子性的，要么整个 操作成功，要么整个操作失败。数据库的事务概念正是吻合了此要求。 一条 SQL 语句成功执行标志着： 37 第 5 章 事务 • SQL 语句被解析，语法正确。 • SQL 语句是有效的，语义正确。 • 一些数据可能被更改，但是这些更改不是永久性的，在此语句所在事务提交前，数据的更改并没有在整个数据 库生效。 对于事务的执行结果，只能有提交和回滚。 • 提交操作意味着：事务内的语句对数据的改变被数据库接受。这将造成数据对于整个数据库发生永久性的改 变。 • 回滚操作意味着：这个事务内所有对数据造成的更改全部被撤销。 一条 SQL 语句执行失败会回滚整个事务。 5.2 事务的特性 为了保证数据库中的数据一致性，确保 KingbaseES 能够在并发访问和系统发生故障时对数据进行维护，事务作 为数据库工作的基本单位，具有下列四个特性，称为事务的 ACID 特性。 • 原子性（Atomicity） 一个事务对数据库的所有操作，是一个不可分割的工作单元。这些操作要么全部执行，要么什么也不做。 • 一致性（Consistency） 一个事务独立执行的结果应保持数据库的一致性，即数据不会因为事务的执行而遭受破坏。例如事务的概念里 银行转账的例子，保证了数据的一致性。 • 隔离性（Isolation） 在多个事务并发执行时，系统应保证与这些事务先后单独执行时的结果一样，此时称事务达到了隔离性的要 求，也就是在多个并发事务执行时，保证执行结果是正确的，如同单用户环境一样。隔离性是由 KingbaseES 的并发控制子系统实现的。 • 持久性（Durability） 一个事务一旦完成全部操作后，它对数据库的所有更新应永久地反映在数据库中。即使以后系统发生故障，也 应保留这个事务执行的痕迹。事务的持久性由 KingbaseES 的恢复管理子系统实现。 事务是恢复和并发控制的基本单位。KingbaseES 能够保证事务的 ACID 特性不被破坏，分为以下几种情况： • 多个事务并发运行时，不同事务的操作交叉执行。KingbaseES 保证多个事务的交叉运行不影响这些事务的原子 性。 • 事务在运行过程中被强行终止。KingbaseES 保证被强行终止的事务对数据库和其他事务没有任何影响。 38 第 5 章 事务 5.3 数据库对事务的管理 对于事务的管理，KingbaseES 支持事务的隐式和显式提交，即自动提交和非自动提交。 • 自动提交事务 默认一个 SQL 语句为一个事务，当此语句执行成功后，由系统隐式提交。数据更改被数据库接受。当 SQL 执 行失败，自然视做回滚，不对数据造成影响。KingbaseES 默认提交事务方式为自动提交。 • 非自动提交事务 事务的开始为第一个 SQL 语句。事务的结束必须用户显式的使用 COMMIT 语句提交整个事务或者使用 ROLLBACK 语句回滚整个事务。如果客户端连接断开，那么未提交的事务做回滚操作。当用户想自己控制事务的执 行，可以显式的用 BEGIN 语句开始一个事务，然后在事务结束时应该使用 COMMIT 或 ROLLBACK 语句显 式提交。 接口中也有对事务提交方式的控制，配置相应的配置文件可以使会话处于自动提交或非自动提交模式。 一个事务的提交和回滚对数据库实例造成的影响为： • 一个事务提交对数据库实例造成如下影响： – 所有数据库更改对整个数据库生效。 – 保证此事务产生的日志写到磁盘。 – 释放所有此事务进行过程中申请的锁。 – 数据库标记此事务结束。 • 一个事务回滚对数据库实例造成如下影响： – 撤销所有对数据的更改到此事务开始之前的状态。 – 释放所有此事务进行过程中申请的锁。 – 数据库标记此事务结束。 5.4 并发控制 KingbaseES 为开发者提供了一组丰富的工具来管理对数据的并发访问。在内部，数据一致性通过使用一种多版 本模型（多版本并发控制，MVCC）来维护。这就意味着每个 SQL 语句看到的都只是一小段时间之前的数据快照 （一个数据库版本），而不管底层数据的当前状态。这样可以保护语句不会看到可能由其他在相同数据行上执行更新 的并发事务造成的不一致数据，为每一个数据库会话提供事务隔离。MVCC 避免了传统的数据库系统实现并发控制 使用的封锁方法，将锁争夺最小化来允许多用户环境中的合理性能。 使用 MVCC 而不是封锁来实现并发控制的主要优点是，MVCC 中对查询（读）数据的锁请求与写数据的锁请求 不冲突，所以读不会阻塞写，而写也不阻塞读。甚至在使用由可序列化快照隔离（SSI）技术实现的最严格的可序列 化事务隔离级别时，KingbaseES 也能保持读写不冲突。 39 第 5 章 事务 在 KingbaseES 里也有表和行级别的锁功能，用于那些通常不需要完整事务隔离并且想要显式管理特定冲突点的 应用。不过，恰当地使用 MVCC 通常会提供比锁更好的性能。另外，KingbaseES 也提供咨询锁实现由应用定义语义 的封锁，这种封锁机制可以在事务或 session 级别实现不与数据库本身封锁机制冲突的封锁。 5.4.1 事务隔离 SQL 标准定义了四种隔离级别。最严格的是可序列化，定义是：一组可序列化事务的任意并发执行被保证效果 和以某种顺序一个一个执行这些事务一样。其他三种级别使用并发事务之间交互产生的现象来定义，每一个级别中都 要求必须不出现一种现象。注意由于可序列化的定义，在该级别上这些现象都不可能发生。 在各个级别上被禁止出现的现象是： • 脏读 一个事务读取了另一个并行未提交事务写入的数据。 • 不可重复读 一个事务重新读取之前读取过的数据，发现该数据已经被另一个事务（在初始读之后提交）修改。 • 幻读 一个事务重新执行一个返回符合一个搜索条件的行集合的查询，发现满足条件的行集合因为另一个最近提交的 事务而发生了改变。 • 序列化异常 成功提交一组事务的结果与这些事务所有可能的串行执行结果都不一致。 SQL 标准和 KingbaseES 实现的事务隔离级别在下表事务隔离级别中描述。 表 5.4.1: 事务隔离级别 隔离级别 脏读 不可重复读 幻读 序列化异常 读未提交 可能，但在 KingbaseES 中不可能 可能 可能 可能 读已提交 不可能 可能 可能 可能 可重复读 不可能 不可能 可能，但在 KingbaseES 中不可能 可能 可序列化 不可能 不可能 不可能 不可能 在 KingbaseES 中，可以使用四种标准事务隔离级别中的任意一种，但是内部只实现了三种不同的隔离级别， KingbaseES 的读未提交级别的行为和读已提交相同。这是把标准隔离级别映射到 KingbaseES 的多版本并发控制架 构的唯一合理的方法。 表格中也表明了 KingbaseES 的可重复读实现不允许幻读。SQL 标准允许这种更严格的定义：四种隔离级别只定 义了哪种现像不能发生，但是没有定义哪种现像必须发生。可用的隔离级别的行为将在下面的小节中详细描述。 要设置一个事务的事务隔离级别，使用 SET TRANSACTION 命令。 40 第 5 章 事务 注意: 某些 KingbaseES 数据类型和函数关于事务的行为有特殊的规则。特别是，对一个序列的修改（以及用 serial 声明的一列的计数器）是立刻对所有其他事务可见的，并且在作出该修改的事务中断时也不会被回滚。 • 读已提交隔离级别 • 可重复读隔离级别 • 可序列化隔离级别 5.4.1.1 读已提交隔离级别 读已提交是 KingbaseES 中的默认隔离级别。当一个事务运行使用这个隔离级别时，一个查询（没有 FOR UPDATE/SHARE 子句）只能看到查询开始之前已经被提交的数据，而无法看到未提交的数据或在查询执行期间其它事 务提交的数据。实际上，SELECT 查询看到的是一个在查询开始运行的瞬间该数据库的一个快照。不过 SELECT 可 以看见在它自身事务中之前执行的更新的效果，即使它们还没有被提交。还要注意的是，即使在同一个事务里两个相 邻的 SELECT 命令可能看到不同的数据，因为其它事务可能会在第一个 SELECT 开始和第二个 SELECT 开始之间 提交。 UPDATE、DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在搜索目标行时的行为和 SELECT 一样：它们将只找到在命令开始时已经被提交的行。不过，在被找到时，这样的目标行可能已经被其它并发 事务更新（或删除或锁住）。在这种情况下，即将进行的更新将等待第一个更新事务提交或者回滚（如果它还在进行 中）。如果第一个更新事务回滚，那么它的作用将被忽略并且第二个事务可以继续更新最初发现的行。如果第一个更 新事务提交，若该行被第一个更新者删除，则第二个更新事务将忽略该行，否则第二个更新者将试图在该行的已被更 新的版本上应用它的操作。该命令的搜索条件（WHERE 子句）将被重新计算来看该行被更新的版本是否仍然符合搜 索条件。如果符合，则第二个更新者使用该行的已更新版本继续其操作。在 SELECT FOR UPDATE 和 SELECT FOR SHARE 的情况下，这意味着把该行的已更新版本锁住并返回给客户端。 带有 ON CONFLICT DO UPDATE 子句的 INSERT 行为类似。在读已提交模式，要插入的每一行将被插入或 者更新。除非有其他错误出现，这两种结果之一一定会发生。如果在另一个事务中发生了冲突的修改，并且其效果对 于 INSERT 还不可见，则 UPDATE 子句将会影响那行数据，即便那一行对于该命令来说还没有可见版本。 带有 ON CONFLICT DO NOTHING 子句的 INSERT 有可能因为另一个效果对 INSERT 快照不可见的事务的 结果无法让插入进行下去。再次强调，这只是读已提交模式中的情况。 因为上面的规则，正在更新的命令可能会看到一个不一致的快照：它们可以看到并发更新命令在它尝试更新的相 同行上的效果，但是却看不到那些命令对数据库里其它行的效果。这样的行为令读已提交模式不适合用于涉及复杂搜 索条件的命令。不过，它对于更简单的情况是正确的。例如可以考虑用这样的命令更新银行余额： BEGIN; UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534; COMMIT; 如果两个这样的事务同时尝试修改帐号 12345 的余额，那我们很明显希望第二个事务从账户行的已更新版本上开 始工作。因为每个命令只影响一个已经决定了的行，让它看到行的已更新版本不会导致任何麻烦的不一致性。 41 第 5 章 事务 在读已提交模式中，更复杂的使用可能产生不符合需要的结果。例如：考虑一个在数据上操作的 DELETE 命 令，它操作的数据正被另一个命令从它的限制条件中移除或者加入，例如，假定 website 是一个两行的表，两行的 website.hits 等于 9 和 10： BEGIN; UPDATE website SET hits = hits + 1; -- run from another session: DELETE FROM website WHERE hits = 10; COMMIT; 即便在 UPDATE 之前有一个 website.hits = 10 的行，DELETE 将不会产生效果。这是因为更新之前的行值 9 被跳过，并且当 UPDATE 完成并且 DELETE 获得一个锁，新行值不再是 10 而是 11，这再也不匹配条件了。 因为在读已提交模式中，每个命令都是从一个新的快照开始的，而这个快照包含在该时刻已提交的事务，因此同 一事务中的后续命令将看到任何已提交的并行事务的效果。以上的焦点在于单个命令是否看到数据库的绝对一致的视 图。 读已提交模式提供的部分事务隔离对于许多应用而言是足够的，并且这个模式速度快并且使用简单。不过，它不 是对于所有情况都够用。做复杂查询和更新的应用可能需要比读已提交模式提供的更严格一致的数据库视图。 5.4.1.2 可重复读隔离级别 可重复读隔离级别只看到在事务开始之前被提交的数据；它从来看不到未提交的数据或者并行事务在本事务执行 期间提交的修改（不过，查询能够看见在它的事务中之前执行的更新，即使它们还没有被提交）。这是比 SQL 标准 对此隔离级别所要求的更强的保证，并且阻止表事务隔离级别中描述的除了序列化异常之外的所有现象。如上面所提 到的，这是标准特别允许的，标准只描述了每种隔离级别必须提供的最小保护。 这个级别与读已提交不同之处在于，一个可重复读事务中的查询可以看见在事务中第一个非事务控制语句开始时 的一个快照，而不是事务中当前语句开始时的快照。因此，在一个单一事务中的后续 SELECT 命令看到的是相同的 数据，即它们看不到其他事务在本事务启动后提交的修改。 使用这个级别的应用必须准备好由于序列化失败而重试事务。 UPDATE、DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在搜索目标行时的行为和 SELECT 一样：它们将只找到在事务开始时已经被提交的行。不过，在被找到时，这样的目标行可能已经被其它并发 事务更新（或删除或锁住）。在这种情况下，可重复读事务将等待第一个更新事务提交或者回滚（如果它还在进行 中）。如果第一个更新事务回滚，那么它的作用将被忽略并且可重复读事务可以继续更新最初发现的行。但是如果第 一个更新事务提交（并且实际更新或删除该行，而不是只锁住它），则可重复读事务将回滚并带有如下消息 ERROR: could not serialize access due to concurrent update 因为一个可重复读事务无法修改或者锁住被其他在可重复读事务开始之后的事务改变的行。 当一个应用接收到这个错误消息，它应该中断当前事务并且从开头重试整个事务。在第二次执行中，该事务将见 到作为其初始数据库视图一部分的之前提交的改变，这样在使用行的新版本作为新事务更新的起点时就不会有逻辑冲 突。 注意只有更新事务可能需要被重试；只读事务将永远不会有序列化冲突。 42 第 5 章 事务 可重复读模式提供了一种严格的保证，在其中每一个事务看到数据库的一个完全稳定的视图。不过，这个视图并 不需要总是和同一级别上并发事务的某些序列化（一次一个）执行保持一致。例如，即使这个级别上的一个只读事务 可能看到一个控制记录被更新，这显示一个批处理已经被完成但是不能看见作为该批处理的逻辑组成部分的一个细节 记录，因为它读取空值记录的一个较早的版本。如果不小心地使用显式锁来阻塞冲突事务，尝试用运行在这个隔离级 别的事务来强制业务规则不太可能正确地工作。 注意: 在 KingbaseES V8R3 版本之前，一个对于可序列化事务隔离级别的请求会提供和这里描述的完全一样的行 为。为了保持之前版本的可序列化行为，现在应该请求可重复读级别。 5.4.1.3 可序列化隔离级别 可序列化隔离级别提供了最严格的事务隔离。这个级别为所有已提交事务模拟序列事务执行；就好像事务被按照 序列一个接着另一个被执行，而不是并行地被执行。但是，和可重复读级别相似，使用这个级别的应用必须准备好因 为序列化失败而重试事务。事实上，这个隔离级别完全像可重复读一样地工作，除了它会监视一些条件，这些条件可 能导致一个可序列化事务的并发集合的执行产生的行为与这些事务所有可能的序列化（一次一个）执行不一致。这种 监控不会引入超出可重复读之外的阻塞，但是监控会产生一些负荷，并且对那些可能导致序列化异常的条件的检测可 能触发一次序列化失败的错误。 例如，考虑一个表 mytab，它初始时包含： class | value ------+------1 | 10 1 | 20 2 | 100 2 | 200 假设可序列化事务 A 计算： SELECT SUM(value) FROM mytab WHERE class = 1; 并且接着把结果（3）作为一个新行的 value 插入，新行的 class = 2。同时，可序列化事务 B 计算： SELECT SUM(value) FROM mytab WHERE class = 2; 并得到结果 300，它会将其与 class = 1 插入到一个新行中。然后两个事务都尝试提交。如果其中一个事务运行 在可重复读隔离级别，两者都被允许提交；但是由于没有执行的序列化顺序能在结果上一致，使用可序列化事务将允 许一个事务提交并且将回滚另一个并伴有这个消息： ERROR: could not serialize access due to read/write dependencies among transactions 43 第 5 章 事务 这是因为，如果 A 在 B 之前执行，B 将计算得到合计值 330 而不是 300，而且相似地另一种顺序将导致 A 计算 出一个不同的合计值。 当依赖可序列化事务来阻止异常时，重要的一点是任何从一个持久化用户表读出数据都不被认为是有效的，直到 读它的事务已经成功提交为止。即便是对只读事务也是如此，除了在一个可推迟的只读事务中读取的数据是读出以 后立刻有效的，因为这样的一个事务在开始读取任何数据之前会等待，直到它能获得一个快照保证来避免这种问题 为止。在所有其他情况下，应用不能依靠在一个后来被中断的事务中读取的结果；相反，它们应当重试事务直到它成 功。 KingbaseES 使用谓词锁保证真正的可序列化，系统会保存这些锁以便能够判断什么时候一个写操作会对一个并 发事务中先发生的读取结果产生影响。在 KingbaseES 中，这些锁并不导致任何阻塞，因此不会导致任何死锁。它们 被用来标识并发可序列化事务之间的依赖性，这些事务的组合可能导致序列化异常。作为对比，在读已提交或可重复 读级别事务如果想要保证数据一致性，可能需要持有表级的锁，这可能阻塞其他尝试使用该表的用户；或者通过 SELECT FOR UPDATE 或 SELECT FOR SHARE 实现，在可能阻塞其他事务的同时还会导致磁盘 I/O。 像大部分其他数据库系统，KingbaseES 中的谓词锁基于被一个事务真正访问的数据。这些谓词锁将显示在 sys_locks 系统视图中，它们的 mode 为 SIReadLock。这种在一个查询执行期间获得的特别的锁将依赖于该查询所使 用的计划，并且在事务过程中多个细粒度锁（如元组锁）可能和少量粗粒度锁（如页面锁）相结合来防止耗尽用于跟 踪锁的内存。如果一个 READ ONLY 事务检测到不会有导致序列化异常的冲突发生，它可以在完成前释放其 SIRead 锁。事实上，READ ONLY 事务将常常可以在启动时确立这一事实并避免拿到任何谓词锁。如果你显式地请求一个 SERIALIZABLE READ ONLY DEFERRABLE 事务，它将阻塞直到它能够确立这一事实（这是唯一一种可序列化 事务阻塞但可重复读事务不阻塞的情况）。在另一方面，SIRead 锁常常需要被保持到事务提交之后，直到重叠的读 写事务完成。 坚持使用可序列化事务可以简化开发。成功提交的并发可序列化事务的任意集合将得到和一次运行一个相同效果 的这种保证意味着，如果你能证明一个单一事务在独自运行时能做正确的事情，则你可以相信它在任何混合的可序列 化事务中也能做正确的事情，即使它不知道那些其他事务做了些什么，否则它将不会成功提交。重要的是使用这种技 术的环境有一种普遍的方法来处理序列化失败（总是会返回一个 SQLSTATE 值’40001’），因为它将很难准确地预计 哪些事务可能为读/写依赖性做贡献并且需要被回滚来阻止序列化异常。读/写依赖性的监控会产生开销，如重启被序 列化失败中止的事务，但是作为在该开销和显式锁及 SELECT FOR UPDATE 或 SELECT FOR SHARE 导致的阻 塞之间的一种平衡，可序列化事务是在某些环境中最好性能的选择。 虽然 KingbaseES 的可序列化事务隔离级别只允许并发事务在能够证明有一种串行执行能够产生相同效果的前提 下提交，但它却不能总是阻止在真正的串行执行中不会发生的错误产生。尤其是可能会看到由于可序列化事务重叠执 行导致的唯一约束被违背的情况，这些情况即便在尝试插入键之前就显式地检查过该键不存在也会发生。避免这种问 题的方法是，确保所有插入可能会冲突的键的可序列化事务首先显式地检查它们能不能那样做。例如，试想一个要求 用户输入新键的应用，它会通过尝试查询用户给出的键来检查键是否已经存在，或者是通过选取现有最大的键并且加 一来产生一个新键。如果某些可序列化事务不遵循这种协议而直接插入新键，则也可能会报告唯一约束被违背，即便 在并发事务串行执行的情况下不会发生唯一约束被违背也是如此。 当依赖可序列化事务进行并发控制时，为了最佳性能应该考虑一下问题： 在可能时声明事务为 READ ONLY。 控制活动连接的数量，如果需要使用一个连接池。这总是一个重要的性能考虑，但是在一个使用可序列化事务的 繁忙系统中这尤为重要。 只在一个单一事务中放完整性目的所需要的东西。 44 第 5 章 事务 不要让连接不必要地处于“idle in transaction”状态。配置参数 idle_in_transaction_session_timeout 可以被用 来自动断开此类连接。 在那些由于使用可序列化事务自动提供的保护的地方消除不再需要的显式锁、SELECT FOR UPDATE 和 SELECT FOR SHARE。 当系统因为谓词锁表内存短缺而被强制结合多个页面级谓词锁为一个单一的关系级谓词锁时，序列化失 败的比例可能会上升。你可以通过增加 max_pred_locks_per_transaction、max_pred_locks_per_relation 和 max_pred_locks_per_page 来避免这种情况。 一次顺序扫描将总是需要一个关系级谓词锁。这可能导致序列化失败的比例上升。通过缩减 random_page_cost 和/或增加 cpu_tuple_cost 来使优化器倾向使用索引扫描将有助于缓解此类问题。 5.4.2 显式锁定 KingbaseES 提供了多种锁模式用于控制对表中数据的并发访问。这些模式可以用于在 MVCC 无法给出期望行为 的情境中由应用控制的锁。同样，大多数 KingbaseES 命令会自动要求恰当的锁以保证被引用的表在命令的执行过程 中不会以一种不兼容的方式删除或修改（例如，TRUNCATE 无法安全地与同一表中上的其他操作并发地执行，因此 它在表上获得一个排他锁来保障这个行为）。 要检查在一个数据库服务器中当前未解除的锁列表，可以使用 sys_locks 系统视图。有关监控锁管理器子系统状 态的更多信息，请参考监控数据库活动。 • 表级锁 • 行级锁 • 页级锁 • 死锁 • 咨询锁 • 锁定和索引 5.4.2.1 表级锁 下面的列表显示了可用的锁模式和 KingbaseES 自动使用它们的场合。也可以用 LOCK 命令显式获得这些锁。请 记住所有这些锁模式都是表级锁，即使它们的名字包含“row”单词（这些名称是历史遗留问题）。在一定程度上， 这些名字反应了每种锁模式的典型用法—但是语意却都是一样的。两种锁模式之间真正的区别是它们有着不同的冲 突锁模式集合。两个事务在同一时刻不能在同一个表上持有属于相互冲突模式的锁（但是，一个事务决不会和自身冲 突。例如，它可以在同一个表上获得 ACCESS EXCLUSIVE 锁然后接着获取 ACCESS SHARE 锁）。非冲突锁模式 可以由许多事务同时持有。请特别注意有些锁模式是自冲突的（例如，在一个时刻 ACCESS EXCLUSIVE 锁不能被 多于一个事务持有) 而其他锁模式不是自冲突的（例如，ACCESS SHARE 锁可以被多个事务持有)。 表级锁模式 • ACCESS SHARE 45 第 5 章 事务 只与 ACCESS EXCLUSIVE 锁模式冲突。SELECT 命令在被引用的表上获得一个这种模式的锁。通常，任何 只读取表而不修改它的查询都将获得这种锁模式。 • ROW SHARE 与 EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在目标表上取得一个这种模式的锁（加上在被引用但没有选择 FOR UPDATE/FOR SHARE 的任何其他表 上的 ACCESS SHARE 锁）。 • ROW EXCLUSIVE 与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。命令 UPDATE、DELETE 和 INSERT 在目标表上取得这种锁模式（加上在任何其他被引用表上的 ACCESS SHARE 锁）。通常，这种锁模式将被任何修改表中数据的命令取得。 • SHARE UPDATE EXCLUSIVE 与 SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式保护一个表不受并发模式改变和 VACUUM 运行的影响。由 VACUUM（不 带 FULL）、ANALYZE、CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY, CREATE STATISTICS, 以及 ALTER INDEX 和 ALTER TABLE 的变体获得 (更多信息，参见 ALTER INDEX and ALTER TABLE)。 • SHARE 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式保护一个表不受并发数据改变的影响。由 CREATE INDEX（不带 CONCURRENTLY）取得。 • SHARE ROW EXCLUSIVE 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式保护一个表不受并发数据修改所影响，并且是自排他 的，这样在一个时刻只能有一个会话持有它。由 CREATE TRIGGER 和很多 ALTER TABLE 的很多形式所获 得（见 ALTER TABLE）。 • EXCLUSIVE 与 ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。这种模式只允许并发的 ACCESS SHARE 锁， 即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。由 REFRESH MATERIALIZED VIEW CONCURRENTLY 获得。 • ACCESS EXCLUSIVE 与所有模式的锁冲突（ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE）。这种模式保证持 有者是访问该表的唯一事务。由 ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、 VACUUM FULL 和 REFRESH MATERIALIZED VIEW（不带 CONCURRENTLY）命令获取。ALTER INDEX 和 ALTER TABLE 的很多形式也在这个层面上获得锁（见 ALTER TABLE）。这也是未显式指定模式 的 LOCK TABLE 命令的默认锁模式。 46 第 5 章 事务 注意: 只有 ACCESS EXCLUSIVE 锁阻塞 SELECT（不带 FOR UPDATE/SHARE）语句。 一旦被获取，一个锁通常将被持有直到事务结束。但是如果在建立保存点之后才获得锁，那么在回滚到这个保存 点的时候将立即释放该锁。这与 ROLLBACK 取消保存点之后所有的影响的原则保持一致。同样的原则也适用于在 PL/SQL 异常块中获得的锁：一个跳出块的错误将释放在块中获得的锁。 表 5.4.2: 冲突的锁模式 请求的锁模式 当前的锁模式 ACCESS ROW ROW SHARE SHARE SHARE EXCLUSIVE ACCESS SHARE SHARE EX- UP- ROW EXCLU- CLU- DATE EX- SIVE SIVE EX- CLU- CLU- SIVE SIVE ACCESS X SHARE ROW SHARE ROW EX- X X X X X X X X X X X X X CLUSIVE SHARE UP- DATE EX- X CLUSIVE SHARE X X SHARE ROW X X X X X X X X X X X X X X X X X X X X EXCLUSIVE EXCL USIVE ACCESS EX- X CLUSIVE 5.4.2.2 行级锁 除了表级锁以外，KingbaseES 中还有行级锁，下文列出了行级锁以及在哪些情境下 KingbaseES 会自动使用它 们。行级锁的完整冲突表请见表” 冲突的行级锁”。注意同一个事务可能会在相同的行上持有冲突的锁，甚至是在不 同的子事务中。但是除此之外，两个事务永远不可能在相同的行上持有冲突的锁。行级锁不影响数据查询，它们只阻 47 第 5 章 事务 塞对同一行的写入者和加锁者。 行级锁模式 • FOR UPDATE FOR UPDATE 会导致由 SELECT 语句检索到的行被锁定，就好像它们要被更新。这可以阻止它们被其他事 务锁定、修改或者删除，一直到当前事务结束。也就是说其他尝试 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者 SELECT FOR KEY SHARE 这些 行的事务将被阻塞，直到当前事务结束。反过来，SELECT FOR UPDATE 将等待已经在相同行上运行以上这 些命令的并发事务，并且接着锁定并且返回被更新的行（或者没有行，因为行可能已被删除）。不过，在一个 REPEATABLE READ 或 SERIALIZABLE 事务中，如果一个要被锁定的行在事务开始后被更改将会抛出一个 错误。任何在一行上的 DELETE 命令也会获得 FOR UPDATE 锁模式，在某些列上修改值的 UPDATE 也会 获得该锁模式。当前 UPDATE 情况中被考虑的列集合是那些具有能用于外键的唯一索引的列（所以部分索引 和表达式索引不被考虑），但是这种要求未来有可能会改变。 • FOR NO KEY UPDATE 行为与 FOR UPDATE 类似，不过获得的锁较弱：这种锁将不会阻塞尝试在相同行上获得锁的 SELECT FOR KEY SHARE 命令。任何不获取 FOR UPDATE 锁的 UPDATE 也会获得这种锁模式。 • FOR SHARE 行为与 FOR NO KEY UPDATE 类似，不过它在每个检索到的行上获得一个共享锁而不是排他锁。一个共享锁 会阻塞其他事务在这些行上执行 UPDATE、DELETE、SELECT FOR UPDATE 或者 SELECT FOR NO KEY UPDATE，但是它不会阻止它们执行 SELECT FOR SHARE 或者 SELECT FOR KEY SHARE。 • FOR KEY SHARE 行为与 FOR SHARE 类似，不过锁较弱：SELECT FOR UPDATE 会被阻塞，但是 SELECT FOR NO KEY UPDATE 不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的 DELETE 或者 UPDATE，但不会阻塞 其他 UPDATE，也不会阻止 SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者 SELECT FOR KEY SHARE。 KingbaseES 不会在内存里保存任何关于已修改行的信息，因此对一次锁定的行数没有限制。但是，锁住一行会 导致一次磁盘写，例如，SELECT FOR UPDATE 将修改选中的行以标记它们被锁住，并且因此会导致磁盘写入。 表 5.4.3: 冲突的行级锁 请求的锁模式 当前的锁模式 FOR KEY SHARE FOR SHARE FOR NO KEY UPDATE FOR KEY SHARE X FOR SHARE FOR NO KEY UPDATE FOR UPDATE FOR UPDATE X X X X X X X X X 48 第 5 章 事务 5.4.2.3 页级锁 除了表级别和行级别的锁以外，页面级别的共享/排他锁被用来控制对共享缓冲池中表页面的读/写。这些锁在行 被获取或者更新后马上被释放。应用开发者通常不需要关心页级锁，在这里提到它们只是为了完整性。 5.4.2.4 死锁 显式锁定的使用可能会增加死锁的可能性，死锁是指两个（或多个）事务相互持有对方想要的锁。例如，如果事 务 1 在表 A 上获得一个排他锁，同时试图获取一个在表 B 上的排他锁，而事务 2 已经持有表 B 的排他锁，同时却正 在请求表 A 上的一个排他锁，那么两个事务就都不能进行下去。KingbaseES 能够自动检测到死锁情况并且会通过中 断其中一个事务从而允许其它事务完成来解决这个问题（具体哪个事务会被中断是很难预测的，而且也不应该依靠这 样的预测）。 要注意死锁也可能会作为行级锁的结果而发生（并且因此，它们即使在没有使用显式锁定的情况下也会发生)。 考虑如下情况，两个并发事务在修改一个表。第一个事务执行： UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111; 这样就在指定帐号的行上获得了一个行级锁。然后，第二个事务执行： UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222; UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111; 第一个 UPDATE 语句成功地在指定行上获得了一个行级锁，因此它成功更新了该行。但是第二个 UPDATE 语 句发现它试图更新的行已经被锁住了，因此它等待持有该锁的事务结束。事务二现在就在等待事务一结束，然后再继 续执行。现在，事务一执行： UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222; 事务一试图在指定行上获得一个行级锁，但是它得不到：事务二已经持有了这样的锁。所以它要等待事务二完 成。因此，事务一被事务二阻塞，而事务二也被事务一阻塞：一个死锁。KingbaseES 将检测这样的情况并中断其中 一个事务。 防止死锁的最好方法通常是保证所有使用一个数据库的应用都以一致的顺序在多个对象上获得锁。在上面的例子 里，如果两个事务以同样的顺序更新那些行，那么就不会发生死锁。我们也应该保证一个事务中在一个对象上获得的 第一个锁是该对象需要的最严格的锁模式。如果我们无法提前做到这些，那么可以通过重试因死锁而中断的事务来即 时处理死锁。 只要没有检测到死锁情况，请求一个表级或行级锁的事务将无限等待冲突锁被释放。这意味着一个应用长时间保 持事务开启不是什么好事（例如等待用户输入）。 5.4.2.5 咨询锁 KingbaseES 提供了一种方法创建由应用定义其语义的锁。这种锁被称为咨询锁，因为系统并不强迫其使用—而 是由应用来保证其正确的使用。咨询锁可用于 MVCC 模型不适用的锁定策略。例如，咨询锁的一种常用用法是模拟 49 第 5 章 事务 悲观锁策略。虽然一个存储在表中的标志可以被用于相同目的，但咨询锁更快、可以避免表膨胀并且会由服务器在会 话结束时自动清理。 有两种方法在 KingbaseES 中获取一个咨询锁：在会话级别或在事务级别。一旦在会话级别获得了咨询锁，它将 被保持直到被显式释放或会话结束。不同于标准锁请求，会话级咨询锁请求不保持事务语义：在一个后来被回滚的事 务中得到的锁在回滚后仍然被保持。一个锁在它所属的进程中可以被获取多次；对于每一个完成的锁请求必须有一个 相应的解锁请求，直至锁被真正释放。在另一方面，事务级锁请求的行为更像普通锁请求：在事务结束时会自动释放 它们，不需要显式的解锁操作。这种行为通常比会话级别的行为更方便，因为它使用一个咨询锁的时间更短。对于同 一咨询锁标识符的会话级别和事务级别的锁请求按照期望将彼此阻塞。如果一个会话已经持有了一个给定的咨询锁， 由它发出的对这个锁的请求将总是成功，即使有其他会话在等待该锁；不管现有的锁和新请求是处在会话级别还是事 务级别，这种说法都是成立的。 和所有 KingbaseES 中的锁一样，当前被任何会话所持有的咨询锁的完整列表可以在 sys_locks 系统视图中找 到。 咨询锁和普通锁都被存储在一个共享内存池中，它的尺寸由 max_locks_per_transaction 和 max_connections 配置变量定义。需要注意不要耗尽这些内存，否则服务器将不能再授予任何锁。这对服务器可以授予的咨询锁数量设 置了一个上限，根据服务器的配置不同，这个限制通常是数万到数十万。 在使用咨询锁方法的特定情况下，特别是查询中涉及显式排序和 LIMIT 子句时，由于 SQL 表达式被计算的顺 序，必须小心控制锁的获取。例如： SELECT sys_advisory_lock(id) FROM foo WHERE id = 12345; -- ok SELECT sys_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger! SELECT sys_advisory_lock(q.id) FROM ( SELECT id FROM foo WHERE id > 12345 LIMIT 100 ) q; -- ok 在上述查询中，第二种形式是危险的，因为不能保证在锁定函数被执行之前应用 LIMIT。这可能导致获得某 些应用不期望的锁，并因此在会话结束之前无法释放。从应用的角度来看，这样的锁将被挂起，虽然它们仍然在 sys_locks 中可见。 5.4.2.6 锁定和索引 尽管 KingbaseES 提供对表数据访问的非阻塞读/写，但并非 KingbaseES 中实现的每一个索引访问方法当前都能 够提供非阻塞读/写访问。不同的索引类型按照下面方法操作： • B-tree、GiST 和 SP-GiST 索引 短期的页面级共享/排他锁被用于读/写访问。每个索引行被取得或被插入后立即释放锁。这些索引类型提供了 无死锁情况的最高并发性。 • Hash 索引 Hash 桶级别的共享/排他锁被用于读/写访问。锁在整个 Hash 桶处理完成后释放。Hash 桶级锁比索引级的锁 提供了更好的并发性但是可能产生死锁，因为锁持有的时间比一次索引操作时间长。 50 第 5 章 事务 • GIN 索引 短期的页面级共享/排他锁被用于读/写访问。锁在索引行被插入/抓取后立即释放。但要注意的是一个 GIN 索 引值的插入通常导致对每行产生几个索引键的插入，因此 GIN 可能为了插入一个单一值而做大量的工作。目 前，B-tree 索引为并发应用提供了最好的性能。因为它还有比 Hash 索引更多的特性，在那些需要对标量数据 进行索引的并发应用中，我们建议使用 B-tree 索引类型。在处理非标量类型数据的时候，B-tree 就没什么用 了，应该使用 GiST、SP-GiST 或 GIN 索引替代。 51 第6章 6 第 章 数据库实例体系结构 数据库实例体系结构 本部分包含以下主题： • 实例结构 • 进程结构 • 内存结构 6.1 实例结构 KingbaseES 数据库管理系统，由数据库文件和 KingbaseES 实例组成。 • 数据库文件 数据库文件为存储用户数据以及元数据的一组磁盘文件。元数据为描述数据库结构、配置和控制有关的信息。 • KingbaseES 实例 包含若干对存储的数据进行操作的数据库服务进程，还包括分配和管理内存，统计各种信息，以及实现各种协 调工作的后台进程。一台设备上，可以同时运行多个实例。实例注册成实例服务后，会有唯一的名字标志一个 实例。一个 KingbaseES 实例在操作系统上表现为一个 KingbaseES 进程，它可以由控制器启动，也可以单独用 命令行启动。一个 KingbaseES 实例管理多个逻辑上的数据库。启动一个 KingbaseES 实例后，使用客户端可以 访问到这个实例管理的任意一个数据库。 KingbaseES 实例的结构以及和数据库文件的关系可以表示为： 52 第6章 数据库实例体系结构 图 6.1.1: KingbaseES 实例和数据库文件 6.2 进程结构 数据库服务进程是多进程结构 KingbaseES 数据库服务进程，称该进程为一个“KingbaseES 数据库实例”。在一个数据目录只能同时启动一个 实例，不同的数据目录可以同时以不同的端口，手动启动为不同的实例。 KingbaseES 实例采用多进程架构，因此一个实例中会包含多个进程。这些进程按照功能的不同可以分为后台进 程和服务进程两类： 后台进程 • KingbaseES 主进程 主进程负责统一管理各服务进程和其他后台进程。该进程负责启动服务进程和其他后台进程，并且在子进程退 出的时候做清理工作。该进程负责分发来自操作系统的信号到各子进程。系统退出时，主进程负责发送信号通 知各子进程退出，然后再停止自己。 • 后台写进程 在这个进程中，共享缓冲池上的脏页会逐渐定期地写入持久存储 (例如 HDD、SSD)。 • 检查点进程 53 第6章 数据库实例体系结构 用来执行检查点过程。 • 自动 vacuum 进程 会定期地在服务器上执行清理和回收工作。 • WAL 日志写进程 这个进程周期性地将 WAL 缓冲区上的 WAL 数据写入和刷新到持久存储。 • 统计进程 在此进程中，会收集 sys_stat_activity 和 sys_stat_database 等统计信息。 • 归档进程 归档进程负责将日志文件归档到指定的位置。 • 日志收集进程 日志收集进程负责将数据库运行中的输出信息写入日志文件。 除以上后台进程外，在特定时机或使用一些特性时会有额外的后台进程，例如： • 恢复进程 实例处于从恢复状态或开启热备份时恢复进程负责重做 WAL 日志。 • WAL 日志发送、接收进程 使用 KingbaseES RWC 集群时，主节点通过 WAL 日志发送进程发送 WAL 给备节点上的 WAL 日志接收进程 实现数据复制。 • kwr、ksh 进程 开启 kwr 和 ksh 功能时，对应的 kwr、ksh 后台进程负责收集和维护负载、会话历史数据。 • 自动作业进程 开启自动作业功能时，自动作业后台进程实现作业的自动调度。 服务进程 KingbaseES 使用客户端/服务器的模型。对于每个客户端的连接，KingbaseES 主进程接收到客户端连接后，会 为其创建一个新的服务进程。KingbaseES 用服务进程来处理连接到数据库服务的客户端请求。该进程负责实际处理 客户端的数据库请求，连接断开时退出。 6.3 内存结构 KingbaseES 统一管理实例所用的内存资源。配置参数 shared_buffers 决定了数据库实例使用多少内存。当系统 启动时，数据库实例向操作系统申请一块大内存（大小由 shared_buffers 决定）作为共享内存。在这之后各个进程对 内存资源的使用都在这块内存里操作。 KingbaseES 对于共享内存的使用主要可以分为以下几部分： 54 第6章 数据库实例体系结构 • 数据页面缓存 在内存里缓存数据页面，shared_buffers 越大，在内存里保存的数据页面就越多。相同条件下操作数据时进行 的 IO 操作更少。 • 日志页面缓存 日 志 缓 冲 区， 操 作 数 据 时 产 生 的 日 志 都 放 在 这 个 缓 冲 区 上， 由 写 日 志 线 程 和 服 务 线 程 刷 到 磁 盘。 参 数 wal_buffers 设置日志页面缓存大小。 • 排序和连接运算使用的缓存 服务器对元组进行排序或者连接运算时，需要用到内存缓存数据。如果所需的运算还需更大的空间，KingbaseES 会借助于临时文件完成。参数 work_mem 设置每个服务进程排序和连接运算使用的缓存大小。 • 锁缓存 多线程并发操作会用到锁，KingbaseES 从共享内存开辟独立的内存空间用于存放锁信息。锁缓存的大小由总的 共享内存大小决定。 • 临时分配的内存 服务器在处理数据流程中，用于临时存放数据所使用的内存，也从共享内存里分配。KingbaseES 对于共享内存 的使用通常是可配置的，详细信息请参考 内存。 55 第 7 章 数据库对象管理 7 第 章 数据库对象管理 简要介绍数据库对象管理 本部分包含以下主题： • 表 • 索引 • 分区、视图和其他模式对象 • 数据完整性约束 • 数据字典和动态性能视图 7.1 表 本章介绍了模式对象，并讨论了表，这些表对象是最常见的模式对象类型。 本章包括以下几节： • 模式对象简介 • 表概述 • 临时表概述 • 外部表概述 7.1.1 模式对象简介 数据库模式是数据库所有对象的集合，称为模式对象。您可以使用 SQL 创建和操作模式对象。 本节包含以下主题： • 模式对象类型 • 模式对象存储 56 第 7 章 数据库对象管理 • 模式对象依赖关系 • 模式和管理员 7.1.1.1 模式对象类型 KingbaseES SQL 提供许多类型的模式对象。模式对象的主要类型如下表所示： 表 7.1.1: 模式对象类型 对象 描述 表 表按行存储数据。表是关系数据库中最重要的模式对象。 索引 索引提供指向存储在表的指定列中数据值的指针，实现对行进行直接、快速访问。 KingbaseES 数据库支持几种类型的索引。 分区 分区是大表和索引的一部分。每个分区都有自己的名称，并且可以选择拥有自己的存 储特性。 视图 视图是对一个或多个表或其他视图中数据查询。可以将它们视为存储查询。视图实际 上不包含数据。 序列 序列自动生成有序的整数值，可以由多个用户共享使用。通常，使用序列来生成主键 值 同义词 同义词是另一个模式对象的别名对象。因为同义词只是一个别名，所以它不需要存 储。除了在数据字典中的定义外，它不需要存储。 PL/SQL 程序和包 PL/SQL 是 SQL 的过程扩展。PL/SQL 子程序是一个命名 PL/SQL 的块，可以用一 组参数调用。PL/SQL 包对逻辑相关的 PL/SQL 类型、变量和子程序进行分组。 7.1.1.2 模式对象存储 模式对象将数据存储在文件中。其他模式对象，如视图和序列，仅包含元数据。 KingbaseES 数据库在表空间中逻辑地存储模式对象。模式和表空间之间没有对应关系，表空间可以包含来自不 同模式的对象，而一个模式的对象可以存储在不同的表空间中。KingbaseES 中表或索引等对象都被记录在表空间 中，并被拆分成若干个段存储。一个段对应了一个物理文件，并只存储一个关系的部分数据。段内部会被划分为若干 个数据块进行数据的管理和组织。 7.1.1.3 模式对象依赖关系 一些模式对象引用其他对象，从而产生模式对象依赖关系。例如，视图包含引用表或视图的视图，而 PL/SQL 子程序调用其他子程序。如果对象 A 的定义引用对象 B，那么 A 是 B 依赖对象，B 是 A 的被引用对象。 57 第 7 章 数据库对象管理 KingbaseES 数据库提供了一种自动机制来确保依赖对象对其引用对象始终是最新的。创建依赖对象时，数据库 会跟踪依赖对象以及它的引用对象之间的依赖关系。当被引用对象以可能影响依赖对象的方式更改时，数据库会标记 依赖对象为无效。例如，如果用户删除了一个表，则基于已删除表的视图都不可用。在依赖对象可用之前，必须根据 引用对象的新定义重新编译无效的依赖对象。当引用无效的依赖对象时，会自动进行重新编译。 7.1.1.4 模式和管理员 当创建数据库时，会创建一个管理员账号（如 SYSTEM)。 管理帐户有很高的特权，仅用于授权的 DBA 执行如启动和停止数据库、管理内存和存储、创建和管理数据库用 户等。SYS 系统模式在创建数据库时自动创建，系统模式中的表仅由数据库自行维护，绝不能被任何用户修改。系统 模式存储显示管理信息的其他表和视图，以及各种 KingbaseES 数据库选项和工具使用的内部表和视图。不要使用系 统模式来存储非管理性用户的表。 7.1.2 表概述 表是 KingbaseES 数据库中最基本的操作对象。一个表描述了一个实体对象，它记录了实体对象的重要信息。 KingbaseES 数据库表对象类型是关系表，关系表由简单的列组成，是最常见的表类型。关系表有以下特征： • 堆组织表不以任何特定顺序存储行。CREATE TABLE 语句默认创建的是堆组织表。 • 外部表是只读表，其元数据存储在数据库中，但它的数据存储在数据库外部。 表分为普通表和临时表。普通表定义和数据跨会话持续存在。临时表定义与普通表定义以相同的方式持续存在， 但数据仅在事务或会话期间存在。临时表对于一些必须临时保存结果集的应用程序很有用，可能是因为临时结果集是 通过运行多个操作产生的。 本节包含以下主题： • 列 • 行 • 示例：CREATE TABLE 和 ALTER TABLE 语句 • 数据类型 • 完整性约束 • 表储存 • 表压缩 7.1.2.1 列 表定义包括表名和列。列描述的表对应的实体属性。例如，products 表中 productname 列是指产品实体的产品 名称属性。通常，在创建表时，给每个列定义一个列名、一个数据库类型和精度。例如，productname 的数据类型为 VARCHAR（40），表示此列最多只能存储 40 个字符的字符串数据。 58 第 7 章 数据库对象管理 7.1.2.1.1 虚拟列 表可以包含一个虚拟列，与非虚拟列不同，它不会占用磁盘空间。数据库通过计算一组用户指定的表达式或函 数，根据需求导出虚拟列中的值。例如，订单明细表中的金额可以是单价列和折扣列的函数值。 7.1.2.1.2 隐藏列 隐藏列是用户指定的列，其值只有在通过显式指定列名时才可见。您可以在不影响现有应用程序的情况下向表中 添加一个隐藏列，并在必要时使列可见。例如，下面的示例创建具有隐藏列的 orders 表，然后使隐藏列可见： CREATE TABLE orders (orderid INT,price NUMERIC(10,2) INVISIBLE); ALTER TABLE orders MODIFY (price VISIBLE); 7.1.2.2 行 行是表中记录对应列信息的集合。例如，订单表中的一行描述了订单实体对象的属性：订单编号、客户编号、订 购日期等。创建表后，就可以使用 SQL 插入、查询、删除和更新行。 7.1.2.3 示例：CREATE TABLE 和 ALTER TABLE 语句 示例 7-1-1 创建表 使用 CREATE TABLE 语句创建订单表和客户表，并且为每个列指定列名和数据类型。 CREATE TABLE orders ( orderid INT4 NOT NULL, customerid CHARACTER(5) NOT NULL, employeeid INT4 NOT NULL, orderdate DATE , requireddate DATE, shippeddate DATE, shipvia INT4, freight NUMERIC(10,2) DEFAULT 0, shipname VARCHAR(40), shipaddress VARCHAR(60) , shipcity VARCHAR(15), shipregion VARCHAR(15), shippostalcode VARCHAR(10), shipcountry VARCHAR(15) ); CREATE TABLE customers ( 59 第 7 章 数据库对象管理 customerid VARCHAR(10) CONSTRAINT pk_customers PRIMARY KEY, companyname VARCHAR(30) NOT NULL, city VARCHAR(20) ); 示例 7-1-2 修改表 使用 ALTER TABLE 语句，将完整性约束添加到订单表中。完整性约束强制执行约束规则，防止无效数据进入 表中。 ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY(orderid); ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY(customerid) REFERENCES customers(customerid); 示例 7-1-3 查询表 查询了订单表的部分数据，列举了表部分列的 10 行记录。 orderid |customerid |employeeid |orderdate |requireddate |shippeddate |shipregion | --------+-----------+-----------+-----------+-------------+------------+--------------+ 10248 |VINET |5 |1996-07-04 |1996-08-01 |1996-07-16 | | 10249 |TOMSP |6 |1996-07-05 |1996-08-16 |1996-07-10 | | 10250 |HANAR |4 |1996-07-08 |1996-08-05 |1996-07-12 |RJ | 10251 |VICTE |3 |1996-07-08 |1996-08-05 |1996-07-15 | | 10252 |SUPRD |4 |1996-07-09 |1996-08-06 |1996-07-11 | | 10253 |HANAR |3 |1996-07-10 |1996-07-24 |1996-07-16 |RJ | 10254 |CHOPS |5 |1996-07-11 |1996-08-08 |1996-07-23 | | 10255 |RICSU |9 |1996-07-12 |1996-08-09 |1996-07-15 | | 10256 |WELLI |3 |1996-07-15 |1996-08-12 |1996-07-17 |SP | 10257 |HILAA |4 |1996-07-16 |1996-08-13 |1996-07-22 |Táchira | 前面的示例输出说明了表、列和行的以下一些重要特性： • 表中一行描述了一个员工的属性：订单编号、客户编号、订购日期等。例如，输出中的第一行显示了编号为 10248 的订单记录。 • 列描述订单的属性。示例中，orderid 列是主键，每个订单都由订单 ID 唯一标识。不同订单都一定是不同的订 单编号。 • 非主键列可以有相同值。例中，订单 10250 和 10252 的员工号相同，都为 4。 • 外键列是指引用同一表或不同表中的主键或唯一键。在本例中，customerid 中的值对应于客户表的 customerid 列。 • 字段是行与列的交集。它只能有一个值。例如，订单 10249 的订购日期为 1996-07-05。 • 字段可以没有值。在这种情况下，字段为空值。例如，订单号 10248 记录的 shipregion 列的值为 null。除非在 此列上定义了 not null 或主键完整性约束，否则允许列空值，如果定义了该约束，就不允许空值插入任何行。 60 第 7 章 数据库对象管理 7.1.2.4 数据类型 每个列都有一个数据类型，它有特定的存储格式、约束和有效的值范围。数据类型将固定的属性与值关联在一 起。这些属性导致 KingbaseES 数据库将以不同的方式处理不同数据类型的值。例如，您可以将 NUMBER 数据类 型的值相乘，但不能将 RAW 数据类型的值相乘。 创建表时，必须为其每个列指定数据类型。随后插入列中的每个值都采用已定义的列数据类型。KingbaseES 数 据库提供了多种内置数据类型。最常用的数据类型分为以下几类：字符数据类型、数值数据类型和日期时间数据类 型。其他重要内置数据类别包括 RAW、大对象类型和集合。PL/SQL 有常量和变量的数据类型，包括 BOOLEAN、 引用类型、复合类型（RECORDS）和用户自定义的类型。部分数据类型支持按指定模板进行格式化。 7.1.2.4.1 字符数据类型 字符数据类型将数字、字母或文字数据存储在字符串中。最常见的字符数据类型是 VARCHAR2，这是存储字 符数据最有效的类型。字符值对应的字符编码，通常称为字符集。数据库字符集是在数据库创建时建立的。KingbaseES 字符集有 GBK、UTF-8、EUC_CN 等。 字符数据类型的长度定义以字符数或字节数来限制大小。将字符串按字节来处理称为字节语义，将字符串按字符 来处理称为字符语义。KingbaseES 数据库中可以通过参数 CHAR_DEFAULT_TYPE 设置字符串类型的长度单位为 CHAR 或 BYTE, 默认为字符。 VARCHAR2 是可变长度的字符数据类型。字符串是一个固定的数据值，例如，‘人大金仓‘，‘KingbaseES ‘，和‘123’都是字符串，11 是数字字符串。字符串被封装在单个引号中，以便数据库将它们的模式对象名称区分 开来。当您使用 VARCHAR2 列创建表时，您可以指定字符串最大长度。KingbaseES 数据库将列中的每个值存储为 可变长度字段，除非它的值超过最大长度限制. 例如 VARCHAR2（25 char）的数据类型，这意味着存储在列中的值 最多有 25 个字符。如果在 VARCHAR2（25 char）列值输入 10 个字符，则实际列只存储 10 个字符，而不是 25 个。 使用 VARCHAR2 可以减少空间消耗。 与 VARCHAR2 相反，CHAR 是存储固定长度的字符串。当您创建带有 CHAR 字符类型列的表时，需要固定字 符串长度，CHAR 字符串长度默认为 1 个字符。数据库使用空格将值填充到指定的长度。 KingbaseES 数据库中，NCHAR、NVARCHAR2 与 CHAR、VARCHAR2 的含义相同。 7.1.2.4.2 数值数据类型 KingbaseES 数据库数字类型存储定点数和浮点数、零和无穷大。一些数字类型还存储作为操作未定义结果的 值，称为“非数字”或 NaN。 KingbaseES 数据库以可变长度格式存储数字数据。每个值都以科学计数法存储，其中一个字节用于存储指数。 数据库最多使用 20 个字节来存储尾数，尾数是浮点数的一部分，包含其有效数字。 7.1.2.4.2.1 数字类型 NUMBER 数据类型存储定点数和浮点数。在 KingbaseES 数据库可以存储几乎任意数量级的数字。这些数据能 在不同操作系统中运行的 KingbaseES 数据库之间进行移植。所以对于必须存储数字数据的大多数情况，都建议使用 NUMBER 数据类型。 61 第 7 章 数据库对象管理 您可以使用 NUMBER(p，s) 指定一个定点数，其中 p 和 s 指的是以下含义： • 精度 精度指定总位数。如果没有指定精度，则列存储的值与应用程序提供的值完全一致，而不进行任何舍入。 • 标度 标度指定从小数点到最低有效数字的位数。正数精度将小数点右边的数字计数到最低有效位（包括最低有效 位）。负数精度将小数点左边的数字计数到最低有效位（不包括最低有效位）。如果指定一个没有指定标度，如 NUMBER（6）中所示，则标度为 0。 7.1.2.4.2.2 浮点数类型 KingbaseES 数据库专门为浮点数提供数字数据类型有：FLOAT 和 DOUBLE。这些类型支持 NUMBER 数据类 型提供的所有基本功能。但是 NUMBER 类型使用十进制精度计算，BFLOAT 和 DOUBLE 使用二进制精度计算， 这使得算术计算更快并且降低了存储需求。 FLOAT 和 DOUBLE 是近似数值数据类型。它们存储小数值的近似数，而不是精确数。例如，值 0.1 不能用 FLOAT 和 DOUBLE 精确表示。它们经常用于科学计算。这两种类型的区别类似于 Java 和 XMLSchema 中的数据 类型 FLOAT 和 DOUBLE。 7.1.2.4.3 日期时间数据类型 日期时间数据类型为 DATE、TIMESTAMP 和 DATETIME。KingbaseES 数据库为时间戳提供全面的时区支 持。 7.1.2.4.3.1 DATE 数据类型 DATE 类型存储日期和时间。虽然日期时间可以用字符或数字数据类型表示，但 DATE 具有特殊的关联属性。 日期存储在固定长度字段中，对应于世纪、年、月、日、小时、分钟和秒。数据库根据指定的格式显示日期, 格式模 型是描述字符串中日期时间格式的字符文字。KingbaseES 数据库以 24 小时格式存储时间 (HH24:MI:SS), 如果没有输 入时间部分，那么默认情况下，DATE 字段中的时间为 00:00:00。 7.1.2.4.3.2 TIMESTAMP 数据类型 TIMESTAMP 和 DATATIME 数据类型是 DATE 数据类型的扩展。除了存储在 DATE 数据类型中的信息外， TIMESTAMP 还存储分秒。TIMESTAMP 数据类型用于存储精确的时间值。TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE 包含时区信息。当用户选择时区数据时，该值将被调整到用户会话指定 的时区。这种数据类型对于需要使用跨地理区域的日期信息的业务程序很有用。 7.1.2.4.4 格式化模型 格式化模型是描述存储在字符串中的日期时间或数字数据格式的字符文字。格式化模型不会改变数据库内部值的 含义。当您将字符串转换为日期或数字时，格式模型将确定数据库如何显示字符串。在 SQL 中，您可以使用格式化 模型作为 TO_CHAR 和 TO_DATE 函数的参数来格式化要从数据库返回的值或格式化要存储在数据库中的值。 62 第 7 章 数据库对象管理 下面的示例使用 TO_CHAR 函数查询，将 freight 列转换为数字格式模型“￥fm9990.90”： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, orderdate DATE, freight NUMBER(6,2) ); INSERT INTO orders VALUES(10248,'1996-06-05',32.38),(10249,'1996-07-15',11.61),(10250,'1996-07-24',65.83); SELECT orderid, TO_CHAR(freight, '￥fm9990.90') AS freight FROM orders; orderid |freight | --------|--------+ 10248 |￥32.38 | 10249 |￥11.61 | 10250 |￥65.83 | 下面的示例使用 TO_DATE 函数查询，将字符串类型格式化为“yyyy-mm-dd hh24:mi:ss”： SELECT TO_DATE('2000/01/02 10:08:18.822447', 'yyyy-mm-dd hh24:mi:ss'); to_date | ----------------------+ 2000-01-02 10:08:18 7.1.2.5 | 完整性约束 完整性约束是一个命名规则，用于限制表中一个或多个列的值。数据完整性规则防止无效的数据进入表中。此 外，当存在某些依赖项时，约束可以阻止删除表的操作。 如果启用了约束，则数据库会在输入或更新数据时对其进行数据检查。KingbaseES 数据库不允许输入不符合约 束的数据。如果禁用了约束，则 KingbaseES 数据库允许不符合约束的数据进入数据库。例如，在示例 7-1-1 CREATE TABLE 语句中，orderid 列指定为主键约束。约束子句标识约束的列和条件。主键约束确保指定的列不包含空 值且唯一。如果尝试插入没有 orderid 的订单记录就会报错。您可以在创建表时或创建表完成之后创建和修改约束。 如果需要，也可以暂时禁用约束。数据库中约束存储在数据字典中。 7.1.2.6 表储存 7.1.2.6.1 堆组织表 默认情况下，表为堆组织表，这意味着数据库将行存储在最合适的位置，而不是按照用户指定的顺序。因此，堆 组织表是行的无序集合。当用户添加行时，数据库将这些行储存在数据段中的第一个可用空间中。不保证按插入顺序 63 第 7 章 数据库对象管理 检索行。表中所有行的列顺序相同。数据库通常按照 CREATE TABLE 语句中列出的顺序存储列，但不能完全保证 按此顺序。如果将新列添加到表中，则新列将成为存储的最后一列。 一个表可以有一个虚拟列，与普通列不同，它不会占用磁盘空间。数据库可通过计算一组用户指定的表达式或函 数来根据需求导出虚拟列中的值。您可以给虚拟列建索引，收集它们的统计信息，并创建完整性约束。因此，虚拟列 很像非虚拟列。 7.1.2.6.2 行存储 KingbaseES 中管理数据的最小单元叫做数据块，或者称为页面。一个数据块是最小的 IO 单元。数据库中一个数 据块的大小通常是 8KB 数据库将数据按行存储在数据块中，单个数据行不允许跨数据块存储，对于长度大于数据块用于存储数据的空间 的 1/4 时会尝试压缩数据行中的数据或将数据行拆分成多个物理行存储在另外的表中。这种存储方式称为 TOAST， 存储这类数据的表称为 TOAST 表。 7.1.2.6.3 空值存储 空值 null 在列中没有值。null 表示缺少、未知或不适用的数据。在 KingbaseES 数据库中，元祖头上会标记一行 中是否有 null 值。如果一行中没有 null 值，将不占用任何物理存储；如果有，元祖头上将使用一个最小分配单位是 字节的位图来标识值为 null 的列，每个列占用一位。例如一行包含 10 列的数据，如果其中包含 null 值，存储上将额 外分配 2 个字节的位图来标记 null 值。 表压缩 7.1.2.7 数据库使用表压缩来减少表所需的存储量。KingbaseES 数据库超过四分之一页面的行，默认启动压缩。压缩可 以节省磁盘空间，减少数据库缓冲区缓存中的内存使用，在某些情况下加快查询执行速度。表压缩对应用程序是透明 的。 7.1.3 临时表概述 临时表用于存放只存在于事务或会话期间的数据。临时表中的数据对会话是私有的，每个会话只能看到和修改自 己的数据。 您可以创建全局（GLOBAL）临时表或本地 (LOCAL) 临时表。下表列举了它们之间的区别: 64 第 7 章 数据库对象管理 表 7.1.2: 全局（GLOBAL）临时表或本地 (LOCAL) 临时表 全局 本地 定义 CREATE GLOBAL TEMPORARY TABLE CREATE LOCAL TEMPORARY TABLE 存储 表定义是持久的，表数据是临时的 表定义和表数据都是临时的，会在会话退出 后被删除 所属模式 创建在用户指定模式下 在临时模式下，用户不可以指定 约束限制 不支持外键引约用束 支持外键引用约束 ON COMMIT 类型 事务级 (DELETE ROWS) 和会话级 (PRE- 事务级 SERVE ROWS) (PRESERVE (DELETE ROWS) ROWS) 会话级 和事务结束后删 除（DROP） 7.1.3.1 临时表的用途 临时表对于必须临时缓存结果集的应用程序很有用。例如，每个员工创建本地临时表，插入本季度的所有订单销 售记录，进行统计分析，计算分析后再数据填入普通表中。在会话操作期间，临时表的数据是私有的，各个会话之间 数据彼此不影响。表中的数据都可以在事务结束之后删除，或者连接退出之后删除。数据都存在在临时表空间中，临 时表都不记录日志。 7.1.3.2 临时表的创建 创建临时表使用 CREATE TEMPORARY/TEMP TABLE 语句。将 GLOBAL 或 LOCAL 写在 TEMPORARY 或 TEMP 的前面, 指定全局临时表或本地临时表。创建临时表时若不指定 GLOBAL 或 LOCAL，则默认值为 LOCAL。 临时表在一个事务块结束时的行为由 ON COMMIT 控制。三种选项是： • PRESERVE ROWS 在事务结束时不采取特殊的动作，是本地临时表采用的默认行为。 • DELETE ROWS 在每一个事务块结束时将删除临时表中的所有行。实质上，在每一次提交时会完成一次自动的。在分区表上使 用时，不会将其级联到其分区。这是全局临时表的默认行为。 • DROP 在当前事务块结束时将删除临时表。在分区表上使用时，此操作将删除其分区，在具有继承子级的表上使用 时，将删除依赖子级。仅本地临时表支持在当前事务块结束时将删除临时表。全局临时表不支持 ON COMMIT DROP 选项。 临时表可以创建索引，这些索引也是临时的。临时表上的索引使用 temp_tablespaces 参数指定位置。 65 第 7 章 数据库对象管理 7.1.4 外部表概述 外部表访问外部数据源中的数据，就好像这些数据在数据库中的表中一样。数据源可以是文件类型或者是其他数 据库。您可以使用 SQL 和 PL/SQL 程序查询外部表。 7.1.4.1 外部表的用途 外部表用于当应用程序必须访问数据库外部数据的业务环境。例如，在数据仓库的环境中，执行 ETL 任务时使 用外部表可以方便流程设计和运行。KingBaseES 支持文件外部表和数据库外部表。您可以创建一个文件外部表，将 文本文件复制到外部表定义中指定的位置，然后使用 SQL 查询文本文件中的数据。您也可以创建一个外部服务器， 数据库外部表通过外部服务器访问其他数据库的数据。 7.1.4.2 外部表的创建 创建外部表使用 CREATE FOREIGN TABLE 语句。外部表定义描述列类型，定义 SERVER 是文件或者外部数 据库服务器。使用文件外部表则需要在 SERVER 定义中说明文件的位置和格式等，外部数据库服务器则需要说明服 务器的连接信息。在数据库内部，创建外部表会在数据字典中创建元数据。与普通表不同，外部表不用管数据是如何 外部存储的，但外部表定义描述了外部表层如何向数据库提供数据。如果指定了 PARTITION OF 子句，则该表被创 建为具有指定边界的 parent_table 的分区。例如，下面的示例创建外部表 films，通过服务器 myserver 访问它： CREATE EXTENSION kingbase_fdw; CREATE SERVER myserver FOREIGN DATA WRAPPER kingbase_fdw OPTIONS (host 'foo', dbname 'foodb', port '54321'); CREATE FOREIGN TABLE films ( code char(5) NOT NULL, title varchar(40) NOT NULL, did integer NOT NULL, date_prod date, kind varchar(10), len interval hour to minute ) SERVER myserver; 7.2 索引 索引是模式对象，可以加速对数据行的访问。 本章包括以下几节： • 索引概述 • B 树索引概述 66 第 7 章 数据库对象管理 • 函数索引概述 7.2.1 索引概述 索引是一种与表相关联的数据结构，是为了提高数据检索的性能而建立的。KingbaseES 索引为表数据提供快 速存取路径。索引适用于一范围的行查询或指定行的查询。索引可建立在一个表的一列或多列上。一旦建立，将由 KingbaseES 数据库自动维护和使用，对用户是完全透明的。系统的优化器会根据统计信息确定是否使用索引来提高 系统性能。索引逻辑和物理上都独立于与其相关联的表数据。因此，您可以删除或创建索引，但不会对索引表产生影 响。 本节包含以下主题： • 索引的优缺点 • 键和列 • 组合索引 • 唯一索引和非唯一索引 • 索引类型 • 索引的维护 • 索引存储 7.2.1.1 索引的优缺点 使用索引不需要改变任何 SQL 语句的写法。索引是对单行数据的快速访问路径，它只影响执行的速度。给定一 个已索引的数据值，索引将直接指向该数据行的位置。当一个索引存在于表的一个或多个列上时，数据库在某些情况 下可以从表行中检索一组随机分布的行。索引是减少磁盘 I/O 的手段之一。如果一个表没有索引，数据库必须执行全 表扫描来查找值。例如，如果没有索引，查询 orders 表 orderid 为 10523 的位置，数据库需要搜索每个表块中的每一 行来找到该值。当数据量增加时，这种方式性能会很慢。 索引的缺点如下： • 手动创建索引通常需要对数据模型、应用程序和数据分布有深入的了解。 • 随着数据的变化，您必须重新考虑以前关于索引的决策。索引可能不再有用，或者可能需要新的索引。 • 索引占用磁盘空间。 • 当索引数据上发生 DML 时，数据库必须更新索引，这会造成性能开销。 通常，在下列情况下可以考虑在某列上创建索引： • 要索引的列经常被查询，并只返回表中的行的总数的一小部分。 • 在索引的列或列集上存在引用完整性约束。索引可以避免当你更新父表主键、合并父表、从父表删除行时可能 引起的全表锁定。 67 第 7 章 数据库对象管理 • 要在表上设置唯一键约束，并且您想手动指定索引和所有索引选项。 7.2.1.2 键和列 键是一个列集或表达式，您可以在它上面创建索引。虽然这两个术语通常互换使用，但索引和键还是不同的。索 引是存储在数据库中的一种结构，用户使用 SQL 语句来管理它们，而键严格来讲只是一个逻辑概念。例如，下面的 语句在 orders 表的 employeeid 列上创建索引，employeeid 列是索引键。索引被命名为 index_employeeid： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); CREATE INDEX index_employeeid ON orders(employeeid); 7.2.1.3 组合索引 组合索引，也称为连接索引，是在表中的多个列上的索引。组合索引中的列应该以在检索数据的查询中效率最高 的顺序出现，但在表定义中的顺序不需要与和组合索引列顺序一致。若 WHERE 子句引用了组合索引中的所有列或 前导列，组合索引可以加快 SELECT 语句的数据检索速度。所以，在组合索引定义中所使用的列顺序很重要。一般 最常被访问的列放在前面。 例如，假定应用程序经常查询 orders 表中的 orderid、customerid 和订购时间列的数据。相对于表的总行数来 说，customerid 不同值的基数很大。可以按以下的列顺序创建索引： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); CREATE INDEX index_orders ON orders (customerid,orderid,orderdate); 如果查询这个三列，或者包含 customerid 列都会使用此索引。不访问 customerid 列的查询，将不会使用索引。 当满足以下任一条件时，同一张表上可以存在多个列序相同的索引： • 索引的类型不同。 例如，您可以在相同的列上创建位图和 B 树索引。 • 索引使用不同的分区方案。 68 第 7 章 数据库对象管理 例如，您可以创建本地分区的索引和全局分区的索引。 • 索引具有不同的唯一性属性。 例如，您可以在同一列集上创建惟一索引和非惟一索引。 如果同一列集中的索引在类型或分区模式上没有差异，那么这些索引必须使用不同的列排列。例如，下面的 SQL 语句指定的排列都是有效的： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); CREATE INDEX index1_orders ON orders (customerid,orderdate); CREATE INDEX index2_orders ON orders (orderdate,customerid); 7.2.1.4 唯一索引和非唯一索引 索引分为唯一索引或者非唯一索引。唯一索引保证在表的键列或键列集上没有重复值的行。例如，orders 表中所 有行的 orderid 都不同。非唯一索引允许在索引的列或列集中有重复的值。例如，orders 表的 customerid 列中可能包 含多个相同值。 7.2.1.5 索引类型 KingbaseES 数据库提供了几种索引类型，来增强数据检索性能。B 树索引是标准索引类型，它适用于主键索引 和过滤性强的索引。在组合索引中使用时，B 树索引可以按多个索引列以排序方式检索数据。 B 树索引的子类型如下表所示： 表 7.2.1: B 树索引的子类型 B-Tree 索引子类 描述 降序索引 这种类型的索引将存储在一个特定的列或多列中的数据按降序排序。 组合索引 这种类型的索引将数据按降序存储在一个或多个特定的列上。 唯一索引 这种类型的索引来强制列值的唯一性，或者是多个列组合值的唯一性。 非 B 树索引的子类型如下表所示： 69 第 7 章 数据库对象管理 表 7.2.2: 非 B 树索引的子类型 类型 描述 HASH 索引 HASH 索引理想情况下仅需一次检索即可定位数据，但 Hash 引索只能处理简单等值比较。 GIN 索引 GIN 索引是倒排索引，它适合于包含多个组合值的查询，例如数组。 GINST 索引 GINST 索引适用于多维数据类型集和合数据类型，可以用来做位置搜索。 BRIN 索引 块范围索引，存储表的连续数据库区间以及对应的数据取值范围。 BITMAP 索引 位图索引，以位图形式存储对应的数据是否出现与出现的位置。 7.2.1.6 索引的维护 创建索引之后，数据库将自动维护和使用索引。 索引自动将数据变化更新到其底层表。包括添加、更新和删除行等操作，都不需要用户维护索引。即使插入行， 索引数据的检索性能也几乎保持不变。但是，表上的索引过多会降低 DML 性能，因为 DML 操作会导致数据库更新 索引。 7.2.1.7 索引存储 KingbaseES 数据库将索引数据存储在索引表空间的索引段中。索引的表空间，是所有者的默认表空间，或在 CREATE INDEX 语句中明确指定的表空间。为了便于管理，您可以在一个独立于表所在表空间的单独表空间中存 储索引。例如，您可以选择把包含索引的表空间放在低容量的高速存储上加速访问。 7.2.2 B 树索引概述 平衡树，简称 B 树，是最常见的数据库索引类型。一个 B 树索引是被划分为多个范围的已排序的值列表。通过 索引键与一行或行范围关联起来，B 树可以对多种类型的查询提供优秀的检索性能，包括精确匹配和范围搜索等。 7.2.2.1 B 树索引中不同类型的块 B-树索引中可能出现的几种不同类型的块： 1. meta : 索引中的第一个块，存储索引的结构版本、root 位置等元信息 2. root : 逻辑上没有父节点的节点，在多层索引中存储导航信息，在单层索引中存储实际索引值 3. 非 root 的中间块: 多层索引中存储导航信息的非 root 块 4. leaf : 存储实际索引值的块 70 第 7 章 数据库对象管理 索引的树形结构由 2、3、4 类型的块组成，每种块中也存储和兄弟节点间的导航信息。 7.2.2.2 索引扫描 在索引扫描中，数据库使用语句中涉及的有索引的列，通过遍历其索引来检索行。数据库扫描索引，将使用 n 个 I/O 就能找到其要查找的值，其中 n 即是 B-树索引的高度。这是数据库索引背后的基本原理。如果 SQL 语句仅需要 被索引的列值，那么数据库只需直接从索引中读取值，而不用读取表数据。如果该语句同时还需要访问除索引列之外 的列，那么数据库会使用索引来查找表中的行数据。通常，为检索表数据，数据库以交替方式先读取索引块，然后读 取相应的表块。 7.2.2.2.1 全索引扫描 在全索引扫描中，数据库顺序读取整个索引。如果在 SQL 语句中的谓词（WHERE 子句）引用了一个索引列， 或者在某些情况下未指定任何谓词，此时可能使用全索引扫描。全扫描可以消除排序，因为数据本身就是基于索引键 排过序的。 例如，假设应用程序运行以下查询： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, freight NUMBER(6,2) ); CREATE INDEX index_orders ON orders (orderid,customerid,freight); INSERT INTO orders VALUES(10248,'VINET',102.01),(10249,'TOMSP',90),(10250,'HANAR',209.41),(10251,'VICTE', 190.99); SELECT orderid, customerid, freight FROM orders WHERE freight > 100 ORDER BY orderid,customerid; 其中，customerid，customername 和 freight 是一个组合索引。KingbaseES 数据库会执行全索引扫描，按 customerid 和 customername 排序的顺序读取索引数据，并基于 freight > 100 的条件进行筛选。通过这种方式，数据库 只需扫描满足 freight > 100 的条件的数据所属的表数据页，而不用扫描那些不满足条件的表数据页，并避免了对该 数据进行排序。 全索引扫描可能这样读取索引条目，如下所示： 10248,VINET,102.01,tid 10250,HANAR,209.41,tid 10251,VICTE,190.99,tid . 71 第 7 章 数据库对象管理 . . 7.2.2.2.2 仅索引扫描 仅索引扫描也是一种全索引扫描，区别在于数据库仅访问索引本身的数据，而无需再访问表数据。 当满足以下两个条件时，仅索引扫描将会被启用： • 索引必须包含查询所需的所有列。 • 对于表做过 ANALYZE 操作。 例如，应用程序发出以下查询，仅索引扫描将会被启用： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, freight NUMBER(6,2) ); CREATE INDEX index_orders ON orders (orderid,customerid); INSERT INTO orders VALUES(10248,'VINET',102.01),(10249,'TOMSP',90),(10250,'HANAR',209.41),(10251,'VICTE', 190.99); SELECT orderid,customerid FROM orders WHERE orderid < 10250; 其中，customerid 和 customername 是一个组合索引，目标列中只有索引相关列，仅索引扫描只需读取索引条 目，就可以获取所需的信息。 仅索引扫描可能这样读取索引条目，如下所示： 10248,VINET,tid 10250,HANAR,tid 10251,VICTE,tid . . . 7.2.2.2.3 范围索引扫描 范围索引扫描也是全索引扫描的一种，区别在于仅针对指定范围建立索引。 通常，数据库使用范围索引扫描来访问带有范围选择谓词的数据。 例如，针对 orders 表，建立一个 customerid 在 [100,400] 这个范围的范围索引： 72 第 7 章 数据库对象管理 CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, freight NUMBER(6,2) ); CREATE INDEX range_customer_id_idx ON orders(customerid) WHERE customerid >= 100 AND customerid <= 400; 当用户查询 customerid 在这个范围中的数据时，范围索引扫描会被使用。 explain SELECT customerid, customerid, freight FROM orders WHERE customerid >= 200 AND customerid <= 300; QUERY PLAN | ---------------------------------------------------------------------------------------+ Index Scan using range_customer_id_idx on orders (cost=0.15..10.17 rows=101 width=15) | Index Cond: ((customerid >= 200) AND (customerid <= 300)) | 7.2.2.2.4 表达式索引扫描 表达式索引扫描也是全索引扫描的一种，区别在于仅针对表达式建立索引。 例如，针对 orders 表，建立一个表达式索引： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, freight NUMBER(6,2) ); INSERT INTO orders VALUES(10248,'VINET',102.01),(10249,'TOMSP',90),(10250,'HANAR',209.41),(10251,'VICTE', 190.99); CREATE INDEX exp_idx ON orders((MOD(freight,3) = 0)); 当用户查询含有这个表达式谓词时，表达式/函数索引扫描可能会被使用。 set enable_seqscan = off; EXPLAIN SELECT * FROM orders WHERE MOD(freight,3) = 0; QUERY PLAN | -------------------------------------------------------------------------+ Index Scan using exp_idx on orders (cost=0.29..514.78 rows=70 width=15) | Index Cond: ((mod(freight, 3) = 0) = true) | 73 第 7 章 数据库对象管理 7.2.2.3 升序和降序索引 对于升序索引，数据库按升序排列的顺序存储数据。默认情况下，字符数据按每个字符中包含的二进制值排序， 数值数据按从小到大排序，日期数据从早到晚排序。 例如，下面是一个升序索引的例子： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, orderdate DATE ); CREATE INDEX index_orderdate ON orders(orderdate); 数据库对 orders 表按 orderdate 列进行升序排序。按 orderdate 列升序顺序加载索引。使用此索引，数据库检索 已排序的 orderdate 值。 通过在 CREATE INDEX 语句中指定 DESC 关键字，可以创建一个降序索引。在这种情况下，索引在指定的一 列或多列上按降序顺序存储数据。降序索引的默认搜索顺序是从最高值到最低值。当查询需要按一些列升序而另一些 列降序排序时，可以使用降序索引。例如，在 customerid 列和 orderdate 列上创建一个复合索引，如下所示： CREATE INDEX index_orders ON orders(customerid ASC, orderdate DESC); 对 orders 表查询，要求按 customerid 按字母升序排序序而 orderdate 以日期类型降序排序，则数据库可以使用 此索引检索数据并避免额外的排序步骤，如下所示： SELECT customerid, orderdate FROM orders ORDER BY customerid ASC,orderdate DESC; 7.2.2.4 索引压缩 当索引长度超过页面可存储数据空间的 1/16 时，数据库会透明的尝试对索引中包含的存储模式为 EXTENDED 和 MAIN 的列做压缩。 7.2.3 函数索引概述 函数索引是基于函数的索引计算函数或涉及一个或多个列的表达式的值。用于生成索引的函数可以是算术表达 式，或一个包含 SQL 函数、用户定义 PL/SQL 函数、包函数，或 C 调用的表达式。例如，函数可以将列值全部 小写。 74 第 7 章 数据库对象管理 函数索引的使用 7.2.3.1 函数索引对于在 WHERE 子句中包含函数计算的语句是有效的。仅当在查询中包含该函数时，数据库才使用基 于函数的索引。当数据库处理 INSERT 和 UPDATE 语句时，它仍然必须计算函数才能完成对语句的处理。 示例 7-2-1 创建函数索引 CREATE TABLE order_details ( orderid INT4 CONSTRAINT pk_order_details PRIMARY KEY, unitprice NUMBER(6,2), quantity INT4, discount NUMBER(6,2) ); INSERT INTO order_details VALUES(10462,4.80,1,0.0),(10281,7.30,1,0.0),(10850,2.50,4,0.15),(10420,4.80,2,0.1); CREATE INDEX index_price ON order_details ((unitprice*quantity*(1-discount)), unitprice, quantity, discount); 数据库在处理以下查询时可以使用此函数索引： SELECT orderid, unitprice, quantity, discount,unitprice*quantity*(1-discount) AS price FROM order_details WHERE price <100 ORDER BY price ; orderid|unitprice|quantity|discount| price| -------+---------+--------+--------+------+ 10462| 4.80| 1| 0.00|4.8000| 10281| 7.30| 1| 0.00|7.3000| 10850| 2.50| 4| 0.15|8.5000| 10420| 4.80| 2| 0.10|8.6400| 示例 7-2-2 基于 SQL 函数 UPPER 的索引 基于 SQL 函数 UPPER(column_name) 或 LOWER(column_name) 定义的索引，经常用于大小写无关的数据检 索。例如，customers 表的 city 列包含混合大小写字符。可以在 customers 表上创建了如下索引： CREATE TABLE customers ( customerid VARCHAR(10) CONSTRAINT pk_customers PRIMARY KEY, companyname VARCHAR(30) NOT NULL, city VARCHAR(20) ); INSERT INTO customers VALUES('RDJC','Bejing Renda Jingcang','Beijing'); INSERT INTO customers VALUES('BJRDJC','Bejing Renda Jingcang','BeiJing'); 75 第 7 章 数据库对象管理 查询 city 列时，可以使用此索引： SELECT customerid,UPPER(city) AS city FROM customers WHERE UPPER(city) = 'BEIJING'; customerid |city | ------------|------------+ RDJC |BEIJING | BJRDJC |BEIJING | 示例 7-2-3 为表中的特定行建立索引 函数索引对于只在一个表中的特定行上建立索引也是有用的。例如，territory 表中的 regionid 列有 4 个值。可以 编写一个函数，让 regionid 不为 1 的其它任何行都返回一个空值。创建索引如下所示： CREATE TABLE territory ( territoryid VARCHAR(10)CONSTRAINT pk_territory PRIMARY KEY, territoryname VARCHAR(30), regionid INT4 NOT NULL ); CREATE INDEX index_regionid ON territory ( CASE regionid WHEN '1' THEN '1' END ); 7.2.3.2 函数索引的优化 对于在 WHERE 子句中包含表达式的查询，优化器可以在一个基于函数的索引上使用索引范围扫描。当谓词 具有高度选择性，即选择相对较少的行时，范围扫描访问路径尤其有用。示例 7-2-1 中，如果索引是基于表达式 unitprice*quantity*(1-discount) 来创建的，优化器可以使用索引范围扫描。虚拟列可用于快速访问由表达式生 成的数据。例如，您可以为表达式 unitprice*quantity*(1-discount) 定义虚拟列 price，并在该 price 列上创建索 引。优化器通过分析在 SQL 语句中的表达式来执行表达式匹配，然后比较的语句表达式目录树和函数索引。 7.3 分区、视图和其他模式对象 虽然表和索引是最重要和最常用的模式对象，但数据库支持许多其他类型的模式对象，其中最常见的模式对象将 在本章中讨论。 本章包括以下几节： • 分区概述 • 视图概述 • 物化视图概述 76 第 7 章 数据库对象管理 • 序列概述 • 同义词概述 7.3.1 分区概述 在 KingbaseES 数据库中，分区能够将大表和索引分解成更小、更易于管理子分区。每个分区是一个独立的对 象，具有其自己的名称和存储特征。 下面用一个例子来解释分区的概念，假定我们正在为一个大型的冰激凌公司构建数据库。该公司每天测量最高温 度以及每个区域的冰激凌销售情况。销售表结构如下： CREATE TABLE measurement ( city_id INT NOT NULL, logdate DATE NOT NULL, peaktemp INT, unitsales INT ); 大部分查询只会访问上周的、上月的或者上季度的数据，因为这个表的主要用途是为管理层准备在线报告。为了 减少需要被存放的旧数据量，我们决定只保留最近 3 年的数据。在每个月的开始我们将去除掉最早的那个月的数据。 在这种情况下我们可以使用分区技术来帮助我们满足对 measurement 表的所有不同需求。 从应用程序的角度来看，分区表是一个表对象，不需要修改 SQL 语句就可以访问该分区表。分区对许多类型的 应用程序都很有用，特别是那些管理大量数据的应用程序。其优点如下： • 可用性增强 其中某个分区不可用并不意味着整个表不可用。部分分区未被引用时，优化器自动从执行计划中删除未引用的 分区，而查询不会受影响。 • 更轻松的管理模式对象 分区表具有多个分片，可以将其作为一个整体来管理，也可以单独管理各个分片。DDL 语句可以单独处理分 区，而不是整个表或索引。因此，您可以分解大量占用资源的任务，如重建索引或表。例如，您可以一次只修 改一个表分区。如果发生了问题，则只需重新修改该分区，而不是整个表。而且，删除一个分区可以避免执行 许多 DELETE 语句。 • 在 OLTP 系统中减少对共享资源的争用 在一些 OLTP 系统中，分区可以减少对共享资源的争用。例如，DML 被分散到很多文件中，而不只是一个文 件。 • 在数据仓库中增强的查询性能 在数据仓库中，分区可以加快带条件的范围查询的速度，优化灵活选择条件的查询。 77 第 7 章 数据库对象管理 7.3.1.1 分区特征 每个表或索引的分区必须具有相同的逻辑属性，如列名称、数据类型和约束。例如，在一个表中的所有分区都共 享相同的列和约束定义，并在索引中的所有分区都共享相同的索引列。但是，每个分区可以有单独的物理属性，即所 属的表空间可以不同。 7.3.1.1.1 分区键 分区键是一个列或列集，来确定分区表中的每一行应在的分区。每个行会被准确地分配到某个分区。例如，在 measurement 表中，您可以指定 city_id 列作为列表分区的键。基于此列来判断是否属于某一列表，数据库将行分 配到所属分区。通过使用分区键，KingbaseES 数据库将插入、更新、和删除操作自动地分配到对应的分区。 7.3.1.1.2 分区类型 KingbaseES 分区提供了几个分区策略，来控制数据库如何将数据放置到分区。基本类型有范围分区、列表分区 和哈希分区等。单一分区只使用一种数据分区方法，例如，仅使用列表分区，或仅使用范围分区。在组合分区中，表 先按一种数据分区方法分区，然后每个分区使用第二种数据分区进一步分成子分区。 7.3.1.1.2.1 范围分区 在范围分区中，数据库基于分区键的值范围将行映射到各个分区。范围分区是最常见的分区类型，通常与日期一 起使用。 示例 7-3-1 创建范围分区表 分区表 measurement_range，logdate 列是分区键： CREATE TABLE measurement_range ( city_id INT NOT NULL, logdate DATE NOT NULL, peaktemp INT, unitsales INT ) PARTITION BY RANGE (logdate) ( PARTITION q1 VALUES LESS THAN ('2006-04-01'), PARTITION q2 VALUES LESS THAN ('2006-07-01'), PARTITION q3 VALUES LESS THAN ('2006-10-01'), PARTITION q4 VALUES LESS THAN ('2007-01-01') ); 数据库根据 PARTITION BY RANGE 子句中指定的范围分区列，基于 logdate 值为每个行选择适当的分区。范 围分区键值指定分区的上限，示例中使用 logdate 列将分区按日期范围划分为四个季度。 78 第 7 章 数据库对象管理 7.3.1.1.2.2 间隔分区 间隔分区是范围分区的扩展。 如果插入的数据超过现有的范围分区，那么 KingbaseES 数据库将自动创建指定间隔的分区。例如，measurement 表可以将每个月的数据存储在一个单独的分区中。 间隔分区使您能够避免显式地创建范围分区。您可以对每个范围分区的表使用区间分区，并对新分区使用固定的 区间。在按区间进行分区时，必须指定至少一个范围分区。范围分区键值确定各个范围分区的高值。数据库会自动为 超过范围的数据分区。每个区间划分的下边界是上一个区间划分或区间划分的包容上边界。 示例 7-3-2 创建间隔分区表 间隔分区表 measurement_interval，按照 logdate 日期列，每三个月为一个间隔自动创建分区。 CREATE TABLE measurement_interval ( city_id INT NOT NULL, logdate DATE NOT NULL, peaktemp INT, unitsales INT ) PARTITION BY RANGE (logdate) INTERVAL ('3 MONTH'::INTERVAL) ( PARTITION p1 VALUES LESS THAN ('2006-04-01') ); 数据库根据 INTERVAL 子句中指定的间隔规则自动分区。超过日期’2006-04-01’ 的数据，数据库自动为其创建新 的分区。 7.3.1.1.2.3 列表分区 在列表分区中，数据库使用一些具体值的列表作为每个分区的分区键。分区键由一个或多个列组成。你可以使用 列表分区来控制单个行如何映射到特定的分区。当用来区分数据集的键不方便排序时，可以通过使用列表来分组和组 织相关的数据集。 示例 7-3-3 创建列表分区表 列表分区表 measurement_list, 以 city_id 列为分区键，按照城市编号分为四个区： CREATE TABLE measurement_list ( city_id INT NOT NULL, logdate DATE NOT NULL, peaktemp INT, unitsales INT ) PARTITION BY LIST (city_id) 79 第 7 章 数据库对象管理 ( PARTITION city1 VALUES ('1'), PARTITION city2 VALUES ('2'), PARTITION city3 VALUES ('3'), PARTITION city4 VALUES ('4') ); 数据库根据 PARTITION BY LIST 子句中指定的规则，基于 city_id 为每个行选择适当的分区。按照城市编号 分为四个区，例如，city_id 值 2 的行将存储在 city2 分区中。 7.3.1.1.2.4 哈希分区 在哈希分区中，基于用户所指定的将在分区键上应用的哈希算法，数据库将行映射到各个分区。通过为每个分区 指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余 数。哈希分区可用于划分大表，以提高可管理性。例如，哈希分区适合用于在更新频繁的 OLTP 系统中, 一个表被分 为几个部分，每个分区都可以被更新，而不像单表更新容易产生锁冲突。 示例 7-3-4 创建哈希分区表 哈希分区表 measurement_hash, 以 city_id 列为 HASH 分区键： CREATE TABLE measurement_hash ( city_id INT NOT NULL, logdate DATE NOT NULL, peaktemp INT, unitsales INT ) PARTITION BY HASH(city_id); 数据库根据 PARTITION BY HASH 子句中指定 HASH 键分布数据。示例中，这些分区的名称将由系统自动生 成的。不能指定某数据行被放置在哪一个分区，数据库应用哈希算法，确定数据的分区分配。 7.3.1.1.2.5 组合分区 在组合分区中，用一种数据分区方法对表进行分区，然后使用另一种数据分区方法将每个分区进一步细分为子分 区。因此，组合分区结合了基本的数据分区类型。 组合分区提供了以下几个优点： • 根据 SQL 语句，对一个或两个维度进行分区修改可提高性能。 • 查询可以在任意维度上使用全连接或部分分区连接。 • 可以对单个表执行并行备份和恢复。 • 分区的数量大于单级分区，有利于并行执行。 80 第 7 章 数据库对象管理 • 如果应用通过分区修改或分区连接可以提高性能，那么您可以实现一个滚动窗口来支持历史数据，并且仍然在 另一个维度上进行分区。 • 您可以根据分区键的标识来以不同的方式存储数据。 范围、列表和哈希分区都可以作为组合分区表的子分区策略。数据库将组合分区表中的每个子分区存储为单独的 文件。因此，子分区物理存储属性可能与表的属性不同，也可能与子分区所属的分区不同。 7.3.1.2 分区表 分区表包含一个或多个分区，它们可以单独进行管理，并且可以独立于其他分区进行操作。 表分为分区表和非分区表两张类型。即使分区表只包含一个分区，此表也是与一个未分区表不同的，不能将分区 加入到未分区表中。 7.3.1.2.1 表分区文件 分区表由一个或多个表文件组成。数据库为每一个子分区表分配文件，将每个表分区的数据存储在其自己的文件 中。每个表分区文件包含表数据的一部分。 7.3.1.3 分区索引 与分区表类似，分区索引被分解成更小、更易于管理的索引分区。在 KingbaseES 数据库中，分区表上创建的索 引可分为全局分区索引和本地分区索引。全局分区索引在分区表的所有分区数据基础上创建的索引。本地分区索引是 指在每个表分区上单独创建的索引，是一种局部索引，也是一种分区索引，某一个索引分区只能索引到一个表分区。 与分区表类似，分区索引提高了可管理性、可用性、性能、和可扩展性。 7.3.1.3.1 本地分区索引 在本地分区索引中，索引基于表上相同的列来分区，与表分区具有相同分区数目和相同的分区边界。 每个索引分区仅与底层表的一个分区相关联，所以一个索引分区中的所有键都只引用存储在某个单一表分区中的 行。通过这种方式，数据库会自动同步索引分区及其关联的表分区，使每个表和索引相对保持独立。 本地分区索引在数据仓库环境中很常见，提供了以下优点： • 使分区中的数据不可用的操作只会影响当前分区，这有助于提高可用性。 • 简化了分区维护。当移动一个表分区，或某个分区的数据老化时，只须重建或维持相关联的索引分区。而在全 局索引中所有索引分区必须被全部重建或维护。 • 如果分区发生时间点恢复，则可以将本地分区索引单独恢复，不需要重建整个索引。 示例 7-3-5 创建本地分区索引 使用示例 7-3-1 的 measurement_range 表，在 city_id 列上创建一个本地分区索引： 81 第 7 章 数据库对象管理 CREATE INDEX index_local_measurement_range ON measurement_range(city_id) LOCAL; 在示例 7-3-1 中，measurement_range 表有四个分区，创建的本地分区索引也有四个，系统自动命名分区索引为 q1_city_id_idx、q2_city_id_idx、q3_city_id_idx 和 q4_city_id_idx。每个索引分区与其对应的表分区相关联。 例如，分区索引 q1_city_id_idx 对表分区 q1 中的行建立索引，而分区索引 q2_city_id_idx 对表分区 q2 中的行建 立索引。 当新的分区创建时，对应的本地分区索引也会自动被创建。相同地，删除一个表分区时，对应的本地分区索引会 被自动删除。分区和本地分区索引之间对应的关系由数据库自行维护，不能手动干预。 7.3.1.3.1.1 本地前缀索引和局部非前缀索引 分区索引可以带前缀，也可以不带前缀。 索引子类型定义如下： • 本地前缀索引 在 这 种 情 况 下， 分 区 键 处 于 索 引 定 义 的 前 导 部 分。 例 如，示 例 7-3-1 中的范围分区表 measure- ment_range，logdate 列是范围分区列。如果以 logdate 作为其索引列列表中的第一列，则索引本地 局部前缀索引。 • 本地非前缀索引 在这种情况下，分区键不是索引列列表的前导部分，甚至根本不必在该列表中。例如，在示例 7-3-5 创建 的本地分区索引中，由于分区键 logdate 不属于前导列，所以该索引是局部非前缀索引。 这两种类型的索引都可以充分利用分区消除，此时，优化器将不考虑无关的分区，以加快数据访问速度。查询是 否可以消除分区取决于查询谓词，使用本地前缀索引的查询允许索引分区消除，而使用一个本地非前缀索引的查询可 能不行。 7.3.1.3.1.2 本地分区索引存储 与表分区类似，局部索引分区被存储在其自己的文件中。每个文件包含整个索引数据的一部分。因此，由四个分 区组成的局部索引，不是存储在一个单一索引文件中，而是在四个单独的文件中。 7.3.1.3.2 全局分区索引 全局分区索引指向任意或所有的表分区，而在一个本地分区索引中，索引分区与分区表之间却存在一对一的配对 关系。KingbaseES 数据库支持全局分区索引的创建、修改、删除、维护，vacuum/analyze 以及在查询中支持使用全 局分区索引，支持的索引类型为 B 树索引。不支持在非根节点分区表上创建全局索引，不支持分区和主表列顺序不 一致的情形。 当 KingbaseES 数据库创建全局分区索引时，首先尝试创建本地分区索引，当不满足本地索引的条件（唯一索引 的索引列不包括全部分区列或者分区条件为表达式）时会创建全局索引。全局索引支持条件索引，不支持全局分区索 引。全局索引不支持排他约束。启用分区表上的主键或唯一约束时，先尝试创建本地索引，不满足时则创建全局唯一 索引。 82 第 7 章 数据库对象管理 示例 7-3-6 创建全局分区索引 使用示例 7-3-1 的 measurement_range 表，在 city_id 列上创建一个全局分区唯一索引： CREATE UNIQUE INDEX index_global_measurement_range ON measurement_range(city_id) GLOBAL; 示例 7-3-6 创建的索引，是在 measurement_range 表所有分区上创建的唯一索引，包括所有数据行。 7.3.2 视图概述 视图是一个或多个表的逻辑表示形式。视图在本质上是一个数据存储的查询。 视图的数据来自它所依赖基表，基表可以是表或其他视图。在视图上执行的所有操作实际上都指向基表。所以在 大多数需要使用表的地方，您也可以使用视图。 视图使您能够为不同类型的用户定制数据表示形式。视图通常用于以下场景： • 通过限制对一组预定义的表行或表列的访问，提供一个额外的表安全级别 • 隐藏数据复杂性。例如，一个单一视图的定义可以包含一个联接，它是多个表中的相关列或行的集合。但视图 隐藏了这个事实，即此信息实际上来自于几个表。查询还可能会对表的信息执行大量计算。因此，用户可以查 询视图，但不需要知道如何执行联接或计算。 • 以一个不同于基表的角度来呈现数据。例如，可以重命名视图的列名，而不会影响基表。 • 隔离应用程序和对基表定义的更改。例如，如果视图的查询定义引用了一个四列表的其中三列，若添加第五列 到该表，则视图的定义不会受到影响，并且使用该视图的所有应用程序也不会受到影响。 例如，只允许用户查看 orders 表的某些列数据，可以创建一个视图如下所示： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); CREATE VIEW v_orders AS SELECT orderid, customerid,employeeid, orderdate FROM orders; 与所有子查询一样，定义视图的查询中不能包含 FOR UPDATE 子句。示例中创建的 v_orders 视图只引用了基 表中的四列。 83 第 7 章 数据库对象管理 7.3.2.1 视图特征 与表不同，视图既不分配存储空间，也不包含数据。而是通过定义的一个查询，从它所引用的基表中提取或派生 出数据。视图基于其他对象，除了只需要在数据字典中存储定义视图的查询，它不需要其他存储。视图对其所引用的 对象存在的依赖关系，由数据库自动处理。例如，如果您删除并重新创建了视图的基表，则数据库会确定新的基表是 否仍然符合该视图的定义。 7.3.2.1.1 视图的数据操作 由于视图是从表派生的，所以他们有很多相似之处。用户可以查询视图，在某些限制条件下他们也可以在视图上 执行 DML。在视图上执行的操作会影响到视图的基表中的数据，并遵从基表的完整性约束和触发器。 示例 7-3-7 创建视图 基于 orders 表创建一个视图： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); INSERT INTO orders VALUES(10249,'TOMSP',1,'1996-06-05'),(10264,'FOLKO',6,'1996-07-15'),(10271,'SPLIR',4, '1996-07-24'); CREATE VIEW V_EMP_orders AS SELECT orderid, customerid, employeeid, orderdate FROM orders WHERE employeeid =6 WITH LOCAL CHECK OPTION; 示例 7-3-7 的视图查询定义只引用了员工编号为 6 的行。定义中有 WITH CHECK OPTION，如果对该视图发出 的 INSERT 和 UPDATE 语句所生成的新行不符合该视图的选择条件，则该语句将不会成功。如果新行的 employeeid 不为 6，数据库则拒绝该行的 INSERT 或 UPDATE 操作 7.3.2.1.2 视图的数据访问 数据库将视图定义存储在数据字典中，存的是定义视图查询的文本。当在 SQL 语句中引用视图时，数据库将执 行以下任务： 1. 将 SQL 查询，与定义该视图的查询进行合并，KingbaseES 数据库会优化合并查询，就好像您发出的查询并没 有引用视图一样。因此，无论在视图定义中或在针对该视图的用户查询中是否引用了该列，数据库可以使用任 何被引用基表上的索引。有时，数据库不能将视图定义与 SQL 查询合并。在这种情况下，数据库可能不会使用 所有引用列上的索引。 84 第 7 章 数据库对象管理 2. 在 SQL 缓存区中，解析合并后的 SQL 语句不存在, 数据库就会 SQL 缓存区中解析这个引用了视图的语句。 3. 执行 SQL 语句 示例 7-3-8 视图的数据访问 下面的示例演示了当查询视图时的数据访问。创建基于 orders 表和 customers 表的连接视图： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); INSERT INTO orders VALUES(101,'RDJC',1,'1999-03-20'); CREATE TABLE customers ( customerid VARCHAR(10) CONSTRAINT pk_customers PRIMARY KEY, companyname VARCHAR(30) NOT NULL, city VARCHAR(20) ); INSERT INTO customers VALUES('RDJC','Bejing Renda Jingcang','BEIJING'); CREATE VIEW orders_qry AS SELECT orders.orderid, orders.customerid, orders.employeeid, orders.orderdate, customers.companyname, customers.city FROM customers JOIN orders ON customers.customerid = orders.customerid; 对视图 orders_qry 执行以下查询： SELECT orderid,orderdate,companyname FROM orders_qry WHERE customerid='RDJC'; 数据库将视图和表进行连接，构建如下面的查询，然后执行此查询以检索数据： SELECT orderid,orderdate,orders_qry.companyname FROM customers JOIN orders_qry ON customers.customerid = orders_qry.customerid AND customers.customerid='RDJC'; 85 第 7 章 数据库对象管理 7.3.3 物化视图概述 物化视图是查询结果被提前存储或” 物化” 的模式对象。定义的查询 FROM 子句中可以是命名表、视图、和物化 视图。 物化视图通常用作复制中的主表和数据仓库中的事实表。物化视图用于汇总、计算、复制、和分发数据。它们适 用于如下各种计算环境： • 在数据仓库中，可以使用物化视图来计算和存储由聚合函数（如求和或平均值）所生成的数据。汇总是一个聚 合的视图，它通过预先计算联接和聚合操作，并将结果存储在一个表中，来减少查询时间。物化视图相当于汇 总，您也可以使用物化视图来计算聚合联接。 • 物化视图包含从某个表的单一时间点的完整或部分拷贝。物化视图在分布式站点上复制数据，并将在多个站点 上执行的更新同步。 • 在移动的计算环境中，可以使用物化视图将数据子集从中央服务器下载到移动客户端，从中央服务器定期刷新 客户端，并定期将客户端更新传输回中央服务器。 7.3.3.1 物化视图的特征 物化视图与索引和普通视图具有某些共同特性。 物化视图在以下几方面与索引类似： • 它们包含实际数据，并且占用存储空间。 • 当其主表中数据更改时，他们可以被刷新。 • 当用于查询重写操作时，可以提高 SQL 的执行性能。 • 它们的存在对 SQL 应用程序和用户是透明的。 物化视图也类似于非物化视图，因为它也呈现其他的表和视图中的数据。与索引不同的是，用户可以直接使用 SELECT 语句查询物化视图。取决于所需的刷新类型，物化视图也可以用 DML 语句进行更新。 示例 7-3-9 创建物化视图 创建一个基于 orders 的物化视图, 存取 employeeid 为 6 的数据行： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4 NOT NULL, orderdate DATE ); INSERT INTO orders VALUES(10249,'TOMSP',1,'1996-06-05'),(10264,'FOLKO',6,'1996-07-15'),(10271,'SPLIR',4, '1996-07-24'),(10272,'RATTC',2,'1996-07-09'); CREATE MATERIALIZED VIEW mv_orders AS 86 第 7 章 数据库对象管理 SELECT orderid, customerid, employeeid, orderdate FROM orders WHERE employeeid =6; 然后，删除物化视图 mv_orders 的主表 orders，再查询 mv_orders。查询有结果，是因为物化视图的数据与主 表中的数据是分开存储的。 DROP TABLE orders; SELECT * FROM mv_orders; orderid |customerid |employeeid |orderdate | --------|-----------|-----------|-----------+ 10264 |FOLKO |6 |1996-07-15 | 物化视图可以进行分区。您可以在分区表上定义一个物化视图，并可以在该物化视图上创建一个或多个索引。 7.3.3.2 物化视图的刷新方法 在主表中的数据更改后，数据库通过刷新物化视图来对其进行维护。KingbaseES 目前支持完全刷新。 完全刷新执行会完全替换一个物化视图的内容，旧的内容会被抛弃。创建物化视图时，WITH [ NO ] DATA 这 个子句指定物化视图是否在创建时被填充。如果不是，该物化视图将被标记为不可扫描并且在 REFRESH MATERIALIZED VIEW 被使用前不能被查询。一次完整的刷新可能很慢，尤其是在读取和处理大量数据的情况下。在创建物 化视图之后，您可以在任何时间执行一次完整的刷新。例如，刷新示例 7-3-9 的物化视图 mv_orders： REFRESH MATERIALIZED VIEW mv_orders; 刷新时如果指定了 WITH DATA（默认值），查询将被执行以提供新的数据，并且会让物化视图将处于可扫描的 状态。如果指定了 WITH NO DATA，则不会生成新数据并且会让物化视图处于一种不可扫描的状态。您还可以指定 CONCURRENTLY 选项，对物化视图的刷新不阻塞在该物化视图上的并发选择。如果没有这个选项，一次影响很多 行的刷新将使用更少的资源并且更快结束，但是可能会阻塞其他尝试从物化视图中读取的连接。这个选项在只有少量 行被影响的情况下可能会更快。只有当物化视图上有至少一个 UNIQUE 索引（只用列名并且包括所有行）时，才允 许这个选项。也就是说，该索引不能建立在任何表达式上或者包括 WHERE 子句。当物化视图还未被填充时，这个 选项不能被使用。即使带有这个选项，对于任意一个物化视图一次也只能运行一个 REFRESH。 7.3.4 序列概述 序列是一种模式对象，用户可以用它来生成有序的整数。序列生成器提供可扩展性高的和性能良好的方法，来为 数字数据类型生成代理键。 87 第 7 章 数据库对象管理 7.3.4.1 序列特征 序列定义包括序列的所有信息，如下列所示： • 起始值、最大值和最小值 • 增量值 • 是否缓存序列号集合 • 是否循环 示例 7-3-10 创建序列 下面的示例创建序列 orders_orderid_seq。当向 orders 表中添加行时，应用程序可以使用此序列生成 orderid。 CREATE SEQUENCE orders_orderid_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999 START WITH 1 NOCACHE NOCYCLE; 使用 NEXTVAL 函数访问下一个序列值，如下所示： SELECT NEXTVAL('orders_orderid_seq'); nextval| -------+ 1| 对 orders_orderid_seq 的第一次引用会返回起始值 1。第二次引用返回值 2，每次增量为 1。 7.3.4.2 序列的并发访问 同一序列可以为多个表生成编号。以这种方式，数据库可以自动生成主键，并可以跨多个行或多个表。例如，示 例 7-3-10 创建的序列可以为 orders 表和 order_details 表生成主键。 序列可用于在多用户环境中生成唯一编号，不会引起磁盘 I/O 开销或事务锁定。例如，两个用户同时向 orders 表中插入新行。通过使用序列来为 orderid 列生成唯一编号，任何一个用户都不必等待别人输入订单号。 序列自动为每个用户生成正确的值。每个引用序列的用户都有权访问当前序列号，即在该会话中生成的最后一个 序列值。用户可以发出一个语句生成一个新的序列号，或使用该会话最后生成的当前序列号。单个序列号可以跳过， 如果个别序列号在某个事务中被生成使用，而最终被回滚，则它们会被跳过。 88 第 7 章 数据库对象管理 7.3.5 同义词概述 同义词是一个模式对象的别名。例如，您可以为一个表或视图、序列、PL/SQL 程序单元、用户定义的对象类 型、或另一个同义词等创建同义词。因为同义词只是一个别名，因此除了要在数据字典存储其定义之外，不需要其它 存储。同义词可以为数据库用户简化 SQL 语句。同义词也可以用于隐藏底层模式对象的标识和位置。如果必须重命 名或移动底层对象，仅需要重新定义同义词。基于同义词的应用程序，可以减少大量因对象名改变的修改工作。 KingbaseES 支持创建私有同义词和公共同义词，通过 PUBLIC 参数来指定。公有同义词创建在 PUBLIC 模式 下，所有用户查询公有同义词时候，可以不用指定模式。创建私有同义词必须指定模式，私有同义词只有其所有者对 其可用性具有控制权。同义词的名称不能与同一模式中任何其他同义词同名。 示例 7-3-11 创建同义词 为 orders 表创建了一个叫做 orderinfo 的公共同义词。然后通过 orderinfo 查询表行数： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); INSERT INTO orders VALUES(10249,'TOMSP',1,'1996-06-05'),(10250,'FOLKO',6,'1996-07-15'),(10251,'SPLIR',4, '1996-07-24'); CREATE PUBLIC SYNONYM orderinfo FOR orders; SELECT COUNT(*) FROM orderinfo; count | ------+ 3 | 同义词和引用对象之间没有强依赖关系，数据库不检查引用对象是否存在，同义词也无状态可言，一直是有效。 例如，删除引用对象 orders 表后，同义词 orderinfo 依然有效，如下所示： SELECT synname,refobjnspname,refobjname FROM sys_synonym WHERE synname='orderinfo'; synname |refobjnspname|refobjname| ---------+-------------+----------+ orderinfo|public |orders | DROP TABLE orders; SELECT status FROM all_objects WHERE object_name= 'orderinfo'; status| ------+ 89 第 7 章 数据库对象管理 VALID | 同义词本身不是安全可控的，过度使用公共同义词会导致应用程序之间的命名空间冲突，使数据库整合更困难。 当您在一个同义词上授予对象权限时，你其实是在底层对象上授予权限。同义词在 GRANT 语句中只作为对象的别 名。 7.4 数据完整性约束 本章解释了完整性约束如何执行与数据库关联的业务规则，并防止无效信息进入表。 本章包括以下几节： • 数据完整性简介 • 完整性约束的类型 • 完整性约束的状态 7.4.1 数据完整性简介 保持数据的数据完整性十分重要，它由数据库管理员或应用程序开发人员来确定，以遵从业务规则。业务规则会 指定一些条件和关系，它们要么必须始终为真，要么必须始终为假。 7.4.1.1 保证数据完整性的方式 在设计数据库应用程序时，开发人员有多种方式用于保证存储在数据库中的数据完整性。这些方式包括如下： • 通过数据库触发器存储过程，强制实施业务规则 • 使用存储过程完全控制数据访问 • 在数据库应用程序的代码中执行业务规则 • 使用数据库完整性约束，它们是定义在列级或对象级上的，用来限制数据库中的值的规则 7.4.1.2 完整性约束的优势 完整性约束是一个模式对象，它使用 SQL 来创建和删除。相对于其他强制数据完整性的方法，使用完整性约束 的优点如下： • 容易声明 在您定义或更改表时，使用 SQL 语句定义完整性约束，无需任何额外的编程。SQL 语句易于编写，也易于排除 编程错误。 90 第 7 章 数据库对象管理 • 集中化 完整性约束定义在表上，并存储在数据字典中。因此，由所有应用程序输入的数据都必须遵守相同的完整性约 束。如果约束规则在表级发生了更改，应用程序也不需要变更。甚至在数据库检查 SQL 语句之前，应用程序就可以 利用数据字典中的元数据信息及时告知用户已修改的约束规则。 • 灵活性 当加载大量数据时，您可以暂时禁用完整性约束，以避免性能开销。当数据加载完成后，您可以重新启用完整性 约束。 7.4.2 完整性约束的类型 KingbaseES 数据库可以在表级或列级应用完整性约束。作为列或属性定义的一部分而指定的约束，称为行内规 范约束。作为表定义的一部分而指定的约束称为行外规范约束。有好几种类型的完整性约束定义中都使用键这个术 语。键是某种类型的完整性约束的定义中包含的列或列集。键描述关系数据库中的表与列之间的关系。键中的单个值 称为键值。下表描述了约束的类型，除 NOT NULL 必须指定为行内规范，其它每一个都可以指定为行内或行外规 范。KingbaseES 完整性约束的类型如下表所示： 表 7.4.1: 完整性约束的类型 约束类型 描述 NOT NULL 非空约束，是否允许在指定的列插入或更新包含空值的行。 UNIQUE KEY 唯一约束，在相同的列、或多个列的组合中，不允许多个行具有相同的值，但允许一 些值为空。 PRIMARY KEY 主键约束，合并 NOT NULL 约束和唯一性约束。在相同的列、或多个列的组合中， 不允许多个行具有相同的值，也不允许为空。 FOREIGN KEY 外键约束，指定一个列作为外键，在外和键主键或唯一键之间建立关系，也称为引用 键。 CHECK 检查约束，要求某个数据库值服从指定的条件。 7.4.2.1 NOT NULL 约束 NOT NULL 约束要求表中的列不包含空值。默认情况下，一个表中的所有列都允许空值。NOT NULL 约束主要 用于不能缺少值的列。 例如，orders 表在 employeeid 有非空约束。试图插入一个没有 employeeid 的订单号会生成一个错误： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, 91 第 7 章 数据库对象管理 customerid VARCHAR(10) NOT NULL, employeeid INT4 NOT NULL, orderdate DATE ); INSERT INTO orders(orderid, customerid)VALUES(1,1); ERROR: null value in column "employeeid" violates not-null constraint Detail: Failing row contains (1, 1, null) 只有在表不包含任何行或指定默认值的情况下，才能给列添加 NOT NULL 约束。 7.4.2.2 UNIQUE 约束 UNIQUE 约束要求在一个列或列集中的每个值是唯一的。在一个表中，不允许多个行在有唯一约束的列或列集 上具有重复值。唯一约束适合于不允许存在有重复值的列。唯一约束与主键约束不同，通常它只要求值唯一，但允许 一些值为空。 例如，在供应商表 suppliers 中，存在唯一键 companyname，如下所示： CREATE TABLE suppliers ( supplierid INT4 NOT NULL , companyname VARCHAR(40 char), address VARCHAR(60 char), CONSTRAINT pk_suppliers PRIMARY KEY (supplierid), CONSTRAINT unique_companyname UNIQUE(companyname) ); 如以上示例所示，unique_companyname 约束可以确保每个公司名称都不同，如果插入两个值相同的公司名称， 则会报错： SELECT * FROM suppliers supplierid | WHERE companyname='Bejing Renda Jingcang'; companyname | address| ------------+-----------------------+--------+ 10 | Bejing Renda Jingcang | Beijing| (1 row) INSERT INTO PUBLIC.suppliers(supplierid, companyname,address) VALUES(11, 'Bejing Renda Jingcang','Beijing'); ERROR: duplicate key value violates unique constraint "unique_companyname" Detail: Key (companyname)=(Bejing Renda Jingcang) already exists. 除非也指定了 NOT NULL 约束，否则空值也始终满足唯一键约束。因此，典型的情况是在列上同时具有唯一键 约束和非空约束。这种组合强制用户输入的是唯一值，并消除新行数据与现有行数据发生冲突的可能性。 92 第 7 章 数据库对象管理 7.4.2.3 PRIMARY KEY 约束 PRIMARY KEY 约束中的列或列集，其值能唯一的标识行。每个表只能有一个主键，确保不存在任何重复的非 空行。主键可以是自然键或代理键。自然键是由表中的现有属性组成的一个有特殊含义的标识符。例如，员工表中的 身份证号码就可以作为一个自然键。代理键是一个系统生成的递增标识符，以确保值的唯一性。通常，由一个序列生 成代理键。 PRIMARY KEY 约束可以看成是 UNIQUE 和 NOT NULL 的组合，KingbaseES 数据库的主键约束规则如下： • 任何两行在指定的列或列集上都不具有重复值。 • 主键列不允许空值。 例如，每个订单 ID 编号可以作为一个主键。每个订单只有一行记录。orders 表中 orderid 是主键，添加一行已 经存在的订单 ID 号记录，或者添加一行没有订单 ID 号的记录都会报错，如下所示： CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4 NOT NULL, orderdate DATE ); INSERT INTO orders(orderid, customerid, employeeid) VALUES(10269, 'WHITC', 5); ERROR: duplicate key value violates unique constraint "pk_orders" DETAIL: Key (orderid)=(10269) already exists. INSERT INTO orders(orderid, customerid, employeeid) VALUES(null, 'WHITC', 5); ERROR: null value in column "orderid" violates not-null constraint DETAIL: Failing row contains (null, WHITC, 5). 数据库使用索引来强制主键约束。通常，在某列上创建的主键约束会隐含创建一个唯一索引和一个非空约束。默 认情况下，隐式创建的索引名称即是主键约束的名称，您也可以为索引指定一个用户定义的名称。您可以通过 CREATE TABLE 或 ALTER TABLE 语句中的 USING TABLESPACE 子句，来为索引指定存储选项。 7.4.2.4 FOREIGN KEY 约束 只要两个表包含一个或多个公共列，则数据库可以通过一个外键约束来强制建立两个表之间的关系。外键约束要 求定义约束的列中的每个值，必须与被引用表中的指定列中的值相匹配，这种约束规则维持了两个关联表之间的引用 完整性。外键可以定义在多个列上。但是，组合外键必须引用具有相同数量和相同数据类型列的复合主键或复合唯一 键。外键的值要么匹配被引用主键或唯一键的值，要么为空。如果一个组合外键的任何列为空，则该键的非空部分不 需要匹配父项中的任何列。 93 第 7 章 数据库对象管理 例如，orders 表的 fk_orders_customers 外键，引用 customers 表的 customerid 列。外键约束就保证 orders 表 中 customerid 列的每个值必须与 customers 表的 customerid 列中的某个值相匹配。所以，在 orders 表中就不会存在 无效的 customerid 值。 CREATE TABLE orders ( orderid INT4 CONSTRAINT pk_orders PRIMARY KEY, customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); CREATE TABLE customers ( customerid VARCHAR(10) CONSTRAINT pk_customers PRIMARY KEY, companyname VARCHAR(30) NOT NULL, city VARCHAR(20) ); ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY(customerid) REFERENCES customers(customerid) ON DELETE CASCADE ON UPDATE CASCADE; 7.4.2.4.1 自引用完整性约束 自引用完整性约束是一个引用同一个表中的父键为外键的约束，即引用列和被引用列都在同一张表中。例如， employees 表可以添加一列部门经理编号 managerid, 并引用表中的 employeeid, 这样就可以保证不存在无效的 managerid 值。 7.4.2.4.2 空值和外键 KingbaseES 数据库允许外键的值可以匹配被引用主键或唯一键值，或者为空。例如，用户可以向 orders 表插入 customerid 为空的数据行。如果一个组合外键的任何列为空，则该键的非空部分不需要匹配父项中的任何列。例如， 添加一个组合外键约束，oders 表的 shipvia 列和 shipname 列引用 shippers 表的 shipperid 列和 companyname 列， 则允许 shipname 列的值是 null。 7.4.2.4.3 父键的删除和更新 删除或修改更新会影响外键和父键之间的关系。在父键被修改时，参照完整性约束可以指定在子表中的相关行 上，执行以下某种操作之一： • 限制删除或更新操作 (RESTRICT CASCADE OR RESTRICT CASCADE) RESTRICT 表示阻止删除或更新一个被引用的行，NO ACTION(DELETE NO ACTION OR UPDATE NO ACTION) 表示约束被检查时如果有任何引用行存在，则会抛出一个错误，这是默认动作。这两种选择 94 第 7 章 数据库对象管理 的本质不同在于 NO ACTION 允许检查被推迟到事务的最后，而 RESTRICT 则不会。例如，orders 表的 fk_orders_customers 外键，引用 customers 表的 customerid 列，如果删除 customers 表被引用列所在的行会 报错违反约束。 • 级联删除或更新 (DELETE CASCADE OR UPDATE CASCADE) 级联删除或更新是当包含被引用键值的行被删除或更新时，子表中的所有的外键依赖值所在行也会被删除或更 新。例如，删除 customers 表中的某行记录，会导致订单表中所有属于这个客户的订单记录行也被删除。 • 对删除或者更新置空 (DELETE SET NULL OR UPDATE SET NULL) 删除或者更新置空是当包含被引用键值的行被删除或者更新时，子表中的所有的外键依赖值被全部置空。例 如，删除 customers 表中的某行，会导致订单表中所有属于这个客户的订单记录行中的 customerid 列被置为 空。 7.4.2.4.4 索引和外键 一个外键所引用的列必须是一个主键或者被唯一约束所限制。这意味着被引用列总是拥有一个索引（位于主键或 唯一约束之下的索引），因此在其上进行的一个引用行是否匹配的检查将会很高效。由于从被引用表中 DELETE 或 者 UPDATE 一行时，一个被引用列将要求对引用表进行扫描以得到匹配旧值的行，在引用列上建立合适的索引也会 提高检索速度。但由于这种做法并不是必须的，而且创建索引也有很多种选择，所以在 KingbaseES 数据库中外键约 束的定义并不会自动在引用列上创建索引。 7.4.2.5 Check 约束 检查约束是最普通的约束类型，它允许指定一个特定列中的值必须要满足一个布尔表达式。检查约束在其约束表 达式值为真或空值时被满足，如果 DML 语句导致约束条件的计算结果为假，则 SQL 语句将被回滚。检查约束的主 要好处是，具备强制执行非常具体的完整性规则的能力。 例如，在 orders 表中增加一个检查约束，运费 freight 列的值不能大于 1000，如下所示： ALTER TABLE orders ADD CONSTRAINT check_freight CHECK (freight < 1000); 现在尝试插入运费大于 1000 的值，报错如下所示： INSERT INTO orders(orderid, customerid, employeeid, freight) VALUES(999,2,2,1009); ERROR: new row for relation "orders" violates check constraint "CHECK_freight" DETAIL: Failing row contains (999, 2, 2, 1009.00). 可以在列定义中包括多个检查性约束。例如，除了上面示例所示运费 freight 列值不能大于 1000，还可以再添 加一个检查约束使其值必须大于 5。如果在某列上存在多个检查约束，则他们必须被合理设计，保证业务不会发生冲 突。但数据库不会检验这些检查条件的顺序和是否相互排斥。 95 第 7 章 数据库对象管理 7.4.3 完整性约束的状态 作为约束定义的一部分，您可以指定数据库应如何及何时强制执行该约束条件，从而确定约束状态。 7.4.3.1 数据检查 KingbaseES 数据库可以指定将某个约束应用于现有数据还是应用于新数据。如果约束是启用的，则当输入或更 新数据时，数据库会检查新的数据。不符合该约束的数据不能输入到数据库。例如，对 orders 表的 customerid 列启 用 NOT NULL 约束，可以保证新插入每一个订单记录都有一个 customerid。如果约束是禁用的，则表可能存在违反 约束的行。 您可以将约束设置为以下两种验证模式之一： • 验证 (VALIDATE) 现有数据必须符合约束。例如，orders 表的 customerid 列启用 NOT NULL 约束，并将其设置为 VALIDATE 检查现有的每一行的 customerid 是否都非空。 • 不验证 (NOVALIDATE) 当表的约束处于启用非校验状态时，表中已有的数据可以不符合约束，但后面新增的数据必须进行校验， 这对于数据仓库来说特别有用。因为基本上数据仓库都是使用增量更新，在停用约束后如果采用启用校验 约束状态，则需要对大量数据进行校验，影响性能。例如，可以在 orders 表的 orderdate 列上创建一个 NOT NULL 约束，并将该约束设置为 NOVALIDATE。 数据检查的验证模式如下表所示： 表 7.4.2: 数据检查的验证模式 新数据 已存在的数据 说明 ENABLE VALIDATE 现有的和新数据都必须遵守约束。如果试图在一个已填 充的表上应用一个新的约束，而现有的行违反该约束， 则会导致一个错误。 ENABLE NOVALIDATE 数据库会检查该约束，但不需要对所有为行。真。因 此，已存在的行可以违反该约束，但新的或修改后的行 必须遵守该约束。这种模式通常用于包含已验证完整性 的现有数据的数据仓库。 DISABLE VALIDATE 数据库禁用该约束，并防止修改受约束的列。 DISABLE NOVALIDATE 不检查约束，也不需要为真。 7.4.3.2 检查约束的有效性 约束可以处于不可延迟（默认值）或可延迟两种状态之一。该状态确定数据库何时检查约束的有效性 96 第 7 章 数据库对象管理 7.4.3.2.1 不可延迟约束 (NOT DEFERRABLE) NOT DEFERRABLE 是默认值, 不可延迟的约束将在每一次命令后立刻被检查，如果违反了约束，则该语句被 回滚。例如，orders 表的 orderdate 列上创建一个不可延迟的 NOT NULL 约束。如果用户试图插入一行没有订购日 期的记录，则数据库会立即回滚该语句，因为违反了 NOT NULL 约束。 7.4.3.2.2 可延迟约束 (DEFERRABLE) 可延迟约束允许事务使用 SET CONSTRAINT 子句将约束检查推迟到事务结束时。如果你对数据库中做了可能 违反约束的更改，则此设置让您有效地禁用该约束，直到完成所有更改。 您可以设置数据库检查可延迟约束时的默认时间。您可以指定下列属性之一： • INITIALLY IMMEDIATE 数据库在每个语句执行后，立即检查约束。如果违反了约束，则数据库回滚该语句。 • INITIALLY DEFERRED 数据库在事务结束时检查约束，如果违反了约束，则数据库回滚该事务。 例如，在 orders 表的 orderdate 列上的创建可延迟的 PRIMARY KEY 约束，并被设置为 INITIALLY DEFERRED。主键值有重复时，当用户尝试提交时，数据库才检查约束并回滚所有的语句。 示例如下： --创建表、可延迟约束 CREATE TABLE orders ( orderid INT4 , customerid VARCHAR(10) NOT NULL, employeeid INT4, orderdate DATE ); ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY(orderid) DEFERRABLE INITIALLY DEFERRED; --设置事务约束延迟 \set AUTOCOMMIT off START TRANSACTION; SET CONSTRAINTS ALL DEFERRED; --验证 INSERT INTO orders(orderid,customerid,orderdate) VALUES(100,'KINGBASE','2000-01-01'); INSERT INTO orders(orderid,customerid,orderdate) VALUES(100,'KINGBASE','2000-03-01'); --事务提交时才会检查约束，提示违反约束限制的错误 COMMIT; 错误：重复键违反唯一约束 97 第 7 章 数据库对象管理 7.4.3.3 约束检查的示例 下面的例子可以帮助说明数据库何时执行检查约束。使用 employees 表分析，存在自引用外键约束，部门经理编 号 managerid 引用表中的员工编号 employeeid。 CREATE TABLE employees ( employeeid INT4 CONSTRAINT pk_orders PRIMARY KEY, managerid INT4, territoryid INT4 ); ALTER TABLE employees ADD CONSTRAINT fk_employees FOREIGN KEY(managerid) REFERENCES employees(employeeid) ON DELETE CASCADE ON UPDATE CASCADE; INSERT INTO employees VALUES(1,1,1); INSERT INTO employees VALUES(10,1,1); 例如，当父键值不存在时，往外键列中插入值。考虑 employees 表中插入第一行的情况。由于当前不存在任何 行，那么如果 managerid 列中的值不能引用表 employeeid 列中的任何现有值，输入新行可能存在的情况如下： • 如果在 managerid 列上没有定义 NOT NULL 约束，可以为第一行中的列输入一个空值。因为在外键中允许使 用空值，此行可以被插入到表中。 • 如果员工是部门经理，插入的 employeeid 列和 managerid 列的值相同。这种情况是允许的，数据库是在完全运 行该语句之后，执行其约束检查的。若要允许输入在父键和外键中具有相同值的行，数据库必须首先运行该语 句，然后再确定表中是否有 employeeid 列对应新行的 managerid 列。 • 一个多行的 INSERT 语句，如一个具有嵌套 SELECT 语句的 INSERT 语句，可以插入相互引用的多个行。约 束检查被推迟到整个语句执行完成。首先，所有行先被插入，然后对所有行进行违反约束检查。 在语句进行解析之前，其默认值作为 INSERT 语句的一部分被包括在内。因此，默认列值都要遵从所有完整性 约束检查。例如：同时更新外键和父键的值。假设需要对员工编号重新设置。对员工编号加 100，您可以执行下面的 SQL 语句来更新数据： UPDATE employees SET employeeid= employeeid +100，managerid=managerid+100; 虽然已定义了一个约束，以验证引用值匹配，但前面的语句仍然是合法的，因为数据库在该语句完成后检查约 束。 本节中的示例只说明了在 INSERT 和 UPDATE 语句执行过程中的约束检查机制。实际上，数据库在所有类型的 DML 语句中都使用相同的机制。 98 第 7 章 数据库对象管理 7.5 数据字典和动态性能视图 每个 KingbaseES 数据库的只读引用表和视图的中心集合统称为数据字典。动态性能视图是在数据库打开和使用 时不断更新的特殊视图。 本章包括以下几节： • 数据字典概述 • 动态性能视图概述 • 数据库对象的元数据 7.5.1 数据字典概述 KingbaseES 数据库的一个重要部分是它的数据字典，提供有关数据库管理元数据的表和视图。 数据字典包含如下信息： • 在数据库中每个模式对象的定义，包括列的默认值和完整性约束信息 • 分配给模式对象的空间量及当前已使用量 • KingbaseES 数据库用户的名称、授予用户的权限和角色、和与用户相关的审计信息 就像存储其他数据一样，数据库将数据字典数据存储在表中，用户可以使用 SQL 来查询其中的数据。数据字典 包含基表和视图。 这些对象的定义如下： • 基表 它们存储有关数据库的信息。只应该由数据库写入和读取这些表。用户很少直接访问基础表，因为他们已被规范 化，且大多数数据存储为一种特定的格式。 • 视图 它们通过使用联接和 WHERE 子句来简化信息，将基础表的数据解码成有用的信息（如用户或表名等）。这些 视图包含数据字典中的所有对象的名称和描述。一些视图可以被所有数据库用户访问，而其他一些则仅供管理员访 问。 7.5.1.1 数据字典的内容 数据字典包含系统视图和一些兼容 oracle 的视图。一些视图可以被所有数据库用户访问，而其他一些则仅供管理 员访问。 KingbaseES 数据库创建后，会生成几个默认的模式。information_schema 信息模式和 sys_catalog 提供主要的 系统视图，包含当前数据库里定义的用户，模式对象和存储结构信息，以及所有的内置数据类型、函数和操作符等。 KingbaseES 数据库 sys 模式下还提供一些兼容 oracle 的视图，分为以下几类： 99 第 7 章 数据库对象管理 • 具有 DBA_ 前缀的视图 具有 DBA_ 前缀的视图显示在整个数据库中的所有相关信息。 • 具有 ALL_ 前缀的视图 具有 ALL_ 前缀的视图，是站在用户角度，从整体上看待数据库。 • 具有 USER_ 前缀的视图 具有 USER_ 前缀的视图指的是用户在数据库中的私有环境，包括用户所创建的模式对象的元数据，对该用户 的授权等等。 • DUAL 表 DUAL 是数据字典中的一个很小的表，数据库和用户编写的程序可以引用它，以保证一个已知的结果。 另请参阅: 系统视图详见《KingbaseES 数据库参考手册》 7.5.1.2 数据字典的存储 数据字典基表是在数据库初始化时创建的第一批对象。所有数据库的数据字典表和视图都存储在 sys_global 表 空间中。当数据库打开时，sys_global 表空间始终处于联机状态，数据字典总是可用的。 7.5.1.3 数据字典的使用 在数据库运行期间，数据库读取数据字典，以确定存在模式对象，且用户对它们具有适当的访问权限。KingbaseES 数据库也会不断地更新数据字典，以反映对数据库结构、审计、授权、和数据等所做的更改。数据字典由系 统用户自行维护，其他用户不能修改系统表或系统视图。 7.5.1.3.1 数据字典缓存 很多数据字典信息存在于数据字典缓存中，因为数据库需要这些信息，来不断验证用户的访问权限，并验证模式 对象的状态。解析信息通常保存在缓存中。 7.5.1.3.2 其他程序与数据字典 版本迭代时，新版本的 KingbaseES 数据库产品可能会引用现有的视图，并创建额外的数据字典表或视图。 7.5.2 动态性能视图概述 在 KingbaseES 数据库的操作过程中，它会维护一组记录当前数据库活动的虚拟表。性能视图是动态的，因为他 们在数据库处于打开状态和在使用过程中会不断更新。 • 系统和会话参数 100 第 7 章 数据库对象管理 • 内存使用和分配 • CPU 和 IO 优化点分析 • 文件状态 • 会话和事务进度 • SQL 运行 • 统计和度量 动态性能视图有以下主要用途： • 数据库管理器使用这些视图，来获取有关数据库的信息 • 管理员可以使用这些视图，用于性能监控和调试。 7.5.2.1 动态性能视图的内容 动 态 性 能 视 图 不 能 被 数 据 库 管 理 员 更 改 或 删 除， 用 户 只 能 访 问 动 态 性 能 视 图。 动 态 性 能 视 图 都 存 放 在 sys_catalog 模式下，动态性能视图基于系统动态性能表创建。例如，sys_stat_activity 视图包含数据库所有实 时的会话信息。动态性能视图内容详见《KingbaseES 数据库参考手册》。 7.5.2.2 动态性能视图的存储 动态性能视图基于从数据库内存结构生成的虚拟表。这些视图不是存储在数据库中的常规表。由于数据是动态更 新的，所以不能保证视图的读一致性。因为动态性能视图并不是真正的表，其数据取决于数据库和实例的状态。 7.5.3 数据库对象的元数据 DBMS_METADATA 包是 Kingbase 数据库中提供的从数据库字典中以 DDL 语句形式检索元数据的一系列函 数。您还可以通过 KingbaseES 图形化数据库对象管理工具，直接选择查看每个对象的 DDL 语句。 101 版权声明 版权声明 北京人大金仓信息技术股份有限公司（简称：人大金仓）版权所有，并保留对本手册及本声明的一切权利。 未得到人大金仓的书面许可，任何人不得以任何方式或形式对本手册内的任何部分进行复制、摘录、备份、修 改、传播、翻译成其他语言、将其全部或部分用于商业用途。 免责声明 本手册内容依据现有信息制作，由于产品版本升级或其他原因，其内容有可能变更。人大金仓保留在没有任何通 知或者提示的情况下对手册内容进行修改的权利。 本手册仅作为使用指导，人大金仓在编写本手册时已尽力保证其内容准确可靠，但并不确保手册内容完全没有错 误或遗漏，本手册中的所有信息也不构成任何明示或暗示的担保。 技术支持 • 人大金仓官方网站：http://www.kingbase.com.cn/ • 人大金仓文档中心：http://help.kingbase.com.cn/ • 全国服务热线：400-601-1188 • 人大金仓技术支持与反馈信箱：support@kingbase.com.cn 102 服务周期承诺 服务周期承诺 由于市场需求在不断变化，技术创新和发展的进程不断加剧，产品的版本更迭不可避免。人大金仓对于产品版本 生命周期的有效管理，有助于您提前规划项目，更好地从产品服务终止上过渡。 表 1: KingbaseES 产品生命周期里程碑 关键里程碑点 定义 产品发布日期 产品正式发布版本，即 GA（general availability）版本的发布日期。 停止销售日期 正式停止销售的日期，版本停止接受订单日。该日之后，产品将不再销售。 停止功能升级日期 在该日期之后，不再提供新特性和新硬件支持。但依旧提供错误修复、安全修复、功 能维护等服务。 停止功能维护日期 在该日期之后，不再维护功能，修复问题。但依旧提供安全修复等服务 停止安全维护日期 在该日期之后，不再发布补丁版本修复中高风险漏洞，仅提供有限的支持。 产品服务终止日期 停止提供产品服务和支持的日期。包括软件维护版本，缺陷修复，以及针对该产品的 所有服务支持（包括服务热线和远程/现场支持）。 服务周期策略 金仓数据库管理系统 KingbaseES 产品确保以下的服务周期： 1）产品自发布之日起至产品停止功能升级（包含新特性、新硬件支持）之日不少于 5 年。 2）产品停止功能升级之日起至产品停止功能维护（主要包括问题修复）之日不少于 4 年。 3）产品功能维护停止之日起至产品停止安全维护（包括中高风险漏洞修复）之日不少于 2 年。 服务终止策略 金仓数据库管理系统 KingbaseES 产品确保在销售后，至少提供 6 年的服务支持。 注意: 人大金仓将会综合各方因素来确定产品服务终止日期。并将在实际产品服务终止日期之前至少 90 天，通过公 103 服务周期承诺 开方式宣布产品服务终止日期。 104