LIKE '%...' 如何查找索引? [英] How can LIKE '%...' seek on an index?

问题描述

我希望这两个 SELECT 具有相同的执行计划和性能.由于 LIKE 上有一个前导通配符，我希望进行索引扫描.当我运行它并查看计划时，第一个 SELECT 的行为符合预期(通过扫描).但是第二个 SELECT 计划显示了索引查找，并且运行速度提高了 20 倍.

I would expect these two SELECTs to have the same execution plan and performance. Since there is a leading wildcard on the LIKE, I expect an index scan. When I run this and look at the plans, the first SELECT behaves as expected (with a scan). But the second SELECT plan shows an index seek, and runs 20 times faster.

代码:

-- Uses index scan, as expected:
SELECT 1
    FROM AccountAction
    WHERE AccountNumber LIKE '%441025586401'

-- Uses index seek somehow, and runs much faster:
declare @empty VARCHAR(30) = ''
SELECT 1
    FROM AccountAction
    WHERE AccountNumber LIKE '%441025586401' + @empty

问题:

当模式以通配符开头时，SQL Server 如何使用索引查找?

How does SQL Server use an index seek when the pattern starts with a wildcard?

奖励问题:

为什么连接空字符串会改变/改进执行计划?

Why does concatenating an empty string change/improve the execution plan?

详情:

• Accounts.AccountNumber
上有一个非聚集索引
• 还有其他索引，但搜索和扫描都在这个索引上.
• Accounts.AccountNumber 列是一个可为空的 varchar(30)
• 服务器是 SQL Server 2012

• There is a non-clustered index on Accounts.AccountNumber
• There are other indexes, but both the seek and the scan are on this index.
• The Accounts.AccountNumber column is a nullable varchar(30)
• The server is SQL Server 2012

表和索引定义:

CREATE TABLE [updatable].[AccountAction](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [AccountNumber] [varchar](30) NULL,
    [Utility] [varchar](9) NOT NULL,
    [SomeData1] [varchar](10) NOT NULL,
    [SomeData2] [varchar](200) NULL,
    [SomeData3] [money] NULL,
    --...
    [Created] [datetime] NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE NONCLUSTERED INDEX [IX_updatable_AccountAction_AccountNumber_UtilityCode_ActionTypeCd] ON [updatable].[AccountAction]
(
    [AccountNumber] ASC,
    [Utility] ASC
)
INCLUDE ([SomeData1], [SomeData2], [SomeData3]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


CREATE CLUSTERED INDEX [CIX_Account] ON [updatable].[AccountAction]
(
    [Created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

注意:这是两个查询的实际执行计划.对象的名称与上面的代码略有不同，因为我试图让问题保持简单.

NOTE: Here is the actual execution plan for the two queries. The names of the objects differ slightly from the code above because I was trying to keep the question simple.

推荐答案

这些测试(数据库 AdventureWorks2008R2)显示了会发生什么:

These tests (database AdventureWorks2008R2) shows what happens:

SET NOCOUNT ON;
SET STATISTICS IO ON;

PRINT 'Test #1';
SELECT  p.BusinessEntityID, p.LastName
FROM    Person.Person p
WHERE   p.LastName LIKE '%be%';

PRINT 'Test #2';
DECLARE @Pattern NVARCHAR(50);
SET @Pattern=N'%be%';
SELECT  p.BusinessEntityID, p.LastName
FROM    Person.Person p
WHERE   p.LastName LIKE @Pattern;

SET STATISTICS IO OFF;
SET NOCOUNT OFF;

结果:

Test #1
Table 'Person'. Scan count 1, logical reads 106
Test #2
Table 'Person'. Scan count 1, logical reads 106

SET STATISTICS IO 的结果表明 LIO 是相同.但是执行计划完全不同:

The results from SET STATISTICS IO shows that LIO are the same. But the execution plans are quite different:

在第一个测试中，SQL Server 使用一个 Index Scan 显式，但在第二个测试中 SQL Server 使用一个 Index Seek，它是一个 Index Seek - range扫一扫.在最后一种情况下，SQL Server 使用 Compute Scalar 运算符来生成这些值

In the first test, SQL Server uses an Index Scan explicit but in the second test SQL Server uses an Index Seek which is an Index Seek - range scan. In the last case SQL Server uses a Compute Scalar operator to generate these values

[Expr1005] = Scalar Operator(LikeRangeStart([@Pattern])), 
[Expr1006] = Scalar Operator(LikeRangeEnd([@Pattern])), 
[Expr1007] = Scalar Operator(LikeRangeInfo([@Pattern]))

而且，Index Seek 操作符使用 Seek Predicate(优化)来进行范围扫描(LastName > LikeRangeStart ANDLastName ) 加上另一个未优化的 Predicate (LikeRangeEnd).

and, the Index Seek operator use an Seek Predicate (optimized) for a range scan (LastName > LikeRangeStart AND LastName < LikeRangeEnd) plus another unoptimized Predicate (LastName LIKE @pattern).

LIKE '%...' 如何在索引上查找?

How can LIKE '%...' seek on an index?

我的回答是:它不是真正的"Index Seek.这是一个 Index Seek - range scan，在这种情况下，它具有与 Index Scan 相同的性能.

My answer: it isn't a "real" Index Seek. It's a Index Seek - range scan which, in this case, has the same performance like Index Scan.

另请参阅 Index Seek 和 Index Scan 之间的区别(类似的争论):所以...是搜索还是扫描?.

Please see, also, the difference between Index Seek and Index Scan (similar debate): So…is it a Seek or a Scan?.

编辑 1:OPTION(RECOMPILE) 的执行计划(请参阅 Aaron 的建议)还显示了一个 Index Scan(而不是索引搜索):

Edit 1: The execution plan for OPTION(RECOMPILE) (see Aaron's recommendation please) shows, also, an Index Scan (instead of Index Seek):

这篇关于LIKE '%...' 如何查找索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！