MySQL全文索引实现简单版搜索引擎

1 前言

• 只有Innodb和myisam存储引擎能用全文索引（innodb支持全文索引是从mysql5.6开始的）
• char、varchar、text类型字段能创建全文索引（fulltext index type）
• 全文索引的基于关键词的，如何区分不同的关键词了，就要用到分词（stopword）
• 英文单词用空格，逗号进行分词；中文分词不方便（一个句子不知道怎样区分不同的关键词）
• 内置分词解析器ngram支持中文，日文，韩文（将句子分成固定数字的短语）
• 当对表写入大量数据时，写入数据后再创建全文索引的速度更快（减少了维护索引的开销）
• 全文索引的原理的倒排索引（一种数据结构），一般利用关联数组，在辅助表中存储单词与文档中所在位置的映射
• 每张表只能有一个全文检索的索引
• 由多列组合而成的全文检索的索引必须使用相同的字符集与排序规则
• 不支持没有单词界定符delimiter的语言，如中文 日文汉语等

2 使用

用MATCH() ... AGAINST 方式来进行搜索

match()表示搜索的是那个列，against表示要搜索的是那个字符串

查看默认的分词（以这些词来区分不同的关键词）；也可以自定义分词，以这些词来区分不同的关键词

    SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

如

3 三种类型的全文搜索方式

natural language search（自然语言搜索）

通过MATCH AGAINST 传递某个特定的字符串来进行检，默认方式

boolean search（布尔搜索）

为检索的字符串增加操作符，如“+”表示必须包含，"-"不包含，"*" 表示通配符，即使传递的字符串较小或出现在停词中，也不会被过滤掉

query expansion search（查询扩展搜索）

搜索字符串用于执行自然语言搜索，然后，搜索返回的最相关行的单词被添加到搜索字符串，并且再次进行搜索，查询将返回来自第二个搜索的行

4 相关参数

配置相关参数

（1）innodb_ft_min_token_size

默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小

（2）innodb_ft_max_token_size

默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小

（3）ngram_token_size

默认2，表示2个字符作为内置分词解析器的一个关键词，如对“abcd”建立全文索引，

关键词为'ab'，'bc'，'cd'当使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size 无效

注意 这三个参数均不可动态修改，修改了这些参数，需重启MySQL服务，并重新建立全文索引

5 测试innodb引擎使用全文索引

5.1 准备

（1） 目标

查询文章中是否含有某个关键词；一系列文章出现某个关键词的次数

查询文章的标题是否含有某个关键词

（2） 设置以下参数减少磁盘IO压力

 SET GLOBAL sync_binlog=100;

    
 SET GLOBAL innodb_flush_log_at_trx_commit=2;

（3）导入1kw 数据进行测试全文索引

（4） 某个文章表的结构

 CREATE TABLE `article` (

    
   `id` bigint(10) NOT NULL,
    
   `url` varchar(1024) CHARACTER SET latin1 NOT NULL DEFAULT '',
    
   `title` varchar(256) NOT NULL DEFAULT '',
    
   `source` varchar(32) DEFAULT '' COMMENT '真实来源',
    
   `keywords` varchar(32) DEFAULT NULL,
    
   `publish_time` timestamp NULL DEFAULT NULL,
    
   PRIMARY KEY (`id`),
    
   KEY `title_idx` (`title`)
    
 ) ENGINE=InnoDB

注意：不需要建立，导入时候会自己建

使用myloader 多线程导入测试数据

 先把测试数据进行解压

    
 tar -zxf mydumper_dump_article.tar.gz
    
 time myloader -u root -p 123456  -S /data/mysql/mysql.sock -t 32 --database=article -d /home/dump_article -v 3

（5） 导入数据后总数据量和数据文件、索引文件大小

    SELECT COUNT(*) FROM `article`;

可以看到统计所花时长31秒

    SELECT table_name, CONCAT(FORMAT(SUM(data_length) / 1024 / 1024,2),'M') AS dbdata_size, CONCAT(FORMAT(SUM(index_length) / 1024 / 1024,2),'M') AS dbindex_size, CONCAT(FORMAT(SUM(data_length + index_length) / 1024 / 1024 / 1024,2),'G') AS `db_size(G)`, AVG_ROW_LENGTH,table_rows, update_time FROM information_schema.tables WHERE table_schema = DATABASE() and table_name='article';

5.2 使用默认方式创建全文索引

（1） 该表已有关键词字段（对文章内容的简述），并以“,”作为分词符

    select keywords from article limit 10;

（2）不建全文索引时搜索某个关键词

需要进行全表扫描

    select count(*) from article where keywords like '%时尚%';

（3） 对关键词字段创建全文索引（以 , 作为分词）

my.cnf配置文件中设置innodb_ft_min_token_size，并重启MySQL服务（最小两个字符作为一个关键词，默认三个字符作为一个关键词）

 [mysqld]

    
 innodb_ft_min_token_size=2

设置自定义stopwords（即分词）

 USE mysql;

    
 CREATE TABLE my_stopwords(VALUE VARCHAR(30)) ENGINE = INNODB;
    
 INSERT INTO my_stopwords(VALUE) VALUE (',');
    
 SET GLOBAL innodb_ft_server_stopword_table = 'mysql/my_stopwords';

注意：停用词表第一列必须是value，如果数据库设置的是区分大小写的，将上面的VALUE替换为value,，否则会报错。

    InnoDB: invalid column name for stopword table mysql/my_stopwords. Its first column must be named as 'value'.

查看设置的自定义分词

    SHOW GLOBAL  VARIABLES WHERE Variable_name IN('innodb_ft_min_token_size','innodb_ft_server_stopword_table');

创建全文索引，这个时间很长，请耐心等待。

 USE article;

    
 alter table article add fulltext index idx_full_keyword(keywords);

注意：剩余磁盘空间需足够，原表4.6G，剩余5.7G磁盘，添加全文索引也会失败，因为过程中会创建和原表大小的文件。

利用创建的全文索引进行查询某个关键词出现的次数

查询响应时间有了很大的提升，只需0.03s；使用where keywords like '%时尚%' 需要9.93s，如下两张图。

如需同时完全匹配多个关键词，用布尔全文搜索

表示完全匹配 "三里屯,北京" 的记录数

    select count(*) from article where match(keywords)  against('+三里屯,北京' in boolean mode);

表示匹配“三里屯” 或者 “北京”的记录数

    select count(*) from article where match(keywords)  against('三里屯,北京');

创建全文索引后，会创建一些其它文件

- 前6个表示倒排索引（辅助索引表）

- 第7，8个表示包含已删除文档的文档ID（DOC_ID），其数据当前正在从全文索引中删除

- 第9个表示FULLTEXT索引内部状态的信息

- 第10，11个表示包含已删除但尚未从全文索引中删除其数据的文档

6 使用ngram分词解析器创建全文索引

ngram解析器将文本序列分词为连续的n个字符。例如，你可以使用ngram全文解析器为“abcd”进行不同值的n分词。

 n=1: 'a', 'b', 'c', 'd'

    
 n=2: 'ab', 'bc', 'cd'
    
 n=3: 'abc', 'bcd'
    
 n=4: 'abcd'

ngram 全文解析器是一个内置的服务器插件。与其他内置服务器插件一样，它会在服务器启动时自动加载。

ngram_token_size 默认值为2，最小值为1，最大值为10。

例如，ngram_token_size 为2时，ngram解析器将字符串“abc def”解析为四个tokens：“ab”, “bc”, “de” 和 “ef”。

一般建议将ngram_token_size设置为你想要搜索的最大词的大小。如果你只打算搜索单个字符，可以将ngram_token_size设置为1。较小的字符长度将产生较小的全文搜索索引，并且搜索速度更快。例如，“Happy Birthday”用简体中文是“生日快乐”，其中“生日”是“birthday”，“快乐”意为“happy”。若要搜索像这样的两个字符的词，可以将ngram_token_size设置为2或更高的值。

ngram_token_size是一个只读变量，只能启动字符串时设置或在配置文件my.conf中设置：

 [mysqld]

    
 ngram_token_size=2

配置完后，重启mysql服务.

6.1 对title字段建立全文索引（该字段没有固定的stopwords 分词，使用ngram分词解析器）

    select title from article limit 10;

6.2 对title字段创建全文索引

    alter table article add fulltext index ft_index_title(title) with parser ngram;

注意：这个时间比较耗时。

会创建倒排索引（title字段越长长，创建的倒排索引越大），如下图，自行与上面的截图对比

6.3 不建立全文索引搜索title的某个关键词

    select count(*) from article where title like '%户外%';

可以看到耗时较长，耗费17秒

    select count(*) from article where title like '%后台%';

可以看到耗时较长，耗费4秒

6.4 使用全文索引搜索某个关键词

响应时间有很大提升

    select count(*) from article where match(title)  against('户外');

    select count(*) from article where match(title)  against('后台');

注意： 当搜索的关键词字符数大于2 （ngram_token_size定义大小）会出现不一致问题，这和ngram解析器特殊处理方式有关

6.5 ngram解析器特殊处理方式

（1）空格处理

ngram解析器在解析过程中忽略了单词间的空白，因此“ab cd”和“a bc”都会相应地被解析为"ab", "cd"和"bc"。

（2）停止词处理

与内置全文解析器不同，ngram解析器会排除包含停止词的tokens。默认情况下，ngram解析器采用英语停止词列表，但您可以创建适合中文、日文或韩文的停止词列表。

（3）通配符搜索

由于 ngram FULLTEXT 索引仅包含 ngram，并且不包含有关术语开头的信息，因此通配符搜索可能会返回意外结果。以下行为适用于使用 ngram FULLTEXT 搜索索引的通配符搜索：

• 如果通配符搜索的前缀术语短于 ngram 标记大小，则查询将返回包含以前缀术语开头的 ngram 标记的所有索引行。例如，假设 ngram_token_size=2 ，搜索“a*”将返回以“a”开头的所有行。
• 如果通配符搜索的前缀术语长于 ngram 令牌大小，则前缀术语将转换为 ngram 短语，并忽略通配符运算符。例如，假设 ngram_token_size=2 ，“abc*” 通配符搜索转换为 “ab bc”。

如下：

普通搜索，实际中出现该关键词的记录数为6

    select count(*) from article where title like '%公子大%';

全文搜索，出现关键字的记录数为9443

    select count(*) from article where match(title)  against('公子大');

实际出现该关键字的记录数为1

    select count(*) from article where title like '%花田自助%';

全文搜索出现该关键词的记录数为3202

    select count(*) from article where match(title)  against('花田自助');

7 结论

• 当mysql 某字段中有固定的stopword 分词（英文的空格符，中文的“,”"-"等），对该字段建立全文索引，能快速搜索出现某个关键词的相关记录信息，实现简单搜索引擎的效果
• 当mysql 某字段没有固定的stopword 分词，使用内置解析器ngram 可将字段值分成固定数量（ngram_token_size定义大小）的关键词快速进行搜索；当搜索的关键词的字符数量不等于ngram_token_size定义大小时，会出现与实际情况不一致的问题
• 全文索引能快速搜索，也存在维护索引的开销；字段长度越大，创建的全文索引也越大，会影响DML语句的吞吐量，可用专门的全文搜索引擎ES来做这件事