全文索引_我的学习教程

创建索引
索引配置
全文搜索
索引介绍
停用词
全文搜索相关性排名
全文索引限制
ngram全文解析器

FULLTEXT索引是在基于文本的列（CHAR， VARCHAR或TEXT列）上创建的，以加快对这些列中包含的数据的查询和DML操作，而忽略定义为停用词的任何单词
InnoDB FULLTEXT索引由于具有缓存和批处理行为，因此具有特殊的事务处理特性。具体来说，FULLTEXT索引的更新和插入是在事务提交时处理的，这意味着FULLTEXT搜索只能看到提交的数据。
全文索引只能在InnoDB或MyISAM表中使用，并且只能用于创建CHAR， VARCHAR或 TEXT列。
MySQL提供了一个内置的全文ngram解析器，该解析器支持中文，日文和韩文；以及一个用于日语的可安装的MeCab全文解析器插件。
FULLTEXT索引定义可以在被给予CREATE TABLE的语句中创建表时，或者稍后使用ALTER TABLE或CREATE INDEX添加。
对于大型数据集，将数据加载到没有FULLTEXT索引的表中然后再创建索引要比将数据加载到具有现有FULLTEXT索引的表中快得多。
全文索引不能跨表，对于MyISAM表，可以在没有索引的情况下进行布尔搜索(尽管比较慢)，在这种情况下，可以命名多个表中的列

创建索引

mysql> CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200),
       FULLTEXT idx (opening_line)
       ) ENGINE=InnoDB;

重建全文索引

InnoDB

为了使修改生效，必须在修改以下全文索引变量后重建全文索引:innodb_ft_min_token_size;innodb_ft_max_token_size;innodb_ft_server_stopword_table;innodb_ft_user_stopword_table;innodb_ft_enable_stopword;ngram_token_size。修改innodb_ft_min_token_size、innodb_ft_max_token_size或ngram_token_size需要重启服务器。删除并重新创建每个索引

ALTER TABLE DROP INDEX
ALTER TABLE ADD INDEX

MyISAM

如果您修改影响索引的全文变量（ft_min_word_len， ft_max_word_len或 ft_stopword_file），或者更改了停用词文件本身，则必须FULLTEXT在进行更改并重新启动服务器后重新构建索引。

REPAIR TABLE tbl_name QUICK;

优化InnoDB全文索引

重建全文索引，删除已删除的文档ID，并在可能的情况下合并同一单词的多个条目

set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE table_name;

索引配置

搜索单词的最小长度和最大长度

InnoDB

innodb_ft_min_token_size
innodb_ft_max_token_size

MyISAM

ft_min_word_len
ft_max_word_len

全文搜索

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier:
{
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
}

MATCH()中的字段列表表示集合，AGAINST()中是查询的字符串和查询修饰符

IN NATURAL LANGUAGE MODE/未指定（自然语言搜索）

查询

将搜索字符串解释为自然人类语言中的短语，除双引号（"）字符外，没有特殊的运算符。适用停用词列表
对于表中的每一行，MATCH()返回一个相关值;表示搜索字符串与MATCH()列表中命名的列中的文本之间的相似度
用双引号(")字符括起来的短语只匹配按字面意思输入的包含短语的行，全文引擎将短语拆分为单词，并在FULLTEXT索引中对单词执行搜索。非单词字符不需要精确匹配:短语搜索仅要求匹配项包含与短语完全相同的单词，并且顺序相同（"test phrase" matchs "test, phrase"）；如果该短语不包含索引中的单词，则结果为空。如果所有单词都是停用词或比索引单词的最小长度短，则结果为空。
集合中和查询中的每个正确单词都将根据其在集合或查询中的重要性进行加权。因此，存在于许多文档中的单词的权重较低，因为在此特定集合中单词的语义值较低。相反，如果单词很少见，则其权重更高。单词的权重被组合以计算行的相关性。这项技术最适合大型收藏

搜索规则

默认情况下，搜索以不区分大小写的方式执行。对索引列使用二进制排序规则(如：latin1)会执行区分大小写的全文搜索
MATCH()函数中命名的列必须与表中某些FULLTEXT索引中包含的列相同

排序

相关性值高的排在前面。相关性值是非负浮点数。零相关性意味着没有相似性；相关性是根据行（文档）中单词的数量，行中唯一单词的数量，集合中单词的总数以及包含特定单词的行数量来计算的

短语解析

任何真实的单词字符(字母、数字和下划线,')序列将视为一个单词；但是每行中只能有一个',（aaa'bbb解析为1个单词，aaa''bbb解析为2个单词）；全文解析器会去除单词开头或结尾的撇号'aaa'bbb'将被解析为aaa'bbb
内置解析器通过查找特定分隔符（空格，逗号，句号）确定单词的开始和结束，没有分隔符（如中文）将无法确定单词的开始和结束。需要添加分隔符到内置解析器或使用ngram解析器插件（中文、日文或韩文）或MeCab解析器插件（日文）创建索引

查询忽略

太短的词都会被忽略，默认单词最小长度：InnoDB为3个字符，MyISAM为4个字符；通过innodb_ft_min_token_size(InnoDB)/ft_min_word_len(MyISAM)可以再创建索引前配置单词最小长度（配置项不适用于ngram解析器，ngram解析器的配置项为ngram_token_size）
停用词

MyISAM的局限性

对于很小的表，单词分布不能充分反映其语义值，并且此模型有时可能会为MyISAM表上的搜索索引产生奇怪的结果，例如，表的每一行中都有单词“MySQL”，但是在MyISAM搜索索引中搜索该单词不会产生任何结果。搜索结果为空，因为至少有50％的行中包含单词“MySQL”，因此有效地将其视为停用词。这种过滤技术更适用于大型数据集，在小型数据集中，您可能不希望结果集从1GB表返回第二行，而在小型数据集中，它可能导致流行术语的结果不佳

示例

mysql> CREATE TABLE articles (
          id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
          title VARCHAR(200),
          body TEXT,
          FULLTEXT (title,body)
        ) ENGINE=InnoDB;

简单地计算匹配项

mysql> SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

简单地计算匹配项，做了一些额外的工作（按相关性对结果进行排序），如果搜索匹配几行，则索引查找可能会更快

mysql> SELECT COUNT(*) FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

执行全表扫描，如果搜索词出现在大多数行中，这可能比索引查找更快

mysql> SELECT
    COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
    AS count
    FROM articles;
+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.03 sec)

查询返回相关性值，并且还按相关性递减的顺序对行进行排序，需要指定MATCH()两次，一次在 SELECT，一次在WHERE子句；但是MySQL优化器仅会调用一次进行全文搜索，不会造成额外的开销

mysql> SELECT id, body, MATCH (title,body) AGAINST
    ('Security implications of running MySQL as root'
    IN NATURAL LANGUAGE MODE) AS score
    FROM articles WHERE MATCH (title,body) AGAINST
    ('Security implications of running MySQL as root'
    IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+

IN BOOLEAN MODE(布尔搜索)

使用特殊查询语言规则解释搜索字符串。该字符串包含要搜索的单词；它还可以包含运算符，以使匹配行中的单词必须存在或不存在，或者其权重应高于或低于平常。某些常见单词（停用词）将从搜索索引中省略，并且如果出现在搜索字符串中则不匹配

查询

使用特殊查询语言规则解释搜索字符串。该字符串包含要搜索的单词；它还可以包含运算符，以使匹配行中的单词必须存在或不存在，或者其权重应高于或低于平常。
某些常见单词（停用词）将从搜索索引中省略，并且如果出现在搜索字符串中则不匹配
InnoDB表需要在MATCH()表达式的所有列上都有索引才能执行布尔查询。MyISAM即使没有FULLTEXT索引，布尔查询也可以工作，以这种方式执行的搜索会非常慢。

运算符使用

InnoDB不支持在单个搜索词上使用多个运算符。在单个搜索词上使用多个运算符将语法错误返回标准输出
InnoDB仅支持前导加号(+)或减号(-)，尾随运算符将报错
InnoDB不支持带通配符的前导加号('+*')、加号和减号组合('+-')或加号和减号组合的前导('+-apple')，使用将报错
InnoDB不支持使用@符号，@符号保留给@distance近似搜索操作符使用。
MyISAM忽略除紧邻搜索词的运算符之外的所有运算符。
InnoDB不使用适用于MyISAM搜索索引的50%阈值。

排序

不会按照相关性递减的顺序自动对行进行排序

查询忽略

最小和最大字长参数配置适用内置FULLTEXT解析器和MeCab解析器
停用词

运算符

无运算符（默认）该单词为可选，但包含该单词的行的评级较高，类似MATCH() ... AGAINST()没有IN BOOLEAN MODE修饰符的行为

'apple banana' 
查找至少包含两个单词之一的行。

+

前导或尾随加号表示此单词必须出现在返回的每一行中。InnoDB仅支持前导加号

'+apple +juice' 
查找包含两个单词的行。
'+apple macintosh'  
查找包含单词“apple”的行，但如果行中也包含“macintosh”，则将行排名更高 。

-

前导或尾随的负号表示此单词在返回的任何行中均不得出现。InnoDB仅支持前导减号。
-操作符的作用只是排除其他搜索条件匹配的行。
如果一个布尔模式搜索只包含前缀为-的项，那么它将返回一个空结果。

'+apple -macintosh' 
查找包含单词“apple”但不包含“macintosh”的行。

@distance

仅适用于InnoDB表，它测试两个或两个以上的单词之间的距离是否在distance之内，以字节为单位。紧接在@distance操作符之前的双引号字符串中指定搜索词。

MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE)

> <

这两个操作符用于更改一个单词对赋给一行的相关值的贡献。
>操作符增加相关性
<操作符减少相关性。

( )

括号将单词分组为子表达式。带括号的组可以嵌套

'+apple +(>turnover  <strudel)' 
查找包含单词“apple”和“turnover”，或“apple”和“strudel”(任意顺序)的行，
但“apple turnover”将排在“apple strudel”之前。

~

充当否定运算符，使单词对行的相关性的贡献为负。
这对于标记“噪音”字很有用。
包含这样一个单词的行评分低于其他行，但不会像使用-操作符时那样完全排除。

'+apple ~macintosh' 
查找包含单词“apple”的行，但如果该行还包含单词“macintosh”，则其相关性将低于不包含单词“macintosh”的行。
这比搜索“+apple -macintosh”更“柔和”，因为“-macintosh”的出现会导致该row根本不会返回

*

用作截断（或通配符）运算符。它被附加到受影响单词的后面。
如果单词以*运算符之前的单词开头，则单词匹配。
如果用截断运算符指定了一个单词，则不会从一个布尔查询中删除它，即使它太短或是个停止词。
通配符单词被认为是一个必须出现在一个或多个单词开头的前缀。如果最小单词长度是4，那么搜索“+word +the*”返回的行数可能比搜索“+word +the”返回的行数少，因为第二个查询忽略了太短的搜索词the。

'apple*' 
查找包含诸如“apple”， “apples”，“applesauce”或 “applet”之类的单词的行。

"

包含在双引号（"）字符中的短语仅与按字面意义包含该短语的行匹配。
全文引擎将短语分解为单词，并在FULLTEXT索引中搜索单词。
非单词字符不必完全匹配：短语搜索仅要求匹配项包含与短语完全相同的单词，并且顺序相同。例如， "test phrase"matchs "test, phrase"。
如果该短语不包含索引中的单词，则结果为空。由于多种因素，这些单词可能不在索引中：可能是停用词，或者比索引单词的最小长度短。
"字符是分隔短语的操作符。它们不是包含搜索字符串本身的引号

'"some words"'  
查找包含精确短语“some words”的行
例如，包含“some words of wisdom”而不是“some noise words”的行。

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION/WITH QUERY EXPANSION(查询扩展搜索)

对自然语言搜索的修改，盲目查询扩展(也称为自动相关性反馈)
当搜索词太短时，这通常很有用，这通常意味着用户依赖于全文搜索引擎所缺乏的隐含知识。例如，用户搜索 “database”可能实际上意味着 “MySQL”，“Oracle”，“DB2”和“RDBMS”都是应与“database”匹配的短语并且也应该返回。这就是隐含的知识。
它通过执行两次搜索来工作，第一次搜索字符串用于执行自然语言搜索。然后，将搜索返回的最高相关行中的单词添加到搜索字符串中，执行第二次搜索。
由于查询扩展往往会通过返回不相关的文档而显着增加噪声，因此仅在搜索短语较短时才使用它。

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' WITH QUERY EXPANSION);
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL      | In this tutorial we show ...             |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

索引内容

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES
       WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|      333 | test/FTS_0000000000000147_00000000000001c9_INDEX_1 |   289 |
|      334 | test/FTS_0000000000000147_00000000000001c9_INDEX_2 |   290 |
|      335 | test/FTS_0000000000000147_00000000000001c9_INDEX_3 |   291 |
|      336 | test/FTS_0000000000000147_00000000000001c9_INDEX_4 |   292 |
|      337 | test/FTS_0000000000000147_00000000000001c9_INDEX_5 |   293 |
|      338 | test/FTS_0000000000000147_00000000000001c9_INDEX_6 |   294 |
|      330 | test/FTS_0000000000000147_BEING_DELETED            |   286 |
|      331 | test/FTS_0000000000000147_BEING_DELETED_CACHE      |   287 |
|      332 | test/FTS_0000000000000147_CONFIG                   |   288 |
|      328 | test/FTS_0000000000000147_DELETED                  |   284 |
|      329 | test/FTS_0000000000000147_DELETED_CACHE            |   285 |
|      327 | test/opening_lines                                 |   283 |
+----------+----------------------------------------------------+-------+

前六个表代表反向索引，并称为辅助索引表。对传入文档进行标记时，各个单词（也称为 “标记”）与位置信息和关联的文档ID（DOC_ID）一起插入索引表中。根据单词第一个字符的字符集排序权重，单词在六个索引表中得到完全排序和分区。
反向索引分为六个辅助索引表，以支持并行索引创建。默认情况下，两个线程对索引表中的单词和相关数据进行标记，排序和插入。
辅助索引表名称以FTS_前缀和INDEX_*后缀。每个索引表都通过索引表名中的十六进制值与索引表关联，该值与索引表的table_id匹配。例如，例如，test/opening_lines表的table_id是327，其十六进制值是0x147
其他索引表称为通用索引表，用于删除处理和存储FULLTEXT索引的内部状态。与为每个全文索引创建的反向索引表不同，这组表对于在所有特定表上创建的全文索引是共有的。
即使删除了全文索引，也会保留公用辅助表。当删除全文索引时，将保留为索引创建的FTS_DOC_ID列，因为删除FTS_DOC_ID列将需要重新构建表。需要使用通用的axillary表来管理FTS_DOC_ID列。

InnoDB全文索引文档ID和FTS_DOC_ID列

InnoDB使用唯一的文档标识符（称为文档ID（DOC_ID））将全文索引中的单词映射到出现该单词的文档记录。映射需要在索引表上有一列FTS_DOC_ID。如果未定义列FTS_DOC_ID，则在创建全文索引时InnoDB自动添加一个隐藏的FTS_DOC_ID列。
自定义FTS_DOC_ID时，必须定义为BIGINT UNSIGNED NOT NULL并命名为FTS_DOC_ID （全部大写）（不需要定义为AUTO_INCREMENT列，但是AUTO_INCREMENT可以使加载数据更加容易。）
如果选择自己定义FTS_DOC_ID列，则您有责任管理该列，以免出现空值或重复值。FTS_DOC_ID值不能重复使用，这意味着FTS_DOC_ID值必须不断增加。
可以在FTS_DOC_ID列上创建所需的唯一索引FTS_DOC_ID_INDEX（全部大写）。如果未创建FTS_DOC_ID_INDEX，InnoDB则会自动创建。
当时用CREATE FULLTEXT INDEX或ALTER TABLE ADD FULLTEXT时，innodb将重建表并添加FTS_DOC_ID 列。并将返回相同的警告
与在装载了数据的表上添加FTS_DOC_ID列相比，create table时定义FTS_DOC_ID在时间上要快的多。

InnoDB全文索引缓存

插入文档后，将对其进行标记化，并将各个单词和关联的数据插入FULLTEXT索引。即使对于小型文档，此过程也可能导致大量小的数据插入辅助索引表，从而使同时访问这些表成为争执点。为避免此问题，InnoDB将使用FULLTEXT索引缓存来临时缓存最近插入的行的索引表插入。此内存中的高速缓存结构将保留插入，直到高速缓存已满，然后将其批量刷新到磁盘（至辅助索引表）。INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE表可以查询最近插入的行的标记化数据。
缓存和批处理刷新行为避免了对辅助索引表的频繁更新，这可能导致在繁忙的插入和更新期间并发访问问题。批处理技术还避免了同一单词的多次插入，并最大程度地减少了重复输入。代替单独刷新每个单词，对同一单词的插入进行合并，并作为单个条目刷新到磁盘，从而提高了插入效率，同时使辅助索引表尽可能小。
全文索引缓存存储与辅助索引表相同的信息。但是，全文索引缓存仅缓存最近插入的行的标记化数据。查询时，已刷新到磁盘（到全文索引辅助表）的数据不会带回到全文索引缓存中。直接查询辅助索引表中的数据，并将辅助索引表中的结果与全文索引缓存中的结果合并，然后再返回。
innodb_ft_cache_size变量用于配置全文索引缓存大小（基于每个表），这会影响刷新全文索引缓存的频率。
innodb_ft_total_cache_size变量为所有表定义全局全文索引高速缓存大小限制

停用词

使用服务器字符集和排序规则（character_set_server和 collation_server系统变量的值），加载停用词列表并搜索全文查询。如果用于全文索引或搜索的停用词文件或列的字符集或排序规则不同于character_set_server或collation_server ，则停用词查找可能会出现错误的命中或遗漏。
停用词查询的大小写敏感性取决于服务器的排序规则。例如，如果排序规则是latin1_swedish_ci，则查找不区分大小写，而如果排序规则是latin1_general_cs或latin1_bin，则查找是区分大小写的。

InnoDB搜索索引的停用词

InnoDB默认停用词的列表相对较短，因为来自技术，文学和其他来源的文档经常使用短词作为关键字或重要短语。例如，您可能搜索 “是或不是”，并期望得到明智的结果，而不是忽略所有这些词。

-- 查询停用词
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

自定义停用词

定义一个于INNODB_FT_DEFAULT_STOPWORD表结构相同的表，并使用停用词填充该表。设置innodb_ft_server_stopword_table=db_name/table_name，然后再创建全文索引。自定义的停用词表必须有一个名为value的VARCHAR列。通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE中的单词，验证指定的停用词('Ishmael')是否出现。如果要逐个表创建停用词列表，请创建其他停用词表，并使用innodb_ft_user_stopword_table=db_name/table_name指定要使用的停用词表，然后再创建全文索引

CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopwords(value) VALUES ('Ishmael');
CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200)
) ENGINE=InnoDB;
SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';
CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);

MyISAM搜索索引的停用词

如果character_set_server是ucs2、utf16、utf16le或utf32，则停用词文件将使用latin1进行加载和搜索。
ft_stopword_file变量可以覆盖MyISAM表的默认停用词列表，变量值应该是停用词列表文件的路径名或者是禁用停用词过滤的空字符串,向来对路径是在数据目录中查找停止词列表可以使用任何非字母数字字符(如换行符、空格或逗号)(除下划线字符(_)和单撇号('))分隔停止词
停用词列表的字符集是服务器的默认字符集。
在MySQL源代码发行版中，可以在storage/myisam/ft_static.c文件中找到停用词列表。

全文搜索相关性排名

InnoDB全文搜索以Sphinx全文搜索引擎为模型，使用的算法基于BM25和TF-IDF排名算法。
InnoDB布尔型全文搜索的相关度排名可能与MyISAM相关度排名不同
InnoDB使用“术语频率-逆文档频率” (TF-IDF)加权系统的变体对给定的全文搜索查询对文档的相关性进行排名。
TF-IDF权重是基于单词在文档中出现的频率，由单词在集合中所有文档中出现的频率抵消。单词在文档中出现的频率越高，在文档集合中出现的频率越低，文档的排名就越高

多个单词搜索的相关性排名

${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}

mysql> SELECT id, title, body, MATCH (title,body)  AGAINST ('mysql tutorial' IN BOOLEAN MODE)
    AS score FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+----------------------+
| id | title                        | body                                | score                |
+----+------------------------------+-------------------------------------+----------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |   0.7405621409416199 |
|  3 | Optimizing Your Database     | In this database tutorial ...       |   0.3624762296676636 |
|  5 | MySQL Security               | When configured properly, MySQL ... | 0.031219376251101494 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     | 0.031219376251101494 |
|  2 | How To Use MySQL             | After you went through a ...        | 0.015609688125550747 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        | 0.015609688125550747 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... | 0.015609688125550747 |
|  6 | Database, Database, Database | database database database          |                    0 |
+----+------------------------------+-------------------------------------+----------------------+

在第一个记录（id）中，“ mysql”出现一次，而“ tutorial”出现两次。
“ mysql”有六个匹配记录，“ tutorial”有两个匹配记录。
将这些值插入用于多字搜索的排名公式中时，
MySQL命令行客户端将返回预期的排名值：

mysql> SELECT (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2));
+-------------------------------------------------------+
| (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)) |
+-------------------------------------------------------+
|                                    0.7405621541938003 |
+-------------------------------------------------------+

全文索引限制

仅InnoDB和MyISAM表支持全文索引。
分区表不支持全文索引
全文索引可用于大多数多字节字符集。例外的是，对于Unicode，可以使用utf8/utf8mb4字符集，但不能使用ucs2/utf16/utf16le/utf32字符集。虽然不能使用ucs2/utf16/utf16le/utf32列上的全文索引，但您可以在没有全文索引的ucs2列上执行布尔模式搜索。
表意语言（例如中文和日语）没有单词定界符。因此，内置的全文分析器无法确定单词在这些语言和其他此类语言中的开头和结尾。InnoDB和MyISAM提供了与table一起使用的基于字符的ngram全文语法分析器，该语法支持中文，日语和韩语（CJK）;以及基于单词的MeCab语法分析器插件，该插件支持日语。
FULLTEXT索引中的所有列必须使用相同的字符集和排序规则
MATCH()列的列表必须与该表的某些全文索引定义中的列列表完全匹配，除了MyISAM表的布尔搜索模式。
对于MyISAM表，可以在非索引列上执行布尔模式搜索，它们可能会很慢。
AGAINST()的参数必须是在查询计算期间不变的字符串值。例如，这不能时数据表的列，因为每一行的数据可能不同
与非FULLTEXT搜索相比，FULLTEXT搜索的索引提示更加有限
对于InnoDB，所有涉及到有全文索引的列的DML操作(INSERT， UPDATE， DELETE)都在事务提交时处理。例如，对于一个INSERT操作，插入的字符串被标记化并分解为单个单词。然后在提交事务时将各个单词添加到全文索引表中。因此，全文搜索只返回已提交的数据。
全文索引不支持'%'字符。

ngram全文解析器

支持中文，日语和朝鲜语（CJK）的ngram全文语法解析器，支持与InnoDB和MyISAM一起使用。
ngram是内置的服务器插件。启动服务器时会自动加载该插件。
ngram是从一个给定的文本序列中提取的n个字符的连续序列。ngram解析器将文本序列标记为一个由n个字符组成的连续序列。例如，您可以使用ngram全文解析器将n的不同值标记为“abcd”。

n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'

配置ngram令牌大小

搜索单词包含文字数量，默认为2，最小为1，最大为10；较小的令牌大小将产生较小的全文搜索索引，并加快搜索速度

ngram_token_size

创建索引

mysql> CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body) WITH PARSER ngram
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;

解析

ngram解析器在解析时会消除空格,“ab cd”解析为“ab”， “cd”；“a bc”被解析为“bc”
ngram解析器不排除与停用词列表中的条目相等的令牌，而是排除包含停用词的令牌;例如，ngram_token_size=2将包含“a，b”的文档解析为“a，” 和“，b”。如果将逗号（“，”）定义为停用词，则“a，”和“，b”被排除在索引之外，因为它们包含逗号。
默认情况下，ngram解析器使用默认停用词列表，其中包含英语停用词列表。对于适用于中文，日语或韩语的停用词列表，必须创建自己的停用词。有关创建停用词列表的信息。长度大于ngram_token_size的停用词将被忽略。
对于自然语言模式搜索，搜索词将转换为ngram词的并集。例如，字符串“abc”（假设ngram_token_size=2）被转换为“ab bc”。给定两个文档，一个包含“ab”，另一个包含 “abc”，搜索词“ab bc”匹配两个文档。
对于布尔模式搜索，搜索项将转换为ngram短语搜索。例如，字符串“abc”（假设ngram_token_size=2）被转换为'“ab bc”'。给定两个文档，一个包含“ab”，另一个包含“abc”，搜索短语““ab bc””仅与包含“abc”的文档匹配。
如果通配符搜索的前缀词短于ngram令牌大小，则查询将返回所有包含以前缀词开头的ngram令牌的索引行。例如，假设ngram_token_size=2搜索“a*”将返回以“a”开头的所有行。
如果通配符搜索的前缀词长于ngram令牌大小，则该前缀词将转换为ngram短语，并且通配符运算符将被忽略。例如，假设ngram_token_size=2将“abc*”通配符搜索转换为“ab bc”。
短语搜索将转换为ngram短语搜索。例如，将搜索短语“abc”转换为“ab bc”，这将返回包含“abc”和“ab bc”的文档.
搜索短语“abc def”被转换为“ab bc de ef”，返回包含“abc def”和“ab bc de ef”的文档。不返回包含“abcdef”的文档。

重建全文索引

优化InnoDB全文索引

搜索单词的最小长度和最大长度

查询

搜索规则

排序

短语解析

查询忽略

MyISAM的局限性

示例

查询

运算符使用

排序

查询忽略

运算符

+

-

@distance

> <

( )

~

*

"

InnoDB全文索引文档ID和FTS_DOC_ID列

InnoDB全文索引缓存

InnoDB搜索索引的停用词

自定义停用词

MyISAM搜索索引的停用词

相关性排名的计算方式

多个单词搜索的相关性排名

配置ngram令牌大小

创建索引

解析