人大金仓数据库KingbaseES-Hint介绍-CSDN博客

本文链接：https://blog.csdn.net/arthemis_14/article/details/134461990

关键字：

SQL调优、SQL语言特点、SQL处理过程、人大金仓、KingbaseES

概述

(1) 问题：查询优化器在尽量提供最优的执行计划，但是因为种种原因，优化器以及它所依赖的统计信息存在着自由的局限性。所以，很多情况下，优化器提供的统计信息不是最优，需要人工干预。

(2) 方案：使用HINT，人为控制优化器选择更加高效的执行计划。可控制内容包括：

① 扫描类型 (Scaning methods)

② 连接类型 (Join methods)

③ 连接顺序 (Joining order)

④ 连接返回行数 (row number correction)

⑤ 语句级GUC参数 (GUC setting)

⑥ 自动映射HINT规则 (parallel query)

(3) 效果：不修改应用即可选出

HINT 的功能

(1) Scan method

Scan method 相关的 HINT 注释只会在普通表、继承表、UNLOGGED 表、临时表和系统目录上生效。其括号内的值只能是一个关系。另外，也可以在一个查询中，对其中的多个目标表进行干预。包括：

① SeqScan(table)：强制在表上使用顺序扫描。

② TidScan(table)：强制在表上使用 Tid 扫描。

③ IndexScan(table[ index...])：强制在表上使用索引扫描，只限制指定的索引。

④ IndexOnlyScan(table[ index...])：强制在表上使用 Indexonly scan，限制特定的索引。当 Indexonlyscan 不可用时，可以使用索引扫描。

⑤ BitmapScan(table[ index...])：强制不在表上使用位图扫描。

⑥ NoSeqScan(table)：强制不在表上使用顺序扫描。

⑦ NoTidScan(table)：强制不在表上使用 Tid 扫描。

⑧ NoIndexScan(table)：强制不在表上使用索引扫描和 indexonlyscan。

⑨ NoIndexOnlyScan(table)：强制不在表上使用 indexonlyscan。

⑩ NoBitmapScan(table)：强制不在表上使用位图索引。

(2) Join method

Join method 相关 HINT 注释可以影响普通表、继承表、UNLOGGED 表、临时表、外部表、系统目录、表函数、VALUES 命令结果和 CTEs 上的连接。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...])：在对指定的表进行连接时，强制不使用排序合并连接。

(3) Join order

Leading 短语括号中的值必须为两个或多个关系，并且每个关系不能重复。在使用Leading 时，可以使用圆括号嵌套来干预连接操作的顺序。使用 UNION 操作符对多个 SELECT语句进行合并时， UNION 内部的各个查询语句之间的连接顺序无法用 Leading 短语干预。主要有以下两种形式：

① Leading(table table[ table...])：强制使用指定的连接顺序。

② Leading(<join pair>)：强制使用指定的连接顺序和方向。其中一个 joinpair 可以是一对表和/或者用括号括起来的可以构成嵌套结构的其他连接对。

(4) Row number correction

Rows 短语的圆括号内必须包含两个或者两个以上的关系。当圆括号内存在多个不相关连接时，只有最左边的连接生效。当查询中有 where 子句时，依然不影响对结果的修正。

Rows(table table[ table...] correction)：修正特定的表连接后的结果的行数. 可用的修正方法有 absolute (#<n>), addition (+<n>), subtract (-<n>) and multiplication (*<n>). <n>必须是函数 strtod()可以读取的字符串。

(5) Parallel query configuration

它对普通表，继承父项，UNLOGGED 表和系统目录产生影响。外部表，表函数，values子句，CTE，视图和子查询不受影响。

Parallel(table <# of workers> [soft|hard])：在指定的表上强制执行或者禁止并行执行. <# of workers>是所需的并行 workers 数, 0 代表禁止并行执行。如果第三个参数是 soft (默认值), 仅会改变 max_parallel_workers_per_gather 并把所有事情留给优化器。 Hard 表示把 workers 数强制为指定的值。

(6) GUC setting

Set 短语可以一次对多个参数进行修改，但是当多个相同的 hint 修改同一个 GUC 参数时，只有最后运行的生效。参数的修改只在此次的查询有效。

Set(GUC-param value)：在优化器运行时，将这个 GUC 参数设置为指定的值。

(7) plsql 中的使用

sys_hint_plan 也可以用于在 PLSQL 脚本中的查询，但有一定的限制。仅适用于返回一行的查询(SELECT, INSERT, UPDATE, DELETE)、返回多行的查询(RETURN QUERY)、动态 SQL语句(EXECTE)、Cursor 开启(OPEN)、循环查询的结果(FOR)。在 PLSQL 中 HINT 注释需要放在 SELECT 与目标列之间。

HINT 的使用

HINT 通过在目标 SQL 语句 SELECT 之后给出的特殊形式的注释来读取 HINT 注释。注释的形式以字符序列‘/+’开头以‘/’结尾，例如/+SeqScan(tablename)/，其具体使用方法如下：

(1) 在 kingbase.conf 配置文件中，配置：enable_hint = on

(2) 启动数据库

(3) 使用 Hint 的注释使 Hint 生效

① Scan 类型的 hint：指明单表扫描时在目标表上使用特定的扫描方法。它只会在普通表、继承表、UNLOGGED 表、临时表和系统目录上生效。外部表、表函数、VALUES 子句、CTE、视图和子查询不受影响。其括号内的值只能是一个表名。但可以在一个查询中，对其中的多个目标表进行不同方式的干预。

② 并行执行类型的 hint：在扫描时强制执行或禁止并行执行。它可以对普通表，继承父项，UNLOGGED 表和系统目录产生影响。外部表，表函数，值子句，CTE，视图和子查询不受影响：Parallel(table_name workers)。

③ ROWS 类型的 hint：指明中间结果集的大小，支持绝对值和相对值。该 Hint 可以修正因优化器的限制而连接操作后错估的行数。该 hint 支持指定多个表的连接预估行数，也可以指定简单表，继承或分区表，外表，子查询，CTE 的行数：rows(table_list #|+|-|* const)。

④ Join 类型的 hint：在指定的表进行连接时，强制使用特定的连接方法。它可以影响普通表、继承表、UNLOGGED 表、临时表、外部表、系统目录、表函数、VALUES 命令结果和 CTE 上的连接。但视图上的连接和子查询不受影响。

⑤ Leading 类型的 hint：指定在两个或多个表进行连接时的连接顺序，包括内外表顺序。一个查询块上只能有一个 leading 类型的 hint 生效，ordered hint 具有最高的优先级，当没有 ordered 而有多个 leading 时，最后一个起作用：

• 仅指定 join 顺序，不指定内外表顺序：同时指定 join 顺序和内外表顺序： leading((outer_table inner_table))

• 按照 SQL 中表出现的顺序指定连接的顺序：ordered

⑥ SET 类型的 hint：可以对查询语句块设置用户级别的配置参数，即通过 SET 语句可以修改的参数。参数影响的为该语句块及其子查询涉及的表的执行计划生成： Set(Param_Name Param_Value) 。

⑦ Blockname 类型的 hint：为子查询或子链接命名。Hint 支持跨查询块的对象引用。即一个查询块中的 hint 可以通过〞块名.表名〞的形式控制其子查询块中的表。当有些子查询块没有名字标识时 (例如子链接)，可以使用该 hint 来进行命名： blockname(subquery_name)。

⑧ Materialized 类型的 hint：可多个表进行连接时，强制使用 nestloop 连接且内表为 inner_table_list 指定的一个或多个表，并且对内表进行物化： materialize(inner_table_list)。

⑨ Use_nl_with_index 类型的 hint：可多个表进行连接时，强制使用 nestloop 连接且内表为 inner_table 指定的表，并且内表使用连接条件上的列使用索引扫描： use_nl_with_index(inner_table)。

⑩ Aggregate 类型的 hint：KingbaseES 的聚合运算的算法有两种：排序 (GroupAgggregate) 和哈希 (HashAggregate)。通过 hint 可以影响优化器选择哪种聚合运算： Hashagg 和Groupagg。

参数配置

参数名	含义	默认值
enable_hint	启用 HINT	off
hint_debug_print	是否打印 HINT 的 debug 信息	off
hint_message_level	指定 debug 打印的信息级别。可用的值为 error, warning, notice, info, log, debug1, debug2, debug3, debug4, debug5	log

参数 enable_hint 默认关闭，当此参数关闭后，SQL 查询语句中的 HINT 注释将不会对执行计划产生影响。

当参数 hint_debug_print 设置为 on，并且 hint_message_level 设置为 log 后，在一个带有格式正确的 HINT 注释的 SQL 语句运行时，会一同输出 hint 的调试信息，内容包含 used hint、not used hint、duplication hint、error hint。

测试注意事项

(1) 总体测试策略为：设计hint能够精确控制执行计划的情况以及hint的四条新功能和五个bug修复问题的正向用例，在有一定数据量的情况下，测试hint三期新增功能和修复bug的性能对比；对比查询计划；使用旧版本执行sql语句得到查询结果与带有hint三期功能得到的查询结果进行对比，预期是查询结果一致。主要包括以下内容：

① 测试正向功能满足需求：hint的四条新功能和五个bug修复问题符合预期；

② 测试逆向用例满足需求：本特性中不改进的问题（指定子查询的连接、别名问题）使用hint不生效。

(2) select/*+hashagg*/ max(a),min(a) from pt order by a; 这样的SQL不能用hash聚集；

(3) 如图，圈出的位置显示的是hint里面能够处理的表名，在hint中按照这个地方的提示修改表名，hint就会生效；

$C:\Users\sunna\AppData\Roaming\Ktalk\localfiles\media\d8ebddd995f68cfda116df2dcc78fb2b.png$

(4) 备份还原后的视图执行计划发生变化。具体来说，还原出的库首次查询到的视图的执行计划与原本的库不同，再次查询就相同了（如图所示）。原因在于：备份还原后，统计信息发生了变化（还原的过程相当于再次执行一遍sql，但是没有analyze，当第一次查询，会执行analyze，再次查询，执行计划就一致了）；

备份前

41f0a0120989e1b7bad232e55effebeb

(5) 由于BUG2023051701337的影响，在测试支持指定union all并行时，ParallelAppend(n)，n>max_parallel_workers_per_gather、n指定为非数值，结果与预期不符（一轮测试）。

(6) 支持指定agg节点并行功能，在设计测试用例时需要注意的是：使用聚合函数时，语句中要有group by。

(7) 支持指定union all并行时，ParallelAppend(n)，n指定为非数值时，hint语法错误，预期不使用hint，即hint提示：error hint: parallelappend()，且执行计划与不使用hint时相同。

(8) 在oracle数据库按原方案执行测试，设置并行度为2，结果如下：

① 不使用hint，并行度取2

② 使用hint，指定parallel(1)，hint提示 “U - parallel(1)”，执行计划中并行度为1

（3）使用hint，指定parallel(+)，hint提示“E - parallel(”，执行计划与不使用hint时相同（并行度为2）

Hint三期与竞品差异点

Hint	ORACLE	Sqlserver	MySQL	DM	Gauss	KES
INSERT/UPDATE/DELETE	√	√	√	√	×	×
union all并行	√	×	×	×	×	×
支持指定agg节点并行	×	×	×	×	×	×
支持指定参数化路径行数	×	×	×	×	√	×