KingbaseES 优化之sql优化方法
金仓数据库在sql层面提供了多种优化手段，但是这些的前提时需要保证我们的统计信息准确，优化器已经在正确信息下选择了它认为的最优的执行计划，
优化手段包括
•使用索引
索引解决的问题用于在进行表的扫描时进行加速，在执行计划中我们经常看到seq scan 它的含义时全表扫描，出现这种情况时我们就要去分析是否合理。
当然针对小表或者需要返回行数很多的情况优化器会选择走seq scan 即使有对应的索引， 这是因为索引的扫描在找到叶子节点之前一次只扫描一个节点，比如一个三层索引查找一行数据至少要走三次IO，虽然大部分btree索引在叶子节点加上了双向指针，可以提升索引范围扫描的效率，但是在数据行数达到一定比例后 在IO层面的消耗还是比一张表的全表扫描高，这是因为全表扫描可以一次IO读取多个数据块。
索引种类很多这里只介绍几个常见的索引类型
BTREE索引
BTREE索引，适合按照顺序存储的数据进行比较查询和范围查询，b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。
索引与递归查询结合，还能实现快速的稀疏检索。
BTREE索引使用场景：
-
<，<=，=，>，>=
-
以及这些操作的组合，比如between and，也可以使用BTREE。
-
在索引列上的IS NULL 或者IS NOT NULL也可以使用BTREE。
-
BTREE索引也可以用于模糊查询，但是仅限字符串开头是常量的情况下，比如 name LIKE ‘Jason%’，或者name ~ ’^Jason’。但是name LIKE ‘%Jason’是不能用的。
-
Min/Max聚集操作也可使用BTREE索引。
-
其实在merge join以及order by中，可以通过使用BTREE索引的有序性来减少sort带来的代价。
Hash索引
只能处理简单的等值比较，当索引列涉及到等于操作比较时，优化器会考虑使用Hash索引。
由于hash索引结构的特殊性，其检索效率非常高，可以一步到位。而一般使用的B-tree索引需要从根节点->枝节点->页节点。所以从工作模式上看，hash索引的效率要比btree索引要高。
hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。
GIN索引
gin是倒排索引，存储被索引字段的VALUE或VALUE的元素，以及行号的list或tree。
（ col_val:(tid_list or tid_tree) ， col_val_elements:(tid_list or tid_tree) ）
应用场景
- 当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索）
- 当用户的数据比较稀疏时，如果要搜索某个VALUE的值，可以适应btree_gin支持普通btree支持的类型。（支持btree的操作符）
- 当用户需要按任意列进行搜索时，gin支持多列展开单独建立索引域，同时支持内部多域索引的bitmapAnd, bitmapOr合并，快速的返回按任意列搜索请求的数据。
BRIN索引
块范围索引，它将数据在磁盘上的block按照一定的数目进行分组，分组之后，计算每组的取值范围。在查找数据时，会遍历这些取值范围，排除掉不在范围之内的分组。BRIN索引适用于存储流式数据日志。例如：按照时间插入的数据，由于数据是按照时间插入，因此数据块上记录的范围信息很少会出现交叉情况，索引过滤后需要比较的数据块也会少很多；反之，如果数据交叉严重，通过索引无法过滤掉任何一个数据块时，操作起来会比全表扫描更加耗时。
BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。
BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果非常的好。
例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很棒。
小结 - 索引的选择
- B-TREE 索引：适用于大多数查询或不同的数据类型
- HASH 索引：适用于等值查询
- BRIN 索引：适用于 非常大的顺序排列数据集
- GIN 索引：文档和数组
•使用 HINT
hint的目的就是手动的对执行计划进行改写，改性范围包括扫描方式、join方式、join 顺序（leading 类型）、并行、聚集 、参数、
hint 使用方法：
select /*+hint内容 */
详细格式
表扫描
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...]) 强制在表上使用索引扫描，只限制指定的索 引。
ForceIndexOnlyScan(table[ index...]) 强制在表上使用 Index only scan，限制特定 的索引。当 Index only s can 不可用时，可以 使用索引扫描。
ForceBitmapScan(table[ index...]) 强制在表上使用位图扫描。当指定索引名之 后，位图扫描会使用指定的索引进行扫描。
连接方式
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...])
并行
Parallel(table_name workers)
rows 限定行数
explain analyze select/*+Rows(t2 #3)*/t2.id from t2,t3
leading 类型
leading(join_table_list)
leading((outer_table inner_table))
ordered --按照表出现的顺序
explain select/*+ordered*/t2.id from t2,t3,t1 where t1.id=t3.id and t1.id<3 and t3.val=t2.id
set类型 --临时指定变量
Set(Param_Name Param_Value)
Materialized 类型的 hint：可多个表进行连接时，强制使用 nestloop 连接且内表为 inner_table_list
指定的一个或多个表，并且对内表进行物化
materialize(inner_table_list)
Use_nl_with_index 类型的 hint：可多个表进行连接时，强制使用 nestloop 连接且内表为 inner_table 指定的表，并且内表使用连接条件上的列使用索引扫描：
use_nl_with_index(inner_table)
聚集
Hashagg
Groupagg
• 调整性能参数
通过调整性能参数可以在以下几个方面进行优化
1、通过系统参数修改执行计划评估的因子，影响执行计划生成，但是在生产环境不建议在系统参数级别使用，可以通过或hint方式进行调整。
比如 seq_page_cost、andom_page_cost、cpu_tuple_cost、cpu_index_tuple_cost、cpu_operator_cost
2、修改节点扫描和join相关的参数同上不建议在参数文件级别进行调整。
3、内存参数。
内存参数调整使用中常用的手段，用来解决sql语句扫描数据或者排序是用到disk IO的情况，这些情况都是出现在系统内存命中率低的时候。
buffer 读取如果用到了很多disk IO 可以尝试调整shared buffer
排序 hash等操作用到了 temp 或者disk IO 可以通过调整work mem 。
临时表的操作比较频繁可以调整 temp_buffers
如果是系统维护引起的内存不足问题可以调整 maintenance_work_mem
• 使用并行
多核 CPU 来加速一个 SQL 语句的执行时间，这种特性被称为并行查询。由于现实条件的限 制或因为没有比并行查询计划更快的查询计划存在，很多查询并不能从并行查询获益。
kingbase ES 也添加了一些系统参数可以控制并行，比如什么情况下使用并行操作、并行使用的系统资源限制等。 参数列表如下
并行度相关参数：
• 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。
• 使用 Query Mapping • 物化视图
Query Mapping 功能允许用户预先创建 SQL 语句的映射关系并储存在相应的系统表，当用户输入的 SQL 语句 与所创建的映射关系相匹配时，替换成映射的 SQL 语句去实际执行，
具体用法可以详细阅读《KingbaseES数据库SQL调优指南.pdf》
参考文档：
KingbaseES数据库SQL调优指南.pdf
KingbaseES数据库性能调优指南.pdf
