MySQL5.7 InnoDB全文检索联合整数字段查询性能极低如何优化

查询3性能差的核心原因是MySQL优化器的行估算错误：执行计划显示优化器认为全文检索仅返回1行数据，所以选择了fx_cat全文索引作为执行路径，实际检索java会返回168944条匹配记录，数据库需要对这16万条记录全部回表，逐条判断tenant是否等于429，大量随机IO导致耗时飙升。

方案1：新增带tenant前缀的联合全文索引（最优） #

InnoDB支持在全文索引的前缀位置添加普通整数类型字段，索引构建后会先按tenant值对全文索引做分区，查询时可以直接在全文索引内部完成tenant=429的过滤，再执行关键词检索，全程无需回表，性能最优。
建索引语句：

ALTER TABLE `cvprofiles` 
ADD FULLTEXT INDEX `fx_cat_tenant` (
    `tenant`,
    `skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`,
    `abilityKeywords`,`lngKeywords`,`roleKeywords`,
    `industryKeywords`,`educationKeywords`,`prefLocKeywords`
);

索引创建完成后，原有查询3无需修改即可自动命中新索引，耗时可以降至毫秒级。

方案2：改写SQL缩小匹配范围（无需改索引，次优） #

利用tenant=429仅返回845条记录的特性，先过滤出该租户的所有主键，再对这少量数据执行全文匹配，避免16万次回表：

SELECT COUNT(*) 
FROM (
    SELECT tid FROM cvprofiles WHERE tenant = 429
) AS tenant_records
INNER JOIN cvprofiles AS c ON tenant_records.tid = c.tid
WHERE MATCH(
    `skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`,
    `abilityKeywords`,`lngKeywords`,`roleKeywords`,
    `industryKeywords`,`educationKeywords`,`prefLocKeywords`
) AGAINST ('java' IN BOOLEAN MODE);

方案3：强制走tenant索引（临时应急） #

如果前两个方案暂时无法落地，可以用FORCE INDEX强制查询优先使用tenant的普通索引，先过滤出845条租户记录再做全文匹配：

SELECT COUNT(*)
FROM cvprofiles FORCE INDEX(sel)
WHERE 
    MATCH(
        `skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`,
        `abilityKeywords`,`lngKeywords`,`roleKeywords`,
        `industryKeywords`,`educationKeywords`,`prefLocKeywords`
    ) AGAINST ('java' IN BOOLEAN MODE)
    AND tenant=429;

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