2021年9月15日10:16:44
mysql官方支持中文分词工具 ngram
自MySQL5.7.6版起，MySQL将ngram全文解析器作为内置的服务器插件
官方文档
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html
如果你需要额外的其他插件安装方法差不多
https://github.com/yanyiwu/sqljieba git clone git@github.com:yanyiwu/sqljieba.git cd sqljieba make
在吧编译好的dll，或者so复制到插件目录下
sudo cp libsqljieba.so /usr/local/mysql/lib/plugin/
[mysqld]
添加你的dll 或者so
重启服务
注意：上面的sqljieba 已经很多年不更新，不建议使用，提供一个参考
-- 现已所有插件 show plugins; -- 插件默认目录 show variables like 'plugin_dir';
#mysql版本必须大于5.7 在mysql配置文件my.ini里面添加ngram_token_size=2 2这个值代表按两个汉字长度分词 [mysqld] ngram_token_size=2 使用方法： #创建全文索引并分词 ALTER TABLE article ADD FULLTEXT INDEX ft_index (title,intro) WITH PARSER ngram; #查询并按相关性排序 SELECT id,title FROM article WHERE MATCH (title,intro) AGAINST ('筷子要吃饭' IN NATURAL LANGUAGE MODE) #查询相关性值 SELECT id,title,MATCH (title,intro) AGAINST ('筷子要吃饭' IN NATURAL LANGUAGE MODE) AS score FROM article ORDER BY score desc;
实际测试
SELECT COUNT(*) FROM dictionary;
1452979条数据
添加索引
ALTER TABLE dictionary ADD FULLTEXT INDEX description (description) WITH PARSER ngram;
查看测试
SELECT * FROM dictionary where MATCH(description) against('中医西医' IN BOOLEAN MODE) LIMIT 10; SELECT * FROM dictionary where MATCH(description) against('中医西医') LIMIT 10;
两种方式数据返回基本都在 1秒左右，如果是线上服务器可能会好一点
建议：
1，如果你想在线上去匹配 description 内容比较多，字段内容较多的全文索引，响应会比较慢，建议使用内容较少的字段，比如简介，title 字段内容较少效果会更好，like其实性能还不错，如果你必须要分词的话
2，建议优化一下mysql配置，提高性能
3，innodb和myisam都可以使用
4，如果你需要高性能的查询响应请使用 elasticsearch，同样的数据查询，es单机第一次只需要大概30-50ms，第二次10ms左右，mysql需要1秒左右，多次也差不多，性能差很多
一些小技巧：
1，查看match 匹配度
SELECT *, MATCH(description) AGAINST ('中医') as matching_rate FROM dictionary ORDER BY matching_rate desc LIMIT 20;
2，如果计算返回很慢，可以增加计算内存大小，这速度会快很多
innodb_ft_cache_size=1024M
3，基础操作
3.1. 自然语言模式
select unique_code, title from tb_name where match(unique_code) against ('album page');
其中unique_code 上有全文索引
mysql将搜索词语 “album page” 分成两个独立的关键词进行搜索。然后根据搜索结果与搜索词的相关性来排序。（在s使用全文索引进行排序的时候，无法再使用索引排序，所以不想使用文件排序的话，在全文索引的查询中就不要使用order by）。
3.2. boolean 全文索引
boolean 搜索通过停用词列表过滤掉噪声词，除此之外还要求搜索关键词必须大于 “ft_min_word_len" 小于“ft_max_word_len"。 其搜索返回的结果是未经排序的。
“+mysql”: 必须包含"mysql"
“-mysql”：不可以包含“mysql”
“～mysql”：包含mysql的rank值更低
“mysql”：包含mysql的rank值更高
“mysql*”：包含以mysql开头的单词的行rank更高
“""”: 短语搜索，要求精确匹配质指定的短语
3.3 boolean 索引使用
select unique_code, title from tb_name where match(unique_code) against ('+album +page');
1. 全文索引比普通索引有更多的碎片问题，所以要经常使用 OPTIMIZE TABLE 来减少碎片。
2. 保证索引缓存足够大，从而保证所有的全文索引都能够缓存在内存中。可以为全文索引设置单独的键缓存，保证不会被其他索引缓存挤出内存。
2. 提供一个停词表：尤其是对某些专业的文档，某一些关键词会大量出现。
3. 忽略一些太短的词可以提升全文索引的效率；索引词的最小长度可以通过 “ft_min_word_len" 来配置。
注意：当调整允许最小词长后，需要通过OPTIMIZE TABLE 来重建索引才会生效。
4. 当向全文索引表中导入大量数据时，最好先 “DISABLE KEYS” 来禁用全文索引，然后在导入结束后使用 “ENABLE KEYS” 来建立全文索引。
Notes：停用词表，最小词长都可以通过减少索引词语来提升全文索引的效率，但是同时也降低了搜索的精确度。
5. 如果数据集特别大，需要对数据进行手动分区，最好通过外部的搜索引擎来实现，如：Lucence或者Sphinx。
全文索引的限制
1. 全文索引只有全部在内存中的时候，性能才会非常好。如果内存无法装载全部索引，那么搜索速度会非常的慢。
2. 全文索引会影响查询优化器的工作。如果查询中使用来match against，而对应的列上有可用的全文索引，那么mysql就一定会使用这个全文索引，而忽视掉性能更好的其他索引。
3. 全文索引不可能使用索引覆盖扫描。