KingabseES例程之巧用QueryMapping解决查询硬解析问题
什么是QueryMapping
KingbaseES Query Mapping 是一种查询映射功能。有过SQL优化经历的人都知道，对于有些SQL性能问题，可能需要涉及到SQL层面的修改，这不仅麻烦，而且在已上线的系统还存在很大的风险。KingbaseES V8R6 提供了query mapping功能，用户可以通过SQL映射，可以避免直接修改SQL的过程。
QueryMapping的作用
一个无法使用索引的查询语句，通过QueryMapping，可以解决性能问题。
假设应用有这么一条SQL : select id from v_t1 where upper(name)=$1; 可以看下执行计划：由于做了upper转换，无法使用索引。
kingbase=# explain analyze select id from t1 where upper(name)='A1234567';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..20435.00 rows=5000 width=4) (actual time=44.860..190.009 rows=1 loops=1)
Filter: (upper(name) = 'A1234567'::text)
Rows Removed by Filter: 999999
Planning Time: 0.113 ms
Execution Time: 190.025 ms
(5 行记录)
KingbaseES Query Mapping 实现不修改SQL，并且能快速解决问题。
set enable_query_rule = on
--创建SQL映射关系
select create_query_rule('qm1','select id from t1 where upper(name)=$1;','select id from t1 where name=lower($1);', true, 'text');
explain (usingquerymapping,analyze) select id from t1 where upper(name)='A1234567';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using ind_t1_name on t1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)
Index Cond: (name = 'a1234567'::text)
Planning Time: 0.089 ms
Execution Time: 0.040 ms
(4 行记录)
延伸思考
KingbaseES Query Mapping用一个性能良好的SQL语句，替换原SQL语句，实现性能的提升。如果SQL语句的执行性能良好，但解析性能较慢，是否可以解决SQL语句仅能在事务中实现软解析。
实验场景
一个复杂查询
一个语句复杂的视图，虽然执行性能优良，但解析需要较长的时间，造成查询语句的整体运行时间较长。
kingbase=# explain analyze select id from sch01.v_tun1k where id = 8888;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.28..8293.50 rows=1000 width=4) (actual time=0.088..4.602 rows=1 loops=1)
...
Planning Time: 376.659 ms
Execution Time: 6.621 ms
每次查询返回结果的时间，如下。
kingbase=# select id from sch01.v_tun1k where id = 888; \watch
id
-----
888
(1 行记录)
时间：374.361 ms
2022年09月25日 星期日 15时23分37秒 (每 2s)
id
-----
888
(1 行记录)
时间：377.814 ms
2022年09月25日 星期日 15时23分39秒 (每 2s)
id
-----
888
(1 行记录)
时间：381.633 ms
2022年09月25日 星期日 15时23分41秒 (每 2s)
id
-----
888
(1 行记录)
时间：382.636 ms
2022年09月25日 星期日 15时23分44秒 (每 2s)
id
-----
888
(1 行记录)
时间：378.316 ms
函数实现软解析
使用函数可以在同一会话中，执行6次后，实现软解析。
create or replace function f_tun1k(int)
returns table(id int)
language plsql
as
$$
begin
return query select id from sch01.v_tun1k where id = $1;
end;
$$
;
kingbase=# select *,round(date_part('sec',clock_timestamp()-now())*1000,3)||'ms' ms from f_tun1k(8888) ;\watch
id | ms
------+-----------
8888 | 456.109ms
(1 行记录)
2022年09月25日 星期日 15时12分00秒 (每 2s)
id | ms
------+-----------
8888 | 382.978ms
(1 行记录)
2022年09月25日 星期日 15时12分02秒 (每 2s)
id | ms
------+-----------
8888 | 391.402ms
(1 行记录)
2022年09月25日 星期日 15时12分05秒 (每 2s)
id | ms
------+-----------
8888 | 380.328ms
(1 行记录)
2022年09月25日 星期日 15时12分07秒 (每 2s)
id | ms
------+-----------
8888 | 385.995ms
(1 行记录)
2022年09月25日 星期日 15时12分09秒 (每 2s)
id | ms
------+-----------
8888 | 382.862ms
(1 行记录)
2022年09月25日 星期日 15时12分12秒 (每 2s)
id | ms
------+---------
8888 | 9.208ms
(1 行记录)
2022年09月25日 星期日 15时12分14秒 (每 2s)
id | ms
------+---------
8888 | 7.971ms
(1 行记录)
2022年09月25日 星期日 15时12分16秒 (每 2s)
id | ms
------+---------
8888 | 8.243ms
(1 行记录)
将函数与QueryMapping结合
创建SQL映射关系
kingbase=# select create_query_rule('qm10', 'select id from sch01.v_tun1k where id = $1;', 'select id from f_tun1k($1) ;', true, 'text');
create_query_rule
-------------------
(1 行记录)
函数与QueryMapping结合的效果，在同一会话中，原SQL实现了软解析。
kingbase=# select id from sch01.v_tun1k where id = 888; \watch
id
-----
888
(1 行记录)
时间：10.250 ms
2022年09月25日 星期日 15时21分34秒 (每 2s)
id
-----
888
(1 行记录)
时间：7.958 ms
2022年09月25日 星期日 15时21分36秒 (每 2s)
id
-----
888
(1 行记录)
时间：9.958 ms
2022年09月25日 星期日 15时21分38秒 (每 2s)
id
-----
888
(1 行记录)
时间：10.581 ms
2022年09月25日 星期日 15时21分40秒 (每 2s)
id
-----
888
(1 行记录)
时间：11.401 ms
在语句块中实现软解析
创建SQL映射关系，在语句块中实现QueryMapping，需要semantics键值。
kingbase=# select create_query_rule('qm11', 'select id from sch01.v_tun1k where id = $1;', 'select id from f_tun1k($1) ;', true, 'semantics');
create_query_rule
-------------------
(1 行记录)
在一个会话中，语句块使用组合的软解析效果。
kingbase=# do
kingbase-# $$
kingbase$# declare
kingbase$# v_id int ;
kingbase$# v_ref int;
kingbase$# begin
kingbase$# v_id := (random() * 1000000)::int;
kingbase$# select id into v_ref from sch01.v_tun1k where id = v_id;
kingbase$# raise notice ' ID = % , % ',v_ref , round(date_part('sec', clock_timestamp() - now()) * 1000,3) || 'ms';
kingbase$# end;
kingbase$# $$ \watch
注意: ID = 387169 , 8.635ms
2022年09月25日 星期日 15时33分01秒 (每 2s)
ANONYMOUS BLOCK
注意: ID = 35458 , 10.489ms
2022年09月25日 星期日 15时33分03秒 (每 2s)
ANONYMOUS BLOCK
注意: ID = 39627 , 9.775ms
2022年09月25日 星期日 15时33分05秒 (每 2s)
ANONYMOUS BLOCK
注意: ID = 730817 , 10.634ms
2022年09月25日 星期日 15时33分07秒 (每 2s)
ANONYMOUS BLOCK
注意: ID = 582763 , 9.363ms
2022年09月25日 星期日 15时33分09秒 (每 2s)
ANONYMOUS BLOCK
注意: ID = 148173 , 9.186ms
2022年09月25日 星期日 15时33分11秒 (每 2s)
ANONYMOUS BLOCK
注意: ID = 800521 , 9.766ms
2022年09月25日 星期日 15时33分13秒 (每 2s)
ANONYMOUS BLOCK
总结
目前，QueryMapping功能需要手动创建查询脚本的对照关系，不便于大规模的应用，对于频繁且无法优化的查询，也可以提升执行性能。
KINGBASE研究院
