定义mysql索引
- 什么是索引？
- 什么是全文？
我知道这两个问题的答案，但我无法以准确的方式向面试官公开这些答案：
- 索引意味着类似书籍
- 全文中的索引意味着搜索字符串
可以请给我每个问题的非常简单的定义吗？
- What is indexing?
- What is full text?
I know the answers to both questions, but I can't expose those answers in the exact way to an interviewer:
- indexing means something like index in book
- fulltext means for search string
Can please give me very simple definition for each of these questions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
索引是创建索引的过程。索引是一种结构，它允许 SQL（或更一般地说是 DBMS 或搜索引擎）以一种非常有效的方式根据一个（或多个）的值来定位记录它们包含的字段。
例如，数据库可能包含包含学生记录的表，其中包括学生 ID、姓名、出生日期、电话号码……通过在电话号码上创建索引，我们可以根据电话号码搜索学生。在没有索引的情况下，系统会找到相同的记录，但该操作将通过查找每条记录并与所需的电话号码进行比较来进行。
全文索引是为一个（或多个包含文本的字段）创建索引的过程。与基于将字段的完整值（或可能是简单的正则表达式）与所需搜索值进行比较的常规索引不同，全文索引可以根据在字段中找到的单词来定位记录。
例如，书目数据库可能包含描述书籍的记录，其中包含 ISBN、作者、标题、类型、价格等字段。标题字段上的全文索引（有时称为“目录”）可以在通过“man”一词进行搜索时有效地定位标题为“老人与海”的书。
全文引擎通常对与文本相关的语言概念有内置的、可参数化的“理解”。例如，“干扰词”（也称为“停用词”）是文本中经常出现的词（例如英语中的“the”、“and”、“of”、“in”），可以被忽略，以便最小化索引大小并使使用更具选择性的单词进行搜索更加有效。此外，全文引擎可能会意识到单词的各种语法形式，例如单词的单数和复数形式（例如 Inch 和 Inches、Foot 和 Feet、Cat 和 Cats）或动词的变形（例如 Catch、Catching）以及捕获或解释、解释、解释）。由于这种语法意识，全文引擎可以（如果有指示）找到单词，即使它们与搜索条件不完全匹配。
全文引擎通常还公开搜索语言/语法，允许用户指定所需搜索的特定元素。例如，搜索单词“man”的 5 个单词内的单词“sea”。或者找到单词“Lake”或“Ocean”以及单词“water”等。
Indexing, is the process of creating Indexes. Indexes are structures which allow SQL (or more generally a DBMS or Search Engine) to locate, in a very efficient fashion, records based on the value of one (or several) of the fields they contain.
For example, a database may include at table containing student records, with their Student ID, their Name, Date of Birth, Phone Number ... By creating a index on the Phone Number, we can then search Student based on a phone number. In the absence of an index, the system would have found the same records, but this operation would have been carried by looking every single record and comparing with the desired phone number.
FullText Indexing is the process of creating a index for one (or several fields which contain text). Unlike with regular indexes which are based on comparing the complete value of the field (or possibly a simple regular expression) with the desired search value, a FullText index may locate a record based on words found within the field.
For example a bibliographic database may contain records describing books, with fields such as ISBN, Author, Title, Type, Price...). A fulltext index (sometimes called a "catalog") on the field Title, would allow to locate, efficiently, the book titled "The old man and the sea" when search by say the word "man".
Fulltext engines typically have a built in and parametrizable "understanding" of linguistic concepts pertaining to text. For example the "noise word" (also called "stopwords") are word frequently found in the text (example "the", "and", "of", "in", in English) may be ignored, for the purpose of minimizing the index size and making searches with more selective words more efficient. Also, fulltext engine may be aware of the various grammatical forms of a word, say the singular and plural form of words (as say Inch and Inches, Foot and Feet, Cat and Cats), or the conjugation of verbs (as Catch, Catching and Caught or Interpret, Interpreting, Interpreted). Thanks to this grammatical awareness, the FullText Engine can (if so instructed) locate words even if they do not match exactly the search criteria.
FullText engine also typically expose a search language/syntax which allows users to specify particular elements of the desired search. For example to search for the word "sea" within 5 words of the word "man". Or to find the word "Lake" or "Ocean" and the word "water" etc.
mysql 中的索引是从列中的每个值（或一组列中的值）到包含该列中的值（或一组列中的值）的行的映射。
列上的全文索引是从每个单词（通常用空格分隔）到包含该单词的行集的映射。
正常指标：
id:1“酒吧”
id:2“foo baz”
搜索“foo”->没有结果。
搜索“foo baz”-> id 为 2 的行
搜索“酒吧”-> id 为 1 的行
全文索引：
id:1“酒吧”
id:2“foo baz”
搜索“foo”-> id 为 2 的行
搜索“foo baz”-> id 为 2 的行
搜索“酒吧”-> id 为 1 的行
An index in mysql is a mapping from each value in a column (or values in a set of columns) to the rows containing that value in that column (or those values in the set of columns).
A full text index on a column is a mapping from each word (generally separated by whitespace) to the set of rows that contains that word.
Normal index:
id:1 "bar"
id:2 "foo baz"
search for "foo" -> no results.
search for "foo baz" -> row with id:2
search for "bar" -> row with id:1
Fulltext index:
id:1 "bar"
id:2 "foo baz"
search for "foo" -> row with id:2
search for "foo baz" -> row with id:2
search for "bar" -> row with id:1