化腐朽为神奇的QueryMapping

化腐朽为神奇的QueryMapping

老车除了报废没别的方法？

应用系统就像老车，经过十几二十年的使用，积累了大量里程数据，但是英雄迟暮，反应迟钝，时不时还要病休。但就这样报废，推到重来，如果没有充足的预算，实在是下不了这个决心，不知道该怎么办，让这辆老车焕发第二春。

人们习惯，难以改变

缓慢的启动

已经在驾驶室坐好，发动机已经启动，人也准备妥当，就是老车慢热。已经过去了十分钟，老车还是不能出发。

工作时间到了，人们纷纷打开应用系统，然后就把他切换到后台，开始其他的工作。因为应用软件系统已经工作了十年，累计了海量的数据，而且软件用户增加数倍，很多功能界面打开时间越来越长。

但是人们习惯这样工作模式，不愿改进。

仪表盘的指针总是滞后

今天收到了超速罚单，实在是冤枉。驾驶认真 ，时刻注意指针，避免超速，可是速度指示滞后，让超速摄像头抓拍了。

数据库管理员抱怨，反馈告警信息，不够及时，造成不知道数据库处于崩溃的边缘。

又在高速抛锚

需要高速行驶的时间，越来越长，老车就罢工。

虽然升级的服务器资源，但是低效的查询语句，把新资源消耗在冗余的运算中。

增加一个行李架，不得不降速

任务越来越多，需要加装行李架，但是车速就要下降了。

新的业务不断加入到应用系统，但原有模式的性能，已经不能满足新的需求。

车虽老，使用更频繁

用户多了，根本没有休息的时间，只能一边工作，一边改善维修。

在一个7*24 的应用系统中，没有给数据库管理员，留出足够运维窗口时间，晚上的定时任务，执行时间越来越长，甚至到了第二条的上班时间。

深挖潜能，不断涌现新问题

全表过滤不能满足性能要求

有些查询没有预计数据表增大了，原来可以全表进行条件过滤的查询，已经严重影响工作。

数据多源，使用视图代替原表名

业务类型增加，数据来源复杂，数据表不得不使用视图整合多源的数据表，这就增加查询的硬解析时间。

数据海量增长，使用分区表

分区表的分区过多，带来硬解析的时间超长，这是频繁执行的查询所不能忍受。

外部数据增加，网络交互量延长查询时间

原本性能很好的查询，突然变慢了，发现远程的外部表，突然数据增加很多，而且以后持续增长。

数据库遇到性能瓶颈

运行多年负荷逐渐增加

• 数据量的积累
• 用户数增多
• 功能点使用增多

数据库优化手段

优化SQL语句，但是：

• 复杂多变的过滤条件，无法创建适合的索引

• 商业软件的SQL语句， 已经封装，不能修改

• 应用系统已经验收，项目结束，开发团队已经离场

上述理由，造成在数据库中执行的SQL语句，无法改变。

硬件优化与升级

• 升级CPU，增加并行度；

• 升级内存，增加数据缓存

• 升级存储与网络，使用磁盘IO的高带宽低时延的解决方案

由于不能投入更多的资金，升级服务器的硬件资源，所以无法提升数据库的性能参数。

传统数据库厂商Oracle的解决方案

• Outline

  锁定一个给定SQL语句的执行计划，保持其执行计划稳定，不管数据库环境如何变更。

  Outline将执行计划的hint集合保存在outline的表中（数据字典）。当执行SQL解析时，Oracle会与outline中的SQL比较，如果该SQL有保存的outline，则通过保存的hint集合生成指定执行计划。

• SQL Tuning Advisor
  SQL Tuning Advisor 检查给定的 SQL 语句或一组 SQL 语句，并提供提高效率的建议。它可以提出各种类型的建议，例如创建 SQL Profile（使查询优化器能够为 SQL 语句创建最佳执行计划的信息集合）、重组 SQL 语句和刷新优化器统计信息。SQL Tuning Advisor 还使您能够从过去选取备用执行计划（存储在 AWR 中）并将其与 SQL 语句一起使用，还可以推荐并行度配置文件。

  SQL 优化顾问是一种机制，用于解决与次优执行 SQL 语句相关的问题，获取有关提高 SQL 语句性能的建议，并通过仅执行最佳计划来防止回归。

• SQL Plan Baseline Management

  SQL Plan Baseline Management是一种预防性机制，它使优化程序能够自动管理执行计划，从而确保数据库仅使用已知或已验证的计划。 首要用途是可防止由计划更改引起的性能回归，其次是通过仅验证和接受可提高性能的计划更改，优雅地适应更改，例如新的优化程序统计信息或索引。

  SQL 计划管理的主要组件，有计划捕获、计划选择、计划演变。

上述方案，都是通过稳定SQL语句的执行计划，解决因数据膨胀或需求增加，带来的SQL语句性能问题。

这些创意改造让人超惊喜！

改造工具：QueryMapping

KingbaseES Query Mapping 是一种查询映射功能。有过SQL优化经历的人都知道，对于有些SQL性能问题，可能需要涉及到SQL层面的修改，这不仅麻烦，而且在已上线的系统还存在很大的风险。KingbaseES V8R6 提供了query mapping功能，用户可以通过SQL映射，可以避免直接修改SQL的过程。 可以用高效的查询语句，悄然的替换不堪大用的旧查询语句。

使用优化后的SQL代替原SQL，使用索引代替全表，

读取某日的数据的查询，原SQL使用trunc()函数，截断date类型数据的time部分，造成无法使用索引。由于全表数据量较少，尚未影响查询的性能，但是随着时间，速度慢的问题逐渐暴露出来。

drop table tt66;
create table tt66
as
    select id ,now()-random()*100 c1
from generate_series(1,100000) id;


create index tt66_c1 on tt66(c1);


explain analyze
select * from tt66 where trunc(c1) = to_date('2022-09-01','yyyy-mm-dd');

Seq Scan on tt66  (cost=0.00..2044.00 rows=500 width=12) (actual time=0.014..17.080 rows=1023 loops=1)
  Filter: ((trunc(c1, 'DDD'::text))::timestamp without time zone = '2022-09-01 00:00:00'::timestamp without time zone)
  Rows Removed by Filter: 98977
Planning Time: 0.226 ms
Execution Time: 17.112 ms

--如下的SQL，才能使用索引
explain analyze
select * from tt66 where c1 >= '2022-09-01'::date and c1 < '2022-09-01'::date + 1 ;


--创建SQL映射关系
select drop_query_rule('qm08');
select create_query_rule('qm08','select * from tt66 where trunc(c1)= to_date($1,''yyyy-mm-dd'');','select * from tt66 where c1 >= to_date($1,''yyyy-mm-dd'') and  c1 <to_date($1,''yyyy-mm-dd'') +1;', true, 'text');


--验证查询脚本没有修改，但是执行性能得到提升。
explain (usingquerymapping,analyze)
select * from tt66 where trunc(c1) =  to_date('2022-09-01','yyyy-mm-dd');

Bitmap Heap Scan on tt66  (cost=22.35..593.43 rows=981 width=12) (actual time=0.157..0.608 rows=1023 loops=1)
  Recheck Cond: ((c1 >= '2022-09-01 00:00:00'::timestamp without time zone) AND (c1 < '2022-09-02 00:00:00'::timestamp without time zone))
  Heap Blocks: exact=457
  ->  Bitmap Index Scan on tt66_c1  (cost=0.00..22.10 rows=981 width=0) (actual time=0.129..0.129 rows=1023 loops=1)
        Index Cond: ((c1 >= '2022-09-01 00:00:00'::timestamp without time zone) AND (c1 < '2022-09-02 00:00:00'::timestamp without time zone))
Planning Time: 0.403 ms
Execution Time: 0.642 ms

比较 Oracle 固化执行计划方案 的差异

SQL语句的执行计划不稳定，往往因为统计信息发生变化。当数据量的变化，或者增加索引，需要改变SQL语句，才能改善应用系统的性能。固化执行计划方案虽然可以解决数据库环境发生变化时的性能问题 ，一旦改变了数据结构，则无能为力了。

Query Mapping 可以使用完全不同的SQL语句，替代原SQL语句，生成崭新的查询结果。这样既可以优化查询语句，也能从不同的表和列获得结果。KES的SQL语句，只能在同一个事务中，生成一次执行计划，不需要重复解析语句，但是即使在同一个会话不同的事务中，重复执行同一个SQL语句，也需要解析语句。如果SQL语句非常复杂，解析时间就会很长，甚至超过执行时间。Query Mapping 可以使用函数封装复杂SQL语句，用来替代原SQL语句，从而减少解析时间，提高应用系统的性能。

垂死病中惊坐起

由于时代的局限，历史数据的累计，造成性能良好的老应用系统，逐渐的不堪使用。QueryMapping 功能，可以让这些疲惫的老爷车，枯木逢春，再战二十年。