导读

数据库的查询优化器是整个系统的"大脑"，一条 SQL 语句执行是否高效在不同的优化决策下可能会产生几个数量级的性能差异，因此优化器也是数据库系统中最为核心的组件和竞争力之一。阿里云瑶池旗下的云原生数据库 PolarDB MySQL 版作为领先的云原生数据库，希望能够应对广泛用户场景、承接各类用户负载，助力企业数据业务持续在线、数据价值不断放大，因此对优化器能力的打磨是必须要做的工作之一。本系列将从 PolarDB for MySQL 的查询变换能力开始，介绍我们在这个优化器方向上逐步积累的一些工作。

*本篇为「PolarDB 优化器查询变换」系列第五篇，前四篇内容分别解读了：

1. Join消除

2. 窗口函数

3. Join条件下推

4. IN-List变换

背景

谓词下推是一种优化技术，它可以将查询中的谓词（条件）尽可能地下推到数据源中进行处理，以减少数据的读取和处理量，提高查询效率。具体来说，谓词下推可以在查询过程中尽早地过滤掉不符合条件的数据，减少数据传输和处理，从而大幅提升查询性能。

数据库为什么要做谓词下推呢？在大型数据库中，数据量一般都会很大，查询操作十分频繁，如果不进行谓词下推，查询操作就会非常耗时和低效。因此，谓词条件下推是数据库优化器查询变换的重要规则之一，能够减少后续查询计算的代价，大幅提升查询性能。

*上述部分内容参考自 ChatGPT

谓词条件下推到 derived table

基于以上目的，MySQL 8.0.22 及之后的版本支持将谓词条件下推到派生表(derived table)，当派生表不能合并到外部查询时（例如，派生表使用聚合），将外部 WHERE 条件下推到派生表中应该会减少需要处理的行数，从而加快查询的执行速度。示例如下：

SELECT i, j     FROM (        SELECT i                   FROM t1             GROUP BY i         ) dt, t2     WHERE i > 2         AND j < 3;
====变换后====>
SELECT i, jFROM (        SELECT i        FROM t1        WHERE i > 2        GROUP BY i) dtWHERE j < 3;

MySQL 社区版本的实现原理是 prepare 阶段，在所有变换完成之后，由外向内，层层递归，判断当前 block 中 WHERE Clause 的条件是否可以下推或者部分下推到任一个物化派生表。具体代码逻辑如下：

-> SELECT_LEX::prepare         -> push_conditions_to_derived_tables()               -> 循环每一个物化表处理 WHERE condition            -> make_cond_for_derived() // 生成可以下推到派生表的条件               ->extract_cond_for_table() //提取和当前只和派生表相关的条件               - push_past_window_functions（）//生成推到派生表HAVING Clause的条件               - push_past_group_by（）//生成推到派生表 WHERER Clause的条件               - make_remainder_cond() //生成下推之后剩余的条件       -> 自顶向下，产生的下推条件可以被下推到嵌套在派生表内部派生表            ->push_conditions_to_derived_tables() //递归

MySQL 8.0.29 及以后的版本支持派生表条件下推优化可以用于 UNION 查询，虽然放开了对物化表是 UNION 的限制，但增加了以下限制：

• 如果 UNION 中的任何物化派生表是递归公共表表达式；

• 不能将包含不确定性表达式的谓词条件下推到派生表。

PolarDB 版本的谓词条件下推

谓词条件下推到 derived table 增强版

基于用户复杂的查询场景，我们发现数据库需要更加强大的下推能力来加速用户查询。因此 PolarDB 基于 MySQL 8.0.2，对原有的谓词条件下推实现进行了较大改造，实现了更加完善和强大的下推能力，主要包括：

包含等值条件传递的谓词条件下推

MySQL 社区版本在检查谓词条件下推时并没有考虑条件等值传递的影响。实际上，如果某一列满足谓词条件下推的判断，其等价列也应该满足条件下推的判断，进而下推更多的条件，更大可能的减少中间数据和后续计算代价。同时，为了尽可能多的考虑等价条件的影响，PolarDB 将保留当前层的 WHERE 条件，而不去移除已经下推的条件。相比较于 filter 的代价，更多的下推的可能性将带来更大性能收益。例如，考虑如下的查询场景：

SELECT *       FROM t1, (                  SELECT x                  FROM t2                  GROUP BY x               ) d_tab, t2       WHERE t1.a = d_tab.x               AND t1.a > 6;
       ====变换后====>
      SELECT *        FROM t1, (                   SELECT x                   FROM t2                   WHERE x > 6                   GROUP BY x                ) d_tab        WHERE t1.a = d_tab.x                AND t1.a > 6;

虽然对于 t1.a > 6 条件，t1.a 列并不依赖于派生表 d_tab，但由于 t1.a = d_tab.x 的等值条件，我们可以推导出 t1.a > 6 条件是可以下推到 derived table 的，且按照映射关系转换为条件 x > 6 对物化表 d_tab 进行数据过滤，减少数据量的同时也减少了物化代价后后续数据的计算代价。当用户场景中的数据量大且条件过滤性好时，对于整个查询的性能提升十分明显。

谓词条件下推到派生表是 UNION 的情况

MySQL 社区版本起初由于实现限制，并没有实现条件下推到派生表是 UNION 的情况，PolarDB 版本解除了这一限制。对于 derived table 是 UNION 的情况，根据 UNION 中并列的每个子 query block 的情况，依次将可下推的条件下推到部分符合的 query block 中。

SELECT f1    FROM (      SELECT (          SELECT f1          FROM t1          LIMIT 1        ) AS f1      FROM t1      UNION      SELECT f2      FROM t2    ) dt    WHERE f1 = 1;
    ====变换后====>
    SELECT f1    FROM (      SELECT (          SELECT f1          FROM t1          LIMIT 1        ) AS f1      FROM t1      UNION      SELECT f2      FROM t2      WHERE f2 = 1    ) dt    WHERE f1 = 1

在上面的 SQL 中，对于 derived table 是两个 select 的 UNION，分别判断 WHERE 条件 f1 = 1 是否可以下推。对于 SELECT#1 有 LIMIT，条件下推之后将影响结果的行数，因此不可以下推到 SELECT#1；而检查 SELECT#2 则满足下推的检查，因此最终 f1 = 1 可以下推到 SELECT#2 的 WHERER Clause 上并映射为 f2 = 1。

MySQL 8.0.29 及以后的版本支持的“下推到 UNION 时优化”是：若 UNION 的某个子 SELECT 不支持下推，则该条件不能下推到该 UNION 的所有子 SELECT。相比较而言，PolarDB 支持下推到部分的 UNION，在保证语义正确前提下，更大限度的支持条件下推。

下推后的条件可进一步基于等价关系级联下推

PolarDB 还增加了对于当前 query block 将符合条件的位于 HACVING Clause 上的条件下推至 WHERER Clause。这样可以在结果进行 group by 操作之前对数据进行过滤，减少后续计算代价，极大提高查询性能。

在谓词条件下推到派生表的过程中，我们仅仅将可以下推的条件应该放到派生表的 HAVING Clause，进而考虑下推到 HAVING Clause 的条件是否可以继续下推到派生表的 WHERE Clause。为了更大限度的在更早时期对数据进行过滤，PolarDB 在谓词条件下推的变换中，增加了检查每个 query block 中所有 HAVING CALUZE 上的条件是否可以下推到 WHRER Clause 的检查。同时，在这个过程中也考虑等值条件传递，衔接条件下推到派生表的逻辑，进而尽可能将条件层层下推到内层 query block。举例如下：

SELECT t1.a, MAX(t1.b)    FROM t1    GROUP BY t1.a    HAVING t1.a > 2    AND MAX(c) > 12;
    ====变换后===>
    SELECT t1.a, MAX(t1.b)    FROM t1    WHERE t1.a > 2    GROUP BY t1.a    HAVING MAX(c) > 12;

为此，PolarDB 在考虑 WHERE 条件下推到 derived table 之前先进行 HAVING 条件是否可以下推到 WHERE 条件，并且自外向内地对每个 query block 依次检查是否可以条件下推，进而将可以下推的条件尽可能下到最内层。示例如下：

SELECT *   FROM (     SELECT f1, f2     FROM t1   ) dt   GROUP BY f1   HAVING f1 < 3   AND f2 > 11   AND MAX(f3) > 12;
   ====变换后===>
   SELECT *   FROM (     SELECT f1, f2     FROM t1     WHERE f1 < 3   ) dt   WHERE f1 < 3   GROUP BY f1   HAVING f2 > 11   AND MAX(f3) > 12;

总结

云原生数据库 PolarDB 建立了完善的谓词条件下推变换逻辑，在下推检查的过程中增加了对等值条件的考虑。同时，为了尽可能多的利用条件之间的传递关系，谓词条件下推到新的 query block 之后，原来的 query block 仍然保留下推下去的条件，以便在后续优化中更多的利用过滤条件。PolarDB 后续会按照论文《Query Optimization by Predicate Move-Around》提出的谓词下推算法演进，进一步增强 PolarDB 的谓词下推能力。