如用索引，CHARINDEX不？

Question

这个问题与我的老问题有关。执行以下查询需要10至15秒：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0) 

在一些文章中，我看到使用CAST和CHARINDEX将不会从索引中获益。还有一些文章说使用LIKE '%abc%'不会从索引中获益，而LIKE 'abc%'则会：

http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where https://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries http://www.sqlservercentral.com/Forums/Topic186262-8-1.aspx#bm186568

在我的例子中，我可以将查询重写为：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'

此查询提供与前一个查询相同的输出。我已经为列Phone no创建了一个非聚集索引。当我执行这个查询时，它只需1秒就能运行。与之前的14秒相比，这是一个巨大的变化。

LIKE '%123456789%'如何从索引中获益？

为什么上市的文章说它不会提高性能？

我尝试重写查询以使用CHARINDEX，但是性能仍然很慢。为什么CHARINDEX不能像LIKE查询那样从索引中获益？

使用CHARINDEX查询：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

执行计划：

使用LIKE查询：

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'

执行计划：

Answer 1

“%123456789%”如何从索引中受益？

只有一点点。查询处理器可以扫描整个非聚集索引，寻找匹配项，而不是整个表(聚集索引)。非聚集索引通常比它们构建的表小，因此扫描非聚集索引可能更快。

缺点是，查询所需的、未包含在非聚集索引定义中的任何列都必须在每一行的基表中查找。

优化器根据成本估算，在扫描表(聚集索引)和使用查找扫描非聚集索引之间做出决定。估计的成本在很大程度上取决于优化器希望您的LIKE或CHARINDEX谓词选择多少行。

为什么上市的文章说它不会提高性能？

对于不以通配符开头的LIKE条件，Server可以执行索引的部分扫描，而不是扫描整个内容。例如，仅通过测试索引记录LIKE 'A%和< 'B' (确切的边界值取决于排序规则)，就可以正确地评估>= 'A'。

这类查询可以使用b-树索引的查找能力:我们可以使用b-树直接进入第一条记录>= 'A'，然后按索引键顺序向前扫描，直到到达< 'B'测试失败的记录为止。因为我们只需要将LIKE测试应用于较少的行数，所以性能通常更好。

相反，不能将LIKE '%A转换为部分扫描，因为我们不知道从何处开始或结束；任何记录都可能以'A'结尾，因此我们无法提高扫描整个索引和单独测试每一行的能力。

我尝试重写查询以使用CHARINDEX__，但是性能仍然很慢。为什么CHARINDEX没有从索引中受益，因为它看起来像查询一样？

在这两种情况下，查询优化器在扫描表(聚集索引)和扫描非聚集索引(有查找)之间有相同的选择。

在成本估算的基础上，对两者进行了选择。因此，Server可能会为这两种方法产生不同的估计值。对于查询的LIKE形式，估计可以使用特殊的字符串统计信息来生成合理准确的估计。CHARINDEX > 0表单根据猜测生成一个估计值。

不同的估计值足以使优化器为CHARINDEX选择聚集索引扫描，并为LIKE选择带查找的NonClustered索引扫描。如果强制CHARINDEX查询使用带有提示的非聚集索引，您将得到与LIKE相同的计划，性能将大致相同：

SELECT
    [Customer name],
    [Sl_No],
    [Id]
FROM dbo.customer WITH (INDEX (f))
WHERE 
    CHARINDEX('9000413237', [Phone no]) >0;

运行时处理的行数对于这两种方法都是相同的，只是LIKE表单在本例中会产生更精确的估计，所以查询优化器选择了更好的计划。

如果您发现自己经常需要LIKE %thing%搜索，那么您可能想考虑一下我在Server中的Trigram通配符字符串搜索中写过的一种技术。

Answer 2

Server以试一试的形式维护字符串列中的子字符串统计信息，这些字符串列可由LIKE查询使用，但CHARINDEX不能使用。

有关此问题的更多信息，请参见字符串汇总统计部分。

几个重要的注意事项是，任何通配符的转义都必须使用专有的平方括号技术，而不是ESCAPE关键字，对于超过80个字符的字符串，只使用前40个和最后40个字符。

WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

将只对30%的行将返回的不等式谓词使用标准猜测。

LIKE查询(在您的例子中)估计与谓词匹配的行数可能会少得多。

请注意，前面的通配符仍然会阻止索引查找。一个完整的索引仍然被扫描，但是它使用的是一个比聚集索引更窄的索引。更窄的索引并不涵盖查询所使用的所有列，因此第二个计划需要一个键查找才能检索缺少的列。

根据30%的估计，这一计划极不可能被选择。Server将考虑更便宜地扫描整个聚集索引并避免那么多查找。有关其他示例，请参阅本文在临界点上的内容。