MySQL JOIN查询时groupsTable的nCode索引被忽略如何解决

问题根因分析 #

1. LEFT JOIN逻辑失效：你声明了LEFT JOIN，但WHERE条件中增加了t.cStat='y'和p.stat = t.nCode的限制，相当于强制要求右表groupsTable必须存在匹配行，实际执行时已经被优化为INNER JOIN，会干扰优化器的表连接顺序判断。
2. groupsTable数据量过小：从EXPLAIN结果可以看到groupsTable的扫描行数为1、type为system，说明表中总共只有1行数据，MySQL优化器判定走索引的开销比直接全表读取更高，因此主动放弃使用索引。
3. 联合索引覆盖不足：groupsTable的现有联合索引是(nCode, cStat, aCode)，你的查询需要读取groupName字段，该字段不在索引列表中，即使走索引也需要回表查询主键索引获取数据，进一步降低了索引的性价比。

解决方法 #

1. 修正JOIN逻辑（根据业务需求选择） #

如果确实需要保留左连接逻辑，把t.cStat='y'的过滤条件移到ON子句中，不要放在WHERE里：

select p.pid, p.title, t.groupName 
from postsTable as p
left join groupsTable as t 
on p.stat = t.nCode and t.cStat='y'
where p.mainID=0 
group by p.pid 

如果业务本身就要求必须匹配groupsTable的有效数据，直接把LEFT JOIN改成INNER JOIN，逻辑更清晰，也能减少优化器的判断成本。

2. 调整groupsTable的索引为覆盖索引 #

把需要查询的groupName字段加到联合索引的末尾，让索引可以覆盖整个查询需求，不需要回表：

-- 删除原有索引
drop index nCode on groupsTable;
-- 新建覆盖索引
create index idx_ncode_cstat_groupname on groupsTable(nCode, cStat, groupName);

3. 强制指定索引（仅测试验证用，不推荐线上直接使用） #

如果后续数据量增长后索引仍不命中，可以用FORCE INDEX临时测试索引是否有效：

select p.pid, p.title, t.groupName 
from postsTable as p
inner join groupsTable as t FORCE INDEX(idx_ncode_cstat_groupname)
on p.stat = t.nCode
where p.mainID=0 and t.cStat='y'
group by p.pid 

4. 数据量验证 #

如果当前groupsTable确实只有个位数行数据，优化器不选择索引是正常的性能最优选择，不需要强行干预，等数据量上升到数百行以上，优化器会自动选择更合适的索引。

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