弹性搜索全文与mysql全文? [英] Elastic search full text vs mysql full text?

问题描述

我正在尝试在laravel应用中实现搜索功能.由于数据安全性问题，我的主管不希望使用Angolia.除此之外，一个不错的选择是实现弹性搜索，另一个选择是使用来自mysql的全文搜索.我不确定有什么优点和缺点，尽管我在一些地方读到弹性搜索应该是更好的选择，但是我想了解自己的工作，因为在此之前我还没有从事过搜索工作./p>

我正在查看MySQL要求，看来我只能使用InnoDb引擎以5.6或更高版本实现它.除此之外，我只能在char和text type字段上实现全文索引.我不确定弹性搜索中允许使用哪种字段.

我知道我必须要做些类似的事情:

  DB :: statement("ALTER TABLE帖子添加全文搜索(标题，正文)")； 

然后我想我会做类似的事情:

  SELECT * FROM帖子在哪里比赛(标题，正文)反对(自然语言模式下为"foo")； 

是否有雄辩的实现方式?还是必须使用DB Facade手动进行?如果没有Eloquent实现，那么是否存在具有此功能的第三方软件包?

除此之外，我实际上还必须在相关表中进行搜索，例如注释和标签等.跨表如何创建索引还是不可能?如果无法在MySQL的各个表之间创建索引，那么ES中有什么可以克服的.

使用MySQL全文搜索与弹性搜索相比，我可能还会遇到哪些其他利弊?

解决方案

使用MySQL，您将始终在索引和搜索数据.

使用ElasticSearch，您可以更灵活地索引一个单位.您可以将一个项目的所有内容注释和标签都放入一个项目中，然后将其放在ES中.

您还可能会发现，与MySQL相比，ES通常会提供更好的性能和更好的结果.您还可以更灵活地处理同义词和权重之类的内容.

但这确实意味着您需要维护另一个堆栈，并且必须管理内容的索引编制和更新.

因此，这取决于您的数据大小和搜索作为一项功能的重要性.

我建议您从MySql文本搜索开始，因为在一个简单的情况下，它会很快设置，如果不能满足您的需求，请升级到弹性搜索.您至少将具有稻草人"功能，可用于进一步完善您的搜索要求.

I am trying to implement search functionality in my laravel app. Angolia is not preferred by my supervisors due to data security problems. Other than that one good option is to implement elastic search and another is to use full text search from mysql. I am not sure what are the pros and cons and although I have read in a few places that elastic search should be the better alternative, I would like to understand what I am working with since I haven't worked with searching before this.

I am looking at MySQL requirements and it seems I can only implement it with 5.6 or above with InnoDb engine. And other than that I can only implement fulltext indexes on char and text type fields. I am not sure what kind of fields are permitted in Elastic search.

I know I have to something like:

DB::statement('ALTER TABLE posts ADD FULLTEXT search(title, body)');

Then I guess I do something like:

SELECT * FROM posts
WHERE MATCH (title, body)
AGAINST ('foo' IN NATURAL LANGUAGE MODE);

Is there Eloquent implementation for this or do I have to do this manually using the DB facade? If there isn't a Eloquent implementation, are there 3rd party packages with this functionality?

Other than that, I actually have to search in related tables too like comments and tags and so on. How is it possible to create indexes across tables or is it not possible? Is there anything in ES in which I can over come this if it is not possible to create indexes across tables in MySQL.

What are the other pros and cons I might face using MySQL full text searches against elastic search?

解决方案

With MySQL you will always be indexing and searching your data.

With ElasticSearch you have more flexibility in what you index as one unit. You could take all of content comments and tags for an item and put it in ES as one item.

You'll also likely find that ES will give better performance and better results in general that you would get with mysql. You also have more flexibility with things like synonyms and weighting.

But it does mean you have another stack to maintain and you have to manage indexing and updating of content.

So it will depend on your data size and the importance of search as a feature.

I would suggest that you start with MySql text search, as in a simple case it will be quick to set up and if this does not provide what you need then upgrade to elastic search. You will at least have a straw man feature which can be used to further refine your search requirements.

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