高级SQL优化之过滤谓词下推

本篇属于高级SQL优化系专题中的一篇，高级SQL优化系列专题介绍PawSQL优化引擎以及Oracle等数据库优化器的查询优化算法原理及优化案例，欢迎大家订阅。

问题定义

首先，过滤条件下推（Filter Predicate Pushdown，简称FPPD）优化不是索引条件下推（Index Condition Pushdown，简称ICP）优化。

ICP是在SQL执行时，把过滤条件下推到在存储引擎层进行数据过滤，减少在服务层的数据处理压力，进而提升SQL执行性能，ICP属于执行优化。

FPPD优化通过尽可能的 “下压” 过滤条件至SQL中的内部查询块，提前过滤掉部分数据, 减少中间结果集的大小，减少后续计算需要处理的数据量，进而提升SQL执行性能，FPPD属于重写优化。

譬如下面的案例中，在外查询有一个过滤条件nation = 100，可以下压到personDT子查询中：

select *
from (select c_nationkey nation, 'C' as type, count(1) num
     from customer
     group by nation
     union
     select s_nationkey nation, 'S', count(1) num
     from supplier
     group by nation) as person
where nation = 100

重写之后的SQL如下:

select *
from (select c_nationkey nation, 'C' as type, count(1) num
     from customer
     where c_nationkey = 100
     group by nation
     union
     select s_nationkey nation, 'S', count(1) num
     from supplier
     where s_nationkey = 100
     group by nation) as person

适用条件

• 过滤条件，而非连接条件

• 过滤条件是单独的或是通过AND连接的查询条件的一部分

• 过滤条件的字段来自FROM子查询（如果是视图，应该被视图定义的SQL替换掉）

• 该子查询没有被查询折叠优化消除掉

• 该子查询本身没有LIMIT子句

• 该子查询本身没有rownum或rank等窗口函数

性能验证

我们使用上面案例中的SQL在MySQL(8.0.20版本)进行性能验证，其执行计划如下：

1. 改写前的执行计划

在c_nationkey和s_nationkey有索引，即使数据库优化器利用索引避免全表扫描；但是由于过滤条件nation = 100没有下推至数据表，其代价和执行时间仍然是比较大的，整体的执行时间在172.6ms。

2. 改写后的执行计划

FPPD将条件nation = 100下推到UNION子查询的两个分支，使得后续的分组和排序运算的输入大大减少，整体的执行时间也降低到0.32ms。

3* 优化过程解析

可以看到，在SQL改写之前，即使在c_nationkey和s_nationkey有索引，数据库优化器利用索引避免了全表扫描；但是由于过滤条件nation = 100没有下推至数据表进行提前过滤，其代价和执行时间仍然是比较大的，整体的执行时间在172.6ms。

而经过FPPD改写之后的执行计划，通过将条件nation = 100下推到UNION子查询的两个分支，可以通过索引快速定位数据，并应用条件过滤，使得后续的分组和排序运算的输入大大减少，整体的执行时间也降低到0.32ms，性能提升了500多倍。

PawSQL对FPPD的支持

• 自动优化：PawSQL针对所有数据库默认开启此优化，而且可以基于重写后的SQL进行索引推荐（如果没有最优索引的话），以上的SQL通过PawSQL的优化结果如下。

• PawSQL提供优化前后的执行计划对比。

• 启用设置：用户可以在自己的默认优化设置或是定义每个优化任务的时候自主启用或禁用该选项。

特殊说明：本次测试的MySQL版本为8.0.20，而在8.0.22及以上版本，MySQL优化器已开始支持过滤谓词下推的重写优化。但是此PawSQL支持此优化仍有意义，

• 还有很多在生产中运行的MySQL数据库版本低于8.0.22，PawSQL的谓词下推重写优化可以帮助这些数据库提升其性能。

• 经过谓词下推重写优化，PawSQL可能基于重写后的SQL推荐最优索引，从而进一步提升查询的性能。

• 经过谓词下推重新优化，可能会触发其他类型的重写优化，譬如SATTC优化。

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，openGauss、Oracle等数据库，提供的SQL优化产品包括

• PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员。

• PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。

• PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以以docker镜像的方式独立安装部署，并通过http/json的接口提供SQL优化服务。

可以通过微信搜索公众号PawSQL获取更多关于查询优化的知识和PawSQL的能力。