CHARINDEX和LIKE搜索在性能方面非常不同，为什么？

Question

我们使用Entity Frameworks进行数据库访问，而当我们“想到”LIKE语句时 - 它实际上生成CHARINDEX内容。因此，这里是两个简单的查询，我将它们简化以证明我们在某些服务器上的观点：

-- Runs about 2 seconds
SELECT * FROM LOCAddress WHERE Address1 LIKE '%1124%' 
-- Runs about 16 seconds
SELECT * FROM LOCAddress WHERE ( CAST(CHARINDEX(LOWER(N'1124'), LOWER([Address1])) AS int)) = 1

目前，表中包含约10万条记录。Address1是一个VarChar(100)字段，没有什么特别之处。

下面是两个计划的片段并排放置。它看起来毫无意义，显示50%和50%，但执行时间却为1:8

我在网上搜索了一下，一般的建议是使用CHARINDEX代替LIKE。但我们的经验恰恰相反。我的问题是，这是什么原因，并且我们如何在不改变代码的情况下解决它？

- katit

---

请查看这个其他的SO问题 - 你可以使用数据注释来告诉EF，这一列是一个VARCHAR，并使其在实际上不是nvarchar时停止将该列解释为nvarchar... - marc_s

Answer 1

由于很难找到正确的答案，而且我在 SQL Server 2012 执行计划输出中发现了问题，因此我将自己的问题回答。如您在原始问题中看到的，表面上一切看起来都很正常。这是 SQL Server 2008。

当我在 2012 上运行相同的查询时，CHARINDEX 查询出现警告。问题在于 - SQL Server 必须进行类型转换。 Address1 是 VarChar，而查询使用的是 Unicode 或 NVarChar 的 N'1124'。如果我把查询更改为以下内容：

SELECT * 
FROM LOCAddress 
WHERE (CAST(CHARINDEX(LOWER('1124'), LOWER([Address1])) AS int)) 

然后它会像LIKE查询一样运行。 因此，由Entity Framework生成器引起的类型转换导致了性能的可怕下降。

- katit

---

我也想提到它，但我更喜欢指出CHARINDEX和LIKE的T-SQL视角。不过，我很高兴你已经自己解决了问题 :). 你应该将你的回复标记为答案，以供未来读者参考！ - Anuj Tripathi

Answer 2

首先，您可以看到两个查询都是相同的，都不能使用索引。CHARINDEX和LIKE在使用通配符时执行相同的操作。例如：%YourValue%。然而，当您使用像'YourValue%'这样的通配符时，它们的性能会有所不同。在这种情况下，LIKE运算符可能比CHARINDEX更快，因为它可以允许部分扫描索引。

现在，在您的情况下，两个查询都是相同的，但由于以下可能的原因，它们的性能有所不同：

统计信息：SQL Server维护子字符串在字符串列中的统计信息，这些统计信息被LIKE运算符使用，但对于CHARINDEX来说并不完全可用。在这种情况下，LIKE运算符将比CHARINDEX更快。 您可以使用适当的表提示强制SQL Server使用索引来为CHARINDEX使用索引。

例如：FROM LOCAddress WITH (INDEX (index_name))

阅读更多这里，其中在“字符串摘要统计”部分中说：

SQL Server 2008包含用于估计LIKE条件选择性的专利技术。它构建了一个字符列（字符串总结）的子字符串频率分布的统计摘要。这包括text、ntext、char、varchar和nvarchar类型的列。使用字符串摘要，SQL Server可以准确地估计模式中可能有任意数量的通配符以及任何组合的LIKE条件的选择性。

- Anuj Tripathi

---

仅在前缀扫描时，LIKE才会使用索引。这与选择性无关。前缀扫描实际上是从前缀开始并按字母表顺序结束于其后一个单词的范围搜索。这使得服务器可以使用索引执行类似于“where field>='YourValue' and field<'YourValuf'”的操作。 - Panagiotis Kanavos