MySQL全文检索性能疑问：多条件查询耗时激增及单条查询优化咨询

背景信息 #

表xx_user_detail共500万行数据，结构及索引如下：

CREATE TABLE xx_user_detail (
  user_id int(11) NOT NULL,
  site1 varchar(20) NOT NULL,
  site2 varchar(20) NOT NULL,
  site3 varchar(20) NOT NULL,
  site4 varchar(20) NOT NULL,
  site5 varchar(20) NOT NULL,
  site6 varchar(20) NOT NULL,
  site7 varchar(20) NOT NULL,
  site8 varchar(20) NOT NULL,
  site9 varchar(20) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

ALTER TABLE xx_user_detail ADD FULLTEXT INDEX the_site_index(site1,site2,site3,site4,site5,site6,site7,site8,site9) WITH PARSER ngram;

• user_id为INT类型主键索引
• site1至site9为联合全文索引（采用ngram分词器）

执行两条SQL的耗时差异显著：

1. SQL1（耗时0.3秒）：

SELECT * FROM xx_user_detail WHERE  (user_id=14) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9)  AGAINST ('苏娟的食品店');

2. SQL2（耗时2.5秒）：

SELECT * FROM xx_user_detail WHERE  (user_id=14 or user_id=15) AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9)  AGAINST ('苏娟的食品店');

问题1：新增一个user_id的OR条件后，SQL2耗时剧增的原因及优化方案 #

原因 #

MySQL优化器无法同时高效利用主键索引和全文索引处理OR条件：

• SQL1中，优化器优先通过主键索引定位到user_id=14的单行数据，仅对这一行执行全文匹配检查，逻辑简洁高效。
• SQL2中，user_id=14 OR user_id=15的条件会让优化器放弃主键索引的精准定位，转而选择两种低效执行路径：要么先扫描全文索引匹配'苏娟的食品店'的所有数据，再筛选符合user_id条件的记录；要么分别执行两次主键查询后合并结果再做全文检查。但MyISAM引擎的优化器对这类组合条件的执行计划选择效率极低，导致需要扫描大量无关数据，耗时骤增。

优化方法 #

• 拆分SQL并合并结果：将OR条件拆分为两个独立子查询，用UNION ALL合并结果，让每个子查询都能利用主键索引+单行全文检查的高效逻辑：

SELECT * FROM xx_user_detail WHERE user_id=14 AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9) AGAINST ('苏娟的食品店')
UNION ALL
SELECT * FROM xx_user_detail WHERE user_id=15 AND MATCH (site1,site2,site3,site4,site5,site6,site7,site8,site9) AGAINST ('苏娟的食品店');

• 迁移至InnoDB引擎：InnoDB对组合条件的索引利用逻辑更优，能更好地处理主键+全文索引的OR查询（注意InnoDB需开启全文索引支持，且兼容ngram分词器）。

问题2：SQL1仅扫描一行却耗时0.3秒是否正常及优化方案 #

是否正常 #

0.3秒扫描单行数据明显不正常。正常情况下，主键定位单行+全文检查的耗时应在毫秒级，该延迟说明单次全文匹配的效率极低。

优化方法 #

1. 调整ngram分词参数：ngram默认分词长度为2，若'苏娟的食品店'的分词逻辑导致匹配时遍历过多分词项，可修改ngram_token_size参数（例如设置为3），减少不必要的分词匹配开销。
2. **避免SELECT ***：仅查询业务需要的字段，减少数据IO传输量。若不需要所有site字段，只选取必要列能显著提升速度。
3. 迁移至InnoDB引擎：MyISAM的全文索引性能整体弱于InnoDB，尤其是单行检查时的锁机制和缓存效率差异，迁移后可大幅提升单条记录的全文匹配速度。
4. 缓存查询结果：若该查询结果不频繁变化，可将结果缓存到应用层（如Redis），避免重复执行全文匹配操作。

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