KingbaseES数据库开发指南.pdf

KingbaseES 数据库开发指南 金仓数据库管理系统 KingbaseES 文档版本：V9（V009R001C001B0024） 发布日期：2023 年 10 月 12 日 北京人大金仓信息技术股份有限公司 目 目 录 录 第 1 章 前言 1 1.1 适用读者 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 相关文档 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 术语 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.4 手册约定 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 第 2 章 开发基础 2.1 设计基础 2.1.1 2.1.2 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 数据库部署模式 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.1.1.1 单机模式 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2.1.1.2 双机热备模式 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.1.1.3 读写分离集群模式 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.1.1.4 数据库运行模式选择 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 数据库环境规划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 运行环境规划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.1.2.1.1 主机规划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.1.2.1.2 网络规划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1.2.1.3 存储规划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 数据库安装部署规划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.1.2.2.1 软件安装路径 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.1.2.2.2 数据库实例配置 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.1.2.2.3 数据库参数设置 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.1.2.2.4 表空间设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.1.2.2.4.1 业务数据量估算 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.1.2.2.4.2 表空间使用规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.1.2.2.4.3 表空间的类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.1.2.2.4.4 表空间相关概念 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.1.2.2.4.5 表空间的分配原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 表和索引设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.1.2.2.5.1 表的参数设置 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.1.2.2.5.2 索引的使用原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.1.2.1 2.1.2.2 2.1.2.2.5 I 目 录 2.1.3 可扩展性设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.1.4 安全设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.1.5 可移植性设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.1.6 可诊断性设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.1.7 业务系统设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.1.7.1 在线事务处理系统 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.1.7.2 在线分析系统 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.1.7.3 数据库的规模 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 特殊场景特性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2.1.8.1 SQL 分析函数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2.1.8.2 物化视图 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.1.8.3 分区 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.1.8.3.1 逻辑分割 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.1.8.3.2 物理分割 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.1.8.3.3 数据分区的优缺点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.1.8.3.4 KingbaseES 分区技术 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.1.8.3.5 分区索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.1.8.3.6 分区使用建议 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 数据库应用程序的连接策略 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 2.2.1 连接池设计准则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 2.2.2 登录策略 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 2.2.3 防止程序会话泄漏 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 安全 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 用户设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 2.3.1.1 用户权限管理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 2.3.1.2 用户安全实现 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 2.3.1.3 用户类型及角色命名规范 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 数据访问控制 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2.3.2.1 数据脱敏 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2.3.2.2 标记和强访问控制 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 2.3.2.3 透明加密 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 数据库审计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 PLSQL 的高级特性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 2.4.1 PLSQL 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 2.4.2 动态 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.4.3 异常处理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.4.4 批量绑定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.1.8 2.2 2.3 2.3.1 2.3.2 2.3.3 2.4 第 3 章 面向应用程序的 SQL 开发 3.1 26 SQL 处理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 SQL 语句的处理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 SQL 语句的处理过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 3.1.1 3.1.1.1 II 目 录 SQL 缓存区 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 事务操作的分组 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.1.2.1 关于事务操作的分组 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.1.2.2 提高事务性能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.1.3 事务的只读 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 3.1.4 表的显式锁定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 显式表级锁 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 3.1.4.1.1 行共享和行独占模式锁定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.1.4.1.2 共享模式锁定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.1.4.1.3 共享行独占模式锁定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.1.4.1.4 独占模式锁定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 显式行级锁 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.1.5 事务控制表锁定 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 3.1.6 事务的并发控制 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 3.1.6.1 事务并发和隔离级别 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 3.1.6.2 设置隔离级别 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 3.1.6.3 读取和可串行化隔离级别 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 DDL 语句的阻塞 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 SQL 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 数据类型的选择 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 3.2.1.1 正确的数据类型提高数据完整性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 3.2.1.2 选择合适的数据类型降低存储开销 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 3.2.1.3 正确的数据库类型提高性能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 3.2.2 字符数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 3.2.3 数值数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.2.4 时间日期数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.2.4.1 查询当前时间 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.2.4.2 插入和查询日期 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.2.4.3 插入和查询时间 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.2.4.4 日期时间数据的运算 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 3.2.4.5 日期时间数据的转换函数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 3.2.4.6 导出和导入日期时间数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 特殊数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 3.2.5.1 空间数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 3.2.5.2 大对象类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 3.2.5.3 json 数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 3.2.5.4 xml 数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 伪列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 正则表达式的应用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 3.3.1 正则表达式概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 3.3.2 KingbaseES 对正则表达式的支持 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 3.3.3 正则表达式标准 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 3.1.1.2 3.1.2 3.1.4.1 3.1.4.2 3.1.7 3.2 3.2.1 3.2.5 3.2.6 3.3 III 目 录 正则表达式的运算符 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 3.3.4.1 KingbaseES 对 POSIX 运算符的支持 . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 3.3.4.2 KingbaseES-Perl 对 POSIX 运算符的扩展 . . . . . . . . . . . . . . . . . . . . . . . . 40 索引的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 3.4.1 使用索引的基本准则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 3.4.2 管理索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 3.4.3 函数索引的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 3.4.3.1 函数索引的优缺点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 3.4.3.2 函数索引的示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 3.4.3.2.1 基于算术表达式的函数索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 3.4.3.2.2 基于自定义函数的索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 3.4.3.2.3 基于大小写敏感的函数索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 数据完整性的维护 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 3.5.1 使用约束限制业务规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 3.5.2 索引约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 3.5.3 非空约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 3.5.4 默认值 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 3.5.5 主键约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 3.5.6 唯一约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 3.5.7 外键约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 管理外键约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 3.5.7.1.1 外键列的数据类型和名称 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 3.5.7.1.2 创建外键的权限 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 3.5.7.1.3 外键的属性选择 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 3.5.7.2 外键与空值 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 3.5.7.3 引用表的关系 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 3.5.7.4 多外键约束规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 3.5.7.5 延迟约束检查 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 3.5.7.6 外键与索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 3.3.4 3.4 3.5 3.5.7.1 3.5.8 check 约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 3.5.8.1 check 约束的限制 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 3.5.8.2 check 约束设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 3.5.8.3 多个 check 约束规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 3.5.8.4 check 约束和非空约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 启用和禁用约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 3.5.9.1 禁用约束的场景 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 3.5.9.2 启用和禁用约束的执行 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 3.5.9.3 修复异常约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 3.5.10 修改约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 3.5.11 约束的重命名 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 3.5.12 删除约束 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 3.5.13 约束信息的数据字典 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 3.5.9 IV 目 第 4 章 面向应用程序的 PL/SQL 开发 4.1 51 PL/SQL 子程序和包的编写 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 4.1.1 PL/SQL 子程序概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 4.1.2 PL/SQL 包概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 4.1.3 PL/SQL 单元概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 4.1.4 创建 PL/SQL 子程序和包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.1.4.1 创建子程序和包所需的权限 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.1.4.2 创建子程序和包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.1.4.3 PL/SQL 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 PL/SQL 标量数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 4.1.4.3.1.1 SQL 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 4.1.4.3.1.2 PLS_INTEGER 和 BINARY_INTEGER 数据类型 . . . . . . . . . . 55 4.1.4.3.1.3 REF CURSOR 数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . 55 4.1.4.3.1.4 用户定义的 PL/SQL 子类型 . . . . . . . . . . . . . . . . . . . . . . . 56 4.1.4.3.2 PL/SQL 复合数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4.1.4.3.3 抽象数据类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 向客户端返回结果集 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4.1.4.4.1 游标变量的优点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.1.4.4.2 游标变量的缺点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.1.4.4.2.1 解析游标变量的影响 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.1.4.4.2.2 游标变量的多行提取影响 . . . . . . . . . . . . . . . . . . . . . . . . . 58 隐式返回查询结果 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 4.1.4.5 从函数返回大量数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 4.1.4.6 PL/SQL 函数结果缓存 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 4.1.4.7 批量绑定概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 4.1.4.7.1 引用集合的 DML 语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 4.1.4.7.2 引用集合的 SELECT 语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 4.1.4.7.3 引用集合并返回 DML 的 FOR 循环 . . . . . . . . . . . . . . . . . . . . . . . 62 PL/SQL 动态 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 4.1.5 更改 PL/SQL 子程序和包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 4.1.6 删除 PL/SQL 子程序和包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 4.1.7 从 SQL 语句调用存储的 PL/SQL 函数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 4.1.7.1 为什么从 SQL 语句中调用 PL/SQL 函数？ . . . . . . . . . . . . . . . . . . . . . . . . 65 4.1.7.2 PL/SQL 函数可以出现在 SQL 语句中的位置 . . . . . . . . . . . . . . . . . . . . . . . 65 4.1.7.3 PL/SQL 函数何时可以出现在 SQL 表达式中 . . . . . . . . . . . . . . . . . . . . . . . 66 4.1.7.4 控制从 SQL 语句调用的 PL/SQL 函数的副作用 . . . . . . . . . . . . . . . . . . . . . 67 4.1.7.4.1 对从 SQL 语句调用的函数的限制 . . . . . . . . . . . . . . . . . . . . . . . . 67 4.1.7.4.2 从并行化 SQL 语句调用的 PL/SQL 函数 . . . . . . . . . . . . . . . . . . . . 68 PL/SQL Profiler 的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 4.2.1 PL/SQL Profiler 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 4.2.2 收集数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 4.2.3 了解 profiler 对象 74 4.1.4.3.1 4.1.4.4 4.1.4.4.3 4.1.4.8 4.2 录 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . V 4.2.4 4.2.5 目 录 了解 profiler 数据信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 4.2.4.1 执行信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 4.2.4.2 堆栈信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 4.2.4.3 源码信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 分析数据 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 4.2.5.1 理解层次分析关系 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 4.2.5.2 相关话题 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 第 5 章 面向应用程序的高级功能 5.1 5.2 5.3 闪回技术 77 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 5.1.1 闪回概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 5.1.2 闪回的配置 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 5.1.3 闪回的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 5.1.3.1 闪回表的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 5.1.3.2 清除回收站 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 编程环境的选择 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 5.2.1 应用程序体系结构概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 5.2.2 KingbaseES PL/SQL 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 5.2.3 KingbaseES JAVA 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 5.2.4 KingbaseES ODBC 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 5.2.5 KingbaseES 预编译器概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 5.2.6 KingbaseES OCCI 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 5.2.7 KingbaseES .NET Data Provider 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 数据库的依赖关系 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 5.3.1 对象依赖关系概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 5.3.2 对象状态 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 5.3.3 对象状态的失效 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 5.3.4 减少依赖无效的准则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 5.3.5 对象的重新验证 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 第 6 章 业务系统开发建议 6.1 82 数据库模型设计规范 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 6.1.1 命名规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 6.1.2 注释规范 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 6.1.3 数据库对象 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 6.1.3.1 建表的参数设置 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 6.1.3.2 主外键设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 6.1.3.3 列设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 6.1.3.4 临时表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 6.1.3.5 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 6.1.3.6 视图 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 6.1.3.7 存储过程、函数和包 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 6.1.3.8 触发器 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 VI 目 录 序列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 6.1.3.10 别名 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 6.1.3.11 Database Link . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 PLSQL 开发规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 6.2.1 总体开发原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 6.2.2 程序编写规范 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 6.2.2.1 PL/SQL 中的 SQL 编写规范 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 6.2.2.2 变量声明原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 6.2.2.3 游标 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 6.2.2.4 集合 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 6.2.2.5 动态 PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 6.2.2.6 对象 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 6.2.2.7 LOB 类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 6.2.2.8 PACKAGE(包) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 6.2.2.9 故障处理规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 6.1.3.9 6.2 6.3 SQL 语句编写规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 6.3.1 6.3.2 查询语句的使用原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 6.3.1.1 使用正确索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 6.3.1.2 改写查询语句 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 6.3.1.3 减少排序发生 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 6.3.1.4 使用并行查询 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 6.3.1.5 减少死锁发生 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 6.3.1.6 使用集合运算符 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 6.3.1.7 使用连接方式的原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 6.3.1.8 复杂查询的原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 DML 语句的调整原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 6.3.2.1 KingbaseES 存储参数的影响 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 6.3.2.2 大数据类型的影响 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 6.3.2.3 DML 执行时约束的开销 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 6.3.2.4 DML 执行时维护索引所需的开销 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 第 7 章 其他数据库开发者转向 KingbaseES 的注意事项 111 版权声明 112 服务周期承诺 113 VII 第 1 章 前言 1 第 章 前言 本文档本主要以设计者的角度进行组织编写，为 KingbaseES 数据库应用系统在规划和开发等环节工作的规范化 提供参考，力求对实际的设计、开发工作起到规范和指导作用。 前言部分包含以下主题： • 适用读者 • 相关文档 • 术语 • 手册约定 1.1 适用读者 KingbaseES 数据库开发指南面向项目基础设施可行性研究、设计和系统设计开发人员。 1.2 相关文档 有关更多与 KingbaseES 开发相关的信息，请参阅 KingbaseES 数据库文档集中的以下文档： • KingbaseES 数据库管理员指南 • KingbaseES SQL 语言参考手册 • KingbaseES PL/SQL 语言参考手册 • KingbaseES 安全指南手册 以及 KingbaseES 应用开发和迁移合集手册，包括各种接口的指南。 1 第 1 章 前言 1.3 术语 1.4 手册约定 本文档中可能出现“注意、提示、警告、另请参阅”等标志，它们所代表的含义如下： 注意: 用于突出重要/关键信息、最佳实践等。 提示: 用于突出小窍门、捷径等。 警告: 用于传递设备或环境安全警示信息，若不避免，可能会导致设备损坏、数据丢失、设备性能降低或其 它不可预知的结果。 另请参阅: 用于突出参考、参阅等。 以下程序代码书写约定适用于本文档： 符号 说明 [] 表示包含一个或多个可选项。不需要输入中括号本身。 {} 表示包含两个以上（含两个）的候选，必须在其中选取一个。不需要输入花括号本身。 | 分割中括号或者花括号中的两个或两个以上选项。不需要输入“|”本身。 ... 表示其之前的元素可以被重复。 斜体 表示占位符或者需要提供特定值的变量。 大写 表示系统提供的元素，以便与用户定义的元素相互区分。除出现在方括号中的元素外，应当按 照顺序逐字输入。当然，部分元素在系统中是大小写不敏感的，因此用户可以根据系统说明以 小写形式输入。 小写 表示由用户提供的元素。 2 第2章 开发基础 2 第 章 开发基础 本章节包括的主题如下： • 设计基础 • 数据库应用程序的连接策略 • 安全 • PLSQL 的高级特性 2.1 设计基础 2.1.1 数据库部署模式 本章节介绍了 KingbaseES 数据库的部署运行方式。本节包含以下内容： • 单机模式 • 双机热备模式 • 读写分离集群模式 • 数据库运行模式选择 2.1.1.1 单机模式 数据库服务器采用单服务器模式，满足对可用性和性能要求不高的应用，具备以下特点: 1. 硬件成本低。单节点，硬件投入较低，满足非重要系统的需求。 2. 安装配置简单。由于是单节点、单实例，所以安装配置比较简单。 3. 管理维护成本低。单实例，维护成本低。 4. 对应用设计的要求较低。由于是单实例，不存在集群应用设计时需要注意的事项，所以应用设计的要求较低。 3 第2章 开发基础 5. 可用性不高。由于是单服务器、单实例，所以服务器和实例的故障都会导致数据库不可用。 6. 扩展性差。无法进行横向扩展，只能进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库服务器无 法进行增加节点、实例等横向扩展，只能进行增加硬件配置等纵向扩展，且扩展性有局限。 7. 根据该模式的特点有如下要求： 1) 硬件配置方面预留扩展量。由于该模式无法进行横向扩展，所以在选择硬件配置时要为以后的纵向扩展预 留扩展量，避免硬件无法满足性能需求的情况。 2) 充分考虑该模式是否满足应用未来一段时间的需求。需要考虑应用在未来一段时间是否会发生变化，该模 式是否满足应用变化的需求。 2.1.1.2 双机热备模式 数据库服务器采用双机热备模式，可以满足对可用性有一定要求的应用， 具备以下特点： 1. 需要冗余的服务器设备。该模式需要有冗余的服务器硬件，以满足一备一或者一备多的需求。硬件成本较高。 2. 需要 HA 软件的支持。该模式需要配合 HA 软件才可以实现。 3. 安装配置相对简单。该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤。 4. 管理维护成本低。单实例，对维护人员的要求较低，维护成本低。 5. 对应用设计的要求较低。由于是单实例，不存在 RAC 系统应用设计时需要注意的事项，所以应用设计的要求 较低。 6. 具备一定的高可用性。由于是多服务器、单实例，所以服务器和实例有故障时会发生实例在不同服务器上的切 换，导致数据库的暂时不可用。无法满足对可用性有严格要求的应用类型。 7. 扩展性差。无法进行横向扩展，只能进行纵向扩展。当应用对性能有更高的要求时，该模式的数据库服务器无 法进行增加节点、实例等横向扩展，只能进行增加硬件配置等纵向扩展，且扩展性有局限。 根据该模式的特点有如下要求： 1) 硬件配置方面预留扩展量。由于该模式无法进行横向扩展，所以在选择硬件配置时要为以后的纵向扩展预留扩 展量，避免硬件无法满足性能需求的情况。 2) 充分考虑该模式是否满足应用未来一段时间的需求。需要考虑应用在未来一段时间是否会发生变化，该模式是 否满足应用变化的需求。 2.1.1.3 读写分离集群模式 数据库服务器采用读写分离集群模式，可以满足对可用性有特殊需求的应用，具备以下特点： 1. 需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。 2. 需要冗余的存储设备。主机和备机都需要同样的存储空间，成本较高。 4 第2章 开发基础 3. 安装配置比较简单。KingbaseES 提供了一键部署的工具。 4. 具备容灾特性。当主机整个数据库系统不可用并短期内无法恢复时，可以把数据库系统切换到备机上，具备容 灾的功能。主机和备机的切换支持实时同步和异步同步，其中实时同步，可以支持金融级的数据保护。 5. 备机可以用作只读查询。备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。 根据该模式的特点，要求主机与备机在物理上要分开。为了实现容灾的特性，需要在物理上分割主机和备机。 2.1.1.4 数据库运行模式选择 在设计数据库时必须考虑系统的可用性、业务连续性，针对系统所能容忍的最大业务中断时间（RTO）和最大数 据丢失数量（RPO）需求，采用不同的数据库部署模式： 1. 系统不能中断且不允许数据丢失的业务，建议数据库采用实时同步的集群模式，数据库单台设备故障时对业务 没有影响，并考虑灾备系统的设计。 2. 对于允许以分钟级别中断，数据不能丢失的系统，建议数据库采用双机热备的集群模式，设备故障时通过 HA 技术切换到备用设备，保证系统的可用性，对重要的系统要考虑灾备的设计。 3. 对于允许以天为级别中断的业务系统，建议可采用双机热备模式，或单机。 4. 对非关键系统、开发环境、测试环境，建议采用 PC 服务器、冷备或单机的模式。 2.1.2 数据库环境规划 2.1.2.1 运行环境规划 根据用户需求在业务系统前期的实施规划上，需要做好详细的规划设计，包括主机、网络和存储环境规划等，要 将整个软硬件融为一体，充分考虑系统的安全性，可靠性，高可用性等因素，只有一个规划好的系统才能充分发挥其 优于单节点的优势，同时也为后期的运维管理提供方便。 在评估数据库服务器性能时，最困难的事情是如何把握准确度问题，到底考虑哪些因素等。理想情况下，应考虑 下列要素：交易的复杂性、交易率、数据读/写比例、并发连接数、并发交易数、数据库最大表的大小、性能度量的 目标。 本章节介绍了数据库运行的物理环境的配置规划。本章节包含以下内容： • 主机规划 • 网络规划 • 存储规划 2.1.2.1.1 主机规划 主机规划主要需考虑服务器在不同的用户数量下，系统的响应时间和吞吐量，并得出当前服务器的各种资源的利 用情况。在规划系统配置时要预留做系统管理时所消耗的资源，如在做备份、恢复、问题诊断、性能分析、系统维护 5 第2章 开发基础 时都会对系统资源带来额外的消耗，对重要资源要考虑为将来留下升级和可扩展的余地。 在进行服务器配置规划时，要注意以下几点： 1）CPU：要考虑业务高峰时处理器的能力，并适当保留一些缓冲，确保在业务增长时，系统有扩展的余 地。 2）内存：要为运行在此服务器的所有应用软件考虑内存，所需要的内存主要依赖于用户数、应用程序类 型、进程的方式、和应用程序处理的数据量决定。 3）磁盘：评估业务的实际用户的数据量，以此推算出磁盘的最小个数，不要忘记选择备份设备（如磁带 机）。 4）IO 槽：尽量保留更多的 IO 槽，防止将来插更多的 PCI 卡。 5）网络：选择合适的网卡，保证网络不是系统的瓶颈。 数据库服务器优先考虑使用小型机和 UNIX 操作系统，但是当前用户大都选择 PC 服务器和 Linux 操作系统， 推荐的数据库服务器配置如下： 处理器：各类 CPU 的主流 PC 服务器，核数尽量多一些。 内存：容量不低于 128G。 存储：双盘，单盘容量不低于 500GB，支持 RAID。 网卡和 HBA 卡：2 个千兆网口，2 个万兆网口。网卡进行绑定。 操作系统：Linux。 2.1.2.1.2 网络规划 网络规划的基本原则就是将业务生产网络、存储网络和管理网络分开，推荐在生产网络使用万兆网，存储使用 SAN 存储网络，管理网可使用千兆网。 对于数据库单机模式、以及集群模式的生产环境，符合网络规划的基本原则即可。 对集群而言，分为数据网络和业务网络，内部数据传输一般和业务接入的网络分开。符合业务数据分离原则（但 不强制）。 • 公有 IP 和虚拟 IP KingbaseES 集群模式下主节点有一个虚拟 IP，简称 VIP，与公网 PUBLIC IP 在同一个网段。VIP 附属在 public 网口接口。 VIP 和 PUBLIC IP 最主要的不同之处在于：VIP 是浮动的，而 PUBLIC IP 是固定的。在所有节点都正常运行 时，VIP 会被分配到主节点的 public NIC 上；在 linux 下 ifconfig 查看，public 网卡上是 2 个 IP 地址；如果一个主 节点宕机，这个节点的 VIP 会被转移到成功提升为主的的节点上。 PUBLIC IP 地址是一个双网卡绑定的公有地址，用户通过交换机来进行访问。 • 业务 IP 和数据 IP 6 第2章 开发基础 KingbaseES 集群模式在有业务网络和数据网络分开的环境下，数据 IP 指数据库内部数据传输，通信所在的 IP 网络；业务 IP 主要是用来应用访问，VIP 和与业务 IP 的 PUBLIC IP 在一起。VIP 随着数据库的高可用而做转移。 2.1.2.1.3 存储规划 数据库一般使用磁盘阵列（RAID）保存数据，使用磁盘阵列有两个优点：首先，盘阵可以提供一个具有容错能 力的 I/O 系统，当系统中某个磁盘驱动器出现故障时，可避免丢失数据，因此具有容错能力；其次，允许多个但磁 盘驱动器配置成为一个大的虚拟磁盘驱动器，从而方便管理，提高性能。 盘阵 RAID 方式分为 RAID0，RAID1，RAID10，RAID2，RAID3，RAID4，RAID5 等，其逻辑和物理组合方 式各有差异。 基于 KingbaseES 数据库配置 RAID 系统，有以下几种解决方案： 1. 最佳解决方案 对容错能力最好的解决方案就是最大限度地使用 RAID1 和 RAID10，规划部署时遵循以下原则： 1）对操作系统和 KingbaseES 程序使用 RAID1； 2）对数据库重做日志文件使用 RAID10，可以优化性能； 3）对归档日志文件使用 RAID10，既能保护数据，又不会影响性能； 4）对数据文件使用 RAID10，并使用多个磁盘驱动器以保证不超过单块盘的负载。 2. 较好的解决方案 对于容错能力，较好的解决方案是混合使用 RAID10 和 RAID5，遵循以下原则： 1）对操作系统和 KingbaseES 程序使用 RAID1； 2）对数据库 WAL 日志文件共享一个 RAID1 或 RAID10； 3）对归档日志文件可使用 RAID10 或 RAID5，这两种方式均可保护数据且不影响性能； 4）对数据文件使用 RAID10，并使用过个磁盘驱动器以保证不超过单个磁盘负载。混合使用 RAID10 和 RAID5 可实现很好的性能，容错能力也很高。 3. 经济型解决方案 对容错能力，此方案使用 RAID1 和 RAID5，遵循以下原则： 1）对操作系统和 KingbaseES 程序使用 RAID1; 2）对重做日志文件使用 RAID1; 3）对归档日志文件使用 RAID10 或 RAID5; 4）对数据文件使用 RAID5。 此方案提供的系统性能比前两个方案要低，其价格是优势。 在进行存储规划时，需要特别注意： 7 第2章 开发基础 1）若系统没有使用容错功能，那么只要有一块磁盘驱动器发生故障，就必须恢复整个数据库； 2）容错磁盘不能替代数据库备份策略； 3）系统可能会发生变化，要紧跟用户的需求； 4）以上的解决方案都要考虑磁盘驱动器的数量，应具备足够数量的磁盘驱动器以防系统瓶颈的产生； 5）对于硬件的保护不仅仅是磁盘驱动器，还包括冗余电源，磁盘控制卡和风扇等等，如果存储系统没有 冗余机制，则这其中任何一项故障都会导致业务系统停机和丢失数据等损失。 2.1.2.2 数据库安装部署规划 2.1.2.2.1 软件安装路径 KingbaseES 软件的安装目录要和系统盘分开，建立单独的文件系统来安装数据库软件，且文件系统的 mount 点 不要直接建立在根目录下。例如，规划安装路径为：/opt/Kingbase/ES/V9/ 在 Unix 下，KingbaseES 数据库系统的帐号和组的权限也要作相应设置。创建数据库管理员组 (DBA) 并分配 root 和 KingbaseES 软件拥有者的用户 ID 给这个组。DBA 能执行的程序只有“710”权限。在安装过程中 SQL*DBA 系统权限命令被自动分配给 DBA 组。 2.1.2.2.2 数据库实例配置 对于 KingbaseES 各个版本的参数优化，随版本的区别而有所不同。但万变不离其宗，大部分的参数设置原理是 相同的，其优化原则也一致。 数据库字符集的确定非常重要，如果选择不当，会给业务数据的保存带来麻烦。如在现实中，有的汉字保存到数 据库时发生乱码，从而使客户的信息不能正常保存和显示。 数据库字符集在系统设计开发阶段就应当确定。在数据库系统上线后，再更改数据库字符集，代价会非常大。因 为不同的字符集设置之间，存在转换操作，如果不兼容，只能逐条转换。一般选择原则是要适中，满足当前和未来业 务数据的保存。既不要选择太大，也不要过小。现有支持汉字的字符集包括： GBK：此字符包含了大部需要的汉字字符，由于目前已经有了新的国标 GB18030-2005，而 GB18030 不 是 GBK 的严格超集，部分字符编码有改动，如果要升级到 GB18030 只能是将库导出导入重建，因此不 建议使用该字符集。 GB18030：此字符集是最新国家标准字符集，包含字符较全，如果系统将来不会有其他国家字符被使用， 可以使用此字符集。 UTF8：此字符集是 KingbaseES 推荐使用的字符集，对汉字支持也较好。如果系统可能会有其他国家字 符录入，建议选用此字符集。 对于其他字符集的选择，请查询应用安装手册。 另外，是否需要大小写敏感 enable-ci，页面大小 block-size，以及是否需要指定加密引擎，都是实例级的配置， 需要事前确定。 大小写是否敏感，影响表的查找，以及 text 之类值的比较。 8 第2章 开发基础 页面大小，默认是 8k，大页（例如 32k），适合每个原则的大小超过 8k，可以更合理的使用每个页的空间，减 少 toast 数据，从而提高性能。 2.1.2.2.3 数据库参数设置 数据库参数的设置与业务应用密不可分。不同的业务类型，决定着关键参数的值，同时合理参数值也就决定着数 据库系统的性能优劣。不仅如此，业务数据的需求也决定着数据库系统的设置。对于实例创建成功而不可更改的或者 修改代价比较大的参数，必须慎重考虑，在充分调研各个方面的需求后，方可确定参数值，以满足业务的正常健康的 运行。具体参见《数据库参考手册》。 2.1.2.2.4 表空间设计 2.1.2.2.4.1 业务数据量估算 估算所有业务对象下的所有表的尺寸。 数据量估算的前提： 1）数据库的物理表结构已经确定，并且设计已冻结。 2）用户方提供较为准确的估算依据，例如业务变动的频率、数据需要保存的周期等。 该表是一个示例，可根据业务的不同有所变化。 序号 表名 增长量（/小时/天/周） 增长量（/月/半年） 年数据量 数据库生命周期内的总计 1 2 3 4 5 合计 新上线或扩容时，对所申请的存储不得全部一次性挂上，应该预留出 30％左右的空间用于追加，以防止出现业 务发展和预期不一致时剩余空间多寡不均，调整困难。 操作系统上应该预先做好几个合适大小的逻辑卷备用，包括用于 sys_global、sys_default 等表空间的小尺寸的 逻辑卷和用于数据表空间、索引表空间的大尺寸逻辑卷，这些逻辑卷要求在所有节点上都可见，避免单纯因为数据库 增加数据文件而需要重新同步。 9 第2章 开发基础 2.1.2.2.4.2 表空间使用规则 目前多数数据库系统采用数据“大集中”原则，对数据库的性能要求较高。这就要求对数据库进行必要的优化配 置。在表空间的配置上，应遵循以下原则： 1. 最小化磁盘 I/O。 2. 在不同的物理磁盘设备上，分配数据。 3. 尽可能使用本地管理表空间。 多数系统采用 RAID1+0 或 RAID0+1，该技术很好的解决了最小化磁盘 I/O。基本不必考虑在不同的物理磁盘 设备上，分配数据的原则。 2.1.2.2.4.3 表空间的类型 为了更好的管理表空间，同时提高 KingbaseES 数据库系统性能，针对数据的业务功能，进一步对其加以分类。 因此 KingbaseES 数据库的表空间划分为基本表空间和应用表空间。如下表： 1. 基本表空间：是指 KingbaseES 数据库系统为其自身运行而使用的表空间。 2. 应用表空间：是指业务应用数据保存在此类表空间中。它由 DBA 或相关的数据库规划设计人员创建和规划。 表空间类别 表空间名称 存储内容 说明 数据表空间 TABLES 表空间 存储小数据表公用业务数据 由 DBA 设定—应用表空间 数据表空间 TABLES PARTITION 表空间 存储巨型表数据 由 DBA 设定—应用表空间 数据表空间 INDEXS 表空间 存储小数据表的索引 由 DBA 设定—应用表空间 2.1.2.2.4.4 表空间相关概念 在规范表空间存储参数之前有必要澄清关于数据块（data block）、文件之间的概念及其之间的关系。 数据块（data block）：KingbaseES 存储数据最细粒度是数据块，它是操作系统文件块的整数倍（有时也称逻辑 块，KingbaseES 块，或页）。一个数据块大小有 4k、8k、32k 等，并以此单位大小保存在物理磁盘中。 文件（file）：一个表/索引，对应着一个或者多个文件。每个文件由多个数据块组成。 表空间（tablespace）：每个表空间对应着一个目录。可以通过软连接的方式，扩展表空间在其他逻辑卷的大 小。 2.1.2.2.4.5 表空间的分配原则 对于小规模数据库，I/O 不是主要的性能瓶颈，可以不考虑物理分布的问题。 对于中规模数据库及大规模数据库，应当考虑： 尽可能把应用数据表空间、应用的索引表空间以及相应得分区表空间分布在独立的物理卷上。 10 第2章 开发基础 2.1.2.2.5 表和索引设计 2.1.2.2.5.1 表的参数设置 1. fillfactor 存储参数 fillfactor ，在 10 和 100 之间。 对于主要操作为 insert 的数据对象，可以考虑设定较大的 fillfactor 。 若对于有大量 update 操作的数据表，fillfactor 可以设置的更小些。 2. max_parallel_workers 表扫描中可以使用的并行进程个数。 2.1.2.2.5.2 索引的使用原则 1. 基本使用原则 1) 当查询的行数占整个表总行数的比例 <=5% 时，建立 b 树索引效果比较明显。（普通索引就是 b 树索 引） 2) 在频繁进行排序或分组（即进行 group By 或 order By 操作）的列上建立索引。 3) 在频繁使用 distinct 关键字进行查询的列上面建立索引。 4) 进行表连接时，在连接字段上面建立索引。 5) 对于键值频繁更新的索引，需要定期的进行重建。 2. 复合索引的使用原则 一般情况下，对于经常同时使用多个数据项进行查询的对象可以创建复合索引，使用复合索引时特别要考 虑的各个数据项在索引中的相对位置。 一般情况下，把最常用的列放在第一位而不太常用的列放在稍后面的位置。 在复合索引创建后，要求用户在查询数据的时候也遵循同样的方式来使用索引。 虽然目前的 KingbaseES 数据库版本能够使用复合索引中的后面的数据项，但是按序使用复合索引可以带 来较高的效率。 3. 函数索引的使用原则 1) 对于经常进行运算比较的一些列，可以考虑建立函数索引，但是也可以通过在表中使用原来的列的函数 形式来实现 2) 在 OLTP 系统中，一般情况下不建议使用函数索引。 11 第2章 2.1.3 开发基础 可扩展性设计 可扩展性是数据库应用程序对负载变化的适应。数据库应用程序的可扩展性越高，添加或更改功能就越容易，而 对现有功能的影响最小。为了最大限度地提高可扩展性，您必须提前设计应用架构，设计原则是允许在不对基础架构 进行重大更改的情况下进行功能增加。 负载是数据量、用户数量和其他相关因素的组合。应用要适应负载的增加，必须使用有效的基准测试策略、适当 的应用程序开发技术（如绑定变量）和合适的数据库架构特性。 2.1.4 安全设计 数据库安全设计涉及以下方面： • 用户管理 包括口令管理、角色和权限分配等。KingbaseES 的默认安装会建立很多缺省的用户名和密码，以及 对应的默认权限。所以设计应用架构，需要提前规划好应用的用户分配。 • 提前考虑项目是否需要通过等保分保测试 包括密码复杂度，审计，敏感数据保护等策略的明确方案，从而在系统进行性能和稳定性测试时， 同时考虑安全属性。特别是敏感数据保护，是采用函数加密，表空间透明加密，还是表加密。函数加 密，需要应用程序负责数据的加密和解密。表空间的透明加密，若数据库的内存较大，基本等于数据 量，那么采用透明加密是对性能影响最小的方案。 2.1.5 可移植性设计 PL/SQL 在操作系统和语言之间具有高度可移植性。大多数编程语言都可以调用 PL/SQL，并且 PL/SQL 可以 在支持 KingbaseES 数据库的不同平台上运行。如果您在一个平台上开发 PL/SQL 应用程序，可以直接移植到其他平 台上运行。 PL/SQL 存储过程提供了跨多个数据库的一些应用程序可移植性。尽管使用以给定供应商的语言编写的存储过程 在某种程度上似乎将您与该供应商联系在一起，但存储过程使应用程序逻辑具有可移植性。数据逻辑针对运行应用程 序的数据库进行了最佳编码。由于数据逻辑隐藏在存储过程中，您可以使用扩展功能来优化数据层。 当在数据库上开发和部署时，应用程序逻辑保存在该数据库上。如果将应用程序移动到另一个数据库，应用程序 逻辑可以独立于存储过程中的数据逻辑移动，从而简化了移植步骤。 2.1.6 可诊断性设计 KingbaseES 数据库包括一个故障诊断基础架构，用于预防、检测、诊断和解决数据库问题。问题包括代码错 误、元数据损坏和客户数据损坏等。可诊断性基础架构的目标是主动检测问题，在检测到问题后限制损坏和中断，减 少诊断和解决问题所需的时间。 12 第2章 2.1.7 开发基础 业务系统设计 业务系统处理数据的特点决定了设计人员规划和创建什么样的数据库，通常来说，业务分为两类：在线事务处理 系统（OLTP）和在线分析系统（OLAP）或者 DSS（决策支持系统）。这两类系统在数据库的设计上是不同的，比 如 OLTP 系统强调数据库的内存效率，强调各种内存指标的命中率，强调绑定变量，强调并发操作：而 OLAP 系统 则强调数据分析，强调 SQL 执行时长，强调磁盘 I/O，强调分区等。 2.1.7.1 在线事务处理系统 通常 OLTP（在线事务处理系统）的用户并发数很多，但只对数据库做很小的操作，数据库侧重于对用户操作的 快速响应，这是对数据库最重要的性能要求。 对于一个 OLTP 系统来说，数据库内存设计非常重要，如果数据都可以在内存中处理，那么数据库的性能会提 高很多。 内存的设计通常是通过调整 KingbaseES 和内存相关的初始化参数来实现的，比较重要的几个是内存相关的参 数，包括共享内存的大小（shared_buffers），session 级内存大小（work_mem，maintenance_work_mem 等）等， 这些参数对一个 OLTP 系统是非常重要的。OLTP 系统是一个数据块更新非常频繁，SQL 语句提交非常频繁的一个 系统。对于数据块来说，应尽可能让数据块保存在内存当中，对于 SQL 来说，尽可能使用变量绑定技术来达到 SQL 的重用，减少物理 I/O 和重复的 SQL 解析，能极大的改善系统的性能。 2.1.7.2 在线分析系统 OLAP（在线分析系统）数据库在内存上可优化的余地很小，但提升 CPU 处理速度和磁盘 I/O 速度是最直接的 提高数据库性能的方式。实际上，用户对 OLAP 系统性能的期望远远没有对 OLTP 性能的期望那么高。 对于 OLAP 系统，SQL 的优化显得非常重要，如果一张表中只有几千行数据，无论执行全表扫描或是使用索 引，对用户来说差异都很小，几乎感觉不出来，但是当数据量提升到几亿或者几十亿或者更多的时候，全表扫描，索 引可能导致极大的性能差异，因此 SQL 语句的优化显得重要起来。 分区技术在 OLAP 数据库中很重要，这种重要主要是体现在数据管理上，比如数据加载，可以通过分区交换的 方式实现，备份可以通过备份分区表空间，删除数据可以通过分区进行删除。 2.1.7.3 数据库的规模 对于数据库的规模，仅从数据量来衡量其规模的大小。因为数据量的规模是反映数据库规模的主要指标。具体如 下： 1. 数据库业务数据量小于 100GB 属小规模数据库 2. 数据库业务数据量大于 100GB 小于 1TB 以内属中大规模数据库 3. 数据库业务数据量大于 1TB 属大规模数据库 13 第2章 2.1.8 开发基础 特殊场景特性 2.1.8.1 SQL 分析函数 SQL 分析函数基于一组行计算聚合值。SQL 分析函数与聚合函数的不同之处在于它为每个组返回多行。对于每 一行，定义了一个行窗口，确定用于对当前行执行计算的行范围。SQL 分析函数为面向集合的 SQL 提供了在结果集 上使用数组语义的功能。它们可以对原本繁琐复杂的逻辑进行简化、或者直接编码, 提高了处理效率，并且可以在数 据库层进行 SQL 调优。 KingbaseES 可用的分析函数如下所示，函数详细信息和用法请参考 SQL 语言。 AVG CORR COUNT COVAR_POP COVAR_SAMP CUME_DIST DENSE_RANK FIRST FIRST_VALUE LAG LAST LAST_VALUE LEAD LISTAGG MAX MIN NTH_VALUE NTILE PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC RANK RATIO_TO_REPORT ROW_NUMBER STDDEV STDDEV_POP STDDEV_SAMP SUM VAR_POP VAR_SAMP VARIANCE 14 第2章 2.1.8.2 开发基础 物化视图 物化视图是已存为架构对象的查询结果，用于汇总、计算、复制和分发数据。用户可以查询物化视图，而不是单 独聚合详细信息记录。物化视图使用于复制数据和移动计算等场景。 虽然物化视图需要时间来创建和更新，并占用一定的磁盘空间，但可以高效的提升查询速度。在这些方面，物化 视图类似于索引，它们被称为“数据仓库的索引”。与索引不同，物化视图可以直接查询，有时可以使用 DML 语句 进行更新。 2.1.8.3 分区 分区技术是为解决数据库中巨大的表或索引读写速度过慢而提出的解决方案。分区技术是利用物理上和逻辑上对 数据进行分割来提高处理速度的。 同时，合理的分区也提高了数据库数据的可管理性。 Partition 表和索引考虑使用分区的条件： 1. 数据损坏的故障隔离； 2. 支持在线增加、删除； 3. 特定分区上的批处理； 4. 按分区备份； 5. 维护时可访问正常分区； 6. 恢复最关键的数据分区。 2.1.8.3.1 逻辑分割 根据分区策略，一张表的数据可以逻辑上分布于多个分区、子分区中，对数据的查询如果利用分区策略就可以缩 小访问的范围，在一定量级上提高查询速度。 同样，对于索引分区也是一样的，从逻辑上分割表，缩小处理中的范围，能够极大地提高 KingbaseES 本身的处 理速度。 2.1.8.3.2 物理分割 对于逻辑分割后的分区，可以通过策略分布到不同的表空间中，从而分布到不同的数据文件中，而数据文件又可 以分配到不同的存储介质空间中，这样就可以充分利用操作系统的并行访问，同时也利用存储介质的并行访问，极大 地同时提高写入和读取速度。 同样，对于索引分区也是一样的机制。 15 第2章 开发基础 2.1.8.3.3 数据分区的优缺点 数据库进行分区，一般有以下优点： 1. 分区允许数据库管理员进行数据管理操作：数据装载，索引的创建和重建，在分区级别的备份与恢复。因此可 节省数据库管理员大量的操作时间。 2. 分区可大幅度减少因维护引起的业务停止时间。分区的独立性使数据库管理员对同一表或索引的各个分区进行 并发的维护管理操作；也可对分区表并行的执行 SELECT 和 DML 操作。 3. 分区可增加数据库的可用性。减少维护窗口，恢复次数以及系统失效的影响。 4. 分区不要求修改任何应用程序。如，数据库管理员可把非分区表转换为分区表，而不必修改或重写 SELECT 语 句或 DML 语句。同时也不必重写前端的应用程序代码。 但是数据分区带来了数据库创建方面的复杂度。即维护操作原来是面向一个表或索引对象，现在则要面向几十至 上百个分区。所以需要提前计算成本，如果后台工作复杂度的加大换来了前台数据操作的效率提高，也是值得的。 2.1.8.3.4 KingbaseES 分区技术 KingbaseES 支持的分区类型如下所示： 1. 范围分区 范围分区是对某个可度量的字段在可以预见的范围内进行划分的分区方式，例如：日期字段。 2. list 分区 list 分区是对某个可列举确定值的字段按照不同值进行划分的分区方式，例如：区县代码字段。 3. 散列分区 散列分区是对某个离散性很大的字段按照根据散列算法计算出的散列值进行分区，例如：证件号码。 4. 间隔分区 间隔分区是对分区键按照指定间隔，服务器自动创建新的分区。 5. 组合范围-散列分区 组合范围-枚举值分区是按照范围做主分区，在主分区的基础上再次进行按照枚举值分区的组合分区。例如：日 期-区县。 6. 组合枚举-散列分区 组合范围-散列分区是按照范围做主分区，在主分区的基础上再次进行按照散列分区的组合分区。例如：日期-证 件号码。 2.1.8.3.5 分区索引 KingbaseES 支持分区索引包括全局索引和本地索引，如下所示： • 全局索引 (GLOBAL index ) 16 第2章 开发基础 一个索引可以指向多个分区的数据，对单个或少量记录的访问比较有效，但管理维护上有额外成本。 主要特点如下： 1. 指向任何一个分区中的记录 2. 表可以被分区或不分区 3. 分区键值可以是有前缀后没有前缀 • 本地索引 (LOCAL index) 每个表分区都有一个索引分区，而且只索引该表分区的数据。一个给定索引分区中的所有条目都指向 一个表分区，表分区中的所有行都表示在一个索引分区中。适合并行索引扫描，但对少量记录查询相 对效率不高。 1. 每个本地索引分区只包含本分区的记录 2. 二种类型的本地索引 前缀 (prefixed)：唯一或非唯一列，可有效的使用分区消除，适于索引并行查询，适用于 OLTP 非前缀 (non-prefixed)：适合于索引并行查询，可有效的使用分区消除，唯一索引受限，适用于 DSS 3. 分区键值可以与索引键值不同 2.1.8.3.6 分区使用建议 如果数据按照某个（某些）值分区，那么 range 分区就最合适，比如按照“销售定额”、“财务年度”、“月 份”等等，在这种情况下，range 分区可以利用分区消除，这包括应用中使用“＝”、“>”、“<”等作为条件。 如果不能找到其他合适的自然条件进行分区，那么 HASH 分区就比较合适，这里建议选择唯一列或者几乎唯一 的列作为分区键值。这种情况下，分区数据是均匀的，使用分区键值“＝”或者 in(value1,value2…) 时，hash 分区 可以利用分区消除，但是使用其他条件时，hash 无法利用分区消除。 如果分区后，每个分区的数据量还是很大，建议使用组合分区，例如，首先按照自然条件做 range 分区，之后， 对分区再进行分区。 2.2 数据库应用程序的连接策略 数据库连接是客户端进程和数据库实例之间的物理通信路径。数据库会话是数据库后台的一个服务进程，它表示 当前登录到数据库的用户的状态。从用户通过数据库进行身份验证开始，会话一直持续到用户断开连接或退出数据库 应用程序为止。一个连接对应一个会话。 2.2.1 连接池设计准则 连接池是应用程序可以使用的 KingbaseES 数据库连接的缓存。 17 第2章 开发基础 在运行时，应用程序从池中请求连接。如果池中包含一个可以满足请求的连接，那么它将返回给应用程序。应用 程序使用该连接在数据库上执行工作，工作完成后，释放连接，将该连接返回给池，释放的连接随后可用于下一个连 接请求。 在静态连接池中，连接池的连接数是固定的，无法创建更多的连接来满足需求。因此，如果连接池池找不到空闲 连接来满足新的应用程序请求，则请求将排队或返回错误。但是，在动态连接池中，该池创建一个新的连接，然后将 其返回给应用程序。从理论上讲，动态连接池能够增加或减少连接池中连接的数量，从而节省系统资源，以免在维再 维护不必要的连接时丢失资源。然而，在实践中，动态连接池策略允许潜在的连接风暴和过度的订阅问题。 • 连接风暴 连接风暴是一种竞争状态，在这种情况下，应用服务器发起的连接请求数量不断增加，但数据库服务 器 CPU 无法立即调度它们，这将导致应用服务器创建更多的连接。 在一次连接风暴中，数据库连接的数量可以在不到一分钟的时间内从百个猛增到数千个。 动态连接池特别容易发生连接风暴。随着连接请求数量的增加，相对于 CPU 核的数量，数据库服务 器会超额订阅。在任何给定的时间，一个 CPU 核心上只能运行一个进程。因此，如果服务器上存在 32 个核，那么一次只能有 32 个进程在工作。如果应用服务器创建数百或数千个连接，那么 CPU 就 会因为试图跟上系统上争夺时间的进程数量而变得繁忙。 在数据库内部，等待活动随着活动会话数量的增加而增加。您可以通过查看 KSH 或 KWR 报告中的 等待事件来观察这个活动。典型的等待事件包括队列的锁、行缓存对象、锁空闲、enq：TX 索引争用 和缓冲区繁忙等待。随着等待事件的增加，事务吞吐量降低，因为会话无法执行工作。由于服务器计 算机超额使用，监控工具进程必须在 CPU 上争取时间。在最极端的情况下，使用键盘就变成了不可 能，使调试变得困难。 • 防止连接风暴的准则：使用静态池 我们建议您使用静态连接池而不是动态连接池。在对连接风暴多年诊断的过程中，我们发现，动态连 接池经常使用太多的进程来完成必要的工作负载。一个普遍的误区是，动态连接池根据需要创建连 接，并在不需要时减少它们。实际上，当连接池耗尽时，应用服务器会允许数据库连接池的大小迅速 增加。当所有会话都处于活动状态时，会导致性能问题。 由于动态连接池会迅速破坏系统的稳定，我们建议您使用静态连接池而不是动态连接池。 减少连接的数量可以减少了 CPU 的压力，从而获得更快的响应时间和更高的吞吐量，这一结果似乎是自相矛盾 的，性能提高的原因如下： • 较少的连接意味着数据库中与争用相关的等待事件更少。在减少连接之后，以前在缓存区锁定和缓存区分配上 消耗的 CPU 时间周期可以更多的花费在处理数据库事务上。 • 随着连接数量的减少，连接在 CPU 上的调度时间可能会延长。因此，所有与这些进程相关的内存页都驻留在 CPU 缓存中。它们在调度上变得越来越高效，在内存中的停留的次数也越来越少。 根据性能调优经验，我们建议用户对系统 CPU 的利用率为 10%-90%，并且在数据库服务器上平均每个 CPU 核 心不超过 10 个进程，连接的数量应根据 CPU 的内核数而不是 CPU 内核线程的数量。例如，假设一台服务器有 2 个 CPU，每个 CPU 有 18 个核心，每个 CPU 核心有 2 个线程，根据性能调优指南，应用程序可以有 36 到 360 个到数 据库实例的连接。 18 第2章 2.2.2 开发基础 登录策略 所有数据库开发人员面临的一个问题是应用程序如何以及何时登录到数据库来启动事务。 在此次优设计中，数据库应用程序对每个 SQL 请求执行以下步骤： 1. 登录数据库. 2. 发出 SQL 请求，例如插入或更新语句。 3. 退出数据库。 使用登录/注销策略的应用程序可能满足功能需求。此外，当每秒事务数较低时，它们可能会执行的很好。但 是，登录和退出数据库是一个非常占用数据库资源的操作。我们发现，使用此类算法的应用程序不能很好地扩展，并 且可能导致严重的性能问题，特别是当与动态连接池一起使用时，登录/注销策略通常不使用连接池。 如果应用程序使用登录/注销设计，如果 DBA 和开发人员没有意识到问题的根源，那么第一个症状可能是数据库 吞吐量低和不稳定、过高的响应时间。对数据库的诊断调查可能会显示，当资源争用较低时，活动的会话相对较少。 性能欠佳的迹象是，每秒的登录数接近每秒的事务数。当使用每个事务登录/退出策略时，数据库实例和操作系 统在幕后执行大量工作，以创建新进程、数据库连接和相关内的存区域。这些步骤中的许多都是序列化的，导致低 CPU 利用率与低事务吞吐量。 出于上述原因，我们强烈建议，对于任何必须扩展到支持大量事务的应用程序，不要采用登录/注销设计。 2.2.3 防止程序会话泄漏 当程序失去连接，但其会话在数据库实例中仍然处于活动状态时，就会发生会话泄漏。泄漏的会话以编程方式丢 失到应用程序中。 优化设计的应用程序可以防止会话泄漏。通常，会话泄漏是由于应用程序捕获的异常而发生的。如果应用程序不 能正确处理异常，则它可能在不执行提交或回滚的情况下终止连接，从而泄漏会话。 会话泄漏可能会导致严重的数据库性能和数据完整性问题。典型的问题有以下几种形式： • 连接池排空 • 锁泄漏 • 逻辑混乱 • 连接池排空 设计缺陷会导致连接池排空。 例如，假设应用程序设计缺陷导致它一致地泄漏会话。即使泄漏率很低，动态连接池也会导致越来越多的会话 在编程上变得无法使用。 其效果是减少可用的连接池，并导致剩余的连接无法跟上工作负载。不可用会话的数量不断的攀升，直到池中 没有可用的连接。 19 第2章 开发基础 • 锁泄漏 锁泄漏通常是会话泄漏的副作用。 例如，正在进行批处理更新的泄漏会话可能持有表中多行的锁。如果泄漏的会话持有锁，那么希望获取锁的会 话将在泄漏的会话后面形成一个队列。 持有该锁的程序正在等待来自客户端的交互以释放该锁，但由于连接以编程方式丢失了，消息将不会被发送。 因此，数据库无法提交或回滚会话中任何活动的事务。 • 逻辑混乱 泄漏的会话可能包含对数据库未提交的更改。例如，当数据库连接意外释放时，事务正在工作中。 这种情况可能导致以下问题： – 应用程序向 UI 报告一个错误。在这种情况下，用户可能会抱怨他们失去了工作数据，例如商务订单或航 班时间表。 – 即使没有发生提交或回滚，UI 也会收到提交消息。这是最糟糕的情况，因为后续事务可能同时提交自己的 工作和泄漏会话中一半的事务。在这种情况下，数据库在逻辑上已经损坏。 • 查看会话泄漏 会话泄漏是由于应用程序或应用服务中的问题而发生的，无法在数据库中解决。这个问题需要在应用程序或应 用服务器中解决。检查会话泄漏的一种简单方法是修改连接池以使用一个数据库连接测试应用程序。只使用一 个连接进行测试可以更容易地在应用程序中找到问题的根本原因。 2.3 安全 2.3.1 用户设计 2.3.1.1 用户权限管理 业务功能的安全分配是指开发团队定义的用户、角色、特权，它是面向应用程序和开发的。数据库用户安全分配 往往取自前台应用设计开发团队的交付生产时的定义。这种安全定义了用户、角色、系统特权、对象特权分配等等。 它往往是面向开发的，没有细致考虑用户权限的控制。在数据库系统上线时，才发现有不妥之处。而这种用户安全分 配多数情况下不能修改，否则对前台应用造成运行错误。 但在交付生产时，投产方和用户方必须对其安全性进行审计。因为这时提供的用户安全往往是面向开发的，而不 是面向末端用户的。主要检查一下几个方面： 1. 每个业务用户不得授予 DBA 角色。 2. 取消一些系统特权。但取消之前必须征询开发者的意见，否则可能对前台应用运行带来不可预测的错误。 3. 坚持最小化特权原则。 20 第2章 开发基础 对象（如: 表、索引、触发器、过程等）管理权限归数据中心，应用（创建临时表权限）权限归项目组。 对于一个软件项目，在应用系统开发过程中，就开始对数据库用户权限进行严格的控制。即按照该系统未来生产 时的方式进行分配，尽管此时数据库还处在开发服务器之中，尽管给开发项目的控制带来更多的工作，但数据库的安 全性大大提高了。 对数据库用户（user）的授权，应通过数据库角色（role）进行分配。而不要把对象特权和系统特权直接授权给 数据库用户。 2.3.1.2 用户安全实现 KingbaseES 数据库是通过权限（Privilege）这个概念来实现数据安全的。所谓权限指用一种指定的方式访问 数据库数据对象的一个许可，如查询一个数据表的许可等。这个特权能够被授予某个实体，因此这个授予实体权限 （privilege）的过程，称之为“授权”（Grant）。 涉及 KingbaseES 数据库系统安全的实体有两个，分别是系统权限（System Privileges）和对象权限（Object Privileges）。 1. 系统权限 系统权限是指登录到 KingbaseES 数据库系统的用户，执行数据库系统级别的某种操作或者是某一数据库对象 的创建、修改、删除。在 KingbaseES 数据库系统中有一系列的系统内置预定义特权，系统用这些特权去控制 数据的安全。 不得授予普通用户额外的全局权限，如 select any/delete any/execute any 等，应用有特殊需求的除外。 2. 对象权限 对象权限是指登录到 KingbaseES 数据库系统的用户，有权执行数据库对象级别的某种操作。例如表的 INSERT，DELETE，UPDATE 操作等。同样，在 KingbaseES 数据库系统中有一系列的对象内置预定义权限， 系统用这些特权去控制数据的安全。 由于 KingbaseES 数据库系统业务处理的复杂性，对 KingbaseES 数据库的系统权限和对象权限的分配也就变得 十分复杂。因此，为了方便管理系统权限和对象权限，需要引入角色这个基本概念。 所谓角色是指系统权限和对象权限的集合。通过对角色的管理，使得 KingbaseES 数据库的系统权限和对象权限 管理变得更加方便和容易。 基于角色的安全管理主要有以下几点优势： 1. 减少授权工作量：可以通过授权给与一组用户相关联的角色，再由该角色授权给该用户组的成员用户。 2. 动态权限管理：如果授权给某个用户的权限需要改变，只须修改相关角色的授权，那么与这个角色相关的用户 的权限会自动改变，不须修改授权给用户特权。 3. 设置权限的可用性：当某个被授予用户的角色，需要取消，只须对相应的角色设置禁用（DISABLED）。因 此，在任何特定的情况下，都可对用户的授权进行必要的控制。 4. 应用程序级的设置可用性：前台应用程序在试图以某个数据库用户的身份与后台数据库相连接时，可以对角色 设置可用性。这种做法可以把非应用程序例如 ksql，屏蔽在数据库系统之外，以保证数据库的安全。 21 第2章 开发基础 角色可以根据业务的需求自由定义，系统权限和对象权限可以授权给角色，角色也可授权给另外的角色，角色也 可授权给用户。基于上面描述的角色安全管理的优点和特点，KingbaseES 数据库系统选择角色来实施数据库用户的 授权管理，并根据 KingbaseES 的业务需求从不同的角度实现业务的权限分配。 根据需求，设置不同级别的角色，某一级别体现对某一项业务的特权。各角色级别之间或是子集关系，或是交集 关系；同一级别的角色之间，或是交集，或是互为独立集合的关系。随着对业务需求的增加或变化，不断增加、完善 访问控制的粒度，并坚持最小化特权原则。如下图： 1. 通过存储过程管理权限（stored procedures） 使用存储过程（stored procedures）来限制数据库的操作，客户端用户只需有权执行存储过程，并通过存储过程 来实现对数据库表的访问。因而就屏蔽了用户直接对数据库表的操作。 2. 通过视图（VIEWS）管理权限 通过视图（VIEWS）来控制 KingbaseES 数据库系统的安全。即只分配给用户查询视图的权限，而对基表（定 义视图的相关的数据表）则进行屏蔽，禁止对数据表的直接操作。 视图可以实现以下两种安全级别： 1. 使用视图可以限制对数据表中的特定的列的访问。 2. 使用视图可以限制对数据表中的特定的行的访问。 如：对于某一基表，要求只显示部分行，则可通过创建实体的 WHERE 子句来控制行的显示。 22 第2章 开发基础 用户类型及角色命名规范 2.3.1.3 可以用 SQL 语句 GRANT 来授予系统权限和角色给其它角色和用户。有 GRANT ANY ROLE 系统权限的任何 用户可以授予数据库里的任何角色。 下面的语句授予了对应 EMP 表所有列的 SELECT，INSERT 和 DELETE 的对象权限给用户 JFEE 和 TSMITH： SQL>GRANT SELECT, INSERT, DELETE ON EMP TO JFEE, TSMITH; 要授予只对应 EMP 表的 ENAME 列和 JOB 列的 INSERT 的对象权限给用户 JFEE 和 TSMITH，声明下面的 句子： GRANT INSERT (ENAME, JOB) ON EMP TO JFEE, TSMITH; 要把对应于 SALARY 视图的所有对象权限给用户 JFEE，要使用 ALL 关键字，例子如下所示： GRANT ALL ON SALARY TO JFEE; 把 ACCOUNTS 表的 ACCT_NO 列的 INSERT 权限授予给用户 S： GRANT INSERT (ACCT_NO) ON ACCOUNTS TO S; 除了 system 用户，其他 KingbaseES 默认用户都应该置为 expire 或 lock 状态，除非有特殊需求; 数据库所有活动状态用户的密码不得设置为已知的默认密码； 数据库用户的密码应该定期修改。 2.3.2 数据访问控制 可以通过数据脱敏、强访问控制和透明加密等功能控制用户对应用程序中的数据访问级别。 2.3.2.1 数据脱敏 数据脱敏功能可以让用户访问数据时对数据进行一定规则的屏蔽。对某些敏感信息通过脱敏规则进行数据的变 形，实现敏感隐私数据的可靠保护。KingbaseES 的数据脱敏支持以下策略： • 完整脱敏，对所有数据都进行脱敏处理 • 部分脱敏，对部分数据进行脱敏输出 • 随机脱敏，通过将整个值替换成随机值来实现数据脱敏 • 邮件数据脱敏，KingbaseES 支持邮件格式字符串专用脱敏函数 email_mask，将 email 字符数据最后一个符号. 之前的所有字符都将用符号 * 进行脱敏。 23 第2章 2.3.2.2 开发基础 标记和强访问控制 KingbaseES 支持标记和强制访问控制，保护用户数据，防止非法窃取。强制访问控制为所控制的主体和客体指 派安全标记，然后依据这些标记进行访问仲裁。并且，只有主体标记能支配客体标记时才允许主体访问。设置标记可 以在行级别保护数据库，并且对不同行设置不同的安全级别。 2.3.2.3 透明加密 透明存储加密能够加密存储在表和表空间中的敏感数据。加密数据后，当授权用户或应用程序访问此数据时，将 以透明方式解密此数据。KingbaseES 实现了数据在写到磁盘上时对其进行加密，当授权用户重新读取数据时再对其 进行解密。无需对应用程序进行修改，授权用户甚至不会注意到数据已经在存储介质上加密，加密解密过程对用户和 应用程序都是透明的。 2.3.3 数据库审计 KingbaseES 支持创建审计策略来审核数据库中的操作。并且将用户对数据库的所有操作自动记录下来放入审计 日志中，审计员（sao）可以通过对审计日志的分析，对潜在的威胁提前采取有效地措施加以防范。KingbaseES 数据 库提供了一套完整的审计机制，用来保证对数据库中的各种行为进行监控，进而为数据库的安全、可靠和有效提供有 力的保障。针对不同的应用系统，您可以创建不同的审计策略来满意业务的安全性需求。 2.4 PLSQL 的高级特性 本章介绍了 PL/SQL 高级特性，有关详细信息，请参阅其他章节或文档。 本章节包含以下内容： • PLSQL 数据类型 • 动态 SQL • 异常处理 • 批量绑定 也可以看看： PL/SQL Language Reference 了解 PL/SQL 的完整描述 2.4.1 PLSQL 数据类型 PL/SQL 数据类型包括 SQL 数据类型、附加标量数据类型和复合数据类型。您还可以定义复合数据类型以及标 量数据类型的子类型。 也可以看看： PL/SQL 数据类型 24 第2章 2.4.2 开发基础 动态 SQL 动态 SQL 是一种用于在运行时生成和运行 SQL 语句的编程方法。在编写诸如动态查询系统之类的通用且灵活的 程序时，在编写必须运行数据库定义语言 (DDL) 语句的程序时，或者当您在编译时不知道 SQL 语句的全文或编号， 或其输入和输出变量的数据类型时，它很有用。 也可以看看： PL/SQL 动态 SQL 2.4.3 异常处理 异常（PL/SQL 运行时错误）可能来自设计错误、编码错误、硬件故障和许多其他来源。您无法预料所有可能的 异常，但您可以编写异常处理程序，让您的程序在它们存在的情况下继续运行。 也可以看看： PL/SQL 语言参考 2.4.4 批量绑定 批量绑定将 PL/SQL 与 SQL 之间通信的性能开销降到最低，可以大大提高性能。 也可以看看： 批量绑定概述。 25 第3章 3 第 章 面向应用程序的 SQL 开发 面向应用程序的 SQL 开发 本章节包括的主题如下： • SQL 处理 • SQL 数据类型 • 正则表达式的应用 • 索引的使用 • 数据完整性的维护 3.1 SQL 处理 3.1.1 SQL 语句的处理 3.1.1.1 SQL 语句的处理过程 应用程序通过接口向数据库发送 SQL 语句，执行的过程分为以下步骤： 1. 打开或创建游标 程序接口调用游标来获取 SQL 语句。大多数应用程序隐式创建游标，预编译程序可以隐式或者显式创建游标。 2. 分析语句 应用程序进程将 SQL 语句发送到 KingbaseES 数据库，数据库分析器对 SQL 语句进行分析，检查语义、语法 等。如果解析过程中有错误，会返回报错信息给应用程序。 3. 确定 SQL 语句的类型 4. 定义变量 如果是查询语句，需定义变量，定义变量接收查询返回的结果。需要定义变量的数据类型和长度。 5. 绑定参数 26 第3章 面向应用程序的 SQL 开发 数据库需要 SQL 语句中变量的信息，通过绑定的参数获取。应用程序需要指定变量的位置即内存地址，每次数 据库运行 SQL 时，都会通过其地址获取变量的值。数据库将会执行数据类型转换，并为每个值指定数据库类型和长 度。 6. 执行 SQL 语句 数据库运行查询语句，不会锁定数据行，会进行表数据检索，最后返回结果集。如果执行的是 DML 语句，数据 库将产生行锁直到事务完成，来确保数据完整性。 7. 关闭游标 事务完成后，数据库关闭游标。 3.1.1.2 SQL 缓存区 KingbaseES 数据库会检测到应用程序发送相似的 SQL 语句。解析过的 SQL 信息会将缓存到 SQL 缓存区内，以 便后续再次利用。SQL 缓存区机制减少了数据库服务器的内存使用量，提高了系统吞吐量。 3.1.2 事务操作的分组 3.1.2.1 关于事务操作的分组 如何对事务中的操作进行分组是使用数据库编程接口的应用开发人员最关心的问题，在决定如何分组时，需要考 虑以下方面： • 定义事务在逻辑单元中完成工作，并且保证数据一致。 • 确保所有引用表的数据在事务开始和结束期间都处于一致状态。 • 确保每个事务只包括 SQL 语句或者 PL/SQL 程序，这些 SQL 语句或者 PL/SQL 程序对数据的修改一致。 3.1.2.2 提高事务性能 在设计和编写应用程序时，可以考虑以下方式来增强性能： • 如果不能使用单个 SQL 执行完一个事务，则可以使用 PL/SQL。但尽可能的少用 PL/SQL。 • 创建编写 SQL 语句的标准，以便可以合理利用共享 SQL 缓存区。 • 及时收集和更新表和索引对象的统计信息，优化器将更准确地为 SQL 语句生成最优计划。 • 通过在 SQL 表达式中使用用户编写的 PL/SQL 函数，提供用户程序的工作效率和查询效率。 27 第3章 3.1.3 面向应用程序的 SQL 开发 事务的只读 要确保事务级的读取一致性，可以指定事务为只读状态。只读事务中的查询只能看到事务开始之前的提交的修 改，保证查询结果在事务持续时间内是一致的。只读事务查询数据时，不需要产生表锁，其他事务可以查询和更新相 同的数据。只读事务的语句如下： SET TRANSACTION READ ONLY; 3.1.4 表的显式锁定 KingbaseES 数据库有默认的锁机制，来确保数据的并发性、完整性和读取的一致性。但您可以显式地锁定表来 实现这些机制。显式锁表通常用于以下场景： • 应用程序中的事务需要对资源进行独占访问，以便该事务不需要等待其他的事务操作。 • 应用程序需要事务读取一致性 KingbaseES 数据库可以通过以下语句来实现显式锁定表： • LOCK TABLE • SELECT ... FOR UPDATE • SET TRANSACTION READ ONLY OR ISOLATION LEVEL SERIALIZABLE 3.1.4.1 显式表级锁 LOCK TABLE 获得一个表级锁，必要时会等待任何冲突锁被释放。如果指定了 NOWAIT，LOCK TABLE 不 会等待以获得想要的锁。如果它不能立刻得到表锁，该命令会被中止并且发出一个错误。一旦获取到，该锁会被在当 前事务中一直持有（没有 UNLOCK TABLE 命令，锁总是在事务结束时被释放）。 当一个视图被锁定时，出现在该视图定义查询中的所有关系也将被使用同样的锁模式递归地锁住。 KingbaseES 中支持以下模式锁定表： • ACCESS SHARE • ROW SHARE • ROW EXCLUSIVE • SHARE UPDATE EXCLUSIVE • SHARE • SHARE ROW EXCLUSIVE • EXCLUSIVE • ACCESS EXCLUSIVE 28 第3章 面向应用程序的 SQL 开发 3.1.4.1.1 行共享和行独占模式锁定 ROW SHARE 和 ROW EXCLUSIVE 表锁模式提供最高程序的并发。在以下情况下，可以使用这种模式的表 锁： • 事务在更新表之前，必须阻止另一个事务获取该表的独占锁或者行锁 • 事务必须防止表被更改或删除，然后事务才能修改表数据 3.1.4.1.2 共享模式锁定 SHARE 模式的表锁定是非常严格的数据锁。在以下情况下，可以使用这种模式的表锁： • 事务只需要查询表，并且在事务持续时间内需要表数据一致 • 暂缓更新锁定表的其他事务，直到在该表上保留锁的事务都已提交或者回滚 • 其他事务可能会在同一张表上获取并发锁，这也为它们提供了事务级读取一致性的选项 3.1.4.1.3 共享行独占模式锁定 SHARE ROW EXCLUSIVE 模式，在以下情况下，可以使用这种模式的表锁： • 事务既需要指定表的事务级读取一致性，也需要更新锁定的表 • 不需要考虑其他事务是否获取显式行锁，但这可能会使锁定事务中的语句等待并产生死锁 • 只存在单个事务有此操作 3.1.4.1.4 独占模式锁定 EXCLUSIVE 模式，在以下情况下，可以使用这种模式的表锁： • 事务需要对锁定表进行即时更新。当事务持有独占表锁时，其他事务无法锁定表的数据行 • 事务可以确保表的事务级读取一致性，直到事务被提交或者回滚 • 不用担心低级别的数据并发性，会使独占表锁的事务排队等待按顺序更新表 3.1.4.2 显式行级锁 使用 SELECT FOR UPDATE 语句可以获取数据行锁，锁定数据行但不修改它。SELECT FOR UPDATE 语 句允许用户修改一个或者多个特定行字段，确保在交互式程序中只有一个用户能够更新数据行。SELECT FOR UPDATE 语句返回的结果集中每一行都被单独锁定，如果存在冲突，将会一直等待直到另一个事务释放冲突锁。 29 第3章 3.1.5 面向应用程序的 SQL 开发 事务控制表锁定 如果通过 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 语句来实现数据库控制表锁定，那么应 用程序的编程逻辑会更简单，同时控制权也会更少。SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 语 句可以保留 ANSI 可串行化，无需改变底层的锁协议。 3.1.6 事务的并发控制 默认情况下，数据库允许并发运行的事务对同一行数据进行增删改。当事务更改表时，在提交更新之前，这些更 新对其他并发允许的事务是不可见的。如果一个事务尝试修改其他事务已锁定的行，那它需要等待其他事务提交或者 回滚后才能修改成功。此外，一些应用程序需要可序列化的事务运行机制。可序列化事务在序列化模式下并发允许。 在序列化模式下，并发事务只能串行修改数据，否则会发生错误。 3.1.6.1 事务并发和隔离级别 由于一个事务可能包含多个 SQL 语句，SQL 语句是顺序执行的多个事务在一个数据库上并发执行，可能有如下 类型的问题： 表 3.1.1: 事务并发 事务并发 定义 脏读（Dirty Read） 事务 T1 更新了一个数据，并且在 T1 提交或者回滚前，另外一个事务 T2 读取了那个数据。如果 T1 这个时候回滚，那么 T2 就读取了一个 未提交的虚假的值，即“脏”数据。 不可重复读（Fuzzy Read） 事务 T1 读取了一个数据。这个时候另外一个事务 T2 修改或者删除了 这个数据并且提交了。如果 T1 尝试再读取一次这个数据，就会发现再 次读到的数据与之前不一致或不存在。 幻象读（Phantom Read） 事务 T1 读取了若干个满足某个查询条件的数据。这个时候事务 T2 创 建了一个数据恰好也满足 T1 的这个查询条件。如果这个时候 T1 再次 根据这个查询条件进行读取，它会发现会多出了部分数据。 很显然，高并发度带来了数据的不一致，这些情况一些是用户不愿意见到，一些则是无法接受的。用户需要在高 并发带来的高性能和数据的一致性之间做取舍。SQL92 标准提出了事务隔离级别，来解决上面的问题。如下表所示： 30 第3章 面向应用程序的 SQL 开发 表 3.1.2: 隔离级别 隔离级别 读脏数据 不可重复读 读幻象数据 读未提交 (Read uncommitted) 可能 可能 可能 读已提交 (Read committed) 不可能 可能 可能 可重复的读 (Repeatable read) 不可能 不可能 可能 可串行化 (Serializable) 不可能 不可能 不可能 四个隔离级别从上到下对事务执行的并发程度进行了不同程度的限制，更加严格的限制在带来更好的数据一致性 的同时，也会损失更多并发带来的高性能。 KingbaseES 向用户提供以下隔离级别： • READ COMMITTED 一个语句只能看到在它开始前提交的行。这是默认值。 • SERIALIZABLE 当前事务的所有语句只能看到这个事务中执行的第一个查询或者数据修改语句之前提交的行。 3.1.6.2 设置隔离级别 使用 SET TRANSACTION 命令设置当前事务的隔离级别。SET SESSION CHARACTERISTICS 设置一个会 话后续事务的默认事务特性。在个体事务中可以用 SET TRANSACTION 覆盖这些默认值。 3.1.6.3 读取和可串行化隔离级别 KingbaseES 向用户提供两种隔离级别: 读已提交 (Read committed) 和可串行化 (Serializable)。这两种隔离级别 都提供高度的一致性和并发性，减少数据争用。在实际的应用程序中可以利用这些机制。 • 读已提交 (Read committed) READ COMMITTED 是 KingbaseES 默认的事务隔离级别。在运行在该隔离级别的事务中，查询语 句只能看到该查询开始执行之前提交的数据，而不会看到任何未提交的数据或查询执行期间并发的其 它事务提交的数据，但是该查询可以看到本事务中查询之前执行的数据更新。在 READ COMMITTED 隔离级别下，对于事务 T1 中的更新和删除语句，与查询语句相同，只能看到语句开始执行时提 交的数据行，但是，这些数据行可能已经被同时并发的另一个事务 B 更新，在这种情况下，事务 T1 将等待事务 T2 回滚或提交。如果事务 T2 回滚，则事务 T1 在原来的数据上继续更新，如果事务 T2 提交了，将分两种情况，1）：如果事务 T2 删除了该行，则事务 T1 忽略该行，2）：如果事务 T2 对 该行进行了更新，则事务 T1 判断该行的新值是否还满足条件，如果满足条件，则事务 T1 在新数据 行上进行更新，如果不满足条件，则忽略该行。在 READ COMMITTED 隔离级别下，同一个事务 的不同查询看到的可能是不同的数据，因此会出现不可重复读的问题。这种隔离级别对于大多数的应 31 第3章 面向应用程序的 SQL 开发 用已经能够满足要求，但有些应用需要提供更加严格的数据一致性。为了避免同样的查询得到不同的 结果，在 READ COMMITTED 隔离级别下，应用程序在同一事务内应该尽量避免重复的查询。 • 可串行化 (Serializable) SERIALIZABLE 提供了更加严格的事务隔离级别，在该事务隔离级别下，事务并发执行，其结果与 某个串行执行顺序的结果完全相同。在运行在 SERIALIZABLE 隔离级别的事务中，查询语句只能看 到该事务开始执行之前已经提交的数据，而不会看到任何未提交的数据或事务执行期间并发的其它事 务提交的数据，但是该查询可以看到本事务中查询之前执行的数据更新。在 SERIALIZABLE 隔离级 别下，对于事务 T1 中更新和删除语句，与查询语句相同，只能看到事务开始执行时提交的数据行， 但是，这些数据行可能已经被同时并发的另一个事务 T2 更新（事务 T2 开始的时间在事务 T1 前）， 在这种情况下，事务 T1 将等待事务 T2 回滚或提交。如果事务 T2 回滚，则事务 T1 在原来的数 据上继续更新，如果事务 T2 提交了，则事务 T1 就会出错回滚，因为一个串行事务不能修改在该 事务开始后被其它事务修改的数据。SERIALIZABLE 隔离级别可以确保每个事务看到一个完全一致 的数据，但是当出现并发更新时，后更新的事务必须回滚，只能在之前的更新提交后再重新执行。在 SERIALIZABLE 隔离级别下，应用程序如果碰到更新语句回滚，应该重执行更新语句所在的整个事 务。 3.1.7 DDL 语句的阻塞 当有事务对表执行 DML 语句时，又有新的会话发出 DDL 语句，这个会话会一直等待。直到所有表的 DML 语 句都已提交或者回滚后，DDL 语句才能执行成功。 在会话等待期间，并发会话可能会发出新的 DML 语句，如果 DDL 语句是非阻塞的，那么新的 DML 语句会立 即执行。如果 DDL 语句是阻塞的，那么新的 DML 语句会在 DDL 语句执行完成后再执行。存在 DDL 超时参数来控 制会话阻塞是否超时，超时阻塞将会抛出错误。 对于分区表来说，DDL 针对每个分区的阻塞情况都是不一样的，每个分区都是独立的。 3.2 SQL 数据类型 3.2.1 数据类型的选择 数据库应用程序创建和使用数据库时，为每个对象选择正确的数据类型能够提高数据的完整性、降低存储开销和 提高应用的性能。 3.2.1.1 正确的数据类型提高数据完整性 正确的数据类型可以当作一种数据约束，来提高数据完整性。例如，日期类型字段只能存储日期数据，但如果使 用字符类型，则可能存在非日期类型的数据。这样通过数据类型就能防止无效的数据写入，不需要再通过应用程序格 外设计条件来规范数据格式。在 kingbaseES 数据库中，每种数据类型字段中只允许对应格式的数据写入。 32 第3章 面向应用程序的 SQL 开发 选择合适的数据类型降低存储开销 3.2.1.2 除了选择正确的数据类型，还需要正确地指定类型的长度或者精度，例如： • 创建包含 n 个字符串的列时，需要指定字段类型为 varchar(n) • 创建整数列时，使用 numeric(6,0)，指定标度为 0 数据类型的长度和精度会影响数据的存储。如果每个列都提供其数据类型的最大长度或精度，那么数据库为其分 配固定的存储空间，会产生很多空闲的存储块，增加存储开销。并且对于查询来说，扫描的数据块的代价也会变大。 对单个查询的效率可能影响不大，但如果是批量查询或者并发查询效率就会低很多。因此，不要随意将字段类型的长 度或者精度设置为最大值，如果应用程序后续需要增加列的长度，可以使用 SQL 语句再修改列定义。 正确的数据库类型提高性能 3.2.1.3 选择正确的数据类型可以提高查询效率，不正确的数据类型可能会导致优化生成效率低的执行计划。比如，对日 期数据比较范围时，在 timestamp 类型字段创建索引，会比 varchar 类型更容易生成最优执行计划。 3.2.2 字符数据 KingbaseES 支持的字符类型如下表所示： 表 3.2.1: 字符类型 数据类型 描述 char/nchar 固定长度的字符串 varchar/nvarchar 可变长度的字符串 text 无限变长，存储任何长度的字符串, 存储最大值为 1G。 clob 字符大对象, 存储最大值为 1G。 在 char 和 varchar 之间进行选择时，需要考虑以下方面： • 存储开销 char 为固定长度字符类型，数据库将自动填充数据值，varchar 不会自动被填充。因此，varchar 类型比 char 类 型更有效的利用空间，节省存储开销。 • 性能 由于填充差异，对包含 varchar 列的表进行全表扫描读取的数据块可能比包含 char 列的表进行全表扫描读取的 数据块少。如果您的应用程序经常对包含字符类型的大表进行全表扫描，那您可以通过选择 vachar 数据类型来提高 查询效率。 • 语义 33 第3章 面向应用程序的 SQL 开发 需要比较语义中的 ANSI 兼容性时，请使用 char 数据类型。当字符串尾随空格会影响比较结果时，请使用 varchar 数据类型。 3.2.3 数值数据 KingbaseES 中存储数值数据的类型有 numeric、integer、double precision 等。 numeric 存储 0 以及绝对值为 [1.0 x 10-130, 1.0 x 10126) 的正、负定点数。integer 存储有符号整数，取值范围 -2147483648~ +2147483647。double precision 存储双精度浮点数（8 字节）。 3.2.4 时间日期数据 KingbaseES 支持使用 date 和 timestamp 类型来存储日期时间数据，表示世纪、年、月、日、小时、分钟、秒以 及时区等格式数据。支持的类型见下表所示： 表 3.2.2: 时间日期类型 数据类型 描述 date 存储日期和时间值 timestamp 存储日期和时间值 timestamp with time zone 存储带时区的日期时间值 timestamp with local time zone 存储本地时区的日期时间值 interval year to month 存储两个 timestamp 值的间隔，描述若干年若干月间隔 interval day to second 存储两个 timestamp 值的间隔，描述若干天若干秒间隔 3.2.4.1 查询当前时间 查询当前日期时间最简单的方法，是查询 SYSDATE。还可以通过 to_char 函数指定格式化模型。例如： select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'); 3.2.4.2 插入和查询日期 插入或者查询日期数据时，可以使用 to_char 或者 to_date 格式模型函数来格式化日期数据。例如，下面的例子 演示了如何格式化插入和查询日期数据 34 第3章 面向应用程序的 SQL 开发 --建表 create table t_date (c1 date); --使用 to_date 插入日期数据 INSERT INTO t_date VALUES(TO_DATE('05 Dec 2000', 'DD Mon YYYY')); --不指定格式查询日期数据 select c1 from t_date; c1 | ----------------------| 2000-12-05 00:00:00 | --使用 to_char 指定格式查询日期数据 select to_char(c1,'yyyy-mon-dd') from t_date; to_char | ------------| 2000-dec-05 | 3.2.4.3 插入和查询时间 插入或者查询时间数据时，可以使用 to_char 或者 to_date 格式模型函数来格式化时间数据。对于 date 列： • 默认时间为 00:00:00 如果只插入日期部分的数据，则时间部分的数据默认是 00:00:00 • 必须插入日期数据 如果不插入日期部分的数据，数据库会报错无效数据。 例如，下面的例子演示了如何格式化插入和查询时间数据 --建表 create table t_time (c1 date); --使用 to_date 插入日期数据 INSERT INTO t_TIME VALUES(TO_DATE('05 Dec 2000 8:08:08 p.m', 'DD Mon YYYY HH:MI P.M')); --不指定格式查询日期数据 select c1 from t_time; c1 | ----------------------| 2000-12-05 08:08:00 | --使用 to_char 指定格式查询日期数据 select to_char(c1,'DD Mon YYYY HH:MI P.M') from t_time; 35 第3章 to_char 面向应用程序的 SQL 开发 | ----------------------| 05 Dec 2000 08:08 P.M | 3.2.4.4 日期时间数据的运算 日期时间值的运算结果有 KingbaseES 数据库 SQL 语言手册中的规则确定。KingbaseES 提供很多日期时间函 数，例如，函数 DATEDIFF 计算两个 DATE 之间的天数。 3.2.4.5 日期时间数据的转换函数 KingabaseES 支持的时间转换函数如下表，函数的具体用法参见 SQL 语言。 表 3.2.3: 日期时间数据的转换函数 函数 输入 输出 to_char date、timestamp、timestamp with time zone、times- varchar tamp with local time zone、interval year to month、 interval day to second to_date char、varchar date to_timestamp char、varchar timestamp 3.2.4.6 导出和导入日期时间数据 使用导出和导入备份功能时，无需担心 timestamp with time zone 等带时区日期时间数据值的偏移，因为数据 库以标准化格式存储这些值。当导入导出 date 或者 timestamp 类型时，需要调整源端和目的端数据库之间的时区差 异，因为数据库不存储它们的时区。 3.2.5 特殊数据 3.2.5.1 空间数据 空间数据一般应用于支持位置的应用程序、地理信息系统和地理成像的应用程序，有关空间数据信息可参考 KingbaseGIS 使用手册。 36 第3章 面向应用程序的 SQL 开发 大对象类型 3.2.5.2 对象数据类型主要存储多媒体或者文件类型的数据，KingbaseES 支持的大对象类型如下表所示： 表 3.2.4: 大对象类型 数据类型 描述 blob 二进制大对象，以二进制格式存储数据。通常用于多媒体数据，例如图像、视频 和音频。 clob 字符串大对象，存储字符串数据，通常用于存储大型字符串或者文档 text 字符串大对象，存储字符串数据 3.2.5.3 json 数据 KingbaseES 为存储 JSON 数据提供了 json 和 jsonb 两种类型。并且提供 gin 索引，可以对 json 数据进行操作。 对于 json 数据的处理，可以使用 json 类型来约束，或者使用 is_json 函数来约束数据格式。更多 json 函数内容可参 考 SQL 语言。 3.2.5.4 xml 数据 KingbaseES 提供 xml 数据类型来存储 xml 格式的文件数据。还可以使用 XMLPARSE 等相关的系统函数来处理 数据，更多 xml 函数内容可参考 SQL 语言。 3.2.6 伪列 访问数据表行最快的一种方式是使用伪列。KingbaseES 支持 ROWNUM 伪列，ROWNUM 返回一个数值指示该 条记录在 KingbaseES 从表取得或连接产生时的顺序。 伪列的行为与普通的列十分相似，但伪列的值并非与表存储在一起。用户可以使用 SELECT 来选择伪列，但不 能对伪列进行任何的修改。伪列与不带参数的函数十分相似，但没有参数的函数其函数返回结果对于结果集中每条记 录一般都是固定不变的，而伪列对于同一记录集在不同查询条件下所返回的结果可能是不相等的。 3.3 正则表达式的应用 3.3.1 正则表达式概述 正则表达式是一个字符序列，它是定义一个串集合（一个正则集）的缩写。正则表达式使用操作符和字符指定搜 索模式。 37 第3章 面向应用程序的 SQL 开发 如果一个串是正则表达式描述的正则集中的一员时，我们就说这个串匹配该正则表达式。和 LIKE 一样，模式字 符准确地匹配串字符，除非在正则表达式语言里有特殊字符—不过正则表达式用的特殊字符和 LIKE 用的不同。和 LIKE 模式不一样的是，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。 3.3.2 KingbaseES 对正则表达式的支持 KingbaseES 支持数据库应用程序使用正则表达式来实现复杂的模式匹配。使用正则式进行模式匹配有以下优 点： • 通过在数据库建立集中模式匹配逻辑，可以避免应用程序对 SQL 结果集进行复杂的字符串处理 • 通过在服务器端使用正则表达式强制实施约束，可以避免在多个客户端上重复验证逻辑。 KingbaseES 支持具有模式匹配条件的正则表达式和一些函数结合使用，模式匹配在给定的字符串中搜索指定的 模式，支持的函数如下表所示： 表 3.3.1: 日期时间数据的转换函数 函数 描述 regexp_replace 用目标字符串去替换模式匹配的源字符串，返回替换后的字符串 regexp_count 返回返回目标字符串在源字符串中出现的次数 regexp_instr 返回模式匹配字符串的起始位置，或者结束位置。 regexp_substr 在字符串中搜索正则表达式字符串，返回搜索到的子字符串 regexp _matches 返回模式匹配正则表达式得到的所有子串 函数的具体用法可参见 SQL 语言。 下表列举了一些模式匹配选项和例子： 38 第3章 面向应用程序的 SQL 开发 表 3.3.2: 日期时间数据的转换函数 模式匹配选项 描述 示例 i 大小写不敏感的匹配 函数返回的结果是 {KingbaseES}: select regexp_match(’KingbaseES’,’K.*es’,’i’); 大小写敏感的匹配 (默认) c 函数返回的结果是 null: select regexp_match(’KingbaseES’,’K.*es’,’c’); 新行敏感的匹配 n 函数返回的结果是 null: select regexp_match(’KingbaseES’,’K.*ES’,’n’); 非新行敏感的匹配（默认） s 函数返回的结果是 {’Kingbase + ES’} : select regexp_match(’KingbaseES’,’K.*ES’,’n’); 扩展语法（可忽略空格） x 函数返回的结果是 KingbaseES: select regexp_match(’KingbaseES’,’K.* ES’,’x’); 3.3.3 正则表达式标准 KingbaseES 提供了三种独立的实现模式匹配的方法：SQL LIKE 操作符、更近一些的 SIMILAR TO 操作符 （SQL:1999 里添加进来的）和 POSIX-风格的正则表达式。 正则表达式（RE）在 POSIX 1003.2 中定义，它有两种形式：扩展正则表达式 ERE 和基本正则表达式 BRE。 KingbaseES 支持两种形式，并且还实现了一些 POSIX 标准中没有但是在类似 Perl 或者 Tcl 这样的语言中得到广泛 应用的一些扩展。使用了那些非 POSIX 扩展的 RE 叫高级 RE。 3.3.4 正则表达式的运算符 3.3.4.1 KingbaseES 对 POSIX 运算符的支持 KingbaseES 遵循 POSIX 标准中定义的运算符和匹配语义，支持的运算符如下表所示： 39 第3章 面向应用程序的 SQL 开发 表 3.3.3: KingbaseES 对 POSIX 运算符的支持 运算符 匹配 * 一个由原子的 0 次或更多次匹配组成的序列 + 一个由原子的 1 次或更多次匹配组成的序列 ? 一个由原子的 0 次或 1 次匹配组成的序列 {m} 一个由原子的正好 m 次匹配组成的序列 {m,} 一个由原子的 m 次或更多次匹配组成的序列 { m,n} 一个由原子的从 m 次到 n 次（包括）匹配组成的序列;m 不能 超过 n *? * 的非贪婪匹配 +? + 的非贪婪匹配 ?? ? 的非贪婪匹配 {m}? {m} 的非贪婪匹配 {m,}? {m,} 的非贪婪匹配 {m,n}? {m,n} 的非贪婪匹配 ^ 串开头的匹配 $ 串末尾的匹配 (?=re) 在匹配 re 的子串开始的任何点的 positivelookahead 匹配（只 对 ARE） (?!re) 在匹配 re 的子串开始的任何点的 negativelookahead 匹配（只 对 ARE） (?<=re) 只要有一个点上有一个子串匹配 re 端，positivelookbehind 就 在这个点上匹配（只对 ARE） (?'2000/09/09'; 3.4.3.2.3 基于大小写敏感的函数索引 你可以利用 upper 或者 lower 函数创建索引，允许表数据进行大小写不敏感的搜索扫描，加快查询速度。示例如 下： 43 第3章 面向应用程序的 SQL 开发 --建表 create table t2 (c1 varchar(100)); insert into t2 values('KingbaseES'),('KINGBASE'),('kes'); --创建函数索引 create index inx_t2 on t2(lower(c1)); --查询使用函数索引 select * from t2 where lower(c1)='kingbasees'; 3.5 数据完整性的维护 3.5.1 使用约束限制业务规则 使用数据库约束来强制性限制业务规则或者数据规范，比起应用程序运行规则代码，执行效率更高并且检查速度 更快，设计流程也会更简单。 但如果应用层可以在不查询表的情况下就能检查数据的有效性时，建议同时使用数据库约束和应用代码强制业务 规则。这样可以减少无效数据插入数据库的负载。 3.5.2 索引约束 创建唯一键或者主键约束后，KingbaseES 数据库会自动创建索引。如果要使用包含外键约束的索引，需要手动 创建。当已经存在约束索引时，再单独创建约束时数据库不会再自动为其创建索引。对于约束和索引的使用需要注意 以下几点： • 唯一约束和主键约束可以使用唯一索引 • 约束和索引中的列顺序不需要一致 • 如果已启用唯一或者主键约束，则不能删除对应的索引。需要先删除或者禁用该约束，再删除索引 3.5.3 非空约束 数据库默认情况下，列可以包含 null 值。要确保列不包含 null 值，需要使用 not null 约束。在以下情况下可以 使用 not null 约束： • 列必须为非空值 • 允许数据库对表进行索引扫描。在索引列中必须要有一个非空列，数据库查询时才能使用索引扫描 44 第3章 3.5.4 面向应用程序的 SQL 开发 默认值 默认情况下，列值为 null。向表插入数据时，如果未指定列值，则插入默认值。可以在建表时使用 default 指定 列的默认值，也可以之后再修改表定义指定默认值。在下面这些情况下可以指定默认值： • 列包含非空约束 • 列有一个最常见的值 • 非空列在未指定值时，有一个默认值 • 希望由数据库自动插入列值 3.5.5 主键约束 primary key 主键约束标识列值唯一且值非空。每张表只能有一个主键，但主键可以是组合列。但是通常情况 下，单个列作为主键是最佳方案，例如可以选择序列作为主键列。单列作为主键有以下特点： • 唯一的 • 非空 • 简短 因此，在程序设计中尽量减少使用复合主键，组合列的值很长且不能用序列自动生成 3.5.6 唯一约束 一般情况下，在不允许重复的非空列上使用 unique 唯一约束。例如，身份证号、员工编号等类型字段。 3.5.7 外键约束 当两张表共享一列或者多列数据时，可以使用 foreign key 外键约束来强制执行完整性约束，确保共享列在两张 表中始终包含相同值。将一个表指定为引用表，另一个表为被引用表。被引用表中的共享列需要包含主键约束或唯一 约束，引用表中的共享列引用外键约束。在外键列上定义额外的约束可能会影响引用关系。如果被引用键的主键约束 或者唯一约束不存在或者被禁用，则无法启用外键。 3.5.7.1 管理外键约束 3.5.7.1.1 外键列的数据类型和名称 引用表和被引用表的共享列必须使用相同的数据类型，但列名可以不一样。 45 第3章 面向应用程序的 SQL 开发 3.5.7.1.2 创建外键的权限 要创建外键，需要对引用表和被引用表都有访问权限。用户对被引用表的引用列有对象权限，对于引用表需要有 建表权限。 3.5.7.1.3 外键的属性选择 KingbaseES 数据库允许强制执行不同类型的外键约束。 • 默认设置下，数据库会阻止删除和更新被引用列操作。 • 设置外键为 DELETE CASCADE 或 UPDATE CASCADE 时，允许删除或者更新被引用键。这种情况下，引 用键的数据行也会随之被删除或者更新。 • 设置外键为 DELETE SET NULL 或 UPDATE SET NULL 时，删除或者更新被引用键时，引用键的值会被置 为空。 3.5.7.2 外键与空值 外键允许键值为空。如果要对复合外键中的空值执行完整性约束，不允许空值存在，可以再定义一个 check 约 束。 3.5.7.3 引用表的关系 引用表和被引用表之间的关系可以通过被应用表外键列上的其他约束类型类确定。 • 外键没有约束 当没有在外键上定义其他约束时，引用表中的列值可以引用相同的引用列值。并且允许外键为空值。 • 外键和非空约束 当外键不允许空值时，引用表中的每一行值都必须显式引用被引用表的键值，外键不存在空值。 • 外键和唯一约束 在外键上创建唯一约束后，引用表只存在一行可以引用被引用表的键值，外键允许存在空值 • 外键和唯一非空约束 当外键上同时包含唯一约束和非空约束时，引用表只存在一行可以引用被引用表的键值，并且外键不允许为空 值。 3.5.7.4 多外键约束规则 KingbaseES 允许一个列被多个外键约束引用，并且没有数量限制。 46 第3章 3.5.7.5 面向应用程序的 SQL 开发 延迟约束检查 如果需要将检查约束推迟到事务结束时，可以使用 SET CONSTRAINT 子句。设置延迟约束检查时，需要注意 以下几点： • 选择适当的数据，可以推迟唯一约束和外键约束的检查 • 外键定义为更新级联操作 • 确保约束是可延迟的，确保创建唯一约束和外键约束时设置的是 DEFERRABLE • 在事务开始前设置所有约束延迟 示例如下： --创建表、可延迟约束 CREATE TABLE t1 (a int); ALTER TABLE t1 ADD CONSTRAINT pk_a PRIMARY KEY(a) DEFERRABLE INITIALLY DEFERRED; --设置事务约束延迟 \set AUTOCOMMIT off START TRANSACTION; SET CONSTRAINTS ALL DEFERRED; --验证 INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(1); --事务提交时才会检查约束，提示违反约束限制的错误 COMMIT; 错误：重复键违反唯一约束 3.5.7.6 外键与索引 创建外键约束时，不会自动为其创建对应的索引。如果被引用列存在更新或者删除时，最好为外键单独创建索 引，提升效率。 3.5.8 check 约束 需要基于逻辑表达式强制完整性约束时，可以使用 check 约束。 3.5.8.1 check 约束的限制 check 约束要求表的每一行都满足表达式条件，否则回滚该数据修改语句。check 约束条件有以下限制： 47 第3章 面向应用程序的 SQL 开发 • 条件必须是一个布尔表达式，可以使用表达式对正在插入或者更新的行数据进行评估 • 条件不能包含子查询和序列 • 条件不能包含 sysdate,user 等不确定值的函数 • 条件不能包含伪列 rownum • 条件不能包含用户自定义的函数 3.5.8.2 check 约束设计 使用检查约束是，仅当条件计算结果为 false 时才违反约束，true 和 null 值都不违反检查条件 3.5.8.3 多个 check 约束规则 单个列可以用多个检查约束，check 约束的数量没有限制。但在设计时需要注意多个约束的顺序，以及避免互相 检查产出的冲突。 3.5.8.4 check 约束和非空约束 check 约束可以实现 not null 约束的功能，可以使用 not null 约束或者 check 约束来执行非空约束。not null 约 束设置会更简单一些。 复合键只能允许所有值为空或者非空时，必须使用 check 约束。例如，支持表 t1 的 a 列和 b 列必须同时为空或 者同时非空： create table t1 (a int,b int); alter table t1 add constraint t1_check check ((a is null and b is null) or (a is not null and b is not null)); 3.5.9 启用和禁用约束 启用和禁用约束都有对应的运行机制和过程： • 启用约束 约束被启用后，将对关联列中的数据值强制执行约束规则。约束的定义存储在数据字典中 • 禁用约束 约束被禁用后，不会强制执行约束检查，但约束的定义仍然保存在数据字典中。 48 第3章 3.5.9.1 面向应用程序的 SQL 开发 禁用约束的场景 在正常情况下，约束一般保持被启用状态。但在某些特殊情况下，出于性能原因，可以暂时禁用约束。例如： • 需要将大量数据插入到表中 • 对大表执行批量更新操作 • 导入导出表时 上述情况下，暂时禁用约束可以加快操作的执行速度。 3.5.9.2 启用和禁用约束的执行 数据库创建约束时，默认约束是启用状态，可以通过 ALTER 语句来修改约束的启用状态。 例如，当数据库需要执行大批量更新操作时，可以暂时禁用约束： ALTER TABLE table_name disable primary key; ALTER TABLE table_name MODIFY CONSTRAINT public_table_name_constraint_1 disable; 当批量操作完成后，再启用约束： ALTER TABLE table_name enable primary key; ALTER TABLE table_name MODIFY CONSTRAINT public_table_name_constraint_1 enable; 3.5.9.3 修复异常约束 在修改约束时，如果存在某一行数据违反约束规则，必须修改或者删除该行后，才能修改或者启用约束。 3.5.10 修改约束 可以通过 ALTER MODIFY CONSTRAINT 语句来修改约束，例如约束的启用、验证等： --启用约束 ALTER TABLE table_name MODIFY CONSTRAINT public_table_name_constraint_1 enable; --设置约束为 novalidate 状态 ALTER TABLE table_name MODIFY CONSTRAINT public_table_name_constraint_1 enable novalidate; 49 第3章 3.5.11 面向应用程序的 SQL 开发 约束的重命名 您可以修改约束的名字。和表对象一样，约束名必须是唯一的，不存在同名约束。创建约束时，如果未指定约束 名，数据库会自动为约束命名。为了方便程序开发管理，建议最好是为约束统一命令，方便之后的约束管理。 使用 alter 语句来修改约束名，例如： ALTER TABLE table_name rename CONSTRAINT public_table_name_constraint_1 to table_name_pk; 3.5.12 删除约束 通过 ALTER TABLE DROP 语句来删除约束。要删除约束和依赖于它的所有其他完整性约束，可以指定 cascade 语句。例如： ALTER TABLE table_name drop CONSTRAINT table_name_pk; 3.5.13 约束信息的数据字典 要查找约束的相关信息，可以通过系统视图查看，如下表所示： 表 3.5.1: 约束的数据字典 视图 描述 sys_constraint 包含数据库所有的约束信息 sys_constraint_status 包含数据库所有的约束状态信息 all_cons_columns 包含数据库所有的约束基本信息 all_constraints 包含数据库所有的约束基本信息和状态信息 user_cons_columns 包含用户所有的约束基本信息 user_constraints 包含用户所有的约束基本信息和状态信息 50 第 4 章 面向应用程序的 PL/SQL 开发 4 第 章 面向应用程序的 PL/SQL 开发 4.1 PL/SQL 子程序和包的编写 PL/ SQL 子程序和包是数据库应用程序的构建块。建议您将应用程序实现为一个包，原因在《PLSQL 过程语 言》中给出。 另请参阅: 《PLSQL 过程语言》了解有关处理 PL/SQL 子程序和包中的错误的信息 《plsql_plprofiler》了解有关应用程序跟踪工具的信息，它可以帮助您发现 PL/SQL 代码中的问题 4.1.1 PL/SQL 子程序概述 PL/SQL 源程序的基本单元是块，它对相关的声明和语句进行分组。一个块有一个可选的声明部分、一个必需的 可执行部分和一个可选的异常处理部分。块可以是匿名的或命名的。 PL/SQL 子程序是一个可以重复调用的命名块。如果子程序有参数，那么每次调用子程序时参数的值可能不同。 子程序要么是一个过程，要么是一个函数。通常，过程用于执行特定的操作，不需要返回值；而函数用于返回特 定的数据。 子程序也可以是嵌套子程序（在 PL/SQL 块内创建，可以是另一个子程序）、包子程序（在包规范中声明并在 包体中定义）或独立子程序（在模式级别创建）。独立程序是外存态子程序。外存态的子程序被编译并存储在数据库 中，许多应用程序可以在其中调用它。 外存态的子程序受 AUTHID 子句的影响。AUTHID 子句影响子程序在运行时发出的 SQL 语句的名称解析和权 限检查。 PL/SQL 允许您重载所有程序。重载的程序具有相同的名称，但它们的形式参数在名称、数量、顺序或数据类型 方面不同。 触发器与存储过程相同的是，它是一个命名的 PL/SQL 单元，它存储在数据库中并且可以重复调用。与存储过 程不同的是，您可以启用和禁用触发器，但不能显式调用它。当触发器被启用时，数据库会在触发事件发生时自动调 用它——也就是说，触发器会触发。当触发器被禁用时，它不会触发。 51 第 4 章 面向应用程序的 PL/SQL 开发 由于内部重试，BEFORE UPDATE 触发器可以触发多次。在设计应用程序时考虑这一点。如果您使用普通触发 器，则在重试时，触发器所做的任何工作都会回滚。 另请参阅: 《PLSQL 过程语言》，了解有关 PL/SQL 子程序的完整信息 《PLSQL 过程语言》，了解更多关于 PL/SQL 块的信息 《PLSQL 过程语言》，了解有关子程序的更多信息 《PLSQL 过程语言》，了解有关重载子程序的更多信息 《PLSQL 过程语言》，了解有关触发器的更多信息 4.1.2 PL/SQL 包概述 PL/SQL 包是一个模式对象，它对逻辑相关的 PL/SQL 类型、变量、常量、子程序、游标和异常进行分组。一 个包被编译并存储在数据库中，应用程序可以共享它的内容。 一个包总是有一个规范，它声明了可以从包外部引用的公共对象。公共对象可以由具有包的 EXECUTE 权限或 EXECUTE ANY PROCEDURE 权限的外部用户使用或调用。 如果公共对象包括游标或子程序，那么包也必须有一个包体。包体必须定义公共游标的查询和公共子程序的代 码。包体还可以声明和定义不能从包外部引用但对于包的内部工作来说是必需的私有对象。最后，包体可以包含一个 初始化部分以及一个异常处理部分，其中初始化部分用作初始化包中变量并执行其他一次性设置步骤。可以在不更改 包规范或对公共对象的引用的情况下更改包体；因此，可以将包体视为一个黑盒子。 包规范的 AUTHID 子句确定包中的子程序和游标是否以其定义者（默认）或调用者的权限运行，以及它们对模 式对象的非限定引用是否在定义者或调用者的模式中解析。 注意: 在您创建自己的包之前，请查看《KingbaseES 插件参考手册》以查看是否提供了具有您需要的功能的包。 另请参阅: 《PLSQL 过程语言》了解有关 PL/SQL 包的完整信息 《PLSQL 过程语言》使用包的原因 4.1.3 PL/SQL 单元概述 PL/SQL 单元是以下之一： • PL/SQL 匿名块 • FUNCTION • PACKAGE 52 第 4 章 面向应用程序的 PL/SQL 开发 • PACKAGE BODY • PROCEDURE • TRIGGER • TYPE • TYPE BODY PL/SQL 单元受 PL/SQL 编译参数（一类数据库初始化参数）的影响。不同的 PL/SQL 单元，例如，包规范及 其包体可以有不同的编译参数设置。 PL/SQL 单元的 AUTHID 属性会影响该单元在运行时发出的 SQL 语句的名称解析和权限检查。 另请参阅: 《PLSQL 过程语言》，了解更多关于 PL/SQL 单元和编译参数的信息 《PLSQL 过程语言》，了解更多关于 AUTHID 属性 4.1.4 创建 PL/SQL 子程序和包 4.1.4.1 创建子程序和包所需的权限 要在您自己的模式中创建独立的子程序或包，您必须具有 CREATE PROCEDURE 系统权限。要在另一个模式 中创建独立的子程序或包，您必须具有 CREATE ANY PROCEDURE 系统特权。 如果您创建的子程序或包引用模式对象，那么您必须对这些对象具有必要的对象权限。这些权限必须明确授予 您，而不是通过角色授予。 如果子程序或包的所有者的权限发生变化，则子程序或包必须在运行之前重新验证。如果从子程序或包的所有者 处撤销了对引用对象的必要对象特权，则子程序将无法运行。 授予子程序的 EXECUTE 权限使用户可以在子程序所有者的安全域下运行该子程序，因此无需授予用户对子程 序引用的对象的权限。EXECUTE 权限允许为数据库应用程序及其用户制定更规范、更有效的安全策略。此外，它 允许将子程序和包存储在数据字典中（在 sys_catalog 模式下的表中），其中没有控制创建子程序和包的用户可用的 空间。 另请参阅: 《SQL 语言》，了解有关系统和对象权限的信息 4.1.4.2 创建子程序和包 本章节说明如何使用 SQL 数据定义语言 (DDL) 语句创建独立的子程序和包。 用于创建独立子程序和包的 DDL 语句是： • CREATE FUNCTION 53 第 4 章 面向应用程序的 PL/SQL 开发 • CREATE PROCEDURE • CREATE PACKAGE • CREATE PACKAGE BODY 包的名称及其公共对象的名称在包模式中必须是唯一的。包规范和包体必须具有相同的名称。包结构在包的范围 内必须具有唯一的名称，重载的子程序除外。 前面的每个 CREATE 语句都有一个可选的 OR REPLACE 子句。指定 OR REPLACE 以重新创建现有的 PL/ SQL 单元，即更改其声明或定义而不删除它、重新创建它并重新授予之前授予的对象权限。如果重新定义 PL/SQL 单元，数据库会重新编译它。 警告: CREATE OR REPLACE 语句在替换现有的 PL/SQL 单元之前不会发出警告。 使用任何文本编辑器，创建一个包含用于创建任意数量的子程序和包的 DDL 语句的文本文件。 另请参阅: 《PLSQL 过程语言》以获取有关以下函数的更多信息 • CREATE FUNCTION • CREATE PROCEDURE • CREATE PACKAGE • CREATE PACKAGE BODY 4.1.4.3 PL/SQL 数据类型 本章节介绍 PL/SQL 数据类型并参考其他章节或文档以获取更多信息。 为数据库应用程序中的每个 PL/SQL 变量使用正确且最具体的 PL/SQL 数据类型。 另请参阅: 《面向应用程序的 SQL 开发》中“正确的数据类型提高数据完整性”章节 4.1.4.3.1 PL/SQL 标量数据类型 标量数据类型存储没有内部属性的值。 标量数据类型可以有子类型。子类型是一种数据类型，它是另一种数据类型的子集，另一种数据类型是它的基本 类型。子类型具有与其基类型相同的有效操作。数据类型及其子类型构成数据类型族。 PL/SQL 在 SYS 模式中预定义了许多类型和子类型，并允许您定义自己的子类型。 另请参阅: 《PLSQL 过程语言》，了解标量 PL/SQL 数据类型的完整描述 54 第 4 章 面向应用程序的 PL/SQL 开发 《PLSQL 过程语言》，用于按数据类型族分组的预定义 PL/SQL 数据类型和子类型 4.1.4.3.1.1 SQL 数据类型 PL/SQL 数据类型包括 SQL 数据类型。 另请参阅: 《SQL 数据类型》，以获取有关如何在数据库应用程序中使用 SQL 数据类型的信息 《PLSQL 过程语言》，了解有关 SQL 数据类型的更多信息 4.1.4.3.1.2 PLS_INTEGER 和 BINARY_INTEGER 数据类型 PL/ SQL 数据类型 PLS_INTEGER 和 BINARY_INTEGER 是 相 同 的。 为 简 单 起 见， 本 指 南 使 用 PLS_INTEGER 来表示 PLS_INTEGER 和 BINARY_INTEGER。 PLS_INTEGER 数据类型存储 -2,147,483,648 到 2,147,483,647 范围内的带符号整数，以 32 位表示。 另请参阅: 《PLSQL 过程语言》，了解有关 PLS_INTEGER 数据类型 4.1.4.3.1.3 REF CURSOR 数据类型 REF CURSOR 是游标变量的数据类型。 游标变量类似于显式游标，除了： • 它不限于一个查询。 可以为查询打开游标变量，处理结果集，然后将游标变量用于另一个查询。 • 可以为其分配一个值。 • 可以在表达式中使用它。 • 它可以是子程序参数。 可以使用游标变量在子程序之间传递查询结果集。 • 它可以是主机变量。 可以使用游标变量在 PL/SQL 存储的子程序及其客户端之间传递查询结果集。 • 它不能接受参数。 不能将参数传递给游标变量，但可以将整个查询传递给它。 游标变量具有这种灵活性，因为它是一个指针；也就是说，它的值是一个查询结果集的地址，而不是查询结果集 本身。 另请参阅: 《PLSQL 过程语言》，了解更多关于 REF CURSOR 数据类型和游标变量 55 第 4 章 面向应用程序的 PL/SQL 开发 4.1.4.3.1.4 用户定义的 PL/SQL 子类型 PL/ SQL 允许您定义自己的子类型。基本类型可以是任何标量 PL/ SQL 类型，包括先前定义的用户定义子类 型。 子类型可以： • 显示该类型数据项的预期用途 • 检测超出范围的值 另请参阅: 《PLSQL 过程语言》，了解有关用户定义的 PL/SQL 子类型的更多信息 4.1.4.3.2 PL/SQL 复合数据类型 复合数据类型具有内部属性。PL/SQL 复合数据类型是集合和记录。 在集合中，内部属性始终具有相同的数据类型，称为元素。您可以通过其唯一索引访问集合变量的每个元素。 PL/SQL 具有三种集合类型——关联数组、VARRAY（可变大小数组）和嵌套表。 在记录中，内部属性可以有不同的数据类型，称为字段。您可以通过名称访问记录变量的每个字段。 您可以创建记录集合，以及包含集合的记录。 另请参阅: 《PLSQL 过程语言》，了解有关 PL/SQL 复合数据类型的更多信息 4.1.4.3.3 抽象数据类型 抽象数据类型 (ADT) 由数据结构和操作数据的子程序组成。 另请参阅: 《PLSQL 过程语言》，了解有关 ADT 的更多信息 4.1.4.4 向客户端返回结果集 在 PL/SQL 中，与在传统数据库编程中一样，您使用游标来处理查询结果集。游标是指向私有 SQL 区域的指 针，该区域存储有关处理特定 SELECT 或 DML 语句的信息。 由 PL/SQL 构造和管理的游标是隐式游标。您构造和管理的游标是显式游标。显式游标优于隐式游标的唯一优 点是，使用显式游标可以限制获取的行数。 游标变量是指向游标的指针。也就是说，它的值是游标的地址，而不是游标本身。因此，游标变量比显式游标具 有更大的灵活性。但是，游标变量也有显式游标没有的额外消耗。 56 第 4 章 面向应用程序的 PL/SQL 开发 4.1.4.4.1 游标变量的优点 游标变量类似于显式游标，除了： • 它不限于一个查询。 可以为查询打开游标变量，处理结果集，然后将游标变量用于另一个查询。 • 可以为其分配一个值。 • 可以在表达式中使用它。 • 它可以是子程序参数。 可以使用游标变量在子程序之间传递查询结果集。 • 它可以是主机变量。 可以使用游标变量在 PL/SQL 存储的子程序及其客户端之间传递查询结果集。 • 它不能接受参数。 不能将参数传递给游标变量，但可以将整个查询传递给它。查询可以包括变量。 上述特征为游标变量提供了以下优势： • 封装 查询集中在打开游标变量的存储子程序中。 • 易于维护 如果您必须更改游标，那么您必须只更改存储的子程序，而不是调用存储子程序的每个应用程序。 • 方便安全 应用程序使用用户的用户名连接到服务器。用户必须对打开游标的存储子程序有 EXECUTE 权限， 但不需要对查询表有 READ 权限。 4.1.4.4.2 游标变量的缺点 如果您不需要使用游标变量，请使用隐式或显式游标，以获得更好的性能和易于编程。 4.1.4.4.2.1 解析游标变量的影响 当你关闭一个显式游标时，从你的角度来看，游标会关闭，也就是说，你不能在需要打开游标的地方使用它，但 是 PL/SQL 会缓存处于打开状态的显式游标。如果重新执行与游标关联的语句，那么 PL/SQL 将使用缓存的游标， 从而避免解析。 避免解析可以显著减少 CPU 使用，并且显式游标的缓存对您是透明的；它不会影响您的编程。PL/SQL 不会减 少可用打开游标的限制。 PL/SQL 不能缓存处于打开状态的游标变量。因此，解析游标变量具有影响。 57 第 4 章 面向应用程序的 PL/SQL 开发 4.1.4.4.2.2 游标变量的多行提取影响 示例 14-1 创建了一个包含多行的表，并两次获取所有这些行，第一次使用隐式游标（获取数组），然后使用游 标变量（获取单个行）。隐式游标的代码比游标变量的代码更简单且性能更好。 尽管您可以使用游标变量来获取数组，但您需要更多的代码。具体来说，您需要代码来执行以下操作： • 定义您将获取数组的集合的类型 • 显式批量收集到集合中 • 循环遍历集合以处理获取的数据 • 关闭显式打开的游标变量 示例 14-1 游标变量的数组获取影响 创建表以查询并显示其行数： CREATE TABLE t1 AS SELECT * FROM ALL_OBJECTS; SELECT COUNT(*) FROM t1; 结果类似于： count ------4902 (1 row) 使用隐式游标和游标变量执行等效操作： \set SQLTERM / DECLARE rc SYS_REFCURSOR; r t1%ROWTYPE; BEGIN FOR i IN (SELECT * FROM t1) LOOP -- 显式游标 NULL; END LOOP; OPEN rc FOR SELECT * FROM t1; -- 游标变量 LOOP FETCH rc INTO r; EXIT WHEN rc%NOTFOUND; END LOOP; CLOSE rc; 58 第 4 章 面向应用程序的 PL/SQL 开发 END; / 4.1.4.4.3 隐式返回查询结果 存储的子程序可以通过调用 DBMS_SQL.RETURN_RESULT 过程将查询结果隐式返回给客户端程序或子程序 的直接调用者。DBMS_SQL.RETURN_RESULT 返回结果后，只有接收方可以访问。 注意: 要隐式返回使用动态 SQL 执行的查询的结果，子程序必须使用 DBMS_SQL 过程执行查询，而不是 EXE- CUTE IMMEDIATE 语句。原因是 EXECUTE IMMEDIATE 语句返回子程序的游标在 EXECUTE IMMEDIATE 语句完成时关闭。 另请参阅: 《PLSQL 过程语言》，以获取有关 DBMS_SQL.RETURN_RESULT 程序 《PLSQL 过程语言》，了解有关使用 DBMS_SQL 动态 SQL 过程 4.1.4.5 从函数返回大量数据 在数据库环境中，您可能会使用 PL/SQL 函数来转换大量数据。您可以通过一系列转换传递数据，每个转换都 由不同的函数执行。PL/SQL 表函数允许您执行此类转换，而无需显着的内存开销或在每个转换阶段之间将数据存储 在表中。这些函数可以接受和返回多行，可以在准备好时返回行，而不是一次全部返回，并且可以并行化。 另请参阅: 《PLSQL 过程语言》，了解有关使用管道表函数执行多个转换的更多信息 4.1.4.6 PL/SQL 函数结果缓存 使用 PL/SQL 函数结果缓存可以节省大量空间和时间。每次使用不同的参数值调用结果缓存的 PL/SQL 函数 时，这些参数及其结果都会存储在缓存中。随后，当使用相同的参数值调用相同的函数时，将从缓存中检索结果，而 不是重新计算。因为缓存存储在共享全局区域 (SGA) 中，所以它可用于运行应用程序的任何会话。 如果用于计算缓存结果的数据库对象被更新，缓存结果将变为无效，必须重新计算。 结果缓存的最佳候选者是经常调用但依赖于不经常或从不更改的信息的函数。 另请参阅: 《PLSQL 过程语言》，了解更多关于 PL/SQL 函数结果缓存的信息 59 第 4 章 面向应用程序的 PL/SQL 开发 4.1.4.7 批量绑定概述 数据库使用两个引擎来运行 PL/SQL 单元。PL/SQL 引擎运行过程语句，SQL 引擎运行 SQL 语句。每个 SQL 语句都会导致两个引擎之间的上下文切换。通过最小化每个 PL/SQL 单元的上下文切换次数，您可以极大地提高数 据库应用程序的性能。 当 SQL 语句在使用集合元素作为绑定变量的循环中运行时，所需的大量上下文切换可能会导致性能下降。 集合包括： • 关联数组 • 可变数组 • 嵌套表 绑定是将值分配给 SQL 语句中的 PL/SQL 变量。批量绑定是一次绑定整个集合。批量绑定通过单个操作在两个 引擎之间传递整个集合。 通常，批量绑定可以提高影响多个数据库行的 SQL 语句的性能。受 SQL 语句影响的行越多，批量绑定的性能增 益就越大。考虑使用批量绑定来提高引用集合的 DML 和 SELECT INTO 语句以及引用集合并返回 DML 的 FOR 循 环的性能。 注意: 批量绑定禁用并行 DML 语句。 另请参阅: 《PLSQL 过程语言》，了解有关批量绑定的更多信息，包括如何处理批量绑定操作期间发生的异常 《PLSQL 过程语言》，了解有关并行 DML 语句的更多信息 4.1.4.7.1 引用集合的 DML 语句 使用 FORALL 关键字的批量绑定可以提高引用集合元素的 INSERT、UPDATE 或 DELETE 语句的性能。 示例 14-2 中的 PL/SQL 块增加了学生 ID 号为 1001、1002 或 1003 的学生的得分。如果没有批量绑定，PL/SQL 会为每个更新的员工向 SQL 引擎发送一条 SQL 语句，从而导致性能下降的上下文切换。 另请参阅: 《PLSQL 过程语言》，以获取有关 FORALL 语句的信息 示例 14-2 引用集合的 DML 语句 CREATE TABLE student (id int PRIMARY KEY, name text, score number); insert into student values (1001, 'xz', 65); insert into student values (1002, 'xw', 88); insert into student values (1003, 'xl', 72); insert into student values (1004, 'xh', 99); insert into student values (1005, 'xq', 81); 60 第 4 章 面向应用程序的 PL/SQL 开发 \set SQLTERM / DECLARE TYPE numlist IS VARRAY(100) OF int; v_id numlist := numlist(1001, 1002, 1003); BEGIN --批量绑定 FORALL i IN v_id.FIRST..v_id.LAST UPDATE student SET score = 1.1 * score WHERE id = v_id(i); --非批量绑定 FOR i IN v_id.FIRST..v_id.LAST LOOP UPDATE student SET score = 1.1 * score WHERE id = v_id(i); END LOOP; END; / 4.1.4.7.2 引用集合的 SELECT 语句 BULK COLLECT 子句可以提高引用集合的查询的性能。您可以将 BULK COLLECT 与标量值表或%TYPE 值 表一起使用。 另请参阅: 《PLSQL 过程语言》，以获取有关该 BULK COLLECT 子句 示例 14-3 中的 PL/SQL 块将多个值查询到 PL/SQL 表中。如果没有批量绑定，PL/SQL 会为每个选定的学生向 SQL 引擎发送一条 SQL 语句，从而导致性能下降的上下文切换。 示例 14-3 引用集合的 SELECT 语句 \set SQLTERM / DECLARE TYPE ASS_TAB IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; v_id v_name index ASS_TAB; ASS_TAB; NUMBER; CURSOR c IS SELECT id, name FROM student WHERE id > 1001; BEGIN --使用批量绑定 SELECT id, name BULK COLLECT 61 第 4 章 面向应用程序的 PL/SQL 开发 INTO v_id, v_name FROM student WHERE id > 1001; --不使用批量绑定 index := 1; FOR r IN c LOOP v_id(index) := r.id; v_name(index) := r.id; index := index + 1; END LOOP; END; / 4.1.4.7.3 引用集合并返回 DML 的 FOR 循环 您可以将 FORALL 关键字与 BULK COLLECT 关键字一起使用，以提高引用集合并返回 DML 的 FOR 循环的 性能。 另请参阅: 《PLSQL 过程语言》，以获取有关将 BULK COLLECT 子句与子句一起 RETURNING INTO 使用 示例 14-4 中的 PL/ SQL 块通过计算存储学生信息的集合类型的得分来更新 student 表。然后将得分返回到 score_inst 集合变量中。如果没有批量绑定，PL/SQL 会为每个更新的学生向 SQL 引擎发送一条 SQL 语句，从而 导致性能下降的上下文切换。 示例 14-4 引用集合并返回 DML 的 FOR 循环 \set SQLTERM / DECLARE TYPE stu_list IS VARRAY(100) OF student.id%TYPE; stuids stu_list := stu_list(1001, 1002, 1003, 1004, 1005); TYPE score_list IS TABLE OF student.score%TYPE; score_inst score_list; BEGIN --批量绑定 FORALL i IN stuids.FIRST..stuids.LAST UPDATE student SET score = 0.1 * score WHERE id = stuids(i) RETURNING score BULK COLLECT INTO score_inst; --非批量绑定 FOR i IN stuids.FIRST..stuids.LAST LOOP 62 第 4 章 面向应用程序的 PL/SQL 开发 UPDATE student SET score = 0.1 * score WHERE id = stuids(i) RETURNING score INTO score_inst(i); END LOOP; END; / 4.1.4.8 PL/SQL 动态 SQL 动态 SQL 是一种用于在运行时生成和运行 SQL 语句的编程方法。在编写诸如动态查询系统之类的通用且灵活的 程序时，在编写必须运行数据库定义语言 (DDL) 语句的程序时，或者当您在编译时不知道 SQL 语句的全文或编号或 其输入和输出变量的数据类型时，它很有用。 如果您不需要动态 SQL，则使用静态 SQL，它具有以下优点： • 编译会验证静态 SQL 语句是否引用了有效的数据库对象，以及访问这些对象所需的权限是否到位。 • 编译会创建模式对象依赖项。 另请参阅: 《面向应用程序的高级功能》中“数据库的依赖关系”章节，以获取有关模式对象依赖关系的信息 《PLSQL 过程语言》，了解有关动态 SQL 的更多信息 《PLSQL 过程语言》，了解更多关于静态 SQL 的信息 4.1.5 更改 PL/SQL 子程序和包 要更改存储的独立子程序或包的名称，您必须删除它，然后使用新名称创建它。例如： \set SQLTERM / CREATE PROCEDURE proc1 IS BEGIN NULL; END; / \set SQLTERM ; DROP PROCEDURE proc1; \set SQLTERM / CREATE PROCEDURE proc2 IS BEGIN NULL; END; / \set SQLTERM ; 63 第 4 章 面向应用程序的 PL/SQL 开发 OR REPLACE 要更改存储的独立子程序或包而不更改其名称，您可以通过包含在 CREATE 语句中将其替换为 具有相同名称的新版本。例如： \set SQLTERM / CREATE OR REPLACE PROCEDURE proc1 IS BEGIN RAISE NOTICE 'Hello World!'; END; / 注意: ALTER 语句（例如 ALTER FUNCTION、ALTER PROCEDURE 和 ALTER PACKAGE）不会改变现有 PL/SQL 单元的声明或定义，它们只会重新编译这些单元。 另请参阅: 《PLSQL 过程语言》，以获取有关 ALTER 语句的信息 4.1.6 删除 PL/SQL 子程序和包 要删除存储的独立子程序，请使用以下语句： • DROP FUNCTION • DROP PROCEDURE 要删除包（包规范和包体）或仅删除其包体，请使用该 DROP PACKAGE 语句。 另请参阅: 《PLSQL 过程语言》，了解更多关于 DROP FUNCTION 的信息 《PLSQL 过程语言》，了解更多关于 DROP PROCEDURE 的信息 《PLSQL 过程语言》，了解更多关于 DROP PACKAGE 的信息 4.1.7 从 SQL 语句调用存储的 PL/SQL 函数 警告: 因为 SQL 是一种声明性语言，而不是命令式（或过程式）语言，所以您无法知道 SQL 语句调用的函 数将运行多少次——即使该函数是用命令式语言 PL/SQL 编写的。 如果您的应用程序要求某个函数执行一定次数，请不要从 SQL 语句中调用该函数。请改用游标。 例如，如果您的应用程序要求为每个选定的行调用一个函数，则打开一个游标，从游标中选择行，然后为每一行 调用该函数。这种技术保证调用函数的次数是从游标中获取的行数。 64 第 4 章 面向应用程序的 PL/SQL 开发 有关游标的一般信息，请参阅《PLSQL 过程语言》。 这些 SQL 语句可以调用 PL/SQL 存储函数： • INSERT • UPDATE • DELETE • SELECT SELECT 也可以调用在其 WITH 子句中声明和定义的 PL/SQL 函数。 • CALL CALL 也可以调用 PL/SQL 存储过程。 要从 SQL 语句调用 PL/SQL 函数，您必须拥有或拥有该函数的 EXECUTE 权限。要从使用 PL/SQL 函数定义 的视图中进行选择，您必须对该视图具有 READ 或 SELECT 权限。从视图中选择不需要单独的 EXECUTE 权限。 注意: PL/SQL 函数的 AUTHID 属性也会影响从 SQL 语句调用函数所需的权限，因为 AUTHID 会影响单元在运行 时发出的 SQL 语句的名称解析和权限检查。有关详细信息，请参阅《PLSQL 过程语言》。 另请参阅: 《SQL 语言》，以获取有关 SELECT 语句的信息 《PLSQL 过程语言》，获取有关调用子程序的信息，包括传递参数 4.1.7.1 为什么从 SQL 语句中调用 PL/SQL 函数？ 在 SQL 语句中调用 PL/SQL 函数可以： • 通过扩展 SQL 提高用户工作效率 当活动过于复杂、过于笨拙或不能使用 SQL 时，SQL 语句的表达能力就会提高。 • 提高查询效率 查询子句中的函数 WHERE 可以使用必须由应用程序评估的标准过滤数据。 • 处理字符串以表示特殊数据类型（例如，纬度、经度或温度） • 提供并行查询执行 如果查询是并行化的，那么 PL/SQL 子程序中的 SQL 语句也可以并行运行（使用并行查询选项）。 4.1.7.2 PL/SQL 函数可以出现在 SQL 语句中的位置 PL/SQL 函数可以出现在 SQL 语句中，只要 SQL 函数或表达式可以出现在 SQL 语句中。例如： 65 第 4 章 面向应用程序的 PL/SQL 开发 • SELECT 语句的选择列表 • WHERE 或 HAVING 子句的条件 • CONNECT BY, START WITH, ORDER BY 或 GROUP BY 子句 • INSERT 语句的 VALUES 子句 • UPDATE 语句的 SET 子句 PL/SQL 表函数（返回行集合）可以出现在 SELECT 语句中，而不是： • SELECT 列表中的列名 • FROM 子句中的表名 PL/SQL 函数不能出现在这些需要不变定义的上下文中： • CREATE 或 ALTER TABLE 语句的 CHECK 约束子句 • 列的默认值规范 4.1.7.3 PL/SQL 函数何时可以出现在 SQL 表达式中 要从 SQL 表达式调用，PL/SQL 函数必须满足以下要求： • 它必须是用户定义的聚合函数或行函数。 • 它的形参必须是 IN 形参，而不是 OUT、IN OUT 形参。 示例 14-5 中的函数满足上述要求。 示例 14-5 SQL 表达式中的 PL/SQL 函数（遵循规则） \set SQLTERM / CREATE OR REPLACE FUNCTION get_score ( v_id IN NUMBER, add_score IN NUMBER ) RETURN NUMBER IS v_score NUMBER; BEGIN SELECT score + add_score INTO v_score FROM student WHERE id = v_id; RETURN v_score; END get_score; / 66 第 4 章 面向应用程序的 PL/SQL 开发 4.1.7.4 控制从 SQL 语句调用的 PL/SQL 函数的副作用 如果一个子程序改变了除了它自己的局部变量的值之外的任何东西，它就会产生副作用。 例如，更改以下任何内容的子程序都会产生副作用： • 它自己的 OUT 或 IN OUT 参数 • 全局变量 • 包中的公共变量 • 一个数据库表 • 数据库 • 外部状态（例如通过调用 RAISE NOTICE 或发送电子邮件） 副作用可能会阻止查询的并行化、产生与顺序相关的（因此是不确定的）结果，或者需要跨用户会话维护包状 态。 从 SQL 查询或 DML 语句调用的函数中不允许出现某些副作用。 4.1.7.4.1 对从 SQL 语句调用的函数的限制 注意: 从 SQL 语句调用的函数的限制也适用于 SQL 语句触发的触发器。 如果一条 SQL 语句调用了一个函数，并且该函数运行了一条新的 SQL 语句，那么新语句的执行在逻辑上嵌入在 调用该函数的语句的上下文中。为确保新语句在此上下文中是安全的，数据库对函数实施以下限制： • 如果调用函数的 SQL 语句是查询或 DML 语句，则函数不能结束当前事务、创建或回滚到保存点或 ALTER 系 统或会话。 • 如果调用该函数的 SQL 语句是查询或并行化 DML 语句，则该函数不能运行 DML 语句或以其他方式修改数据 库。 • 如果调用该函数的 SQL 语句是 DML 语句，则该函数既不能读取也不能修改由调用该函数的 SQL 语句修改的 表。 无论函数如何运行新的 SQL 语句，这些限制都适用。例如，它们适用于该函数的新 SQL 语句： • 从 PL/ SQL 调用，无论是直接嵌入到函数体中、使用 EXECUTE IMMEDIATE 语句运行，还是使用 DBMS_SQL 包运行 • 使用 JDBC 运行 • 使用外部 C 函数中的回调上下文与 OCI 一起运行 为避免这些限制，请确保新 SQL 语句的执行未在逻辑上嵌入到调用该函数的 SQL 语句的上下文中。例如，将新 SQL 语句放入自治事务中，或者在 OCI 中，为外部 C 函数创建新连接，而不是使用 OCIExtProcContext 参数提供 的句柄。 67 第 4 章 面向应用程序的 PL/SQL 开发 另请参阅: 自治事务 4.1.7.4.2 从并行化 SQL 语句调用的 PL/SQL 函数 当数据库运行并行化的 SQL 语句时，多个进程同时工作以运行单个 SQL 语句。当并行化的 SQL 语句调用一个 函数时，每个进程都可能调用它自己的函数副本，只针对该进程处理的行的子集。 每个进程都有自己的包变量副本。当并行执行开始时，为每个进程初始化包变量，就像用户登录系统一样；包变 量值不会从原始登录会话中复制。一个进程对包变量所做的更改不会自动传播到其他进程或原始登录会话。 4.2 PL/SQL Profiler 的使用 您可以使用 PL/SQL Profiler 来识别 PL/SQL 应用程序中的瓶颈和性能调整机会。 PL/SQL Profiler 报告由函数调用组织的 PL/SQL 程序的动态执行概况，并分别说明 SQL 和 PL/SQL 执行时 间。不需要特殊的源代码或编译时准备；可以分析任何 PL/SQL 程序。 本章描述 PL/SQL Profiler 并解释如何使用它来收集和分析 PL/SQL 程序的分析数据。 4.2.1 PL/SQL Profiler 概述 PL/SQL Profiler 记录程序在每个子程序中花费的时间——每个子程序的函数时间或自身时间。知道函数消耗的 时间是有帮助的，但通常是不够的。例如，知道一个程序有 40% 的时间花在子程序 INSERT_ORDER 上会很有帮 助，但知道哪些子程序经常调用 INSERT_ORDER 以及程序在 INSERT_ORDER 下花费的总时间（包括其后代子 程序）会更有帮助。PL/SQL Profiler 提供此类信息。 PL/SQL Profiler： • 报告您的 PL/SQL 程序的动态执行配置文件，按子程序调用组织 • 分别计算 SQL 和 PL/SQL 执行时间 • 不需要特殊的源代码或编译时准备 • 提供子程序级别的执行摘要信息，例如： – 子程序调用次数 – 子程序本身花费的时间（函数时间或自身时间） – 在子程序本身及其后代子程序中花费的时间 – 详细的调用关系信息，例如： ∗ 给定子程序的所有调用者（父函数） ∗ 给定子程序调用的所有子程序（子程序） 68 第 4 章 面向应用程序的 PL/SQL 开发 ∗ 从 y 调用时在子程序 x 中花费了多少时间 ∗ y 对子程序 x 的调用次数 PL/SQL 分析器由 plsql_plprofiler 插件实现： 数据采集和数据分析，可以通过调用指定 plsql_plprofiler 插件接口进行数据采集。 收集数据 4.2.2 要从 PL/SQL 程序中为 PL/SQL 分析器收集分析数据，请执行以下步骤： 1. 启动分析器 2. 运行应用程序 3. 关闭分析器 4. 数据 flush 5. 分析数据 用户在使用 profiler 前，需要创建 profiler 扩展。profiler 收集数据，通过调用 pl_profiler_set_enabled_local 函 数来控制分析器的启动或者停止。分析器收集到的数据存储在内存中，如果用户断开连接，分析器将不会自动存储数 据，需要显示调用 pl_profiler_collect_data 函数将数据刷到共享内存。需要注意的是，目前 profiler 数据不会落盘存 储，所以一旦重启数据库，分析数据将会丢失。 profiler 示例 1： --创建扩展 create extension plsql_plprofiler; --创建对象 create table t1(i int); \set SQLTERM / create or replace procedure p1() as begin raise notice 'this is procedure p1'; for i in 1..1000 loop insert into t1 values(99999); end loop; if 1000 < 100 then null; else null; end if; end; / create or replace procedure p2() as begin raise notice 'this is procedure p2'; 69 第 4 章 面向应用程序的 PL/SQL 开发 p1(); end; / create or replace procedure p3() as begin raise notice 'this is procedure p3'; p2(); if 1 < 100 then raise notice '1 < 100'; end if; end; / \set SQLTERM ; --清理本地数据 select pl_profiler_reset_local(); --清理全局数据 select pl_profiler_reset_shared(); --启动分析器 select pl_profiler_set_enabled_global(true); --运行应用程序 call p3(); --关闭分析器 select pl_profiler_set_enabled_global(false); --数据 flush select pl_profiler_collect_data(); --分析数据 ----查看已记录的对象（可选） select * from pl_profiler_func_oids_shared(); ----oid 转对象名（可选） select * from pl_profiler_get_stack(pl_profiler_func_oids_shared()); test=# \set SQLTERM ; test=# --清理本地数据 test=# select pl_profiler_reset_local(); pl_profiler_reset_local ------------------------(1 row) test=# --清理全局数据 test=# select pl_profiler_reset_shared(); pl_profiler_reset_shared -------------------------(1 row) 70 第 4 章 面向应用程序的 PL/SQL 开发 test=# --启动分析器 test=# select pl_profiler_set_enabled_global(true); pl_profiler_set_enabled_global -------------------------------t (1 row) test=# --运行应用程序 test=# call p3(); NOTICE: this is procedure p3 NOTICE: this is procedure p2 NOTICE: this is procedure p1 NOTICE: 1 < 100 CALL test=# --关闭分析器 test=# select pl_profiler_set_enabled_global(false); pl_profiler_set_enabled_global -------------------------------f (1 row) test=# --数据 flush test=# select pl_profiler_collect_data(); pl_profiler_collect_data -------------------------0 (1 row) test=# --分析数据 test=# ----查看已记录的对象（可选） test=# select * from pl_profiler_func_oids_shared(); pl_profiler_func_oids_shared -----------------------------{16793,16792,16794} (1 row) test=# ----oid 转对象名（可选） test=# select * from pl_profiler_get_stack(pl_profiler_func_oids_shared()); pl_profiler_get_stack --------------------------------------------------------------------------{"public.p2() oid=16793","public.p1() oid=16792","public.p3() oid=16794"} (1 row) 71 第 4 章 面向应用程序的 PL/SQL 开发 ----查看数据（可选） select func_oid, func_oid::regproc as funcname,line_number, source from pl_profiler_funcs_source(pl_ profiler_func_oids_shared()); test=# ----查看数据（可选） test=# select func_oid, func_oid::regproc as funcname,line_number, source from pl_profiler_funcs_ source(pl_profiler_func_oids_shared()); func_oid | funcname | line_number | source ----------+----------+-------------+----------------------------------------------------16793 | p2 | 0 | -- Line 0 16793 | p2 | 1 | 16793 | p2 | 2 | 16793 | p2 | 3 | raise notice 'this is procedure p2'; 16793 | p2 | 4 | p1(); 16793 | p2 | 5 | 16792 | p1 | 0 | -- Line 0 16792 | p1 | 1 | 16792 | p1 | 2 | 16792 | p1 | 3 | raise notice 'this is procedure p1'; 16792 | p1 | 4 | for i in 1..1000 loop 16792 | p1 | 5 | insert 16792 | p1 | 6 | into 16792 | p1 | 7 | t1 16792 | p1 | 8 | values(99999); 16792 | p1 | 9 | end loop; 16792 | p1 | 10 | if 1000 < 100 then null; else null; end if; 16792 | p1 | 11 | 16794 | p3 | 0 | -- Line 0 16794 | p3 | 1 | 16794 | p3 | 2 | 16794 | p3 | 3 | raise notice 'this is procedure p3'; 16794 | p3 | 4 | p2(); 16794 | p3 | 5 | if 1 < 100 then 16794 | p3 | 6 | 16794 | p3 | 7 | 16794 | p3 | 8 | begin end begin end begin raise notice '1 < 100'; end if; end (27 rows) ----查看对象调用关系 select * from pl_profiler_callgraph_shared(); stack | call_count | us_total | us_children | us_self ---------------------+------------+----------+-------------+--------{16794,16793} | 1 | 8175 | 7990 | 185 72 第 4 章 面向应用程序的 PL/SQL 开发 {16794,16793,16792} | 1 | 7990 | 0 | 7990 {16794} 1 | 8473 | 8175 | 298 | (3 rows) -----源码和分析数据对应该系 SELECT L.func_oid::regproc as funcname, L.func_oid as func_oid, L.line_number, sum(L.exec_count)::bigint AS exec_count, sum(L.total_time)::bigint AS total_time, max(L.longest_time)::bigint AS longest_time, S.source FROM pl_profiler_linestats_shared() L JOIN pl_profiler_funcs_source(pl_profiler_func_oids_shared) S ON S.func_oid = L.func_oid AND S.line_number = L.line_number GROUP BY L.func_oid, L.line_number, S.source ORDER BY L.func_oid, L.line_number; funcname | func_oid | line_number | exec_count | total_time | longest_time | source ----------+----------+-------------+------------+------------+--------------+---------------------------------------------------p1 | 16792 | 0 | 1 | 7990 | 7990 | -- Line 0 p1 | 16792 | 1 | 0 | 0 | 0 | p1 | 16792 | 2 | 1 | 7989 | 7989 | p1 | 16792 | 3 | 1 | 14 | 14 | raise notice 'this is for i in 1..1000 loop begin procedure p1'; p1 | 16792 | 4 | 1 | 7830 | 7830 | p1 | 16792 | 5 | 1000 | 7181 | 314 | insert p1 | 16792 | 6 | 0 | 0 | 0 | into p1 | 16792 | 7 | 0 | 0 | 0 | t1 p1 | 16792 | 8 | 0 | 0 | 0 | values(99999); p1 | 16792 | 9 | 0 | 0 | 0 | end loop; p1 | 16792 | 10 | 2 | 0 | 0 | if 1000 < 100 then 0 | null; else null; end if; p1 | 16792 | 11 | 0 | 0 | p2 | 16793 | 0 | 1 | 8175 | p2 | 16793 | 1 | 0 | 0 | 0 | p2 | 16793 | 2 | 1 | 8174 | 8174 | p2 | 16793 | 3 | 1 | 22 | 22 | end 8175 | -- Line 0 begin raise notice 'this is procedure p2'; p2 | 16793 | 4 | 1 | 8151 | 8151 | p2 | 16793 | 5 | 0 | 0 | 0 | p3 | 16794 | 0 | 1 | 8473 | p3 | 16794 | 1 | 0 | 0 | p1(); end 8473 | -- Line 0 0 | 73 第 4 章 面向应用程序的 PL/SQL 开发 p3 | 16794 | 2 | 1 | 8471 | 8471 | p3 | 16794 | 3 | 1 | 50 | 50 | begin raise notice 'this is procedure p3'; p3 | 16794 | 4 | 1 | 8365 | 8365 | p3 | 16794 | 5 | 1 | 52 | 52 | p3 | 16794 | 6 | 1 | 13 | 13 | p3 | 16794 | 7 | 0 | 0 | 0 | p3 | 16794 | 8 | 0 | 0 | 0 | p2(); if 1 < 100 then raise notice '1 < 100'; end if; end (27 rows) 4.2.3 了解 profiler 对象 支持对以下对象进行分析： • 支持函数分析 • 支持存储过程分析 • 支持包分析 包中支持分析的对象：包中初始化块、包中函数、包中存储过程。 包中对象的分析数据记录到包对应的 oid。 • 支持触发器分析 • 支持嵌套函数分析 • 支持匿名块分析 匿名块的分析数据记录到 oid 为 9999 的行。 • 支持 object type 分析 object type 中支持分析的对象：member 方法、construct 方法、static 方法。 object type 中分析数据记录到各个方法对应的 oid。 注意: 如果是重复执行同一个对象（以 oid 为标识），需要清理之前记录的数据。 4.2.4 了解 profiler 数据信息 Profiler 的是数据分为三类：执行信息、堆栈信息和源码信息。 74 第 4 章 面向应用程序的 PL/SQL 开发 4.2.4.1 执行信息 plsql_plprofiler 获取到的执行信息如下： • 对象 oid plsql_plprofiler 对象的 oid，包中的所有对象共用包的 oid、匿名块的 oid 为 9999、object type 中的对象使用方 法本身的 oid。嵌套函数的 oid 使用祖先函数的 oid。 • 语句所在行 0 行表示该对象的整体数据。 如果 PL/SQL 源码在一行上，则该语句的任何执行相关数据都属于该行。 如果 PL/SQL 源码在多行上，则该语句的任何执行相关数据都属于该语句的第一行。 如果 PL/SQL 源码的一行出现多个语句时，则多条语句的任何执行相关数据都属于该行。 • 执行次数 记录某一行代码的执行次数。一行代码不一定是一条语句。 • 总共耗时 记录某一行代码的总耗时。如果是控制语句则会包括整个控制语句中包含的语句的时间。时间单位 us。 • 最长耗时 记录某一行代码耗时最长的时间。时间单位 us。 4.2.4.2 堆栈信息 plsql_plprofiler 获取到的堆栈信息如下： • 堆栈 plsql_plprofiler 对象的堆栈 • 调用次数 该对象被调用的总次数。 • 总耗时 该对象总花费时间。 • 调用对象耗时 该对象调用对象的耗时。 • 自身耗时 该对象除去调用其他对象的耗时。 75 第 4 章 面向应用程序的 PL/SQL 开发 4.2.4.3 源码信息 plsql_plprofiler 获取到的源码信息如下： • Oid • 行号 • 源码 由于匿名块在数据库中没有做存储，所以无法获取匿名块的源码信息。 4.2.5 分析数据 4.2.5.1 理解层次分析关系 • 区分重载的子程序，是根据 profiler 数据信息的行号来进行判断。 • 查看对象调用关系 根据 profiler 示例 1 中对象，查看对象调用的关系示例如下。 ----查看对象调用关系 select * from pl_profiler_callgraph_shared(); stack | call_count | us_total | us_children | us_self ---------------------+------------+----------+-------------+--------{16794,16793} | 1 | 8175 | 7990 | 185 {16794,16793,16792} | 1 | 7990 | 0 | 7990 {16794} 1 | 8473 | 8175 | 298 | (3 rows) 如果调用的 plsql 对象中没有调用子程序，则调用 pl_profiler_callgraph_shared 函数只有一行信息；如果调用的 plsql 对象中存在调用子程序，则调用 pl_profiler_callgraph_shared 函数除了显示调用的 plsql 对象，还有显示子程 序的性能数据信息，并且子程序性能数据信息中的 stack 会显示堆栈关系，如 {16513,16515,16514}，其中 16513 为调 用的 plsql 对象，16515 为 16513 的子程序，16514 为 61515 的子程序。 可以查看到对象调用的关系和次数，以及各自对象所花费的总时间和子程序对象花费的时间。 4.2.5.2 相关话题 plsql_plprofiler 76 第 5 章 面向应用程序的高级功能 5 第 章 面向应用程序的高级功能 本章节包括的主题如下： • 闪回技术 • 编程环境的选择 • 数据库的依赖关系 5.1 闪回技术 5.1.1 闪回概述 闪回技术是备份恢复机制的一部分，在数据库发生逻辑错误的时候，能够提供快速且损失最小的恢复。KingbaseES 支持闪回回收站中的表及相关对象, 将数据库对象闪回到以前的状态，无需使用时间点介质恢复。 5.1.2 闪回的配置 回收站功能默认关闭，只有开启了参数才能将表放置到回收站中，参数名称：kdb_flashback.db_recyclebin，参 数为 bool 类型，参数级别为 sighup。 修改 kingbase.conf 文件中 shared_preload_libraries 参数后重启数据库。 shared_preload_libraries = 'kdb_flashback' 在 kingbase.conf 文件中设置参数，开启闪回功能，默认是 off 为关闭状态 kdb_flashback.db_recyclebin = on 77 第 5 章 面向应用程序的高级功能 5.1.3 闪回的使用 5.1.3.1 闪回表的使用 开启了回收站参数后，如果不允许一个删除的表进回收站，需要在 DROP 语句后面加上 PUGRE 选项。如果在 当前已经存在同名的表，则闪回失败，需要使用 RENAME TO 选项指定一个新的表名。成功闪回一个表后，回收站 视图 recyclebin 和回收站系统表 sys_recyclebin 中对象将被清除。单独删除的一个子表、临时表、分区都不支持闪 回，删除的表和相关对象不再允许操作。 例如，将在回收站中的 table_name 闪回到闪回之前的状态： FLASHBACK TABLE table_name TO BEFORE DROP; 例如，将回收站中的表闪回到一个新的表名： FLASHBACK TABLE table_name TO BEFORE DROP RENAME TO newname; 闪回表至历史时刻，基于闪回查询将被操作表的数据还原到指定的快照时刻，该操作基于 AS OF 闪回查询和当 前快照数据的差异进行还原。闪回表至历史时刻可以通过时间戳 (TIMESTAMP)、CSN 两种方式进行指定。闪回表 的过程中默认关闭触发器行为，如果期望开启的话请通过 ENABLE TRIGGERS 语句进行。语法如下： FLASHBACK TABLE table_name TO 5.1.3.2 { TIMESTAMP|CSN|SCN } asof_item [ { ENABLE|DISABLE } TRIGGERS ] 清除回收站 PURGE TABLE 分为删除回收站中指定的一个表和清空回收站。清理回收站时，回收站视图 recyclebin 和回收 站系统表 sys_recyclebin 中对象将被清除。删除回收站中指定的一个表时，将删除表及其相关联对象，例如表的 index、constraint、trigger、policy、rule 等。清空回收站将删除回收站中所有的表及关联对象。 例如，删除回收站中指定的一个表： PURGE TABLE table_name; 清空回收站中所有的表： PURGE RECYCLEBIN; 5.2 编程环境的选择 5.2.1 应用程序体系结构概述 应用程序体系结构是指数据库应用程序连接到数据库的计算环境，一般为以下几种架构： 78 第 5 章 面向应用程序的高级功能 • 客户端/服务器结构 客户端/服务器结构中，应用程序代码在客户端系统上允许数据从客户端系统传输到数据库服务器进行插入和更 新操作，并从服务器返回到客户端进行查询操作。 • 服务器编程 在数据库发送更改请求时，自动执行触发器或者调用存储子程序，应用程序逻辑可以完全在数据库层实现。您可 以重用执行验证和清理代码，以及从各种客户端控制数据库操作。例如，通过一个 web 界面调用存储过程，基于 web 界面，客户端/服务器结构相同的功能。 • 三层体系结构 在三层体系结构中，存在第三台应用服务器专门处理请求，应用程序不直接与数据库服务器通信。通过提高此中 间层的处理能力，能减少客户端系统所需的资源，客户端系统只需要通过 TCP/IP 或者 HTTP 协议发生请求。 5.2.2 KingbaseES PL/SQL 概述 PL/SQL 是 SQL 的过程扩展，是一种完全可移植的高性能事务处理语言。通过 PL/SQL 可以使用 SQL 语句操纵 数据，通过条件选择和循环控制程序逻辑，还可以声明变量、定义子程序以及捕获运行时抛出的错误。在任何 KingbaseES 编程接口中编写的应用程序都可以调用 PL/SQL 子程序和包，并将 PL/SQL 代码块发送到数据库中执行。 PL/SQL 开发的详细内容可参考《PLSQL 过程语言》。 5.2.3 KingbaseES JAVA 概述 KingbaseES JDBC 是纯 JAVA 的 JDBC 驱动程序，它支持 JDBC 3.0 和部分 4.0 API 的标准。通过 JDBC 接 口对象，应用程序可以完成与数据库的连接、执行 SQL 语句、从数据库中获取结果、状态及错误信息、终止事务和 连接等操作。KingbaseES JDBC 的主要工作就是实现这些接口。JDBC 开发的详细内容可参考《KingbaseES 客户端 编程接口指南-JDBC 》。 5.2.4 KingbaseES ODBC 概述 开放数据库连接 (ODBC) 提供了一个标准接口，允许一个应用程序访问许多不同的数据源。KingbaseES ODBC 是纯 C 的 ODBC 驱动程序，它支持 Microsoft ODBC 3.0 标准。通过 KingbaseES ODBC Driver，应用程序可以完成 与数据库的连接、执行 SQL 语句、从数据库中获取结果、获取状态及错误信息、终止事务和连接等操作。ODBC 开 发的详细内容可参考《KingbaseES 客户端编程接口指南-ODBC 》。 5.2.5 KingbaseES 预编译器概述 预编译器是一种编程工具，允许将 SQL 语句迁移到 C、C+ + 等语言编写的高级程序中。KingbaseES 支持 Pro*C 程序，嵌有 SQL 语句的 C 语言程序称为 Pro*C 程序，使用的 SQL 语言称为嵌入式 SQL，嵌入在主语言程序 中的 SQL 语句并不能直接被主语言编译程序识别，需要预编译方能被识别。Pro*C 开发的详细内容可参考《Pro*C 迁移指南》。 79 第 5 章 面向应用程序的高级功能 5.2.6 KingbaseES OCCI 概述 KingbaseES 的 OCCI 兼容了 Oracle OCCI 的常用部分接口，OCCI 是一个应用程序编程接口，它允许以 C++ 编写的应用程序与一个或者多个 Oracle 数据库服务器进行交互。具体兼容情况可以参考《OCCI 迁移指南》。 5.2.7 KingbaseES .NET Data Provider 概述 KingbaseES .NET Data Provider 是 KingbaseES 提供的 ADO.NET 数据提供程序, 它允许用 C ＃,Visual Basic,F ＃编写的程序访问 KingbaseES 数据库服务器。它以 100％的 C ＃代码实现。KingbaseES .NET Data Provider 的开 发遵循.NET 平台的规范，并且还继承 Microsoft·.NET Framework 类库提供的类和接口。.NET Data Provider 开发 的详细内容可参考《KingbaseES 客户端编程接口指南-ado.net 》。 5.3 数据库的依赖关系 5.3.1 对象依赖关系概述 数据库对象可以在其定义中引用其他对象。例如，视图引用基表定义查询块，PL/SQL 过程中引用表。对象 a 定 义中引用 b, 则 a 是 b 的依赖对象，b 是 a 的引用对象。 5.3.2 对象状态 每一个数据库对象都有一个状态值，如下表所示： 状态 描述 有效 数据字典当前定义为已成功编译对象 无效 该对象被标记为无效，因为它引用的对象发生了更改或者是编译错误 可以通过系统视图 all_objects 的 status 字段查询对象的有效状态，valid 表示有效，无效状态为 invalid。 5.3.3 对象状态的失效 对象之间的级联引用，还会产生间接依赖关系。被引用对象的修改，可能会导致直接依赖关系失效。从属引用对 象的修改，可能会产生级联依赖失效。当用户授予或者撤销对象权限时，也会导致对引用对象失去权限，从而依赖关 系失效，对象变为无效状态。 下表列举了部分对象 DDL 操作导致依赖失效的情况： 80 第 5 章 面向应用程序的高级功能 DDL 操作 依赖失效 增加表列 依赖对象引用表查询 依赖对象引用表%rowtype 依赖对象不指定列表 insert 操作 修改表列 依赖对象引用列 依赖对象引用表%rowtype 依赖对象不指定列表 insert 操作 依赖对象是行级触发器 修改视图 依赖对象引用视图 依赖对象引用视图%rowtype 依赖对象不指定列表 insert 操作 删除索引 索引是函数索引，依赖对象是基于函数索引的行级触发器 索引是唯一索引，依赖对象是视图且引用唯一索引列 修改 PL/SQL 过程 依赖对象引用函数或者过程，函数名或过程名被修改 或包 依赖对象引用过程中的变量，变量被修改 5.3.4 减少依赖无效的准则 要减少依赖对象的失效，请遵循以下准则 • 将新增过程添加到包头的末尾 向包对象中新增过程或函数时，请将其添加到包的末尾。这样也可以减少对已存在的过程或者函数的影响，防止 其变成无效状态。 • 通过视图引用表 使用视图间接引用表，向表添加列时，不会使依赖视图的对象失效。修改或者删除视图未引用列，也不会对依赖 对象有影响。 5.3.5 对象的重新验证 引用无效对象必须先进行验证，才能被调用。当引用对象时，数据库自动进行验证，不需要用户手动操作。如果 对象无效，则其状态在编译时会抛出错误。编译器将重新验证未经授权的对象，但不需要重新编译它。编译器自动重 新编译无效对象时，如果编译没有错误，则重新验证。否则，需手动修改后才能验证成功。 81 第 6 章 业务系统开发建议 6 第 章 业务系统开发建议 本章节介绍了系统开发的建议。本章节包含以下内容： • 数据库模型设计规范 • PLSQL 开发规则 • SQL 语句编写规则 6.1 数据库模型设计规范 6.1.1 命名规则 数据库对象如表、列、序列、过程、函数等在命名时要遵循如下规则： 1. 命名要使用富有意义英文词汇，避免使用缩写。 2. 数据库、数据库对象的名称可由多个单词组成的。 3. 数据库对象名称由如下部分组成：范围、类型、名称实体，各词汇间采用”_” 连接。 4. 其中各数据库对象的范围和类型的具体含义及取值详见各数据库对象的命名规则。 5. 数据库对象的名称不允许是 KingbaseES SQL 和 PL/SQL 的保留字和关键字。 表 6.1.1: 数据库对象 数据库对象 格式 样例 说明 表 < 模块名称 >_T_< 表名称 ODS_T_SYSCONFIG 同一个模块的表使用相同前 缀，长度不超过 15 个字符 > 普通索引 IND_< 表名 >_N< 序号 > IND_ORDER_N1 ORDER 表第一个普通索引 位图索引 IND_< 表名 >_B< 序号 > IND_ORDER_B1 ORDER 表第一个位图索引 见续表 82 第 6 章 业务系统开发建议 表 6.1.1 – 续表 数据库对象 格式 样例 说明 唯一索引 IND_< 表名 >_U< 序号 > IND_ORDER_U1 ORDER 表第一个唯一索引 普通视图 V_< 视图名 > V_ORDER ORDER 表的普通视图 物化视图 MV_< 视图名 > MV_ORDER ORDER 表的物化视图 存储过程 P_< 存储过程名 > P_GET_SYSDATA 获取系统时间 函数 F_< 函数名 > F_INSERT_ORDER 对 ORDER 表执行插入 包 PKG_< 包名 > PKG_STAT_ORDER 统计 ORDER 表的数据 触发器 TRG_< 触发器名称 > TRG_INITIAL 初始化数据触发器 序列 SEQ_< 序列名称 > SEQ_ORDERID ORDER 表的 ORDERID 使 用序列生成 别名 6.1.2 < 用户名 >_< 表名 > TEST_ORDER ORDER 表的别名 注释规范 对较为复杂的 sql 语句加上注释，说明算法、功能。注释风格：注释单独成行、放在语句前面。 • 面对不易理解的分支条件表达式加注释； • 对重要的计算应说明其功能； • 过长的函数实现，应将其语句按实现的功能分段加以概括性说明； • 常量及变量注释时，应注释被保存值的含义 (必须)，合法取值的范围 (可选) ； • 注释均采用 方式。 6.1.3 数据库对象 6.1.3.1 建表的参数设置 1. 不允许将表建立在 SYSTEM 表空间上。 2. 表和索引建立在不同表空间上。 3. 建表时必须指明所存储的表空间。 4. 生成建表脚本时非空的列放在表的前部，可空的列放置在表的后部。 5. max_parallel_workers ：表扫描中可以使用的并行进程个数。 83 第 6 章 业务系统开发建议 6. UNLOGGED：对于不在意数据是否持久，丢失修改没有影响的表，可以设置这个参数。 7. 若数据量非常大，但是访问存在热数据，就可以进行分区。 6.1.3.2 主外键设计 1. 必须设置主键，通常不使用存在实际意义的列做主键，具体情况应结合业务特性综合考虑。 2. 由 Sequence 产生的 ID 列，不作为组合 PK 中的列。 3. 删除约束时使用 keep index 参数。 4. 唯一性约束用于限定表中记录的唯一性，允许为空，允许对创建唯一性约束的列进行修改操作。 5. 检查 (check)，一般使用于列的取值受限于一些特定值的情况下，如员工的性别，年龄，贷款的状态等。 6. 触发子约束，一般情况下不要使用。 6.1.3.3 列设计 1. 定长字符型列使用 CHAR 类型，不定长字符型列使用 VARCHAR2 类型。 2. DATE 精确到微秒。 6.1.3.4 临时表 1. 对于只对本事务有效的临时表使用 ON COMMIT DELETE ROWS 关键字创建该表。 2. 对于只对本会话有效的临时表使用 ON COMMIT PRESERVE ROWS 关键字创建该表。 6.1.3.5 索引 1. 选择使用普通 B 树索引。 2. 小表（数据量小于 5000 条记录为标准）不需要创建索引。 3. 对于 OLTP 应用，分区表使用分区索引。 4. 分区索引必须包含分区列，分区列按序放置在分区索引的末尾。 5. 建立分区索引必须指明表空间。 6. 单个表上索引的个数不超过 5 个。 7. 将记录差别数最多的列放在索引顺序的最前面。 8. 索引数据的重复率不能超过 20%。 9. 进行 order by column desc 排序时，创建 column desc 索引。 84 第 6 章 业务系统开发建议 6.1.3.6 视图 1. 物化视图，主动刷新，间隔时间最小为 3 分钟。 2. 物化视图的基表必须创建主键。 6.1.3.7 存储过程、函数和包 1. 存储过程、函数和包中不建议使用 DML 或 DDL 语句。 2. 存储过程、函数和包建议有相应的出错处理功能。 6.1.3.8 触发器 1. 触发器的的选择必须与应用程序事务属性保持一致，以避免死锁发生。 2. 在有大量数据导入表中的情况下，不使用触发器。 6.1.3.9 序列 1. 加大序列的 cache 值。 2. 应用程序不要将任何商业逻辑建立在序列的完全连续性上。 3. 不要设置序列为循环使用，防止数据覆盖现象。 6.1.3.10 别名 1. 对于只读用户，必须创建与表相同名字的别名。 2. 别名的访问顺序：public 别名 -> private 别名 -> 与表同名的对象。 6.1.3.11 Database Link 1. 只允许从其它数据库中查询少量数据时使用 dblink。 2. 不使用 dblink 更新其它数据库中的数据。 85 第 6 章 业务系统开发建议 6.2 PLSQL 开发规则 6.2.1 总体开发原则 1. 完全按照设计文档进行开发； 2. 程序模块内聚度要高；外联度要低。 3. 要有正确、全面的故障对策。 4. 程序编写结构合理，条理清晰。 5. 程序名称要按照统一的命名规则进行命名。 6. 要充分考虑程序的运行效率，包括程序的执行效率和数据库的查询、存储效率。在保证应用的同时要尽量使用 效率高的处理方法。 7. 程序注释要详细、正确、规范。 8. 除非应用特别需要控制 commit 和 rollback 的提交时机，否则必须在存储过程结束时执行显式的 commit 或者 rollback 操作。 9. 程序处理尽量支持 7×24 小时；对于中断，应用程序提供安全、简单的断点再续处理； 10. 提供标准、简单的应用输出，为应用维护人员提供明确的进度显示、错误描述和运行结果；为业务人员提供明 确、直观的报表、凭证输出。 6.2.2 程序编写规范 6.2.2.1 PL/SQL 中的 SQL 编写规范 知识点描述 PL/SQL 是对 SQL 的过程化扩展。在标准 SQL 的基础上面增加了流程控制、游标、异常处理等机制。PL/SQL 是一种第四代的高级编程语言，它可以单独使用，也可以嵌入其他宿主语言一起使用。PL/SQL 使得用户对 KingbaseES 数据库的操作变得非常简单。 使用原则 1. 相关参数 1) plsql.compile_checks 该参数可以设为 off 和 on，缺省值为 off。 2) 使用情况 该参数用于 plsql 在对象创建时对象的合法性和数据的匹配性检查，比如引用的列名、表、视图、函数等 对象是否存在，对象赋值时类型是否匹配。 在进行开发的过程中，可设置为 on，用以检查模块的正确性。 86 第 6 章 业务系统开发建议 2. 如何使用可以提高效率 1) 尽量使用 RETURNING 子句 举例 使用 RETURNING 子句的例子： 更改某员工工资水平的同时返回更改员工的姓名和更改后的薪水。 \set SQLTERM / CREATE PROCEDURE update_salary (emp_id NUMBER) IS name VARCHAR2(15); new_sal NUMBER; BEGIN UPDATE employee_salary SET sal = sal + 10000 WHERE empid = emp_id RETURNING name, sal INTO name, new_sal; END; / 6.2.2.2 变量声明原则 1. 使用%TYPE 声明 变量类型声明时，如果其含义和某 students 表的 name 字段含义或某个变量相同时，使用%TYPE 声明。如： DECLARE stu_name students.name%TYPE; ...... 2. 使用%ROWTYPE 声明 记录类型声明时，如果其含义和某 students 表行数据或某 cursor 定义相同时，使用%ROWTYPE 声明，如： DECLARE one_stu students%ROWTYPE; ...... --或者和某 cursor 定义相同时 DECLARE CURSOR stu_cur IS SELECT * FROM students WHERE name LIKE '李%'; one_sf_stu stu_cur%ROWTYPE; ...... 3. 变量声明格式 87 第 6 章 业务系统开发建议 每行可以包含多条语句，例如： 推荐的写法： v_valid BOOLEAN; v_area VARCHAR2(20); 也可以写成如下形式： v_valid BOOLEAN; v_area VARCHAR2(20); 6.2.2.3 游标 知识点描述 为了处理 SQL 语句，KingbaseES 必须分配一片内存区域，用来存储完成该处理所必需的信息，这就是上下文区 域（CONTEXT AREA）。游标（CURSOR）就是一个指向上下文区域的句柄（handle）或指针。必须首先通过游 标定位某个位置，然后才能处理该位置的内容。 显式游标用来处理返回多于一行的 select 语句。显式游标可以由用户自定义，并通过 open，fetch，close 步骤来 调用。 隐式游标用于处理 insert，update，delete 和单行 select…into 语句。隐式游标是系统自定义的，通过 PL/SQL 引擎打开和关闭，因此，隐式游标不需要用户 open，fetch，close。 游标变量动态地指向 sql 语句。它类似于 pl/sql 变量，在运行时刻可以拥有不同的取值，因此可以实现在运行时 刻与不同的语句相关联。 游标属性附加在游标名字的后面，但游标属性返回的不是类型，它返回的是在表达式中可以使用的数值。游标属 性分以下四种： %FOUND: 这是一个布尔属性。如果前一个 fetch 语句返回一个行，那么它就会返回 true，否则，返回 false。 %NOTFOUND: 这是一个布尔属性，如果前一个 FETCH 语句返回一个行，返回 FALSE。仅当前一个 FETCH 语句没有返回任何行，才返回 TRUE。 %ISOPEN: 这是一个布尔属性，用来确定相关的游标是否被打开。如果已打开，返回 TRUE，否则，返 回 FALSE。 %ROWCOUNT: 这是个数字属性，它返回到目前位置，由游标返回的行的数目。 使用原则 1. 相关参数 1) ora_open_cursors(仅在 PLSQL 中生效) 2. 如何使用可以提高效率 88 第 6 章 业务系统开发建议 1) 空记录测试 在应用开发的过程中，经常需要测试表中是否含有符合某种条件的记录，一种常见的错误的做法是使用 count(*) 的方式判断其值是否为 0，这是一个极端低效率的做法，因为它要对全部数据扫描一遍。 推荐使用判断游标属性的方法。 原来的代码： SELECT count(*) INTO :v_tmp FROM … WHERE ... IF v_tmp >0 THEN ...... END IF; 改进的代码： CURSOR c1 is SELECT FROM ... WHERE ... OPEN c1; FETCH C1 INTO R; If c1%NOTFOUND THEN ...... END IF; 6.2.2.4 集合 知识点描述 集合是一组类型相同的元素。每个元素有一个唯一的下标，下标确定元素在集合中的位置。 数组：数组包含固定数目（数组长度）的元素，可以在运行过程中改变数组长度。 嵌套表：嵌套表可以包含任意数目的元素，嵌套表使用顺序数字做为下标。 关联数组：类似于其他编程语言中的散列表，关联数组的下标可以是数字或者字符串。 使用原则 1. 使用情况 1) 嵌套表和关联数组的比较： 在许多方面嵌套表和关联数组有相似之处，例如：两种表数据类型拥有相同的结构，他们都使 用下标记号进行访问。 但是两者还是有一些不同之处： a. 关联数组适合于相对较小的集合，这些集合可以在内存构建。关联数组还有一个优点在于下 标的灵活性。关联数组的下标可以是负数，可以不是顺序的，而且可以是字符串而非数字。 2) 嵌套表和数组的比较： a. 在嵌套表中，可以删除任意元素，而不仅仅只能去掉最后一个元素。 89 第 6 章 业务系统开发建议 2. 如何使用可以提高效率 使用批量绑定提高性能所谓绑定就是指为 SQL 语句中的 PL/SQL 变量赋值。PL/SQL 引擎执行过程控制 语句而将 SQL 语句传给 SQL 引擎，SQL 引擎执行 SQL 语句并将执行的结果返回给 PL/SQL 引擎。在 PL/SQL 引擎和 SQL 引擎之间进行频繁的上下文切换会严重影响性能。如下面的 PL/SQL 块所示，循环 执行某个 SQL 语句，该 SQL 语句是单独处理集合的一个元素。FOR 循环的每一次迭代，DELETE 语句 都会发往 SQL 引擎。KingbaseES 提供以下一些途径来减少 PL/SQL 引擎和 SQL 引擎之间的上下文切 换。 1) 使用 FORALL 语句。 FORALL 关键字通知 PL/SQL 引擎在将 PL/SQL 变量值传入 SQL 语句前进行批量绑定。虽然 FORALL 语句在形式上是循环迭代，但在实质上它不是 FOR 循环语句。FORALL 的语法格式如下，其中 index 只 能是集合的下标，sql_statement 必须有引用集合元素的 INSERT，UPDATE 或者 DELETE 语句： FORALL index IN lower_bound..upper_bound sql_statement; 2) 使用 BULK COLLECT 子句将查询结果返回到集合中。 关键字 BULK COLLECT 通知 SQL 引擎在将结果返回到 PL/SQL 引擎前进行批量绑定，这里的批量绑 定是指将输出结果绑定到集合中。BULK COLLECT 可以用在 SELECT INTO，FETCH INTO 和 RETURNING INTO 语句中。BULK COLLECT 的语法如下所示： ... BULK COLLECT INTO collection_name[, collection_name] ... 3. 使用过程之中会经常出现的错误以及解决方案 1) Reference to uninitialized collection a. 范例一： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums(1) := 1; -- raises COLLECTION_IS_NULL (1) END; 该例会报以上错误。 b. 原因： 定义集合之后，要求初始化集合。该语句在对集合中的元素操作（语句 1）之前，没有对集合进行初始化。 c. 解决建议： 对集合或集合中的元素进行操作前，首先初始化集合，例如，在语句（1）之前加上初始化语句：nums:= NumList(null,null); 2) collection type subscript in assignment must not be NULL 90 第 6 章 业务系统开发建议 a. 范例二： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums := NumList(1,3,6); -- initialize table nums(null) := 3 ; END; 该例会报错：collection type subscript in assignment must not be NULL b. 范例三： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null a VARCHAR2(2); BEGIN nums := NumList(1,3,6); -- initialize table a:='a'; -- (1) nums(a) := 3 ; -- (2) END; 该例会报错：invalid input syntax for type integer: ”a” c. 原因： 下标的类型不对，或者下标的转换未成功。 例如范例二中下标为”null”，不是 number 类型，也不能转换成 number 类型，因此会报以上错误。 例如范例三中下标 a 的值为’a’，该值不能转换成 number 类型，因此会报以上错误。 d. 解决建议： 将下标的值改为数字，或者 number 型变量，或者可以转换为 number 型变量的字符型变量。例如，将范例三 中的语句 (1) 改为 a:=’2’，或者直接将语句 (2) 改为 nums(2):=3。 3) collection type subscript outside limit a. 范例四： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums := NumList(0,0); -- initialize table（1） nums(0) := 3; -- （2） nums(3) := 3; -- （3） END; 91 第 6 章 业务系统开发建议 语句 2）和 3）都会报这个错误。 b. 原因： 初始化语句初始了 n 个元素，则下标的范围就是 1~n，超出该范围就会报错。 范例四中，初始化语句（1）初始化该集合有两个元素，因此，下标的范围为 1~2，超出这个范围就会报错。 c. 解决建议： 将下标的值改为正确的范围以内。 4) no data found a. 范例五： DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN nums := NumList(0,0); -- initialize table nums.DELETE(1); -- delete element 1 (1) IF nums(1) = 1 THEN dbms_output.put_line('successful!');--(2) END IF; END; 该语句会报以上的错误。 b. 原因： 语句 (1) 中已将 nums(1) 元素删去，nums(1) 元素已经没有数据了，因此语句（2）中读 num(1) 元素的值就会 报错。 c. 解决建议： 如果想让某元素中没有数据，可以给它赋空值，以后对该值的读取就不会出错。例如范例五中，语句（1）可以 换为：nums(1):=null。 举例 1. 下面的 DELETE 语句只发往 SQL 引擎一次，即使它执行了三次 DELETE 操作。 DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM emp WHERE deptno = depts(i); END; 92 第 6 章 业务系统开发建议 2. 在下面的例子的例子中，SQL 引擎将 EMP 表中所有的 EMPNO，EMPNAME 数据装载到嵌套表中，然后将 嵌套表返回给 PL/SQL 引擎。 DECLARE TYPE NumTab IS TABLE OF emp.empno%TYPE; TYPE NameTab IS TABLE OF emp.ename%TYPE; enums NumTab; -- no need to initialize names NameTab; BEGIN SELECT empno, ename BULK COLLECT INTO enums, names FROM emp; ... END; 6.2.2.5 动态 PL/SQL 知识点描述 大多数 PL/SQL 程序所做的工作都是特定的、预知的工作。例如，一个存储过程可能接受一个员工号码做为输 入参数，然后更新该员工的薪水。在这种情况下，UPDATE 语句的全文在编译时是确定的。这样的语句是不会在执 行阶段发生变化的，这种 SQL 语句称之为静态 SQL 语句。 但是有些时候，程序必须在执行阶段处理一个变化的 SQL 语句。例如，一个通用的报表程序为了产生不同的报 表必须运行不同的 SQL 语句。这种语句很可能在执行阶段发生改变，这种 SQL 语句称之为动态 SQL 语句。 动态 SQL 语句存储在字符串中，程序在执行阶段建立这个语句。这些字符串必须包含合法的 SQL 语句，要绑定 的变量前要加冒号，变量的名称是无关的。例如，下面两个语句是没有区别的： 'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm' 'DELETE FROM emp WHERE sal > :s AND comm < :c' 拼装 SQL 语句执行的性质与动态 SQL 语句相同。如果拼装 SQL 语句的源如果有些是别人输入的、外部文件读 取的，则不能直接拼上去，必须先检查，防止攻击。 防止攻击的办法有： 1. 尽量使用绑定变量的的动态 sql； 2. 对输入条件进行字符检查 使用原则 1. 使用限制 由于使用动态 sql，在编译阶段是分析不出该语句的。因此每次执行都需要编译，对系统运行产生额外的 性能开销。所以，不建议联机系统的交易程序使用动态 SQL。 2. 使用情况 93 第 6 章 业务系统开发建议 由于在通常的 PL/SQL 中只能使用数据操作语言 DML 和事务控制语言 TCL 而不能使用其它一些命令如 数据定义语言 DDL 和系统控制命令，当出现这种需求是一个做法是调用外部任务，另外一个做法就是使 用动态的 SQL。 动态 SQL 并不在客户端被解释转换，而是由客户端的程序传输到服务器端后在服务器端解释并执行。 使用动态 SQL 的最常见的用法有：使用 truncate table 命令代替 delete from table 的操作、在系统进行 日结、月结、年终结算时创建新的数据表，创建应用系统使用的临时表等， 下面是通过动态 SQL 来使用 truncate table 命令的方法： ... CREATE OR REPLACE PROCEDURE p_truncate_table (table_name varchar2(30)) as cursor_handle INTEGER; no_query_sql_command VARCHAR2(200); rc int; BEGIN no_query_sql_command := 'truncate table ’||table_name; cursor_handle:=dbms_sql.open_cursor; dbms_sql.parse(cursor_handle,no_query_sql_command,1); rc :=dbms_sql.execute(cursor_handle); dbms_sql.close_cursor(cursor_handle); END; / ... 也可以直接使用 EXECUTE IMMEDIATE 来执行，如： ... BEGIN EXECUTE IMMEDIATE 'truncate table ’||table_name;; END; ... 应当说明的是动态 SQL 的使用在不同的版本间稍有不同，在使用是需要在不同的版本上做测试。 3. 如何使用可以提高效率 1) 尽量使用变量绑定 联机交易的事务特点是执行的频度高，执行时间短。此类交易的 sql 语句都建议使用变量绑定。因为使用 变量绑定的方式编写的 sql 语句，能大大减少 sql 语句硬分析的性能消耗。 2) 使用批量动态 SQL（BULK FETCH 语句，BULK EXECUTE IMMEDIATE 语句，FORALL 语句， COLLECT INTO 语句，RETURNING INTO 子句） 4. 使用过程之中会经常出现的错误以及解决方案 1) “syntax error at or near ”:”” 94 第 6 章 业务系统开发建议 a. 原因： 在动态 PL/SQL 中数据对象名称不能使用变量绑定。 例如：下面的存储过程 CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name; END; b. 解决方法： 将变量绑定改为直接字符串拼凑。 CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END; 举例 1. 在动态 SQL 中使用变量绑定的例子 CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num' USING emp_id; END; 6.2.2.6 对象 知识点描述 对象类型（object）是一个用户定义的复杂的数据类型，其数据结构包含一些变量，以及对这些变量进行处理 的函数和过程。组成数据结构的变量，称为“属性”（attributes）。对属性进行操作的过程和函数，称为“方法” (methods)。当创建一个对象时，实际上是创建了一个对真实世界中的对象的抽象模板。例如，对一个雇员创建对 象，这个对象包括的属性有：姓名，部门，职位，等等；包括的方法有：工资计算，考勤计算，等等。 使用对象时，需要创建实例（instance），通过实例来调用对象中的方法。在真实世界中，不难理解这样做的目 的。还是以雇员对象为例，只有一个对象，确有很多个雇员，每个雇员的属性都不相同，所以，要为每个雇员创建实 例，他们可以共享“雇员对象”这个数据结构，而分别拥有自己的数据。 使用原则 1. 使用过程之中会经常出现的错误以及解决方案 1) method dispatch on NULL SELF argument is disallowed a. 范例： 95 第 6 章 业务系统开发建议 DECLARE Eagle employee; --创建实例 Jacky employee; --创建实例 BEGIN Dbms_output.put_line(Eagle.salary(eagle.rank,eagle.dept)); --通过实例调用对象中的方法 END; 该语句会报以上错误。 b. 原因： 实例已创建，但没有初始化。 c. 解决建议： 在调用方法前，初始化实例。以上范例中，加上初始化语句： Eagle := NEW employee('EAGLE',24,7,'TACH'); 举例 例一：创建对象 CREATE OR REPLACE TYPE employee AS OBJECT ( -- 定义属性（attribute） Name VARCHAR2(40), -- 定义方法 (method) MEMBER FUNCTION salary (rank NUMBER,dept VARCHAR2) RETURN NUMBER ); CREATE OR REPLACE TYPE BODY employee AS MEMBER FUNCTION salary (rank NUMBER,dept VARCHAR2) RETURN AGE IS BEGIN ...... END salary; END; 例二：使用对象 DECLARE Eagle employee; --创建实例 Jacky employee; --创建实例 96 第 6 章 业务系统开发建议 BEGIN Eagle := NEW employee('EAGLE',24,7,'TACH'); --初始化实例 Jacky := NEW employee('JACKY',36,1,'MANAGE'); --初始化实例 Dbms_output.put_line(Eagle.salary(eagle.rank,eagle.dept)); --通过实例调用对象中的方法 END; 6.2.2.7 LOB 类型 知识点描述 一个 LOB 是一个存储大量数据的数据库字段，例如图形文件或长的文本形式的文档。KingbaseES 提供了四种不 同类型的 LOB：CLOB、NCLOB、BLOB、BFILE。 LOB 类型 说明 CLOB 用于存储单字节字符数据 BLOB 存储没有结构的二进制数据 NCLOB 用于存储定宽的多字节国家字符集数据 BFILE 用于对 KingbaseES 数据库以外存储的大型二进制文件进行只读形式的访问。 使用原则 1．使用情况 LOB 是用于存储非结构化数据的。所谓非结构化数据是指不能被分解为标准组件的数据。例如，一个员 工可以分解为姓名（字符串），标识（数字），薪水等等。但是，如果给的是一张图片，会发现数据是由 一长串 0 和 1 的数字流，在数据存储方面，他们不能分解为良好的结构。非结构化的数据往往是非常大 的，如文本、图片、视频片断或者音乐等等。一个典型的员工纪录可以有几百个字节，但是即使是少量的 多媒体数据也可能有它几千倍那么大。 LOB 类型帮助支持 Internet 应用，随着 Internet 和内容丰富应用的发展，数据支持这样一种数据类型是 非常有需要的：1）可以存储非结构化数据；2）优化处理大量的非结构化数据；3）为存储在数据库内或 者数据库外非结构化数据提供统一的访问方式。 由于 BLOB 没有字符集转换，因此当几种 LOB 都能满足应用要求时，推荐使用 BLOB。 2．如何使用以提高效率 1) 适时的释放临时 LOB。 在 PL/SQL，C(OCI) 和 Java 中，当 SQL 语句在 LOB 列上面操作时会返回临时 LOB 作为结果，临时 LOB 在 PL/SQL 块结束时会自动释放。 97 第 6 章 业务系统开发建议 可以在任何时候释放不需要的临时 LOB，以释放系统系统资源和临时表空间。如果不合理的释放 SQL 语 句返回的临时 LOB，临时表空间将会被过度的消耗，而系统性能将会下降。 举例 1.PLSQL 使用 dbms_lob 包，操作 BLOB 的例子。 plsql 操作 blob 的例子 create or replace function append_chars() return BLOB as myblob BLOB; begin dbms_lob.createtemporary( myblob, true); dbms_lob.append(myblob,'\x414243'::BLOB); dbms_lob.append(myblob,'\x444546'::BLOB); return myblob; end; / -- 表中初始数据 create table dbms_lob_test(id int, data blob ); insert into dbms_lob_test values (1, append_chars()); select * from dbms_lob_test where id = 1; / kingbase-# / id | data ----+-------1 | ABCDEF (1 row) declare x blob; begin select data into x from dbms_lob_test where id = 1; -- 修改 dbms_lob.writeappend(x, 2, 'GH'); -- 写入表中 update dbms_lob_test set data = x where id = 1; raise notice '%', x; end; / -- 查询结果 select * from dbms_lob_test where id = 1; 98 第 6 章 业务系统开发建议 / kingbase-# / id | data ----+---------1 | ABCDEFKK (1 row) 6.2.2.8 PACKAGE(包) 知识点描述 包是一个可以将相关对象存储在一起的 PL/ SQL 结构，包含了两个分离的部件：规范 (specification) 和主体 (body)，每个部件都单独被存储在数据字典中。包中可以包含过程、函数、游标和变量。将这些对象放入一个包中的 一个好处就是可以从其他的 PL/SQL 块中引用它们，这样包也提供了用于 PL/SQL 的全局变量。 使用原则 1．使用情况 有以下情况发生时，需要使用包： 1) 存在私有函数（或过程）或私有变量，只能被某一函数（或过程）调用时，将这个私有的函数（或过 程）或私有变量放入调用主函数（或过程）所在的包内。 2) 函数（或过程）的参数里有自定义的类型时，应该建立包，将该类型的定义和该函数（或过程）的定义 都放入包中。 3) 直观的说，当具有一些能够处理相关的任务的程序代码同时希望包装共享该代码时可以使用包。 2. 如何使用可以提高效率 1) 使包尽量大众化，使其可以在未来的应用中被重用。 2) 尽量使用 KingbaseES 提供的包，注意不要重复创建 KingbaseES 已有的包。 3) 包头反映出应用的设计。应该在包头只声明用户或其他开发人员必须用到的类型，项和子程序，而将包 自己用的对象作为私有变量放在包体中。 4) 减少包中静态的全局变量的使用，避免操作的相互影响。 6.2.2.9 故障处理规则 知识点描述 PL/SQL 中，一个警告或错误条件被称为“异常”，这个条件可以是系统本身的，也可以由用户自定义。当满足 条件时，程序从当前块中跳出，进入异常处理块，程序的异常处理由用户自己定义。 使用异常处理的目的：防止错误的结果被带到外部程序，并且使出错的程序异常处理后退出，而不是中断后直接 返回操作系统，从而提高应用程序的健壮性。 99 第 6 章 业务系统开发建议 使用情况 在使用的 PLSQL 块中，如果使用了不能返回的确定结果的 SQL 语句，应用要在 EXCEPTION 中对程序可能出 现的异常进行处理，避免出现未处理的出错被传递到外层块，导致程序逻辑错误。 对 于 系 统 已 经 定 义 了 的 异 常， 应 用 可 以 直 接 使 用， 对 于 用 户 自 行 定 义 的 异 常 需 要 使 用 pragma excep- tion_init(exception_name，-SQLCODE) 方式来初始化。 对于继续处理的 EXCEPTION，可以不记录错误堆栈，对于无法继续处理的程序，EXCEPTION 中至少要获取 程序的返回码，返回错误码，错误描述，其名称分别定为 out_retcode，out_errcode，out_errm。为了能够进一步 分析程序出现异常的原因，最好能够获得出现异常时的调用错误堆栈。 常见错误及解决方案 很多情况下，特别是含有 DML 语句的程序中，系统希望一个存储过程是一个原子操作，要么不执行，要么完全 执行。由于出错后程序跳出当前块，进入异常处理块，所以有可能因为程序只执行了一半而造成数据不一致。为防止 这种情况发生，可以将 commit 尽量放在块的末尾，或者可能发生出错的地方之后。并在异常处理中使用 rollback。 将没做完的事务回滚。 在应用设计时应该设置一个错误信息表，出错后，必须向这个表写入错误信息。写入的方式建议使用独立会话， 把错误信息插入到错误信息表。 举例 EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; out_retcode:='L9003'; out_errcode:=SQLCODE; out_errm:=SQLERRM; < 把错误代码插入到错误信息表> RETURN; WHEN OTHERS THEN ROLLBACK; out_retcode:='L9000'; out_errcode:=SQLCODE; out_errm:=SQLERRM; < 把错误代码插入到错误信息表> RETURN; 100 第 6 章 业务系统开发建议 6.3 SQL 语句编写规则 6.3.1 查询语句的使用原则 6.3.1.1 使用正确索引 合理的使用正确的索引是提高系统执行效率的关键因素，对索引的使用需要注意以下一些问题： 1. 过度索引 一般情况下，使用索引可以缩短查询语句的执行时间，提高系统的执行效率，但是要避免以下两种过度索 引的情况出现： 1) 对一个表建立了过多的索引，从而造成维护索引所需要的时间超过使用索引所降低的时间，从而造成整 个系统效率的下降，这一般发生在对一些进行大量更新的表上面。因此一个联机表上的索引，最多不要超 过 5 个。 2.LIKE 运算符 在应用程序中为了使用方便，对字符型变量进行比较时经常使用 LIKE 运算符进行字符模式的匹配。 需要注意的是对于 LIKE 运算，如果通配符% 出现在字符串的尾部或中间，LIKE 运算符将可以使用索引 进行字符串的匹配，否则如果通配符% 出现在字符串的开始，则 LIKE 必须使用全表扫描的方式去匹配字 符串，这将产生较大的系统负荷。 一般情况下，为了提高系统的效率，可在通配符的左端提供较多的数据信息以降低查询的数量。 3.NULL 值 NULL 值是系统中目前尚无法确定的值，在 KingbaseES 数据库系统中 NULL 是一个比所有的确定值都 大的值，然而又不能用大于小于等于运算符来比较，对 NULL 值的处理只能用是与否来判定，所有的对 NULL 值的判定都会引起全表扫描，除非同时使用其它的查询条件。 4. 复合索引 复合索引是使用多个数据列的索引，第一个字段的数据区分度非常重要，也是影响一个联合索引效率的关 键所在。 6.3.1.2 改写查询语句 1. 关联子查询与非关联子查询 对于一个关联子查询，子查询是每行外部查询的记录都要计算一次，然而对于一个非关联子查询，子查询 只会执行一次，而且结果集被保存在内存中。 因此，通常在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；而子查询中只有 少量的记录的时候，则非关联子查询将会比关联子查询执行得更快。 2. 尽量用相同的数据类型的数据进行比较，以避免发生数据转换 101 第 6 章 业务系统开发建议 SQL 语言对于数据类型不像 JAVA 和 C++ 那样进行严格的数据类型检查，不同种数据间可以进行某些 运算，但是在做数据操作时需要数据库进行隐含的类型转换，在大数据量的查询中，由于要对每一个数据 项做同样的操作，会造成时间和 CPU 处理能力的浪费。 实际应用中通常发生的隐含的数据类型的转换有： 1) 字符型到数字型的转换，如：SELECT ‘1234’+3 FROM DUAL 等 2) 数字型到字符型的转换，如：UPDATE DEPT SET EMPNO=5678 等 3) 日期型到字符型的转换，如：UPDATE EMP SET DNAME=current_date（）等。 上述的转换都是隐含发生的，在实际使用中要避免使用不同类型的数据操作。 6.3.1.3 减少排序发生 排序是数据库中执行频度比较大的一种操作，根据排序执行的范围不同又可以分为内排序和外排序。数据库中的 排序操作的数量应该尽量的减少同时每个排序的时间能够缩短。为此可以： 1. 使用 UNION ALL 来代替 UNION 2. 添加索引。在表连接的时候使用索引可以避免排序的发生。 3. 在 DISTINCT，GROUP BY，ORDER BY 子句涉及到的列上创建索引。 4. 使用较大 work_mem。 6.3.1.4 使用并行查询 并行查询适合下列情况： 1. 全表扫描的处理 2. 聚集函数的处理 6.3.1.5 减少死锁发生 应用开发中，事务尽可能小，尽量避免大事务，频繁提交的大事务会明显带来并发的降低。 批量处理的任务最好能较少执行频率，且最好放在访问量低的时候做。 避免死锁。 两到多个事务相互争用资源时，可能发生死锁。支持自动检测死锁，并且自动处理。 例如：事务 T1 已经获得表 A 上的排它锁（如成功更新表 A 上的列 a：update A set a = 1;），事务 T2 已经获 得表 B 上的排它锁（如成功更新表 B 行的列 a：update B set a = 1;），现在 T1 申请表 B 上的排它锁（如尝试更 新表 B 上的列 a：update B set a = 1;），由于冲突，发生锁等待，这时 T2 申请表 A 上的排它锁（如尝试更新表 A 上的列 a：update A set a = 1;），也冲突，形成死锁。这时 KingbaseES 检测到 T1 和 T2 发生死锁，强制回滚 T2 ，解决死锁。 102 第 6 章 业务系统开发建议 死锁的危害：降低系统并发性和用户体验。 如何避免死锁：在不同的事务中对表的操作顺序保持一致。比如在两个方法中都对 order 和 order_detail 进行更 新，那么要保证两个方法中两个表的更新顺序是一致的。 6.3.1.6 使用集合运算符 KingbaseES 数据库的集合运算包括: UNION，UNION ALL，INTERSECT 和 MINUS 操作。 一般情况下当两个集合中的数据都比较多时，集合运算都是比较耗时的操作，使用时需要谨慎小心。如果可能， 可以使用 UNION ALL 操作代替 UNION 操作。 6.3.1.7 使用连接方式的原则 1. 嵌套循环连接（NESTED LOOP） 知识点描述 嵌套循环连接操作关系到两个表，一个内部表和一个外部表。KingbaseES 比较内部数据集的每一条记录和外部 数据集的每一条记录，并返回满足条件的记录。 嵌套循环连接通常会产生巨大的数据量，所以对嵌套循环连接的使用要有严格的限制。 当一个嵌套循环连接的内部表中存在索引的情况，嵌套循环连接变为改进的有索引的嵌套循环连接 (NESTED LOOP)，通常有索引的嵌套循环连接在产生较小的数据量的情况下可以较快的执行。 在使用有索引的嵌套循环连接是必须确保在查询中使用了正确的驱动表和正确的驱动数据集，通常情况下使用包 含数据量较小的表作为驱动表。 一般如果使用基于成本的优化器，系统会自动选择驱动表，如果是使用基于规则的优化器，则后表作为驱动表。 应用原则 一般的嵌套循环连接的速度较慢，产生的数据量较大，应该严格控制其使用。 在使用有索引的嵌套循环连接时，必须保证其驱动表有合适的索引，最好为主键或唯一键，同时希望在另外一张 表在相同的列上有索引。 举例 下面给出了两种连接的例子： 对于不存在索引的表 EMP 和 DEPT 执行以下操作： # \d student Column | Type | Collation | Nullable | Default --------+----------------------------+-----------+----------+--------sno | integer | | | sname | character varying(20 char) | | | 103 第 6 章 业务系统开发建议 ssex | character varying(5 char) | | | # \d score Column | Type | Collation | Nullable | Default -------+---------+-----------+----------+--------sno | integer | | | cno | integer | | | degree | numeric | | | explain select * from student, score where student.sno = score.sno and student.sno = 5; QUERY PLAN --------------------------------------------------------------------Nested Loop (cost=0.00..44.06 rows=24 width=126) -> Seq Scan on score (cost=0.00..24.88 rows=6 width=40) Filter: (sno = 5) -> Materialize (cost=0.00..18.89 rows=4 width=86) -> Seq Scan on student (cost=0.00..18.88 rows=4 width=86) Filter: (sno = 5) (6 rows) 为 NESTED LOOP JOIN，因为数据库需要对表 DEPT 中所有的行进行处理。如果此时 JOB 表的 JOBNO 列 上有索引则上述查询的方式转变为有索引的嵌套循环连接 (Nested Loop). create index student_ind1 on student(sno); explain select * from student, score where student.sno = score.sno and student.sno = 5; QUERY PLAN ----------------------------------------------------------------------------------Nested Loop (cost=10000000000.13..10000000033.08 rows=6 width=126) -> Index Scan using student_ind1 on student (cost=0.13..8.15 rows=1 width=86) Index Cond: (sno = 5) -> Seq Scan on score (cost=10000000000.00..10000000024.88 rows=6 width=40) Filter: (sno = 5) (5 rows) 2. 散列连接 (Hash Join) 知识点描述 散列连接将驱动表加载进内存，使用散列技术将驱动表与较大的表进行连接，连接过程中，对大表的访问使用了 散列访问。散列连接可以提高等连接的速度。 如果可用的散列空间内存足够大，可以加载构建输入，那么散列连接算法能够很好地运行简单的散列连接. 应用原则 一般的散列连接发生在一个大表和一个小表做连接的时候，此时小表中的数据全部被读入内存，其处理的速度较 快。 104 第 6 章 业务系统开发建议 举例 explain select * from student, score where student.sno = score.sno; QUERY PLAN ----------------------------------------------------------------------Hash Join (cost=25.98..198.70 rows=4224 width=126) Hash Cond: (score.sno = student.sno) -> Seq Scan on score (cost=0.00..21.90 rows=1190 width=40) -> Hash (cost=17.10..17.10 rows=710 width=86) -> Seq Scan on student (cost=0.00..17.10 rows=710 width=86) (5 rows) 3. 合并连接 (Merge Join) 知识点描述 合并连接是指从目标表中读取两个记录数据集，并使用连接字段将两个记录集分别排序的操作。合并过程将来自 一个数据集的每一条记录同来自另一个数据集与之匹配的记录相连接，并返回记录数据集的交集。 合并连接有几种不同的排序方法：外部合并连接，反合并连接和半合并连接。这些不同的排列方法使得合并步骤 服务于不同的目的，可以是找到记录数据集的交集，也可以是找到满足 SQL 语句中 WHERE 子句条件的那些记录。 应用原则 一般的合并连接是在散列连接不能达到应用的要求或 KingbaseES 优化器认为排序合并连接效率更高的情况下使 用。并且只有当数据表之间的连接是等值连接而不是其它连接时，排序合并连接才能被使用。 举例 在下述的查询中 explain select * from student, score where student.sno = score.sno;; QUERY PLAN ---------------------------------------------------------------------Merge Join (cost=83.80..90.07 rows=30 width=49) Merge Cond: (student.sno = score.sno) -> Sort (cost=1.11..1.12 rows=5 width=9) Sort Key: student.sno -> Seq Scan on student (cost=0.00..1.05 rows=5 width=9) -> Sort (cost=82.69..85.66 rows=1190 width=40) Sort Key: score.sno -> Seq Scan on score (cost=0.00..21.90 rows=1190 width=40) (8 rows) 6.3.1.8 复杂查询的原则 1. 限制表连接操作所涉及的表的个数 105 第 6 章 业务系统开发建议 对于数据库的连接操作操作，可以简单的将其想象为一个循环匹配的过程，每一次匹配相当于一次循环，每一个 连接相当于一层循环，则 N 个表的连接操作就相当于一个 N-1 层的循环嵌套。 一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越复杂，其执行的效率就越低，为此 需要尽可能的限制参与连接的表的数量。 1) 3-5 个表的处理方法 对于较少的数据表的连接操作，需要合理的确定连接的驱动表，从某种意义上说，确定合理的驱动表就是确定 多层循环嵌套中的最外层的循环，可以最大限度的提高连接操作的效率，可见选择合适的驱动表的重要性。 RBO 模式下，在 SQL 语句中 FROM 子句后面的表就是要进行连接操作的数据表，KingbaseES 按照从右到左 的顺序处理这些表，让它们轮流作为驱动表去参加连接操作，这样可以把包含参与连接的数据量最少的表放在 FROM 子句的最右端，按照从右到左的顺序依次增加表中参与连接数据的量。 CBO 模式下，则不需要考虑表放置的位置。 2) 5 个表以上的处理方法 对于涉及较多的表 (>5+) 的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路 径的个数与连接的表的个数的阶乘有关：当 n=5 时存取路径＝ 1X2X3X4X5=120 个，而当连接的表的个数为 6 时存取路径变为 1X2X3X4X5X6=720 个，数据库优化器对于数据的存取路径的判断近乎为不可能，此时完全依 赖与用户的语句书写方式。 对于较多的表的连接，要求开发人员查询返回的结果能够有所预测，同时判断出各个参与连接的表中符合条件 的记录的数量，从而控制查询的运行时间。 同时为了提高查询的效率，此时可以把部分表的一些连接所形成的中间结果来代替原来的连接表，从而减少连 接的表的数目。 3) 对表连接操作涉及的表数目不应多于 8 个表 如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控性降低，容易引起数据库的运行效率低 下，即使在开发测试环境中已经经过充分的测试验证，也不能保证在生产系统上由于数据量的变化而引发的相 关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。 2. 限制嵌套查询的层数 应用中影响数据查询的效率的因素除了参与查询连接的表的个数以外，还有查询的嵌套层数。对于非关联查询， 嵌套的子查询相当于使查询语句的复杂度在算术级数的基础上增长，而对于关联查询而言，嵌套的子查询相当于使查 询语句的复杂度在几何级数的基础上增长。 因此，降低查询的嵌套层数有助于提高查询语句的效率。 对嵌套查询层数的限制要求：如果查询语句拥有过多的嵌套层数，那么会使该查询语句的复杂度高速增加，应该 在数据库设计阶段就避免这种情况出现，不应多于 5 层。 3. 灵活应用中间表或临时表 在对涉及较多表的查询和嵌套层数较多的复杂查询的优化过程中，使用中间表或临时表是优化、简化复杂查询的 一个重要的方法。 106 第 6 章 业务系统开发建议 通过使用一些中间表，可以把复杂度为 M*N 的操作转化为复杂度为 M+N 的操作，当 M 和 N 都比较大时 M+N «M*N，则查询的复杂度被大大地降低。 下面显示了一个使用中间结果集来替代多表操作的例子。 create table student_ng as select sno from score where degree <= 60; select * from student_ng ; sno ----2 3 (2 rows) delete from student where sno in (select * from student_ng ); select * from student; sno | sname | ssex -----+-------+-----1 | lq | f 4 | zl | m 5 | zs | m (3 rows) 上述 sql 等效于： delete from student where sno in (select sno from score where degree <= 60); select * from student; sno | sname | ssex -----+-------+-----1 | lq | f 4 | zl | m 5 | zs | m (3 rows) 4. 使用一些改写复杂查询的技巧 1) 转换连接类型 参见上文的改写查询语句部分。 2) 把 OR 转换为 UNION ALL 在某些场景下，比如多个条件 OR 之后无法使用索引的场景，将 OR 的每个条件拆解成独立的子查询，然后通 过 UNION ALL 来做一个集合操作，会使拆解后的子查询应用索引扫描，从而提升执行效率。 3) 区分不同的情况使用 IN 或 EXISTS 对于主查询中包含较多条件而子查询条件较少的表使用 EXISTS，对于主查询中包含较少条件而子查询条件较 多的表使用 IN。 4) 使用合理的连接方式 107 第 6 章 业务系统开发建议 在不同的情况下使用不同的连接方式：散列连接适用于一个较小的表和较大的表的连接，排序合并连接需要对 小表进行排序操作，索引的嵌套循环连接对于一般连接是有效的，但是需要在连接的关键字上有索引的存在。 应用开发人员应该根据不同的情况选取合适的连接方式。 • 使用并行查询 如果查询的数据在表中所占的比例较大，可以考虑使用并行查询来提高查询的执行速度。 • 使用 PL/SQL 过程和临时表代替复杂查询。 对于涉及巨大的表的连接的统计查询，由于可能会造成大量的排序统计工作，使得查询的速度变慢，此时可以 考虑使用 PLSQL 替代原来的查询。 6.3.2 DML 语句的调整原则 DML 语句包括 Insert、Update、Delete 和 Merge。在使用 DML 语句的时候，也会遇到性能低下的情况，可以 参考以下的内容来做出调整。 6.3.2.1 KingbaseES 存储参数的影响 KingbaseES 的 DML 语句出现性能问题的一些情况： 1. Insert 操作缓慢并且占用过多的 I/O 资源。 这种情况发生在 fillfactor 较高且行记录较大，频繁地寻找新的空闲数据块的时候。 在数据对象有 (多个) 索引的情况下，Insert 操作还需要对索引进行维护，这额外的增加了数据插入的成本，所 以对于没有过度索引的表的维护是比较花费资源的。 2. Update 操作缓慢。 Update 操作需要获得操作对象上的独占锁，如果其它的用户已经占有了该对象的非兼容的锁，那么 Update 操 作就需要等待，通常这是非常短的时间，但是如果该用户在操作时被打断，则该用户持有这个锁的时间就可能 变长，造成其它用户的等待，这是一个管理上的问题。 如果 Update 操作扩展了一个 Varchar 或 Blob 列导致发生了行迁移的时候，其更新也会变慢。 3. Delete 操作缓慢。 通常由于删除操作会产生大量的 redo 信息，所以对系统的性能的影响较大。如果可能可以使用更改状态标志和 在另外的表中插入新的记录来代替删除操作。 对于删除全表的操作可以用 truncate table 等命令来实现。 调整原则 请参考上文相关部分以适当地对这几个参数进行设置，以在有效空间利用和高性能之间获得一个平衡点。 108 第 6 章 业务系统开发建议 一般情况下对于并发更新比较频繁的对象要降低同一数据块中的数据行数以减少系统对于同一数据块的竞争，以 空间换时间以提高性能。对于并发更新竞争不是那么频繁的对象要提高同一数据块中的数据行数，以提高系统空间的 利用效率，同时提高缓存的利用率以达到提高系统整体效率的目的。 作为队列使用的表的竞争会比较剧烈，这类表中包含的总的数据行数不会太多，所以可以使用空间来换取效率。 6.3.2.2 大数据类型的影响 使用大数据类型（RAW，LONG，BLOB，CLOB 等）的时候，主要问题在于它们常常会超出普通的数据块大 小，导致数据列分散到相邻的数据块。这样，每行记录被访问时，KingbaseES 都会产生两次以上的 I/O 操作。 调整原则 对于使用大数据类型的表，一般情况下其数据的行连接是不可避免的，因此只能尽量的降低这种事件发生的频 率。 或者，使用 oid 类型，这种存储的大对象类型，和行中其他列不存在一起，可以降低更新行中其他类型的值带来 的大量 IO 影响。 或者，将表进行列式存储。 6.3.2.3 DML 执行时约束的开销 约束会对 DML 操作的性能产生影响。 1. 完整性约束：时间会耗费在验证每一个数据值是否合法上。 2. 主键约束：主键约束是由唯一索引来强制实施的，而且这个索引在插入和更新操作上的开销使大容量的插入操 作和更新操作运行变慢，因为每个值都必须从索引中查询一次。 3. 外键约束：强制实施了交互表之间的数据关系，必须访问外部的数据表以确认当前值是否合法，才能进行插 入。 4. 其它约束：对于其它检查的，数据也需要做相应的检查 5. 触发器：触发器对 DML 的执行效率也有较大的影响，特别当触发器的类型为 for each row 的时候。 调整原则 1. 在执行大容量的插入或更新任务时，可以暂时禁用所有与所影响的数据表有关的约束、触发器，装载数据，最 后才重新启用约束 2. 在启用约束时，需要考虑非法的数据。 6.3.2.4 DML 执行时维护索引所需的开销 在记录被插入和修改时，表上所有的参与索引都必须实时地进行更新。这通常会产生由于大量排序而增加的系统 开销，严重降低系统的执行性能。 109 第 6 章 业务系统开发建议 调整原则 1. 在大型的 DML 批操作中，在更改数据表之前，删除全部索引。在批操作之后，重新建立起索引。 2. ora_open_cursor 如果索引因为不平衡而产生拆分等额外操作，那么可以通过重建索引操作，也会减少维护索 引所需的时间。 110 第7章 7 第 章 其他数据库开发者转向 KINGBASEES 的注意事项 其他数据库开发者转向 KingbaseES 的注意事项 一、对象创建和修改 1. 对象名称，例如表名，最多 63 个字节，和 oracle 不同 2. 不支持在一个表中同时存在“ABC”和“abc”这种只是全大写和全小写的区别，其他都一致的字段名 3. varchar(char) 和 varchar（byte）是 2 个类型，所以尽量在一个项目中使用一类，不要交叉使用 4. Oracle 的存储管理，和 kingbase 不同，对应的存储属性不同 二、SQL 1. DDL 语句不是自动提交的，DDL 语句支持放到显式的事务中，并支持回滚 2. truncate 分区对表加排他锁，不支持同时访问，所以谨慎使用 3. 不支持回滚段，rowid 是物理的可变地址，使用 rowid 时，需要保证没有并发的修改这个元祖的事务提交；否 则，建议用 oid 类型的隐含列替代。 三、PLSQL 1. Oracle 同一个包中允许存在同名同参数的函数和存储过程，KingbaseES 不支持，请对于这种情况定义不同的名 字 2. 不支持方法的连续调用，例如不支持方法 1. 方法 2. 方法 3，需要分开调用，例如 var1 := 方法 1，var2:=var1. 方法 2, var3:=var2. 方法 3。 111 版权声明 版权声明 北京人大金仓信息技术股份有限公司（简称：人大金仓）版权所有，并保留对本手册及本声明的一切权利。 未得到人大金仓的书面许可，任何人不得以任何方式或形式对本手册内的任何部分进行复制、摘录、备份、修 改、传播、翻译成其他语言、将其全部或部分用于商业用途。 免责声明 本手册内容依据现有信息制作，由于产品版本升级或其他原因，其内容有可能变更。人大金仓保留在没有任何通 知或者提示的情况下对手册内容进行修改的权利。 本手册仅作为使用指导，人大金仓在编写本手册时已尽力保证其内容准确可靠，但并不确保手册内容完全没有错 误或遗漏，本手册中的所有信息也不构成任何明示或暗示的担保。 技术支持 • 人大金仓官方网站：http://www.kingbase.com.cn/ • 人大金仓文档中心：http://help.kingbase.com.cn/ • 全国服务热线：400-601-1188 • 人大金仓技术支持与反馈信箱：support@kingbase.com.cn 112 服务周期承诺 服务周期承诺 由于市场需求在不断变化，技术创新和发展的进程不断加剧，产品的版本更迭不可避免。人大金仓对于产品版本 生命周期的有效管理，有助于您提前规划项目，更好地从产品服务终止上过渡。 表 1: KingbaseES 产品生命周期里程碑 关键里程碑点 定义 产品发布日期 产品正式发布版本，即 GA（general availability）版本的发布日期。 停止销售日期 正式停止销售的日期，版本停止接受订单日。该日之后，产品将不再销售。 停止功能升级日期 在该日期之后，不再提供新特性和新硬件支持。但依旧提供错误修复、安全修复、功 能维护等服务。 停止功能维护日期 在该日期之后，不再维护功能，修复问题。但依旧提供安全修复等服务 停止安全维护日期 在该日期之后，不再发布补丁版本修复中高风险漏洞，仅提供有限的支持。 产品服务终止日期 停止提供产品服务和支持的日期。包括软件维护版本，缺陷修复，以及针对该产品的 所有服务支持（包括服务热线和远程/现场支持）。 服务周期策略 金仓数据库管理系统 KingbaseES 产品确保以下的服务周期： 1）产品自发布之日起至产品停止功能升级（包含新特性、新硬件支持）之日不少于 5 年。 2）产品停止功能升级之日起至产品停止功能维护（主要包括问题修复）之日不少于 4 年。 3）产品功能维护停止之日起至产品停止安全维护（包括中高风险漏洞修复）之日不少于 2 年。 服务终止策略 金仓数据库管理系统 KingbaseES 产品确保在销售后，至少提供 6 年的服务支持。 注意: 人大金仓将会综合各方因素来确定产品服务终止日期。并将在实际产品服务终止日期之前至少 90 天，通过公 113 服务周期承诺 开方式宣布产品服务终止日期。 114