KingbaseES数据库SQL调优指南.pdf

KingbaseES 数据库 SQL 调优指南 金仓数据库管理系统 KingbaseES 文档版本：V9（V009R001C001B0024） 发布日期：2023 年 10 月 12 日 北京人大金仓信息技术股份有限公司 目 目 录 录 第 1 章 前言 1 1.1 适用读者 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 相关文档 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 术语 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.4 手册约定 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 第 2 章 SQL 调优概述 4 SQL 调优基础 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.1.1 关于 SQL 调优 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.1.2 SQL 调优的目的 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.2 SQL 调优步骤 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.3 SQL 调优工具 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.3.1 诊断技能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.3.2 优化手段 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.1 第 3 章 查询优化器基础 3.1 3.2 8 SQL 基础简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3.1.1 SQL 语言特点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3.1.2 SQL 处理过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 查询优化器简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 逻辑优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 3.2.1.1 逻辑优化规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 3.2.1.2 逻辑操作符简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 物理优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 3.2.2.1 物理优化简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 3.2.2.2 代价估算模型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.2.2.2.1 代价估算 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 3.2.2.2.2 选择率计算 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 单表扫描代价估算 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 3.2.2.3.1 顺序扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 3.2.2.3.2 索引扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.2.1 3.2.2 3.2.2.3 I 目 录 两表连接代价估算 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 3.2.2.4.1 嵌套循环连接（NestLoop） . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 3.2.2.4.2 归并连接（MergeJoin） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 3.2.2.4.3 Hash 连接（HashJoin） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 优化器的局限性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 优化器统计信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 3.3.1 统计信息简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 3.3.2 统计信息内容 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 3.3.2.1 表级信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 3.3.2.2 列统计信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.3.2.2.1 基本统计信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3.3.2.2.2 数据分布信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.3.2.2.3 高频值示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.3.2.2.4 直方图示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 统计信息的收集 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 3.3.3.1 自动收集 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 3.3.3.2 主动收集 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 扩展统计信息 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 扩展统计信息介绍 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.3.4.1.1 函数依赖 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.3.4.1.2 多元 N-Distinct 计数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.3.4.1.3 多元 MCV 列表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 扩展统计信息使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 统计信息的局限性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 3.2.2.4 3.2.3 3.3 3.3.3 3.3.4 3.3.4.1 3.3.4.2 3.3.5 第 4 章 执行计划 4.1 4.2 39 查看执行计划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 4.1.1 使用 EXPLAIN 查看 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 4.1.2 使用对象管理工具查看 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 4.1.3 在日志记录中查看 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 阅读执行计划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 4.2.1 主要内容 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 4.2.2 执行顺序 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 4.2.3 计划节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 扫描节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 4.2.3.1.1 顺序扫描（Seq scan） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 4.2.3.1.2 索引扫描（Index scan） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 4.2.3.1.3 位图扫描（Bitmap scan） . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 4.2.3.1.4 TID 扫描（TID scan） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 4.2.3.1.5 覆盖索引扫描（Index only scan） . . . . . . . . . . . . . . . . . . . . . . . . 49 4.2.3.1.6 其他扫描过滤节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 连接节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 4.2.3.1 4.2.3.2 II 目 录 4.2.3.2.1 嵌套循环连接（NestLoop join） . . . . . . . . . . . . . . . . . . . . . . . . . 51 4.2.3.2.2 哈希连接（Hash join） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 4.2.3.2.3 归并连接（Merge join） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 物化节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 4.2.3.3.1 物化节点（Material） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 4.2.3.3.2 分组节点（Group） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 4.2.3.3.3 排序节点（Sort） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.2.3.3.4 去重节点（Unique） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.2.3.3.5 其他物化节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 控制节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.2.3.4.1 BitmapAnd/BitmapOr 节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.2.3.4.2 Result 节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 4.2.3.4.3 Append 节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 4.2.3.4.4 RecursiveUnion 节点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 分析执行计划 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 4.3.1 分析步骤 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 4.3.2 问题示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4.3.2.1 统计信息不准确 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4.3.2.2 缺少索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.3.2.3 连接方式不准确 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 4.3.2.4 基数估计不准 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 4.3.2.5 内存不够写临时文件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 执行计划的改变 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 4.2.3.3 4.2.3.4 4.3 4.3.3 第 5 章 SQL 优化手段 62 使用索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 索引类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 5.1.1.1 Btree 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 5.1.1.2 Hash 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 5.1.1.3 Bitmap 索引 (执行节点) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 5.1.1.4 Bitmap 索引 (索引 AM) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 5.1.1.5 GIN 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 5.1.1.6 GiST 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 5.1.1.7 SP-GiST 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 5.1.1.8 BRIN 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 索引使用技巧 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 5.1.2.1 使用表达式索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 5.1.2.2 使用局部索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 5.1.2.3 使用联合索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 5.1.2.4 使用索引提升 Like 模式匹配性能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 5.1.2.4.1 前匹配或者精确匹配使用 Btree 索引 . . . . . . . . . . . . . . . . . . . . . . . 73 5.1.2.4.2 后匹配 reverse() 表达式 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 5.1 5.1.1 5.1.2 III 目 录 中间匹配使用 TRGM 索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 5.1.2.5 删除不必要的索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 5.1.2.6 定期 vacuum 和重建索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 5.1.2.7 其他建议 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 使用索引建议工具 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 使用 HINT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 5.2.1 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 5.2.2 HINT 的功能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 5.2.3 HINT 的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 5.2.4 配置参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 5.2.5 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 5.2.5.1 常用 Scan 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 5.2.5.2 Parallel 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 5.2.5.3 ROWS 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 5.2.5.4 常用 Join 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 5.2.5.5 Leading 连接顺序 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 5.2.5.6 SET 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 5.2.5.7 Blockname 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 5.2.5.8 Materialize 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 5.2.5.9 Use_nl_with_index 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 5.2.5.10 Aggregate 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 5.2.5.11 Ordered 类型 HINT 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 5.2.5.12 HINT 混合使用示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 注意 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 调整性能参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 5.3.1 成本参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 5.3.2 节点开关参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 5.3.3 多表连接参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 5.3.4 内存参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 5.3.5 其他相关参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 5.1.2.4.3 5.1.3 5.2 5.2.6 5.3 5.4 使用并行 5.4.1 并行查询原理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 5.4.2 并行查询相关参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 5.4.3 并行查询支持的范围 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.4.4 5.5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 5.4.3.1 并行全表扫描 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 5.4.3.2 并行哈希连接 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 5.4.3.3 并行嵌套循环连接 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 5.4.3.4 并行聚集 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 5.4.3.5 并行排序 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 5.4.3.6 并行 Append . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Worker 进程数与查询性能关系 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 使用 Query Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 IV 目 录 5.5.1 概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 5.5.2 Query Mapping 的功能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 5.5.3 Query Mapping 的使用 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 5.5.4 示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 5.6 物化视图 5.7 分区表执行计划优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 5.8 逻辑优化规则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 5.9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 5.8.1 count(distinct) 查询优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 5.8.2 distinct 消除 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 SQL 优化建议 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 5.9.1 Union 转 Union All 建议 5.9.2 Not in 转 not exists 建议 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 第 6 章 SQL 优化组件 6.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 120 SQL 监控 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 6.1.1 SQL 监控的功能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 6.1.2 SQL 监控的目的 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 6.1.3 SQL 监控的示例 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 6.1.4 SQL 监控的外部接口 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 6.1.4.1 SQL 监控参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 6.1.4.2 DBMS_SQL_MONITOR 子程序 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 6.1.4.3 SQL 监控视图 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 版权声明 128 服务周期承诺 129 V 第 1 章 前言 1 第 章 前言 本文档主要讨论如何在 KingbaseES 数据库系统中通过提高 SQL 语句性能，以达到特定的、可测量的、可实现 目标的迭代调优过程。 前言部分包含以下主题： • 适用读者 • 相关文档 • 术语 • 手册约定 1.1 适用读者 KingbaseES 数据库 SQL 调优指南面向所有使用 KingbaseES 的用户，主要是数据库管理员和应用程序开发人 员。 1.2 相关文档 有关 KingbaseES 数据库性能调优指南的更多信息，请参阅以下资源： • KingbaseES 数据库概念，全面介绍本手册中使用的概念和术语 • 数据库管理员指南，了解有关管理 KingbaseES 数据库的信息 • SQL 语言，用于了解 KingbaseES sql 命令和函数的信息 • 数据库管理员指南，了解有关管理 KingbaseES 数据库的信息 • KingbaseES 插件参考手册 kwr，ksh，kddm 章节 1 第 1 章 前言 1.3 术语 术语 定义 KingbaseES 人大金仓企业级版本，本文指代单机服务版本，也被称作单机版或者 KingbaseES 单机版，单机版其成员可能包括数据节点（Data Node）、备份节点 （Repo Node）。 SYS_KWR SYS_KWR 是 KingbaseES 自动负载信息库（Kingbase Auto Workload Repertories）的简称，它通过周期性自动记录性能统计相关的快照，分析出 KingbaseES 的操作系统运行环境、数据库时间组成、等待事件和 TOPSQL 等性能指标，为 数据库性能调优提供指导。 SYS_KSH KingbaseES 引入了明细会话历史 (Kingbase Session History) 和相应的报告工 具。用户可以使用该工具进行会话历史的分析，并针对报告呈现的性能瓶颈进行 优化。 SYS_KDDM KDDM 是 KingbaseES 性能自动诊断和建议的报告。它基于 KWR 快照采集的 性能指标和数据库时间模型（DB Time），自动分析等待事件、IO、网络、内存 和 SQL 执行时间等，给出一系列性能优化建议。 1.4 手册约定 本文档中可能出现“注意、提示、警告、另请参阅”等标志，它们所代表的含义如下： 注意: 用于突出重要/关键信息、最佳实践等。 提示: 用于突出小窍门、捷径等。 警告: 用于传递设备或环境安全警示信息，若不避免，可能会导致设备损坏、数据丢失、设备性能降低或其 它不可预知的结果。 另请参阅: 用于突出参考、参阅等。 以下程序代码书写约定适用于本文档： 2 第 1 章 前言 符号 说明 [] 表示包含一个或多个可选项。不需要输入中括号本身。 {} 表示包含两个以上（含两个）的候选，必须在其中选取一个。不需要输入花括号本身。 | 分割中括号或者花括号中的两个或两个以上选项。不需要输入“|”本身。 ... 表示其之前的元素可以被重复。 斜体 表示占位符或者需要提供特定值的变量。 大写 表示系统提供的元素，以便与用户定义的元素相互区分。除出现在方括号中的元素外，应当按 照顺序逐字输入。当然，部分元素在系统中是大小写不敏感的，因此用户可以根据系统说明以 小写形式输入。 小写 表示由用户提供的元素。 3 第 2 章 SQL 调优概述 2 第 章 SQL 调优概述 随着互联网技术在商业应用中的广泛普及，构建与数据库之上的应用日益庞大复杂，随之数据库性能表现的重要 性也越发凸显。在数据库的众多性能问题中，SQL 性能问题占有极大的比例，因此 SQL 性能调优是数据库管理员进 阶需要掌握的一项重要技能。凭借多年的性能调优经验，KingbaseES 总结出了一套 SQL 调优方法论供读者使用。 本部分包含以下章节： • SQL 调优基础 • SQL 调优步骤 • SQL 调优工具 2.1 SQL 调优基础 本章节包含以下内容： • 关于 SQL 调优 • SQL 调优的目的 2.1.1 关于 SQL 调优 SQL 调优是诊断和优化未能满足性能指标的 SQL 语句的行为，需要不断的迭代，以满足特定的、可度量的、可 实现的目标。 2.1.2 SQL 调优的目的 当 SQL 语句无法按照预期性能指标执行时，就会成为一个我们需要解决的性能问题。 在确定问题所在后，典型的性能调优至少包含一个以下目标： • 减少用户响应时间，这意味着减少用户发出请求到接收请求的时间。 4 第 2 章 SQL 调优概述 • 提高吞吐量，这意味着使用最少的资源来处理请求。 考虑这样一个场景，对于一条执行时间为 30 秒的 SQL 语句，在硬件资源充足的情况下，考虑减少用户效应时 间，我们可以使用并行来提高执行效率；在硬件资源紧张的时候，考虑吞吐量，则需要通过索引、HINT 等手段来减 少资源的处理请求。问题不同则目标不同，目标不同则使用的手段也会不同。 2.2 SQL 调优步骤 在确定调优目标之后（例如将用户响应时间从 60 秒降低到 5 秒），问题就变成了如何实现此目标。 SQL 调优通常是一个迭代的过程，为了实现得到目标，典型的 SQL 调优需要不断的重复下述的一个或多个步 骤： 1. 识别高负载语句 通过查看过去的执行历史记录，找到消耗了较多系统资源的 SQL 语句。KingbaseES 提供的工具包括： • sys_stat_statement • kbbadger 以上两种工具使用方法请参见“KingbaseES 数据库性能调优指南”3.3.1 章节。 • SYS_KWR 使用方法请参见“KingbaseES 数据库性能调优指南”3.3.5 章节。 • SQL_MONITOR 使用方法请参见“KingbaseES 数据库 SQL 调优指南”6.1.1 章节。 2. 收集性能相关的数据 优化器统计信息是 SQL 调优的关键，如果统计信息不存在或不够及时准确，那么优化器将无法生成最优 的执行计划。与 SQL 性能相关的其他数据包括该语句访问的表和视图的结构，以及该语句可用的任何索 引的定义，方便后续性能问题原因的分析。 如何收集统计信息请参考本手册章节统计信息的收集 和扩展统计信息 3. 确定性能问题产生的原因 对于特定的 SQL 语句，要确定性能问题产生的原因，最重要的手段是分析执行计划。 分析方法参见本手册章节分析执行计划 通常来说，SQL 性能问题的原因包括： • 缺少 SQL 访问结构 缺少索引、物化视图、分区之类的 SQL 访问结构是导致 SQL 性能不佳的典型原因。最佳的访 问结构可以将 SQL 性能提高几个数量级。 索引部分请参考本手册章节使用索引 5 第 2 章 SQL 调优概述 物化视图请参考本手册章节物化视图 • 次优的执行计划 优化器的主要作用是给出最优（较优）的执行计划。有时候，因为优化器自身的局限，会选择 访问路径不理想的计划。例如，选择率较低的条件在大表上使用全表扫描，而不是索引；多表 查询的连接顺序未能让可以过滤更多数据的两表连接最先执行。 • 陈旧的优化统计数据 当统计信息维护操作（自动或手动）无法跟上 DML 导致的表数据更改时，收集到的统计信息 可能会过时。由于表上的陈旧统计信息无法准确反映表数据，因此导致优化器基于错误信息做 出决策并生成次优执行计划。 更多信息，参见本手册章节优化器统计信息 • 低效的 SQL 语句设计 如果编写 SQL 语句以使其执行不必要的工作，那么优化器将无法做很多事情来提高其性能。低 效设计的例子包括： • 忽略添加连接条件，导致笛卡尔积连接 • 指定 UNION 而不是 UNION ALL • 使子查询针对外部查询中的每一行执行 • 硬件问题 受限于当前硬件环境（CPU、内存、IO、网络等），所有的优化手段都不足以达到预期性能， 则需要做硬件扩展。 • 架构问题 单机硬件扩展无法解决的情况下，则需要考虑读写分离集群等方案。 4. 定义问题的范围 解决方案的范围必须与问题的范围相匹配。在数据库级别考虑一个问题和在语句级别考虑一个问题的解决 方案是不一样的。 例如，在单个 SQL 语句未使用有用的索引的情况下，更改整个数据库的优化器参数（enable_seqscan = off）可能会损害整体性能。如果单个 SQL 语句有问题，则可以考虑通过 HINT、改写语句等方式将影响 范围限制在语句级。 5. 实施优化手段 在步骤 3 中，我们可以得知 SQL 性能问题产生的原因较为多样，所以优化手段的实施也会因情况而异。 例如，我们可能会通过将 UNION 改写成 UNIONALL 来提高性能，也可能通过创建索引来提高性能。 6. 评估优化效果 在实施优化手段后，通过实际执行来评估优化效果是否达到了预期。 6 第 2 章 SQL 调优概述 2.3 SQL 调优工具 为了方便用户调优，在这里提供两类 SQL 调优工具： • 诊断技能 用来诊断 SQL 性能问题的理论。 • 优化手段 用来解决 SQL 性能问题的优化手段。 2.3.1 诊断技能 进行 SQL 性能优化前，需要掌握一些数据库的基础知识。本手册建议读者应具有下表所示的知识和技能。 • SQL 和 PLSQL 由于 GUI 的工具存在，所以很多数据库用户可以在不了解 SQL 的情况下开发应用和管理数据库，这在很 大程度上降低了数据库开发门槛。但是，如果不了解 SQL，就无法调优应用程序或数据库。 更多信息，请参考 KingbaseES 用户文档“《SQL 语言》和《PLSQL-SQL 过程语言》” • 数据库查询优化器基础 想要更好的进行 SQL 调优，最好掌握一些数据库查询优化器基础。了解优化器中逻辑优化和物理优化的 原理。 更多信息，请参见本手册， 查询优化器基础 • 执行计划 执行计划是 SQL 性能分析的重要工具，阅读和分析执行计划是确定性能问题原因的必要手段。 更多信息，请参见本手册， 执行计划 2.3.2 优化手段 KingbaseES 提供了多种手段来供用户做 SQL 调优，详见SQL 优化手段 ，常见的调优手段包括： • 使用索引 • 使用 HINT • 调整参数 • 使用并行 7 第 3 章 查询优化器基础 3 第 章 查询优化器基础 查询优化是 SQL 调优的重要理论基础，本章重点介绍相关的基础知识，主题包括： • SQL 基础介绍 • 查询优化器简介 • 优化器统计信息 优化器代价估计依赖于统计信息。优化器使用统计信息来估算从表或索引中检索出来的行数，从而确定候选计划 的成本，然后选择最低成本的执行计划。 注意: 本手册如无特殊说明，提供的示例都是在串行环境下执行，如果使用并行会在示例中有展示。 3.1 SQL 基础简介 本小节主要介绍了 SQL 基础，旨在阐述： • SQL 语言的特点 _ 为什么需要进行 SQL 调优 • SQL 处理过程 从 SQL 语言执行过程来看，SQL 调优是在什么 3.1.1 SQL 语言特点 SQL 语言的特点为： • 综合统一 SQL 语言集数据定义语言 DDL、数据操纵语言 DML、数据控制语言 DCL 的功能于一体。 • 高度非过程化 8 第 3 章 查询优化器基础 用户只需提出“做什么”，而不必指明“怎么做”。存取路径的选择以及 SQL 语句的操作过程由系统自 动完成。大大减轻了用户负担，而且有利于提高数据独立性。 由于 SQL 语言高度非过程化的特点，面对一个 SQL 语句应该要如何处理完全由数据库管理系统决定选择，当处 理过程不够高效时就会出现一些性能问题，此时则需要人工的干预，进行性能调优，从而达到我们的性能目标。 3.1.2 SQL 处理过程 在 KingbaseES 中，SQL 语句的处理通常都要经历以下 5 个阶段。 1) 词法语法分析阶段（parser 子系统） 检查 SQL 语句是否符合词法语法规则，然后转化为一颗解析树。 2) 语义检查阶段（analyzer 子系统） 检查解析树是否符合语义规则，然后转化为一颗查询树。 3) 查询重写阶段（rewritter 子系统） 如果查询树中涉及视图或者规则，重写为查询树。 4) 查询优化阶段（planner 子系统） 经过逻辑优化和物理优化，生成最优的执行计划。 5) 查询执行阶段（executor 子系统） 按照执行计划进行执行。 注意: 本书着重讨论 DML 语句的优化；DDL 和 DCL 语句的执行路径是确定的，不存在执行计划的概念，所以无需 经历查询优化阶段。 9 第 3 章 查询优化器基础 在 SQL 处理过程中，优化阶段（planner 子系统）选择的执行计划是否最优，会直接影响到执行时间。所以，理 解查询优化器的优化过程对 SQL 调优会有很大帮助，本文后续章节将重点介绍优化器。 3.2 查询优化器简介 数据库的使用者在书写 SQL 语句的时候也经常会考虑到查询的性能，根据自己已知的知识争取写出性能很高的 SQL 语句。但是一个应用程序可能要写大量的 SQL 语句，而且有些 SQL 语句的逻辑极为复杂，数据库应用开发人 员很难面面俱到地写出性能良好的语句。 因此，查询优化器是提升查询效率非常重要的一个手段，并且查询优化器的优化过程对数据库开发人员是透明 的。查询优化器的主要作用就是生成最优的执行计划。对于一条给定的 SQL 语句，通常会有一个或多个执行计划可 供选择，每个执行计划都可以返回正确的结果，优化器会选择预期运行最快的执行计划。 为了给出最优的执行计划，KingbaseES 支持了以下两种技术： • 基于规则的逻辑优化 （RBO） • 基于代价估算的物理优化 （CBO） 上述两种技术是自动的，可以极大地解放数据库应用开发人员的生产力。反过来，理解这两种优化手段也有助于 使用者开发出更高效的 SQL 语句，在优化 SQL 语句性能的时候选择更加适合的优化手段。 • 优化器的局限性 3.2.1 逻辑优化 查询优化器在逻辑优化阶段的主要任务是，根据关系代数的等价变换规则找出更高效的变换形式（有关关系代数 的等价变换原理可以参照 《数据库系统实现》）。 3.2.1.1 逻辑优化规则 KingbaseES 目前支持的逻辑优化包括： • 视图重写 视图重写是将对视图的引用重写为对基表的引用，KingbaseES 把带有视图的查询转换为基于表的子查 询，因此重写后视图作为子查询进行进一步优化。目前 KingbaseES 仅支持对简单视图的重写，即对 SPJ 视图重写。KingbaseES 在查询重写阶段进行视图重写优化。 • 表达式预处理 这一阶段主要进行常量化简、连接溯源、表达式规范化等操作。 • 子查询优化 10 第 3 章 查询优化器基础 应用子查询优化技术，上拉子链接 ((NOT) EXISTS / IN / ANY…)、上拉子查询，使得子查询和父查询 处于同一层次，作为连接关系与外层父查询并列，也就是将子查询重写为等价的多表连接，从而尽可能减 少查询语句的层次，便于物理优化阶段考虑多种不同的连接方式。 • 等价谓词重写 数据库执行引擎对一些谓词处理的效率要高于其他谓词，基于这点，把逻辑表达式重写成等价的且效率更 高的形式，能有效提高查询执行效率。 • 条件化简 WHERE、HAVING 和 ON 条件由许多表达式组成，而这些表达式在某些时候彼此之间存在一定的联系。 利用等式和不等式的性质，可以将 WHERE、HAVING 和 ON 条件化简 • 外连接消除 查询优化将满足特定条件的外连接转换为内连接，完成外连接的消除，转换的条件是看 WHERE 子句中 与内表相关的条件是否满足“空值拒绝条件”（reject-NULL 条件）。 • 谓词下推 “选择”、“投影”操作下推（WHERE/JOIN-ON 中的条件尽可能下推），使得关系在连接前尽量减少 元组数量，从而减少连接后得到的中间关系的大小。 • 连接消除 主外键参照的表进行外连接，可以消除主键表，从而消除连接。 • 合并子查询的公共表达式 将 SQL 语句中的子查询的公共表达式进行合并，只需执行一次获得所需结果集，后续直接引用该结果集 即可，减少对公共表达式的重复计算。 3.2.1.2 逻辑操作符简介 构造查询的基本动作通常由关系代数的操作符来表达，常见关系代数的操作符包括： • 选择：与 SQL 查询的 WHERE 子句对应 • 投影：与 SQL 查询的 SELECT 子句对应 • 卡氏积：与 SQL 查询的 FROM 子句的关系列表对应 • 连接：与 SQL 查询中的 JOIN、NATURAL JOIN 和 OUTER JOIN 对应，并、交和差：与 SQL 的操作符 UNION、INTERSECT、EXCEPT 相对应 • 消除重复：与 SQL SELECT 子句中的关键字 DISTINCT 对应，SQL 的操作符 UNION、INTERSECT、EXCEPT 默认消除重复 • 分组：与 SQL 查询中的 GROUP BY 对应 • 排序：与 SQL 查询中的 ORDER BY 具有相同的效果 下面用一个查询来举例说明基础的逻辑操作符： 11 第 3 章 查询优化器基础 SELECT title, birthdateFROM MovieStar, StarsInWHERE year = 1996 AND gender = ‘F’ AND starName = name； 图 3.2.1: 逻辑优化树 3.2.2 物理优化 3.2.2.1 物理优化简介 物理优化则是基于代价的查询优化（Cost-based Optimizer，简称 CBO），其主要流程是枚举各种待选的物理查 询路径，并且根据路径上各节点的信息计算这些待选路径的代价，进而选择出代价最小的路径。 物理路径多样的原因在于，对于各种关系代数操作符（选择、排序、连接、聚集等），执行器中存在着一个或者 多个节点（算法）来应对不同情况。 • 选择：seqscan、indexscan、index only scan 等 • 排序：sort、indexscan。 • 连接：hashjoin、merge join、nestloopjoin。（连接顺序也可以变化） • 聚集：hashaggregate、groupaggregate。 关系代数操作符详细参见逻辑操作符简介 注意: 对于一个形如”select from t1,t2 where t1.c1 = t2.c1 and t1.c3 < 100”的语句，选择操作符有 seqscan、indexscan、indexonly、scan 三种节点（实际情况不止，还有其他扫描节点）可选； t1 和 t2 的连接操作符有 hashjoin、 merge join、nestloop join 三种节点可选（实际情况不止， join 算法存在变种）。该语句则存在着 3*3=9 种物理路径 可选。 12 第 3 章 查询优化器基础 代价估算模型 3.2.2.2 为了合理高效的评估诸多路径中哪条路径最优，我们需要构建一个可量化的模型，也就是代价估算模型，以此来 衡量各条路径的代价。 3.2.2.2.1 代价估算 代价估算是估算某个路径（可以是单表的路径，也可以是两个关系连接得到的路径，还可以是多个表根据不同的 连接方式得到的连接路径）的花费，属于物理优化范畴。 这些路径中每个节点主要的操作可归纳为数据的读写及运算，这些操作所产生的开销主要为 CPU 开销及 I/O 开 销，某些情况下会有通信代价（如并行）。 所以查询优化代价估算基于 CPU 开销和 IO 开销，即： 总代价 =I/O 代价 + CPU 代价 + 通信代价（并行） 大体上来说，I/O 和 CPU 的代价估算和需要进行 IO 和 CPU 运算的数据规模相关，而数据规模的估算和选择率 则关系密切，下章中将予以说明。 3.2.2.2.2 选择率计算 选择率表示“过滤条件”或者“连接条件”在执行完成后剩余的元组数和总行数的比例，选择率的不同会直接影 响到算法的选择。 为了快速的给出代价评估，选择率的估算会依赖于统计信息，使得优化器可以在不真正执行语句的情况下就给出 代价估算。 例如： create table t_sel(id int, name text); insert into t_sel values(generate_series(1,10000), random()); select count(*) from t_sel; COUNT ------10000 (1 row) analyze t_sel; explain select * from t_sel where id < 50; QUERY PLAN --------------------------------------------------------Seq Scan on T_SEL (cost=0.00..189.00 rows=49 width=21) Filter: (ID < 50) (2 rows) 13 第 3 章 查询优化器基础 在本例中，优化器会根据统计信息得到以下信息： • 查出 t_sel 表的总行数为 10000 • 计算出条件 id <50 的选择率为 0.49% 进而可以计算出经过 id < 50 过滤条件后的行数为 49。 使用统计信息来估计选择率，需要及时的收集采样信息以确保统计信息能够尽量真实的反应数据分布情况，否则 选择率的计算可能产生较大的偏差，进而影响最优执行计划的生成。 统计信息的更多内容可以参见本手册章节优化器统计信息 。 3.2.2.3 单表扫描代价估算 在对数据做任何处理之前，通常都需要获取单表的数据，KingbaseES 中常见的单表扫描方式包括： • 顺序扫描（SeqScan） • 索引扫描（IndexScan） 3.2.2.3.1 顺序扫描 顺序扫描也叫全表扫描，基本算法为：根据实际的数据的存取顺序，连续扫描表中的所有数据。 14 第 3 章 查询优化器基础 适用于：选择率高的情况 在选择率高到一定程度的时候，相比于顺序扫描，索引扫描多出扫描索引的代价，所以付出的代价会超过顺序扫 描。 顺序扫描代价估计： 顺序扫描操作的代价估算：tuple IO 代价 +tuple CPU 扫描代价。 页面数和 tuple 数来源于统计信息。 例如： • 数据准备 create table scan (a int); insert into scan values (generate_series(1,100000)); create index ON scan(a); analyze scan; • 高选择率情况 选择率高时，优化器自动选择 seqscan 作为扫描的算法。 explain analyze select * from scan where a>1;--选择率为 0.99999 QUERY PLAN ----------------------------------------------------------------------------------------Seq Scan on SCAN (cost=0.00..1693.00 rows=100000 width=8) (actual time=0.025..38.464 rows=99999 loops=1) Filter: (A > 1) Rows Removed by Filter: 1 Planning time: 0.244 ms Execution time: 49.086 ms (5 rows) 通过禁用 seqscan 让优化器使用 indexscan set enable_seqscan to off; explain analyze select * from scan where a>1; QUERY PLAN ----------------------------------------------------------------------------------------Index Scan using SCAN_A_IDX on SCAN (cost=0.29..3300.29 rows=100000 width=8) (actual time=0.031.. 47.528 rows=99999 loops=1) Index Cond: (A > 1) Planning time: 0.249 ms Execution time: 58.148 ms (4 rows) 15 第 3 章 查询优化器基础 通过上面可以看出当选择率高的时候： 使用 seqscan 的实际执行总代价（实际花费 38.464ms）要小于使用索引扫描的实际执行总代价（实际花费 47.528ms）。在上述例子中，选择率接近 1，全表扫描和索引扫描都要读取所有的页面和 tuples，除此之 外，索引扫描还需要读取索引页面，所以索引扫描比全表扫描慢。 3.2.2.3.2 索引扫描 索引扫描的基本算法为，根据条件值，通过索引结构快速的找到条件值的索引位置，进而找出所有符合条件的 值。 适用于：选择率低的情况 在选择率低的情况下，索引扫描需要扫描的索引页面和数据页面要小于顺序扫描，由此付出的 IO 和 CPU 代价 要小于顺序扫描，所以优化器会选择索引扫描。 索引扫描代价估计： 索引扫描操作的代价估算：索引扫描代价 +tuple IO 代价 +tuple CPU 扫描代价。 例如： • 低选择率情况 优化器自动选择 Indexscan 的计划 explain analyze select * from scan where a=10;--选择率为 0.000001 QUERY PLAN ----------------------------------------------------------------------------------------Index Scan using SCAN_A_IDX on SCAN (cost=0.29..8.31 rows=1 width=8) 16 第 3 章 查询优化器基础 Index Cond: (A = 10) Planning time: 0.226 ms Execution time: 0.073 ms (4 rows) 禁用 indexscan 和 bitmapscan，使用 seqscan 的计划 set enable_indexscan = off; set enable_bitmapscan = off; explain analyze select * from scan where a=10; QUERY PLAN ----------------------------------------------------------------------------------------Seq Scan on SCAN (cost=0.00..1693.00 rows=1 width=8) (actual time=0.037..22.824 rows=1 loops=1) Filter: (A = 10) Rows Removed by Filter: 99999 Planning time: 0.115 ms Execution time: 22.858 ms (5 rows) 通过上面可以看出当选择率低的时候： 使用 seqscan 实际执行总代价（实际花费 22.824ms）大于索引扫描实际执行总代价（实际花费 8.31ms）。 在上述例子中，选择率接近 0，全表扫描要扫所有的页面和 tuples，而索引扫描只需要扫索引页面和从一 个页面读一个 tuple，因此索引扫描比较快。 3.2.2.4 两表连接代价估算 在数据库中表与表的连接为一项重要的操作，两表连接作为连接的基础尤其重要。对于 DBA 而言，调整连接方 式是 SQL 调优的常用手段。研究两表连接的方式，选择合适的连接方式，对 SQL 执行效率的提高有着直接的帮助。 为了支撑复杂多变的 SQL 语句，KingbaseES 提供了 3 种类型的连接方式。每种类型的连接方式对应不同的算法 实现，本文将一一介绍三种连接类型的算法原理及适用场景。 3.2.2.4.1 嵌套循环连接（NestLoop） 嵌套循环连接为两表连接最基本的算法。 1. 基本嵌套循环连接 17 第 3 章 查询优化器基础 循环嵌套连接算法本质为一个两层循环，循环外层的表每次拿出一条元组与循环内层的表作比较逐一匹配 是否满足连接条件选出符合连接条件的元组。 适用于：1）内外表数据量不大的情况 2）内表数据量很小，外表数据量大的情况 嵌套循环连接代价估计 通常情况下由于连接操作主要花费 CPU 资源。所以从理论的角度分析，嵌套循环连接操作的代价估算： 扫描外表的代价和扫描内表的代价之和 例如： 存在两张表 T1 与 T2 做循环嵌套连接，T1 作为循环外层的表存在的元组条数为 n，T2 存在的元组条数 为 m。 准备数据： create table t1(a int); create table t2(a int); insert into T1 values (generate_series(1,1000000)); insert into T2 values (generate_series(1,1000000)); 循环嵌套连接时。表 T1 将每次拿出一条元组，与 T2 的 m 条元组逐一比较。此时在连接中 T1 表循环了 1 次、T2 表循环了 n 次，共比较了 n*m 次。 explain analyze select * from T1 join T2 on t1.a=t2.a where t1.a<10 and t2.a=1; QUERY PLAN ----------------------------------------------------------------------------------------Nested Loop (cost=0.00..36350.01 rows=1 width=8) (actual time=0.057..286.687 rows=1 loops=1) -> Seq Scan on T1 (cost=0.00..19425.00 rows=1 width=4) (actual time=0.034..116.766 rows=1 loops=1) Filter: ((A < 10) AND (A = 1)) Rows Removed by Filter: 999999 ->Seq Scan on T2 (cost=0.00..16925.00 rows=1 width=4) (actual time=0.018..169.916 rows=1 loops=1) 18 第 3 章 查询优化器基础 Filter: (A = 1) Rows Removed by Filter: 999999 Planning time: 0.190 ms Execution time: 286.740 ms 2. 物化嵌套循环连接 每当读取外部表的每个元组时，上述嵌套循环连接都必须扫描内部表的所有元组。由于为每个外部表元组 对内部表做全表扫描是一个昂贵的过程，因此 KingbaseES 支持物化嵌套循环连接以减少内部表的全表扫 描成本。 在运行嵌套循环连接之前，执行程序通过使用临时元组存储模块对内部表进行一次扫描，将内部表元组写 入 work_mem 或临时文件中。与使用缓冲区管理器相比，它有可能更有效地处理内部表元组，尤其是当 所有元组都能写入 work_mem 中时。 适用于：内表数据被重复访问 例如： 物化循环嵌套连接时 T2 的结果物化在内存中。表 T1 将每次拿出一条元组，与 T2 的 m 条元组逐一比 较。此时在连接中 T1 表循环了 1 次、T2 表循环了 n 次，共比较了 n*m 次。 explain analyze select * from T1 join T2 on t1.a=t2.a where t1.a<100 and t2.a<100;--使用 where 条 件控制表 T1、T2 数据量 | QUERY PLAN ----------------------------------------------------------------------------------------Nested Loop (cost=0.00..34000.25 rows=1 width=8) (actual time=0.091..289.167 rows=99 loops=1) Join Filter: (T1.A = T2.A) Rows Removed by Join Filter: 9702 -> Seq Scan on T1 (cost=0.00..16925.00 rows=100 width=4) (actual time=0.046..115.298 rows=99 loops=1) Filter: (A < 100) Rows Removed by Filter: 999901 -> Materialize (cost=0.00..16925.50 rows=100 width=4) (actual time=0.000..1.741rows=99 loops=99) 19 第 3 章 查询优化器基础 -> Seq Scan on T2 (cost=0.00..16925.00 rows=100 width=4) (actual time=0.037..171.538 rows=99 loops=1) Filter: (A < 100) Rows Removed by Filter: 999901 Planning time: 0.215 ms Execution time: 289.226 ms (12 rows) 3. 索引嵌套循环连接 如果存在内部表的索引，并且该索引可以用于满足联接条件的元组以匹配外部表的每个元组，那么优化器 会考虑使用此索引直接搜索内部表元组，而不是顺序扫描。这种变化称为索引嵌套循环联接；尽管该算法 引用了索引“嵌套循环连接”，但该算法基本上只需要在外部表上循环一次，因此，它可以有效地执行连 接操作。 适用于：外部表数据量不大，内部表可以使用索引扫描一次很快的情况 索引嵌套循环连接代价：扫描外表代价 + 使用索引扫描内表代价（通常小于全表扫描内表代价） 例如： 索引循环嵌套连接时 T2 表使用索引提高扫描速度。表 T1 将每次拿出一条元组，与 T2 的 m 条元组逐一 比较。此时在连接中 T1 表循环了 1 次、T2 表循环了 n 次，共比较了 n*m 次。 create index on t2(a); explain analyze select * from T1 join T2 on t1.a=t2.a where t1.a<100 and t2.a<10; QUERY PLAN --------------------- -------------------------------------------------------------------Nested Loop (cost=0.42..16947.10 rows=1 width=8) (actual time=0.049..171.314 rows=9 loops=1) Join Filter: (T1.A = T2.A) Rows Removed by Join Filter: 882 -> Seq Scan on T1 (cost=0.00..16925.00 rows=100 width=4) (actual time=0.032..170.853 rows=99 loops=1) Filter: (A < 100) Rows Removed by Filter: 999901 -> Materialize (cost=0.42..8.63 rows=9 width=4) (actual time=0.000..0.002 rows=9 loops=99) 20 第 3 章 查询优化器基础 -> Index Only Scan using T2_A_IDX on T2 (cost=0.42..8.58 rows=9 width=4) (actual time=0.007.. 0.013 rows=9 loops=1) Index Cond: (A < 10) Heap Fetches: 9 Planning time: 0.397 ms Execution time: 171.371 ms (12 rows) 3.2.2.4.2 归并连接（MergeJoin） 与嵌套循环连接不同，归并联接只能用于自然联接和等值联接。 1. 基本归并连接 归并连接的基本原理为对两个表的数据进行排序，然后做连接。 归并连接算法的步骤是： 1. 将每个表排好序 2. 读入每个表的元组 3. 找出两个表中最小的元组进行匹配 4. 找出次小的元组匹配依此类推，完成其他元组的两表连接。 归并连接算法要求内外表都是有序的，所以对于内外表都要排序。但是如果内表或者外表有索引，就不需 要进行排序，只对无索引的表进行排序。如果两表都有索引，则两表都不需要排序。 适用情况：1）两个表的数据都是基本有序 2）两表可以使用索引或者外表有索引 归并连接代价估计： 通常情况下由于连接操作主要花费 CPU 资源。所以从理论的角度分析，归并连接操作的代价估算：扫描 外表代价 + 扫描内表代价 + 外表排序代价 + 内表排序代价 例如： 21 第 3 章 查询优化器基础 create table t1(a int); create table t2(a int); insert into T1 values (generate_series(1,1000000)); insert into T2 values (generate_series(1,1000000)); analyze ; explain analyze select * from T1 join T2 on t1.a=t2.a where t1.a<100 and t2.a<10 order by t1.a;-使用 order by 让表 t1 有序 QUERY PLAN ----------------------------------------------------------------------------------------Merge Join (cost=33856.66..33857.67 rows=1 width=12) (actual time=276.471..276.478 rows=10 loops=1) Merge Cond: (T1.A = T2.A) -> Sort (cost=16928.32..16928.57 rows=100 width=4) (actual time=164.937..164.938 rows=10 loops=1) Sort Key: T1.A Sort Method: quicksort Memory: 29kB -> Seq Scan on T1 (cost=0.00..16925.00 rows=100 width=4) (actual time=0.036..164.876 rows=99 loops=1) Filter: (A < 100) Rows Removed by Filter: 999901 -> Sort (cost=16928.33..16928.58 rows=100 width=4) (actual time=111.522..111.524 rows=10 loops=1) Sort Key: T2.A Sort Method: quicksort Memory: 25kB -> Seq Scan on T2 (cost=0.00..16925.01 rows=100 width=4) (actual time=0.021..111.506 rows=10 loops=1) Filter: (A < 10) Rows Removed by Filter: 999991 Planning time: 1.085 ms Execution time: 276.591 ms (16 rows) 2. 物化归并连接 与嵌套循环连接类似，归并连接支持物化归并连接（Materialized Merge Join），物化内表，使内表扫描 更为高效。其原理图如下： 22 第 3 章 查询优化器基础 例如： explain analyze select * from T1 join T2 on t1.a=t2.a; QUERY PLAN ----------------------------------------------------------------------------------------Merge Join (cost=255514.69..278014.69 rows=1000000 width=8) (actual time=380.418..910.112 rows=1000000 loops=1) Merge Cond: (t1.a = t2.a) -> Sort (cost=127757.34..130257.34 rows=1000000 width=4) (actual time=198.506..282.071 rows=1000000 loops=1) Sort Key: t1.a Sort Method: external merge Disk: 13736kB -> Seq Scan on t1 (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.028..68.415 rows=1000000 loops=1) -> Materialize (cost=127757.34..132757.34 rows=1000000 width=4)(actual time=181.903..360.212 rows=1000000 loops=1) -> Sort (cost=127757.34..130257.34 rows=1000000 width=4) (actual time=181.900..267.644 rows=1000000 loops=1) Sort Key: t2.a Sort Method: external merge Disk: 13736kB -> Seq Scan on t2 (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.010..62. 750 rows=1000000 loops=1) Planning Time: 0.143 ms Execution Time: 938.136 ms (13 行记录) 3.2.2.4.3 Hash 连接（HashJoin） 与归并连接相同同，hash 联接只能用于自然联接和等值联接。 23 第 3 章 查询优化器基础 内存中的 Hash 连接是在 work_mem 中处理的。Hash 连接有两个阶段：构建阶段和探测阶段。在构建阶段，内 表中的所有元组都会被插入到桶中；在探测阶段，每条外表的元组都会与处理桶中的内表元组比较，如果满足条件则 将两条元组连接起来。 在上面的 Hash 连接步骤中，步骤（1）至（3）是 Hash 连接的构建阶段，步骤（4）至（5）是探测阶段。 Hash 连接的基本原理为： 1) 对内表建立 hash 表，扫描所有内表数据到各个 hash 桶里面。 2) 一行行扫描外表数据，对外表数据进行 hash，hash 到某个桶里面。 3) 跟这个桶里面的数据进行连接。 适用情况：数据分布比较随机无序，重复值不是特别多的情况。 Hash 连接代价估计： 通常情况下由于连接操作主要花费 CPU 资源。所以从理论的角度分析，Hash 连接操作的代价估算：内表 创建 hash 代价 +(n_outer_tuple × m_inner_tuple × 选择率)× a_tuple_cpu_time a_tuple_cpu_time，获取一个元组消耗的 CPU 时间。 n_outer_tuple，扫描获取的外表元组数。 m_inner_tuple，扫描获取的内表元组数。 例如： create table t1(a int); create table t2(a int); insert into T1 values (generate_series(1,1000000)); 24 第 3 章 查询优化器基础 insert into T2 values (generate_series(1,1000000)); analyze ; explain analyze select * from T1 join T2 on t1.a=t2.a where t1.a<1000 and t2.a<100 ;--与前文 nestloop 相比增大了数据量 QUERY PLAN ----------------------------------------------------------------------------------------Hash Join (cost=16926.26..33855.03 rows=1 width=8) (actual time=174.828..285.586 rows=100 loops=1) Hash Cond: (T1.A = T2.A) -> Seq Scan on T1 (cost=0.00..16925.00 rows=1002 width=4) (actual time=0.035..110.639 rows=999 loops=1) Filter: (A < 1000) Rows Removed by Filter: 999001 -> Hash (cost=16925.01..16925.01 rows=100 width=4) (actual time=174.772..174.772 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> Seq Scan on T2 (cost=0.00..16925.01 rows=100 width=4) (actual time=0.019..174.723 rows=100 loops=1) Filter: (A < 100) Rows Removed by Filter: 999901 Planning time: 0.263 ms Execution time: 285.650 ms 3.2.3 优化器的局限性 KingbaseES 的查询优化器在不断的提升自己的优化能力，但是总会有无法覆盖的情况。对于代价计算不准（多 条件、多表连接、相关子查询条件等）的情况，建议用户通过 HINT 的方式人工指定计划。 详细信息请参考本手册章节使用 HINT 3.3 优化器统计信息 本章主要讨论了 KingbaseES 中优化器所使用的统计信息，讨论的主题包括： • 统计信息简介 • 统计信息内容 • 统计信息的收集 • 扩展统计信息 • 统计信息的局限性 25 第 3 章 查询优化器基础 3.3.1 统计信息简介 KingbaseES 优化器支持基于成本的物理优化（CBO），而物理优化的核心是代价估算。在不真正执行语句之 前，提前对代价给出较为合理的估算则是优化器的一项必要的能力。 为了支持代价估算，KingbaseES 提供了统计信息，以采样的方式收集信息，来反应表和索引数据的概览情况， 方便优化器在短时间内对代价进行评估。 3.3.2 统计信息内容 以单表扫描为例，代价估算主要依赖于以下信息，这也是统计信息需要收集的主要内容： • 表级统计信息：页面数、元组数 • 列级统计信息：直方图、MVC 等 列级统计信息主要反应数据的分布情况，进而帮助计算选择率。 单表扫描操作的代价估算公式如下所示。 顺序扫描 N_page * a_tuple_IO_time + N_tuple * a_tuple_CPU_time 索引扫描 C_index + N_page_index * a_tuple_IO_time 说明如下： N_page，数据页面数。 a_page_IO_time，一个页面的 IO 代价。 N_page_index，索引页面数。 a_tuple_CPU_time，一个元组从页面中解析的 CPU 代价。 N_tuple，元组数。 C_index，索引的 IO 代价，C_index = N_page_index×a_page_IO_time。 N_tuple_index，索引作用下的可用元组数，N_tuple_index = N_tuple× 索引选择率。 3.3.2.1 表级信息 表和索引是数据库里最基本的对象，它们的基本信息对优化器来说也是最重要的。优化器需要知道每一个表和索 引占用的磁盘块数，以及表和索引包含的记录数。这些信息主要保存在 sys_class 表中： select * from sys_class where relname = 'student'; -[ RECORD 1 ]-------+-------oid | 16408 relname | student … 26 第 3 章 查询优化器基础 relpages | 64 reltuples | 10000 其中，比较重要的 2 个字段为： • Relpages：表或者索引占用的磁盘页面数。 • Reltuples：表或者索引记录的总数（预估的）。 优化器需要根据表或者索引的记录总数、磁盘页面数来估算对他们进行扫描的时候的成本，为访问路径的选择来 提供决策基础。 注意：当进行一个不扫描全表的 vacuum 或 analyze 操作时，这个操作会以它扫描的部分为基础增量更新 reltuples 计数，这就导致更新的值是近似值。但是出于效率考虑，reltuple 和 relpages 不是实时更新的，因此其中可能会 包含一些失效的旧值，它们只有被 VACUUM、ANALYZE 和几个 DDL 命令（例如 CREATE INDEX）更新。 3.3.2.2 列统计信息 列统计信息是指对每个表的每一个列（也叫属性）的统计信息，它主要存储在 sys_statistic 系统表中，它负责从 多个角度描述该列的数据概况信息，内容主要分 2 部分： 1) 基本统计信息–主要是 NULL 值率、列的平均宽度、列消重后的数据个数或比例 2) 数据分布信息–主要是高频值、直方图、相关系数等能描述数据分布情况的统计信息 3.3.2.2.1 基本统计信息 Sys_statistic 系统表存储字段基本统计信息如下： • Starelid：表 OID • Staattnum：列的序号 • Stainherit：是否为继承列，f 为否，即为表列本身，t 为是，表该列继承自其他表列。 • Stanullfrac：NULL 值的比例 • Stawidth：非空值的平均存储宽度 • Stadistinct：消除重复唯一值的量 假设我们有 student 表，里面有 8 条记录： elect * from student; sno | sname | ssex -----+-------+-----1 | aa | 1 2 | dd | 1 3 | hh | 1 27 第 3 章 查询优化器基础 4 | ll | 1 5 | aa | 0 6 | dd | 0 7 | | 8 | aa | (8 行记录) 则其基本统计信息如下： starelid staattnum stanullfrac stawidth stadistinct 16423 1 0 4 -1 16423 2 0.125 3 -0.5 16423 3 0.25 4 -0.25 (3 行记录) 以下是说明： 1. 16423 为 student 表 OID，staattnum：1,2,3 分别表示 sno, sname, ssex 字段 2. Stanullfrac：NULL 元素的比例 Sno 列没有 NULL 值，所以为 0 Sname 列有 1 个 NULL 值，1 / 8 = 0.125 Ssex 列有 2 个 NULL 值，2 / 8 = 0.25 3. Stawidth：非空项的平均存储宽度，以字节计。 Sno 列为 int，固定宽度，4 字节 Sname 列为 text，平均宽度为 2 + 1（长度）= 3 字节 Ssex 列为 int，固定宽度，4 字节 4. Stadistinct：该列的内容消重后数据的个数或者比例。其取值有 3 种情况。 • = 0，代表未知或者未计算的情况 • > 0，代表消除重复值后的个数，一般不使用这种情况 • < 0，其绝对值是消重之后的个数占总个数的比例，一般使用这种情况 所以： Sno 列为 1~8 递增，无重复，-（8 / 8）= -1 Sname 有 4 个不重复的值，-(4 / 8) = -0.5 28 第 3 章 查询优化器基础 Ssex 有 2 个不重复，-（2 / 8）= -0.25 通过这些字段，优化器基本能够判断每一个列的 NULL 值大概的个数、列宽、该列唯一值的个数等。 3.3.2.2.2 数据分布信息 为了进一步描述列数据的分布情况，Sys_statistic 系统表后面还有 20 个字段： • Stakind[1-5] • Staop[1-5] • stanumbers[1-5] • stavalues[1-5] 每一个列的统计目前最多能应用 5 种统计方法，因此 sys_statistic 中会有 stakind(1-5)，staop(1-5)， stanumbers(1-5)，stavalues(1-5)，分别是 5 个槽位。如果 stakind 不为 0，这说明这个槽位有统计信息，第一个统计 方法会记录到第一个槽位（stakind1, staop1, stanumbersr1, stavalues1）中，第二个会记录到 stakind2…依次类推。 其中 stakind 里面的数字代表的意思如下： 1. 高频值（MCV） - 在一个列中最频繁出现的值，按照出现的频率进行排序，并且生成一个一一对应的频率数 组，这样就能知道一个列中有哪些高频值，这些高频值的频率是多少。 2. 直方图（HISTOGRAM）- 直方图用来描述一个列中的数据分布情况，一般在数据分布比较平坦的情况下会生 成直方图，否则用高频值会更好一些。 3. 相关系数（CORRELATION）- 相关系数记录的是当前列未排序的数据分布和排序后的数据分布的相关性。这 个值通常在索引扫描的时候用来估算代价。如果一个列未排序和排序之后的相关性是 0，也就是完全不相关， 那么索引扫描的代价就会高一些。 4. 类型高频值–用于数组或者其他类型的高频值 5. 数组类型直方图 6. Range 类型基于长度的直方图 7. Range 类型基于边界的直方图 Stakind 数值为 4、5、6、7 这些类型的统计信息主要基于特定数据类型的，其统计方式也和高频值（1）、直方 图（2）和相关系数（3）这三种统计方式类似。 3.3.2.2.3 高频值示例 高频值（MCV）是指在一个列中最频繁出现的值。按照出现的频率进行排序，并且生成一个一一对应的频率数 组，这样就能知道一个列中有哪些高频值，这些高频值的频率是多少。 如下所示，我们创建一个表，插入 1000 行记录，并设置 id 字段为 10 个桶，使用 analyze 更新统计信息后，查 看 sys_statistic 系统表： 29 第 3 章 查询优化器基础 create table test(id int primary key, name text); alter table test alter id set statistics 10; insert into test select id, 'test' || id::text from generate_series(1, 1000) as id; update test set name='ada' where id < 350; update test set name='bob' where id > 350 and id < 700; update test set name='cindy' where id > 700 and id < 900; update test set name='dot' where id > 900; analyze; select starelid, staattnum, stakind1, staop1, stanumbers1, stavalues1 from sys_statistic where starelid= 16442; -[ RECORD 1 ]--------------------------------------------starelid | 16442 staattnum | 2 stakind1 | 1 staop1 | 98 stanumbers1 | {0.35,0.35,0.2,0.1} stavalues1 | {ada,bob,cindy,dot} 我们可以看到高频值’ada’,’bob’,’cindy’’dot’被列了出来，而且其出现频率分别为 35%，35%， 20%，10%。 这样当查询条件里有类似 where name = ‘bob’时，优化器能够估算出符合条件的记录有 35%，大约 350 条记 录。 3.3.2.2.4 直方图示例 直方图（HISTOGRAM）用来描述一个列中的数据分布情况，一般在数据分布比较平坦的情况下会生成直方 图，否则用高频值会更好一些。 30 第 3 章 查询优化器基础 直方图将采样的数据范围划分为等频的桶。桶的数目可以通过 alter table 语句手工设置。还是刚才那个示例，查 询到的第二条记录即为直方图（stakind1=2）如下： -[ RECORD 2 ]--------------------------------------------starelid | 16442 staattnum | 1 stakind1 | 2 staop1 | 97 stanumbers1 | stavalues1 | {1,100,200,300,400,500,600,700,800,900,1000} 可以看出，直方图 histogram_bounds 字段里共有 10 个桶，每个桶里的值有 100 条。当 SQL 查询条件为 where id <= 350 时，优化器能够评估出前后 4 个桶里有数据，这会覆盖前 3 个桶和第 4 个桶的一半，大约 350 条记录。 explain select * from test where id <=350; QUERY PLAN -------------------------------------------------------Bitmap Heap Scan on test (cost=3847.11..4955.81 rows=350 width=11) Recheck Cond: (id <= 350) -> Bitmap Index Scan on test_pkey (cost=0.00..3847.03 rows=350 width=0) Index Cond: (id <= 350) (4 行记录) 31 第 3 章 查询优化器基础 3.3.3 统计信息的收集 良 好 查 询 性 能 最 重 要 的 先 决 条 件 是 正 确 的 统 计 信 息， 陈 旧 的 统 计 信 息 可 能 会 导 致 优 化 器 生 成 低 效 的 执 行 计 划。KingbaseES 内 部 存 在 着 一 个 统 计 进 程， 以 采 样 的 方 式 收 集 统 计 信 息。 该 统 计 进 程 不 是 使 用 全 部 数 据 作 为 样 本 进 行 统 计， 而 是 随 机 地 采 集 表 中 的 一 部 分 元 组 作 为 样 本 来 生 成 统 计 信 息。 通 过 调 整 de- fault_statistics_target 的值可以改变样本容量，目前 default_statistics_target 的默认值是 100，在计算样本容 量时，采用 300×defualt_statistics_target=30000 作为采样的样本默认容量。 采样的方式包括： • 自动收集 • 主动收集 3.3.3.1 自动收集 KingbaseES 会默认自动收集统计信息，在配置参数 autovacuum 开启的情况下，会有一个后台进程自动检测。 autovacuum 是 kingbase 提供的一个 deamon 进程，在 kingbase 中，事务提交/回滚时会发消息给进程 sysstat， sysstat 会汇总这份信息并记录到文件中，autovacuum launcher 会定期读取文件，获得当某个表的改动超过阈值时便 会触发一次统计信息的更新操作。autovacuum 会执行 analyze 操作更新统计信息，analyze 操作对表进行随机采样更 新统计信息，而不是读取表的每一行。这些阈值等一系列配置参数保存在 kingbse.conf 里面，主要包括： 1、总控 autovacuum 参数： • autovacuum： 是否允许自动 vacuum，默认值是 on。 • log_autovacuum_min_duration： 默认值为-1，不记录 vacuum 日志。0 记录所有 vacuumlog。 正整数，单位为微秒，表示 vacuum 执行时间小于该值的不记录，大于该值的的记录 log。 • autovacuum_max_workers： 最大的 autovacuum 进程的数量，默认值为 3。 • autovacuum_naptime： 检查数据库的时间间隔。默认为 1 分钟。 2、自动 vacuum 参数： • autovacuum_vacuum_threshold： 激活自动 vaccum 的最小 DML 操作的行数。默认值 50。 • autovacuum_vacuum_scale_factor： 32 第 3 章 查询优化器基础 该参数采用百分比的方式设定阀值。默认值为 20%。 说明：表上 update 和 delete 记录总数 >= autovacuum_vacuum_scale_factor * 表上记录数（reltuples） + autovacuum_vacuum_threshold 后，触发 vacuum。 3、自动 analyze 参数 • autovacuum_analyze_threshold： 激活自动 analyze 操作的最小行数。默认值 50。 • autovacuum_analyze_scale_factor： 到达阀值时自动激活 analyze 操作。默认值为 10%。 说明：表上 insert、update 和 delete 记录总数 >= autovacuum_ analyze _scale_factor * 表上记录数 （reltuples）+ autovacuum_ analyze _threshold 后，触发自动 analyze。 4、事务 ID 相关参数 • autovacuum_freeze_max_age： 为防止事务 ID 的重置，在启用 vacuum 操作之前，表的 sys_class.relfrozenxid 字段的最大值，默认为 2 亿。 说明：表上事务的最大年龄配置参数 autovacuum_freeze_max_age，达到这个阀值将触发 autovacuum 进程。 3.3.3.2 主动收集 主动收集是指用户通过定时脚本或者人工执行 analyze 命令来进行。此命令将触发数据库对统计信息进行收集并 更新。 但是不带参数运行 ANALYZE 会为数据库中所有的表更新统计信息。这可能会是运行时间非常长的处理，因此 一般不建议这样做。当数据被改变时，使用者应该有选择地 ANALYZE 表。在大型表上运行 ANALYZE 可能需要很 长时间。如果在非常大的表的所有列上运行 ANALYZE 行不通，使用者可以只使用 ANALYZE table(column, ...) 为 选择的列生成统计信息。确保包括用在连接、WHERE 子句、SORT 子句、GROUP BY 子句或者 HAVING 子句中 的列。 主动收集统计时机的信息一般为： • 装载大量数据后 • CREATE INDEX 操作后 • 在大量更改底层数据的 INSERT、UPDATE 以及 DELETE 操作之后 • 执行计划的代价估算不准确时 注意: ANALYZE 仅在表上要求一个读锁，因此它可以与其他数据库活动并行运行。但不要在执行装载、INSERT、 UPDATE、DELETE 以及 CREATE INDEX 操作期间运行 ANALYZE。因为在执行完上面的操作后，表的信息发生 33 第 3 章 查询优化器基础 了改变，需要重新做 ANALYZE。 3.3.4 扩展统计信息 使用基于单列的统计信息来对基于单个列的约束条件进行选择率的估计，误差范围是可控的。但是对于引用了多 个列的约束条件，如果还使用单列的统计信息进行估算，因为统计信息无法体现多列之间的相关性，很可能会导致选 择率计算不准，无法给出事实上最优的执行计划。 KingbaseES 具有计算多元统计信息的能力，可以捕获此类信息。由于可能的列组合数量非常多，自动计算多元 统计信息是不切实际的。取而代之的是，可以手动创建扩展的统计对象（通常称为统计对象），以指示服务器获取统 计信息。 3.3.4.1 扩展统计信息介绍 KingbaseES 的扩展统计信息分为： • 函数依赖 • 多元 N-Distinct 计数 • 多元 MCV 列表 3.3.4.1.1 函数依赖 最简单的扩展统计跟踪函数依赖，这是数据库标准表定义中使用的概念。如果对列 a 值的了解足以确定列 b 的 值，即没有两行具有相同的 a 值，但 b 的值不同，则 b 列在功能上依赖于 a 列。在完全规范化的数据库中，函数依 赖应仅存在于主键和超键上。然而，在实践中，由于各种原因，许多数据集没有完全规范化。即使在完全规范化的数 据库中，某些列之间也可能存在部分相关性，这可以表示为部分函数依赖关系。 函数依赖关系的存在直接影响某些查询中估计值的准确性。如果查询同时包含独立列和依赖列上的条件，则依赖 列上的条件不会进一步减小结果大小；但是如果不知道函数依赖关系，查询规划器将假定这些条件是独立的，从而导 致低估结果大小。 下面是收集函数依赖关系统计信息的示例： 给定表 t(a int, b int)，满足条件 a=1 and b=1 的记录实际有 100 条，但是预估基数值时，估值为 1。这是因为 KingbaseES 是按照属性相互独立的假设来估算的。 • 表 t 中插入 10000 条记录 • 满足条件 a=1 的记录 100 条，选择率 1% • 满足条件 b=1 的记录 100 条，选择率 1% • 按照属性独立的假设，满足 a=1 and b=1 的选择率 = 1% * 1% = 0.01% • 最终预估计基数 10000 * 0.01% = 1 34 第 3 章 查询优化器基础 create table t(a int, b int); insert into t select i %100, i %100 from generate_series(1,10000) s(i); analyze t; explain analyze select * from t where a=1 and b=1; QUERY PLAN ----------------------------------------------------------------------------------------Seq Scan on t (cost=0.00..162.00 rows=1 width=8) (actual time=0.109..5.542 rows=100 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 Planning Time: 0.393 ms Execution Time: 5.625 ms (5 rows) 实际上属性 a, b 是有函数依赖关系的：每一行中 a = b，所以在表 t 上创建依赖关系的多列统计信息： create statistics stts(dependencies) on a,b from t; analyze t; 然后再执行查询时，结果估算正确： explain analyze select * from t where a=1 and b=1; QUERY PLAN ----------------------------------------------------------------------------------------Seq Scan on t (cost=0.00..162.00 rows=100 width=8) (actual time=0.094..5.631 rows=100 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 Planning Time: 0.349 ms Execution Time: 5.741 ms (5 rows) 3.3.4.1.2 多元 N-Distinct 计数 单列统计信息会存储每列中不同值的数量。在 GROUP BY a, b 情况时，由于统计信息中仅具有单列统计数据， 从而导致其选择错误的计划时，组合多于一列时，估算通常是错误的。 为了改善这种估计，可以为列组收集 n 个不同的统计信息。和函数依赖一样，对每个可能的列分组都执行此操作 是不切实际的，因此仅针对在该 N-Distinct 选项定义的统计对象中一起出现的那些列组收集数据。将从列出的列集中 为两个或更多列的每种可能组合收集数据。 示例如下： explain analyze select count(*) from t group by a,b; QUERY PLAN 35 第 3 章 查询优化器基础 ----------------------------------------------------------------------------------------HashAggregate (cost=220.00..230.00 rows=1000 width=16) (actual time=47.090..47.139 rows=100 loops=1) Group Key: a, b -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual time=0.038..3.718 rows=10000 loops=1) 通过执行计划可以看到 groupby 的 HashAggregate 节点基数估计偏差很大。当对列 a,b 进行分组时，预估值为 1000，而实际上因为 a,b 都是从 0 到 99 不断循环且值相同，所以应该有 100 个分组。 因此对 t 表建立多列统计信息后，基数估算准确。 create statistics stts1(ndistinct) on a,b from t; analyze t; explain analyze select count(*) from t group by a,b; QUERY PLAN ----------------------------------------------------------------------------------------HashAggregate (cost=220.00..221.00 rows=100 width=16) (actual time=14.360..14.406 rows=100 loops=1) Group Key: a, b -> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual time=0.033..3.034 rows=10000 loops=1) 3.3.4.1.3 多元 MCV 列表 最常见的为每列存储的另一种统计信息是值列表。这样可以对单个列进行非常准确的估计，但是对于在多个列上 具有条件的查询，可能会导致严重的错误估计。 为了改善这种估计，ANALYZE 可以收集组合列的 MCV 列表信息。与函数依赖项和 n-distinct 相似，对每个可 能的列分组执行此操作都是不切实际的。因此，在这种情况下，MCV 列表（不同于函数依赖项和 n-distinct）存储了 公共列的值，所以只收集使用 mcv 选项定义的统计对象中同时出现的那些列组的数据。 示例如下： 添加多元 MCV 列信息校正基数估计： create statistics stts1(mcv) on a,b from t; analyze t; explain analyze select * from t where a=1 and b=1; QUERY PLAN ----------------------------------------------------------------------------------------Seq Scan on t (cost=0.00..162.00 rows=100 width=8) (actual time=0.044..1.933 rows=100 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 36 第 3 章 查询优化器基础 Planning Time: 0.243 ms Execution Time: 1.965 ms(5 rows) 通过系统表 sys_statistic_ext_data，可以看到 a=1 and b=1 的频率为 0.01。 (0,"{0,0}","{f,f}",0.01,0.0001) (1,"{1,1}","{f,f}",0.01,0.0001) (2,"{2,2}","{f,f}",0.01,0.0001) (3,"{3,3}","{f,f}",0.01,0.0001) (4,"{4,4}","{f,f}",0.01,0.0001) (5,"{5,5}","{f,f}",0.01,0.0001) (6,"{6,6}","{f,f}",0.01,0.0001) (7,"{7,7}","{f,f}",0.01,0.0001) (8,"{8,8}","{f,f}",0.01,0.0001) (9,"{9,9}","{f,f}",0.01,0.0001) (10,"{10,10}","{f,f}",0.01,0.0001) (11,"{11,11}","{f,f}",0.01,0.0001) (12,"{12,12}","{f,f}",0.01,0.0001) (13,"{13,13}","{f,f}",0.01,0.0001) (14,"{14,14}","{f,f}",0.01,0.0001) (15,"{15,15}","{f,f}",0.01,0.0001) (16,"{16,16}","{f,f}",0.01,0.0001) (17,"{17,17}","{f,f}",0.01,0.0001) (18,"{18,18}","{f,f}",0.01,0.0001) (19,"{19,19}","{f,f}",0.01,0.0001) (20,"{20,20}","{f,f}",0.01,0.0001) (21,"{21,21}","{f,f}",0.01,0.0001) (22,"{22,22}","{f,f}",0.01,0.0001) (23,"{23,23}","{f,f}",0.01,0.0001) (24,"{24,24}","{f,f}",0.01,0.0001) (25,"{25,25}","{f,f}",0.01,0.0001) (26,"{26,26}","{f,f}",0.01,0.0001) (27,"{27,27}","{f,f}",0.01,0.0001) (28,"{28,28}","{f,f}",0.01,0.0001) (29,"{29,29}","{f,f}",0.01,0.0001) (30,"{30,30}","{f,f}",0.01,0.0001) …… 3.3.4.2 扩展统计信息使用 使用 CREATE STATISTICS 命令创建统计对象。创建此类对象仅会创建一个统计数据的目录条目。实际数据 收集是通过 ANALYZE（手动命令或后台自动 analyze）执行的。收集的值可以在 sys_statistic_ext_data 目录中查 看。 37 第 3 章 查询优化器基础 ANALYZE 根据用于计算常规单列统计信息的相同表行样本计算扩展统计信息。较大的统计目标通常会使扩展统 计更准确，但会产生更多的计算时间。 3.3.5 统计信息的局限性 对于统计信息，由于是以采样的方式进行收集，因此必然会存在着一个不可回避的问题：统计信息的准确性（或 者说不确定性），以及由不准确所带来的问题：采样导致的误差、统计信息收集不够及时导致的误差、多次选择率计 算叠加产生的误差，进而导致最后选择计划的偏差。 当这些偏差发生时，可以先对统计信息做主动收集。依然不能解决的情况下，可以通过 HINT 注释去控制执行计 划。 38 第4章 执行计划 4 第 章 执行计划 本章重点介绍执行计划相关知识，主题包括： • 查看执行计划 • 阅读执行计划 • 分析执行计划 4.1 查看执行计划 KingbaseES 数据库中存在多种查看执行计划的方式，本小节将详细阐述 KingbaseES 中常用的几种执行计划查 看方式。 • 使用 EXPLAIN 查看 • 使用对象管理工具查看 • 在日志记录中查看 下表给出了三种执行计划的特点以及适用的场景： 查看方式 特点 使用 explain 命令查看 可查看指定 SQL 语句在当前 session 下的执行计划，调试方便 使用对象管理工具查看 使用 auto_explain 查看 4.1.1 可记录所有后台 SQL 语句的执行计划：ksql、jdbc 等 使用 EXPLAIN 查看 KingbaseES 中 explain 命令来查看执行计划时最常用的方式。其命令格式如下： explain [option] statement 39 第4章 执行计划 其中 option 为可选项，可以是以下 5 种情况的组合： • analyze：执行命令并显示执行事件，默认 false • verbose：显示附加信息，比如计划树中每个节点输出的字段名等，默认 false • costs：显示执行计划的成本，默认 true • buffers：显示缓冲区的使用信息，包括共享快、本地块和临时读写块，默认 false，前置条件是 analyze • format：指定执行计划的输出格式，支持：TEXT、XML、JSON 或者 YAML，默认是 text 下面举例说明 explain 与 explain analyze 的不同： explain select * from student where sname='ada'; QUERY PLAN -----------------------------------------------------------------------Index Scan using idx_stu on student (cost=0.14..8.15 rows=1 width=10) Index Cond: (sname = 'ada'::text) (2 行记录) explain analyze select * from student where sname='ada'; QUERY PLAN --------------------------------------------------------------Index Scan using idx_stu on student (cost=0.14..8.15 rows=1 width=10)(actual time=0.074..0.074 rows=0 loops=1) Index Cond: (sname = 'ada'::text) Planning Time: 0.122 ms Execution Time: 0.245 ms (4 行记录) 可以看到使用 explain 查询得到的仅有预期的查询计划，并没有真正执行该语句，而运行 explain analyze 语句得 到的执行计划则包含实际执行语句的 cost、时间等信息。 注：对于某些 DML 语句，如果即想运行 explain analyze 命令，又不想影响实际的数据，则可以把该命令放入一 个事务里，执行完后回滚事务： begin; explain analyze update student set ssex=false where sname='ada'; rollback; 40 第4章 4.1.2 执行计划 使用对象管理工具查看 除了文本形式的执行计划查看方式，KingbaseES 还支持通过“对象管理工具”以图形化的方式来查看和展现执 行计划。 具体方式为，选定想要查看执行计划的 SQL 语句，然后选择 SQL 编辑器中的解析执行计划，就可以得到这条语 句的完整执行计划了，具体情况如下图所示： 41 第4章 4.1.3 执行计划 在日志记录中查看 KingbaseES 数据库提供了插件 auto_explain，用于在日志中自动记录慢速语句的执行计划。这在实际环境中有 着非常重要的意义。相比于 explain 与对象管理工具，auto_explain 可通过连续跟踪，发现大型应用程序中未优化的 查询，或者发现手动 explain 的计划与实际执行时计划存在差异的情况。 根据作用范围不同，auto_explain 有以下两种方式： • 作用于当前会话 LOAD’auto_explain’;（必须是超级用户才能这样做） • 作用于全局 设置 kingbase.conf 中的 shared_preload_libraries 参数为‘auto_explain’，可将其预加载到所有会话中。这 样做会带来额外的性能开销。 可以通过配置参数，来控制 auto_explain 的行为： • auto_explain.log_min_duration（整数） auto_explain.log_min_duration 是最短语句执行时间（以毫秒为单位），将此设置为 0 将记录所有计划。-1 （默认）禁用计划记录。 • auto_explain.log_analyze（boolean） 当记录执行计划时，auto_explain.log_analyze 控制打印 EXPLAIN ANALYZE 输出，而不仅仅是 EXPLAIN 输出。默认情况下，此参数是关闭的。仅超级用户可以更改此设置。 注意: 启用此参数时，所有计划执行的语句都会按计划节点计时，无论它们运行的 时间是否足够长以至于无法 真正记录下来。这可能会对性能产生极大的负面影响。关闭 auto_explain.log_timing 可以降低性能成本，但只 能获取较少的信息。* • auto_explain.log_buffers（boolean） auto_explain.log_buffers 控制在记录执行计划时是否打印缓冲区使用情况统计信息。它等效于 EXPLAIN 的 BUFFERS 选项。除非启用了 auto_explain.log_analyze，否则此参数无效。默认情况下，此参数是关闭的。仅 超级用户可以更改此设置。 • auto_explain.log_timing（boolean） auto_explain.log_timing 控制在记录执行计划时是否打印每个节点的计时信息。它等效于 EXPLAIN 的 TIMING 选项。重复读取系统时钟的开销可能会严重降低某些系统上的查询速度，因此在仅需要实际行计数而不是 确切时间的情况下将此参数设置为 off。除非启用了 auto_explain.log_analyze，否则此参数无效。 • auto_explain.log_verbose（boolean） auto_explain.log_verbose 控制着记录执行计划时是否打印详细信息。它等效于 EXPLAIN 的 VERBOSE 选 项。默认情况下，此参数是关闭的。仅超级用户可以更改此设置。 • auto_explain.sample_rate（boolean） 42 第4章 执行计划 auto_explain.sample_rate 导致 auto_explain 仅解释每个会话中的一部分语句。默认值为 1，表示解释所有查 询。如果是嵌套语句，将全部解释或不解释。仅超级用户可以更改此设置。 4.2 阅读执行计划 本章节包含以下内容： • 主要内容 • 执行顺序 • 计划节点 4.2.1 主要内容 执行计划里每个节点主要内容包括： • 执行动作，及其附加条件，比如索引扫描，索引条件等 • 估算成本：启动 cost、总体 cost • 估计返回的行数和平均宽度 • 如果开启了 Analyze 选项，还会返回： – 实际成本：启动 cost、总体 cost – 实际返回的行数，节点循环执行次数 我们以下例来说明，执行计划输出结果各部分的内容： explain analyze select * from student where sname='ada'; QUERY PLAN --------------------------------------------------------------Index Scan using idx_stu on student (cost=0.14..8.15 rows=1 width=10)(actual time=0.074..0.074 rows=0 loops=1) Index Cond: (sname = 'ada'::text) Planning Time: 0.122 ms Execution Time: 0.245 ms (4 行记录) 1. 执行动作：Index Scan using idx_stu on student • Index Scan：表示索引扫描 • Idx_stu：使用的索引名 43 第4章 执行计划 • Student：访问的表名 整句话表示：访问 student 表的时候，使用的是索引扫描，扫描的索引是 idx_stu。 2. 估算成本：(cost=0.14..8.15 rows=1 width=10) • Cost=0.14..81.5：第一个数 0.14 表示启动成本，也就是说返回第一行需要多少 cost。第二个数值 81.5 表示返回所有的数据的成本。这两个数值用.. 分开。 • Rows=1：表示该查询会返回 1 行记录。 • Width=10：表示每行平均宽度为 10 字节。 需要注意的是：在执行计划里，每一步的 cost 值都传给了上一层，也就是说每一个 cost 值代表的是在此 步以下所有操作的代价总和。启动成本是第一行输出开始前的成本，比如进行排序的成本。 3. 实际成本：(actual time=0.074..0.074 rows=0 loops=1) • Actural time=0.074..0.074：表示实际的启动时间为 0.074，实际的运行时间也为 0.074。 • Rows=0：表示实际查询返回了 0 行记录（没有查询到）。 • Loops=1：表示该索引扫描只执行了 1 次。 这行信息为带有 Analyze 的 explain 语句特有。可以看出实际成本跟估算成本会有一定的偏差。那是因为 估算成本是优化器根据统计信息估算出来的一个结果，而有时统计信息并不完全能够反映真实的情况。 为了让统计信息更准确，可以运行 Analyze 命令来更新整个数据库、某张表或者某个字段的统计信息，然 后再次执行 explain 将得到一个更好的结果。 4. 索引条件：Index Cond: (sname = ’ada’::text) 该句表示当前执行的索引扫描（Index Scan）使用的索引条件为：sname = ’ada’::text 5. 计划和执行时间：Planning Time: 0.122 ms, Execution Time: 0.245 ms 这行信息为带有 Analyze 的 explain 语句特有。表示生成执行计划用时 0.122ms，真正执行查询用时 0.245ms。 4.2.2 执行顺序 执行计划的结构是一颗计划结点的树，通常会有 0-2 个输入，特殊节点会有多个输入，大部分情况呈现出二叉树 的结构。 每一个计划节点对应于树中的一个节点，下层节点的输出作为上层节点输入。数据 (元组) 从底层节点向上层节 点流动，直至根节点，而根节点的输出即为整个査询的结果。 由此可见，査询计划树上的节点就构成了物理元组到执行结果的管道，因此査询计划树的执行过程可以看成是拉 动元组穿过管道的过程。KingbaseES 采用了一次一元组的执行模式，每个节点被执行一次仅向上层节点返回一条元 组。因此，对于整个査询计划树的执行也是一次一元组的模式。这种模式有很多的优点： • 减少了返回元组的延迟。 • 对于某些操作 (例如游标、LIMIT 子句等) 不需要一次性获取所有的元组，节省了开销。 44 第4章 执行计划 • 减少了实现过程中缓存结果带来的代码复杂性和执行过程中临时存储的开销。 我们举一个 join 的例子来说明执行计划里各个部分： explain select t1.name, t2.name from t1, t2 where t1.id = t2.id; QUERY PLAN ------------------------------------------------------------------Merge Join (cost=176.34..303.67 rows=8064 width=64) Merge Cond: (t1.id = t2.id) -> Sort (cost=88.17..91.35 rows=1270 width=36) Sort Key: t1.id -> Seq Scan on t1 (cost=0.00..22.70 rows=1270 width=36) -> Sort (cost=88.17..91.35 rows=1270 width=36) Sort Key: t2.id -> Seq Scan on t2 (cost=0.00..22.70 rows=1270 width=36) (8 行记录) 该执行计划简化后的模型为一棵树： 45 第4章 4.2.3 执行计划 计划节点 执行计划内部是树状结构，里面包含的节点主要有 5 类：扫描节点（Scan Node），连接节点（Join Node），物 化节点（Materialization Node），控制节点（Control Node），并行节点（Parallel Node），每类节点又包括具体的 节点： 1. 扫描节点： • 顺序扫描（Seq scan） • 索引扫描（Index scan） • 位图扫描（Bitmap scan） • TID 扫描（TID scan） • 覆盖索引扫描（Index only scan） 2. 连接节点： • 嵌套循环连接（NestLoop join） • 哈希连接（Hash join） • 归并连接（Merge join） 3. 物化节点： • 物化节点（Material） • 分组节点（Group） • 排序节点（Sort） • 去重节点（Unique） 4. 控制节点： • Result 节点 • BitmapAnd/BitmapOr 节点 • Append 节点 • RecursiveUnion 节点 5. 并行节点： • Gather/GatherMerge • 并行全表扫描（Parallel SeqScan） • 并行 hash（Parallel Hash） • 并行连接（Parallel HashJoin/MergeJoin） • 并行聚集（Partial/Finalize Aggregate/HashAggregate/GroupAggregate） • 并行 Append（Parallel Append） 46 第4章 执行计划 • 并行 UNION（Parallel UNION） 并行节点详细信息请参考本手册章节使用并行 4.2.3.1 扫描节点 扫描过滤主要指数据扫描和数据过滤，目前扫描的方式有顺序扫描，索引扫描，位图扫描，TID 扫描，覆盖索引 扫描等，各自有各自的适用情况。 一般扫描过滤的性能优化建议： 1）提早过滤 2）过滤性越高的字段需要越靠前，过滤性低的靠后 3）核心 SQL 可以考虑采用覆盖索引 indexonlyscan（更新少），确保尽可能高效 4）多 SQL 综合考虑，重复利用索引 5）不干扰过滤前提下，order by 排序字段进入索引 6）根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。索引应该尽量小，尽量 使用字节数小的列建索引，不要对重复值多的列建单一索引。 4.2.3.1.1 顺序扫描（Seq scan） 原理： 根据实际的数据的存取顺序，连续的扫描所有数据。 适用情况： 数据量比较少的表，比如 1000 条以下；选择率比较高的数据，比如 50% 以上都选择出来了。 开启/关闭参数: enable_seqscan=on/off explain select * from student where sname = 'abc'; QUERY PLAN -------------------------------------------------------Seq Scan on student (cost=0.00..1.18 rows=1 width=10) Filter: (sname = 'abc'::text) (2 行记录) 4.2.3.1.2 索引扫描（Index scan） 原理： 47 第4章 执行计划 根据所建的索引，根据查询过滤条件扫描索引，因为索引是有序的，所以采用的折半查找，可以快速的找 到符合条件的索引数据，然后过滤条件和索引键值进行比较，符合条件的索引项，根据索引项记录的实际 数据存储位置读取数据，然后根据过滤条件进行过滤。 适用情况： 数据量比较大的而且选择比较少的，比如 10000 条以上记录，选择率在 1-20% 的。 开启/关闭参数: enable_indexscan=on/off explain select * from student where sname='ada'; QUERY PLAN -----------------------------------------------------------------------Index Scan using idx_stu on student (cost=0.14..8.15 rows=1 width=10) Index Cond: (sname = 'ada'::text) (2 行记录) 索引扫描一般会带着索引条件，如果索引条件不存在，性能会急剧下降，甚至比顺序扫描还要低。这是因为该情 况下，索引扫描不能过滤掉数据，而且索引一般都是排序的，根据索引得到 ctid 再次去表里随机查找数据容易导致 “随机读”的问题。 另外，如果一个包含很多记录（比如 10000 条）的表里选择率高，也容易导致“随机读”问题，此时使用顺序扫 描能获得更好的性能。 如上图所示，索引有时会造成“随机读”问题。 4.2.3.1.3 位图扫描（Bitmap scan） 原理： bitmap 扫描是 Bitmap Index Scan 和 Bitmap Heap Scan 的组合。 48 第4章 执行计划 先通过 Bitmap Index Scan 索引扫描，在内存中创建一个位图表，每一个 bit 表示一个与过滤条件有关的 页面。此页面有可能有数据为 1，不可能为 0。 然后再通过 Bitmap Heap Scan 表扫描，在内存中创建好的位图表指针对应的页面进行顺序扫描，排除不 符合的记录，返回需要的结果。 适用情况： 适用于列中有重复值，查询中包含 and、or 等范围查找的情况，其中对于有多个条件的组合（AND、 OR），可以分别对每个条件做 Bitmap Index Scan，然后再对结果进行 AND 或 OR 操作或当列中的数值 散乱在内存中，用于整理数值。 开启/关闭参数: enable_bitmapscan=on/off 4.2.3.1.4 TID 扫描（TID scan） 原理： 根据数据实际存储位置的 ctid 进行扫描，获取元组。通过隐藏字段 ctid，扫描 ctid 是 KingbaseES 中标 记数据位置的字段，通过这个字段来查找数据，速度非常快。 适用情况： 一般 where 条件带 citd 的。 开启/关闭参数: enable_tidscan=on/off 4.2.3.1.5 覆盖索引扫描（Index only scan） 原理： 允许直接从索引得到元组，覆盖索引扫描要求查询中的某个表所需要数据均可以从这个表上的同一个索引 的索引页面中获得。 适用情况： 更新少的表。 开启/关闭参数: enable_indexonlyscan=on/off 注意: 使用覆盖索引之前，需要先对表进行 VACUUM 才能使覆盖索引被优化器使用。 49 第4章 执行计划 4.2.3.1.6 其他扫描过滤节点 • Sample Scan： 数据取样功能，支持查询返回取样数据。当前只在常规表和物化视图上接受 TABLESAMPLE 子句。 • Subquery Scan： 以另一个査询计划树（子计划）为扫描对象进行元组的扫描，其扫描过程最终被转换为子计划的执行。kingbase 子查询主要包含如下几个关键字: EXISTS, IN, NOT IN, ANY/SOME, ALL。 • Function Scan 在 KingbaseES 中，有一些函数可以返回元组的集合，为了能从这些函数的返回值中获取元组，KingbaseES 定 义了 FunctionScan 节点，其扫描对象为返回元组集的函数。FunctionScan 节点在 Scan 的基础上扩展定义了 functions 列表字段，里面存放了 FuncitonScan 涉及的函数，以及 funcordinality 字段 (是否给返回结果加上序 号列)。 • Values Scan： VALUES 计算由值表达式指定的一个行值或者一组行值。更常见的是把它用来生成一个大型命令内的“常量 表”，但是它也可以被独自使用。 • CTE Scan： WITH 提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或 CTE， 它们可以被看成是定义只在一个查询中存在的临时表。在 WITH 子句中的每一个辅助语句可以是一个 SELECT、INSERT、UPDATE 或 DELETE，并且 WITH 子句本身也可以被附加到一个主语句，主语句也可以 是 SELECT、INSERT、UPDATE 或 DELETE。 • WorkTable Scan： WorkTableScan 会与 RecursiveUnion 共同完成递归合并子査询。 • Foreign Scan： 扫描 KingbaseES 外部数据表，用于 kingbase_fdw 或者 dblink 和外部数据交互的情况。 • Custom Scan： 自定义扫描接口，用户可以进行自定义扫描方法。 4.2.3.2 连接节点 连接节点主要是根据枚举、动态规划，遗传算法估算连接代价确定连接顺序。 调整方法： (1) 对所有查询的表做 analyze; 如果不做，连接顺序可能不是最优的，虽然做了也不一定最优。 (2) 调整合适的连接顺序，使得选择率低的 join 先执行 (3) 选择合适的连接算法 50 第4章 执行计划 4.2.3.2.1 嵌套循环连接（NestLoop join） 原理： 扫描每一条外表的数据（m 条数据），然后和内表所有的记录（n 条数据）去连接，时间复杂度是 o(mn) 适用情况： 数据量不大的情况 开启/关闭参数: enable_nestloop=on/off 4.2.3.2.2 哈希连接（Hash join） 原理： 对内表建立 hash 表，扫描所有内表数据到各个 hash 桶里面，建立 hash 桶，然后一行行扫描外表数据， 对外表数据进行 hash，hash 到某个桶里面，然后跟这个桶里面的数据进行连接。 适用情况： 数据分布比较随机无序，重复值不是特别多的情况。 开启/关闭参数: enable_hashjoin=on/off 4.2.3.2.3 归并连接（Merge join） 原理： 对两个表的数据进行排序，然后做连接。 适用情况： 两个表的数据都是基本有序 开启/关闭参数: enable_mergejoin=on/off 4.2.3.3 物化节点 物化节点是一类可缓存元组的节点。在执行过程中，很多扩展的物理操作符需要首先获取所有的元组后才能进行 操作 (例如聚集函数操作、没有索引辅助的排序等)，这时要用物化节点将元组缓存起来。 51 第4章 执行计划 4.2.3.3.1 物化节点（Material） 原理： Material 节点用于缓存子节点结果，对于需要重复多次扫描的子节点（特别是扫描结果每次都相同时）可 以减少执行的代价。 适用情况： 结果在子查询中会被多次使用 开启/关闭参数: enable_material=on/off 4.2.3.3.2 分组节点（Group） (1)HashAggregate 原理： 通过 hash 算法，把相同的值 hash 到同一桶里面，然后求聚集。 适用情况： 适合数据无序的情况， 开启/关闭参数: enable_hashagg=on/off (2)GroupAggregate 原理： 通过排序的方式进行分组，然后执行聚集 适用情况： 适合数据基本有序的情况 (3) Aggregate 原理： 执行含有聚集函数的 GROUP BY 操作，该节点能够实现三种执行策略：Plain (不分组的聚集 计算）、Sorted(下层节点提供排好序的元组，类似 Group 的分组方法，然后进行聚集计算）、 Hash (首先对下层节点提供的未排序元组进行分组，然后进行计算）。 适用情况： 含有聚集函数的 GROUP BY 操作 52 第4章 执行计划 4.2.3.3.3 排序节点（Sort） 原理： 明确对数据进行排序。我们不可能完全消除明确的排序，但是把关闭这个选项可以让优化器在存在其它方 法的时候优先选择其它方法。 适用情况： 输出结果是有序的的情况 开启/关闭参数: enable_sort=on/off 4.2.3.3.4 去重节点（Unique） 原理： Unique 节点用于对下层节点返回的已排序元组进行去重操作。由于下层节点获取到的元组已经排序，因 此在 Unique 节点的执行过程中只需要缓存上一个返回的元组，判断当前获 f 的元组是否和上一个元组在 指定属性上重复。如果重复，则忽略当前元组并继续从下层节点获取元组；如果不重复，则输出当前元组 并用它替换缓存中的元组。 适用情况： Unique 节点一般用于处理査询中的 DISTINCT 关键字，但这不是唯一的处理方式。如果要求去重的属性 被“ORDER BY”子句引用时，一般会使用 Unique 节点进行处理。 4.2.3.3.5 其他物化节点 • WindowAgg：窗口函数 • T_SetOp：setop 语法节点 • LockRows：使用锁定子句，比如 FOR UPDATE、FOR SHARE 等 • Limit：当使用 limit 时的节点 控制节点 4.2.3.4 4.2.3.4.1 BitmapAnd/BitmapOr 节点 原理： BitmapAnd 和 BitmapOr 节点实现了两个或多个位图的“与”和“或”运算。这两个节点的数 据组织类似于 Append 节点，将产生每一个位图的子计划放在一个链表里，在执行过程中先执 行子计划节点获取位图，然后进行“与”(“或”) 操作。下面给出了 BitmapAnd 节点 (BitmapOr 节点的也类似) 的数据结构，它们都有一个子计划的链表 (bitmapplans 字段)。但和 Append 节点不同之处在于这两个节点的子计划返回的是位图，而不是元组。 53 第4章 执行计划 适用情况： BitmapAnd 和 BitmapOr 都是位图 (Bitmap) 类型节点，用于位图计算。 4.2.3.4.2 Result 节点 原理： 执行査询计划不需要扫描表，执行器会直接计算 SELECT 的投影属性或者使用 VALUES 子句构造元组 适用情况： 1. 针对那些不扫描表的查询 2. 用来优化包含仅需计算一次的过滤条件 4.2.3.4.3 Append 节点 原理： Append 处理过程会逐个处理这些子计划 (forwards or backwards)，当一个子计划返回了所有的结果后， 会接着执行链表中的下一个子计划，直到链表中的所有子计划都被执行完。 适用情况： 用于处理包含一个或多个子计划的链表 4.2.3.4.4 RecursiveUnion 节点 原理： 对子节点递归进行处理 适用情况： RecursiveUnion 节点用于处理递归定义的 UNION 语句。 4.3 分析执行计划 分析执行计划是 SQL 性能调优需要掌握的一项重要能力，可以帮助用户定位 SQL 语句性能问题产生的原因，进 而针对性的实施优化手段。 54 第4章 执行计划 • 分析步骤 • 问题示例 • 执行计划的改变 4.3.1 分析步骤 1. 首先需要查看当前慢语句的执行计划 关于如何查看执行计划详细请参见本手册查看执行计划 2. 自上而下的阅读执行计划，理清层次结构 执行计划是数据库将要执行的查询语句转化成的一组操作树，计划中的每个节点表示数据库的一个操作， 例如表扫描，连接，分组聚集，排序等。查询计划应该按照从下向上的顺序来读和执行。 3. 对比统计信息是否准确，如果准确执行步骤 4，如果不准确 analyze 相关表后从步骤 1 重新执行。 关于统计信息详细知识及为什么进行 analyze 参见本手册优化器统计信息 4. 找出计划中耗时的节点 找出计划中耗时的节点可以辅助分析者很快的抓住计划存在的主要问题，有些时候语句的执行计划中可能 不止存在一个问题。分析者应该抓住耗时较长的节点率先解决主要问题。 5. 分析耗时节点计划的合理性 分析耗时节点的合理性可以从以下几个方面进行分析： 55 第4章 执行计划 1）数据扫描是否可以走索引、分区、物化视图 对于每个表上的扫描，考察采取的扫描方式是否合理，比如对于返回大量行数的表，通常采取 顺序扫描的方式，对于返回行数较少的表，通常采取索引扫描的方式。检查每一步采取的扫描 方式是否是最优的，这通常会对性能产生严重的影响。 2）多表连接的连接顺序是否合理 考察执行计划的层次结构，提炼出对于表的操作顺序，判断是否合理，比如有一个在三张表上 的联合查询，这三张表中有两张数据量巨大，另外一张表仅有少量数据。在做连接操作时，可 以先让一张大表和小表进行连接，再将结果与另一张大表进行连接，这可以最大程度上减少中 间结果的产生，对于性能的优化也是非常重要的 3）两表连接的连接算法是否合理 对于每一步的连接操作，考察采取的连接方式是否合理。比如查看基数估计结果是否准确导致 两表连接方式不合理等。 4）返回行数估算是否准确 比较预期返回行数与实际返回行数，考虑统计信息是否差距过大一致，进而导致选择了次优的 执行计划。 5）是否有内存不够的情况 当存在排序等情况时，操作的表超过了 work_men 时就需要考虑是否存在内存不足的情况。 随着系统的持续运行，数据规模、数据分布等因素的变化会直接影响执行计划。所以面对相同的语句计划的优劣 也不是绝对的。执行计划改变的原因详细请参见本手册执行计划的改变 4.3.2 问题示例 在分析执行计划过程中，经常遇到的问题主要有： 4.3.2.1 统计信息不准确 create table student(sno int, sname text, ssex int); create index idx_stu on student(sno); insert into student select generate_series(1,10000), left(random()::text, 10), 1; explain select * from student where sno > 2; QUERY PLAN --------------------------------------------------------Bitmap Heap Scan on student (cost=66.12..171.78 rows=3333 width=40) Recheck Cond: (sno > 2) -> Bitmap Index Scan on idx_stu (cost=0.00..65.28 rows=3333 width=0) Index Cond: (sno > 2) (4 行记录) 56 第4章 执行计划 如上例，创建的 student 表包含 10000 条记录，其 sno 字段为 1~10000 递增。但是当查询条件为 sno > 2 的时 候，选择的物理路径时 Bitmap 索引扫描，预估能够返回的条目为 3333 条。而实际符合 sno > 2 的条目有 9998 条， 即 99.98% 都是符合条件的记录，此时用索引扫描就不合适，一是需要额外扫描索引，二是得到索引的结果后，还需 要在去 student 表里取出 9998 条记录。 analyze; explain select * from student where sno > 2; QUERY PLAN ------------------------------------------------------------Seq Scan on student (cost=0.00..189.00 rows=9998 width=19) Filter: (sno > 2) (2 行记录) 通过 Analyze 命令更新统计信息后，优化器已经能够得到将有 9998 条记录返回，用顺序扫描能够得到最佳的性 能。 4.3.2.2 缺少索引 缺少索引的情况很容易理解，在没有索引的情况下，对表的访问只能是全表扫描。这对那种过滤和连接条件筛选 出结果集比较小，而表里记录又特别多的情况就特别明显。 create table t1(id int, name text); insert into t1 select (random()*100)::integer, 'test' || id from generate_series(1, 10000000) as id; explain analyze select * from t1 where name like 'test99%'; QUERY PLAN -------------------------------------------------------------------Seq Scan on t1 (cost=0.00..179053.25 rows=101009 width=15) (actual time=2.629..3221.751 rows=111111 loops=1) Filter: (name ~~ 'test99%'::text) Rows Removed by Filter: 9888889 Planning time: 0.444 ms Execution time: 3227.571 ms (5 行记录) T1 表由于没有索引，执行全表扫描，选择率为 1.11%，用时 3.2 秒多。 如果为其创建索引，则 0.12 秒即可完成查询： create index idx_t1 on t1(name text_pattern_ops); analyze; explain analyze select * from t1 where name like 'test99%'; 57 第4章 执行计划 QUERY PLAN ---------------------------------------------------Bitmap Heap Scan on t1 (cost=2787.80..58200.43 rows=101010 width=15)(actual time=46.336..110.928 rows=111111 loops=1) Filter: (name ~~ 'test99%'::text) Heap Blocks: exact=607 -> Bitmap Index Scan on idx_t1 (cost=0.00..2762.55 rows=108611 width=0) (actual time=46.118.. 46.118 rows=111111 loops=1) Index Cond: ((name ~>=~ 'test99'::text) AND (name ~<~'test9:'::text)) Planning time: 1.160 ms Execution time: 118.710 ms (7 行记录) 4.3.2.3 连接方式不准确 关于连接算法详细请参见本手册两表连接代价估算 两表连接时应该根据连接的适用条件选择合适的连接算法。 下面的例子中 rows 很小的情况下，不应该选择 HashJoin 连接，选择 NestLoop 或 MergeJoin 更合适。 create table t1(id int, val int, name varchar(64)); create table t2(id int, val int); insert into t1 select i%10, i%10, 'Kingbase'||(i%5) from generate_series(1,1000) as x(i); insert into t2 select i%15, i%5 from generate_series(1,1000) as x(i); analyze t1; analyze t2; create view v1(id, total) as (select t1.id, sum(t1.val) from t1,t2 where t1.id=t2.val group by t1.id); explain analyze select * from t1, t2, v1 where t1.id=t2.id and t2.val=v1.id and t1.id < v1.total and t2.id < 10; QUERY PLAN ------------------------------------------------------------------------------------Hash Join (cost=1700.69..2363.39 rows=14919 width=38) (actual time=71.315..96.564 rows=53600 loops=1) Hash Cond: (t1.id = t2.id) Join Filter: (t1.id < (sum(t1_1.val))) Rows Removed by Join Filter: 13300 -> Seq Scan on t1 (cost=0.00..17.00 rows=1000 width=18) (actual time=0.039..0.200 rows=1000 loops=1) -> Hash (cost=1692.33..1692.33 rows=669 width=20) (actual time=71.262..71.264 rows=669 loops=1) 58 第4章 执行计划 Buckets: 1024 Batches: 1 Memory Usage: 45kB -> Hash Join (cost=1672.32..1692.33 rows=669 width=20) (actual time=70.526..71.044 rows=669 loops=1) Hash Cond: (t2.val = t1_1.id) -> Seq Scan on t2 (cost=0.00..17.50 rows=669 width=8) (actual time=0.040..0.254 rows=669 loops=1) Filter: (id < 10) Rows Removed by Filter: 331 -> Hash (cost=1672.20..1672.20 rows=10 width=12) (actual time=70.478..70.479 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> HashAggregate (cost=1672.00..1672.10 rows=10 width=12) (actual time=70.473.. 70.476 rows=5 loops=1) Group Key: t1_1.id -> Hash Join (cost=29.50..1172.00 rows=100000 width=8) (actual time=1.694.. 31.701 rows=100000 loops=1) Hash Cond: (t2_1.val = t1_1.id) -> Seq Scan on t2 t2_1 (cost=0.00..15.00 rows=1000 width=4)(actual time=0.024..0.210 rows=1000 loops=1) -> Hash (cost=17.00..17.00 rows=1000 width=8) (actual time=1.655..1.656 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 48kB -> Seq Scan on t1 t1_1 (cost=0.00..17.00 rows=1000 width=8)(actual time=0.021..0.786 rows=1000 loops=1) Planning Time: 0.938 ms Execution Time: 98.882 ms (24 rows) 4.3.2.4 基数估计不准 一些情况下由于优化器的局限性会导致返回的行数不准。当涉及到需要多列统计（详细请参见本手册扩展统计信 息 或一些特殊情况时优化器所预估的统计信息会出现问题。 下面举例说明当 Row_Number() 函数时，优化器错误的预估了返回行数。 create table t1(id int, val int, name varchar(64)); insert into t1 select i%10, i%10, 'Kingbase'||(i%5) from generate_series(1,1000) as x(i); analyze t1; explain analyze select * from (SELECT *, Row_Number() OVER (partition by id ORDER BY val desc) v FROM t1) a where a.v=1; QUERY PLAN ----------------------------------------------------------------------------------------Subquery Scan on a (cost=61.83..94.33 rows=5 width=26) (actual time=0.509..2.387 rows=1000 loops=1) 59 第4章 执行计划 Filter: (a.v = 1) WindowAgg (cost=61.83..81.83 rows=1000 width=26) (actual time=0.488..2.178 rows=1000 loops=1) -> Sort (cost=61.83..64.33 rows=1000 width=18) (actual time=0.461..0.554 rows=1000 loops=1) Sort Key: t1.id, t1.val DESC Sort Method: quicksort Memory: 103kB -> Seq Scan on t1 (cost=0.00..12.00 rows=1000 width=18) (actual time=0.085..0.216 rows=1000 loops=1) Planning Time: 0.350 ms Execution Time: 11.337 ms (9 rows) 4.3.2.5 内存不够写临时文件 如果查询语句包含排序操作，排序算法使用的内存超过 work_mem 设置的大小后，也会使用磁盘文件来帮助排 序。 create table big(id int); insert into big values(generate_series(1, 4000000)); analyze big; explain analyze select * from big order by id; QUERY PLAN ----------------------------------------------Sort (cost=636916.43..646915.50 rows=3999630 width=14) (actual time=4037.259..5010.737 rows=4000000 loops=1) Sort Key: id Sort Method: external merge Disk: 99648kB -> Seq Scan on big (cost=0.00..61618.30 rows=3999630 width=14)(actual time=0.038..753.848 rows=4000000 loops=1) Planning Time: 0.248 ms Execution Time: 5611.819 ms (6 行记录) 观察到执行计划中提示排序使用了：external merge Disk：99648KB 后，知道工作内存不够用，调大 work mem 后，执行计划提示不再使用磁盘上的临时文件来排序： set work_mem=640000; explain analyze select * from big order by id; QUERY PLAN ---------------------------------------------------Sort (cost=117010.84..119510.84 rows=1000000 width=26) (actual time=716.270..861.068 rows=1000000 loops=1) 60 第4章 执行计划 Sort Key: id Sort Method: quicksort Memory: 110514kB -> Seq Scan on big (cost=0.00..17353.00 rows=1000000 width=26) (actual time=0.040..173.130 rows=1000000 loops=1) Planning Time: 1.171 ms Execution Time: 945.714 ms (6 行记录) 这样可以让 SQL 查询获得更高的性能。 4.3.3 执行计划的改变 执行计划在一些情况下会发生改变，以支持更好的性能表现，通过更改执行较慢语句的执行计划，可以缩短语句 执行的时间。执行计划的更改可以是自动的，比如优化器对语句进行的统计信息收集，执行计划也可以是被强制修改 的，可以考虑使用 KingbaseES 的 HINT 功能 (详细的使用请参见本手册使用 HINT )，强制修改语句的执行计划， 从而达到想要的性能效果。。 影响执行计划的几个因素罗列如下： 1. 数据规模的变化 数据的规模会影响到执行计划的选择，比如对于数据量非常庞大的表查询，适宜采用全表扫描，可以有效节省时 间，对于数据量很少的表查询，就适宜采用索引扫描。所以数据规模的变化，会影响到执行计划的选择。 2. 数据及索引的膨胀 当对表的大量数据进行更新操作时，比如突然删除或更新了大量的数据，此时索引并没有及时回收，索引的大小 仍然没有改变，导致索引页面稀疏，降低了索引使用效率，就会发生数据及索引的膨胀现象。数据及索引的膨胀会影 响到执行计划的改变。 3. 统计信息准确性的影响 对于查询优化器来说，选择执行计划是以收集到的统计信息作为参考的，如果统计信息收集的不准确，那么就会 导致次优执行计划的选择，以及执行计划的改变，对性能造成不好的影响。比如返回行数预计的不准确，那么就可能 会采用耗时更久的扫描操作，进而影响到连接操作，严重影响性能表现。 61 第 5 章 SQL 优化手段 5 第 章 SQL 优化手段 本章介绍了 SQL 调优实际操作中使用的一些调优手段： • 使用索引 • 使用 HINT • 调整性能参数 • 使用并行 • 使用 Query Mapping • 物化视图 • 分区表执行计划优化 • 逻辑优化规则 • SQL 优化建议 5.1 使用索引 索引是一种有序的存储结构，也是一项极为重要的 SQL 优化手段，可以提高数据检索的速度。通过在表中的一 个或多个列上创建索引，很多 SQL 语句的执行效率可以得到极大的提高。 62 第 5 章 SQL 优化手段 如图所示，使用索引根据索引仅读取想要查找的块，从而减少代价。 • 索引类型 • 索引使用技巧 • 使用索引建议工具 5.1.1 索引类型 5.1.1.1 Btree 索引 Btree 是索引是最常见的索引类型，也是 KingbaseES 的默认索引，采用 B+ 树 (N 叉排序树) 实现，由于树状 结构每一层节点都有序列，因此非常适合用来做范围查询和优化排序操作。Btree 索引支持的操作符有 >，<，>=，<=，=，IN，LIKE 等，同时，优化器也会优先选择 Btree 来对 ORDER BY、MIN、MAX、MERGE JOIN 进行有 序操作。 精确匹配为类似：id = 1005，name =‘abc’之类的条件。而范围匹配则为类似：id < 1005，id > 10 and id <= 100 等条件。 如果 SQL 语句仅访问被索引的列，则整个查询只需要通过索引即可完成，无需查找表数据。如果该语句还需要 访问索引列以外的其他列数据，则会使用索引指向的 tid 来查找表中的行数据。 63 第 5 章 SQL 优化手段 create table t1 (id int, info text); insert into t1 values(generate_series(1,100000), md5(random()::text)); analyze; explain analyze select * from t1 where id < 10; --无索引 QUERY PLAN ---------------------------------------------------------------------------Seq Scan on T1 (cost=0.00..2084.00 rows=10 width=37) (actual time=0.024..20.771 rows=9 loops=1) Filter: (ID < 10) Rows Removed by Filter: 99991 Planning time: 0.216 ms Execution time: 20.804 ms (5 rows) create index i_btree on t1 using btree(id); explain analyze select * from t1 where id < 10; --有 btree 索引 QUERY PLAN ---------------------------------------------------------------------------Index Scan using I_BTREE on T1 (cost=0.29..8.45 rows=9 width=37)(actual time=0.006..0.011 rows=9 loops=1) Index Cond: (ID < 10) Planning time: 0.250 ms Execution time: 0.036 ms (4 rows) explain analyze select min(id) from t1; --BTREE 索引优化聚集函数 min QUERY PLAN ---------------------------------------------------------------------------Result (cost=0.33..0.34 rows=1 width=4) (actual time=0.144..0.144 rows=1 loops=1) InitPlan 1 (returns $0) Limit (cost=0.29..0.33 rows=1 width=4) (actual time=0.132..0.135 rows=1 loops=1) -> Index Only Scan using I_BTREE on T1 (cost=0.29..3691.29 rows=100000 width=4) (actual time=0.129..0.130 rows=1 loops=1) Index Cond: (ID IS NOT NULL) Heap Fetches: 1 Planning time: 0.309 ms Execution time: 0.221 ms (8 rows) 64 第 5 章 SQL 优化手段 5.1.1.2 Hash 索引 Hash 索引查询效率比 Btree 高得多，相比 Btree 索引需要从根节点查询到叶子节点，且需要多次 IO 及 CPU 操 作，Hash 索引因其索引结构的特殊性，理想情况下仅需一次检索即可定位到数据。 与此同时，Hash 也存在一定的局限性，只适合做等值查询。由于 Hash 索引是通过比较哈希值来做定位，因此 当查询列上有较大比例的重复值时，会带来严重的哈希冲突，进而影响查询速度，这种情况下就不建议使用 Hash 索 引。 create table t2 (id int, info text); insert into t2 values(generate_series(1,100000), md5(random()::text)); analyze; create index i_hash on t2 using hash(id); explain analyze select * from t2 where id = 10; --HASH 索引 QUERY PLAN ---------------------------------------------------------------------------Index Scan using I_HASH on T2 (cost=0.00..8.02 rows=1 width=37) (actual time=0.013..0.014 rows=1 loops=1) Index Cond: (ID = 10) Planning time: 0.167 ms Execution time: 0.033 ms (4 rows) explain analyze select * from t2 where id < 10; --HASH 索引不支持非等值操作 QUERY PLAN ---------------------------------------------------------------------------Seq Scan on T2 (cost=0.00..2084.00 rows=10 width=37) (actual time=0.015..26.223 rows=9 loops=1) Filter: (ID < 10) Rows Removed by Filter: 99991 Planning time: 0.070 ms Execution time: 26.245 ms (5 rows) 5.1.1.3 Bitmap 索引 (执行节点) Bitmap 索引是用一个位图来存放记录索引信息的结构。它用一个 bit 位来表示某个属性对应的值是否存在，存 在为 1，不存在为 0，比较适合那种值比较单一（比如：性别字段）的情况。Bitmap 索引有很大的压缩比空间，比较 适合 OLAP 应用。 Kingbase 在为 where x=33 or x=44 or x= 55 类似的查询创建查询计划的时候，将 where 过滤分解成 3 个独立 的基于 x 字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果 OR 在一起并生成最终的结果。另外 65 第 5 章 SQL 优化手段 一个例子是，如果我们在 x 和 y 上分别存在独立的索引，那么一个类似 WHERE x = 5 AND y = 6 的查询，就会分 别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行 AND 操作并生成最终的结果行。 为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个 BITMAP，它将给出索引扫描出的数据 在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行 AND 或者 OR 的操作并得出最终的 BITMAP。 最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原 来的索引的排序都将消失。如果查询中有 ORDER BY 子句，那么还将会有一个额外的排序步骤。因为这个原因，以 及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用 也会如此。 Bitmap 索引主要适用于当表具有很多属性且查询可能会涉及其中任意组合时的情况。 优化器为普通索引建立位图表，即 Bitmap heap table，主要包括 2 种扫描方式： • Bitmap Index Scan：用来在内存中创建一个位图表，每一个 Bit 表示一个与过滤条件有关的页面。Bit 上有数 据为 1，不可能为 0。通过位图扫描，能够快速确定某个值在哪些记录上存在与否。 • Bitmap Heap Scan：Bitmap 上检索到的记录都对应其 rowid，用这个 rowid 到关系表上去查找完整的记录信息 并过滤。 Bitmap index/heap scan 示例： create table t1(id int); create index idx_t1 on t1(id); explain select * from t1 where id = 200; QUERY PLAN ---------------------------------------------------------------------Bitmap Heap Scan on t1 (cost=4.26..14.95 rows=13 width=4) Recheck Cond: (id = 200) -> Bitmap Index Scan on idx_t1 (cost=0.00..4.25 rows=13 width=0) Index Cond: (id = 200) (4 行记录) 5.1.1.4 Bitmap 索引 (索引 AM) Bitmap 索引使用保存在磁盘上的位图结构保存索引信息。与执行节点的 Bitmap 索引的区别是：执行节点的 Bitmap 索引是将计算过程需要的数据以位图的形式进行运算，而索引 AM 的 Bitmap 索引是将索引信息压缩存储 在磁盘上。 在创建 Bitmap 索引时，系统将扫描目标列，创建内部表并保存列中每一个独特值。然后针对每一个独特值，按 其原始数据在表中的位置创建位图并压缩存储在磁盘中。在查询过程中，系统将先查询内部表中记录的目标值位图页 的位置，然后在该位置读取目标值位图信息。需要注意的是，考虑到 Bitmap 索引的存储结构，对 Bitmap 索引相关 列的 Update 操作将会导致 Bitmap 索引的解压缩和重压缩，因此性能会较为缓慢。 Bitmap 索引适用的限制为：- 不会进行 Update 操作的的数据列。- 列的基数值在 1-10000 之间。（若为多列索 引，则基数值为各列的基数的乘积） 66 第 5 章 SQL 优化手段 test=# create table test(a int4, b int4); CREATE TABLE test=# create index idx_t on test using bitmap(a); CREATE INDEX test=# insert into test select round(random()*3),round(random()*100) from generate_series(1,1000000); INSERT 0 1000000 test=# analyze; ANALYZE test=# explain analyze select count(*) from test where a = 1; --使用 Bitmap 索引查询 QUERY PLAN ------------------------------------------------------------------------------------------------------Finalize Aggregate -> Gather (cost=10198.22..10198.23 rows=1 width=8) (actual time=164.351..166.177 rows=1 loops=1) (cost=10198.00..10198.21 rows=2 width=8) (actual time=164.090..166.161 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=9198.00..9198.01 rows=1 width=8) (actual time=144.485..144.487 rows=1 loops=3) -> Parallel Bitmap Heap Scan on test (cost=2701.12..8852.69 rows=138125 width=0) (actual time=82.470..129.104 rows=111086 loops=3) Recheck Cond: (a = 1) Heap Blocks: exact=1646 -> Bitmap Index Scan on idx_t (cost=0.00..2618.25 rows=331500 width=0) (actual time=99. 981..99.981 rows=333258 loops=1) Index Cond: (a = 1) Planning Time: 0.515 ms Execution Time: 166.334 ms (12 rows) 5.1.1.5 GIN 索引 GIN 是通用倒排序索引（Generalized Inverted Index），它是一个存储对（Key，postion list）的集合，其中 Key 是一个键值，而 postion list 是包含 Key 的位置值。比如（’Tom’，’10:25 14:3 29:5’）就表示关键字’Tom’ 在 这些位置（元组 TID）上存在。当我们用关键字’Tom’ 去查询的时候，能一下就定位到包含关键字的元组有这三个。 ‘Tom’-> TID5->TID10->TID100 ‘Ada’-> TID5->TID99 … ‘Bob’-> TID99->TID110->TID200 给定查询的关键字‘Tom’，立即就能知道包含该关键字的元组在哪里能找到。 通用倒排序索引（GIN）主要适用于包含多个组合值的查询，如数组、全文检索等。 67 第 5 章 SQL 优化手段 create table t3(id int, info text); insert into t3 values(generate_series(1,10000), md5(random()::text)); create index i_t3_gin on t3 using gin(to_tsvector('english',info)); analyze; explain analyze select * from t3 where to_tsvector('english', info) @@ plainto_tsquery( 'hello'); --使用 GIN 索引的全文检索 QUERY PLAN ---------------------------------------------------------------------------Bitmap Heap Scan on T3 (cost=12.25..16.77 rows=1 width=37) (actual time=0.029..0.029 rows=0 loops=1) Recheck Cond: (TO_TSVECTOR('ENGLISH'::REGCONFIG, INFO) @@ PLAINTO_TSQUERY('hello'::TEXT)) -> Bitmap Index Scan on I_T3_GIN (cost=0.00..12.25 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=1) Index Cond: (TO_TSVECTOR('ENGLISH'::REGCONFIG, INFO) @@ PLAINTO_TSQUERY('hello'::TEXT)) Planning time: 0.460 ms Execution time: 0.068 ms (6 rows) 5.1.1.6 GiST 索引 GiST 是通用的搜索树 (Generalized Search Tree)。它是一种平衡树结构的访问方法，在系统中作为一个基本模 版，可以使用它实现任意索引模式。B-trees，R-trees 和许多其它的索引模式都可以用 GiST 实现。 GiST 索引适用于多维数据类型和集合数据类型，和 Btree 索引类似，同样适用于其他的数据类型。GiST 可以用 来做位置搜索，如包含、相交、左边、右边等。 以下是一个 GiST 索引使用示例： create table t1(id int, pos1 point); insert into t1 select generate_series(1,100000),point(round((random()*1000)::numeric, 2), round((random()*1000)::numeric, 2)); create index idx_t1_gist on t1 using gist(pos1); analyze t1; explain select * from t1 where circle '((100,100) 10)' @> pos1; QUERY PLAN ---------------------------------------------------------------------------Bitmap Heap Scan on t1 (cost=5.06..272.12 rows=100 width=20) Recheck Cond: ('<(100,100),10>'::circle @> pos1) -> Bitmap Index Scan on idx_t1_gist (cost=0.00..5.03 rows=100 width=0) Index Cond: (pos1 <@ '<(100,100),10>'::circle) (4 rows) 68 第 5 章 SQL 优化手段 5.1.1.7 SP-GiST 索引 SP-GiST 是“space-partitioned GiST”的缩写，即空间分区 GiST 索引。它主要是通过一些新的索引算法提高 GiST 索引在某个情况下的性能。它与 GiST 索引一样，是一个通用的索引框架，基于此框架可以开发出自定义的空 间分区索引。 要实现一个自定义的 SP-Gist 索引，需要实现 5 个自定义函数： • Config：返回索引实现中的一些静态信息 • Choose：选择如何把新的值插入到索引内部 tuple 中 • Picksplit：决定如何在一些叶子 tuple 上创建一个新的内部 tuple • Inner_consistent：在树的搜索过程中返回一系列树杈上的节点 • Leaf_consistent：返回叶子节点是否满足查询 SP-GiST 适用于空间可以递归分割成不相交区域的结构，包括四叉树、k-D 树和基数树。 KingbaseES 已经对一些数据类型实现了 SP-GiST 索引操作类，在这些类型上可以直接创建 SP-GiST 索引： 操作类名称 数据类型 索引操作符 quad_point_ops Point «，<@，<^，»，>^，~= kd_point_ops Point «，<@，<^，»，>^，~= range_ops Range &&，&<，&>，-|-，«，<@，=，»，@> text_ops Text <，<=，=，>，>=，~<=~，~<~，~>=~,~>~ 以下是 SP-GiST 使用 point 的示例： create table t1(p point); create index idx_t1 on t1 using spgist(p); analyze t1; explain select * from t1 where p << point(10, 10); QUERY PLAN ----------------------------------------------------------------------Bitmap Heap Scan on t1 (cost=5.57..17.87 rows=184 width=16) Recheck Cond: (p << '(10,10)'::point) -> Bitmap Index Scan on idx_t1 (cost=0.00..5.52 rows=184 width=0) Index Cond: (p << '(10,10)'::point) (4 rows) 69 第 5 章 SQL 优化手段 5.1.1.8 BRIN 索引 BRIN 索引是块范围索引的简称，由于其存储了表的连续数据块区间以及对应的数据取值范围，因此 BRIN 索引 的体积和维护代价相比其他索引低很多。 BRIN 索引适用于存储流式的数据日志。例如：按照时间插入的数据。由于数据是按照时间插入，因此数据块上 记录的范围信息很少会出现交叉情况，索引过滤后需要比较的数据块也会少很多；反之，如果数据交叉严重，通过索 引无法过滤掉任何一个数据块时，操作起来会比全表扫描更加耗时。 create table t5(id int, name text); insert into t5 values(generate_series(1,100000), md5(random()::text)); analyze; create index i5_brin on t5 using brin(id); explain analyze select * from t5 where id < 10; --使用 BRIN 索引，索引仅能过滤到连续的数据块级别，需要对块内 的数据做再次比较 QUERY PLAN -------------------------------------------------------------------Bitmap Heap Scan on T5 (cost=12.08..48.92 rows=10 width=37) (actual time=0.039..2.498 rows=9 loops=1) Recheck Cond: (ID < 10) Rows Removed by Index Recheck: 15351 Heap Blocks: lossy=128 -> Bitmap Index Scan on I5_BRIN (cost=0.00..12.07 rows=10 width=0) (actual time=0.028..0.028 rows=1280 loops=1) Index Cond: (ID < 10) Planning time: 0.178 ms Execution time: 2.527 ms (8 rows) create index i5_btree on t5 using btree(id); --BRIN 索引大小远小于 BTREE \di+ List of relations Schema | Name | Type | Owner | Table | Size | Description --------+----------+-------+-------+-------+---------+------------PUBLIC | I5_BRIN | index | nimo | T5 | 48 kB | PUBLIC | I5_BTREE | index | nimo | T5 | 2208 kB | (2 rows) 70 第 5 章 SQL 优化手段 5.1.2 索引使用技巧 5.1.2.1 使用表达式索引 KingbaseES 支持创建基于标量表达式的索引。 最常见的就是创建基于函数的索引。比如在做大小写无关的比较时，经常使用 lower(name) 或者 upper(name) 函 数。但是因为用了函数，无法利用字段 name 上的普通索引，所以此时需要一个函数索引： create table t1(name text); create index idx_t1 on t1(upper(name)); explain select * from t1 where upper(name) = 'ada'; QUERY PLAN ------------------------------------------------------------Bitmap Heap Scan on t1 (cost=4.21..14.37 rows=7 width=32) Recheck Cond: (upper(name) = 'ada'::text) -> Bitmap Index Scan on idx_t1 (cost=0.00..4.21 rows=7 width=0) Index Cond: (upper(name) = 'ada'::text) (4 行记录) 也可以创建基于复杂表达式的函数索引（表达式需要用括号括起来）： create table t1(id int, first_name text, last_name text); create index idx_t1 on t1((first_name || ' ' || last_name)); explain select * from t1 where (first_name || ' ' || last_name) = 'Ada B'; QUERY PLAN --------------------------------------------------Bitmap Heap Scan on t1 (cost=4.18..12.66 rows=4 width=68) Recheck Cond: (((first_name || ' '::text) || last_name) = 'Ada B'::text) -> Bitmap Index Scan on idx_t1 (cost=0.00..4.18 rows=4 width=0) Index Cond: (((first_name || ' '::text) || last_name) = 'Ada B'::text) (4 行记录) 表达式索引里面存储的值是表达式的值，所以它并不是在进行索引查找时去计算表达式的，而是在插入数据行或 者更新数据行时进行计算的。因此在插入或者更新数据时，函数索引会慢一些。 5.1.2.2 使用局部索引 局部索引（partial index）是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的（叫做局部索 引的谓词）。该索引只包含表中那些满足这个谓词的行。 由于不是在所有的情况下都需要更新索引，只有新增的符合局部索引条件表达式的数据才会写入到索引里，因此 局部索引会提高数据插入和数据更新的效率，减少了索引的维护成本。又因为局部索引比普通索引要小，因此可以更 71 第 5 章 SQL 优化手段 好的提高确实需要索引部分的查询效率。 比如，t1 里面有 100 万行记录，SQL 应用进程查询的是 id < 500 的那批记录，则可以为它建立局部索引： create table t1(id int); create index idx_t1 on t1(id) where id < 500; explain select * from t1 where id < 400; QUERY PLAN ------------------------------------------Bitmap Heap Scan on t1 (cost=4.35..24.97 rows=850 width=4) Recheck Cond: (id < 400) -> Bitmap Index Scan on idx_t1 (cost=0.00..4.13 rows=850 width=0) Index Cond: (id < 400) (4 行记录) explain select * from t1 where id > 400; QUERY PLAN ----------------------------------------------------Seq Scan on t1 (cost=0.00..41.88 rows=850 width=4) Filter: (id > 400) (2 行记录) 可以看出，符合索引条件范围的查询，能够使用创建的局部索引，不符合条件的则不能。 KingbaseES 支持带任意谓词的局部索引，只要涉及被索引的表的字段就可以。不过谓词必须和那些希望从该索 引中获益的查询中的 where 条件相匹配。准确的说只有在系统识别出该查询的 where 条件简单地包含了该索引的谓 词时，此局部索引才能用于该查询。KingbaseES 还不可以完全识别形式不同但数学上相等的谓词，但可以识别简单 的不相等的包含，谓词条件必须准确匹配查询的 where 条件，不然系统将无法识别该索引是否可用。 由于条件匹配发生在执行计划的规划期间而不是运行期间，因此带绑定变量的条件不能使用局部索引。 5.1.2.3 使用联合索引 联合索引是在建立在某个关系表上多列的索引，也叫复合索引。创建联合索引时，应该将最常被访问的列放在索 引列表前面。当 where 子句中引用了联合索引中的所有列，或者前导列，联合索引可以加快检索速度。 create table student(id int, name text, school text); create index idx_student on student(id, name, school); 当查询条件为：1）id；2）id 和 name；3）id 和 school；4）id、name 和 school 时，都可以使用 idx_student 联合索引。 当查询条件不包括 id 时，则无法使用联合索引。 72 第 5 章 SQL 优化手段 5.1.2.4 使用索引提升 Like 模式匹配性能 KingbaseES 产品支持基于 Like 表达式的通配符模式匹配，通过合理的应用索引，可以提高查询性能。 5.1.2.4.1 前匹配或者精确匹配使用 Btree 索引 普通的 btree 索引能够在 2 种情况下支持 like 操作符使用索引： 1. 精确匹配型 like create table t1(id int, name text); create index idx_t1 on t1(name); explain select * from t1 where name like 'abc'; QUERY PLAN ---------------------------------------------------------------------Bitmap Heap Scan on t1 (cost=4.67..61.54 rows=50 width=36) Filter: (name ~~ 'abc'::text) -> Bitmap Index Scan on idx_t1 (cost=0.00..4.66 rows=50 width=0) Index Cond: (name = 'abc'::text) (4 行记录) 精确匹配下，优化器能够将 name like ’abc’ 转换为 name = ’abc’ 的索引条件。 2. 索引字段为“C”collate create table t1(id int, name text collate "C"); create index idx_t1 on t1(name); explain select * from t1 where name like 'abc%'; QUERY PLAN ---------------------------------------------------------------------Bitmap Heap Scan on t1 (cost=4.21..13.68 rows=6 width=36) Filter: (name ~~ 'abc%'::text) -> Bitmap Index Scan on idx_t1 (cost=0.00..4.21 rows=6 width=0) Index Cond: ((name >= 'abc'::text) AND (name < 'abd'::text)) (4 行记录) Collate 为”C” 的字段做 like 操作时，也被转换为”>= AND <” 的一对索引条件。 其他情况则需要使用 text_pattern_ops，bpchar_pattern_ops 指定的 btree 索引： 3. like const 表达式： create table t1(id int, name text); create index idx_t1 on t1(name text_pattern_ops); explain select * from t1 where name like 'abc%'; QUERY PLAN 73 第 5 章 SQL 优化手段 --------------------------------------------------------Bitmap Heap Scan on t1 (cost=4.21..13.68 rows=6 width=36) Filter: (name ~~ 'abc%'::text) -> Bitmap Index Scan on idx_t1 (cost=0.00..4.21 rows=6 width=0) Index Cond: ((name ~>=~ 'abc'::text) AND (name ~<~ 'abd'::text)) (4 行记录) 4. like var 表达式： create table t1(id int, name text); create table t2(id int, name text); create index idx_t2 on t2(name text_pattern_ops); explain select t1.name, (select count(*) from t2 where t2.name like t1.name || '%') as cnt from t1; KingbaseES 产品也支持对 a like b，其中 b 为一个变量表达式的情况，使用 btree 索引。 限制： Btree 索引应用于 like 模糊查询时，只能做前匹配（a like‘abc%’）或者精确匹配（a like ‘abc’），不能做 后匹配（a like ‘%abc’）和中间匹配（a like ‘%abc%’），这是由 btree 索引只能做大于、大于等于、等于、小 于、小于等于等操作决定的。 5.1.2.4.2 后匹配 reverse() 表达式 如果只有后匹配查询需求，比如字符串 like ‘%abc’，使用 collate “C”的 reverse() 表达式的 Btree 索引。字 符串 like ‘%abc’等价于 reverse(字符串) like ‘cba%’，将其转换成类似前匹配的方式。 explain select * from t1 where reverse(t1.name) like '2%'; QUERY PLAN -------------------------------------------------------------------Seq Scan on t1 (cost=10000000000.00..10000000017.00 rows=5 width=18) Filter: (reverse((name)::text) ~~ '2%'::text) (2 rows) create index on t1(reverse(name) collate "C"); analyze t1; explain select * from t1 where reverse(t1.name) like '2%'; QUERY PLAN -------------------------------------------------------------------Bitmap Heap Scan on t1 (cost=6.20..11.20 rows=200 width=18) Filter: (reverse((name)::text) ~~ '2%'::text) -> Bitmap Index Scan on t1_reverse_idx (cost=0.00..6.15 rows=200 width=0) 74 第 5 章 SQL 优化手段 Index Cond: ((reverse((name)::text) >= '2'::text) AND (reverse((name)::text) < '3':: text)) (4 rows) 5.1.2.4.3 中间匹配使用 TRGM 索引 sys_trgm 是一个扩展模块，它提供函数和操作符测定字母数字文本基于三元模型匹配的相似性，还有支持快速 搜索相似字符串的索引操作符类。 三元模型是一组从一个字符串中获得的三个连续的字符。我们可以通过计数两个字符串共享的三元模型的数量来 测量它们的相似性。 sys_trgm 从一个字符串提取三元模型时忽略非文字字符（非字母）。当确定包含在字符串中的三元模型集合 时，每个单词被认为有两个空格前缀和一个空格后缀。例如，字符串”cat” 中的三元模型的集合是 ”c”，”ca”，”cat” 和”at”。字符串”foo|bar” 中的三元模型的集合是 ”f”，”fo”，”foo”，”oo”，”b”，”ba”，”bar” 和”ar”。 sys_trgm 模块提供 GiST 和 GIN 索引操作符类，并且额外支持基于三元模型的索引搜索：LIKE，ILIKE，~ 和 ~* 查询。 sys_trgm 支持 like 模糊匹配示例： create extension sys_trgm; create table t1(id int, name varchar(20)); create table t2(id int, name varchar(20)); create index idx_t2_trgm on t2 using gin(name gin_trgm_ops); explain select t1.id, (select count(*) from t2 where t2.name like t1.name || '%') as cnt from t1; QUERY PLAN -------------------------------------------------------------Seq Scan on t1 (cost=0.00..14894.01 rows=900 width=12) SubPlan 1 Aggregate (cost=16.52..16.53 rows=1 width=8) -> Bitmap Heap Scan on t2 (cost=8.04..16.51 rows=4 width=0) Recheck Cond: ((name)::text ~~ ((t1.name)::text || '%'::text)) -> Bitmap Index Scan on idx_t2_trgm (cost=0.00..8.04 rows=4 width=0) Index Cond: ((name)::text ~~ ((t1.name)::text || '%'::text)) (7 行记录) 不过对于 like 的部分匹配和全匹配情况，trgm 索引的性能可能会弱于 btree 索引。trgm 索引并不支持相等也不 支持简单的比较操作符，所以你可能也需要一个普通的 B-tree 索引。 5.1.2.5 删除不必要的索引 通过查看系统表 sys_stat_user_indexes，来观察哪些索引表从来没有被使用。对于长期不使用的索引可以删除 它们，以便减少磁盘占用空间和维护索引的代价。 75 第 5 章 SQL 优化手段 select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from sys_stat_user_indexes order by idx_ scan, idx_tup_read, idx_tup_fetch; relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch --------+--------------+----------+--------------+--------------bar | idx_btree_bar| 0 | 0 | 0 t1 | idx_t1_name | 1 | 1 | 3 (2 rows) 5.1.2.6 定期 vacuum 和重建索引 KingbaseES 数据库执行 delete 操作后，表中的记录只是被标示为删除状态，并没有释放空间，在以后的 update 或 insert 操作中该部分的空间不能够被重用。可以利用数据库磁盘空间工具 VACUUM，其作用是删除那些已经标示 为删除的数据并释放空间。但 vacuum 工具不能够对相应的索引进行清理，需要手动去重建索引。 另外，对于 Btree 索引，只有那些已经完全清空的索引页才会得到重复使用，对于那些仅部分空间可用的索引页 将不会得到重用，如果一个页面中大多数索引键值都被删除，只留下很少的一部分，那么该页将不会被释放并重用。 在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不 仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。对于重建后的索引还存在另外一个性能上的优势， 因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的 几率，从而提高整个操作的 IO 效率。 可以在以下几个级别重建索引： • 数据库： reindex database d1; • 表：reindex table t1; • 单个索引：reindex index idx1; 重置索引后，需要执行 ANALYZE 命令重新分析一下表。 5.1.2.7 其他建议 索引的使用通常能够提高 select，update 以及 delete 语句的性能，但会降低 insert 语句的性能，因此索引并非是 越多越好，使用索引应该遵循以下原则： • 仅当要通过索引访问表中很少的一部分记录（1%~20%） • 更新较少的表可以考虑使用覆盖索引 覆盖索引扫描，允许直接从索引得到元组，覆盖索引扫描要求查询中的某个表所需要的数据均可以从这个表上的 同一个索引的索引页面中获得。 使用覆盖索引之前, 需要先对表进行 VACUUM 才能使覆盖索引被优化器使用。对于更新频繁的表，需要多次 VACUUM。 76 第 5 章 SQL 优化手段 不合适建索引的场合： • 索引列上有函数（确定性的函数可以创建函数索引） • 索引列选择率差，不如全表扫描 • 不要索引常用的小型表，其维护代价有时会高于其收益 • 不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间 5.1.3 使用索引建议工具 索引建议根据数据库服务器运行过程中 where 和 join 里的统计信息，根据 SQL 语句提示哪些关系表如果创建了 相应的索引后，性能会有很大提升。 索引建议能够针对：select、update、delete、insert into…select、explain 等语句，提供包括：btree、bitmap、 hash、gin、gist、brin、spgist 访问类型的索引建议。 索引建议列表里面包括的信息有：SQL 语句、索引建议（create index DDL）、当前无索引时 cost、创建索引后 的 cost、cost 提升百分比等信息。 索引建议的使用，需要使用 KingbaseES V9 的版本。其基本使用方法如下： 1. 编辑 kingbase.conf 文件，添加以下内容后重启服务器： shared_preload_libraries = ‘sys_stat_statements, sys_qualstats’; sys_qualstats.enabled = true 2. 创建相关扩展组件： create extension sys_qualstats; create extension sys_hypo; 3. 执行 SQL，查询索引建议： select * from t1 where id = 100; select * from index_recommendation_by_qual; select * from index_recommendation_by_index; 具体使用情况，请参考 KingbaseES 扩展插件参考手册。 5.2 使用 HINT 本章节包含以下内容： • 概述 • HINT 的功能 77 第 5 章 SQL 优化手段 • HINT 的使用 • 配置参数 • 示例 • 注意 5.2.1 概述 KingbaseES 使用的是基于成本的优化器。优化器会估计 SQL 语句的每个可能的执行计划的成本，然后选择成本 最低的执行计划来执行。因为优化器不计算数据的某些属性，比如列之间的相关性，优化器有时选择的计划并不一定 是最优的。 Hint 的作用就是通过使用特殊形式的注释中的 hint 短语来指定执行 SQL 语句所用的执行计划。Hint 为用户提 供了直接影响执行计划生成的手段，用户可以通过指定 join 顺序，join、scan 方法，指定结果行数等多个手段来进行 执行计划的调优，以提升查询的性能。 5.2.2 HINT 的功能 从 V8R6C4 开始增加了新的 HINT 功能，并对其中的一些功能进行了增强。 版本 功能 V8R6C4 表扫描 SeqScan(table) TidScan(table) IndexScan(table[ index...]) IndexOnlyScan(table[ index...]) BitmapScan(table[ index...]) NoSeqScan(table) NoTidScan(table) NoIndexScan(table) NoIndexOnlyScan(table) NoBitmapScan(table) IndexScanRegexp (table[regexp...]) BitmapScanRegexp (table[regexp...]) IndexOnlyScanRegexp(table[regexp...]) ForceSeqScan(table) ForceTidScan(table) ForceIndexScan(table[ index...]) ForceIndexOnlyScan(table[ index...]) ForceBitmapScan(table[ index...]) 见续表 78 第 5 章 SQL 优化手段 表 5.2.1 – 续表 版本 功能 连接方式 NestLoop(table table[ table...]) HashJoin(table table[ table...]) MergeJoin(table table[ table...]) NoNestLoop(table table[ table...]) NoHashJoin(table table[ table...]) NoMergeJoin(table table[ table...]) ForceNestLoop(table table[ table...]) ForceHashJoin(table table[ table...]) ForceMergeJoin(table table[ table...]) 连接顺序 leading(join_table_list) leading((outer_table inner_table)) 行数更正 rows(table_list #|+|-|* const) 并行执行 Parallel(table_name workers) 设置 GUC Set(Param_Name Param_Value) 参数 V8R6C5 块命名 blockname(subquery_name) Nestloop 内 materialize(inner_table_list) 表物化 Nestloop 内 use_nl_with_index(inner_table) 表使用索引 表连接顺序 ordered 聚集 Hashagg Groupagg 5.2.3 HINT 的使用 HINT 通过在目标 SQL 语句 SELECT 之后给出的特殊形式的注释来读取 HINT 注释。注释的形式以字符序列 ‘/+’开头以‘/’结尾，例如/+SeqScan(tablename)/，其具体使用方法如下： 1. 在 kingbase.conf 配置文件中，配置： a. enable_hint = on 2. 启动数据库 79 第 5 章 SQL 优化手段 3. 使用 Hint 的注释使 Hint 生效： a. Scan 类型的 hint：指明单表扫描时在目标表上使用特定的扫描方法。它只会在普通表、继承表、 UNLOGGED 表、临时表和系统目录上生效。外部表、表函数、VALUES 子句、CTE、视图和子查 询不受影响。其括号内的值只能是一个表名。但可以在一个查询中，对其中的多个目标表进行不同 方式的干预。 HINT 类型 描述 SeqScan(table) 优先在表上使用顺序扫描。 TidScan(table) 优先在表上使用 Tid 扫描。 IndexScan(table[ index...]) 优先在表上使用索引扫描，只限制指定的索 引。 IndexOnlyScan(table[ index...]) 优先在表上使用 Index only scan，限制特定 的索引。当 Index only s can 不可用时，可以 使用索引扫描。 BitmapScan(table[ index...]) 优先在表上使用位图扫描。 NoSeqScan(table) 优先不在表上使用顺序扫描。 NoTidScan(table) 优先不在表上使用 Tid 扫描。 NoIndexScan(table) 优先不在表上使用索引扫描和 index only scan。 NoIndexOnlyScan(table) 优先不在表上使用 index only scan。 NoBitmapScan(table) 优先不在表上使用位图索引。 IndexScanRegexp (table[regexp...]) 优先在表上使用索引扫描。索引名要满足指 定的正则表达式。 BitmapScanRegexp (table[regexp...]) 优先在表上使用位图扫描。索引名要满足指 定的正则表达式。 IndexO nlyScanRegexp(table[regexp...]) 优先在表上使用 Index only scan。索引名要 满足指定的正则表达式。 ForceSeqScan(table) 强制在表上使用顺序扫描。 ForceTidScan(table) 强制在表上使用 Tid 扫描。 ForceIndexScan(table[ index...]) 强制在表上使用索引扫描，只限制指定的索 引。 见续表 80 第 5 章 SQL 优化手段 表 5.2.2 – 续表 HINT 类型 描述 ForceIndexOnlyScan(table[ index...]) 强制在表上使用 Index only scan，限制特定 的索引。当 Index only s can 不可用时，可以 使用索引扫描。 ForceBitmapScan(table[ index...]) 强制在表上使用位图扫描。当指定索引名之 后，位图扫描会使用指定的索引进行扫描。 b. 并行执行类型的 hint：在扫描时强制执行或禁止并行执行。它可以对普通表，继承父项，UNLOGGED 表和系统目录产生影响。外部表，表函数，值子句，CTE，视图和子查询不受影响： Parallel(table_name workers) c. ROWS 类型的 hint：指明中间结果集的大小，支持绝对值和相对值。该 Hint 可以修正因优化器的限 制而连接操作后错估的行数。该 hint 支持指定多个表的连接预估行数，也可以指定简单表，继承或 分区表，外表，子查询，CTE 的行数： rows(table_list #|+|-|* const) d. Join 类型的 hint：在指定的表进行连接时，强制使用特定的连接方法。它可以影响普通表、继承 表、UNLOGGED 表、临时表、外部表、系统目录、表函数、VALUES 命令结果和 CTE 上的连接。 但是视图上的连接和子查询不受影响： HINT 类型 描述 NestLoop(table table[ table...]) 在对指定的表进行连接时，使用循环嵌套连接。 HashJoin(table table[ table...]) 在对指定的表进行连接时，使用散列连接。 MergeJoin(table table[ table...]) 在对指定的表进行连接时，使用排序合并连接。 NoNestLoop(table table[ table...]) 在 对 指 定 的 表 进 行 连 接 时， 不 使 用 循 环 嵌 套 连 接。 NoHashJoin(table table[ table...]) 在对指定的表进行连接时，不使用散列连接。 NoMergeJoin(table table[ table...]) 在 对 指 定 的 表 进 行 连 接 时， 不 使 用 排 序 合 并 连 接。 e. Leading 类型的 hint：指定在两个或多个表进行连接时的连接顺序，包括内外表顺序。一个查询块上 只能有一个 leading 类型的 hint 生效，ordered hint 具有最高的优先级，当没有 ordered 而有多个 leading 时，最后一个起作用： • 仅指定 join 顺序，不指定内外表顺序： 81 第 5 章 SQL 优化手段 leading(join_table_list) • 同时指定 join 顺序和内外表顺序： leading((outer_table inner_table)) • 按照 SQL 中表出现的顺序指定连接的顺序： ordered f. SET 类型的 hint：可以对查询语句块设置用户级别的配置参数，即通过 SET 语句可以修改的参数。 参数影响的为该语句块及其子查询涉及的表的执行计划生成： Set(Param_Name Param_Value) g. Blockname 类型的 hint：为子查询或子链接命名。Hint 支持跨查询块的对象引用。即一个查询块中 的 hint 可以通过”块名. 表名的”的形式控制其子查询块中的表。当有些子查询块没有名字标识时 (例如子链接)，可以使用该 hint 来进行命名： blockname(subquery_name) h. Materialized 类型的 hint：可多个表进行连接时，强制使用 nestloop 连接且内表为 inner_table_list 指定的一个或多个表，并且对内表进行物化： materialize(inner_table_list) i. Use_nl_with_index 类型的 hint：可多个表进行连接时，强制使用 nestloop 连接且内表为 inner_table 指定的表，并且内表使用连接条件上的列使用索引扫描： use_nl_with_index(inner_table) j. Aggregate 类型的 hint：KingbaseES 的聚合运算的算法有两种：排序 (GroupAgggregate) 和哈希 (HashAggregate)。通过 hint 可以影响优化器选择哪种聚合运算： Hashagg Groupagg 5.2.4 配置参数 参数名 描述 默认值 enable_hint 启用 HINT off hint_debug_print 是否打印 HINT 的 debug 信息 off hint_message_level 指定 de bug 打印的信息级别。可用的值为 error, warning, log notice, info, log, debug1, debug2, debug3, debug4,debug5 82 第 5 章 SQL 优化手段 参数 enable_hint 默认关闭，当此参数关闭后，SQL 查询语句中的 HINT 注释将不会对执行计划产生影响。 当参数 hint_debug_print 设置为 on，并且 hint_message_level 设置为 log 后，在一个带有格式正确的 HINT 注释的 SQL 语句运行时，会一同输出 hint 的调试信息，内容包含 used hint、not used hint、duplication hint、error hint。 5.2.5 示例 环境准备： create table t1(id int, val int, name varchar(64)); create table t2(id int, val int); create table t3(id int, val int); create index t1_idx on t1(id); create index t2_idx on t2(id); create index t3_idx on t3(id); insert into t1 select i, i%5000, 'Kingbase'||(i%5) from generate_series(1,3000000) as x(i); insert into t2 select i, i%5000 from generate_series(1,1000000) as x(i); insert into t3 select i, i%5 from generate_series(1,100) as x(i); analyze t1; analyze t2; analyze t3; 5.2.5.1 常用 Scan 类型 HINT 示例 SeqScan HINT 例子: explain select * from t1 where id=20; QUERY PLAN ------------------------------------------------------------------> Index Scan using t1_idx on t1 (cost=0.43..8.45 rows=1 width=18) Index Cond: (id = 20) (2 rows) explain select * from t1 where id=20; QUERY PLAN ------------------------------------------------------------------> Seq Scan on t1 (cost=0.00..56731.00 rows=1 width=18) Filter: (id = 20) (2 rows) 83 第 5 章 SQL 优化手段 IndexScan HINT 例子： explain select * from t1 where id > 10 and id < 20000000; QUERY PLAN ------------------------------------------------------------------> Seq Scan on t1 (cost=0.00..64231.00 rows=2999990 width=18) Filter: ((id > 10) AND (id < 20000000)) (2 rows) explain select * from t1 where id > 10 and id < 20000000; QUERY PLAN ------------------------------------------------------------------> Index Scan using t1_idx on t1 (cost=0.43..112326.23 rows=2999990 width=18 Index Cond: ((id > 10) AND (id < 20000000)) (2 rows) IndexOnlyScan HINT 例子： explain select count(0) from t1; QUERY PLAN -----------------------------------------------------------------Aggregate (cost=56731.00..56731.01 rows=1 width=8) -> Seq Scan on t1 (cost=0.00..49231.00 rows=3000000 width=0 (2 rows) explain select count(0) from t1; QUERY PLAN -----------------------------------------------------------------Aggregate (cost=104826.43..104826.44 rows=1 width=8) -> Index Only Scan using t1_idx on t1 (cost=0.43..97326.43 rows=3000000 width=0) (2 rows) Bitmapscan HINT 例子： explain select * from t1 where id > 30; QUERY PLAN -------------------------------------------------------------> Seq Scan on t1 (cost=0.00..56731.00 rows=2999970 width=18) Filter: (id > 30) (2 rows) 84 第 5 章 SQL 优化手段 explain select * from t1 where id > 30;** QUERY PLAN -----------------------------------------------------------------Bitmap Heap Scan on t1 (cost=56342.20..113072.82 rows=2999970 width=18) Recheck Cond: (id > 30) Bitmap Index Scan on t1_idx (cost=0.00..55592.20 rows=2999970 width=0) Index Cond: (id > 30) (4 rows) 5.2.5.2 Parallel 类型 HINT 示例 使用并行例子 explain analyze select t2.id from t2,t3 where t2.id=t3.val group by t2.id; QUERY PLAN -----------------------------------------------------------------Group (cost=5.35..7.15 rows=100 width=4) (actual time=22.341..24.182 rows=4 loops=1) Group Key: t2.id -> Merge Join (cost=5.35..6.90 rows=100 width=4) (actual time=22.338..24.142 rows=80 loops=1) Merge Cond: (t2.id = t3.val) -> Gather Merge (cost=0.02..10424.84 rows=1000000 width=4)(actual time=22.100..23.800 rows=5 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Index Only Scan using t2_idx on t2 (cost=0.00..0.00 rows=416667 width=4) (actual time=0.051..0.328 rows=421 loops=3) Heap Fetches: 1262 -> Sort (cost=5.32..5.57 rows=100 width=4) (actual time=0.210..0.250 rows=100 loops=1) Sort Key: t3.val Sort Method: quicksort Memory: 30kB -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) (actual time=0.042..0.110 rows=100 loops=1) Planning Time: 0.857 ms Execution Time: 24.359 ms (15 rows) 5.2.5.3 ROWS 类型 HINT 示例 测试示例原始计划，例子中 t2 表的基数估计值为 1467，而实际执行行数是 3，差距较大。 85 第 5 章 SQL 优化手段 explain analyze select t2.id from t2,t3 where t2.id=t3.id and t2.id>5 and t2.val<8 and t3.id<9 group by t2.id; QUERY PLAN -----------------------------------------------------------------Group (cost=2.77..27.00 rows=1 width=4) (actual time=0.221..6.299 rows=2 loops=1) Group Key: t2.id -> Merge Join (cost=2.77..27.00 rows=1 width=4) (actual time=0.218..6.290 rows=2 loops=1) Merge Cond: (t2.id = t3.id) -> Index Scan using t2_idx on t2 (cost=0.42..35468.31 rows=1467 width=4) (actual time=0. 104..6.166 rows=3 loops=1) Index Cond: (id > 5) Filter: (val < 8) Rows Removed by Filter: 4992 -> Sort (cost=2.35..2.37 rows=7 width=4) (actual time=0.100..0.104 rows=8 loops=1) Sort Key: t3.id Sort Method: quicksort Memory: 25kB -> Seq Scan on t3 (cost=0.00..2.25 rows=7 width=4) (actual time=0.029..0.078 rows=8 loops=1) Filter: (id < 9) Rows Removed by Filter: 92 Planning Time: 1.033 ms Execution Time: 6.431 ms (16 rows) ROWS HINT 修正基数估计，修正后优化器选择了最优的连接方式 NestLoop，而不是最原始的 MergeJoin。 explain analyze selectt2.id from t2,t3 where t2.id=t3.id and t2.id>5 and t2.val<8 and t3.id<9 group by t2.id; QUERY PLAN -----------------------------------------------------------------Group (cost=61.46..61.47 rows=1 width=4) (actual time=0.206..0.215 rows=2 loops=1) Group Key: t2.id -> Sort (cost=61.46..61.47 rows=1 width=4) (actual time=0.201..0.204 rows=2 loops=1) Sort Key: t2.id Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=0.42..61.45 rows=1 width=4) (actual time=0.098..0.186 rows=2 loops=1) -> Seq Scan on t3 (cost=0.00..2.25 rows=7 width=4) (actual time=0.037..0.086 rows=8 loops=1) Filter: (id < 9) Rows Removed by Filter: 92 -> Index Scan using t2_idx on t2 (cost=0.42..8.45 rows=1 width=4) (actual time=0.009. .0.010 rows=0 loops=8) 86 第 5 章 SQL 优化手段 Index Cond: ((id = t3.id) AND (id > 5)) Filter: (val < 8) Rows Removed by Filter: 0 Planning Time: 1.079 ms Execution Time: 0.337 ms (15 rows) 5.2.5.4 常用 Join 类型 HINT 示例 本章节以 select t2.id from t2,t3 where t2.id=t3.val and t2.id>5 group by t2.id; 语句为例子进行演示三种常用 Join 类型的 HINT。 原始计划使用 MergeJoin 连接： explain select t2.id from t2,t3 where t2.id=t3.val and t2.id>5 group by t2.id; QUERY PLAN -------------------------------------------------------------------Group (cost=5.75..7.64 rows=100 width=4) Group Key: t2.id -> Merge Join (cost=5.75..7.39 rows=100 width=4) Merge Cond: (t2.id = t3.val -> Index Only Scan using t2_idx on t2 (cost=0.42..32968.32 rows=999994 width=4) Index Cond: (id > 5) -> Sort (cost=5.32..5.57 rows=100 width=4) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) (9 rows) NestLoop 连接 HINT： explain select t2.id from t2,t3 where t2.id=t3.val and t2.id>5 group by t2. id; QUERY PLAN -----------------------------------------------------------------Group (cost=842.82..843.32 rows=100 width=4) Group Key: t2.id -> Sort (cost=842.82..843.07 rows=100 width=4) Sort Key: t2.id -> Nested Loop (cost=0.42..839.50 rows=100 width=4) -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) -> Index Only Scan using t2_idx on t2 (cost=0.42..8.37 rows=1 width=4) Index Cond: ((id = t3.val) AND (id > 5)) (8 rows) 87 第 5 章 SQL 优化手段 通过 HashJoin 连接 HINT 进行更改： explain select t2.id from t2,t3 where t2.id=t3.val and t2.id>5 group by t2. id; QUERY PLAN -------------------------------------------------------------------Group (cost=29431.62..29432.12 rows=100 width=4) Group Key: t2.id -> Sort (cost=29431.62..29431.87 rows=100 width=4) Sort Key: t2.id Hash Join (cost=29424.93..29428.30 rows=100 width=4) Hash Cond: (t3.val = t2.id) -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) -> Hash (cost=16925.00..16925.00 rows=999994 width=4) -> Seq Scan on t2 (cost=0.00..16925.00 rows=999994 width=4) Filter: (id > 5) (10 rows) 下面例子，执行计划中两表使用 Hashjoin，使用 MergeJoin 连接 HINT 进行更改： explain select t2.id from t2,t1 where t2.id=t1.val group by t2.id; QUERY PLAN -------------------------------------------------------------------HashAggregate (cost=124906.00..134906.00 rows=1000000 width=4) Group Key: t2.id Hash Join (cost=26925.00..117406.00 rows=3000000 width=4) Hash Cond: (t1.val = t2.id) -> Seq Scan on t1 (cost=0.00..49231.00 rows=3000000 width=4) -> Hash (cost=14425.00..14425.00 rows=1000000 width=4) -> Seq Scan on t2 (cost=0.00..14425.00 rows=1000000 width=4) (7 rows) explain select t2.id from t2,t1 where t2.id=t1.val group by t2.id; QUERY PLAN -------------------------------------------------------------------Group (cost=372004.30..424667.70 rows=1000000 width=4) Group Key: t2.id -> Merge Join (cost=372004.30..417167.70 rows=3000000 width=4) Merge Cond: (t2.id = t1.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) 88 第 5 章 SQL 优化手段 -> Sort (cost=371978.97..379478.97 rows=3000000 width=4) Sort Key: t1.val -> Seq Scan on t1 (cost=0.00..49231.00 rows=3000000 width=4) (8 rows) 5.2.5.5 Leading 连接顺序 HINT 示例 Leading 类型的 HINT 种类 1：仅指定 join 顺序，不指定内外表顺序： 下面例子中各表的连接顺序为（t3 t1 t2），使用 leading HINT 更改连接顺序，但不指定内外表。 explain select t2.id from t1,t2,t3 where t1.id=t3.id and t1.id<3 and t3.val=t2.id group by t2.id; QUERY PLAN -----------------------------------------------------------------Group (cost=10.72..10.87 rows=1 width=4) Group Key: t2.id -> Merge Join (cost=10.72..10.87 rows=1 width=4) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=10.29..10.30 rows=1 width=4) Sort Key: t3.val -> Merge Join (cost=5.92..10.28 rows=1 width=4) Merge Cond: (t1.id = t3.id) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) Index Cond: (id < 3) -> Sort (cost=5.32..5.57 rows=100 width=8) Sort Key: t3.id -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) (14 rows) explain select t2.id from t1,t2,t3 where t1.id=t3.id and t1.id<3 and t3. val=t2.id group by t2.id; QUERY PLAN -------------------------------------------------------------------Group (cost=15.67..15.68 rows=1 width=4) Group Key: t2.id -> Sort (cost=15.67..15.68 rows=1 width=4) Sort Key: t2.id -> Merge Join (cost=11.30..15.66 rows=1 width=4) Merge Cond: (t3.id = t1.id) -> Sort (cost=10.70..10.95 rows=100 width=8) 89 第 5 章 SQL 优化手段 Sort Key: t3.id -> Merge Join (cost=5.75..7.38 rows=100 width=8) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=5.32..5.57 rows=100 width=8) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) Index Cond: (id < 3) (16 rows) Leading 类型的 HINT 种类 2：同时指定 join 顺序和内外表顺序： 对上面的例子指定 t1 为外表，t3 为内表进行连接，两表连接的结果作为外表与 t2 作为内表进行连接： explain select t2.id from t1,t2,t3 where t1.id=t3.id and t1.id<3 and t3. val=t2.id group by t2.id; QUERY PLAN -----------------------------------------------------------------Group (cost=10.72..10.87 rows=1 width=4) Group Key: t2.id -> Merge Join (cost=10.72..10.87 rows=1 width=4) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=10.29..10.30 rows=1 width=4) Sort Key: t3.val -> Merge Join (cost=5.92..10.28 rows=1 width=4) Merge Cond: (t1.id = t3.id) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) Index Cond: (id < 3) -> Sort (cost=5.32..5.57 rows=100 width=8) Sort Key: t3.id -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) (14 rows) 5.2.5.6 SET 类型 HINT 示例 下面的 SQL 语句执行计划使用 MergeJoin，通过 SET HINT，设置 enable_mergejoin off，使用 Nestloop。 explain select t2.id, count(t3.val) from t2,t3 where t2.id=t3.val group by t2.id; QUERY PLAN 90 第 5 章 SQL 优化手段 -------------------------------------------------------------------GroupAggregate (cost=5.75..8.88 rows=100 width=12) Group Key: t2.id -> Merge Join (cost=5.75..7.38 rows=100 width=8) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=5.32..5.57 rows=100 width=4) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) (8 rows) explain select t2.id, count(t3.val) from t2,t3 where t2.id=t3.val group by t2.id; QUERY PLAN -------------------------------------------------------------------GroupAggregate (cost=842.57..844.32 rows=100 width=12) Group Key: t2.id -> Sort (cost=842.57..842.82 rows=100 width=8) Sort Key: t2.id -> Nested Loop (cost=0.42..839.25 rows=100 width=8) -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) -> Index Only Scan using t2_idx on t2 (cost=0.42..8.36 rows=1 width=4) Index Cond: (id = t3.val) (8 rows) 5.2.5.7 Blockname 类型 HINT 示例 在下面的例子中，父查询和子查询都是用表 t2，通过名字无法区分两个表，有了 blockname HINT 之后，可以 使用 blockname 进行区分，更改如下： explain select t2.id from t2 where t2.id in (select id from t2 where t2.val < 9); QUERY PLAN -----------------------------------------------------------------Nested Loop (cost=16929.55..28344.71 rows=1651 width=4) -> HashAggregate (cost=16929.13..16945.64 rows=1651 width=4) Group Key: t2_1.id -> Seq Scan on t2 t2_1 (cost=0.00..16925.00 rows=1651 width=4) Filter: (val < 9) -> Index Only Scan using t2_idx on t2 (cost=0.42..6.89 rows=1 width=4) Index Cond: (id = t2_1.id) (7 rows) 91 第 5 章 SQL 优化手段 explain select t2.id from t2 where t2.id in (select id from t2 where t2.val < 9); QUERY PLAN -----------------------------------------------------------------Hash Semi Join (cost=16945.64..34014.00 rows=1651 width=4) Hash Cond: (t2.id = t2_1.id) -> Seq Scan on t2 (cost=0.00..14425.00 rows=1000000 width=4) -> Hash (cost=16925.00..16925.00 rows=1651 width=4) -> Seq Scan on t2 t2_1 (cost=0.00..16925.00 rows=1651 width=4) Filter: (val < 9) (6 rows) 5.2.5.8 Materialize 类型 HINT 示例 下面的例子中将（t2 t3）连接的中间结果进行物化处理： explain select t2.id from t1,t2,t3 where t1.id=t3.id and t1.id<3 and t3.val=t2.id group by t2. id; QUERY PLAN -----------------------------------------------------------------Group (cost=10.72..10.87 rows=1 width=4) Group Key: t2.id -> Merge Join (cost=10.72..10.87 rows=1 width=4) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=10.29..10.30 rows=1 width=4) Sort Key: t3.val -> Merge Join (cost=5.92..10.28 rows=1 width=4) Merge Cond: (t1.id = t3.id) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) Index Cond: (id < 3) -> Sort (cost=5.32..5.57 rows=100 width=8) Sort Key: t3.id -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) (14 rows) explain selectt2.id from t1,t2,t3 where t1.id=t3.id and t1.id<3 and t3. val=t2.id group by t2.id; 92 第 5 章 SQL 优化手段 QUERY PLAN -----------------------------------------------------------------Group (cost=19.10..19.11 rows=1 width=4) Group Key: t2.id -> Sort (cost=19.10..19.11 rows=1 width=4) Sort Key: t2.id -> Nested Loop (cost=6.18..19.09 rows=1 width=4) Join Filter: (t3.id = t1.id) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) Index Cond: (id < 3) -> Materialize (cost=5.75..7.88 rows=100 width=8) -> Merge Join (cost=5.75..7.38 rows=100 width=8) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=5.32..5.57 rows=100 width=8) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) (15 rows) 5.2.5.9 Use_nl_with_index 类型 HINT 示例 下面的 SQL 语句中，对表 t2 使用参数化路径： explain select t2.id, count(t3.val) from t2,t3 where t2.id=t3.val group by t2.id; QUERY PLAN -----------------------------------------------------------------GroupAggregate (cost=5.75..8.88 rows=100 width=12) Group Key: t2.id -> Merge Join (cost=5.75..7.38 rows=100 width=8) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=5.32..5.57 rows=100 width=4) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) (8 rows) explain selectt2.id, count(t3.val) from t2,t3 where t2.id=t3.val group by t2.id; QUERY PLAN ------------------------------------------------------------------ 93 第 5 章 SQL 优化手段 GroupAggregate (cost=842.57..844.32 rows=100 width=12) Group Key: t2.id -> Sort (cost=842.57..842.82 rows=100 width=8) Sort Key: t2.id -> Nested Loop (cost=0.42..839.25 rows=100 width=8) -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) -> Index Only Scan using t2_idx on t2 (cost=0.42..8.36 rows=1 width=4) Index Cond: (id = t3.val) (8 rows) 5.2.5.10 Aggregate 类型 HINT 示例 使用 hashagg HINT： explain select t2.id, count(t3.val) from t2,t3 where t2.id=t3.val group by t2.id; QUERY PLAN -----------------------------------------------------------------GroupAggregate (cost=5.75..8.88 rows=100 width=12) Group Key: t2.id -> Merge Join (cost=5.75..7.38 rows=100 width=8) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=5.32..5.57 rows=100 width=4) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) (8 rows) explain selectt2.id, count(t3.val) from t2,t3 where t2.id=t3.val group by t2.id; QUERY PLAN -----------------------------------------------------------------HashAggregate (cost=7.88..8.88 rows=100 width=12) Group Key: t2.id -> Merge Join (cost=5.75..7.38 rows=100 width=8) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=5.32..5.57 rows=100 width=4) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=4) (8 rows) 94 第 5 章 SQL 优化手段 5.2.5.11 Ordered 类型 HINT 示例 下面的例子中表的连接顺序是 t1，t3，t2，加入 ordered HINT 后，执行计划按照表出现的顺序进行连接： explain select t2.id from t2,t3,t1 where t1.id=t3.id and t1.id<3 and t3.val=t2.id group by t2.id; QUERY PLAN -----------------------------------------------------------------Group (cost=10.72..10.87 rows=1 width=4) Group Key: t2.id -> Merge Join (cost=10.72..10.87 rows=1 width=4) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=10.29..10.30 rows=1 width=4) Sort Key: t3.val -> Merge Join (cost=5.92..10.28 rows=1 width=4) Merge Cond: (t3.id = t1.id) -> Sort (cost=5.32..5.57 rows=100 width=8) Sort Key: t3.id -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) Index Cond: (id < 3) (14 rows) explain selectt2.id from t2,t3,t1 where t1.id=t3.id and t1.id<3 and t3.val=t2.id group by t2.id; QUERY PLAN -----------------------------------------------------------------Group (cost=15.67..15.68 rows=1 width=4) Group Key: t2.id -> Sort (cost=15.67..15.68 rows=1 width=4) Sort Key: t2.id -> Merge Join (cost=11.30..15.66 rows=1 width=4) Merge Cond: (t3.id = t1.id) -> Sort (cost=10.70..10.95 rows=100 width=8) Sort Key: t3.id -> Merge Join (cost=5.75..7.38 rows=100 width=8) Merge Cond: (t2.id = t3.val) -> Index Only Scan using t2_idx on t2 (cost=0.42..30468.42 rows=1000000 width=4) -> Sort (cost=5.32..5.57 rows=100 width=8) Sort Key: t3.val -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) 95 第 5 章 SQL 优化手段 Index Cond: (id < 3) (16 rows) 5.2.5.12 HINT 混合使用示例 通常 SQL 调优中会使用多种 HINT，如下例子中使用了连接顺序 leading HINT、连接方式 NestLoop HINT 以 及聚集 HINT hashagg。 explain select t2.id from t1,t2,t3 where t1. id=t3.id and t1.id<3 and t3.val=t2.id group by t2.id; QUERY PLAN -----------------------------------------------------------------HashAggregate (cost=1514438.72..1514438.73 rows=1 width=4) Group Key: t2.id -> Nested Loop (cost=0.43..1514438.72 rows=1 width=4) Join Filter: (t3.id = t1.id) -> Nested Loop (cost=0.00..1514427.25 rows=100 width=8) Join Filter: (t2.id = t3.val) -> Seq Scan on t2 (cost=0.00..14425.00 rows=1000000 width=4) Materialize (cost=0.00..2.50 rows=100 width=8) -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8) Materialize (cost=0.43..8.47 rows=2 width=4) -> Index Only Scan using t1_idx on t1 (cost=0.43..8.46 rows=2 width=4) Index Cond: (id < 3) (12 rows) 5.2.6 注意 1. 关于并行查询的 GUC 参数 max_parallel_workers 和 max_parallel_workers_per_gather，当在 Hint 中指定 的 max_parallel_workers_per_gather 的值比 kingbase.conf 中 max_parallel_workers 设置的值大时，会在系 统日志中记录这个事件，并行 Hint 不会生效。 2. Set 类型的 Hint 只支持用户级可变的优化器相关的参数，对其他参数（例如 enable_upper_colname）不支持。 3. 当带有 Hint 的 SQL 语句中包含多个“values”表达式时，比如： explain (costs on) select /*+rows(*VALUES * #1000)*/ * from(values('A'),('B'),('C')) as s(n) , (values('C'), ('D'),('E')) as t(n) where s.n=t.n; 由于对于 HINT 来说，通过一个 VALUES 名字无法区分对应的是哪一个具体的 values 表达式，因此 Hint 不支 持包含多个 values 表达式的 SQL 语句。 96 第 5 章 SQL 优化手段 5.3 调整性能参数 SQL 性能相关的参数较多，具体见下文。在使用时需注意作用范围，可以考虑通过 HINT 来指定，尽量缩小影 响范围。 • 成本参数 • 节点开关参数 • 多表连接参数 • 内存参数 • 其他相关参数 5.3.1 成本参数 优化器内部使用基于成本的算法来获取总成本最低的访问路径。在计算成本的公式中，会用到一些定义好的参数 因子，这些参数因子会影响到最终计算出出来的总成本，主要包括： • seq_page_cost 数值，默认值为 1.0，表示全表扫描时，读取单个数据块的扫描成本。 • random_page_cost 数值，默认值为 4.0，表示使用索引扫描时，单个数据块的扫描成本。 • cpu_tuple_cost 数值，默认值为 0.01，表示读取一个元组时，CPU 消耗成本。 • cpu_index_tuple_cost 数值，默认值为 0.005，表示从索引上读取一个元组时，CPU 消耗成本。 • cpu_operator_cost 数值，默认值为 0.0025，表示执行一个操作符的 CPU 消耗成本。 这些值是相对的成本值，一般以 seq_page_cost 为基准。比如 random_page_cost 为 4.0，表示执行计划中计算 随机访问的一个数据块页面的开销为顺序访问一个数据块开销的 4 倍。 其中前 2 个参数跟 I/O 有关，后 3 个跟 CPU 计算有关。如果服务器更换了磁盘 I/O 能力更强的硬件，可以尝 试调低 seq_page_cost 和 random_page_cost 的值，比如调为 0.5 和 2.0。同理 CPU 消耗成本也可以调高（CPU 性 能较低时）或者调低（CPU 性能显著提升后）。 5.3.2 节点开关参数 KingbaseES 允许对单个查询关闭特定的优化器特性。如果有事优化器为特定查询选择的执行计划并不是最优 的，可以通过设置这些参数强制优化器选择一个更好的执行计划来临时解决这个问题。引起优化器选择错误路径的原 97 第 5 章 SQL 优化手段 因有可能是统计信息更新不及时造成的，可以通过运行 Analyze 的方式来彻底解决。 扫描类开关： • enable_seqscan 表示是否走全表扫描，当全表数据量非常少或选择率高时使用比较好。 • enable_indexscan 表示是否允许走索引扫描，当选择率低时使用比较好。 • enable_bitmapscan 表示是否允许走 bitmap 扫描，当多个过滤条件，并且需要扫描数据占整表比索引较大时使用比较好。 • enable_tidscan 表示是否允许走 tid 扫描，当指定 ctid 过滤条件时使用比较好。 连接类开关： • enable_hashjoin 表示是否允许走 hash 连接，当数据大都是随机时使用比较好。 • enable_nestloop 表示是否允许走 nestloop 连接，当内外表数据量非常小时使用比较好。 • enable_mergejoin 表示是否允许走合并连接，当内外表数据基本有序时使用比较好。 其它开关： • enable_hashagg 表示是否允许使用 hash 的方式来计算 Aggregate 值。 • enable_groupagg 表示是否允许使用 group 的方式来计算 Aggregate 值。 • enable_sort 表示是否允许使用显式的 sort 节点。 • enable_material 表示是否允许使用物化节点。 以 5.3.5 示例环境进行举例，对一个元素比较多的表求聚集值，默认情况下它会使用 hash 的方式做聚集： explain select count(*) from t2 where id > 100 group by val; QUERY PLAN ----------------------------------------------------------------HashAggregate (cost=21924.43..21974.45 rows=5002 width=12) 98 第 5 章 SQL 优化手段 Group Key: val -> Seq Scan on t2 (cost=0.00..16925.00 rows=999886 width=4) Filter: (id > 100) (4 rows) HashAggregate 在少数聚合函数是表现优异，但是很多聚合函数，性能跟消耗的内存差异很明显。尤其是受 group by 字段的唯一性很明显，字段唯一值越大，hash 聚合消耗的内存越多，性能下降剧烈。 而对于 GroupAggregate 来说，消耗的内存基本上是恒定的，无论 group by 哪个字段。当聚合函数较少的时候， 速度也相对较慢，但是相对稳定。 如果我们禁用 hash 方式聚集方式，则可以设置 enable_hashagg 开关： set enable_hashagg=off; SET explain select count(*) from t2 where id > 100 group by val; QUERY PLAN -----------------------------------------------------------------GroupAggregate (cost=116570.66..124119.82 rows=5002 width=12) Group Key: val -> Sort (cost=116570.66..119070.37 rows=999886 width=4) Sort Key: val -> Seq Scan on t2 (cost=0.00..16925.00 rows=999886 width=4) Filter: (id > 100) (6 rows) 当通过开关禁止某种扫描、连接、分组、排序或者物化视图方式的时候，优化器内部将该特性的使用成本加到非 常大的一个数值，这样它被执行的可能性就降低了很多，但是不能完全禁止该特性的使用，取决于最终的成本比较。 比如在某些地方没有索引，关闭 enable_seqscan 开关，依然会选用全表扫描。为了减少开关的禁止打开对所有 SQL 语句的影响，上面的操作尽可能使用 HINT 来控制。 5.3.3 多表连接参数 当一个查询涉及到的表的总数很多的时候（比如超过 12 个），则会有无数的路径组合。KingbaseES 在这种情 况下，为了避免产生大量的可选路径消耗大量 CPU，采用启发式算法来搜索一定范围内的可能的访问路径，这就是 基因查询优化（GEQO）。GEQO 选出来的路径结果有可能是随机的，因此在此情况下生成的执行计划会有不确定 性。 跟基因查询相关的比较重要的参数有 3 个： • geqo Bool 值，是否允许使用基因查询，默认为 true。关闭基因查询虽然能够得到最佳的访问路径，但是其生 成执行计划的时间有可能会变得很大。 • geqo_threshold 99 第 5 章 SQL 优化手段 整数，当关系表的个数超过该值的时候，启用基因查询来做路径选择，默认为 12。当关系表的个数少于 该值的时候，优化器会穷举所有可能的访问路径并从中选出最佳的结果。 • geqo_effort 整数，控制基因查询里规划时间和查询规划有效性之间的平衡，即在效率和结果之间做选择，默认为 5， 可以选择 1~10 之间的数。选的数值越大，则优化器花费更多的时间，当然选到更优的访问路径概率也更 大。 还有 4 个参数：geqo_pool_size, geqo_generations, geqo_selection_bias, geqo_seed 可以进一步控制基因查询的 行为，可以查看 KingbaseES 服务器配置参数参考手册。 5.3.4 内存参数 • work_mem 原理：内部排序和哈希操作可使用的工作内存大小。该内存是在开始使用临时磁盘文件之前使用的内存数 目。 应用范围：数据比较多大的情况，主要和排序的数据有关系，排序数据越大，设置的就越大，比如 16g 内存，tpch 测试，单用户 10g 规模数据，设置 2g 的 work_mem。数值以 kB 为单位的，缺省是 1024(1MB)。索引扫描不用 work_mem。 注意: 对于复杂的查询，可能会同时并发运行好几个排序或者哈希操作，每个都会使用这个参数声明的这 么多内存，然后才会开始求助于临时文件。同样，好几个正在运行的会话可能会同时进行排序操作。因此 使用的总内存可能是 work_mem 的好几倍。ORDER BY、DISTINCT 和 mergejoin 都要用到排序操作， 而哈希操作在哈希连接、哈希聚集和以哈希为基础的 IN 子查询处理中都会用到。 • maintenance_work_mem 原理：在维护性操作 (比如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等) 中 使用的最大的内存数。 应用范围：在维护性操作 (比如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等）调整大小，默认是 16MB，比如创建索引的索引数据很大，比如 10g，如果内存允许就可以调整这个 参数 1g，一般在灌数的时候调大，灌数完毕测试时再调小。 注意: 因为在一个数据库会话里，任意时刻只有一个这样的操作可以执行，并且一个数据库安装通常不会 有太多这样的工作并发执行，把这个数值设置得比 work_mem 更大是安全的。更大的设置可以改进清理 和恢复数据的速度，一般测试时不能调的过大。 • temp_buffers 原理：存储临时信息时使用的最大块数。 100 第 5 章 SQL 优化手段 应用范围：最小值为 100，最大值为 INT_MAX/2，默认值为 1024，执行器使用此区域存储临时表。 • shared_buffers 原理：数据库服务器使用的共享内存缓冲区的数量，主要用于缓存数据，根据需求一般不能设置超过 80% 的内存，但至少是 20%。 应用范围：数据库本身，查询的数据量比较大，比较频繁使用到。 注意: KingbaseES 使用内存总大小为： shared_buffers(数据) + wal_buffers（日志）+ maintenance_work_mem（创建索引排序时使用） + n*work_mem(n 为并发做排序的连接数) + 服务进程上下文使用的内存 (无法精确估计大小) + m*thread_stack_size(thread_stack_size 为进程栈的大小，KingbaseES 默认为 1MB;m 为当前连接数)； 由于 32bit 平台的用户进程内存寻址空间较小 (window 为 2G、linux 为 3G)，所以这些内存的设置在 32bit 平台上的设置不能过大，防止内存溢出。 5.3.5 其他相关参数 • cursor_tuple_fraction 0.1~1.0 之间的一个数值，默认值是 0.1。更小的值使得优化器偏向为游标快速返回第一条记录，但是可能 需要很长时间来获取所有行。更大的值强调总体代价。比如设置为 1.0，游标将和普通查询完全一样地被 规划，只考虑总体代价最低，而不考虑前几行会被多快地返回。 • from_collapse_limit 整数，默认值为 8。如果生成的 FROM 列表不超过这么多项，规划器将把子查询融合到上层查询。较小 的值可以减少规划时间，但是可能会生成较差的查询计划。将这个值设置为 geqo_threshold 或更大，可 能触发使用 GEQO 规划器，从而产生非最优计划。 • join_collapse_limit 如果查询重写生成的 FROM 后的项目数不超这个数，那么优化器器将把显式 JOIN（除了 FULL JOIN）结构重写到 FROM 项列表中。较小的值可减少规划时间，但是可能会生成差些的查询计划。 默认情况下，这个变量被设置成和 from_collapse_limit 相同，这样适合大多数使用。将这个值设置为 geqo_threshold 或更大，可能触发使用 GEQO 规划器，从而产生非最优计划。 5.4 使用并行 KingbaseES 能使用多核 CPU 来加速一个 SQL 语句的执行时间，这种特性被称为并行查询。由于现实条件的限 制或因为没有比并行查询计划更快的查询计划存在，很多查询并不能从并行查询获益。但是，对于那些可以从并行查 询获益的查询来说，并行查询带来的速度提升是显著的。很多查询在使用并行查询时查询速度比之前快了超过两倍， 有些查询是以前的四倍甚至更多的倍数。 101 第 5 章 SQL 优化手段 本文档将介绍并行查询工作的细节，以及用户应当在什么情况下使用并行查询。 • 并行查询原理 • 并行查询相关参数 • 并行查询支持的范围 • Worker 进程数与查询性能关系 5.4.1 并行查询原理 以一个普通并行查询为例： explain SELECT * FROM student WHERE age =6; QUERY PLAN -------------------------------------------------------------------Gather (cost=1000.00..80481.52 rows=102334 width=8) Workers Planned: 5 -> Parallel Seq Scan on student (cost=0.00..69248.12 rows=20467 width=8) Filter: (age = 6) (4 行记录) 102 第 5 章 SQL 优化手段 通过 EXPLAIN 可以查看并行查询的执行计划。当用户输入一个查询语句时，查询分析，查询重写以及查询规划 都和原来一样，只有到执行计划时，才开始真正进入并行查询环节。上面执行计划中，Gather 节点只是执行计划的 一个子节点，属于执行计划的一部分，当查询执行走到 Gather 节点时，会话进程会申请一定数量的 worker 进程（根 据配置参数，以及成本确定）来进行并行查询过程。在这些 worker 进程中，有一个充当 leader worker 的角色，负责 收集汇总各个 worker 进程查询的结果。该 leader worker 进程也会根据查询的数据量大小承担部分的并行查询部分。 执行过程如下图所示： Leader worker 进程和其他 worker 进程通过动态共享内存进行通信。其他 worker 进程（包括 leader worker 进 程）把分担查询的结果存储到共享内存中，然后由 leader worker 进程进行汇总整个查询的结果。所以需要注意的 是，由于并行查询需要使用了动态共享内存，所以 dynamic_shared_memory_type 参数需要设置为 none 以外的值。 5.4.2 并行查询相关参数 要想使用并行查询，需要在 kinbase.conf 里设置以下参数。 并行度相关参数： • max_worker_processes： 设置系统支持的最大后台进程数，默认值是 8，此参数调整后需要重启数据库生效。 • max_parallel_workers： 最大并行 worker 数。该数值不能大于 max_worker_processes。 • max_parallel_workers_per_gather： 最大并行执行 worker 数。不能超过 max_parrellel_workers。 103 第 5 章 SQL 优化手段 • max_parallel_maintenance_workers 最大并行维护操作 worker 数。不能超过 max_parrellel_workers。 这 4 个参数之间的关系为： max_parallel_workers_per_gather+max_parallel_maintenance_workers <= max_parallel_workers <= max_worker_processes 并行触发条件： • min_parallel_table_scan_size： 表的存储空间至少大于等于该值，才有可能触发并行，默认值 8MB。 • min_parallel_index_scan_size 索引存储空间至少大于等于该数值，才有可能触发并行。默认 256 KB。 可以通过该语句来获得表、索引的磁盘存储大小： select sys_size_pretty(sys_relation_size(’student’)); 优化器控制开关： • enable_parallel_append 优化器控制开关，是否允许并行 append plans。 • enable_parallel_hash 优化器控制开关，是否允许并行 hash plans。 其他参数： • parallel_leader_participation 控制 Gather、Gather merge 节点是否能执行 subplans。 • parallel_setup_cost： 表示启动 woker process 的启动成本，因为启动 worker 进程需要建立共享内存等操作，属于附带的额外成本。 其值越小，数据库越有可能使用并行查询。默认为 1000。 • parallel_tuple_cost： woker 进程处理完后的 tuple 要传输给上层 node，即进程间查询结果的交换成本，即后台进程间传输一个元组 的代价。其值越小，数据库越有可能使用并行。默认是 0.1。 优化器主要根据 max_worker_processes 决定能够开启 worker 进程数后，根据目标表或者索引的大小 （min_parallel_table_scan_size、min_parallel_index_scan_size）， 以 及 根 据 parallel_setup_cost 和 paral- lel_tuple_cost 算出来的并行总 cost，跟其他不使用并行方案比较后决定是否启用并行，以及本次查询的并行进程数 （小于等于 max_parallel_workers）。 104 第 5 章 SQL 优化手段 5.4.3 并行查询支持的范围 KingbaseES 支持并行的操作有： • DML • 全表扫描（Seq Scan） • BTree 索扫描（B-tree Index Scan） • Bitmap 扫描（Bitmap Heap Scan） • 哈希连接（Hash Join） • 嵌套循环连接（Nested Loop Join） • 归并连接（Merge Join） • 聚集（Aggregation） • 排序（Sort） • Append • DDL • 创建索引 • Create table as select… 5.4.3.1 并行全表扫描 创建一个包含 1000 万行记录的表 student，设置并行 worker 为 2，由于表大，返回的记录百分比少，优化器使 用了并行全部扫描： create table student (id int primary key, age int); insert into student select id, (random()*100)::integer as age from generate_series(1,10000000) as id; analyze student; explain select * from student where age = 6; QUERY PLAN ----------------------------------------------------------------Gather (cost=1000.00..102331.58 rows=50000 width=8) Workers Planned: 2 -> Parallel Seq Scan on student (cost=0.00..96331.58 rows=20833 width=8) Filter: (age = 6) (4 行记录) 如果我们修改过滤条件，让返回的结果集变大（返回 50% 左右数据，约 500 万条），则优化器根据计算出来的 cost 对比，不再选择并行查询，而使用全部扫描： 105 第 5 章 SQL 优化手段 EXPLAIN SELECT * FROM student WHERE age < 50; QUERY PLAN ----------------------------------------------------------------Seq Scan on student (cost=0.00..169248.60 rows=5012113 width=8) Filter: (age < 50) (2 行记录) 5.4.3.2 并行哈希连接 再创建一个小一点表 score，插入 10 万条记录，然后跟 student 做 join： create table score(sid int, score int); insert into score select sid, (random()*100)::integer as score from generate_series(1, 100000) as sid; create index idx_sc on score(sid); analyze student; explain select * from score join student on score.sid = student.id; QUERY PLAN -----------------------------------------------------------Gather (cost=48269.36..145982.13 rows=97616 width=16) Workers Planned: 5 -> Hash Join (cost=47269.36..135220.53 rows=19523 width=16) Hash Cond: (student.id = score.sid) -> Parallel Seq Scan on student (cost=0.00..64247.95 rows=1999995 width=8) -> Hash (cost=45667.16..45667.16 rows=97616 width=8) -> Seq Scan on score (cost=0.00..45667.16 rows=97616 width=8) (7 行记录) 可以看到，并行查询也支持 hash join。每个 worker 里面都是先扫描小表 score 计算 hash，然后并行扫描大表 student，再做 hash join。最后将数据汇总到 Gather 节点合并最终的结果集。 5.4.3.3 并行嵌套循环连接 explain select * from score join student on score.sid = student.id; QUERY PLAN ------------------------------------------------------------------Gather (cost=1000.43..122011.83 rows=97616 width=16) Workers Planned: 5 -> Nested Loop (cost=0.43..111250.23 rows=19523 width=16) -> Parallel Seq Scan on score (cost=0.00..44886.23 rows=19523 width=8) 106 第 5 章 SQL 优化手段 -> Index Scan using student_pkey on student (cost=0.43..3.39 rows=1 width=8) Index Cond: (id = score.sid) (6 行记录) 支持嵌套循环连接的并行查询。如果不开启并行，则使用普通的 hash join，性能上会比开启并行后低。 5.4.3.4 并行聚集 聚集函数也支持并行： explain select avg(age) from student; QUERY PLAN -------------------------------------------------------------Finalize Aggregate (cost=97331.80..97331.81 rows=1 width=32) -> Gather (cost=97331.58..97331.79 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=96331.58..96331.59 rows=1 width=32) -> Parallel Seq Scan on student (cost=0.00..85914.87 rows=4166687 width=4) (5 行记录) 5.4.3.5 并行排序 下面例子中支持了并行排序和 Gather merge。 create table t1(id int, val int, name varchar(64)); create index t1_idx on t1(id); insert into t1 select i, i%5000, 'Kingbase'||(i%5) from generate_series(1,3000000) as x(i); analyze t1; explain select * from t1 where val< 10 order by name; QUERY PLAN -------------------------------------------------------------------Gather Merge (cost=36003.18..36608.72 rows=5190 width=18) Workers Planned: 2 -> Sort (cost=35003.16..35009.64 rows=2595 width=18) Sort Key: name -> Parallel Seq Scan on t1 (cost=0.00..34856.00 rows=2595 width=18) Filter: (val < 10) (6 行记录) 107 第 5 章 SQL 优化手段 5.4.3.6 并行 Append 对 2 个大表做 union 的时候，也支持并行 append： create table student2 as select * from student; analyze student2; explain select * from student union all select * from student2 where age = 6; QUERY PLAN -------------------------------------------------------------------Append (cost=0.00..303155.94 rows=10100644 width=8) -> Seq Scan on student (cost=0.00..144247.77 rows=9999977 width=8) -> Gather (cost=1000.00..107398.28 rows=100667 width=8) Workers Planned: 2 -> Parallel Seq Scan on student2 (cost=0.00..96331.58 rows=41945 width=8) Filter: (age = 6) (6 行记录) 5.4.4 Worker 进程数与查询性能关系 并不是所有的查询都能适用于并行查询。因为考虑到系统的开销以及进程占用资源的情况，也并不是开启的 worker 数量越多，查询效率越高。下面以对同一个表做同样的查询，然后开启不同的 worker 数量来测试一下。 开启 1 个 worker 进程： create table test(n bigint); insert into test values(generate_series(1, 4000000)); analyze test; explain analyze select * from test where n = 4000000; QUERY PLAN -------------------------------------------------------------------Gather (cost=1000.00..59888.81 rows=1 width=4) (actual time=244.328..301.458 rows=1 loops=1) Workers Planned: 1 Workers Launched: 1 -> Parallel Seq Scan on test (cost=0.00..58888.71 rows=1 width=4) (actual time=268.845..297. 372 rows=0 loops=2) Filter: (n = 4000000) Rows Removed by Filter: 2500000 Planning time: 0.119 ms Execution time: 302.026 ms (8 rows) 开启 2 个 worker 进程： 108 第 5 章 SQL 优化手段 explain analyze select * from test where n = 4000000; QUERY PLAN -------------------------------------------------------------------Gather (cost=1000.00..49165.77 rows=1 width=4) (actual time=218.212..218.287 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on test (cost=0.00..48165.67 rows=1 width=4) (actual time=200.619..213. 684 rows=0 loops=3) Filter: (n = 4000000) Rows Removed by Filter: 1666666 Planning time: 0.117 ms Execution time: 219.005 ms 开启 4 个 worker 进程，但是实际上基于成本考虑只启用了 3 个： explain analyze select * from test where n = 4000000; QUERY PLAN -------------------------------------------------------------------Gather (cost=1000.00..43285.39 rows=1 width=4) (actual time=190.967..191.017 rows=1 loops=1) Workers Planned: 3 Workers Launched: 3 -> Parallel Seq Scan on test (cost=0.00..42285.29 rows=1 width=4) (actual time=174.275..182. 767 rows=0 loops=4) Filter: (n = 4000000) Rows Removed by Filter: 1250000 Planning time: 0.119 ms Execution time: 191.817 ms (8 rows) 通过 create table test(n int) with (parallel_workers = 4); 强制启动 4 个 worker 进程测试： explain analyze select * from test where n = 4000000; QUERY PLAN -------------------------------------------------------------------Gather (cost=1000.00..38749.10 rows=1 width=4) (actual time=181.145..181.315 rows=1 loops=1) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on test (cost=0.00..37749.00 rows=1 width=4) (actual time=163.565..169. 837 rows=0 loops=5) Filter: (n = 4000000) Rows Removed by Filter: 1000000 Planning time: 0.050 ms 109 第 5 章 SQL 优化手段 Execution time: 185.391 ms 对比查询结果，可以看出，查询效率并没有随 worker 数量线性增加；在启用 3 个 worker 进程和 4 个 worker 进 程进行并行查询时，查询效率基本一致了。所以并不是开启的 worker 数量越多，查询效率越高，这个也是系统有成 本考虑在内。 另外，如果由于 CPU、共享内存、worker 进程等资源是整个数据库示例共享，一个查询如果消耗了大量的资 源（比如并行开启了 4 个 worker），则其他会话能够申请到的资源会变得有限，查询性能会受到影响，这一问题在 OLTP 事务型应用里尤其需要注意。所以并行参数的设置一定需要在一个合理的范围以内。 5.5 使用 Query Mapping 本章节包含以下内容： • 概述 • Query Mapping 的功能 • Query Mapping 的使用 • 示例 5.5.1 概述 Query Mapping 功能允许用户预先创建 SQL 语句的映射关系并储存在相应的系统表，当用户输入的 SQL 语句 与所创建的映射关系相匹配时，替换成映射的 SQL 语句去实际执行。 5.5.2 Query Mapping 的功能 Query Mapping 主要作用是做 SQL 的匹配/替换工作，在数据库中配置源 SQL 语句到目标 SQL 语句的映射关 系（存在系统表），根据客户的输入 sql 语句去做匹配，如果匹配，就替换成目标语句进行执行，它有两种级别： • TEXT 级别：不做词法/语法/语义检查，保存用户的原始 sql 和期望 mapping 的目标 sql，进行字符串匹配 110 第 5 章 SQL 优化手段 • SEMANTICS 级别：做词法/语法/语义语义检查，并保存用户原始/目标 sql 对应的 Query tree 它的可用场景为： SQL 调优：将低效 SQL 语句转换成另一高效的等价 SQL 语句 异构数据库迁移：将源数据库的 SQL 语法翻译成 KingbaseES 库的 SQL 语法 注意： 1. 如果希望把 Query Mapping 功能与 Hint 功能配合使用，那么需要使用 TEXT 级别的规则，因为 Hint 使用 的是注释来保存 Hint 具体内容，TEXT 级别会保存 SQL 的注释；而对于 SEMANTICS 级别，由于保存的是 Query tree 所以无法保存注释，也就无法与 Hint 功能配合使用。 2. 对于 SEMANTICS 级别的 Query mapping，目前不支持带有 rownum 的语句映射 5.5.3 Query Mapping 的使用 Query Mapping 的使用方法如下： 1. 在 kingbase.conf 配置文件中，配置：enable_query_rule = on 2. 启动数据库 3. 使用系统函数 create_query_rule() 来新增匹配规则，如： SELECT create_query_rule( 'qm1', -- 规则名 'select $1::TEXT AS col', -- 匹配的 sql 'select 2222', -- 需要替换的 sql true, -- 该规则是否生效 'semantics' -- 级别，text 或者 semantics ); 4. 接下来如果用户输入：select ’aaa’ as col; 那么就会发生替换，其结果为： ?column? ---------2222 (1 row) 111 第 5 章 SQL 优化手段 5. 如果需要删除该规则，使用 SELECT drop_query_rule(’qm1’);-- 入参 qm1 为规则名 6. 如果需要使原本不生效的规则生效，可以使用 SELECT enable_query_rule(’qm1’);-- 入参 qm1 为规则名 7. 如果需要使原本生效的规则失效，可以使用 SELECT disable_query_rule(’qm1’);-- 入参 qm1 为规则名 Query Mapping 的所有对外函数表如下： 表 5.5.1: Query Mapping 特性相关函数函数 名称 返回类型 描述 create_query_mapping_rule (text text text bool text) void 创建一条 Query mapping 规则 drop_query_rule(text) void 根据传入的规则名，删除一条 Query mapping 规则 drop_query_rule() void 删除所有 Query mapping 规则 enable_query_rule(text) void 根据传入的规则名，启用一条 Query mapping 规则 enable_query_rule() void 启用所有 Query mapping 规则 disable_query_rule(text) void 根据传入的规则名，禁用一条 Query mapping 规则 disable_query_rule() void 禁用所有 Query mapping 规则 5.5.4 示例 准备数据： create table t1(id int primary key, val int, name varchar(64)); create table t2(id int, val int, name varchar(64)); insert into t1 select i, i%10, 'Kingbase'||(i%5) from generate_series(1,1000) as x(i); insert into t2 select i, i%20, 'Kingbase'||(i%10) from generate_series(1,1000) as x(i); 1) 单个 $ 变量对单个 $ 变量 Query Mapping 规则，使用规则之前数据有 9 行 2 列，使用规则之后，数据变成 4 行 1 列： select id,val from t1 where id<10; id | val ----+----1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 112 第 5 章 SQL 优化手段 6 | 6 7 | 7 8 | 8 9 | 9 (9 rows) SELECT create_query_rule('qm1', 'select id,val from t1 where id<$1','select id from t1 where id<( $1-5)', true, 'text'); create_query_rule; -----------------(1 row) select id,val from t1 where id<10; id ---1 2 3 4 (4 rows) 2) 多个 $ 变量对单个 $ 变量 Query Mapping 规则，使用规则之前数据有 2 行 2 列，使用规则之后，数据变成了 val=5 的总数，即 count 的值： select id,val from t1 where id<20 and id>3 and val=5; id | val ---+----5 | 5 15 | 5 (2 rows) SELECT create_query_rule('qm1', 'select id,val from t1 where id<$1 and id>$2 and val=$3', 'select count(0) from t1 where val=$3', true, 'text'); create_query_rule ------------------(1 row) select id,val from t1 where id<20 and id>3 and val=5; count ------100 (1 row) 113 第 5 章 SQL 优化手段 3) 单个 $ 变量对多个 $ 变量 Query Mapping 规则，使用之前是 11 行 6 列数据，使用规则后对表 t2 进行过滤， 只有 1 行 6 列。 select * from t1, t2 where t1.id=t2.id and t1.id < 12; id | val | name | id | val | name ---+-----+------------+-----+-----+---------1 | 1 | Kingbase1 | 1 | 1 | Kingbase1 2 | 2 | Kingbase2 | 2 | 2 | Kingbase2 3 | 3 | Kingbase3 | 3 | 3 | Kingbase3 4 | 4 | Kingbase4 | 4 | 4 | Kingbase4 5 | 5 | Kingbase0 | 5 | 5 | Kingbase5 6 | 6 | Kingbase1 | 6 | 6 | Kingbase6 7 | 7 | Kingbase2 | 7 | 7 | Kingbase7 8 | 8 | Kingbase3 | 8 | 8 | Kingbase8 9 | 9 | Kingbase4 | 9 | 9 | Kingbase9 10 | 0 | Kingbase0 | 10 | 10 | Kingbase0 11 | 1 | Kingbase1 | 11 | 11 | Kingbase1 (11 rows) SELECT create_query_rule('qm1', 'select * from t1, t2 where t1.id=t2.id and t1.id<$1', 'select * from t1, t2 where t1.id=t2.id and t1.id>($1-5) and t1.id<$1 and t2.val>($1-2) and t2.val<($1+2)', true, 'text'); create_query_rule ------------------(1 row) select * from t1, t2 where t1.id=t2.id and t1.id < 12; id | val | name | id | val | name ----+-----+-----------+-----+-----+----------11 | 1 | Kingbase1 | 11 | 11 | Kingbase1 (1 row) 4) 多个 $ 变量对多个 $ 变量 Query Mapping 规则，使用前是 2 行 6 列数据，使用规则后变成 1 行 6 列。 select id * from t1, t2 where t1.id=t2.id and t1.id<30 and t2.val=5; | val | name | id | val | name ----+-----+-----------+----+-----+----------5 | 5 | Kingbase0 | 5 | 5 | Kingbase5 25 | 5 | Kingbase0 | 25 | 5 | Kingbase5 (2 rows) SELECT create_query_rule('qm1', 'select * from t1, t2 where t1.id=t2.id and t1.id<$1 and t2.val= $2', 'select * from t1, t2 where t1.id=t2.id and t1.id<$1 and t2.val=$2 and t2.id=$2', true, 'text'); create_query_rule 114 第 5 章 SQL 优化手段 ------------------(1 row) select * from t1, t2 where t1.id=t2.id and t1.id<30 and t2.val=5; id | val | name | id | val | name ----+-----+-----------+----+-----+----------5 | 5 | Kingbase0 | 5 | 5 | Kingbase5 (1 row) 5)$ 变量的交换规则： select id * from t2 where t2.id<50 and t2.val=5; | val | name ----+-----+----------5 | 5 | Kingbase5 25 | 5 | Kingbase5 45 | 5 | Kingbase5 (3 rows) SELECT create_query_rule('qm1', 'select * from t2 where t2.id<$1 and t2.val=$2', 'select * from t2 where t2.id<$2 and t2.val=$1', true, 'text'); create_query_rule ------------------(1 row) select * from t2 where t2.id<50 and t2.val=5; id | val | name ----+-----+-----(0 rows) 6) 条件下推 Query Mapping 规则，通过 Query Mapping 规则可以将外层连接条件下推到 union 子查询中。 explain SELECT count(0) FROM t1, (select * from t2 where t2.val>5 union select * from t1 where t1.val < 20) as v where t1.id=v.id; QUERY PLAN -----------------------------------------------------------------Aggregate (cost=134.48..134.49 rows=1 width=8) -> Hash Join (cost=91.75..130.23 rows=1700 width=0) Hash Cond: (t2.id = t1.id) -> HashAggregate (cost=67.25..84.25 rows=1700 width=154) Group Key: t2.id, t2.val, t2.name 115 第 5 章 SQL 优化手段 -> Append (cost=0.00..54.50 rows=1700 width=154) -> Seq Scan on t2 (cost=0.00..14.50 rows=700 width=18) Filter: (val > 5) -> Seq Scan on t1 t1_1 (cost=0.00..14.50 rows=1000 width=18) Filter: (val < 20) -> Hash (cost=12.00..12.00 rows=1000 width=4) -> Seq Scan on t1 (cost=0.00..12.00 rows=1000 width=4) (12 rows) SELECT create_query_rule('qm1', 'SELECT count(0) FROM t1, (select * from t2 where t2.val>$1 union select * from t1 where t1.val<$2) as v where t1.id=v.id; SELECT count(0) FROM t1, lateral(select * from t2 where t2.val>$1 and t1.id=t2.id union select * from t1 t where t.val<$2 and t.id=t1.id) as v;', true, 'text'); create_query_rule ------------------(1 row) explain (usingquerymapping) SELECT count(0) FROM t1, (select * from t2 where t2.val>5 union select * from t1 where t1.val < 20) as v where t1.id=v.id; QUERY PLAN -----------------------------------------------------------------Aggregate (cost=25287.00..25287.01 rows=1 width=8) -> Nested Loop (cost=25.21..25282.00 rows=2000 width=0) -> Seq Scan on t1 (cost=0.00..12.00 rows=1000 width=4) -> Unique (cost=25.21..25.23 rows=2 width=154) -> Sort (cost=25.21..25.22 rows=2 width=154) Sort Key: t2.id, t2.val, t2.name -> Append (cost=0.00..25.20 rows=2 width=154) -> Seq Scan on t2 (cost=0.00..17.00 rows=1 width=18) Filter: ((val > 5) AND (t1.id = id)) -> Index Scan using t1_pkey on t1 t (cost=0.15..8.17 rows=1 width=18) Index Cond: (id = t1.id) Filter: (val < 20) (12 rows) 7)CTE Query Mapping 规则，查询语句被映射成 with recursive 处理： select * from t1 where id = 10; id | val | name ----+-----+----------10 | 0 | Kingbase0 (1 row) 116 第 5 章 SQL 优化手段 select create_query_rule('qm1', 'select * from t1 where id = $1;','with recursive rtmp as(select id,val from t1 where id=$1 UNION select t1.id,t1.val from t1,rtmp where rtmp.id = t1.val)select * from rtmp;',true,'text'); create_query_rule ------------------(1 row) select * from t1 where id = 10; id | val ----+----10 | 0 (1 row) 5.6 物化视图 物化视图反映的是某个查询的结果或者是中间结果，其本身是会存储数据的，当数据量比较小时可以存储在内 存，但数据量比较大的时候就会存储在磁盘上。物化视图主要用于预先计算并保存比较耗时的操作的结果，比如表连 接或者聚集操作等。使用物化视图可以避免重复进行耗时的操作，从而提升查询性能。 但是 KingbaseES 不支持物化视图自动更新和增量更新，所以如果原始表的数据更新比较频繁，物化视图刷新比 较慢的情况下，不适合使用物化视图。 所以最终是否需要使用物化视图，需要结合具体的场景决定。 物化视图主要适用于以下两种场景： • 基表数据更新不频繁，但是表的操作或者连接操作比较耗时，使用比较频繁 • 查询中使用外部数据库的表，扫描该表会比较慢，可以通过物化视图将外部数据库表数据存储起来（需要手动 刷新物化视图） 5.7 分区表执行计划优化 分区表执行计划优化，旨在解决高并发场景下分区表子表数量较多时，SELECT/UPDATE/DELETE 计划生成 过程中出现的锁问题。 该功能由 GUC 参数 partition_table_limit 控制。取值范围 [0, INT_MAX] ，默认值为 20。当经过剪枝后的分 区表子表数量超过该参数的值时，将启用该功能快速生成执行计划。否则将按原有逻辑生成执行计划。 当 partition_table_limit 值为 0 时，表示不启用分区表执行计划优化功能。 117 第 5 章 SQL 优化手段 分区表执行计划优化适用于以下场景： • 分区表子表数量较多； • 分区表上索引数量较多； • 分区表查询语句中等价类条件较多； 以上场景中，只支持 SELECT/UPDATE/DELETE 命令。其中，下列情况不支持优化： • SELECT FOR SHARE/UPDATE • INSERT SELECT • RETURNING/rowMarks/WITH CHECK • UPDATE/DELETE 语句中涉到多个表 5.8 逻辑优化规则 本章节介绍 KingbaseES 优化器支持的逻辑优化规则及其使用方法。KingbaseES 添加 kdb_rbo 插件 实 现 其 所 支 持 的 逻 辑 优 化 规 则。 如 果 需 要 启 用 优 化 器 逻 辑 优 化 规 则， 需 要 在 kingbase.conf 配置文件中的 shared_preload_libraries 添加 kdb_rbo：shared_preload_libraries = ’kdb_rbo’。 KingbaseES 建议用户开启 kdb_rbo 插件来提升数据库查询性能。 5.8.1 count(distinct) 查询优化 为了解决 count(distinct) 查询性能差的问题，引入了 count(distinct) 查询优化的特性，优化器在 kdb_rbo 插件 对 count(distinct) 查询做了等价转换从而提升查询性能。 该特性通过 kdb_rbo.attribute_distinct_value_threshold GUC 参数进行控制。该参数范围是 [0.0, 1.0]，默认值 是 0.1。 5.8.2 distinct 消除 针对形如 select distinct ... from T where ... 的 sql 语句，当查询的目标列是唯一行时，此时可消除 distinct 操 作，从而提升查询性能。 5.9 SQL 优化建议 本章节介绍 KingbaseES 优化器支持的 SQL 改写优化建议。通过一定的规则调整 SQL 语句，在保证结果正确的 基础上能提高 SQL 执行效率。 118 第 5 章 SQL 优化手段 5.9.1 Union 转 Union All 建议 Union 在合并两个集合时会执行去重操作，而 Union All 则直接将两个结果集合并、不执行去重。执行去重会消 耗大量的时间，因此，在一些实际应用场景中，如果通过业务逻辑已确认两个集合不存在重叠，可用 Union All 替代 Union 以便提升性能。 5.9.2 Not in 转 not exists 建议 not in 语句需要执行 nestloop anti join 来实现，而 not exists 则可以通过 hash anti join 来实现。 在 join 列不存在 null 值的情况下，not exists 和 not in 等价。因此在确保没有 null 值时，可以通过将 not in 转 换为 not exists，通过生成 hash join 来提升查询效率。 以上两种 SQL 优化建议均可以通过如下接口查看 pg_catalog.sql_turning_advisor(query) -- 通过执行的 SQL 文本查看 SQL 语句是否存在优化建议 perf.sql_turning_advisor(queryid) -- 通过执行的 SQL 语句的 queryid 查看 SQL 语句是否存在优化 建议 119 第 6 章 SQL 优化组件 6 第 章 SQL 优化组件 本章介绍了 SQL 调优实际操作可使用的一些调优组件： • SQL 监控 6.1 SQL 监控 本章节介绍 KingbaseES 支持的 SQL 监控功能，开启 SQL 监控后，系统将自动监控并实时更新 SQL 语句的执 行过程和资源消耗。 6.1.1 SQL 监控的功能 启用 SQL 监视器功能后，当满足以下任一条件时，数据库将自动监视简单数据库操作： SQL 语句或 PL/SQL 子程序在一次执行中至少消耗了 5 秒的 CPU 或 I/O 时间。 SQL 语句并行执行。 SQL 语句指定提示。 6.1.2 SQL 监控的目的 实时 SQL 监视有助于确定语句花费时间的位置。可以查看通过 SQL 监控列表查询执行时间和资源使用情况明 细。通过这种方式，您可以更好地确定特定语句的执行成本。 实时 SQL 监控的示例包括： • 频繁执行的 SQL 语句的执行速度比正常情况慢。您必须确定此问题的根本原因。 • 数据库会话性能降低。 • 并行 SQL 语句需要很长时间。 • 生成 KWR 快照花费的时间比预期的要长得多。要诊断问题，需要知道一段时间内正在执行的内容。 120 第 6 章 SQL 优化组件 6.1.3 SQL 监控的示例 使用 SQL 监控的全部功能，建议在 kingbase.conf 里添加以下参数 shared_preload_libraries = 'plsql, sys_stat_statements, sys_sqltune' sql_monitor.track = 'all' sys_stat_statements.track = 'all' enable_hint = on 并在数据库中创建插件 CREATE EXTENSION sys_sqltune; 注入数据 CREATE TABLE smon_test(id bigint); INSERT INTO smon_test SELECT i FROM generate_series(1, 20000) i; 自动将满 5s 的语句纳入监控 SELECT * FROM sys_sleep(5); 可使用 hint 避免满 5s 的语句纳入监控 SELECT * FROM sys_sleep(5); 使用 hint 将不满 5s 的语句纳入监控 select * from sys_sleep(1); 自动将并行语句纳入监控 SELECT count(*) FROM smon_test; 查看视图 \x on SELECT * FROM V$SQL_MONITOR order by last_ts desc limit 1; 121 第 6 章 SQL 优化组件 \x off SELECT plan_index, plan_type, plan_object_name, plan_total_cost, total_time, tuples_num FROM V$SQL_PLAN_MONITOR WHERE sql_id = -7195113866045537130; 122 第 6 章 SQL 优化组件 生成 TEXT 版监控报告 SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(type=>'TEXT'); SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST(type=>'TEXT'); 生成网页版监控报告 SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(type=>'HTML'); 123 第 6 章 SQL 优化组件 SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST(type=>'HTML'); 生成英文网页版监控报告 set sql_monitor.language = 'english'; SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(type=>'HTML'); 124 第 6 章 SQL 优化组件 6.1.4 SQL 监控的外部接口 6.1.4.1 SQL 监控参数 全部相关参数 • sql_monitor.track SQL 语句监控层级，ENUM 类型，默认值为 none TOP：监控顶层 SQL 语句 all：监控嵌套层数小于 64 层的 SQL 语句 none：不监控 会话级参数 • sql_monitor.max 视图最大数据量，整数，默认 1000，最小 100 当系统保存的数据超出最大数量后，扔掉一些陈旧的统计结果，腾出 部分新的空间给新监控的 SQL。 系统级参数 125 第 6 章 SQL 优化组件 • sql_monitor.track_options 采集统计信息控制参数，ENUM 类型，默认为’basic’ basic：采集语句全部统计信息和执行计划节点基本统计信息。 full ：在’basic’ 基础上，采集执行计划节点的 buffer、wal 相关统计信息，有一定性能影响。 会话级参数 • sql_monitor.language 网页版监控报告使用语言，TEXT 类型，默认为中文（chinese 或 chn），可选为英文 (english 或 eng) chinese/ chn：网页版报告输出中文。english/eng：网页版报告输出英文。 会话级参数 6.1.4.2 DBMS_SQL_MONITOR 子程序 表 DBMS_SQL_MONITOR 子程序列出了 DBMS_SQL_MONITOR 所 提 供 的 子 程 序。 DBMS_SQL_MONITOR 包信息参考《PLSQL 系统包和类型参考手册》中的 DBMS_SQL_MONITOR 包章 节。 表 6.1.1: DBMS_SQL_MONITOR 子程序 子程序名称 说明 SQL_MONITOR_RESET 函数 清空视图数据 REPORT_SQL_MONITOR 函数 返回监控详细报告 REPORT_SQL_MONITOR_LIST 函数 返回监控列表报告 REPORT_SQL_MONITOR_TO_FILE 函数 将监控详细报告写入磁盘指定路径 REPORT_SQL_MONITOR_LIST_TO_FILE 函数 将监控列表报告写入磁盘指定路径 6.1.4.3 SQL 监控视图 表 V$SQL_MONITOR 视图列出了 SQL 监控相关的动态视图。更多视图信息参考《KingbaseES 插件参考手 册》中的 sys_sqltune 章节。 126 第 6 章 SQL 优化组件 表 6.1.2: V$SQL_MONITOR 视图 子程序名称 说明 V$SQL_MONITOR 此视图包含 SQL 语句的语句级统计信息。 相较于 sys_stat_statements，SQL 监控的统计信息不 会在多次执行中累积，本视图的一个条目只记录 SQL 语句的单次执行。 如果 SQL 语句采用并行查询，主进程及每个并行 worker 在每次执行中都会有单独的条目，并共享相同的 session_id、exec_id。 V$SQL_PLAN_MONITOR 此视图包含 SQL 语句的执行计划中每个步骤的监控统 计信息。 在执行 SQL 语句时，每个受监控的语句都对应本视图 中的多个条目，每个条目对应语句执行计划中的一个步 骤。 127 版权声明 版权声明 北京人大金仓信息技术股份有限公司（简称：人大金仓）版权所有，并保留对本手册及本声明的一切权利。 未得到人大金仓的书面许可，任何人不得以任何方式或形式对本手册内的任何部分进行复制、摘录、备份、修 改、传播、翻译成其他语言、将其全部或部分用于商业用途。 免责声明 本手册内容依据现有信息制作，由于产品版本升级或其他原因，其内容有可能变更。人大金仓保留在没有任何通 知或者提示的情况下对手册内容进行修改的权利。 本手册仅作为使用指导，人大金仓在编写本手册时已尽力保证其内容准确可靠，但并不确保手册内容完全没有错 误或遗漏，本手册中的所有信息也不构成任何明示或暗示的担保。 技术支持 • 人大金仓官方网站：http://www.kingbase.com.cn/ • 人大金仓文档中心：http://help.kingbase.com.cn/ • 全国服务热线：400-601-1188 • 人大金仓技术支持与反馈信箱：support@kingbase.com.cn 128 服务周期承诺 服务周期承诺 由于市场需求在不断变化，技术创新和发展的进程不断加剧，产品的版本更迭不可避免。人大金仓对于产品版本 生命周期的有效管理，有助于您提前规划项目，更好地从产品服务终止上过渡。 表 3: KingbaseES 产品生命周期里程碑 关键里程碑点 定义 产品发布日期 产品正式发布版本，即 GA（general availability）版本的发布日期。 停止销售日期 正式停止销售的日期，版本停止接受订单日。该日之后，产品将不再销售。 停止功能升级日期 在该日期之后，不再提供新特性和新硬件支持。但依旧提供错误修复、安全修复、功 能维护等服务。 停止功能维护日期 在该日期之后，不再维护功能，修复问题。但依旧提供安全修复等服务 停止安全维护日期 在该日期之后，不再发布补丁版本修复中高风险漏洞，仅提供有限的支持。 产品服务终止日期 停止提供产品服务和支持的日期。包括软件维护版本，缺陷修复，以及针对该产品的 所有服务支持（包括服务热线和远程/现场支持）。 服务周期策略 金仓数据库管理系统 KingbaseES 产品确保以下的服务周期： 1）产品自发布之日起至产品停止功能升级（包含新特性、新硬件支持）之日不少于 5 年。 2）产品停止功能升级之日起至产品停止功能维护（主要包括问题修复）之日不少于 4 年。 3）产品功能维护停止之日起至产品停止安全维护（包括中高风险漏洞修复）之日不少于 2 年。 服务终止策略 金仓数据库管理系统 KingbaseES 产品确保在销售后，至少提供 6 年的服务支持。 注意: 人大金仓将会综合各方因素来确定产品服务终止日期。并将在实际产品服务终止日期之前至少 90 天，通过公 129 服务周期承诺 开方式宣布产品服务终止日期。 130