什么是全文索引，什么时候应该使用它? [英] What is a fulltext index and when should I use it?

问题描述

如标题所述，什么是全文索引，什么时候应该使用它?

As the title states, what is a fulltext index and when should I use it?

推荐答案

在数据库中，当查找where子句中定义的内容时，通常使用索引来提高性能.但是，当涉及到过滤某些文本时，例如使用WHERE TextColumn LIKE '%searchstring%'之类的东西，搜索速度很慢，因为常规数据库索引的工作方式已优化，可匹配列的全部内容"，而不仅仅是部分内容.特别是，包含通配符的LIKE搜索不能使用任何类型的索引.

In databases indices are usually used to enhance performance when looking for something defined in your where clause. However when it comes to filtering some text, e.g. using something like WHERE TextColumn LIKE '%searchstring%' then searches are slow, because the way regular database indices work are optimized for matches against the 'whole content' of a column and not just a part of it. In specific the LIKE search which includes wildcards can not make use of any kind of index.

如下面的注释中所述，MySQL需要MATCH () ... AGAINST语法在全文索引中进行搜索；顺便说一句，这取决于数据库供应商.在MS SQL中，您可以使用CONTAINS，因此在计划也支持其他数据库时，请记住这一点.

As mentioned in the comment below MySQL needs the MATCH () ... AGAINST syntax to search within a fulltext index; BTW this varies depending on the database vendor. In MS SQL you can use CONTAINS so keep this in mind when you plan to support other databases too.

全文索引对于常规文本更好地工作，因为它们针对这些类型的列进行了优化.非常简化:他们将文本分成单词，并在单词而非整个文本上建立索引.查找特定单词时，这对于文本搜索来说要快得多.

Fulltext indices work better for regular text, because they are optimized for these type of columns. Very simplified: They split the text into words and make an index over the words and not the whole text. This works a lot faster for text searches when looking for specific words.

这篇关于什么是全文索引，什么时候应该使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！