sql优化技巧之手动推进谓词

在 MySQL 中，"手动推进谓词"通常指通过查询重写或优化技巧，显式地将过滤条件（谓词）提前执行，从而减少数据处理量、提升性能。这与优化器自动的"谓词下推"（Predicate Pushdown）类似，但由开发者主动控制。以下是常见方法及示例：

1. 将谓词移至子查询（提前过滤）

-- 原查询（谓词在外部）
SELECT *
FROM (
  SELECT a.id, a.name, b.order_date
  FROM customers a
  JOIN orders b ON a.id = b.customer_id
) AS combined
WHERE combined.order_date > '2023-01-01';

-- 优化：将谓词移入子查询（手动推进）
SELECT *
FROM (
  SELECT a.id, a.name, b.order_date
  FROM customers a
  JOIN orders b ON a.id = b.customer_id
  WHERE b.order_date > '2023-01-01'  -- 谓词在子查询中提前执行
) AS combined;

效果：先过滤 orders 表的数据，减少后续 JOIN 的计算量。

2. 拆分复杂查询（分步过滤）

-- 原查询（单条复杂语句）
SELECT a.name, SUM(b.amount)
FROM customers a
JOIN orders b ON a.id = b.customer_id
WHERE b.status = 'shipped' AND a.country = 'US'
GROUP BY a.name;

-- 优化：拆分为两步（手动推进谓词）
WITH filtered_orders AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'shipped'  -- 先过滤订单状态
)
SELECT a.name, SUM(b.amount)
FROM customers a
JOIN filtered_orders b ON a.id = b.customer_id
WHERE a.country = 'US'  -- 再过滤用户国家
GROUP BY a.name;

效果：优先过滤两个表的独立条件，减少 JOIN 的数据量。

3. 利用索引提示（强制使用索引）

若优化器未自动选择索引，可手动指定：

SELECT * FROM orders USE INDEX (idx_order_date)  -- 强制使用日期索引 WHERE order_date > '2023-01-01'   AND product_id = 100;

注意：需确保 idx_order_date 索引存在，且仅在测试后使用（可能干扰优化器）。

4. 减少 JOIN 前的数据量

-- 原查询（JOIN 后过滤）
SELECT *
FROM large_table a
JOIN small_table b ON a.id = b.a_id
WHERE a.create_time > '2024-01-01';

-- 优化：先过滤大表（手动推进谓词）
SELECT *
FROM (
  SELECT * 
  FROM large_table 
  WHERE create_time > '2024-01-01'  -- 先过滤大表
) a
JOIN small_table b ON a.id = b.a_id;

关键原则

1. 减少早期数据处理量：尽早过滤无效数据。

2. 利用索引：确保谓词条件能被索引覆盖。

3. 避免全表扫描：通过子查询/临时表缩小中间结果集。

4. 验证执行计划：使用 EXPLAIN 分析优化前后的差异：

何时需要手动推进？

• 优化器未自动下推谓词（如涉及函数、复杂表达式）。

• 多表 JOIN 时中间结果过大。

• 子查询或视图阻碍了自动优化。

注意：现代 MySQL 优化器通常能自动处理谓词下推。手动推进应作为性能调优的最后手段，并需通过 EXPLAIN 和实际测试验证效果。