解决MySQL子查询触发全表扫描的优化方案咨询

我在MySQL中执行查询时遇到异常：明明子查询仅返回1行结果，且inactive_users_6months表的user_id是主键索引，理应通过索引快速定位，但查询引擎却选择全表扫描，甚至用FORCE INDEX也无法解决。直接执行select * from inactive_users_6months where user_id IN ('XX','YY')能正常利用索引，但改用子查询就触发全表扫描。我尝试了LEFT JOIN和INNER JOIN写法并附上对应EXPLAIN结果，寻求可行优化方案。

相关SQL与执行计划 #

子查询写法及执行计划 #

explain select * 
from inactive_users_6months 
where user_id IN(
    select jid 
    from tig_pubsub_subscriptions 
    where node_id=6433274);

表结构信息 #

inactive_users_6months表结构

explain inactive_users_6months;

tig_pubsub_subscriptions表结构

explain tig_pubsub_subscriptions;

子查询单独执行计划 #

select jid from tig_pubsub_subscriptions where node_id=6433274

LEFT JOIN写法及执行计划 #

explain select * 
from inactive_users_6months A 
LEFT JOIN tig_pubsub_subscriptions B ON  A.user_id = B.jid
Where B.node_id=6433274;

INNER JOIN写法及执行计划 #

explain select * 
from inactive_users_6months A 
INNER JOIN tig_pubsub_subscriptions B ON  A.user_id = B.jid
Where B.node_id=6433274;

可行优化方案 #

• 直接使用常量替代子查询：既然子查询仅返回1行结果，先单独执行select jid from tig_pubsub_subscriptions where node_id=6433274拿到结果，再用select * from inactive_users_6months where user_id IN ('拿到的jid值')查询，确保利用主键索引。
• 给子查询加LIMIT 1：明确告知MySQL子查询只会返回单行结果，帮助优化器正确选择索引：

  explain select * 
  from inactive_users_6months 
  where user_id IN(
      select jid 
      from tig_pubsub_subscriptions 
      where node_id=6433274 LIMIT 1);
  

• 调整JOIN驱动表顺序：将tig_pubsub_subscriptions作为驱动表，先查询小结果集再关联主表，避免全表扫描：

  explain select A.* 
  from tig_pubsub_subscriptions B 
  INNER JOIN inactive_users_6months A ON A.user_id = B.jid
  where B.node_id=6433274;
  

• 检查数据类型一致性：确认inactive_users_6months.user_id与tig_pubsub_subscriptions.jid数据类型完全一致，类型转换会导致索引失效。
• 临时关闭半连接优化（谨慎使用）：通过会话级参数关闭半连接优化，强制优化器将子查询视为常量处理：

  SET optimizer_switch='semijoin=off';
  select * 
  from inactive_users_6months 
  where user_id IN(
      select jid 
      from tig_pubsub_subscriptions 
      where node_id=6433274);
  

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