一条查询跑了 8 小时，改写后 519 毫秒？金仓子查询等价谓词传递优化深度解析

引言明明有 WHERE 条件为什么数据库还是全表扫描你有没有遇到过这样的场景写了一条 SQL外层明明带了精确的 WHERE 过滤条件但执行计划一看——子查询内部仍然是全表扫描没有利用到任何过滤条件。更可怕的是如果外层表有 10 万条记录这个子查询就会被执行 10 万次。一位客户的生产环境就遇到了这样的极端情况一条包含嵌套子查询的 SQL由于外层过滤条件无法传递到子查询内部导致对每条外层记录都执行一次完整的子查询扫描和聚合操作。问题链路 外层 WHERE 条件 常量 │ │ ❌ 条件无法传递到子查询 ▼ 子查询每次都要全量扫描 聚合 │ │ ❌ 执行 10 万次 ▼ 总耗时约 8 小时金仓数据库在 V9R4C19 版本中实现了子查询间等价谓词传递优化通过谓词下推和谓词上推两种策略让过滤条件在查询树中自由传递。优化后同样的查询从 8 小时缩短至 519 毫秒——这不是 20%、30% 的改善而是数量级的飞跃。原理剖析谓词的上下传递问题本质查询树中的信息孤岛要理解这个问题需要先了解数据库是如何看待一条 SQL 的。对于包含子查询的语句数据库将其解析为一棵查询树Query Tree。外层查询和子查询各自为树的一个节点┌─────────────────────┐ │ 外层查询节点 │ │ WHERE t1.col 5 │ └──────────┬──────────┘ │ ┌────────┴────────┐ │ 子查询节点 │ │ SELECT ... │ │ FROM t2 ... │ │ 不知道 col5 │ ← 信息孤岛 └─────────────────┘在没有谓词传递优化的数据库中子查询节点就像被关在一个黑盒里——它不知道外层查询已经知道的信息。即使外层的t1.col 5可以直接帮子查询大幅减少扫描范围子查询也只能闷头干自己的活。解决方案谓词下推与谓词上推金仓数据库通过两种策略打通了查询树中节点之间的信息通道。策略一谓词下推Predicate Pushdown核心思想把外层查询的常量过滤条件推进子查询内部 转换前 WHERE outer.col 5 AND EXISTS (SELECT ... FROM inner WHERE inner.key outer.col) 转换后谓词下推 WHERE outer.col 5 AND EXISTS (SELECT ... FROM inner WHERE inner.key 5)关键转换在于由于outer.col 5常量且inner.key outer.col优化器可以将inner.key outer.col等价替换为inner.key 5。为什么这很重要因为inner.key 5是一个常量条件子查询的优化器可以利用这个条件来使用索引查找Index Lookup替代全表扫描大幅减少需要扫描和聚合的数据量甚至在某些情况下直接返回零行或单行策略二谓词上推Predicate Pull-up核心思想从子查询的执行结果中推导出对外层查询有用的过滤条件 场景示例 SELECT ... FROM outer WHERE outer.id IN (SELECT id FROM inner WHERE inner.status ACTIVE) 谓词上推后 优化器可以从子查询的条件中推断出外层查询的约束 提前过滤掉不满足条件的外层记录。谓词上推相对少见但同样重要。它让子查询的内部约束信息能够反向传递给外层查询帮助外层优化器做出更优的执行计划决策。等价性判定如何保证传递后的结果不变谓词传递听起来简单但优化器必须严格保证改写前后的查询结果完全等价。以下是几个关键判定规则判定规则说明常量传递outer.col 5可以安全传递为inner.key 5等值关系链如果A B且B C则A C可以传递非空判定outer.col IS NOT NULL只在子查询引用该列时传递不可传递场景涉及聚合、GROUP BY、窗口函数的场景需特殊处理代码示例场景构造-- 创建测试表 CREATE TABLE t1 ( id INT PRIMARY KEY, col_a INT, col_b VARCHAR(50) ); CREATE TABLE t2 ( id INT PRIMARY KEY, t1_id INT, amount DECIMAL(10,2), status VARCHAR(20) ); -- 假设 t1 有 10 万条记录t2 有 100 万条记录未优化场景8 小时的查询-- 开发者想查询特定 col_a 值对应的 t2 记录聚合 SELECT t1.col_b, (SELECT SUM(t2.amount) FROM t2 WHERE t2.t1_id t1.id AND t2.status VALID) AS total_amount FROM t1 WHERE t1.col_a 100;在没有谓词传递优化的数据库中执行计划是这样的1. 扫描 t1应用 WHERE t1.col_a 100假设匹配 100 行 2. 对这 100 行中的每一行执行一次子查询 - 子查询不知道 t1.id 的值因为谓词没有下推 - 子查询需要扫描 t2 中与当前 t1.id 匹配的记录 - 执行 SUM 聚合 3. 子查询总共执行 100 次如果 t1 的 col_a 100 匹配了 10 万行更极端的场景那么子查询就会被执行10 万次。每次子查询都需要对 t2 进行扫描和聚合总耗时约8 小时。优化后的执行路径启用谓词下推后优化器会将查询等价改写为-- 优化器内部改写对用户透明 SELECT t1.col_b, (SELECT SUM(t2.amount) FROM t2 WHERE t2.t1_id t1.id AND t2.status VALID) AS total_amount FROM t1 WHERE t1.col_a 100; -- 但子查询中的条件实际上被优化为常量查找核心改变在于由于t1.col_a 100是常量条件且子查询通过t2.t1_id t1.id引用了外层值优化器可以将这个等价关系传递到子查询中使其利用索引直接定位到需要聚合的记录。优化后效果子查询不再需要每次全量扫描和聚合而是利用索引直接定位总耗时降至519 毫秒。实测数据指标未优化优化后改善幅度子查询扫描次数10 万次1 次索引定位-总耗时~8 小时~519ms55000x数量级提升这不是渐进式优化而是从错误做法到正确做法的根本性改变。8 小时到 519 毫秒的差距意味着一个原本需要离线批处理的任务变成了可以在线实时响应的查询。最佳实践什么样的 SQL 能享受这项优化场景是否适用说明WHERE outer.col 常量 子查询引用 outer.col适用谓词下推的典型场景WHERE outer.col1 outer.col2 子查询适用等值关系链传递子查询包含 GROUP BY部分适用需满足等价性判定条件子查询包含窗口函数受限窗口函数的特殊性可能阻止传递WHERE outer.col 常量不等值部分适用不等值谓词的传递受限如何确认谓词传递是否生效-- 使用 EXPLAIN ANALYZE 查看执行计划 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT t1.col_b, (SELECT SUM(t2.amount) FROM t2 WHERE t2.t1_id t1.id AND t2.status VALID) AS total_amount FROM t1 WHERE t1.col_a 100;关注执行计划中的以下信息Filter 条件子查询内部是否出现了常量过滤条件Index Scan vs Seq Scan子查询是否使用了索引扫描Actual Loops子查询的实际执行次数是否减少Actual Rows子查询每次扫描返回的行数是否减少主动利用谓词传递的 SQL 编写技巧虽然优化器会自动完成谓词传递但了解其机制可以帮助你写出更容易被优化的 SQL-- 好写法将过滤条件写在最外层让优化器下推 SELECT * FROM t1 WHERE t1.id IN ( SELECT id FROM t2 WHERE t2.status ACTIVE ) AND t1.type ORDER; -- 这个条件会被下推到子查询中 -- 不太好过滤条件散落在不同位置 SELECT * FROM t1 WHERE t1.id IN ( SELECT id FROM t2 WHERE t2.status ACTIVE AND t1.type ORDER -- 条件写在子查询里但引用的是外层列 );总结金仓数据库 V9R4C19 的子查询等价谓词传递优化解决了复杂查询中一个长期被忽视的性能痛点谓词下推将外层常量条件传递到子查询内部利用索引精准定位避免重复的全量扫描和聚合谓词上推从子查询中提取有用信息反向约束外层查询进一步缩小扫描范围数量级改善从 8 小时到 519 毫秒的实测数据证明了这项优化的巨大威力对于经常编写包含嵌套子查询的复杂 SQL 的开发者和 DBA 来说这项优化意味着你不需要重写 SQL数据库会自动找到最优的执行路径。这正是现代查询优化器应该做的——让开发者专注于业务逻辑让数据库处理性能优化。如果你的生产环境中还存在慢得离谱的子查询不妨升级到 V9R4C19 试试。有时候最好的性能优化就是什么都不做——让优化器替你完成。