一、SQLServer中的变量性能问题从何而来
在日常开发中，我们经常需要在SQLServer中使用变量来存储临时数据。但你可能不知道的是，不同类型的变量使用方式会对查询性能产生巨大影响。今天我们就来聊聊局部变量和参数化查询在执行计划上的差异。
先看一个简单的例子，我们创建一个测试表：
-- 创建测试表
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(100),
Price DECIMAL(10,2),
CategoryID INT
);
-- 插入测试数据
INSERT INTO Products VALUES
(1, '笔记本电脑', 5999.00, 1),
(2, '智能手机', 3999.00, 1),
(3, '平板电脑', 2999.00, 1),
(4, '办公椅', 899.00, 2),
(5, '书桌', 1299.00, 2);
二、局部变量带来的性能陷阱
很多开发者喜欢在存储过程中使用局部变量，认为这样代码更整洁。但局部变量有一个致命的问题：它们会导致SQLServer无法使用最优的执行计划。
-- 使用局部变量的存储过程
CREATE PROCEDURE GetProductsByCategory_LocalVariable
AS
BEGIN
DECLARE @CategoryID INT = 1; -- 局部变量
SELECT * FROM Products
WHERE CategoryID = @CategoryID;
END;
执行这个存储过程后，查看实际执行计划，你会发现SQLServer使用了表扫描而不是索引查找（假设我们在CategoryID上有索引）。这是因为优化器在编译时不知道@CategoryID的具体值，只能生成一个通用的执行计划。
三、参数化查询的优势
相比之下，参数化查询允许优化器在每次执行时根据实际参数值生成最优的执行计划。
-- 使用参数的存储过程
CREATE PROCEDURE GetProductsByCategory_Parameter
@CategoryID INT -- 输入参数
AS
BEGIN
SELECT * FROM Products
WHERE CategoryID = @CategoryID;
END;
当我们执行这个存储过程时：
EXEC GetProductsByCategory_Parameter @CategoryID = 1;
SQLServer会根据实际传入的参数值1，选择使用索引查找（如果有合适的索引），性能会显著提升。
四、深入理解参数嗅探
参数化查询虽然性能更好，但也引入了"参数嗅探"的问题。SQLServer会在第一次执行存储过程时，根据传入的参数值生成并缓存执行计划。
-- 第一次执行使用常见值
EXEC GetProductsByCategory_Parameter @CategoryID = 1; -- 常见值
-- 第二次执行使用罕见值
EXEC GetProductsByCategory_Parameter @CategoryID = 2; -- 罕见值
如果第一次执行使用的是常见值（返回少量数据），而后续执行使用罕见值（返回大量数据），可能会导致性能问题。这时可以考虑使用OPTION(RECOMPILE)提示：
CREATE PROCEDURE GetProductsByCategory_WithRecompile
@CategoryID INT
AS
BEGIN
SELECT * FROM Products
WHERE CategoryID = @CategoryID
OPTION (RECOMPILE); -- 每次重新编译
END;
五、实际应用场景分析
高并发OLTP系统：建议使用参数化查询，因为执行计划会被重用，减少编译开销。
报表查询：如果每次查询参数差异很大，可以考虑使用OPTION(RECOMPILE)或者局部变量+动态SQL的方式。
数据仓库：通常查询模式固定，参数化查询是最佳选择。
六、技术优缺点对比
局部变量的优点：
- 代码结构清晰
- 适合在复杂逻辑中存储中间结果
局部变量的缺点：
- 导致执行计划不优化
- 可能引发性能问题
参数化查询的优点：
- 执行计划可以优化
- 适合高并发场景
- 防止SQL注入
参数化查询的缺点：
- 可能产生参数嗅探问题
- 需要更多管理
七、注意事项
在创建索引时，要考虑查询模式，特别是参数化查询的使用方式。
监控执行计划，特别是当查询性能突然下降时。
对于复杂的查询，可能需要结合使用参数化查询和局部变量。
考虑使用查询存储(Query Store)来跟踪执行计划变化。
八、总结
在SQLServer中，变量使用方式的选择会对查询性能产生重大影响。一般来说，参数化查询是首选方案，但在特定场景下可能需要考虑其他选项。理解执行计划生成机制和参数嗅探问题，才能做出最佳决策。
记住，没有放之四海而皆准的方案，关键是根据实际业务场景和查询模式选择最合适的变量使用策略。
评论