背景

嵌套子查询的性能问题一直是数据库优化的核心问题，原因也很明显，由于子查询的嵌套导致子查询被重复执行多次，尤其当主查询的数据量比较大时，由于子查询的嵌套迭代执行导致的性能问题就越发明显，因此优化器在优化过程中会根据子查询的特点，分别进行针对性的优化改写，提升查询的性能。下面我们就PolarDB对嵌套子查询的优化做一些详细的分析，让用户更深入理解PolarDB在性能优化方面的成就，让用户使用的更放心更安心。

包含聚集函数的子查询的优化方法

子查询优化的方法有很多，比如单值子查询的优化，SEMI-JOIN优化等，本文主要介绍的针对包含聚集函数的相关子查询的优化方法。下面是一个示例:

SELECT
  		SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
        lineitem,
        part
WHERE
        p_partkey = l_partkey
        AND p_brand = 'Brand#55'
        AND p_container = 'JUMBO CASE'
        AND l_quantity < (
                SELECT
                       0.2 * AVG(l_quantity)
                FROM
                        lineitem
                WHERE
                        l_partkey = p_partkey
        )
LIMIT 1;

这是一个TPCH测试中一条件查询语句Query 17, 查询中包含了一个相关子查询，并且子查询中有聚集函数AVG，正常执行时，当主查询中lineitem表和part执行JOIN后产生的每一行数据都要将子查询执行一遍，然后才能做WHERE条件的过滤，得到最终的结果。显然，当主查询的行数非常大时，执行子查询的次数也自然很多，查询效率自然不高。

对此PolarDB提供了两种更好的优化方法，一种是首先对子查询按关联键进行分组执行聚集，将子查询转为一个Derived Table，然后将其提到主查询中，与主查询中的lineitem，part直接JOIN，避免子查询的无数次重复执行，实现性能优化的目的。这种优化方法可以简称为group by + Derived Table变换，下面我们通过改写的SQL来示意它的变换过程。

SELECT
        SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
        lineitem,
        part,
        (SELECT
                l_partkey as dt_partkey, 0.2 * AVG(l_quantity) as avg_quantity
         FROM
                lineitem
         GROUP BY l_partkey
        ) dt

WHERE
        p_partkey = l_partkey
        AND p_brand = 'Brand#55'
        AND p_container = 'JUMBO CASE'
        AND p_partkey = dt.dt_partkey
        AND l_quantity < dt.avg_quantity
LIMIT 1;

通过提前将lineitem按l_partkey分组，计算出每组l_partkey的平均值，然后和外层查询中的表进行JOIN，就可以直接得到对应的平均值，然后执行Where条件过滤即可得到最终结果。

除此之外，还有另外一种变换方法，是通过window function计算每组l_partkey的值，然后再使用Where条件过滤，得到最终的结果。这种优化方法可以简单为Window function + Derived Table变换，下面我们通过改写的SQL来示意它的变换过程。

SELECT
       SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
      (SELECT
              l_quantity,
              l_extendedprice,
              (0.2 * AVG(l_quantity) OVER (PARTITION BY l_partkey)) as avg_quantity
        FROM
              lineitem,
              part
        WHERE
              p_partkey = l_partkey
              AND p_brand = 'Brand#55'
              AND p_container = 'JUMBO CASE'
       ) win
WHERE
        l_quantity < avg_quantity
LIMIT 1;

通过Window function将每组l_partkey的平均值计算出来，与group by + Derived Table变换不同的是，无需再多加一次JOIN，就可以直接过滤得到最终结果。