MySQL同字段全文+精确值OR查询索引失效慢查询优化咨询

MySQL优化器不支持在同一条查询的OR逻辑中同时选用BTREE、FULLTEXT两类不同实现的索引，遇到这种跨索引类型的OR条件时，优化器会判定走索引的成本高于全表扫描，最终放弃索引扫全表，这是原始查询耗时9秒的核心原因。

• 优先选择：改写SQL用UNION合并两个独立索引查询
  不需要调整现有表结构和索引，把OR连接的两个条件拆成两个独立查询，分别命中各自的索引后合并结果即可：

  -- 总耗时约180ms，和两个单查询的耗时之和基本持平
  SELECT * FROM mytable WHERE document = '111/05257'
  UNION
  SELECT * FROM mytable WHERE MATCH(document) AGAINST ('+111/05257' IN BOOLEAN MODE);
  

  如果业务上可以确认精确匹配的结果一定包含在全文检索的结果集中，可以把UNION替换为UNION ALL，省去结果去重的开销，速度还能进一步提升。

  不要试图用FORCE INDEX强制原始OR查询走某一个索引，这种方式只会让单条件走索引，另一个条件的过滤仍然需要逐行判断，性能提升非常有限。

• 优化全文索引配置，解决子串匹配问题
  BTREE索引本身就不支持%xxx%这类前后通配的任意位置子串匹配，不要在这种场景下强行用BTREE。针对你存储的带/、:特殊符号的编号类数据，可以调整全文索引的分词规则适配业务：

  • 修改配置项ft_min_word_len为你需要匹配的最短词长度（比如设为2，避免短编号被过滤）
  • 关闭innodb_ft_enable_stopword停用词表，防止编号类片段被当成停用词不建索引
  • 自定义分词字符集，把/、:这类你数据里的分隔符号标记为有效词字符，不要当成分词边界
    配置修改完成后重启实例，重建全文索引即可，调整后全文检索的准确率和召回率都会明显提升，完全可以替代前后通配的模糊查询。

• 固定查询模式场景可选：自建倒排冗余表
  如果你的查询片段格式非常固定，比如都是固定长度的编号段，可以额外建一张倒排关联表，写入数据时把document字段中所有需要支持检索的片段拆分出来，和原表主键做关联。查询时先匹配倒排表拿到主键，再回表关联原表查询，性能比原生全文索引更高，缺点是需要额外维护冗余数据的一致性，可以通过触发器或者写入层逻辑实现同步。

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