数据库核心术语知识库

定义

**参数嗅探 (Parameter Sniffing)**是指数据库优化器在编译存储过程或参数化查询时,会根据首次执行时传入的具体参数值来生成执行计划,并将该计划缓存起来供后续执行复用。

当后续执行的参数值与首次执行时的参数值在数据分布上差异较大时,缓存的执行计划可能不是最优的,从而导致性能问题。

详细笔记

核心原理

参数嗅探的工作流程

第一次执行存储过程:
  EXEC GetOrders @status = 1
  
步骤一:优化器"嗅探"到参数 @status = 1
步骤二:查看统计信息,status=1 的记录约 10%
步骤三:生成执行计划(使用索引扫描)
步骤四:缓存执行计划

第二次执行:
  EXEC GetOrders @status = 2
  
步骤一:检测到已有缓存计划
步骤二:直接复用缓存计划(即使 status=2 占 90%)
步骤三:可能性能很差(应该用全表扫描)

示例说明

SQL Server 示例

sql

-- 创建测试表
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  status TINYINT,  -- 0:待支付(1%), 1:已支付(9%), 2:已完成(90%)
  create_time DATETIME,
  amount DECIMAL(10, 2),
  INDEX idx_status (status)
);

-- 插入测试数据
INSERT INTO orders VALUES (...);  -- 100 万行

-- 创建存储过程
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  SELECT * FROM orders WHERE status = @status;
END;

场景一:首次执行传入稀有值

sql

-- 第一次执行:status = 0 (仅占 1%)
EXEC GetOrders @status = 0;

-- 优化器分析:
-- 1. 嗅探到 @status = 0
-- 2. 查看统计信息:status=0 约 1 万行(1%)
-- 3. 选择:索引扫描 + 回表
-- 4. 缓存执行计划

-- 第二次执行:status = 2 (占 90%)
EXEC GetOrders @status = 2;

-- 问题:
-- 1. 复用缓存的计划(索引扫描)
-- 2. 但 status=2 有 90 万行
-- 3. 需要回表 90 万次!
-- 4. 性能极差(应该用全表扫描)

-- 执行时间对比:
-- status=0: 50ms (索引扫描合适)
-- status=2: 30秒 (索引扫描不合适,应全表扫描约 2 秒)

场景二:首次执行传入常见值

sql

-- 第一次执行:status = 2 (占 90%)
EXEC GetOrders @status = 2;

-- 优化器选择:全表扫描
-- 缓存执行计划

-- 第二次执行:status = 0 (占 1%)
EXEC GetOrders @status = 0;

-- 问题:
-- 1. 复用缓存的计划(全表扫描)
-- 2. 但 status=0 仅 1 万行
-- 3. 全表扫描 100 万行,只返回 1 万行
-- 4. 性能较差(应该用索引扫描约 50ms)

检测参数嗅探问题

SQL Server

sql

-- 查看缓存的执行计划
SELECT 
  qs.plan_handle,
  qs.execution_count,
  qs.total_worker_time / qs.execution_count AS avg_cpu_time,
  qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
  qp.query_plan,
  st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE st.text LIKE '%GetOrders%'
ORDER BY qs.last_execution_time DESC;

-- 观察:
-- 1. execution_count:执行次数
-- 2. avg_elapsed_time:平均执行时间
-- 3. query_plan:实际执行计划
-- 4. 如果同一计划的执行时间波动很大,可能是参数嗅探问题

MySQL

MySQL 的参数化查询也存在类似问题:

sql

-- 预编译语句
PREPARE stmt FROM 'SELECT * FROM orders WHERE status = ?';

-- 第一次执行
SET @status = 0;
EXECUTE stmt USING @status;

-- 第二次执行
SET @status = 2;
EXECUTE stmt USING @status;

-- MySQL 也会缓存执行计划,可能遇到类似问题

解决方案

方案一:使用局部变量(避免嗅探)

sql

-- SQL Server
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  -- 将参数赋值给局部变量
  DECLARE @local_status TINYINT = @status;
  
  -- 使用局部变量
  SELECT * FROM orders WHERE status = @local_status;
END;

-- 原理:
-- 优化器无法"嗅探"局部变量的值
-- 会使用平均选择性生成计划
-- 计划可能不是最优,但更稳定

优缺点:

✅ 避免极端情况下的次优计划
✅ 计划更稳定
❌ 可能永远得不到最优计划
❌ 所有参数都使用"中等"计划

方案二:OPTION (RECOMPILE)

sql

-- SQL Server
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  SELECT * FROM orders WHERE status = @status
  OPTION (RECOMPILE);  -- 每次重新编译
END;

-- 原理:
-- 每次执行都重新编译,根据当前参数生成最优计划

优缺点:

✅ 每次都得到最优计划
❌ 编译开销大(复杂查询可能几百毫秒)
❌ 无法利用计划缓存
⚠️ 适合执行频率低、编译成本低的查询

方案三:OPTIMIZE FOR

sql

-- SQL Server:固定使用某个典型值
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  SELECT * FROM orders WHERE status = @status
  OPTION (OPTIMIZE FOR (@status = 1));  -- 总是按 status=1 优化
END;

-- 或使用未知值(使用平均选择性)
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  SELECT * FROM orders WHERE status = @status
  OPTION (OPTIMIZE FOR UNKNOWN);
END;

优缺点:

✅ 计划稳定
✅ 可以针对典型场景优化
❌ 非典型参数仍然性能差

方案四:动态 SQL

sql

-- SQL Server
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = 'SELECT * FROM orders WHERE status = ' + CAST(@status AS VARCHAR);
  EXEC sp_executesql @sql;
END;

-- 原理:
-- 每次生成新的 SQL 语句
-- 优化器视为不同查询,重新编译

优缺点:

✅ 每次都可能得到最优计划
❌ SQL 注入风险(需要严格验证参数)
❌ 计划缓存膨胀(每个参数值都有独立计划)
❌ 代码可读性差

方案五:条件分支

sql

-- SQL Server:根据不同参数使用不同策略
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  IF @status = 0  -- 稀有值
    SELECT * FROM orders WHERE status = @status
    OPTION (TABLE HINT(orders, INDEX(idx_status)));
  ELSE IF @status = 2  -- 常见值
    SELECT * FROM orders WHERE status = @status
    OPTION (TABLE HINT(orders, FORCESEEK));  -- 或强制全表扫描
  ELSE
    SELECT * FROM orders WHERE status = @status;
END;

-- 或使用不同的查询逻辑
CREATE PROCEDURE GetOrders @status TINYINT
AS
BEGIN
  IF @status IN (0, 1)  -- 稀有值:使用索引
    SELECT * FROM orders WHERE status = @status;
  ELSE  -- 常见值:使用临时表 + JOIN
    SELECT o.* 
    FROM orders o
    INNER JOIN (
    SELECT order_id FROM orders WHERE status = @status
    ) AS tmp ON o.order_id = tmp.order_id;
END;

优缺点:

✅ 针对不同场景定制最优策略
✅ 性能可预测
❌ 代码复杂
❌ 需要手动维护分支逻辑

方案六:清除计划缓存

sql

-- SQL Server:清除特定计划的缓存
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = plan_handle 
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE '%GetOrders%';

DBCC FREEPROCCACHE (@plan_handle);

-- 或清除所有计划缓存(慎用!)
DBCC FREEPROCCACHE;

-- MySQL:清除查询缓存
RESET QUERY CACHE;

适用场景:

统计信息发生重大变化后
发现计划明显次优时
作为临时应急手段

MySQL 中的类似问题

预编译语句的计划缓存

sql

-- MySQL 8.0+
PREPARE stmt FROM 'SELECT * FROM orders WHERE status = ? AND create_time > ?';

-- 第一次执行
SET @status = 0, @time = '2024-01-01';
EXECUTE stmt USING @status, @time;

-- 第二次执行(参数差异大)
SET @status = 2, @time = '2020-01-01';
EXECUTE stmt USING @status, @time;

-- MySQL 也会缓存执行计划

解决方案:

sql

-- 方案一:禁用计划缓存
SET GLOBAL prepared_stmt_cache_size = 0;

-- 方案二:定期清理
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM '...';

-- 方案三:使用 Hint(MySQL 8.0+)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * 
FROM orders 
WHERE status = ?;

PostgreSQL 中的计划缓存

PostgreSQL 也有类似机制:

sql

-- 预编译语句
PREPARE get_orders(int) AS
SELECT * FROM orders WHERE status = $1;

-- 执行
EXECUTE get_orders(0);
EXECUTE get_orders(2);

-- PostgreSQL 策略:
-- 前 5 次执行:每次都生成自定义计划
-- 第 6 次开始:比较自定义计划和通用计划的成本
-- - 如果通用计划成本接近,则使用通用计划
-- - 否则继续使用自定义计划

查看计划选择:

sql

EXPLAIN (ANALYZE, COSTS, TIMING) EXECUTE get_orders(0);

-- 输出会显示:
-- "Planning Time: 0.123 ms"
-- "Execution Time: 10.456 ms"
-- 如果使用通用计划,会标注 "Generic Plan"

实际案例分析

案例一:电商订单查询

sql

-- 业务场景:
-- 大部分订单状态为"已完成"(90%)
-- 偶尔查询"待支付"订单(1%)

-- 问题存储过程
CREATE PROCEDURE GetOrdersByStatus @status TINYINT
AS
BEGIN
  SELECT * FROM orders WHERE status = @status;
END;

-- 问题:
-- 1. 首次执行:status = 2(已完成)
-- 2. 优化器选择:全表扫描
-- 3. 后续执行:status = 0(待支付)
-- 4. 仍然使用全表扫描,性能差

-- 解决方案:使用局部变量
CREATE PROCEDURE GetOrdersByStatus @status TINYINT
AS
BEGIN
  DECLARE @local_status TINYINT = @status;
  SELECT * FROM orders WHERE status = @local_status;
END;

-- 或使用 RECOMPILE
CREATE PROCEDURE GetOrdersByStatus @status TINYINT
AS
BEGIN
  SELECT * FROM orders WHERE status = @status
  OPTION (RECOMPILE);
END;

案例二:日期范围查询

sql

-- 问题:不同日期范围的数据量差异巨大
CREATE PROCEDURE GetOrdersByDateRange 
  @start_date DATETIME,
  @end_date DATETIME
AS
BEGIN
  SELECT * FROM orders 
  WHERE create_time BETWEEN @start_date AND @end_date;
END;

-- 场景一:查询最近一天
EXEC GetOrdersByDateRange '2024-01-15', '2024-01-16';
-- 返回:1000 行
-- 优化器选择:索引扫描

-- 场景二:查询整年
EXEC GetOrdersByDateRange '2023-01-01', '2023-12-31';
-- 返回:365000 行
-- 问题:复用索引扫描计划,性能差

-- 解决方案:根据范围大小动态选择策略
CREATE PROCEDURE GetOrdersByDateRange 
  @start_date DATETIME,
  @end_date DATETIME
AS
BEGIN
  DECLARE @days INT = DATEDIFF(DAY, @start_date, @end_date);
  
  IF @days <= 7  -- 短期:使用索引
    SELECT * FROM orders 
    WHERE create_time BETWEEN @start_date AND @end_date;
  ELSE  -- 长期:使用全表扫描
    SELECT * FROM orders 
    WHERE create_time BETWEEN @start_date AND @end_date
    OPTION (TABLE HINT(orders, SCAN));
END;

监控与预防

SQL Server:监控计划质量

sql

-- 查找执行时间波动大的查询
SELECT 
  qs.sql_handle,
  qs.plan_handle,
  qs.execution_count,
  qs.min_elapsed_time,
  qs.max_elapsed_time,
  qs.avg_elapsed_time,
  (qs.max_elapsed_time - qs.min_elapsed_time) / 
  NULLIF(qs.avg_elapsed_time, 0) AS variability_ratio,
  st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 10
  AND (qs.max_elapsed_time - qs.min_elapsed_time) / 
  NULLIF(qs.avg_elapsed_time, 0) > 5  -- 波动超过 5 倍
ORDER BY variability_ratio DESC;

最佳实践

识别关键查询:对性能敏感的存储过程要特别注意参数嗅探
选择合适的解决方案:

执行频率高:使用局部变量或 OPTIMIZE FOR
执行频率低:使用 RECOMPILE
参数分布不均:使用条件分支

监控执行计划:定期检查计划缓存,发现异常及时清理
保持统计信息最新:过时的统计信息会加剧参数嗅探问题
测试多种参数:开发阶段测试不同参数值的执行计划
文档化已知问题:记录哪些存储过程存在参数嗅探风险

关联术语

[[执行计划]]
[[统计信息]]
[[查询缓存]]
[[索引提示]]

参考资料

Microsoft Docs: Parameter Sniffing
Brent Ozar: Parameter Sniffing Explained
MySQL 官方文档: Prepared Statement Cache
PostgreSQL 文档: Plan Caching

定义 ​

详细笔记 ​

核心原理 ​

参数嗅探的工作流程 ​

示例说明 ​

SQL Server 示例 ​

检测参数嗅探问题 ​

SQL Server ​

MySQL ​

解决方案 ​

方案一:使用局部变量(避免嗅探) ​

方案二:OPTION (RECOMPILE) ​

方案三:OPTIMIZE FOR ​

方案四:动态 SQL ​

方案五:条件分支 ​

方案六:清除计划缓存 ​

MySQL 中的类似问题 ​

预编译语句的计划缓存 ​

PostgreSQL 中的计划缓存 ​

实际案例分析 ​

案例一:电商订单查询 ​

案例二:日期范围查询 ​

监控与预防 ​

SQL Server:监控计划质量 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义

详细笔记

核心原理

参数嗅探的工作流程

示例说明

SQL Server 示例

检测参数嗅探问题

SQL Server

MySQL

解决方案

方案一:使用局部变量(避免嗅探)

方案二:OPTION (RECOMPILE)

方案三:OPTIMIZE FOR

方案四:动态 SQL

方案五:条件分支

方案六:清除计划缓存

MySQL 中的类似问题

预编译语句的计划缓存

PostgreSQL 中的计划缓存

实际案例分析

案例一:电商订单查询

案例二:日期范围查询

监控与预防

SQL Server:监控计划质量

最佳实践

关联术语

参考资料