一、概要

InnoDB 引擎对 FULLTEXT 索引的支持是 MySQL 5.6 新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。对于FULLTEXT索引的内容可以使用 MATCH()…AGAINST 语法进行查询。

为了在InnoDB驱动的表中使用FULLTEXT索引MySQL5.6引入了一些新的配置选项和 INFORMATION_SCHEMA 表。可以通过 innodb_ft_num_word_optimize 和 innodb_optimize_fulltext_only 选项控制 optimizetable 命令对 InnoDB FULLTEXT 索引的更新。

二、相关库表、相关配置选项

相关表

INFORMATION_SCHEMA 库中与 InnoDB 全文索引相关的表如下：

• INNODB_FT_CONFIG：显示一个InnoDB表的FULLTEXT索引及其相关处理的元数据。

• INNODB_FT_INDEX_TABLE：转化后的索引信息用于处理基于InnoDB表FULLTEXT索引的文本搜索。一般用于调试诊断目的。

• INNODB_FT_INDEX_CACHE：向含FULLTEXT索引的InnoDB表插入数据后新插入数据转后的索引信息。

• INNODB_FT_DEFAULT_STOPWORD：在InnoDB表上创建FULLTEXT索引所使用的默认停止字表。

• INNODB_FT_DELETED：记录了从InnoDB表FULLTEXT索引中删除的行。

• INNODB_FT_BEING_DELETED：用于记录正在被删除的信息，用于监控和调试目的。

使用 INNODB_FT_INDEX_TABLE、INNODB_FT_INDEX_CACHE、INNODB_FT_DELETED、表前需先配置 innodb_ft_aux_table 选项。

相关配置选项

• innodb_ft_aux_table：指定包含FULLTEXT索引的InnoDB表的的名称。

• innodb_ft_cache_size：创建一个InnoDB FULLTEXT索引时在内存中存储已解析文档的缓存大小。

• innodb_ft_enable_diag_print：是否开启额外的全文搜索诊断输出。

• innodb_ft_enable_stopword：是否开启停止字。

• innodb_ft_max_token_size：存储在InnoDB的FULLTEXT索引中的最大词长。

• innodb_ft_min_token_size：存储在InnoDB的FULLTEXT索引中的最小词长。

• innodb_ft_num_word_optimize：为InnoDB FULLTEXT索引执行OPTIMIZE操作每次所处理的词数。

• innodb_ft_server_stopword_table：含有停止字的表。

• innodb_ft_sort_pll_degree：为较大的表构建搜索索引时用于索引和记号化文本的并行线程数。

• innodb_ft_user_stopword_table：含有停止字的表。

• innodb_optimize_fulltext_only：改变OPTIMIZE TABLE语句对InnoDB表操作的方式。

相关的几个变量

-- 查询相关变量，ft就是FullText的简写
show variables like 'ft%';

• ft_boolean_syntax：改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引

• ft_max_word_len：最长的索引字符串，默认值为84，修改后必须重建索引文件

• ft_min_word_len：最短的索引字符串，默认值为4，修改后必须重建索引文件

• ft_query_expansion_limit：查询括展时取最相关的几个值用作二次查询

• ft_stopword_file：全文索引的过滤词文件

show variables like '%token%';

• ngram_token_size：以ngram解释器搜索全文索引时，指定的搜索字符数最小为2个字符（非字节）

重新建立索引命令：repair table tablename quick

三、全文搜索功能

MySQL全文索引的关键字为 FULLTEXT，目前可对 MyISAM 表和 InnoDB 表的 CHAR、VARCHAR、TEXT 类型的列创建全文索引。

全文索引同其他索引一样，可在创建表是由 CREATE TABLE 语句创建也可以在表创建之后用 ALTER TABLE 或者 CREATE INDEX 命令创建（对于要导入大量数据的表先导入数据再创建 FULLTEXT 索引比先创建索引后导入数据会更快）。

1、语法

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

• MATCH：内容为已建立FULLTEXT索引并要从中查找数据的列

• expr：为要查找的文本内容

• search_modifier：可选搜索类型。

search_modifier 取值有四种

• IN NATURAL LANGUAGE MODE（默认）：自然语言全文搜索。

• IN BOOLEAN MODE：布尔全文搜索。

• IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION：带查询扩展的自然语言全文搜索。

• WITH QUERY EXPANSION：等同于上一个

2、自然语言全文搜索

自然语言全文搜索是MySQL全文搜索的默认搜索方式，实现从一个文本集合中搜索给定的字符串。这里，文本集合指的是指由FULLTEXT索引的一个或者多个列建表，并给title，body字段加FULLTEXT索引。

从 MySQL 5.7 开始，MySQL内置了 ngram 全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

MySQL指定了最小字符长度，默认是4，必须要匹配大于4的才会有返回结果，可以用 SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度，也可以在mysql配置文件 my.ini 更改最小字符长度，方法是增加一行，比如：ft_min_word_len = 2 改完后重启mysql即可。

-- 新建表，建立全文索引，包含 title、body 两个字段
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 DEFAULT CHARSET=utf8;

-- 导入数据
INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('Hello World','Hello World 你好听风...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

示例1

-- 全文查询 title、body 两个字段
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

查询结果

语句查找到了包含指定内容的行。实际上，返回的行是按与所查找内容的相关度由高到低的顺序排列的。这个相关度的值由 WHERE 语句中的 MATCH (…) AGAINST (…) 计算所得，是一个非负浮点数。该值越大表明相应的行与所查找的内容越相关，0 表明不相关。该值基于行中的单词数、行中不重复的单词数、文本集合中总单词数以及含特定单词的行数计算得出。

-- 查询相关度评分
SELECT *, MATCH (title,body) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score FROM articles;

查询结果

可以看到，所得结果的第二列即为改行与查找内容的相关度。上例中所得结果的顺序就是按此相关度排列的。

示例2

既看到查找结果又了解具体的相关度，可用下述方法达成。

SELECT *, 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);

查询结果 

注意： 通过在查找部分和条件部分分别使用相同的 MATCH(…) AGAINST(…) 可以同时获取两方面的内容（不会增加额外开销，MySQL优化器知道两个 MATCH(…) AGAINST(..) 是相同的，只会执行一次该语句）

补充

默认情况下全文搜索大小写不敏感，但是可以通过为FULLTEXT索引列所使用的字符集指定一个特定的校对集来改变这种行为。

MATCH()函数中的列必须与FULLTEXT索引中的列相同。如 MATCH(title,body) 与 FULLTEXT(title,body)。若要单独搜索某列，如body列，则需另外单独为该列建全文索引 FULLTEXT(body)，然后用 MATCH(body) 搜索。

全文搜索不仅可以搜索类似 database 这样的单个的单词，还可以搜索句子，

全文搜索把任何数字、字母、下划线序列看作是单词，包含 ’，

FULLTEXT解析器自动移除首尾的’，如’aaa’bbb’被解析为aaa’bbb。

FULLTEXT解析器用“ ”（空格）、“,”（逗号）“.”（点号）作为默认的单词分隔符，因此对于不使用这些分隔符的语言如汉语来说FULLTEXT解析器不能正确的识别单词，对于这种情况需做额外处理。

全文搜索中一些单词会被忽略。首先是过短的单词，InnoDB全文搜索中默认为3个字符，MyISAM默认4个字符，可通过在创建FULLTEXT索引前改变配置参数来改变默认行为，对于InnoDB该参数为：innodb_ft_min_token_size，对于MyISAM为ft_min_word_len；

另外stopword列表中的单词会被忽略。stopword列表包含诸如“the”、“or”、“and”等常用单词，这些词通常被认为没有什么语义价值。MySQL由内建的停止字列表，但是可以所使用自定义的停止字列表来覆盖默认列表。

• 对于InnoDB控制停止字的配置参数为 innodb_ft_enable_stopword，innodb_ft_server_stopword_table，innodb_ft_user_stopword_table

• 对于MyISAM参数为ft_stopword_file。

文本集合和查询语句中的单词的权重由该单词在集合或语句中的重要性确定。单词在越多的行中出现则该单词的权重越低，因为这表明其在文本集合中的语义价值较小。反之权重越高。例1中提到的相关度计算也与此值有关。

3、布尔全文搜索

如果在 AGAINST() 函数中指定了 IN BOOLEN MODE 模式，则MySQL会执行布尔全文搜索。在该搜索模式下，待搜索单词前或后的一些特定字符会有特殊的含义。

• +：表明结果中必须

• -：表明结果中不能包含

• ：空字符表明后跟的单词是可选的，但出现的话会增加该行的相关性

• @distance：用于指定两个或多个单词相互之间的距离（以单词度量）需在指定的范围内

• >：用于增加后跟单词对其所在行的相关性的贡献

• <：用于降低后跟单词对其所在行的相关性的贡献

• ()：用于将单词分组为子表达式且可以嵌套

• ~：后跟单词对其所在行的相关性的贡献值为负；

• *：通配符，若为单词指定了通配符，那么即使该单词过短或者出现在了停止字列表中它也不会被移除

• ”：括在双引号中的短语指明行必须在字面上包含指定的短语，全文搜索将短语分割为词后在FULLTEXT索引中搜索。非字字符无需完全匹配，如 ”test phrase” 可以匹配含”test phrase”和 ”test phrase”的行，也匹配含”phrase test”的行。

示例

-- +”表示结果中必须包含“MySQL”
-- -”表明结果中不能含有“YourSQL”
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL-YourSQL' IN BOOLEAN MODE);

-- 包含MySQL或者YourSQL的行
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL YourSQL' IN BOOLEAN MODE);

-- 同时包含MySQL和YourSQL的行
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL+YourSQL' IN BOOLEAN MODE);

-- 必须包含MySQl的行，YourSQL可有可无，但有YourSQL会增加相关性
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE);

-- 必须包含MySQL的行，YourSQL可有可无，若出现了YourSQL则会降低其所在行的相关性
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE);

-- 必须同时包含MySQL以及Security或Optimizing的行，Security会增加所在行的相关性，而Optimizing会降低所在行的相关性
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL +(>Security <Optimizing)' IN BOOLEAN MODE);

-- 包含da*的行。如包含DataBase、database等
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('da*' IN BOOLEAN MODE);

-- 包含“MySQL Tutorial”短语的行
SELECT * FROM articles WHERE MATCH (title,body) AGAINST('"MySQL,Tutorial"' IN BOOLEAN MODE);

布尔全文搜索特点

• 停止字列表也适用于布尔全文搜索。

• 最小和最大词长全文搜索参数也适用于布尔全文搜索

• MyISAM中的布尔搜索在FULLTEXT索引不存在的时候仍可工作，但速度很慢。而InnoDB表的各类全文搜索必须有FULLTEXT索引，否则会出现找不到与指定列相匹配的FULLTEXT索引的错误

• InnoDB中的全文搜索不支持在单一搜索单词前使用多个操作符如“++MySQL”。MyISAM中全文搜索可以处理这种情况，但是会忽略除了紧邻单词之外的其他操作符。

4、查询扩展全文搜索

某些时候我们通过全文搜索来查找包含某方面内容的行，比如我们搜索“database”，实际上我们期望返回结果不仅仅是仅包含“database”单词的行，一些包含“MySQL”、“SQLServer”、“Oracle”、“DB2”、“RDBMS”等的行也期望被返回。这个时候查询扩展全文搜索就能大显身手。

通过在 AGAINST() 函数中指定 WITH QUERY EXPANSION 或者 IN NATURAL MODE WITH QUERY EXPANSION 可以开启查询扩展全文搜索模式。

扩展全文搜索工作原理

其实是执行两次搜索，第一次用给定的短语搜索，第二次使用给定的短语结合第一次搜索返回结果中相关性非常高的一些行进行搜索。

-- 使用查询扩展全文搜索
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION);

注意事项

因为查询扩展会返回一些不相关的内容，因此会显著的引入噪声。索引仅当要查询的短语较短时才在考虑使用查询扩展全文搜索。

四、全文搜索的限制

• 目前只有InnoDB和MyISAM引擎支持全文搜索。其中InnodB表对FULLTEXT索引的支持从MySQL5.6.4开始。

• 分区表不支持全文搜索。

• 全文索引适用于多数多字节字符集。例外情况比如 Unicode。

• 表意型语言如汉语、日语没有诸如空格之类的单词定界符。因此FULLTEXT解析器不能确定此类语言中词的起止。对于此种情况要特殊处理（比如将中文转换成一种单字节类似英文习惯的存储方式）。

• 允许在同一表中使用多种字符集，但FULLTEXT索引中的列必须使用同一字符集和校对集。

• MATCH()函数中的列必须与FULLTEXT索引中定义的列完全一致，除非是在MyISAM表中使用IN BOOLEAN MODE模式的全文搜索（可在没有建立索引的列执行搜索，但速度很慢）。

参考文档

• MySQL全文索引 FULLTEXT索引和like的区别：https://www.cnblogs.com/guifanbiji/p/6202195.html

• Adding a Collation to a Character Set：http://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

• Adding a Collation for Full-Text Indexing：http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html

未经允许请勿转载：程序喵 » MySQL 全文索引 FULLTEXT 使用