全文搜索与 LIKE

发布于 2024-10-02 22:16:23 字数 219 浏览 9 评论 0原文

我的问题是关于使用全文搜索。据我所知，以 % 开头的 like 查询从不使用索引：

SELECT * from customer where name like %username%

如果我对此查询使用全文，我可以获得更好的性能吗？ SQL Server 能否利用全文索引优势进行查询如%username%？

原文

My question is about using full text search. As I know like queries which begin with % never use index :

SELECT * from customer where name like %username%

If I use fulltext for this query can I take better performance? Can SQL Server use fulltext index advantages for queries like %username%?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

攀登最高峰 2024-10-09 22:16:23

简短回答

在 SQL Server 中，无论是在索引列上使用 LIKE 还是使用全文索引，都没有有效的方法来执行中缀搜索。

长答案

在一般情况下，没有相当于 LIKE 运算符的全文。虽然 LIKE 适用于字符串，并且可以对目标内的任何内容执行任意通配符匹配，但根据设计，全文仅对整个单词/术语进行操作。（这是一个轻微的简化，但它可以满足此答案的目的。）

SQL Server 全文确实支持带有前缀术语运算符的 LIKE 子集。从文档（http://msdn.microsoft.com/en-us/library /ms187787.aspx)：

SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');

将返回名为链锯、链邮件等的产品。从功能上讲，与标准 LIKE 运算符（LIKE 'Chain%'），并且只要列已建立索引，使用 LIKE 进行前缀搜索应该会提供可接受的性能。

LIKE 运算符允许您将通配符放在任何地方，例如 LIKE '%chain'，正如您所提到的，这会阻止使用索引。但对于全文，星号只能出现在查询词的末尾，因此这对您没有帮助。

使用 LIKE，可以通过创建新列、将其值设置为与目标列相反的值并对其建立索引来执行高效的后缀搜索。然后您可以按如下方式查询：

SELECT Name
FROM Production.Product
WHERE Name_Reversed LIKE 'niahc%'; /* "chain" backwards */

它返回名称以“chain”结尾的产品。

我想你可以将前缀和反向后缀 hack 结合起来：

SELECT Name
FROM Production.Product
WHERE Name LIKE 'chain%'
AND Name_Reversed LIKE 'niahc%';

它实现了（可能）索引中缀搜索，但它并不是特别漂亮（而且我从未对此进行过测试以查看查询优化器是否会在其计划中使用这两个索引）。

Short answer

There is no efficient way to perform infix searches in SQL Server, neither using LIKE on an indexed column, or with a fulltext index.

Long answer

In the general case, there is no fulltext equivalent to the LIKE operator. While LIKE works on a string of characters and can perform arbitrary wildcard matches against anything inside the target, by design fulltext operates upon whole words/terms only. (This is a slight simplification but it will do for the purpose of this answer.)

SQL Server fulltext does support a subset of LIKE with the prefix term operator. From the docs (http://msdn.microsoft.com/en-us/library/ms187787.aspx):

SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');

would return products named chainsaw, chainmail, etc. Functionally, this doesn't gain you anything over the standard LIKE operator (LIKE 'Chain%'), and as long as the column is indexed, using LIKE for a prefixed search should give acceptable performance.

The LIKE operator allows you to put the wildcard anywhere, for instance LIKE '%chain', and as you mentioned this prevents an index from being used. But with fulltext, the asterisk can only appear at the end of a query term, so this is of no help to you.

Using LIKE, it is possible to perform efficient postfix searches by creating a new column, setting its value to the reverse your target column, and indexing it. You can then query as follows:

SELECT Name
FROM Production.Product
WHERE Name_Reversed LIKE 'niahc%'; /* "chain" backwards */

which returns products with their names ending with "chain".

I suppose you could then combine the prefix and reversed postfix hack:

SELECT Name
FROM Production.Product
WHERE Name LIKE 'chain%'
AND Name_Reversed LIKE 'niahc%';

which implements a (potentially) indexed infix search, but it's not particularly pretty (and I've never tested this to see if the query optimizer would even use both indexes in its plan).

回复收藏 0 原文

扛刀软妹 2024-10-09 22:16:23

您必须了解索引是如何工作的。索引就像百科全书的枯木版一样。

如果您使用：

SELECT * from customer where name like username%

全文索引或无全文索引应该有效。但

SELECT * from customer where name like %username%

永远不会与索引一起使用。这将是一个耗时的查询。

You have to understand how index is working. Index is the very same like the dead-wood edition of encyclopedia.

If you use:

SELECT * from customer where name like username%

The index, in fulltext or no fulltext should work. but

SELECT * from customer where name like %username%

will never work with index. and it will be time-consuming query.

回复收藏 0 原文

无敌元气妹 2024-10-09 22:16:23

Like 和 contains 非常不同 -

取以下数据值

“john smith”
“萨姆·史密斯”
“约翰·富勒”

喜欢“s%”
“萨姆·史密斯”

喜欢“%s%”
“约翰·史密斯”
'sam smith'

包含 's'

包含 'john'
“约翰·史密斯”
“约翰·富勒”

包含“s*”
“约翰·史密斯”
'sam smith'

contains s 返回与 contains s* 相同的结果 - 最初的星号被忽略，这有点痛苦，但索引是单词 - 而不是字符

回复收藏 0 原文

私野 2024-10-09 22:16:23

根据我对全文索引的了解，我将做出以下推断：

索引时，它会解析文本，搜索单词（某些 RDBMS，如 MySQL，只考虑长度超过 3 个字符的单词），并将单词放入索引中。
当您在全文索引中搜索时，您会搜索单词，然后链接到该行。
如果我对前两个（对于 MSSQL）的看法是正确的，那么只有当您搜索长度为 4 个或更多字符的 WORDS 时，它才会起作用。如果您寻找“椅子”，它不会找到“扶手椅”。

假设所有这些都是正确的，我将继续做出以下声明：全文索引实际上是一个索引，它使搜索速度更快。它很大，并且搜索的可能性比 LIKE 少，但速度要快得多。

回复收藏 0 原文

哽咽笑 2024-10-09 22:16:23

您可以使用：

SELECT * from customer where CONTAINS(name, 'username')

或

SELECT * from customer where FREETEXT(name, 'username')

You can use:

SELECT * from customer where CONTAINS(name, 'username')

SELECT * from customer where FREETEXT(name, 'username')

回复收藏 0 原文

折戟 2024-10-09 22:16:23

https://stackoverflow.com/users/289319/mike-chamberlain，你的建议是非常正确的不足以搜索 'chain' WHERE Name LIKE 'chain%'
AND Name_Reversed LIKE 'niahc%' 不等于 like'%chain%'****