DBA夜读·第一季第5期｜查询优化器陷阱：为什么相同的查询有时快有时慢？

绩隐金
0 阅读8分钟

📘 第一季·《100 SQL Server Mistakes and How to Avoid Them》

本季围绕 Peter A. Carter 的经典著作，系统梳理 SQL Server 开发与管理的常见错误。本书共408页，涵盖T-SQL开发、安装配置、性能优化、高可用性、安全等全领域。

今日晨报联动： 今天早上「DBA晨报·第5期」我们讨论了统计信息缺失导致执行计划"随机摇摆"的问题——这恰恰是本章的核心主题：查询优化器的决策高度依赖输入质量。同时，PostgreSQL 18的优化器增强也从正面印证了：理解优化器行为，才能用好优化器。今晚我们从《100 SQL Server Mistakes》第五章出发，系统梳理查询优化器相关的常见陷阱，包括参数嗅探、统计信息过时、FILTER操作失控等，每一个都来自生产环境中的真实案例。

第一部分：核心总结与实践

一、本期概览

本书第五章聚焦查询优化器陷阱。作者Peter A. Carter指出：

"优化器是SQL Server中最智能的组件，但它的'智能'完全建立在输入信息的准确性之上。一旦输入错误，优化器就会做出'看起来正确实则致命'的决策。"

本章核心观点：

  • • 优化器基于成本模型选择执行计划，但成本估算依赖统计信息的准确性

  • • 参数嗅探是参数化查询中最隐蔽的性能杀手

  • • 统计信息过时会让优化器"失明"

  • • 查询转换失败可能导致FILTER等低效操作

本期我们提炼出4个最常见的优化器陷阱，每个都附带真实案例和解决方案。

二、核心错误与解决方案

错误1：参数嗅探——第一次执行决定一切

问题场景（SQL Server经典案例）：

假设有一张表Table1，Column2数据分布极不均匀——1条记录值为1，1499条记录值为2。

-- 创建存储过程
CREATE PROCEDURE RetrieveData @Col2Value INT
AS
SELECT * FROM Table1 WHERE Column2 = @Col2Value

第一次执行：传入参数1，只返回1条记录

  • • 优化器选择：非聚集索引查找 + 书签查找

  • • 逻辑读：3次

第二次执行：传入参数2，应返回1499条记录

  • • 优化器本应选择：全表扫描（因返回行数超过临界点）

  • • 但实际执行：重用了第一次的索引查找计划

  • • 逻辑读：1505次（每条记录一次书签查找）

这就是参数嗅探——优化器基于第一次执行的参数值编译并缓存计划，后续即使传入不同参数，仍盲目重用该计划。

识别方法： 查看执行计划的参数列表

  • • Parameter Compile Value：编译时的参数值

  • • Parameter Runtime Value：实际运行时的参数值

💡 如果两者不同，说明正在发生参数嗅探

解决方案：

| 方案 | 语法 | 适用场景 | | --- | --- | --- | | RECOMPILE | OPTION (RECOMPILE) | 查询每次执行时参数值差异极大，或用于调试 | | OPTIMIZE FOR | OPTION (OPTIMIZE FOR (@param = 典型值)) | 知道典型参数值，希望计划固定为该值优化 | | OPTIMIZE FOR UNKNOWN | OPTION (OPTIMIZE FOR UNKNOWN) | 让优化器使用平均分布假设，避免极端值影响 | | 本地编译（EF Core） | UseQueryTrackingBehavior | EF Core 5.0+，每次编译计划 |

💡 最佳实践： 对于高频执行且参数分布差异大的存储过程，优先使用 OPTION (RECOMPILE)——虽然增加了编译开销，但能确保每次执行计划最优。

错误2：统计信息过时——优化器"失明"的根源

问题表现：

  • • 某查询原本运行良好，数据量增长后突然变慢

  • • 或刚收集统计信息后性能改善，一段时间后又恶化

根因分析：

SQL Server优化器基于统计信息估算不同执行计划的成本。当统计信息过时时，优化器对数据分布的认知与实际严重不符，导致：

  • • 🔴 选择性估算错误

  • • 🔴 索引使用决策错误

  • • 🔴 连接顺序选择错误

检查方法：

-- 查看统计信息最后更新时间
SELECT 
    OBJECT_NAME(ind.object_id) AS TableName,
    ind.name AS IndexName,
    STATS_DATE(ind.object_id, ind.index_id) AS StatsUpdated
FROM sys.indexes ind
WHERE STATS_DATE(ind.object_id, ind.index_id) IS NOT NULL
ORDER BY StatsUpdated;

解决方案：

| 方案 | 说明 | | --- | --- | | 自动更新 | 确保数据库选项AUTO_UPDATE_STATISTICS为ON（默认开启） | | 手动更新 | 数据量大幅变化后，执行UPDATE STATISTICS或sp_updatestats | | 增加采样精度 | 对关键列使用WITH FULLSCAN或调整STATISTICS_NORECOMPUTE阈值 |

跨平台对比：

  • • PostgreSQL： 使用ANALYZE命令更新统计信息，可通过default_statistics_target调整采样精度（默认100）

  • • OceanBase： 未收集统计信息时使用动态采样，存在计划不稳定风险

错误3：FILTER操作——子查询无法UNNEST的代价

问题场景（Oracle案例，SQL Server同理）：

SELECT * FROM main_table 
WHERE col1 NOT IN (SELECT col1 FROM sub_table)

当子查询中的列存在NULL值时，优化器无法将NOT IN子查询转换为高效的ANTI JOIN，转而使用FILTER操作。

FILTER的危害：

  • • 单子节点时：简单过滤，性能尚可

  • • 多子节点时： 类似嵌套循环，但内部构建哈希表。当匹配行数少、循环次数多时，FILTER成为性能杀手

⚠️ 某真实案例中，FILTER操作导致查询预计运行时间超过10天。

解决方案：

| 方案 | 说明 | | --- | --- | | NOT EXISTS改写 | WHERE NOT EXISTS (SELECT 1 FROM sub_table WHERE sub_table.col1 = main_table.col1) | | 添加NOT NULL约束 | 确保关联列没有NULL值，让优化器可以转换为JOIN | | 显式添加IS NOT NULL条件 | 在子查询中添加WHERE col1 IS NOT NULL | | 使用LEFT JOIN + IS NULL | 另一种改写方式 |

💡 检查方法： 在执行计划中查找FILTER操作符，如果它有多个子节点，且Predicate部分出现类似B1的绑定变量，说明子查询未被展开。

错误4：查询转换失败——优化器"无能"的真相

问题背景：

Oracle优化器的核心组件是查询转换器（Query Transformer），它负责将原始SQL等价重写为更易优化的形式，例如：

  • • 将子查询转换为JOIN

  • • 视图合并

  • • 谓词推入

常见查询转换失败场景：

  • • 复杂子查询无法UNNEST

  • • 复杂视图无法合并

  • • 谓词无法推入

  • • OR扩展失败

严重后果：

  • • 🔴 执行路径受限，无法利用高效JOIN算法

  • • 🔴 可能导致错误结果（Wrong Result）——这是最严重的一类BUG

识别方法：

  • • 使用10053事件（Oracle）查看查询转换过程

  • • 在执行计划中查找FILTER、VIEW等操作符，判断是否转换成功

通用解决方案：

| 方案 | 说明 | | --- | --- | | 简化SQL结构 | 将复杂查询拆分为多个简单查询 | | 使用CTE | 公共表表达式有时能帮助优化器更好地处理 | | 手动改写 | 根据业务逻辑，将子查询改写为JOIN | | 更新数据库版本 | 许多查询转换BUG在新版本中已修复 |

三、本期小结

| 错误类型 | 后果 | 正确姿势 | | --- | --- | --- | | 参数嗅探 | 不同参数值使用相同计划，性能两极分化 | RECOMPILE、OPTIMIZE FOR、本地编译 | | 统计信息过时 | 优化器基于错误认知做决策，计划不优 | AUTO_UPDATE_STATISTICS ON；定期手动更新 | | FILTER操作 | 子查询无法UNNEST，性能杀手 | 改写为NOT EXISTS或添加NOT NULL约束 | | 查询转换失败 | 执行路径受限，可能产生错误结果 | 简化SQL、更新版本、手动改写 |

关于本书第五章

《100 SQL Server Mistakes and How to Avoid Them》第五章"Query Optimizer Traps"深入探讨：

  • • 🔍 参数嗅探的原理与解决方案

  • • 📊 统计信息对优化器决策的影响

  • • 🔄 查询转换机制及其失败场景

  • • 💾 如何利用查询存储监控和固定执行计划

💡 作者强调："优化器不是魔法，它只是一个基于输入信息做数学计算的引擎。DBA的核心价值，是确保优化器获得准确、完整的输入信息。"

新建了一个微信群，目前就我一人，希望慢慢人多起来，共同学习进步。

图片

下期预告

📖 下期主题：《DBA夜读·第一季第6期》 我们将进入并发与锁管理——死锁是如何产生的？如何排查和预防？隔离级别选择不当会带来哪些性能影响？以及如何通过索引设计减少锁竞争。

💬 读者讨论： 你是否遇到过参数嗅探导致的性能问题？或者统计信息过时引发的"计划漂移"？欢迎留言分享，我会在下期精选回复。

本文为学习笔记，内容基于《100 SQL Server Mistakes and How to Avoid Them》第五章提炼总结，作者Peter A. Carter，Manning Publications出版。参考技术资料：金仓数据库信贷风控案例、PostgreSQL 18官方文档、OceanBase动态采样分析、SQL Server参数嗅探经典案例。

avatar
文章
阅读
粉丝