原文链接：https://www.gbase.cn/community/post/7252

更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

案例1、lazyagg：延迟聚合运算

目的：消除子查询中的聚合运算。

应用场景：当子查询中有GROUP BY，子查询中的表很大，子查询与外面的表(比较小/过滤完之后数据量少)，进行关联之后还有GROUP BY，就可以开启lazyagg特性，加快SQL性能。

准备工作：建表插入数据语句

-- 2、开启lazyagg优化

EXPLAIN ANALYZE

SELECT /*+ set(rewrite_rule lazyagg) */

c.customer_type,

SUM(order_summary.total_sales) as total_revenue

FROM customers c,

(SELECT

customer_id,

SUM(quantity * unit_price) as total_sales

FROM sales_orders

GROUP BY customer_id) order_summary

WHERE c.customer_id = order_summary.customer_id

AND c.customer_type = VIP

GROUP BY c.customer_type;

使用lazyagg注意点

想要lazyagg查询改写规则生效，必须满足三个条件：

1.子查询中有GROUP BY

2.子查询与外面的表关联之后还有GROUP BY

3. 能确保转换前后语句是等价的

如果子查询与外面的表关联之后没有GROUP BY，lazyagg查询改写规则不会生效，这个时候请使用谓词推入

如果子查询中的GROUP BY和外面的表关联之后GROUP BY无法消除为1个，lazyagg查询改写规则不会生效，请使用谓词推入

如果子查询包含union，请使用谓词推入，lazyagg查询改写规则不会生效，有union all，lazyagg查询改写规则可以生效。

总结：外层查询有过滤条件，过滤后数据量少,内层子查询需要聚合大表数据,最终结果只需要聚合少量数据，延迟子查询的聚合，先让子查询表与外表join，避免了先聚合全部数据再连接的浪费,延迟聚合到连接之后，只聚合需要的数据,减少了中间结果集的大小和内存使用，从而提升性能。

案例2、magicset主查询表中的where条件下推到子查询

目的：将主查询中的表放到where子查询多关联一次。

应用场景：当主查询的表通过谓词过滤后的数据放在子查询中能快速缩小子查询的数据量时。

--2、开启magicset

EXPLAIN ANALYZE

SELECT /*+ set(rewrite_rule magicset) */

o.order_id,

o.amount,

o.order_date,

c.customer_name,

c.customer_type

FROM orders_big o

JOIN customers_small c ON o.customer_id = c.customer_id

WHERE o.order_date = (

SELECT MAX(o2.order_date)

FROM orders_big o2

WHERE o2.customer_id = c.customer_id

AND c.customer_type = VIP

AND c.country = USA;

-- 3、不开启magicset，对子查询使用no_expand优化

EXPLAIN ANALYZE

SELECT /*+ set(rewrite_rule none) */

o.order_id,

o.amount,

o.order_date,

c.customer_name,

c.customer_type

FROM orders_big o

JOIN customers_small c ON o.customer_id = c.customer_id

WHERE o.order_date = (

SELECT /*+ no_expand */ MAX(o2.order_date)

FROM orders_big o2

WHERE o2.customer_id = c.customer_id

AND c.customer_type = VIP

AND c.country = USA;

添加HINT /*+ no_expand */ 禁止子查询展开,从执行计划发现多了limit，原sql没有limit，原因是优化器对sql进行了改写将子查询

SELECT MAX(o2.order_date) FROM orders_big o2 WHERE o2.customer_id = c.customer_id;转换成了SELECT order_date FROM orders_big o2 WHERE o2.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 1;

走了orders_big表上idx_orders_big_date索引。

magicset相当于对原sql进行了等价改写，在子查询中多join了一次customers表，用来减少子查询的数据量。

SELECT o.*, c.customer_name

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

WHERE o.order_date = (

SELECT MAX(o2.order_date)

FROM orders o2

JOIN customers c2 ON o2.customer_id = c2.customer_id -- 多关联一次！

WHERE o2.customer_id = c.customer_id

AND c2.type = VIP -- 下推的条件

AND c2.country = USA -- 下推的条件

AND c.type = VIP

AND c.country = USA;

总结：数据特征，主查询过滤后结果集较大，子查询表数据量大但推入条件选择性高（能过滤掉大部分数据）。计划变化：执行计划从对子查询结果集进行全量扫描和连接（如Hash Join），变为将条件推入子查询内部，提前进行高效过滤（如使用索引）。

案例3、enable_sublink_pullup_enhanced 子查询上拉

目的：将子查询嵌套调整成与外表连接进行连接。

应用场景：主表数据量较大，且连接前缺少高效过滤，子查询结果集较小，或子查询表在关联字段上有高效索引，子查询聚合后的条件对主表的数据刷选比较好。

EXPLAIN ANALYZE

SELECT /*+ set(rewrite_rule enable_sublink_pullup_enhanced) */

pl.*

FROM products_large pl

WHERE pl.price >= (

SELECT max(cs2.min_price_threshold)

FROM categories_small cs2

WHERE cs2.min_price_threshold

);

总结：此规则将为外层每一行执行一次子查询的模式，转变为先执行子查询一次，再与外表整体连接的模式，并非总是有效：如果子查询本身非常庞大且无法有效缩减（例如需要全表扫描聚合），上拉后强制进行 Hash Join 或 Nested Loop 的成本，可能会高于原执行计划中高效的多次索引扫描（SubPlan），反而导致性能下降的典型案例，有兴趣的话，可以把上面的max改成min对比，会发现enable_sublink_pullup_enhanced开启后性能反而会劣化，不能和disable_pullup_expr_sublink一起使用。

案例4、 intargetlist 将标量子查询改写为left join

目的：将 SELECT 目标列中的相关标量子查询（即每行返回一个值的子查询）改写为 LEFT JOIN，其根本目的是避免为外层查询的每一行都重复执行一次子查询（N次循环），从而将执行复杂度从 O(N * M) 降为 O(M)，实现性能的指数级提升。

应用场景：当外层结果集很大（N大），而内层表相对较小或聚合后结果集很小（M小） 时，收益最显著。优化消除了N次循环，只需一次扫描和连接。

如下图所示，如果外层结果集极小（N很小），而内层表极其庞大（M巨大），且关联列有高效索引时，原始执行计划（SubPlan）可能更优。因为 SubPlan 会进行N次快速的索引扫描，而改写后需要一次性全量扫描内层大表进行聚合，成本可能远超N次小范围查询。

总结：标量子查询本质就是每查询一次主表，子查询就要循环一次，当主表数据量为N时，子查询就要执行N次，intargetlist消除了循环遍历，仔细观察执行计划就会发现开启intargetlis时loops从主表的数据量N改变成1，避免了循环引起的性能劣化。缺点把原来两张表都读到内存中进行HashAggregate。比较依赖work_mem内存大小，如果work_mem无法存放大表的所有数据，此时数据会落盘产生临时文件，导致性能下降。

原文链接：https://www.gbase.cn/community/post/7252

更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。返回搜狐，查看更多