526互联

KingbaseES 优化之sql优化方法

发布时间 2023-09-19 18:42:00作者: KINGBASE研究院

金仓数据库在sql层面提供了多种优化手段，但是这些的前提时需要保证我们的统计信息准确，优化器已经在正确信息下选择了它认为的最优的执行计划，

优化手段包括

•使用索引

索引解决的问题用于在进行表的扫描时进行加速，在执行计划中我们经常看到seq scan 它的含义时全表扫描，出现这种情况时我们就要去分析是否合理。

当然针对小表或者需要返回行数很多的情况优化器会选择走seq scan 即使有对应的索引， 这是因为索引的扫描在找到叶子节点之前一次只扫描一个节点，比如一个三层索引查找一行数据至少要走三次IO，虽然大部分btree索引在叶子节点加上了双向指针，可以提升索引范围扫描的效率，但是在数据行数达到一定比例后 在IO层面的消耗还是比一张表的全表扫描高，这是因为全表扫描可以一次IO读取多个数据块。

索引种类很多这里只介绍几个常见的索引类型

BTREE索引

BTREE索引，适合按照顺序存储的数据进行比较查询和范围查询，b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。

索引与递归查询结合，还能实现快速的稀疏检索。

BTREE索引使用场景：

  1. <，<=，=，>，>=

  2. 以及这些操作的组合，比如between and，也可以使用BTREE。

  3. 在索引列上的IS NULL 或者IS NOT NULL也可以使用BTREE。

  4. BTREE索引也可以用于模糊查询，但是仅限字符串开头是常量的情况下，比如 name LIKE ‘Jason%’，或者name ~ ’^Jason’。但是name LIKE ‘%Jason’是不能用的。

  5. Min/Max聚集操作也可使用BTREE索引。

  6. 其实在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) ）

应用场景

  1. 当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索）
  2. 当用户的数据比较稀疏时，如果要搜索某个VALUE的值，可以适应btree_gin支持普通btree支持的类型。（支持btree的操作符）
  3. 当用户需要按任意列进行搜索时，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