SQL 优化之谓词下推

  所谓谓词下推，就是将尽可能多的判断更贴近数据源，以使查询时能跳过无关的数据。用在 SQL 优化上来说，就是先过滤再做聚合等操作。

要理解谓词下推，应该从两个方面来看，即谓词和下推两部分。

什么是谓词

predicate push down 翻译为谓词下推，这个翻译很准确，明确的告诉了我们这个操作是一个什么动作，但是为人诟病的是，什么是谓词，结合起来是什么意思，就比较难以理解。

predicate push down 又可以叫做 Filter Push down，这个叫法准确的描述了动作，但没有精准定位什么能被称之为 Filter。全局来看，还是 predicate push down 较为准确。

predicate（谓词）即条件表达式，在SQL中，谓词就是返回 boolean 值即 True 和 False 的函数，或是隐式转换为 bool 的函数。SQL 中的谓词主要有 LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS 其结果为布尔值，即 True 或 False。

谓词的使用场景：在 SELECT 语句的 WHERE 子句或 HAVING 子句中，确定哪些行与特定查询相关。

注：并非所有谓词都可以在 HAVING 子句中使用。

什么是谓词下推

谓词下推（Predicate Pushdown）是指将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。简而言之，就是在合适的场景下，优先执行过滤条件。

Hive 中的谓词下推

Hive 中的 Predicate Pushdown，简称谓词下推，主要思想是把过滤条件下推到 map 端，提前执行过滤，以减少 map 端到 reduce 端传输的数据量，提升整体性能。简言之，就 是先过滤 再做聚合等操作。

总结：

• 谓词下推：在存储层即过滤了大量大表无效数据，减少扫描无效数据；所谓下推，即谓词过滤在 map 端执行，所谓不下推，即谓词过滤在 reduce 端执行
• Inner Join 时，谓词放任意位置都会下推
• Left Outer Join 时，左表的谓词应该写在 where 后
• Right Outer Join 时，左表的谓词应该写在 join 后

谓词下推导致结果不一致

SQL1：20672 和 9721

select
    count(distinct t1.role_id) as new_role_cnt,
    count(distinct t2.role_id) as pay_role_cnt
from (
    select
        role_id, part_date
    from ods_game_dev.ods_role_create
    where part_date = '2020-01-01'
) t1
left outer join ods_game.dev.ods_role_recharge t2
    on  t1.role_id = t2.rile_id 
    and t2.part_date = '2020-01-01';

T1 表查询先过滤，T2 表条件写在 on 中，满足谓词下推。各自进行了条件过滤后，再进行 join 。所以 count 的时候，我们看到的是 count 各自过滤条件后的数据。

SQL2：9721 和 9721

select
    count(distinct t1.role_id) as new_role_cnt,
    count(distinct t2.role_id) as pay_role_cnt
from ods_game.dev.ods_role_create t1
left outer join ods_game.dev.ods_role_recharge t2
    on t1.role_id = t2.rile_id 
where t1.part_date = '2020-01-01' 
    and t2.part_date = '2020-01-01';

T1 表在 where 里，满足谓词下推。T2 表不满足谓词下推。所以 T2 表的条件是在 join 之后过滤，这就导致在 count 的时候，都经历了 T2 表的条件。所以数据一致。

SQL3：20672 和 9721

select
    count(distinct t1.role_id) as new_role_cnt,
    count(distinct t2.role_id) as pay_role_cnt
from ods_game.dev.ods_role_create t1
left outer join ods_game.dev.ods_role_recharge t2
    on t1.role_id = t2.rile_id 
    and t2.part_date = '2020-01-01'
where t1.part_date = '2020-01-01';

左表 T1 在 where 满足谓词下推，右表 T2 在 on 满足谓词下推。所以都是先进行了数据的过滤，再进行 join 操作。和 SQL1 一样，count 各自过滤条件后的数据。

SQL4：184125 和 9721

select
    count(distinct t1.role_id) as new_role_cnt,
    count(distinct t2.role_id) as pay_role_cnt
from ods_game.dev.ods_role_create t1
left outer join ods_game.dev.ods_role_recharge t2
    on t1.role_id = t2.rile_id 
    and t2.part_date = '2020-01-01' 
    and t1.part_date = '2020-01-01';

左表 T1 不满足谓词下推，右表 T2 满足过滤条件。针对左表 T1 的过滤条件必须放在 where 上，放在 on 上的效果是不可预期的，不生效。右表 T2 条件在 on 里满足谓词下推，生效。所以 T1 表是全量数据， T2 表是过滤后的数据。

场景示例

在实际数仓开发中，我们经常会遇到多表关联，这个时候就会涉及到 where 与 on 的使用。

• 写法一：

SQL> select * from a left outer join b on a.id = b.id where a.id = '1';

• 写法二：

SQL> select * from a left outer join b on a.id = b.id and a.id = '1';

• 写法三：

SQL> select * from (select * from a where id = '1') t left outer join b on t.id = b.id;

上述三种写法中，写法一与写法三都为高效写法，Hive 只会取指定的数据进行 join，写法二则效率较低，Hive 先会查出所有数据进行 Join，然后再去过滤指定的数据

从执行计划可以得出，写法一和写法三的数据在一开始扫描时候就已经过滤了。而写法二会拿所有的数据先进行查询 Join，然后再进行过滤。

这可以通过 explain 执行计划证明：

• 写法一和写法三：

• 写法二：

从执行计划可以得出，写法一和写法三的数据在一开始扫描时候就已经过滤了。而写法二会拿所有的数据先进行查询 Join，然后再进行过滤。

这种将过滤表达式提前执行的过程我们称为谓词下推。

谓词下推规则

Hive 官网谓词下推介绍：https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior

• 保留行表(Preserved Row table)： 外连接中的表必须返回所有行。对于左外连接，左表是保留行表；对于右外连接，右表是保留行表；对于全外连接，两个表都是保留行表
• 空值提供表(Null Supplying table)： 该表的不匹配行中的列使用空值填充。对于左外连接，左表数据全部返回，左表在右表中无法匹配的数据的列用 NULL 表示；对于右外连接，刚好相反；对于全外连接，左表和右表都会用 NULL 来填充无法匹配的数据
• JOIN 中的谓词(During Join predicate)： join on 子句中的谓词。例如，在 R1 join R2 on R1.x = 5 中，谓词 R1.x = 5 是 JOIN 中的谓词
• JOIN 后的谓词(After Join predicate)： where 子句中的谓词。例如，在 R1 join R2 on R1.m = R2.n where R1.x = 5 中，谓词 R1.x = 5 是 JOIN 后的谓词

总结来说就是：

• 保留行表的谓词写在 join 中不能下推，需要用 where
• 空值提供表的谓词写在 join 后不能下推，需要用 on
• 在 inner join 关联情况下，过滤条件无论在 join 中还是 where 中谓词下推都生效
• 在 full join 关联情况下，过滤条件无论在 join 中还是 where 中谓词下推都不生效

参考资料

Hive谓词下推

什么是谓词下推，看这一篇就够了

SQL优化之谓词下推

原创文章，转载请注明出处：http://www.opcoder.cn/article/89/