KingbaseES性能调优《索引优化建议》

本文主要介绍KingbaseES使用插件（索引优化建议）进行数据库性能调优。

一、插件 sys_qualstats

1、简介
sys_qualstats 是一个 KingbaseES 的扩展，用于保存 WHERE 语句和 JOIN 子句中谓词的统计信息。
如果希望能够分析数据库中最常执行的 quals（谓词），这非常的有用，该插件利用这点来提供索引建议。

2、插件 sys_qualstats 加载方式
在使用 sys_qualstats 之前，我们需要将他添加到 kingbase.conf 文件的 shared_preload_libraries 中，并重启KingbaseES 数据库。

1	`shared_preload_libraries =` `'sys_qualstats,sys_stat_statements'` `# (change requires restart)`

3、创建插件

1 2	`create` `extension sys_qualstats;` `create` `extension sys_stat_statements;`

系统自动创建 sys_stat_statements 插件。

二、使用sys_qualstats插件

1、修改数据库参数配置
kingbase.conf 中设置

shared_preload_libraries = 'sys_qualstats,sys_stat_statements,sys_hypo' # (change requires restart)
sys_stat_statements.track='top'
sys_qualstats.sample_rate=1

2、创建插件

create extension sys_qualstats;
create extension sys_hypo;
create extension sys_stat_statements; --插件 sys_stat_statements 由系统自动创建

三、测试案例

1、准备测试数据

create table t1(id int, name text);
INSERT INTO t1 SELECT generate_series(1, 1000000), md5(generate_series(1, 1000000)::text);

2、查询SQL

select * from t1 where id = 100;
id | name
-----+----------------------------------
100 | f899139df5e1059396431415e770c6dd
(1 行记录)

3、查看执行计划

explain analyze Select * from t1 where id = 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..14612.43 rows=1 width=37) (actual time=0.199..45.577 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on t1 (cost=0.00..13612.33 rows=1 width=37) (actual time=11.960..24.969 rows=0 loops=3)
Filter: (id = 100)
Rows Removed by Filter: 333333
Planning Time: 0.041 ms
Execution Time: 45.611 ms
(8 行记录)

4、查看索引建议
new_cost: 创建索引以后的 cost 值
old_cost 无索引情况的 cost 值
ddl_index 创建 index 的 sql 语句
benefit 创建索引后的收益值

test=# \x
扩展显示已打开.
test=# select * from index_recommendation_by_qual;
-[ RECORD 1 ]---+-------------------------------------------
nspname | public
relid | t1
attnames | {id}
possible_types | {bitmap,brin,btree,hash}
execution_count | 1000000
queryid | 6060413669021853650
query | select * from t1 where id = 100;
ddl_index | CREATE INDEX ON public.t1 USING btree (id)
old_cost | 14612.43
new_cost | 8.07
benefit | 99.94 %

5、根据索引建议创建索引

CREATE INDEX ON public.t1 USING btree (id);

6、查看创建完索引后的执行计划

test=# explain analyze Select * from t1 where id = 100;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Index Scan using t1_id_idx on t1  (cost=0.42..8.44 rows=1 width=37) (actual time=0.021..0.022 rows=1 loops=1)
   Index Cond: (id = 100)
 Planning Time: 0.068 ms
 Execution Time: 0.049 ms
(4 行记录)

7、再次查看索引建议

test=# select * from index_recommendation_by_qual;
nspname | relid | attnames | possible_types | execution_count | queryid | query | ddl_index | old_cost | new_cost | benefit
---------+-------+----------+----------------+-----------------+---------+-------+-----------+----------+----------+---------
(0 行记录)