有过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)
京公网安备 11010802041100号