SQL Server:如何优化“like”查询?

I have a query that searches for clients using "like" with wildcard. For example:

我有一个查询，使用“like”和通配符搜索客户端。例如:

SELECT TOP (10) 
       [t0].[CLIENTNUMBER], 
       [t0].[FIRSTNAME], 
       [t0].[LASTNAME], 
       [t0].[MI], 
       [t0].[MDOCNUMBER]
  FROM [dbo].[CLIENT] AS [t0]
 WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
   AND ([t0].[FIRSTNAME] LIKE '%John%') 
   AND ([t0].[LASTNAME] LIKE '%Smith%') 
   AND ([t0].[SSN] LIKE '%123%') 
   AND ([t0].[CLIENTNUMBER] LIKE '%123%') 
   AND ([t0].[MDOCNUMBER] LIKE '%123%') 
   AND ([t0].[CLIENTINDICATOR] = 'ON')

It can also use less parameters in "where" clause, for example:

它还可以在“where”子句中使用较少的参数，例如:

SELECT TOP (10) 
       [t0].[CLIENTNUMBER], 
       [t0].[FIRSTNAME], 
       [t0].[LASTNAME], 
       [t0].[MI], 
       [t0].[MDOCNUMBER]
  FROM [dbo].[CLIENT] AS [t0]
 WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
   AND ([t0].[FIRSTNAME] LIKE '%John%') 
   AND ([t0].[CLIENTINDICATOR] = 'ON')

Can anybody tell what is the best way to optimize performance of such query? Maybe I need to create an index? This table can have up to 1000K records in production.

谁能告诉我优化这种查询性能的最佳方式是什么?也许我需要创建一个索引?此表在生产中可以有多达1000K的记录。

2 个解决方案

#1

To do much for a LIKE where the pattern has the form '%XXX%', you want to look up SQL Server's full-text indexing capability, and use CONTAINS instead of LIKE. As-is, you're doing a full table scan, because a normal index won't help with a search for an item that starts with a wild card -- but a full-text index will.

要想在模式有“%XXX%”的状态下做很多事情，您需要查询SQL Server的全文索引功能，并使用CONTAINS而不是LIKE。按原样进行全表扫描，因为普通索引无法帮助搜索以通配符开头的项——但全文索引可以。

/* ... */
 WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
   AND (contains([t0].[FIRSTNAME], 'John')) 
   AND (contains([t0].[LASTNAME], 'Smith')) 
   AND (contains([t0].[SSN], '123'))
   AND (contains([t0].[CLIENTNUMBER],'123')) 
   AND (contains([t0].[MDOCNUMBER], '123')) 
   AND ([t0].[CLIENTINDICATOR] = 'ON')

#2

Looks like some databases (PostgreSQL 7.1+, MySQL v3.23.23+, Microsoft-SQL v???, ) already contains such things:

看起来像一些数据库(PostgreSQL 7.1+、MySQL v3.23.23+、Microsoft-SQL v???，)已包含以下内容:

MySQL>> ALTER TABLE articles ADD FULLTEXT(body, title);
MySQL>> SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP')

MS-SQL>> SELECT ProductName FROM Products WHERE FREETEXT (ProductName, 'spread' )

PgSQL>> CREATE FUNCTION fti() RETURNS opaque AS '/path/to/fti.so' LANGUAGE 'C';
PgSQL>> CREATE TABLE articles_fti (string type, id oid);
....

Oracle..., Sybase...