MySQL多MATCH关联查询中全文索引未出现在执行计划possible keys问题排查

我来帮你一步步拆解这个问题，看看为什么你的查询计划里possible_keys没有显示预期的全文索引：

1. 先确认全文索引是否真的有效创建 #

首先得打基础，确保你的字段上确实存在可用的全文索引：

• 执行SHOW CREATE TABLE orders;和SHOW CREATE TABLE order_items;，检查输出里是否有类似FULLTEXT INDEX idx_po_number (po_number)（对应orders.po_number）和FULLTEXT INDEX idx_product_desc (product_description)（对应order_items.product_description）的索引定义。
• 注意：MySQL的全文索引只支持CHAR、VARCHAR、TEXT类型的字段，如果你的这两个字段是其他类型（比如INT、DATE），即使尝试创建索引也不会生效，优化器自然不会识别。

2. JOIN条件的写法限制了优化器识别索引 #

你的LEFT JOIN条件把全文匹配和自关联等值条件混在一起，这可能让优化器无法判断可以使用全文索引：

• 你当前的写法是LEFT JOIN orders o1 on MATCH(o1.po_number) AGAINST('+test*' IN BOOLEAN MODE) and o1.id=o.id，优化器可能优先关注o1.id=o.id这个主键等值关联，直接走主键索引，忽略了全文索引的可能性。
• 可以尝试把全文匹配逻辑挪到WHERE子句中（注意调整逻辑保持原意），让优化器更直接地看到需要用到全文检索的需求：

  EXPLAIN SELECT DISTINCT o.id, o1.id, order_items.order_id
  FROM orders o
  LEFT JOIN orders o1 ON o1.id=o.id
  LEFT JOIN order_items ON order_items.order_id=o.id
  WHERE (MATCH(o1.po_number) AGAINST('+test*' IN BOOLEAN MODE) 
         OR MATCH(order_items.product_description) AGAINST('+test*' IN BOOLEAN MODE))
  

3. WHERE子句的COALESCE条件干扰了索引识别 #

你用COALESCE(o1.id, order_items.order_id) IS NOT NULL来过滤结果，但这个条件比较间接，优化器无法把它和JOIN里的全文匹配逻辑关联起来，导致没考虑到全文索引。

• 换成明确的OR条件（如上面的示例），让优化器清晰看到需要对po_number和product_description做全文检索，这样更容易触发全文索引的使用。

4. 检查全文查询的语法和规则是否合规 #

即使索引存在，不符合规则的查询也会让优化器放弃使用索引：

• 检查test是否是MySQL的停用词：执行SHOW VARIABLES LIKE 'ft_stopword_file';查看停用词文件，若test在列表中，这个查询会被忽略，索引自然不会被用到。
• 检查最小词长设置：执行SHOW VARIABLES LIKE 'ft_min_word_len';，如果这个值大于4（test的长度），那么test不会被加入全文索引，MATCH查询也无法使用索引。

5. 优化器的成本估算优先级问题 #

有时候即使索引存在，优化器可能认为全表扫描或其他索引（比如主键）的成本更低，所以不会把全文索引放到possible_keys里。

• 可以尝试用FORCE INDEX强制指定全文索引，验证是否能触发索引使用：

  EXPLAIN SELECT DISTINCT o.id, o1.id, order_items.order_id
  FROM orders o
  LEFT JOIN orders o1 FORCE INDEX(idx_po_number) 
       ON MATCH(o1.po_number) AGAINST('+test*' IN BOOLEAN MODE) AND o1.id=o.id
  LEFT JOIN order_items FORCE INDEX(idx_product_desc) 
       ON MATCH(order_items.product_description) AGAINST('+test*' IN BOOLEAN MODE) AND order_items.order_id=o.id
  WHERE COALESCE(o1.id, order_items.order_id) IS NOT NULL
  

  如果强制后possible_keys出现了全文索引，说明是优化器的成本判断问题，你可以考虑调整查询结构或者优化器参数来引导它选择全文索引。

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