<span>参数化（三）：参数嗅探</span>

参数化（三）：参数嗅探

全栈程序员-用户IM • 2021年11月26日上午8:00 • 未分类

在之前的随笔中我提到过参数嗅探，这是非常重要的概念。下面我们深入的研究一下参数嗅探…

首先我们知道批处理可以是参数化的或者非参数化。参数化的批处理计划有两种类型：“Prepared” 或者“Proc”。前者对应带有至少一个参数的sys.sp_executesql的执行，并且从T-SQL批处理，或者应用程序通过ADO.NET等直接被执行的。后者的执行计划对应一个存储过程。

参数嗅探在这两种类型中是完全相同的。它的行为在两种计划中是完全一样的。因此我们这里不去讨论类型，只关心参数化批处理本身的作用。

什么是参数嗅探?

当批处理包含一个或者多个参数时并且它需要优化（例如因为没有该批处理执行计划缓存，或者只有不可用的计划），优化器知道参数的值。意味着优化器可以使用参数值去估计计划中每个步骤返回的行数。就好像参数的值被硬编码到批处理的文本中。这就是参数嗅探。

这是很有用的，因为如果优化器不知道参数的值，它将被迫去猜测返回的行数。基于平均统计和其他使用的元数据来尽可能准确地猜测，但是大多数时候仍然与真实行数相去甚远。错误的估计导致选择低效的执行计划并导致很差的性能。

例如，一个批处理第一次被执行，首先编译，因为对应计划在计划缓存中没有。在编译时，由于有参数嗅探，参数的值将被用来生成执行计划。当计划被创建时，计划被放在缓存中用来重用。下次相同的批处理被执行时，虽然有可能用了不同的参数，但是缓存中的计划仍将被重用。当然，第二次执行的结果将基于参数的值。但是执行第二次的计划是与第一次一样的，这个计划就是来自于第一次执行的参数。

如上图。在参数化批处理的实际执行计划的图形表示中，查看最外层的操作符属性（通常是一个select操作符），然后找到“Parameter List”属性。展开属性时，将会看到每个参数编译时和运行时的值。编译时的值就是参数嗅探用来生成计划的参数。运行时的值是实际在指定计划中的参数。

实际上，第二次执行可能是性能很差的，因为优化器在两次生成计划时估计返回数据的行数可能是相差大的。这里纯粹是运气，没有更好地方式。执行计划被参数的值决定，而我们不能控制它，因为不知道编译何时放生。如果计划对于大多数执行时很高效的，那么一切ok，但是如果它不是呢？假使用户用一个很少使用的参数来执行存储过程，这个参数值产生一个执行计划，并且对于指定该值作为参数的执行是非常高效的。但是其他参数时将会表现很糟糕。

因此问题来了：参数嗅探是好还是不好?

一如往常，答案就是：“看情况”。这取决于数据的分布。让我们看一下之前用的存储过程:

CREATE PROCEDURE
    Marketing.usp_CustomersByCountry
(
    @Country AS NCHAR(2)
)
AS

SELECT
    Id ,
    Name ,
    LastPurchaseDate
FROM
    Marketing.Customers
WHERE
    Country = @Country;
GO

这种情况下，使用参数@Country，来过滤行customers表的行数。如果大多数国家有差不多的行数，而且大多数执行使用了这些国家，那么参数嗅探是很好的事情，因为大多数情况执行计划是适用的，并且比不带参数嗅探的计划要好（未知参数）。另一方面，如果国家的值的分布不是均匀的，那么一个国家编码的参数很有可能对于其他国家的查询计划就是一个糟糕的选择，此时参数嗅探就是不好的事情了。

那么我们在参数嗅探是否有益这件事情上能做什么？下一章将介绍如何高效的使用参数嗅探。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/119726.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权正版激活】： 官方授权正版激活支持Jetbrains家族下所有IDE 使用个人JB账号...