SQL 中的 LIKE 解决方法(性能问题) [英] LIKE work-around in SQL (Performance issues)

问题描述

我一直在阅读，发现使用 LIKE 会导致查询速度大幅减慢.

I've been reading around and found that using LIKE causes a big slowdown in queries.

同事推荐我们使用

Select Name
From mytable
a.Name IN (SELECT Name 
           FROM mytable
           WHERE Name LIKE '%' + ISNULL(@Name, N'') + '%' 
           GROUP BY Name)

代替

Select Name
From mytable
a.Name LIKE '%' + ISNULL(@Name, N'') + '%'

现在我不是 SQL 专家，我不太了解这些语句的内部工作原理.这是一个更好的选择，值得为每个 like 语句输入几个额外的字符吗?有没有更好(也更容易打字)的替代方案?

Now I'm no SQL expert and I don't really understand the inner workings of these statements. Is this a better option worth the effort of typing a few extra characters with each like statement? Is there an even better (and easier to type) alternative?

推荐答案

有几个性能问题需要解决...

There are a couple of performance issues to address...

不要将子查询用于无需引用同一表的其他副本即可完成的条件.如果由于使用聚合函数(MAX、MIN 等)而需要表副本中的数据，这是可以接受的，尽管分析函数(ROW_NUMBER、RANK 等)可能更适合(假设支持).

Don't use a subquery for criteria that can be done without the need for referencing additional copies of the same table. It's acceptable if you need data from a copy of the table due to using aggregate functions (MAX, MIN, etc), though analytic functions (ROW_NUMBER, RANK, etc) might be more accommodating (assuming supported).

如果您的参数为 NULL，这意味着您想要比较的列的任何值，请不要包含过滤条件.类似这样的声明:

If your parameter is NULL, and that means that you want any value for the columns you are comparing against, don't include filtration criteria. Statements like these:

WHERE a.Name LIKE '%' + ISNULL(@Name, N'') + '%'

...保证优化器必须比较 name 列的值，无论是否通配符.在 LIKE 的情况下更糟糕的是，在评估的左侧使用通配符可确保如果正在搜索的列上存在索引，则无法使用索引.

...guarantee the optimizer will have to compare values for the name column, wildcarding or not. Worse still in the case with LIKE is that wildcarding the left side of the evaluation ensures that an index can't be used if one is present on the column being searched.

性能更好的方法是:

IF @Name IS NOT NULL 
BEGIN
   SELECT ...
     FROM ...
    WHERE a.name LIKE '%' + @Name + '%'
END
ELSE 
BEGIN
   SELECT ...
     FROM ...
END

性能良好的 SQL 就是根据您的需要量身定制.这就是为什么您在查询具有两个或多个独立条件的查询时应该考虑使用动态 SQL.

Well performing SQL is all about tailoring to exactly what you need. Which is why you should be considering dynamic SQL when you have queries with two or more independent criteria.

当您检查文本数据中是否存在字符串时，LIKE 运算符在搜索文本方面效率不高.全文搜索 (FTS) 技术旨在解决以下缺点:

The LIKE operator isn't very efficient at searching text when you're checking for the existence of a string within text data. Full Text Search (FTS) technology was designed to address the shortcomings:

IF @Name IS NOT NULL
BEGIN
   SELECT ...
     FROM ...
    WHERE CONTAINS(a.name, @Name) 
END
ELSE
BEGIN
   SELECT ...
     FROM ...
END

始终进行测试和测试比较

我同意 LittleBobbyTables - 该解决方案最终依赖于检查所有替代方案的查询/执行计划，因为表设计和数据会影响优化器决策表现.在 SQL Server 中，子树成本最低的那个是最有效的，但如果不维护表统计信息和索引，它会随着时间而改变.

Always Test & Compare

I agree with LittleBobbyTables - the solution ultimately relies on checking the query/execution plan for all the alternatives because table design & data can impact optimizer decision & performance. In SQL Server, the one with the lowest subtreecost is the most efficient, but it can change over time if the table statistics and indexes aren't maintained.

这篇关于SQL 中的 LIKE 解决方法(性能问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！