主页 / dba / 问题 / 39101
Accepted
Michael Fredrickson
Asked: 2013-04-03 15:39:24 +0800 CST

带有参数化 LIKE 的意外表扫描

  • 772

在对语句进行参数化时，我在 SQL Server 2005 上遇到了针对堆表的意外表扫描LIKE......但是当与变量相同的值被硬编码时，预期的索引查找就会发生。

这个问题只发生在这种特定情况下......所以我对如何解决这个问题并不感到困惑，我对为什么会发生这种情况感到困惑。

以下 T-SQL 应该会在 SQL Server 2005 上重现该问题：

IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50)
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY NONCLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT * 
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

Paul White 的这篇文章似乎与我密切相关，但结论/解释与我的具体问题不符。

任何见解表示赞赏。

sql-server sql-server-2005

1 个回答

  1. Best Answer

    它仅针对非聚集索引出现的说法是因为您只有两列 - 一个是索引列，另一个是

    1. 如果有聚簇索引，则为聚簇键（即另一列）
    2. 如果没有聚簇索引，则为 RID

    在第二种情况下，为了满足SELECT *查询的（所有列）部分，它必须执行昂贵的查找，因此选择执行 10,000* 记录表扫描的通用（稳健）计划。在第一种情况下，索引就是满足 SELECT 子句所需的全部内容。

    *应该注意的是，记录数和索引基数也在确定计划中发挥作用。

    对于更多的列，计划可预见地切换到参数化 LIKE 语句的 CLUSTERED INDEX SCAN，即使使用聚集索引，根据下面的修订测试。

    IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50),
    Othercolumn1 uniqueidentifier default (newid()),
    RowVer timestamp
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest (serialnumber) VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY CLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT * 
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

    以下是根据我修改后的表结构生成的计划。对于问题中的架构，顶部变为表扫描，底部变为 RID 查找而不是密钥查找 - 其他所有条件都相同。

    执行计划

    执行查询中成本较高的操作之一是首先构建执行计划。为了帮助解决这个问题，SQL Server 有一个计划缓存来存储语句的文本和相关的 SET 设置。使用不同 SET 设置的相同文本可能会导致不同的行为，因此需要重新规划并存储为单独的条目。

    非参数化查询很容易计划 - 它包含确切的文本“1234%”。SerialNumber 上的 VARCHAR 索引很容易搜索包含前缀“1234”的部分。SQL Server 还会估计查询的基数并且总是会为您的数据选择 INDEX SEEK 计划。向 SQL Server 进一步提供确切的查询语句（文本）将包含静态值“1234%”，并且可以安全地高效地重新执行相同的计划。

    另一方面，参数化查询存储到由语句文本键入的计划缓存（字典）中... WHERE SerialNumber LIKE @Criteria。尽管当前批次中的@Criteria 包含值“1234%”并且可以使用 INDEX SEEK，但另一个用户很有可能提交完全相同的查询并将其@Criteria设置为“%9”而不是使用INDEX SEEK + RID 查找。这将选择 10% 的数据，这些数据通常超过索引搜索不再有利的临界点。为了健壮性和可重用性，为该查询缓存（然后执行）的计划是表扫描版本，它将以@Criteria可能值的平均效率满足最广泛的值范围。

    • 5

相关问题