主页 / dba / 问题 / 175123
Accepted
irimias
Asked: 2017-06-02 01:39:28 +0800 CST

当值为 null 时，对 WHERE 语句中变量的行为感到困惑

  • 772

我有一个像（SQLServer 2008）这样的表：

CREATE TABLE [dbo].[my_test_table](
    [productId] [int] NOT NULL,
    [purchaseId] [bigint] NOT NULL,
    (some other columns....),
 CONSTRAINT [PK_my_test_table] PRIMARY KEY CLUSTERED 
(
    [productId] ASC,
    [purchaseId] ASC
))

大约有 1000 万行。

我想要一个返回产品总行数的查询，或者如果产品未分类，则返回所有产品的总行数。就像是：

declare @productId int

set @productId = 320

select count(*)
from my_test_table t with(nolock)
where productId = @productId
or @productId is null

问题是查询比等效查询花费更多时间：

select count(*)
from my_test_table t with(nolock)
where productId = 320
or 320 is null

我们如何解释这种行为？

以下是执行计划： 在此处输入图像描述

sql-server sql-server-2008

3 个回答

  1. Best Answer

    考虑这个查询：

    select count(*)
from my_test_table t
where null is null;

    优化器在那里进行搜索是否有意义？没有什么可反对的。试图用提示强制它会引发错误：

    select count(*)
from my_test_table t with (forceseek)
where null is null;

    消息 8622，级别 16，状态 1，第 15 行

    由于此查询中定义的提示，查询处理器无法生成查询计划。在不指定任何提示且不使用 SET FORCEPLAN 的情况下重新提交查询。

    对于您现在拥有的查询，您可能需要根据参数值进行索引查找。但是，索引查找并非对所有可能的参数值都有效，因此您的缓存计划不能包含查找。此外，当查询优化器构建计划时，它不会首先检查局部变量的值。

    您表示查询很复杂，因此最好的解决方案可能是至少升级到 SQL Server 2008 R2 SP2，这样您就可以利用参数嵌入优化 (PEO)。结合OPTION (RECOMPILE)提示，查询优化器可以在执行计划之前嗅探局部变量的值。查询计划不能被另一个查询重用。

    让我们看看它的实际效果。以下查询的实际查询计划显示索引查找：

    declare @productId int = 320;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

    在此处输入图像描述

    以下的实际查询计划显示了一次扫描：

    declare @productId int = NULL;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

    在此处输入图像描述

    如果升级不是一个选项，我想你可以试试这个：

    select *
from my_test_table t 
where productId = @productId
and @productId is not null

UNION ALL

select *
from my_test_table t
where @productId is null;

    这可以根据局部变量的值进行查找或扫描，但可能会产生意想不到的副作用。

    如果这也不可接受，我认为您唯一的选择是使用另一个答案中已经涵盖的动态 SQL。

    • 5 
  2. select count(*) from my_test_table t with(nolock) where productId = 320
or 320 is null

    由于320 is null总是返回 false，优化器足够聪明，可以删除“ or 320 is null”

    所以上面的语句等同于

    select count(*) from my_test_table t with(nolock) where productId = 320

    productid 是 CI，而且 productid 是足够有选择性的索引，因此优化器决定使用 Index Seek。

    select count(*) from my_test_table t with(nolock) where productId = @productId
or @productId is null

    假设@productid=2（最初）

    即使那样，Estimated number of rows & Actual number of rows is always maximum即使您为变量提供值。所以优化器决定始终进行索引扫描。

    select count(*) from my_test_table t with(nolock) where productId = @productId
    or @productId is null
    OPTION (RECOMPILE);

    这里的计划每次都会改变。所以当@productid 不为空时，它将是Index Seek其他索引扫描。

    但是假设你的搜索频率非常高，那么重复的重新编译会增加服务器的过载。

    在现实生活中，如果您的查询真的像上面那样，那么您可以使用 if else

     if(@productid is not null)
    select count(*) from my_test_table t with(nolock) where productId = @productId

else 
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'my_test_table' AND (index_id < 2)

    如果您的搜索查询非常复杂也将非常频繁地使用，那么您可以使用“sp_executesql”进行参数化动态 sql。我不认为 sp_executesql 有任何像“Sql 注入”这样的限制。为了克服“动态 sql 中的 sql 注入危害”，我们应该使用 sp_executesql。

    您应该在另一个线程中共享您的复杂查询。

    • 1

  3. SQL Server 尝试生成可重用的查询执行计划，当您再次执行相同的查询时，该计划可以立即运行。所以它不能使用@productId IS NULL 这一事实，因为您将使用@productId NOT NULL 运行相同的查询。我不知道你是否可以直接阻止它被考虑，尽管你可以控制计划缓存和重用。

    我通过使用“动态 SQL”来解决这种情况，其中 SQL 代码在 nvarchar(max) 变量中构造，然后从该变量执行，如果您使用 sp_executesql，则将变量作为参数。此方法有局限性和危险（“SQL 注入”），但对于这种情况，您可以使 @productId 条件仅在 @productId 不为 NULL 时出现在查询中。所以实际上这两种情况有两种不同的查询。

    SET @workstring = N'SET @count = (SELECT COUNT(*) FROM my_test_table ' +
    CASE WHEN ( @productId IS NOT NULL ) THEN N'WHERE ( productId = @testThis )' 
         ELSE N'' END
EXEC sp_executesql @workstring, N'@count int OUTPUT, @testThis int',
    @count OUTPUT, @productId

    对于更长的复杂语句，我声明了一个包含令牌标记的布局精美的 SQL 的长 varchar(max)，例如在本例中为 @{where_condition}，然后我使用 REPLACE() 将这些更改为代码实际需要的内容，在本例是 CASE 语句的结果。我可能会在执行前检查字符串中是否还有剩余的 '@{' ！

    • 1

相关问题