MySQL/MariaDB InnoDB全文索引搜索OCR长文本性能过慢问题排查

• 索引选择逻辑缺陷：InnoDB优化器在存在全文索引的前提下，会优先选择全文索引执行查询，而非先执行普通索引的条件过滤。你所有带全文检索的查询，都会先扫描整个全文索引拿到所有匹配关键词的行ID，再回表过滤dbRollID等其他条件、执行排序，哪怕你只需要某一个dbRollID的结果，也得先处理完几万行匹配数据，高频词匹配行数多，耗时自然成倍增长。子查询写法也不会改变优化器的索引选择逻辑，所以没有提速效果。
• 全文索引过度膨胀：OCR产生的大量无意义乱码、短字符都被纳入了全文索引，导致索引体积远大于有效内容对应的索引大小，单次检索需要扫描的索引页数量大幅提升，哪怕调大缓冲池也无法完全覆盖高频扫描的索引页，产生大量磁盘IO拖慢速度。
• 排序逻辑导致LIMIT失效：所有查询都携带ORDER BY子句，数据库必须拿到所有符合条件的结果完成排序后，才能返回LIMIT指定的行数，哪怕你只需要1条结果，也得等全量检索、过滤、排序完成，所以LIMIT完全不起作用。

快速生效方案（改SQL即可，不需要调整表结构） #

强制查询先走dbRollID的普通索引，先缩小数据范围再做全文匹配，比如查询dbRollID=110的语句修改为：

SELECT xLeadEdge FROM frames 
FORCE INDEX (fk_roll_id_1)
WHERE dbRollID=110 
AND MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
ORDER BY xLeadEdge;

这个写法会先捞出dbRollID=110的512行数据，再逐行做全文匹配，总耗时会和你查询全量dbRollID=110数据的0.27秒接近，完全满足性能要求。

如果不需要排序可以直接去掉ORDER BY子句，LIMIT会直接生效，拿到指定行数就停止检索，速度会进一步提升。

中长期优化方案 #

• 建组合全文索引：如果使用MySQL 8.0、MariaDB 10.0以上版本，可以创建带过滤字段的组合全文索引，优化器可以直接通过该索引同时完成条件过滤和全文检索，不需要回表：

ALTER TABLE frames ADD FULLTEXT INDEX ft_roll_ocr(dbRollID, ocr_text);

• 清洗全文索引内容：
  1. 自定义全文停用词表，把OCR产生的乱码、无意义短串都加入停用词，不纳入索引；
  2. 调整innodb_ft_min_token_size参数，根据业务需要的最小检索词长调整，不需要检索短词的话可以把默认值4调大，进一步减少无效索引内容；
  3. OCR文本入库前做预处理，清洗掉乱码、无效特殊符号，缩小ocr_text的实际长度，降低索引体积。
     以上参数调整和清洗完成后，重建全文索引即可生效。

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