vb：SQL优化-查询重写规则

1.基本规则

尽量早下推
选择合适的连接
子查询转连接

参考：

https://docs.vastdata.com,cn/zh CN/VastbaseG100/V2.2.15/1/3e9f8b619dd94363b34fd5b58b14d2ab

1.1 相关概念

子查询与子链接
沿用了pg的概念，在from后面叫子查询，在其他关键字后面都叫子链接；其他系数据库产品没有区分这种概念。

子链接存在于什么地方？

  • select后面（目标列，target list）
  • where后面
  • having后面

我们能调的大致就如上三个方面。在from后面的子查询人工干预的较少，vb产品本身已经做了较多优化。

sublink可以分为以下几类：

  • exist sublink:对应EXIST、NOT EXIST语句。
  • any_sublink:对应op ANY(select..)语句，其中OP可以是“<”、“>”、“="操作符，IN/NOT IN (select..)也属于这一类。
  • all sublink:对应op ALL(select..)语句，其中OP可以是“<”、“>”、“=”操作符。
  • rowcompare sublink:对应record op(select ..)语句，行比较。
  • expr_sublink:对应(SELECT with single targetlist item ...)语句，仅仅选择一列。
  • array sublink:对应ARRAY(select...)语句。
  • cte sublink:对应with query(...)语句。

相关子链接、非相关子链接:

  • select * from t1 where t1.i1=(select t2.i1 from t2 where t2.i2 = 1 limit 1);
  • select* from t1 where t1.i1 = (select t2.i1 from t2 where t2.i2 = t1.i2 limit 1);

In-sublink(非相关子链接，非易变函数)例子：

Exist-sublink包含相关条件: where子句中必须包含上一层查询的表中的列，子查询的其它部分不能含有上层查询表中的列。
其它限制如下:
子查询必须含有from子句。
子查询不能含有with子句。
子查询不能含有聚集函数。
子查询里不能包含集合操作、排序、limit、windowagg、having操作。
子查询不能包含易变函数。

例：


2.常用的调优规则

2.1 lazyagg

（惰性聚合）

核心思想:
通过推迟某些聚合操作的执行，直到进行了过滤或连接等操作之后，以此来减少计算量和提高查询性能

场景:
基表数据量大、distinct值较多
子查询有groupby，关联外表后还有group by
关联字段是内层的group by字段（或之一）

参考:

https://blog.csdn.net/devcloud/article/details/125457358

例：

2.2 magicset

核心思想:
将主查询中的where过滤条件下推到子查询中，实际中是把表下推，多一次关联。
先针对子查询的关联字段进行关联，分组聚集，再和主查询进行关联，减少相关子链接的重复扫描，提升查询效率。

场景:
子查询/子链接(=,>,<)
关联聚合子查询，子查询数据量很大，条件下推大大减少数据量。
过滤条件是来自于聚合子查询与外表连接的那个表

默认的rewrite_rule就是magicset

postgres=# show rewrite_rule ;
 rewrite_rule
--------------
 magicset
(1 row)

参考：

https://blog.csdn.net/robinson1988/article/details/139048758

例：


2.3 intargetlist

核心思想:提升targetlist中的子查询

场景:子查询在目标列中，有聚集函数

例：

例：

2.4 uniquecheck

核心思想:提升无agg的子链接语句，在执行时检查返回行数是否为1行。

场景:无agg的相关子链接（非相关子查询不适用），每个子链接只有一行输出(Unique check required)

例：

2.5 intargetlist, uniquecheck

场景:
targetlist中的无agg的子查询

例：

2.6 enable_sublink_pullup_enhanced

核心思想：
使用增强后的sublink查询重写规则，包括where、having 子句的子链接
提升场景:where,having子句中，子查询含聚集函数

例：

例：

2.7 qrw_inlist2join_optmode

核心思想:
控制使用inlist-to-join对SQL进行改写

例·：

set qrw_inlist2join_optmode='rule base'
SELECT* FROM rewrite_rule_hint_t5 WHERE slot = '5' AND (name) lN
(SELECT name FROM rewrite_rule_hint_t5 WHERE sIot='5' AND cid IN (5,100,1001,1002,103,1004,1005,1006,1007,2000,4000,10781986,10880002) LIMIT 50);

其中rule base是强制转连接，此外还有另外两种选项：cost base、超阈值转连接

2.8 disable_pullup_expr_sublink

核心思想:禁止表达式的子链接提升

例：

2.9 no_expand

核心思想:不提升子查询
场景:
选择率较低且可以使用索引过滤访问页面的子查询，嵌套执行不会导致性能下降过多，而提升之后扩大了查询路径的搜索范围，可能导致性能变差。

例：

实际使用中no_expand因包含disable_pullup_expr_sublink的功能，所以使用的情况比disable_pullup_expr_sublink要多。

2.10 predpush、predpushnormal、predpushforce

核心思想:
predpush可以将谓词下推到子查询块中

场景:
在父查询块中的数据量较小或子查询(group by/distinct)中可以利用索引的场景下能够提升性能，连接条件下推。

predpush的rewrite_rule规则有3个，分别是:

  • predpushnormal:尝试下推谓词到子查询中。(常用，能下去就下去)
  • predpushforce:尝试下推谓词到子查询中，尽量利用索引扫描
  • predpush:利用代价在predpushnormal和predpushforce中选择一个最优的分布式计划，但是会增加优化时间。

此外还有
predpush_same_level:同层下推，前提:set rewrite rule='predpushforce';

2.11 from_collapse_limit, join_collapse_limit

核心思想:根据生成的FROM列表的项数来判断优化器是否将把子查询合并到上层查询，如果FROM列表项个数小于等于该参数值，优化器会将子查询合并到上层查询。

原理:
根据得出的列表项数来判断优化器是否执行把除FULL JOINS之外的JOIN构造重写到FROM列表中。

SELECT * FROM table1 t1 JOlN table2 t2 ON t1.id =t2.id
JOIN (SELECT * FROM table3 t3 jOlN table4 t4 ON t3.id = t4.id) sub ON t2.id = sub.id;
  1. 解析FROM列表FROM列表包含三个部分:
    table1 t1 table2 t2 子査询 (SELECT* FROM table3 t3 JOlN table4 t4 ON t3.id = t4.id) sub
  2. 递归处理每个FROM列表项优化器会递归地处理每个FROM列表项:
    对于table1 t1和table2 t2，它们是简单的表引用，直接添加到JOIN 列表中。
    对于子査询sub，优化器会进一步递归处理其内部的FROM 列表(table3 t3 和 table4 t4)
  3. 合并子问题的结果如果子查询的结果(sub)的长度不超过fromcollapselimit，则将其合并到当前的JOIN 列表中。
    否则，将子查询的结果作为一个整体添加到当前的JOIN 列表中。

假设from collapse limit=8,则最终生成的JOIN 列表为:[table1 t1,table2 t2,table3 t3, table4 t4]
如果from collapse limit =3,则子查询的结果不会被合并，最终生成的JOIN列表为:[table1t1,table2 t2,(table3 t3 JOIN table4 t4) sub]

场景:
表连接的数据比较多，超过8个。

2.12 try_vector_engine_strategy

核心思想:
使用向量化引擎

向量化执行引擎具有以下优点:

  • 一次一个batch，读取更多数据，节省10。
  • batch中记录较多，CPU cache命中率提升。
  • Pipeline模式执行，所数调用次数少。
  • 一次处理一批数据，效率高。

场景:
分析类复杂查询:如果查询语句中包含表达式计算、多表join、聚集等操作时，通过转换为向量化执行能够获得性能提升。

2.13 rownumenhance/enable_constraint_optimization

可以将rownum<const或rownum<=const 的场景转为 limit 常量算子
消减不必要的条件

例：

2.14 总结

3.各版本新增规则

3.1 【2.2.15版本】新增

  • 2.2.15.3
    enable_any_sublink_pullup_enhanced, 启用此选项后计划器会尝试将相关子连接进行提升，生成更好的执行计划。(即提升any)

  • 2.2.15.4
    rewrite_rule='predpushnormal',query dop=1支持谓词推入。

  • 2.2.15.7

enable remove unused outputs
功能描述:用于减少子查询中不必要的目标列投影。对子查询的目标列进行优化，如果没有被上层引用，满足条件的情况下可以不进行投影，以提升效率。

3.2 【3.0.8版本】新增

    1. enablejeenhanced:
      在查询语句中，支持对左外连接进行优化，消除不需要的右表，提升查询效率
SELECT DISTINCT test1_1200501.* FROM test1_1200501 LEFT JOlN test2_1200501 ON test1_1200501.id=test2_1200501.id;
    1. rewrite rule增加self_join_elimination,magicset
      自连接消除是指对多次引用同一表的连接查询，进行对重复扫描的消除优化
SELECT a.id, b.*FROM table_1238182 a JOlN table_1238182 b ON a.id=b.id;
    1. rewrite rule增加fkey join elimination
      当一个表(子表)通过外键引用另一个表(主表)的主键时，如果子表中的每一行都与主表中的唯一一行相匹配，那么这种一对一的主外键连接将被优化器消除。
    1. rewrite_rule增加order_by_elimination:
      消除多余ORDER BY。支持场景如下:
      查询中带有连接操作(LEFT JOIN、INNER JOIN等)，同时不含有rownum表达式，可以消除连接两个子查询中顶层
      的 ORDER BY 子句。
      查询中存在集合操作(UNION ALL等)，同时不包含rownum表达式，可以消除集合两边中顶层的ORDER BY子句。
      ORDERBY所在该层查询中，不存在LIMIT子句，不存在DISTINCT ON子句。
      外层查询目标列中存在聚集函数，内层查询中的ORDER BY可以进行消除。
      如存在多层嵌套，更深层的子查询中的ORDER BY也可以进行消除。
      子连接中(IN/EXISTS)的ORDER BY可以进行消除。

4.规则限制

4.1 限制1

4.2 限制2

参考

©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容