全文搜索与标准数据库搜索 [英] Fulltext search vs standard database search

问题描述

我想知道全文搜索（搜索文件中的数据）和标准数据库搜索（如LIKE，IN等等）之间的区别。

What I want to know what is difference between fulltext searching (searching data in files) and standard database searching (LIKE, IN ect).

在这里我注意到了什么是在文件中您没有数据类型，但是在数据库中您可以为特定数据定义数据类型。

What I notice here is that in files you don't have data types, but in database you can define data types for particular data.

我对哪种搜索速度更快以及两者的优缺点都感兴趣。

I am interested in what kind of search is faster and what are advantages and disadvantages of both.

谢谢。

推荐答案

全文搜索有一些优点。

索引：

类似的东西：

WHERE Foo LIKE '%Bar';

无法利用索引。它必须查看每一行，看看是否匹配。但是，全文索引可以。实际上，全文索引可以在匹配词的顺序，这些词在一起的紧密程度等方面提供更大的灵活性。

Cannot take advantage of an index. It has to look at every single row, and see if it matches. A fulltext index, however, can. In fact, fulltext indexes can offer a lot more flexibility in terms of the order of matching words, how close those words are together, etc.

词干分析：

全文搜索可以 stem 个单词。如果搜索 run ，则可以获得运行或运行的结果。多数全文引擎具有多种语言的主干词典。

A fulltext search can stem words. If you search for run, you can get results for "ran" or "running". Most fulltext engines have stem dictionaries in a variety of languages.

加权结果：

全文索引可以包含多个列。例如，您可以搜索桃派，并且索引可以包括标题，关键字和正文。与标题匹配的结果可以加权更高，相关性更高，并且可以排序以显示在顶部附近。

A fulltext index can encompass multiple columns. For example, you can search for "peach pie", and the index can include a title, keywords, and a body. Results that match the title can be weighted higher, as more relevant, and can be sorted to show near the top.

缺点：

全文索引可能巨大，比标准B-TREE索引大很多倍。因此，许多提供数据库实例禁用或至少收取额外费用的托管提供程序。例如，上次我检查过，Windows Azure不支持全文查询。

A fulltext index can potentially be huge, many times larger than a standard B-TREE index. For this reason, many hosted providers who offer database instances disable this feature, or at least charge extra for it. For example, last I checked, Windows Azure did not support fulltext queries.

全文索引的更新也可能较慢。如果数据变化很大，与标准索引相比，更新索引可能会有些滞后。

Fulltext indexes can also be slower to update. If the data changes a lot, there might be some lag updating indexes compared to standard indexes.

这篇关于全文搜索与标准数据库搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！