使用ngram分词解析器创建全文索引
1、对title字段建立全文索引(该字段没有固定的stopwords 分词,使用ngram分词解析器)
需先在my.cnf 配置文件中设置ngram_token_size(默认为2,2个字符作为ngram 的关键词),并重启mysql服务
这里使用默认的 2
select title from article limit 10; +------------------------------------------------------------------------------+ | title | +------------------------------------------------------------------------------+ | worth IT | |Launchpad 江南皮革厂小show | |Raw 幕后罕见一刻 “疯子”被抬回后台 | |Raw:公子大骂老爸你就是个绿茶 公子以一打四 | |四组30平米精装小户型,海量图片,附户型图 | |夜店女王性感烟熏猫眼妆 | |大秀哥重摔“巨石”强森 | |少女时代 崔秀英 服饰科普 林允儿 黄美英 金泰妍 郑秀晶 | |德阳户外踏青,花田自助烧烤 | +------------------------------------------------------------------------------+
2、对title字段创建全文索引
alter table article add fulltext index ft_index_title(title) with parser ngram; Query OK, 0 rows affected (3 min 29.22 sec) Records: 0 Duplicates: 0 Warnings: 0
3、会创建倒排索引(title字段越长长,创建的倒排索引越大)
112M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_1.ibd
28M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_2.ibd
20M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_3.ibd
140M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_4.ibd
128M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_5.ibd
668M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_6.ibd
4、不建立全文索引搜索title的某个关键词
select count(*) from article where title like '%户外%'; +----------+ | count(*) | +----------+ | 22058 | +----------+ 1 row in set (8.60 sec) select count(*) from article where title like '%后台%'; +----------+ | count(*) | +----------+ | 1142 | +----------+
5、使用全文索引搜索某个关键词
响应时间有很大的提升
select count(*) from article where match(title) against('户外'); +----------+ | count(*) | +----------+ | 22058 | +----------+ 1 row in set (0.07 sec) select count(*) from article where title like '%后台%'; +----------+ | count(*) | +----------+ | 1142 | +----------+ 1 row in set (8.31 sec)
6、注意当搜索的关键词字符数大于2 (ngram_token_size定义大小)会出现不一致问题
普通搜索,实际中出现该关键词的记录数为6 select count(*) from article where title like '%公子大%'; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (8.40 sec) 全文搜索,出现关键字的记录数为9443 select count(*) from article where match(title) against('公子大'); +----------+ | count(*) | +----------+ | 9443 | +----------+ 1 row in set (0.06 sec) 实际出现该关键字的记录数为1 select count(*) from article where title like '%花田自助%'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (8.33 sec) 全文搜索出现该关键词的记录数为3202 select count(*) from article where match(title) against('花田自助'); +----------+ | count(*) | +----------+ | 3202 | +----------+ 1 row in set (0.06 sec)
结论
参考
InnoDB FULLTEXT Indexes
总结
声明:本网页内容旨在传播知识,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com