MySQL多全文索引联合查询极慢问题求助

针对1000万行数据的pa表，两个单独全文索引查询快但联合AND查询慢的问题，给出以下实用优化方案：

1. 创建联合全文索引（最优方案） #

将tagline和experience合并为一个联合全文索引，让MySQL能通过一次索引扫描完成两个条件的过滤，避免两次独立索引扫描后的交集计算开销：

ALTER TABLE pa ADD FULLTEXT INDEX ft_tagline_experience (tagline, experience);

修改查询语句为：

SELECT count(*) FROM pa
WHERE MATCH(tagline, experience) AGAINST('+"developer" +"python"' IN BOOLEAN MODE);

这种方式直接利用联合索引的特性，性能提升最明显。

2. 先过滤小结果集再关联查询 #

先执行结果集更小的那个单条件查询，得到主键列表后，再在这个子集上执行第二个全文检索：

-- 假设tagline的结果集更小，先获取对应的id
SELECT count(*) 
FROM (SELECT id FROM pa WHERE MATCH(tagline) AGAINST('"developer"' IN BOOLEAN MODE)) AS t
JOIN pa ON t.id = pa.id
WHERE MATCH(experience) AGAINST('"python"' IN BOOLEAN MODE);

如果其中一个条件的结果集远小于另一个，这种方式能大幅减少第二个查询的数据范围。

3. 用内存临时表存储中间结果 #

把第一个查询的主键存入内存临时表，再关联原表执行第二个查询，内存临时表的读写速度远高于磁盘表：

-- 创建内存临时表存储符合第一个条件的id
CREATE TEMPORARY TABLE tmp_ids ENGINE=MEMORY 
SELECT id FROM pa WHERE MATCH(tagline) AGAINST('"developer"' IN BOOLEAN MODE);

-- 关联临时表执行第二个条件查询并统计
SELECT count(*) FROM tmp_ids
JOIN pa ON tmp_ids.id = pa.id
WHERE MATCH(experience) AGAINST('"python"' IN BOOLEAN MODE);

-- 清理临时表
DROP TEMPORARY TABLE tmp_ids;

4. 改用外部全文检索引擎（长期方案） #

如果MySQL全文索引的性能无法满足需求，可以将全文检索逻辑迁移到专业全文检索引擎：

• 将pa表的tagline、experience和主键同步到引擎中
• 在引擎中执行多条件全文检索获取符合要求的主键列表
• 回MySQL通过主键列表统计数量
  专业引擎对多条件全文检索的优化远优于MySQL，适合大数据量的复杂查询场景。

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