MySQL 什么是全文索引，我应该什么时候使用它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3645746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is a fulltext index and when should I use it?
提问by cdxf
As the title states, what is a fulltext index and when should I use it?
正如标题所说，什么是全文索引，我应该什么时候使用它？
采纳答案by Alex
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.
在数据库中，索引通常用于在查找 where 子句中定义的内容时提高性能。然而，当涉及到过滤某些文本时，例如使用类似WHERE TextColumn LIKE '%searchstring%'then 的搜索是很慢的，因为常规数据库索引的工作方式是针对与列的“全部内容”而不只是其中的一部分进行匹配而优化的。特别是包含通配符的 LIKE 搜索不能使用任何类型的索引。
As mentioned in the comment below MySQL needs the MATCH () ... AGAINSTsyntax to search within a fulltext index; BTW this varies depending on the database vendor. In MS SQL you can use CONTAINSso keep this in mind when you plan to support other databases too.
正如下面的评论中提到的，MySQL 需要MATCH () ... AGAINST在全文索引中搜索的语法；顺便说一句，这取决于数据库供应商。在 MS SQL 中，您可以使用，CONTAINS因此当您计划也支持其他数据库时，请记住这一点。
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.
全文索引更适用于常规文本，因为它们针对这些类型的列进行了优化。非常简化：他们将文本拆分为单词，并对单词而不是整个文本进行索引。在查找特定单词时，这对于文本搜索的工作速度要快得多。
回答by Daniel Bingham
A full text index is an index you apply in a MySQL database to text fields that you plan to run a full text search on. A full text search uses the match(field) against('text')syntax. If you want to run a full text search you must have a full text index on the columns you'll be running it against.
全文索引是您在 MySQL 数据库中应用于计划运行全文搜索的文本字段的索引。全文搜索使用该match(field) against('text')语法。如果您想运行全文搜索，您必须在要运行的列上有一个全文索引。
There are three types of Full Text searches. I'll quote the manual, because I think it says it best:
全文搜索分为三种类型。我会引用手册，因为我认为它说得最好：
A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as “some” or “then” are stopwords and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search. For more information, see Section 11.9.2, “Boolean Full-Text Searches”.
A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given.
A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 11.9.3, “Full-Text Searches with Query Expansion”.
布尔搜索使用特殊查询语言的规则来解释搜索字符串。该字符串包含要搜索的词。它还可以包含指定要求的运算符，例如匹配行中必须存在或不存在某个词，或者它的权重应高于或低于通常情况。诸如“some”或“then”之类的常用词是停用词，如果出现在搜索字符串中则不匹配。IN BOOLEAN MODE 修饰符指定布尔搜索。有关更多信息，请参阅第 11.9.2 节，“布尔全文搜索”。
自然语言搜索将搜索字符串解释为自然人类语言中的短语（自由文本中的短语）。没有特殊的运算符。停用词列表适用。此外，出现在 50% 或更多行中的单词被认为是常见的并且不匹配。如果没有给出修饰符，全文搜索是自然语言搜索。
查询扩展搜索是对自然语言搜索的修改。搜索字符串用于执行自然语言搜索。然后将搜索返回的最相关行中的单词添加到搜索字符串中，然后再次进行搜索。查询返回第二次搜索中的行。WITH QUERY EXPANSION 修饰符指定查询扩展搜索。有关更多信息，请参阅第 11.9.3 节，“具有查询扩展的全文搜索”。
For more information take a gander at the Full Text Search Reference Page.
有关更多信息，请查看全文搜索参考页。
