MySQL多表及多全文索引AND查询性能优化求助

针对你的关联查询，以下是几个可行的优化方案：

• 调整查询执行顺序，提前缩小数据集
  原查询从books表出发关联chapters，但chapters数据量远大于books（52M vs 2.6M），先对chapters做全文搜索并限制结果数，再关联books能大幅减少后续关联的计算量：

  SELECT b.ari_id, b.Title, b.Author, t.toc, t.author
  FROM (
      SELECT ari_id, toc, author 
      FROM chapters 
      WHERE MATCH(toc) AGAINST('power*' IN BOOLEAN MODE)
      LIMIT 300
  ) t
  INNER JOIN books b ON t.ari_id = b.ari_id
  WHERE b.isOpenAccess = 1
  LIMIT 300;
  

• 给books表添加覆盖索引
  创建包含过滤条件、关联字段和查询所需字段的联合索引，避免回表查询：

  CREATE INDEX idx_books_openaccess_ari ON books (isOpenAccess, ari_id, Title, Author);
  

  这个索引能直接满足b.isOpenAccess = 1的过滤，同时提供关联用的ari_id和查询需要的Title、Author，无需再去主键索引取数据。

• 优化全文索引参数
  检查MySQL的ft_min_word_len配置（默认是4），如果你的前缀搜索词（比如power）长度小于该值，会导致全文索引无法有效利用。可以将其调整为2或3，修改后需要重建chapters的全文索引：

  -- 修改配置后执行重建
  ALTER TABLE chapters DROP INDEX toc;
  ALTER TABLE chapters ADD FULLTEXT INDEX toc (toc);
  

注意：你查询中的tocs表应该是chapters表（根据前面的表结构），以下优化基于此修正：

• 创建联合全文索引（最优方案）
  MySQL单独的全文索引无法高效组合两个MATCH条件的结果，将toc和author合并为一个联合全文索引，用单条MATCH语句同时搜索两个字段：

  ALTER TABLE chapters ADD FULLTEXT INDEX ft_toc_author (toc, author);
  

  然后改写查询：

  SELECT toc, author
  FROM chapters
  WHERE MATCH(toc, author) AGAINST('+high* +max*' IN BOOLEAN MODE)
  LIMIT 300;
  

  联合索引能一次性完成两个条件的搜索，性能会有数量级的提升。

• 子查询分步过滤（临时方案）
  如果无法修改表结构，可先通过一个全文索引过滤出较小的数据集，再用另一个索引二次过滤，同时提前限制结果数：

  SELECT toc, author
  FROM (
      SELECT toc, author 
      FROM chapters 
      WHERE MATCH(toc) AGAINST('high*' IN BOOLEAN MODE)
      LIMIT 1000  -- 取足够多的结果避免漏数据，后续再过滤
  ) t
  WHERE MATCH(author) AGAINST('max*' IN BOOLEAN MODE)
  LIMIT 300;
  

• 清理无效索引
  如果你创建了联合全文索引，建议删除原有的单独toc和author全文索引，避免索引冗余占用资源：

  ALTER TABLE chapters DROP INDEX toc;
  ALTER TABLE chapters DROP INDEX author;
  

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