谓词下推：数据库查询优化

谓词下推 （ Predicate Pushdown , sometimes also referred to as Filter Pushdown ）是数据库中常用的一种查询优化技术。它使谓词下推到数据源，在进行笛卡尔积前过滤数据，从而减少传输和处理的数据量。

谓词下推的支持非常广泛，包括关系型数据库、非关系型数据库以及 Hadoop、Flink、Spark 等大数据框架。

• 什么是谓词
• 什么是谓词下推
• 谓词下推的应用
  • 1 MySQL 索引条件下推优化
  • 2 非关系型数据库、Hadoop、Flink等待补充
• 总结
• 参考

什么是谓词

在逻辑学中，谓词（predicate）是表示属性或关系的符号 。根据 Gottlob Frege 的说法，谓词的含义是从对象域到真值“true”和“false”的函数。例如，对于集合 $A = \left\{1, 2, 3\right\}$，可以定义一个谓词 $P(x)$，如果 $x \in A$，则 $P(x) = true$，否则 $P(x) = false$。显然，$P(1) = P(2) = P(3) = true$，而 $P(4) = false$。

在数据库中，谓词就是返回布尔值即 true 或 false 的函数，或是隐式转换为 bool 的函数 。SQL 中的谓词主要有 LKIE、BETWEEN、IS NULL、IS NOT NULL、IN 和 EXISTS，其结果为布尔值，即 true 或 false。

谓词的使用场景：在 SELECT 语句的 WHERE 子句或 HAVING 子句中，确定哪些行与特定查询相关 。要注意的是，并非所有谓词都可以在 HAVING 子句中使用。

什么是谓词下推

谓词下推实际上是一个简单的概念：

• 如果在一端发起查询但在另一端进行大量数据过滤，可能会产生大量网络流量，这会导致传输速度慢且减少处理的数据量。

• 如果可以将查询的某些部分“下推”到数据存储的位置，从而过滤掉大部分数据，则可以大大减少网络流量。比如笛卡尔积前用查询条件过滤数据。

一个经典的谓词下推示例是 Oracle Exadata，如下：

• Oracle 的共享架构在查询大量数据时造成了巨大的 I/O 瓶颈，这使得 Oracle 不适合超大型数据仓库。

• Oracle Exadata 添加了二层 server，每层 server 都与整个存储的一个子集相关联；某些谓词被下推到该层。

• Exadata 的两层 server 之间的 I/O 现在是容忍的，因此 Oracle 现在在高端数据仓库市场中具有竞争力。

谓词下推的应用

1 MySQL 索引条件下推优化

索引条件下推 （ ICP ）是 MySQL 使用索引减少回表查询的优化。

MySQL 架构（来自 MySQL 参考手册）

通常把 MySQL 从上至下分为以下几层：

• MySQL server 层：包括 NoSQL 和 SQL 接口、查询解析器、优化器、缓存和 Buffer 等组件。

• 存储引擎层：各种插件式的表格存储引擎，实现事务、索引等各种存储引擎相关的特性。

• 文件系统层: 读写各种文件与日志。

如果没有 ICP ，存储引擎将遍历索引以查找基表中的行，并将它们返回给 MySQL server，该 server 会评估行的 WHERE 条件。 启用 ICP 后，如果要使用索引中的列来评估部分 WHERE 条件，MySQL server 会将这部分 WHERE 条件下推到存储引擎 。然后，存储引擎使用索引检查推送的索引条件，并且仅当满足此条件时，才会从表中读取行。ICP 可以减少存储引擎回表查询次数，减少 MySQL server 访问存储引擎的次数。

当使用“索引条件下推”时，使用 EXPLAINE 命令将在 Extra 列显示 Using index condition，而不是显示 Using index。

MySQL 默认启动了 ICP ，但可以通过系统变量 optimizer_switch 控制：

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

假设有一个表包含人员及其地址的信息，该表的索引为 (zipcode, lastname, firstname)。如果我们知道一个人 zipcode 的值，我们可以这样查询这个人的姓氏：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

根据最左匹配原则，MySQL 会使用索引扫描符合 zipcode='95054 的人员。但 lastname LIKE '%etrunia%' 不能用来减少被扫描行数，所以如果没有 ICP ，这条查询必然回表查询所有 zipcode='95054' 的行。

启用 ICP 后，MySQL 会在读全表前检查是否匹配 lastname LIKE '%etrunia%' 语句。这就避免了匹配条件 zipcode='95054 但不匹配条件 lastname 的回表查询。

2 非关系型数据库、Hadoop、Flink等待补充

…

总结

总的来说，谓词下推是数据库查询优化的重要手段，它把条件过滤的步骤下推到数据源，减少网络 I/O，减少处理的数据量，从而提高查询性能。

参考

0. The point of predicate pushdown, dbms2.

1. Demystifying Predicate Pushdown: A Guide to Optimized Database Queries, airbyte.

2. MySQL 8.0 Reference Manual: Index Condition Pushdown Optimization, mysql.

3. MySQL索引下推（ICP）简单理解及例子, maoyingdong.

文章链接：https://nohsueh.github.io/post/20231227/ppd_database_query_optimization/
版权声明：本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 nohsueh。