谓词下推、逻辑优化、列剪枝

1.谓词下推（Predicate Pushdown Optimization，PPO）

1.1 基本概念与例子

谓词下推（Predicate Pushdown） 是数据库查询优化中的一种技术，它将查询中的谓词（通常是 WHERE 子句、JOIN 条件等）尽早地应用到数据源或表扫描的过程中（这个过程即下推），以减少需要处理的数据量，从而提高查询效率。

谓词下推的目标是通过在数据访问的早期阶段就过滤不必要的数据，避免从数据源加载大量不符合条件的行，最终减少 I/O 操作和提高性能。

以下是几个谓词下推的例子：

1. 在表扫描时应用谓词下推

原始查询：

SELECT * FROM employees WHERE salary > 50000;

优化过程：如果表 employees 是一个大表，数据库优化器会将 salary > 50000 这个谓词推到扫描操作之前。优化后的执行计划会首先扫描 employees 表，并且只读取满足 salary > 50000 条件的行。

优化后的执行计划：

SELECT * FROM employees WHERE salary > 50000;

优化器推导出查询不需要加载 salary <= 50000 的数据行，从而减少不必要的计算和 I/O 操作。

2.在子查询时应用谓词下推

假设你有一个带有子查询的查询，优化器可以将子查询中的谓词推向其数据源。

原始查询：

SELECT * 
FROM orders 
WHERE order_id IN (SELECT order_id FROM order_details WHERE quantity > 100);

优化过程：优化器可以将 quantity > 100 这个谓词推送到 order_details 表的扫描操作中，只读取 quantity > 100 的行。这避免了将所有 order_details 行加载到内存中再进行过滤。

优化后的执行计划：

SELECT * 
FROM orders 
WHERE order_id IN (SELECT order_id FROM order_details WHERE quantity > 100);

虽然查询语句没有变化，但优化器将条件 quantity > 100 提前应用于 order_details 表的扫描，减少了从 order_details 表读取不必要的行。

3.在聚合操作中应用谓词下推

在聚合查询中，优化器也可能将条件下推到聚合操作前，以减少数据量。

原始查询：

SELECT t1.a,MAX(t1.b)
FROM t1
GROUP BY t1.a
HAVING (t1.a>2) AND (MAX(t1.c)>12);

优化后的执行计划：

SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a>2)
GROUP BY t1.a
HAVING (MAX(t1.c)>12);

通过提前对字段 a 进行过滤，减少后续聚合的计算量。

4. 在关联查询中应用谓词下推

多个联接条件中的谓词也可以下推，以减少扫描的数据量。

原始查询：

SELECT * 
FROM orders o 
JOIN customers c ON o.customer_id = c.customer_id 
JOIN products p ON o.product_id = p.product_id 
WHERE c.status = 'active' AND p.category = 'Electronics';

优化过程：优化器可以将 c.status = 'active' 下推到 customers 表，p.category = 'Electronics' 下推到 products 表，从而在执行联接之前就过滤数据。

优化后的执行计划：

SELECT * 
FROM (SELECT * FROM orders) o
JOIN (SELECT * FROM customers WHERE status = 'active') c ON o.customer_id = c.customer_id
JOIN (SELECT * FROM products WHERE category = 'Electronics') p ON o.product_id = p.product_id;

这种优化可以减少不符合条件的行被加载到内存中，从而提高查询的效率。

1.2 谓词下推与索引下推的区别

‌**谓词下推（PPO）**‌：适用于复杂的查询重写和优化，特别是在处理大量数据和复杂查询时，能够提前过滤数据，减少中间结果集的大小，从而提高整体查询性能。
‌**索引下推（ICP）**‌：主要用于减少回表次数，适用于需要大量数据过滤的场景。通过在存储引擎层进行数据过滤，可以显著减少服务器层的处理负担和数据传输量。

2.逻辑简化

例 1：

SELECT * FROM A WHERE A.id > 10 AND A.id > 20;
-- 会被优化为
SELECT * FROM A WHERE A.id > 20;

例 2：

SELECT * FROM table1 WHERE a > 5 AND a < 4;

数据库优化器会分析 WHERE 子句中的条件，并发现它们逻辑上是互斥的。a > 5 和 a < 4 不可能同时为真，因此条件永远不成立。这种情况通常会被优化为一个永远不会返回结果的查询：

SELECT * FROM table1 WHERE 1 = 0;
-- 或者被优化为
SELECT * FROM table1 WHERE FALSE;

3.常量传播

常量传播通常指的是：

将表达式中的常量计算结果提前推导出来。
将查询或代码中的常量值替换成实际的常量。

例子 1：将表达式中的常量计算结果提前推导出来。

SELECT * FROM A WHERE A.id > 5 + 15;

优化器会识别出 5 + 15 等于 20，并将查询转换为：

SELECT * FROM A WHERE A.id > 20;

例子 2：将查询或代码中的常量值替换成实际的常量。

SELECT * FROM A WHERE c1 = c2 AND c2 = 3;

会被优化成：

SELECT * FROM A WHERE c1 = 3 AND c2 = 3;

4.列剪枝（裁剪）

列剪枝（Column Pruning）是一种优化技术，它通过在查询执行时避免读取不必要的列来提高查询处理效率。这通常是通过在查询分析阶段识别和评估哪些列是实际需要的来实现的。

在SQL中，列剪枝可以通过查询优化器自动完成，但是在某些情况下，可以手动指定只选择需要的列来实现列剪枝。

例如，假设有一个包含多列的表employees，列包括id, name, salary, department和title。如果你只需要id和name列，你可以这样写你的查询：

SELECT id, name FROM employees;

这个查询只会读取id和name列，而不是整个employees表的所有列，从而实现了列剪枝。