为何MySQL中WHERE IN语句添加OR会触发全表扫描？

问题背景 #

我们使用MySQL 8.0.36版本，此前因开发者在查询中添加OR + WHERE IN (子查询)语句引发性能问题，遂开始探究这类语句组合的性能劣化场景。意外发现：即便OR条件是常量级操作，给包含WHERE IN子查询的语句添加OR也会导致性能大幅下降。

测试环境包含两张大表：

• sessions表：3900万行数据
• presenters表：3200万行数据，包含指向sessions表的非唯一外键session_id

测试案例1：仅WHERE IN子查询的高效执行 #

查询语句 #

EXPLAIN ANALYZE
select *
from `sessions`
where `sessions`.`session_id` IN (
  select `presenters`.`session_id`
  from `presenters`
  where `presenters`.`user_id` = 71
);

执行计划 #

-> Nested loop inner join  (cost=1.45 rows=1) (actual time=0.0489..0.051 rows=1 loops=1)
    -> Index lookup on presenters using presenters_user_id_foreign (user_id=71)  (cost=1.1 rows=1) (actual time=0.0301..0.032 rows=1 loops=1)
    -> Single-row index lookup on sessions using PRIMARY (session_id=presenters.session_id)  (cost=0.35 rows=1) (actual time=0.0171..0.0171 rows=1 loops=1)

性能表现：耗时不足1毫秒，采用嵌套循环+索引查找的高效执行路径。

测试案例2：添加OR常量条件后的性能劣化 #

查询语句 #

EXPLAIN ANALYZE
select *
from `sessions`
where `sessions`.`session_id` IN (
  select `presenters`.`session_id`
  from `presenters`
  where `presenters`.`user_id` = 71
)
OR 1=0; -- 替换为OR false也会触发执行计划变更

执行计划 #

-> Filter: <in_optimizer>(sessions.session_id,sessions.session_id in (select #2))  (cost=3.27e+6 rows=31.9e+6) (actual time=0.0549..78796 rows=1 loops=1)
    -> Table scan on sessions  (cost=3.27e+6 rows=31.9e+6) (actual time=0.0252..52601 rows=34e+6 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((sessions.session_id = `<materialized_subquery>`.session_id))  (cost=1.3..1.3 rows=1) (actual time=522e-6..522e-6 rows=29.4e-9 loops=34e+6)
            -> Limit: 1 row(s)  (cost=1.2..1.2 rows=1) (actual time=419e-6..419e-6 rows=29.4e-9 loops=34e+6)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (session_id=sessions.session_id)  (actual time=308e-6..308e-6 rows=29.4e-9 loops=34e+6)
                    -> Materialize with deduplication  (cost=1.2..1.2 rows=1) (actual time=0.0224..0.0224 rows=1 loops=1)
                        -> Index lookup on presenters using presenters_user_id_foreign (user_id=71)  (cost=1.1 rows=1) (actual time=0.0161..0.0177 rows=1 loops=1)

性能表现：耗时超过1分钟，触发了sessions表的全表扫描，且对每一行都执行子查询匹配，性能急剧下降。

问题咨询 #

1. 为何添加看似无意义的OR常量条件会导致执行计划发生如此巨大的变化？
2. 除了将查询拆分为两个语句再执行UNION操作外，还有哪些可行的优化解决办法？

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