借助MySQL的全文索引+ngram解释器实现中文搜索的踩坑记录

130次阅读
6条评论

公司要做一个搜索,数据量不大但是着急,引入ES就显得来不及了,于是决定就用mysql的全文索引原地实现,过程中踩了一些坑:

一开始是就是简单了使用了FULLTEXT全文索引 ,然后在搜索的时候发现结果十分的不尽人意,原来是MYSQL的FULLTEXT索引默认的分词策略的问题,中文全部按照一种分词方式来,也就是说 你要是不搜中文的全名,在自然语言模式下(IN NATURE LANGUAGE MODE) 根本搜不出来东西。
全文索引文档:https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
但是在查阅文档的时候发现了ngram这个解释器,mysql自带的
ngram官方文档:https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html

  1. 因为都是已经存在了的表了 就直接Navicat或者DDL语句就可以新建,我依赖于图形工具,就直接新建了但是第一个坑就来了: 借助MySQL的全文索引+ngram解释器实现中文搜索的踩坑记录 直接在原有的索引上进行的修改 保存之后没生效!!! 但是我不知道 展现出来的就还是搜索不出来结果,后来检查了一下才发现不能完全相信Navicat

  2. 那就使用DDL语句呗 2号坑就来了,我用Navicat模拟了一下操作 生成了DDL语句如下(错误的那个):
    错误DDL:

    ALTER TABLE biz_shop_spu DROP INDEX IDX_NAME_FULLTEXT, ADD FULLTEXT INDEX IDX_NAME_FULLTEXT(NAME) WITH PARSER NGRAM;

    正确:

    ALTER TABLE biz_shop_spu DROP INDEX IDX_NAME_FULLTEXT;

    ALTER TABLE biz_shop_spu ADD FULLTEXT INDEX IDX_NAME_FULLTEXT(NAME) WITH PARSER NGRAM;
    必须重新ALTER一下表 不然直接DROP旧索引插入索引的话 ,用FlyWay执行不会报错 但是还是没有使用ngram。
    flyway执行结果图:
    借助MySQL的全文索引+ngram解释器实现中文搜索的踩坑记录

  3. 至此一个使用ngram作为解释器的全文类型的索引 就建立完成了,但是分词只会对新数据进行分词,导致使用全文索引搜不出来历史数据 需要显式的使用 OPTIMIZE TABLE biz_shop_spu
    这个是清理表碎片文件的命令 但是它也会重新更新索引的统计数据
    ps:命令执行期间会锁表哦~

至此踩坑部分完毕

下面就是一些ngram的小技巧

  • 观察索引的分词:

如果使用了ngram就不用看这个SHOW VARIABLES LIKE '%ft%';了 而是看这个 show variables like 'ngram_token_size%'; 默认是2

  • 显式指定全文检索表源:

SET GLOBAL innodb_ft_aux_table="db_name/tableName" 搭配SELECT * FROM information_schema.innodb_ft_index_cache ORDER BY doc_id , position;可以看到哪些词在全文索引里面

借助MySQL的全文索引+ngram解释器实现中文搜索的踩坑记录

评论(6条评论)
2021-04-15 00:30:25 回复

QehXcVyfljn

AndrewTib
2021-04-19 14:59:01 回复
2021-04-29 01:27:25 回复

smABxHun

2021-05-09 21:07:03 回复

I think, you will find the correct decision.

2021-05-13 00:17:39 回复

Hello,

Download club, radio music: https://0daymusic.org
MP3/FLAC, Label, LIVESETS, Music Videos, TV Series.

Genre: House, Club, Techno, Trance, Dance, Italo-Dance, Eurodance, Drum and Bass, Psychedelic, Goa, PsyTrance, Progressive House, Electro, Euro-House, Club-House, Hardtechno, Tech-House, Dutch House, Minimal, Deep-House, Nu-Disco, Hardstyle, Hardcore, Jumpstyle, Electronic, Alternative, Alternative Rock, Ambient, Avantgarde, Ballad, Bass, Beat, Black Metal,Blues, Classical, Chanson, Country, Dance Hall, Death Metal, Disco, Ethnic, Folk, Folk-Rock, Funk, Gangsta Rap, Gothic Rock,Hard Rock, Heavy Metal, Hip-Hop, Indie, Industrial, Instrumental, Jazz, Jungle, Pop, Rock, Metal, Latin, Lo-Fi, New Age, Noise, Oldies, Pop-Folk, Progressive Rock, Psychedelic Rock, Punk Rock, Rap, Reggae, R&B, Rock & Roll, Soul, Soundtrack, Speech, Synthpop, Thrash Metal, Top 40

Best regards,
0day MP3s

2021-05-14 18:26:52 回复

blcpTZCWg