kingbase SQL 调优基础-01

1. 识别高负载语句
通过查看过去的执行历史记录，找到消耗了较多系统资源的 SQL 语句。KingbaseES 提供的工具包括：
 sys_stat_statements
 sys_stat_activity
 kbbadger
以上两种工具使用方法请参见“KingbaseES 数据库性能调优指南”3.3.1 章节。
 SYS_KWR

2. 收集性能相关的数据
与 SQL 性能相关的其他数据包括该语句访问的表和视图的结构，以及该语句可用的任何索引的定义，
如何收集统计信息请参考本手册章节 统计信息的收集 和扩展统计信息
主动收集
主动收集是指用户通过定时脚本或者人工执行 analyze 命令来进行
不带参数运行 ANALYZE 会为数据库中所有的表更新统计信息,运行时间非常长的处理，因此一般不建议这样做

3. 确定性能问题产生的原因
要确定性能问题产生的原因，最重要的手段是分析执行计划
SQL 性能问题的原因包括：
 缺少 SQL 访问结构
缺少索引、物化视图、分区之类的 SQL 访问结构是导致 SQL 性能不佳的典型原因
 次优的执行计划
优化器的主要作用是给出最优（较优）的执行计划
 陈旧的优化统计数据
当统计信息维护操作（自动或手动）无法跟上 DML 导致的表数据更改时，收集到的统计信息可能会过时
 低效的 SQL 语句设计
低效设计的例子包括：
 忽略添加连接条件，导致笛卡尔积连接
 指定 UNION 而不是 UNION ALL
 使子查询针对外部查询中的每一行执行

4. 定义问题的范围
例如，在单个 SQL 语句未使用有用的索引的情况下，更改整个数据库的优化器参数（enable_seqscan =off）可能会损害整体性能。
如果单个 SQL 语句有问题，则可以考虑通过 HINT、改写语句等方式将影响范围限制在语句级。

5. 实施优化手段
在步骤 3 中，我们可以得知 SQL 性能问题产生的原因较为多样，所以优化手段的实施也会因情况而异。
例如，我们可能会通过将 UNION 改写成 UNIONALL 来提高性能，也可能通过创建索引来提高性能。

6. 评估优化效果
在实施优化手段后，通过实际执行来评估优化效果是否达到了预期。

 SQL 调优工具
 执行计划
执行计划是 SQL 性能分析的重要工具，阅读和分析执行计划是确定性能问题原因的必要手段。

优化手段
KingbaseES 提供了多种手段来供用户做 SQL 调优，详见SQL 优化手段 ，常见的调优手段包括：
 使用索引
 使用 HINT
 调整参数
 使用并行

查询优化
查询优化是 SQL 调优的重要理论基础
优化器代价估计依赖于统计信息。优化器使用统计信息来估算从表或索引中检索出来的行数

SQL 处理过程
SQL 语句的处理通常都要经历以下 5 个阶段
1) 词法语法分析阶段（parser 子系统）
检查 SQL 语句是否符合词法语法规则，然后转化为一颗解析树
2) 语义检查阶段（analyzer 子系统）
检查解析树是否符合语义规则，然后转化为一颗查询树。
3) 查询重写阶段（rewritter 子系统）
如果查询树中涉及视图或者规则，重写为查询树。
4) 查询优化阶段（planner 子系统）
经过逻辑优化和物理优化，生成最优的执行计划。
5) 查询执行阶段（executor 子系统）
按照执行计划进行执行。

查询优化器的主要作用就是生成最优的执行计划。
支持了以下两种技术：
 基于规则的逻辑优化（RBO）
 基于代价估算的物理优化（CBO）

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 具有相同的效果

3.2.2.1 物理优化简介
物理优化则是基于代价的查询优化（Cost-based Optimizer，简称 CBO），其主要流程是枚举各种待选的物理查询路径，
并且根据路径上各节点的信息计算这些待选路径的代价，进而选择出代价最小的路径。
 选择：seqscan、indexscan、index only scan 等
 排序：sort、indexscan。
 连接：hashjoin、merge join、nestloopjoin。（连接顺序也可以变化）
 聚集：hashaggregate、groupaggregate。

3.2.2.2.1 代价估算
这些路径中每个节点主要的操作可归纳为数据的读写及运算，这些操作所产生的开销主要为 CPU 开销及 I/O 开销，某些情况下会有通信代价（如并行）。
所以查询优化代价估算基于 CPU 开销和 IO 开销，即：
总代价 =I/O 代价 + CPU 代价 + 通信代价（并行）

常见的单表扫描方式包括：
 顺序扫描（SeqScan）
 索引扫描（IndexScan）

顺序扫描也叫全表扫描，基本算法为：根据实际的数据的存取顺序，连续扫描表中的所有数据。

通过禁用 seqscan 让优化器使用 indexscan
set enable_seqscan to off;

禁用 indexscan 和 bitmapscan，使用 seqscan 的计划
set enable_indexscan = off;
set enable_bitmapscan = off;

3.2.2.4 两表连接代价估算
调整连接方式是 SQL 调优的常用手段

嵌套循环连接（NestLoop）
嵌套循环连接为两表连接最基本的算法。
适用于：1）内外表数据量不大的情况 2）内表数据量很小，外表数据量大的情况

执行总代价
(actual time=0.960..68.599 rows=1 loops=1)
使用 seqscan 实际执行总代价（实际花费 68.599ms）
预估成本和预估代价

3. 索引嵌套循环连接
适用于：外部表数据量不大，内部表可以使用索引扫描一次很快的情况

3.2.2.4.2 归并连接（MergeJoin）
与嵌套循环连接不同，归并联接只能用于自然联接和等值联接。
1. 基本归并连接
归并连接的基本原理为对两个表的数据进行排序，然后做连接

归并连接算法的步骤是：
1. 将每个表排好序
2. 读入每个表的元组
3. 找出两个表中最小的元组进行匹配
4. 找出次小的元组匹配依此类推，完成其他元组的两表连接。

适用情况：1）两个表的数据都是基本有序 2）两表可以使用索引或者外表有索引

Hash 连接（HashJoin）
与归并连接相同同，hash 联接只能用于自然联接和等值联接。
内存中的 Hash 连接是在 work_mem 中处理的。Hash 连接有两个阶段：构建阶段和探测阶段

Hash 连接的基本原理为：
1) 对内表建立 hash 表，扫描所有内表数据到各个 hash 桶里面。
2) 一行行扫描外表数据，对外表数据进行 hash，hash 到某个桶里面。
3) 跟这个桶里面的数据进行连接。
适用情况：数据分布比较随机无序，重复值不是特别多的情况。

3.3 优化器统计信息
统计信息需要收集的主要内容：
 表级统计信息：页面数、元组数
 列级统计信息：直方图、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× 索引选择率。

表级信息
优化器需要知道每一个表和索引占用的磁盘块数，以及表和索引包含的记录数。这些信息主要保存在 sys_class 表中：
select * from sys_class where relname = 'student';
 Relpages：表或者索引占用的磁盘页面数。
 Reltuples：表或者索引记录的总数（预估的）。
出于效率考虑，reltuple 和 relpages 不是实时更新的，因此其中可能会包含一些失效的旧值，
它们只有被 VACUUM、ANALYZE 和几个 DDL 命令（例如 CREATE INDEX）更新。

列统计信息
列统计信息是指对每个表的每一个列（也叫属性）的统计信息，它主要存储在 sys_statistic 系统表中，
基本统计信息
Sys_statistic 系统表存储字段基本统计信息如下：
 Starelid：表 OID
 Staattnum：列的序号
 Stainherit：是否为继承列，f 为否，即为表列本身，t 为是，表该列继承自其他表列。
 Stanullfrac：NULL 值的比例
 Stawidth：非空值的平均存储宽度
 Stadistinct：消除重复唯一值的量