如何在MariaDB InnoDB中基于指定ID优化全文检索性能？

• 强制优化器优先过滤ID
  原查询中优化器可能优先执行全文搜索再过滤ID，导致仍需处理大量数据。可以通过子查询先筛选出目标ID对应的行，再执行全文搜索：

  SELECT t.id 
  FROM (SELECT id FROM my_table WHERE id IN (1, 2)) AS target_ids
  JOIN my_table t ON t.id = target_ids.id
  WHERE MATCH(t.text) AGAINST("some* tex*" IN BOOLEAN MODE);
  

  这里子查询会直接走主键索引快速拿到目标ID，后续仅对这些行执行全文搜索，大幅缩小处理范围。

• 使用临时表处理大批量已知ID
  如果已知ID数量较多（如数千个），临时表的主键索引能让关联操作更高效：

  -- 创建带主键的临时表
  CREATE TEMPORARY TABLE temp_target_ids (id INT PRIMARY KEY);
  -- 批量插入所有已知ID
  INSERT INTO temp_target_ids VALUES (1), (2), ...;
  -- 关联原表执行全文搜索
  SELECT t.id 
  FROM temp_target_ids ids
  JOIN my_table t ON t.id = ids.id
  WHERE MATCH(t.text) AGAINST("some* tex*" IN BOOLEAN MODE);
  

• 验证执行计划
  用EXPLAIN查看查询执行计划，确认优化器是否先执行ID过滤：

  EXPLAIN SELECT t.id FROM (SELECT id FROM my_table WHERE id IN (1,2)) AS target_ids JOIN my_table t ON t.id = target_ids.id WHERE MATCH(t.text) AGAINST("some* tex*" IN BOOLEAN MODE);
  

  若结果中type列显示eq_ref或ref（关联阶段），且Extra列无Using filesort或Using temporary，则说明执行计划符合预期。

• 额外优化点

  • 确认全文索引配置：检查ft_min_word_len参数，确保前缀搜索（如some*）能正常命中索引（默认最小词长为4，"some"长度为4刚好符合，若自定义过需匹配）。
  • 避免返回大量结果：如果即使过滤后结果仍较多，可在查询中添加LIMIT分页，让Java应用分批获取数据，降低单次数据传输压力。

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