mysql全文索引的使用

在mysql 5.6之前的版本，MyISAM引擎支持全文索引，但是InnoDB不支持；从mysql 5.6开始，InnoDB支持全文索引，但是不支持中文分词；从mysql 5.7.6开始，mysql内置了ngram分词器，可支持中文、日语等亚洲语种的分词。

全文索引fulltext index，可用于char/varchar/text列。在需要进行模糊查询时，如果使用 like “%target%” 进行查询，无法使用到索引。如果文本较大，为了优化效率，可以考虑全文索引。

本文将依次介绍以下内容：

分词与倒排索引，了解全文索引的基本原理
mysql分词器配置与全文索引的创建
全文索引的使用示例

分词与倒排索引

我们在数据结构学习时就了解到，倒排索引的典型应用场景是搜索引擎。为了快速的根据文本找出包含此文本的文档，那么需要解决两个基本的问题：

对文档进行分词，找出每个文档包含的“关键字”，称为token。比如：存在一个ID=1的文档内容为“倒排索引的典型应用场景是搜索引擎”，存在的一种分词结果可以是 “倒排索引/的/典型/应用/场景/是/搜索引擎”，如此经过分即能得到这一段文本的关键字
构建倒排索引表，记录关键字-所属文档的这一基本的关联关系，以及：关键字出现次数，即词频；关键字在文档中所处的位置等。接上条，将会存在 “倒排索引” -> (ID = 1) 这一索引记录

对于分词，这是一个算法领域的问题，鄙人不敢说有所了解，大家可以自行网上学习。所幸这并不影响我们的使用层面，了解达到的效果即可。

如此，构建出倒排索引表后，一个检索过程包含的基本步骤有：

对每条输入，我们对输入的内容进行分词，比如：全文索引，分词得到”全文”、”索引”两个token
在倒排索引表中进行匹配，查找出token匹配到的所有文档
根据匹配结果，可以找出匹配到token最多的文档；同时可以根据token在文档中的词频评估文档与token的相关度
通过一系列评估、排序算法得出文档最终的相关性score，得到最终结果

倒排索引对大量文本情况下关键字到文档的检索非常有效。但是构建的过程是比较复杂的（对比正向索引）；关键的是在文档更新时倒排索引表的更新会很复杂，对现有倒排索引表更新的逻辑复杂，重建倒排索引表虽然逻辑相对而言简单，但是代价高。

mysql分词器配置与全文索引的创建

mysql的全文索引即是倒排索引的应用。

在建表时可以指定添加全文索引：

CREATE TABLE `test_fulltext_idx_tbl` (
  `id` bigint(20) NOT NULL COMMENT '文档ID',
  `doc_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '文档名',
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '文档内容',
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT KEY `ft_search_index` (`doc_name`,`content`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='全文索引测试表'

指定了全文索引在mysql 5.7以上使用ngram分词器

但是如上对倒排索引的分析，倒排索引的更新复杂、代价高，因此在一个频繁修改的字段上使用全文索引会影响性能。因此，相比于建表同时创建索引、然后导入数据，更为高效的方式是建表时不添加全文索引、在数据导入完成后再添加全文索引：

1	ALTER TABLE test_fulltext_idx_tbl ADD FULLTEXT ft_search_index(doc_name, content) WITH parser ngram;

对于ngram，需要关注的是mysql的ngram_token_size这一配置。ngram_token_size表示ngram分词器分词的最小字数，默认为2，此时使用全文索引匹配单个字是无法匹配成功的。

这一配置可以在mysql的cnf文件中设置：

1 2	[mysqld] ngram_token_size=2

因为中文词最少包含两个字，因此一般使用默认值2即可。

全文索引的使用示例

全文索引使用的语法为：

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

其中，MATCH指定的字段必须与使用的全文索引的字段一致，expr即检索的关键字，search_modifier可指定两种查询模式：

IN NATURAL LANGUAGE MODE，默认的模式，自然语言模式，根据分词模糊匹配
IN BOOLEAN MODE，布尔模式，可以在表达式中使用操作符，如 ‘+能力 -审核”，表示必须存在“能力”、必须不存在“审核”

在上文的测试表test_fulltext_idx_tbl中添加测试数据并对doc_name、content两个字段创建全文索引

INSERT INTO `test_fulltext_idx_tbl` (`id`, `doc_name`, `content`) VALUES (1, '入门指南', '赋能开放平台目前已接入了各种技术能力，各个子应用根据以下步骤即可快速接入：
创建应用
登录赋能开放平台门户；
依次点击控制台，应用列表，创建应用；
填写应用名称，应用描述等信息，按需勾选能力；
点击立即创建，等待管理员审核完成，一般在1个工作日内审核完成。
获取访问Token
依次点击控制台，应用列表；
在我的应用列表中，复制APP ID和Secret Key；
根据api鉴权文档，获取访问Token。
调用能力接口
在http请求header中access-token，请求能力的OpenAPI。');
INSERT INTO `test_fulltext_idx_tbl` (`id`, `doc_name`, `content`) VALUES (2, '应用审核不通过常见原因', '审核过程中常见的驳回原因及处理方式：
应用名称含有敏感词
修改应用名称后重新提交申请。

应用描述信息不完善
重新编辑、完善描述信息');
INSERT INTO `test_fulltext_idx_tbl` (`id`, `doc_name`, `content`) VALUES (3, '人工智能', '1. 人工智能的定义
人工智能(AI)是指通过普通计算机程序来呈现人类智能的技术。AI是现代计算创新的支柱，可为个人和企业挖掘价值。AI是一个广博的领域，涵盖许多不同的学科，包括计算机科学、数据分析和统计、硬件和软件工程、语言学、神经学，甚至哲学和心理学。在业务使用的操作层面上，AI是一组主要基于机器学习和深度学习的技术，用于数据分析、预测、对象分类、自然语言处理、推荐、智能数据检索等等。

2. 人工智能的优势
2.1 自动化
AI可以自动执行工作流和流程，也可以不依靠人工团队来独立自主地开展工作。例如，AI可以通过持续监控和分析网络流量来帮助自动执行信息安全的各个方面。同样，智能工厂可能使用数十种不同类型的AI，例如机器人使用计算机视觉在工厂车间移动或检查产品是否存在缺陷、创建数字孪生体，或使用实时分析来衡量效率和产量。

2.2 减少人为错误
AI可以通过每次都遵循相同流程的自动化功能和算法来消除数据处理、分析、制造装配和其他任务中的人为错误。

2.3 消除重复任务
AI可用于执行重复任务，从而让人力资源能够空出手来解决影响较大的问题。AI可用于自动执行流程，例如验证文档、转录电话或回答“你们几点关门？”之类的简单客户问题。机器人通常用于代替人类执行“枯燥、肮脏或危险”的任务。

2.4 快速准确
与人类相比，AI可以更快地处理更多信息，从而查找模式并发现人类可能错过的数据关系。

2.5 无限可用性
AI不受时段、休息需求或其他人类负担的限制。在云端运行时，AI和机器学习可以“始终开启”，从而持续处理分配的任务。

2.6 更快的研发速度
快速分析大量数据的能力可以加快获得研发突破的速度。例如，AI已用于潜在新药物疗法的预测建模，或量化人类基因组。');

使用以下语句可以通过全文索引进行检索：

SELECT
	doc_name,
	content,
	MATCH ( doc_name, content ) AGAINST ( '审核' ) AS score 
FROM
	test_fulltext_idx_tbl 
WHERE
	MATCH ( doc_name, content ) AGAINST ( '审核' )

以上sql中score是文档的相关性得分，只有超过0的文档才认为匹配成功。这一查询将得到两条结果。

而因为我们使用了默认的ngram_token_size为2，即分词最小字数为2，所以可以看到以下sql得到所有文档的score均为0：

SELECT
	doc_name,
	content,
	MATCH ( doc_name, content ) AGAINST ( '审' ) AS score 
FROM
	test_fulltext_idx_tbl

如上文所述，检索的过程中将对输入的内容进行分词后再检索匹配，所以以下sql也能筛选出两条结果：

SELECT
	doc_name,
	content,
	MATCH ( doc_name, content ) AGAINST ( '技术能力开放' ) AS score 
FROM
	test_fulltext_idx_tbl 
WHERE
	MATCH ( doc_name, content ) AGAINST (
	'技术能力开放' 
	)

实际上，’技术能力开放’并没有作为整体出现在任何一个文档中，以下like查询的sql结果为0条可以验证：

SELECT
	* 
FROM
	test_fulltext_idx_tbl 
WHERE
	doc_name LIKE "%技术能力开放%" 
	OR content LIKE "%技术能力开放%"

那么如果解决需要严格匹配而不是模糊查询的情况呢？此时使用布尔模式查询配合操作符’+’就可以做到：

SELECT
	doc_name,
	content,
	MATCH ( doc_name, content ) AGAINST ( '+技术能力开放' IN BOOLEAN MODE) AS score 
FROM
	test_fulltext_idx_tbl

可以看到，这一sql查询结果中所有文档的score均为0，均未匹配成功