kingbase SQL 调优基础-06

调整性能参数
5.4.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.4.2 节点开关参数
扫描类开关：
 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.4.4 内存参数
 work_mem
原理：内部排序和哈希操作可使用的工作内存大小。该内存是在开始使用临时磁盘文件之前使用的内存数目。
应用范围：数据比较多大的情况，主要和排序的数据有关系，排序数据越大，设置的就越大，比如 16g
内存，tpch 测试，单用户 10g 规模数据，设置 2g 的 work_mem。数值以 kB 为单位的，缺省是
1024(1MB)。索引扫描不用 work_mem。

 maintenance_work_mem
原理：在维护性操作 (比如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等) 中
使用的最大的内存数。
应用范围：在维护性操作 (比如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY
等）调整大小，默认是 16MB，比如创建索引的索引数据很大，比如 10g，如果内存允许就可以调整这个
参数 1g，一般在灌数的时候调大，灌数完毕测试时再调小。

 temp_buffers
原理：存储临时信息时使用的最大块数。
应用范围：最小值为 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 为当前连接数)；

5.5 使用并行
在 kinbase.conf 里设置以下参数。
并行度相关参数：
 max_worker_processes：
设置系统支持的最大后台进程数，默认值是 8，此参数调整后需要重启数据库生效。
 max_parallel_workers：
最大并行 worker 数。该数值不能大于 max_worker_processes。
 max_parallel_workers_per_gather：
最大并行执行 worker 数。不能超过 max_parrellel_workers。
 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 pg_size_pretty(pg_relation_size(’student’));

优化器控制开关：
 enable_parallel_append
优化器控制开关，是否允许并行 append plans。
 enable_parallel_hash
优化器控制开关，是否允许并行 hash plans。

 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 和 parallel_tuple_cost 算出来的并行总 cost，跟其他不使用并行方案比较后决定是否启用并行，以及本次查询的并行进程数
（小于等于 max_parallel_workers）。

强制使用多少个并行
通过 create table test(n int) with (parallel_workers = 4); 强制启动 4 个 worker 进程测试
查询效率并没有随 worker 数量线性增加；在启用 3 个 worker 进程和 4 个 worker 进
程进行并行查询时，查询效率基本一致了。所以并不是开启的 worker 数量越多，查询效率越高

Query Mapping 功能允许用户预先创建 SQL 语句的映射关系并储存在相应的系统表，当用户输入的 SQL 语句
与所创建的映射关系相匹配时，替换成映射的 SQL 语句去实际执行。
5.6.2 Query Mapping 的功能
Query Mapping 主要作用是做 SQL 的匹配/替换工作，

5.6.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
);
5. 如果需要删除该规则，使用 SELECT drop_query_rule(’qm1’);-- 入参 qm1 为规则名
6. 如果需要使原本不生效的规则生效，可以使用 SELECT enable_query_rule(’qm1’);-- 入参 qm1 为规则名
7. 如果需要使原本生效的规则失效，可以使用 SELECT disable_query_rule(’qm1’);-- 入参 qm1 为规则名

5.7 物化视图
物化视图反映的是某个查询的结果或者是中间结果，其本身是会存储数据的
物化视图主要适用于以下两种场景：
 基表数据更新不频繁，但是表的操作或者连接操作比较耗时，使用比较频繁
 查询中使用外部数据库的表，扫描该表会比较慢，可以通过物化视图将外部数据库表数据存储起来（需要手动刷新物化视图）