主页 / dba / 问题 / 193238
Accepted
jericzech
Asked: 2017-12-16 07:58:05 +0800 CST

神秘的条件 where 子句索引选择

  • 772

你有没有解释，为什么查询优化器在这个例子中选择不同的索引和模式？

/* crete objects and data for testing */

-- table
CREATE TABLE #Test (
    ID INT IDENTITY PRIMARY KEY
    ,CustNo INT NULL
    ,CustNo2 INT NULL
    );

-- populate with data
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO #Test (CustNo)
SELECT TOP (1000) n FROM Nums ORDER BY n;

-- index on CustNo
CREATE NONCLUSTERED INDEX IX__#Test__CustNo ON #Test (CustNo ASC);

/* running test */

-- variables
DECLARE @Step INT = 1;
DECLARE @FindNo INT = 5;

-- #1 - uses CX index scan
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo);

-- #2 - uses NCX index seek
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo)
OPTION (RECOMPILE);

-- #3 - uses NCX index seek
IF @Step = 1
    SELECT CustNo
    FROM #Test
    WHERE CustNo = @FindNo;
sql-server t-sql

1 个回答

  1. Best Answer

    你看到的是不断折叠。

    此链接的更多信息：

    常量折叠是优化器用来删除任何不必要的代码以帮助提高性能的技术。常量折叠通过在编译计划之前删除不必要的变量并简化查询来实现这一点。

    OPTION (RECOMPILE) 允许在第二个查询中不断折叠 @Step 和 @FindNo。由于已知@Step 等于 1，因此该行将OR (@Step = 2 AND CustNo2 = @FindNo)被优化掉。请注意，这会影响估计的行数。

    如果你想看到一个更激烈的例子，试试下面的代码。请注意常量折叠（由选项重新编译启用）如何允许第二个查询计划完全避免合并，因为优化器知道无法返回任何行。

    CREATE TABLE #t (
id int)

INSERT #t
VALUES (1)

DECLARE @v char(5) = 'false'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'
OPTION (RECOMPILE)
    • 8

相关问题