查看映射命令_一一映射

查看映射命令_一一映射有过SQL优化经历的人都知道,对于有些SQL性能问题,可能需要涉及到SQL层面的修改,这不仅麻烦,而且在已上线的系统还存在很大的风险。KINGBASE V8R6 提供了query mapping功能,

KINGBASE Query Mapping 查询映射功能

有过SQL优化经历的人都知道,对于有些SQL性能问题,可能需要涉及到SQL层面的修改,这不仅麻烦,而且在已上线的系统还存在很大的风险。KINGBASE  V8R6 提供了query mapping功能,用户可以通过SQL映射,可以避免直接修改SQL的过程。

以下以举例介绍query mapping 的功能及使用。

一、设置功能开关参数

query mapping 功能开启与否,是通过参数 enable_query_rule 控制的。设置 enable_query_rule = on ,开启query mapping功能。

二、使用例子

1、准备数据

create table t1(id integer,name varchar(9));
insert into t1 select generate_series(1,1000000),"a"||generate_series(1000001,2000000);
create index ind_t1_name on t1(name);
create view v_t1 as select id,upper(name) name from t1;

例子建了个视图,视图对于索引列做了upper操作。upper 操作使得原有的索引无法使用。

2、Mapping前SQL 执行计划分析

假设应用有这么一条SQL : select id from v_t1 where name=$1; 可以看下执行计划:由于做了upper转换,无法使用索引。

test=# explain select id from v_t1 where name="A1234567";
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..22906.00 rows=5000 width=4)
   Filter: (((upper((name)::text))::character varying(8000 char))::text = "A1234567"::text)

如果需要使用索引,只能修改SQL,直接查询表。可以看下执行计划:索引访问

test=# explain select id from t1 where name=lower("A1234567");
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using ind_t1_name on t1  (cost=0.42..8.44 rows=1 width=4)
   Index Cond: ((name)::text = "a1234567"::text)

3、创建Query Mapping

像以上例子,可能修改SQL是不现实的,或是很麻烦的。有没有能不修改SQL,并且能快速解决问题的方法?KINGBASE Query Mapping 就是为实现该功能而量身定做的优化技术。

#创建SQL映射关系
select create_query_rule("qm1","select id from v_t1 where name=$1;","select id from t1 where name=lower($1);", true, "text");
select create_query_rule("qm2","select id from v_t1 where name=$1;","select id from t1 where name=lower($1);", true, "semantics");
#删除映射关系
select drop_query_rule("qm1"); 

在创建mapping时,有两种模式:

  • text在语法词法解析之前做的转化,字符串匹配,执行快。
  • semantics是转化为查询树之后再做转化,会进行语法检查,区分不同schema。对于过程或者匿名块内部的SQL,需要使用这种模式。优化器首先对匿名块作词法语法解析,然后执行的时候发现是查询的查询树,会先进行替换,生成执行计划。

可以查看sys_query_mapping,确认系统创建了哪些query mapping。

4、Mapping后执行计划

查询视图 v_t1 也可以使用到索引。

test=# explain (usingquerymapping, analyze) select id from v_t1 where name="A1234567";
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t1_name on t1  (cost=0.42..8.44 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)
   Index Cond: ((name)::text = "a1234567"::text)
 Planning Time: 0.067 ms
 Execution Time: 0.035 ms
(4 rows)

注意红色部分不能少。

再看下不使用 (usingquerymapping, analyze) 情况下的执行计划:显示的是映射前的执行计划。

test=# explain  select id from v_t1 where name="A1234567";
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..22906.00 rows=5000 width=4)
   Filter: (((upper((name)::text))::character varying(8000 char))::text = "A1234567"::text)
(2 rows)

 

4、如何确认Query Mapping是否升效?

如何确认SQL :select id from v_t1 where name=$1 是否会进行转换了?可以通过查询 sys_stat_statements 确认实际执行的SQL。

test=# select id from v_t1 where name="A1234567";     
   id   
--------
 234567
(1 row)

test=# select id from v_t1 where name="A1234567";
   id   
--------
 234567
(1 row)

test=# select id from v_t1 where name="A1234567";
   id   
--------
 234567
(1 row)

test=# select query,calls from sys_stat_statements where query like "%lower%";
                 query                  | calls 
----------------------------------------+-------
 select id from t1 where name=lower($1) |     3
(1 row)

可以看出SQL映射是生效的。

 三、Query Mapping对于性能影响

Query Mapping 在生成执行计划时,必须先读取sys_query_mapping,不可避免对于性能有一定影响。以下的例子可以看到,query mapping 还是会有20%左右的性能影响。

test=# declare
test-#   v_temp integer;
test-#   v_val text;
test-# begin
test-#   for i in 1000001..2010000 loop
test-#     execute "select id from t1 where name=$1" into v_temp using "a"||i;
test-#   end loop;
test-# end;
test-# /
Time: 102585.759 ms (01:42.586)

test=# declare
test-#   v_temp integer;
test-#   v_val text;
test-# begin
test-#   for i in 1000001..2010000 loop
test-#     execute "select id from v_t1 where name=$1" into v_temp using "A"||i;
test-#   end loop;
test-# end;
test-# /
Time: 128438.435 ms (02:08.438)

四、使用注意

query mapping 要求大写严格一致。

--表名 大小写不一致,无法使用query mapping
test=# explain (usingquerymapping, analyze) select id from V_T1 where name="A1234567";
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..22906.00 rows=5000 width=4) (actual time=99.525..407.516 rows=1 loops=1)
   Filter: (((upper((name)::text))::character varying(8000 char))::text = "A1234567"::text)
   Rows Removed by Filter: 999999
 Planning Time: 0.448 ms
 Execution Time: 407.542 ms
(5 rows)

--空格后面不一致,不影响 query mapping使用 test
=# explain (usingquerymapping, analyze) select id from v_t1 where name="A1234567"; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using ind_t1_name on t1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1) Index Cond: ((name)::text = "a1234567"::text) Planning Time: 0.062 ms Execution Time: 0.036 ms (4 rows)
--FROM 大小写不一致,无法使用query mapping test
=# explain (usingquerymapping, analyze) select id From v_t1 where name="A1234567"; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on t1 (cost=0.00..22906.00 rows=5000 width=4) (actual time=98.008..417.077 rows=1 loops=1) Filter: (((upper((name)::text))::character varying(8000 char))::text = "A1234567"::text) Rows Removed by Filter: 999999 Planning Time: 0.159 ms Execution Time: 417.097 ms (5 rows)

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/6098.html

(0)
上一篇 2023-04-17
下一篇 2023-04-17

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注