MySQL InnoDB JSON生成存储列FULLTEXT搜索慢于LIKE问题咨询

• 检查InnoDB全文索引的基础参数配置：确认innodb_ft_min_token_size参数值，该参数默认是4，不符合长度的词不会被纳入索引，若业务有短词查询需求调整后必须重建全文索引生效。同时检查是否开启了停用词过滤innodb_ft_enable_stopword，大量命中停用词的查询会额外增加无效检索开销。
• 验证全文索引碎片率：全量数据上线过程中大量写入会导致全文索引产生严重碎片，索引碎片化过高会大幅提升IO开销。可执行OPTIMIZE TABLE stores;重建索引后再复测性能。
• 排除查询结果分布的特殊场景影响：当前LIKE查询加了LIMIT 20，如果表的前N行就有足够多的包含搜索词的记录，全表扫描匹配到20条就会直接返回，耗时自然极低。可替换为全表匹配结果少于20条的冷门关键词复测，排除结果前置导致的LIKE耗时虚低的情况。
• 优化生成列的内容冗余：当前生成列直接提取JSON中的完整HTML内容，大量HTML标签、样式代码、无用空格都会被全文索引分词，导致索引体积暴涨，查询时需要加载更多的索引页。可调整生成列的逻辑，提前过滤HTML标签仅保留有效文本内容后再建立全文索引，通常索引体积可缩小50%以上。
• 检查缓冲池配置是否足够：执行以下语句查看全文索引的体积：

SELECT table_name, index_name, stat_value * @@innodb_page_size / 1024 / 1024 AS index_size_mb 
FROM information_schema.innodb_index_stats 
WHERE stat_name = 'size' AND table_name = 'stores' AND index_name = 'html';

如果索引体积超过innodb_buffer_pool_size的可用空间，每次查询都需要从磁盘读取索引页，性能会远低于命中内存的全表扫描。

• 验证BOOLEAN模式通配符查询的效率：你当前使用的是带后缀通配符的BOOLEAN模式查询，这类查询需要遍历所有以forum开头的分词索引项，若匹配的分词数量极多，即使LIMIT 20也需要先检索全部分词结果，耗时自然更高。

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