使用IN子句的MySQL查询性能异常缓慢的原因咨询

1. IN子查询的执行逻辑缺陷 #

MySQL对IN (子查询)的处理逻辑在很多场景下会触发重复执行子查询的问题：它不会先执行子查询得到完整结果集再和主表匹配，而是将其转换为相关子查询——也就是对主表stores的每一行，都重新执行一遍嵌套的子查询。哪怕子查询单独执行只需要几秒，一旦主表有上万行，就相当于重复执行了上万次子查询，整体耗时会呈指数级增长。

而JOIN方式的执行逻辑完全不同：查询优化器会先分析所有关联表的关系，生成高效的执行计划，通常是先计算出关联的中间结果集，再和主表进行匹配，避免了重复计算的开销。

2. 多层嵌套IN的优化限制 #

你的查询嵌套了三层IN子查询，且每层都使用了DISTINCT。MySQL的查询优化器对这种多层嵌套的IN结构支持有限，无法有效合并查询逻辑，也很难对去重操作进行优化，导致执行计划的复杂度急剧上升，额外开销被不断放大。

换成JOIN后，优化器可以清晰识别表与表之间的关联关系，合理安排连接顺序，还能利用索引减少不必要的去重操作，大幅降低执行成本。

3. 索引利用效率差异 #

使用IN子查询时，MySQL可能无法有效利用主表stores的schedule_id索引：当子查询结果集较大时，优化器可能会选择全表扫描主表，再逐行与子查询结果进行匹配，这会带来大量的IO开销。

而JOIN语句可以让优化器直接利用stores.schedule_id与schedules.id的关联索引，通过索引快速定位匹配的数据，大幅减少需要扫描的行数，提升查询速度。

改写后的JOIN示例 #

将你的查询改为JOIN方式后，执行计划会更高效，示例如下：

SELECT DISTINCT s.*
FROM stores s
JOIN schedules sch ON s.schedule_id = sch.id
JOIN users u ON sch.user_id = u.id
JOIN tb_CMSTempRute x1 ON u.account_id = x1.kode
JOIN tb_CMSTempRute x2 ON sch.date = x2.tgl
WHERE x1.flag = 'xxx'
  AND x1.create_user = 'ADMIN'
  AND x2.flag = 'xxx'
  AND x2.create_user = 'ADMIN';

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