什麼是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研究院