在 Babelfish 中优化关联子查询
关联子查询引用外部查询中的表列。对于外部查询返回的每一行，关联子查询都会被评估一次。在以下示例中，子查询引用表 t1 中的一列。此表未包含在子查询的 FROM 子句中，但在外部查询的 FROM 子句中引用了该表。如果表 t1 有 100 万行，则需要对子查询评估 100 万次。
SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);
使用子查询转换提高 Babelfish 查询性能
Babelfish 可以通过将关联子查询转换为等效的外部联接来加速这些子查询。此优化适用于以下两种类型的关联子查询：
-
返回单个聚合值并出现在 SELECT 列表中的子查询。有关更多信息，请参阅 Microsoft Transact-SQL 文档中的 SELECT clause
。 SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1; -
返回单个聚合值并出现在 WHERE 子句中的子查询。
SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;
在子查询中启用转换
要支持将关联子查询转换为等效的外部联接，请将 apg_enable_correlated_scalar_transform 参数设置为 ON。此参数在 Babelfish 4.2.0 及更高版本中提供。此参数的默认值为 OFF。
您可以修改集群或实例参数组来设置参数。要了解更多信息，请参阅 Amazon Aurora 的参数组。
或者，可以通过调用函数 set_config 来仅为当前会话配置此设置。例如，运行以下命令来在 Babelfish 中开启子查询缓存。要了解更多信息，请参阅 Configuration Settings Functions
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
验证转换
使用 EXPLAIN 命令来验证关联子查询是否已在查询计划中转换为外部联接。有关更多信息，请参阅 使用解释计划提高 Babelfish 查询性能。
启用转换后，适用的关联子查询部分将转换为外部联接。例如：
1>select set_config('apg_enable_correlated_scalar_transform', 'true', false);2>GO1>set BABELFISH_STATISTICS PROFILE on2>GO1>select customer_name, ( select max(o.cost) from correlated_orders o2>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount3>from correlated_customers c order by customer_name ;4>GOQUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Text: select customer_name, ( select max(o.cost) from correlated_orders o where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10 ) AS max_order_amount from correlated_customers c order by customer_name Sort (cost=88.23..90.18 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Hash Left Join (cost=30.90..50.76 rows=780 width=40) Hash Cond: (c.customer_id = o.customer_id) -> Seq Scan on correlated_customers c (cost=0.00..17.80 rows=780 width=36) -> Hash (cost=28.73..28.73 rows=174 width=12) -> HashAggregate (cost=26.99..28.73 rows=174 width=12) Group Key: o.customer_id -> Seq Scan on correlated_orders o (cost=0.00..25.30 rows=338 width=12) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10)) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
将 GUC 参数设置为 OFF 时，不会转换相同的查询。计划将不会有外部联接，而是有子计划。
1>select set_config('apg_enable_correlated_scalar_transform', 'false', false);2>GO1>select customer_name, ( select max(o.cost)2>from correlated_orders o3>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount4>from correlated_customers c order by customer_name ;5>GOQUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Sort (cost=21789.97..21791.92 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Seq Scan on correlated_customers c (cost=0.00..21752.50 rows=780 width=40) SubPlan 1 -> Aggregate (cost=27.86..27.87 rows=1 width=8) -> Seq Scan on correlated_orders o (cost=0.00..27.85 rows=2 width=8) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))
限制
-
子查询必须位于 select_list 中或 where 子句的其中一个条件中，否则它不会转换。
-
子查询必须返回一个聚合函数。不支持用户定义的聚合函数进行转换。
-
返回表达式不是简单聚合函数的子查询不会被转换。
-
子查询 WHERE 子句中的关联条件应该是简单的列引用。否则，不会转换此子查询。
子查询中的关联条件，其中子句必须是普通的相等谓词。
包含 TOP 子句的关联子查询无法转换。
-
子查询不能包含 HAVING 或 GROUP BY 子句。
-
子查询中的 where 子句可能包含一个或多个使用 AND 组合的谓词。如果 WHERE 子句包含 OR 子句，则无法对子查询进行转换。