关于fulltext-index的缺点及ngram配置优化的技术咨询

• 索引体积膨胀严重：尤其是使用ngram=1时，每个字符都会被拆解为独立索引项，数据量增长到十万级以上后，索引体积会远超过普通B+树索引，占用更多磁盘资源，备份、恢复的时间和成本都会上升。
• 查询噪音大、精度低：ngram=1会匹配单个字符，搜索时极易出现大量无关结果。比如搜索“周杰伦”，会带出所有包含“周”“杰”“伦”任意字符的条目，包括其他歌手名字含这些字的内容，需要额外过滤无效结果，效率低下。
• 大数量级下性能下滑明显：当数据量突破十万级后，全文索引的查询速度会显著下降，尤其是模糊匹配场景，优化空间远不如专业搜索引擎的倒排索引。
• 无语义分词能力：即便增大ngram尺寸，也只是简单的字符切分，无法识别语义单元。比如“张学友的歌”，切分后无法将“张学友”作为完整主体匹配，容易出现结果偏差。
• 功能扩展性差：不支持复杂搜索语法（如布尔组合精准匹配、权重排序、同义词扩展等），如果后续需要更精细化的搜索逻辑，很难通过全文索引满足需求。

1. 修改ngram_token_size参数 #

以MySQL为例，需修改配置文件（my.cnf或my.ini）：

[mysqld]
ngram_token_size=2  # 可根据需求调整为2-10之间的值，默认是2

修改完成后重启MySQL服务，必须重建全文索引才能让新参数生效：

ALTER TABLE 你的表名 DROP INDEX 全文索引名;
ALTER TABLE 你的表名 ADD FULLTEXT INDEX 全文索引名(singer, song);

2. 实现短内容用LIKE、长内容用全文索引的分支查询 #

应用层判断方案 #

在业务代码中判断用户输入的搜索内容长度：

• 若长度≤2，执行LIKE查询：SELECT * FROM 表名 WHERE CONCAT(singer, ' ', song) LIKE '%关键词%'
• 若长度>2，执行全文索引查询：SELECT * FROM 表名 WHERE MATCH(singer, song) AGAINST('关键词' IN BOOLEAN MODE)

数据库层判断方案 #

用IF函数在SQL中实现分支逻辑：

SELECT * FROM 表名
WHERE
  IF(LENGTH(?) <= 2,
     CONCAT(singer, ' ', song) LIKE CONCAT('%', ?, '%'),
     MATCH(singer, song) AGAINST(? IN BOOLEAN MODE)
  );

注意：为优化短内容的LIKE查询速度，建议给singer和song字段单独创建普通索引，或创建联合普通索引。

内容的提问来源于stack exchange，提问作者user10874312