想象这样一个场景：你的生产环境中有一个存储过程，平时执行只需几百毫秒，突然某天开始，同样的调用却需要几分钟甚至更长时间。你检查了服务器资源——CPU、内存、磁盘IO都正常。你把存储过程中的SQL语句单独拎出来执行，发现跑得飞快。你尝试ALTER一下存储过程（什么代码都没改），问题竟然消失了。

这不是玄学，这是数据库领域最令人头疼的问题之一——参数嗅探（Parameter Sniffing）。

Brent Ozar曾用一个精妙的比喻来形容这个问题：你需要运送一只动物，可能是老鼠，也可能是大象。老鼠可以装进盒子轻松快递，大象却需要租一辆专门的运输车。如果你提前知道运送的是什么，就能做出正确的准备。但参数嗅探的问题在于：第一次运送的是大象，系统就准备了运象车，此后每次运送老鼠都用这辆车——虽然能用，但效率极其低下。

执行计划缓存：问题的起点

理解参数嗅探，必须先理解执行计划缓存存在的意义。

当一条SQL语句到达数据库时，优化器需要决定如何执行它：用哪个索引、以什么顺序连接表、是否使用并行执行。这个过程称为"编译"或"优化"，涉及大量的计算：评估可能的执行路径、估算每条路径的成本、选择成本最低的方案。对于复杂查询，编译过程可能需要几秒甚至几十秒。

如果每次执行都重新编译，CPU资源会被大量消耗。于是，数据库系统设计了执行计划缓存机制：编译一次，多次重用。SQL Server将编译后的执行计划存入Plan Cache，PostgreSQL有类似的Prepared Statement机制，Oracle有Shared Cursor。

这本来是个好设计。问题出在参数化查询上。

当查询包含参数时（比如存储过程、Prepared Statement），优化器在编译时需要知道参数的值来估算结果集大小（基数，Cardinality），从而选择最优的执行计划。于是系统会"嗅探"第一次执行时的参数值，据此生成计划并缓存。

这就是参数嗅探。

数据倾斜：问题爆发的温床

参数嗅探本身不是bug，它是执行计划缓存机制的自然延伸。真正的问题在于数据倾斜。

考虑一个订单表，其中有status字段表示订单状态。假设总共有100万条订单，其中：

  • 99万条是"已完成"状态
  • 5000条是"处理中"状态
  • 5000条分散在其他各种状态

如果查询条件是WHERE status = @status，最优的执行计划完全取决于参数值：

  • 查询"已完成"：返回99万条记录，应该走全表扫描
  • 查询"处理中"：返回5000条记录，应该走索引查找

这就是经典的"大象与老鼠"问题。

第一次执行时，如果碰巧传入的是"处理中"，系统会生成一个使用索引查找的计划并缓存。此后查询"已完成"时，系统沿用这个计划——对99万条记录做索引查找再回表，效率比全表扫描低几个数量级。

更隐蔽的是，这种问题往往难以复现。在开发环境测试时，数据量和生产环境不同；即使数据量相同，第一次执行的参数值也可能不同。于是同样的存储过程，在开发环境跑得飞快，到了生产环境却慢如蜗牛。

基数估计：优化器的眼睛

要深入理解参数嗅探，必须了解基数估计（Cardinality Estimation）的工作原理。

优化器选择执行计划的核心依据是成本估算，而成本估算的基础是知道每个操作会处理多少行数据。这就需要基数估计。

SQL Server使用统计信息来进行基数估计，主要包括两部分：

直方图（Histogram）：记录列值的分布情况。SQL Server的直方图最多包含200个"桶"（Step），每个桶记录一个值范围的统计信息：该范围的上界值、等于上界值的行数、小于上界值的行数、不同值的个数。

密度向量（Density Vector）：记录列或列组合的选择性。密度 = 1 / 不同值的个数，密度越低，选择性越高。

当查询条件使用参数时，优化器会查看直方图，找到参数值对应的桶，估算匹配的行数。问题在于：

  1. 直方图精度有限：200个桶无法完美表示所有数据分布。对于高基数列（不同值很多），很多值可能落在同一个桶里，估算精度下降。

  2. 统计信息可能过时：数据在变，统计信息不会自动实时更新。SQL Server默认在表数据变化超过20%时才更新统计信息。

  3. 参数嗅探的是编译时的值：计划缓存中保存的是第一次编译时嗅探到的参数值。此后执行时，即便参数值完全不同，优化器也看不到——它只是从缓存中取出计划执行。

2014年，SQL Server引入了新的基数估计器（CE 120），2016年又升级到CE 130。新版本在处理相关列、多谓词等场景时有所改进，但对参数嗅探问题本身帮助有限——问题的根源不在于估算方法，而在于缓存的执行计划只有一个。

各大数据库的应对之道

参数嗅探是关系型数据库的共性问题。不同数据库采取了不同的解决策略，各有取舍。

SQL Server：从手动干预到自动优化

传统方案

SQL Server提供了多种手动解决参数嗅探的方法：

OPTION (RECOMPILE)：每次执行都重新编译，不缓存计划。这是最彻底的解决方案，但代价是每次执行都有编译开销。对于执行频率高但每次执行时间短的查询，编译开销可能超过查询本身的时间。

OPTION (OPTIMIZE FOR UNKNOWN)：告诉优化器不要嗅探参数值，而是使用统计密度进行估算。这相当于为所有参数值生成一个"折中"的计划。对于数据倾斜严重的场景，这个折中计划可能对任何参数值都不是最优的。

OPTION (OPTIMIZE FOR (@param = value))：手动指定一个参数值，优化器始终按这个值生成计划。这要求开发者对数据分布有深入了解，且数据分布变化后需要调整。

本地变量：在存储过程中将参数赋值给本地变量，然后使用本地变量查询。优化器对本地变量不进行嗅探，效果等同于OPTIMIZE FOR UNKNOWN。这是一个常见但容易被误解的技巧——本质上是在"欺骗"优化器。

PSP优化：SQL Server 2022的突破

SQL Server 2022引入了参数敏感计划优化（Parameter Sensitive Plan Optimization, PSP），这是该问题的第一个自动化解决方案。

PSP的核心思想是：既然一个计划不能适应所有参数值，那就为不同的参数值范围生成不同的计划。

具体实现如下：

编译阶段，优化器分析查询中的参数化谓词，识别数据分布不均匀的列（基于直方图）。对于符合条件的谓词，系统创建一个调度器计划（Dispatcher Plan），而不是普通的执行计划。

调度器计划包含一个调度器表达式，定义了参数值到不同计划变体的映射规则。具体来说，它将参数值对应的基数（预计返回的行数）分成若干范围（低、中、高），每个范围对应一个查询变体（Query Variant）。

执行阶段，调度器首先估算当前参数值对应的基数，确定属于哪个范围，然后选择对应的查询变体执行。如果对应范围的查询变体尚未编译，系统会编译并缓存它。

flowchart TD
    A[查询请求到达] --> B{调度器计划?}
    B -->|是| C[计算参数基数]
    B -->|否| D[使用缓存的计划]
    C --> E{确定基数范围}
    E -->|低| F[查询变体1: 索引查找]
    E -->|中| G[查询变体2: 索引查找+书签查找]
    E -->|高| H[查询变体3: 全表扫描]
    F --> I[执行查询]
    G --> I
    H --> I
    D --> I

PSP优化有几个限制：

  • 仅支持等值谓词（=操作符）
  • 每个查询最多选择3个谓词进行PSP优化
  • 需要数据库兼容级别为160（SQL Server 2022）

启用PSP优化后，计划缓存中会出现三种类型的计划：

  • 调度器计划（Dispatcher Plan）：存储映射逻辑
  • 查询变体计划（Query Variant Plan）：针对特定基数范围优化的计划
  • 普通计划：非参数化查询或未触发PSP的查询

Oracle：自适应游标共享

Oracle在11g版本就引入了自适应游标共享（Adaptive Cursor Sharing, ACS），比SQL Server的PSP早了十多年。

Oracle的方案核心是游标绑定感知（Bind-Aware Cursor）。当一条绑定变量的SQL语句首次执行时，优化器会正常嗅探绑定变量值并生成计划。但与SQL Server不同的是，Oracle会监控这条SQL后续执行的实际效率。

如果发现某些绑定变量值导致执行效率不佳（比如执行时间与预估时间差异过大），Oracle会将该游标标记为"绑定敏感"（Bind-Sensitive）。此后，不同的绑定变量值范围可能会触发生成不同的子游标（Child Cursor），每个子游标有独立的执行计划。

Oracle使用**选择性立方体（Selectivity Cube）**来追踪不同绑定变量值的选择性，并在后续执行时根据绑定变量的选择性选择合适的子游标。

ACS的工作流程可以简化为：

  1. 首次执行：嗅探绑定变量，生成计划A
  2. 后续执行：监控实际执行效率
  3. 发现效率异常：将游标标记为绑定敏感
  4. 新的绑定值触发新计划：生成计划B
  5. 后续执行：根据绑定值的选择性选择A或B

与SQL Server PSP相比，Oracle ACS是"事后响应"机制——先发现问题，再生成新计划。而PSP是"事前预防"——在首次编译时就预见到可能的问题，提前生成映射逻辑。

PostgreSQL：通用计划与定制计划

PostgreSQL的处理方式更加灵活，核心概念是通用计划（Generic Plan）与定制计划（Custom Plan）。

定制计划：在执行时使用实际的参数值进行优化，相当于每次都"嗅探"参数值。

通用计划：不依赖具体参数值，使用统计信息中的平均选择性进行优化。

PostgreSQL 12之前，系统采用启发式规则：前5次执行使用定制计划，然后比较定制计划和通用计划的预估成本。如果通用计划成本不高于定制计划平均成本的某个阈值，后续就一直使用通用计划；否则继续使用定制计划。

PostgreSQL 12引入了plan_cache_mode参数，允许用户强制选择：

  • auto：默认行为，由系统决定
  • force_generic_plan：强制使用通用计划
  • force_custom_plan：强制使用定制计划

这种设计的优点是简单直观，缺点是把选择权交给了用户。对于数据分布不均匀的场景，通用计划可能对所有参数值都是次优的；而强制定制计划意味着每次执行都有编译开销。

MySQL：直方图与统计信息

MySQL在8.0版本引入了直方图统计信息，但与SQL Server不同，MySQL默认不缓存执行计划（Prepared Statement除外）。

对于普通的SQL语句，MySQL每次执行都会重新解析和优化。这意味着参数嗅探问题在MySQL中表现得不太一样——更常见的问题是统计信息不准确导致的执行计划选择错误。

MySQL 8.0的直方图可以通过以下命令手动创建：

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

直方图可以帮助优化器更好地估算选择性，但对于参数化查询，优化器仍然需要使用平均选择性（因为没有参数的实际值）。这与SQL Server的OPTIMIZE FOR UNKNOWN类似。

解决方案的权衡分析

没有完美的解决方案，每种方法都有其代价。

**OPTION (RECOMPILE)**的代价是编译开销。如果查询本身执行时间很短（比如几十毫秒），编译开销可能占主导地位。更隐蔽的问题是，频繁编译会增加CPU压力，在高并发场景下可能引发连锁反应。此外，使用RECOMPILE的语句不会在计划缓存中留下痕迹，增加了诊断难度。

OPTIMIZE FOR UNKNOWN的代价是次优计划。对于数据分布均匀的列，这个方案效果不错；但对于严重倾斜的数据，折中计划可能比两个极端都差。想象一个只有"大象"和"老鼠"的场景：运送大象用大车是合适的，运送老鼠用小车也是合适的，但如果用一个"中等大小"的车呢？对大象来说装不下，对老鼠来说浪费——两头不讨好。

本地变量本质上与OPTIMIZE FOR UNKNOWN相同，但代码可读性更差，还增加了维护负担。更重要的是，它"隐藏"了问题——从执行计划中看不到参数嗅探的痕迹，诊断更加困难。

PSP优化是最优雅的解决方案，但有其局限性。目前只支持等值谓词，不支持LIKE、BETWEEN、范围查询等场景。此外，它会增加计划缓存的条目数，在极端情况下可能导致缓存膨胀。

Oracle ACS和PostgreSQL plan_cache_mode各有特色，但都要求对数据库有较深入的理解才能正确使用。

诊断与最佳实践

如何判断是否遇到了参数嗅探问题？以下是一些典型症状：

  1. 相同存储过程，不同参数值，性能差异巨大
  2. 清除计划缓存后性能恢复正常（通过DBCC FREEPROCCACHE或重启服务）
  3. ALTER存储过程（不改动代码）后性能恢复正常
  4. 存储过程执行慢，单独执行其中的SQL语句却很快
  5. 执行计划中的"估计行数"与"实际行数"差异巨大

在SQL Server中，可以通过以下方式诊断：

查看执行计划XML，搜索ParameterCompiledValue：

<ColumnReference Column="@ProductID" 
                 ParameterCompiledValue="(870)" 
                 ParameterRuntimeValue="(945)" />

使用Query Store查看同一查询的多个执行计划及其性能差异：

SELECT q.query_id, p.plan_id, p.avg_duration, p.avg_cpu_time
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE q.query_text_id = (SELECT query_text_id FROM sys.query_store_query_text 
                         WHERE query_sql_text LIKE '%YourQuery%')
ORDER BY p.avg_duration DESC;

最佳实践建议：

  1. 保持统计信息更新：过时的统计信息是参数嗅探问题的常见放大器。根据数据变化频率调整统计信息更新策略。

  2. 为高选择性查询创建覆盖索引：如果索引包含了查询所需的所有列，优化器就不需要在"索引查找+回表"和"全表扫描"之间纠结了。

  3. 评估是否真的需要参数化：对于数据分布极其倾斜且查询模式固定的场景，直接使用字面值可能更好。

  4. 优先使用PSP优化：如果使用SQL Server 2022或更新版本，让系统自动处理参数嗅探问题。

  5. 谨慎使用OPTION (RECOMPILE)：只在执行频率低、单次执行时间长的查询上使用。

  6. 避免过度依赖本地变量：这种技巧在短期内可能有效，但会掩盖问题，增加长期维护成本。

没有万能方案的世界

参数嗅探问题的本质是一个执行计划无法适应所有可能的参数值。这是执行计划缓存机制与数据分布不均匀之间的矛盾，没有一劳永逸的解决方案。

SQL Server的PSP优化、Oracle的ACS、PostgreSQL的plan_cache_mode，都是试图在这个矛盾中寻找平衡点。它们各有侧重：PSP是事前预防，ACS是事后适应，plan_cache_mode是用户选择。

理解这些机制背后的权衡，比记住具体的配置命令更重要。下次遇到"同一个存储过程时快时慢"的问题时，你知道该从哪里下手了——检查执行计划中的ParameterCompiledValue，看看系统是不是在用运送大象的卡车送老鼠。

参考文献

  1. Microsoft Learn. Parameter Sensitive Plan Optimization. https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization
  2. Brent Ozar. The Elephant and the Mouse, or Parameter Sniffing in SQL Server. https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
  3. Brent Ozar. PSPO: How SQL Server 2022 Tries to Fix Parameter Sniffing. https://www.brentozar.com/archive/2022/08/pspo-how-sql-server-2022-tries-to-fix-parameter-sniffing/
  4. Oracle-Base. Adaptive Cursor Sharing in Oracle Database 11g Release 1. https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1
  5. Cybertec. Tech Preview: How PostgreSQL 12 Handles Prepared Plans. https://www.cybertec-postgresql.com/en/tech-preview-how-postgresql-12-handles-prepared-plans/
  6. PostgreSQL Documentation. plan_cache_mode Parameter. https://www.postgresql.org/docs/current/runtime-config-query.html
  7. 阿里云开发者社区. SQL Server最佳实践：参数嗅探问题. https://developer.aliyun.com/article/71944
  8. SQLShack. Query Optimization Techniques in SQL Server: Parameter Sniffing. https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/
  9. Erik Darling. Troubleshooting Parameter Sniffing Issues. https://erikdarling.com/troubleshooting-parameter-sniffing-with-sp_whoisactive/
  10. Microsoft Learn. Cardinality Estimation (SQL Server). https://learn.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server