为何MySQL中带固定ID列表的查询无法执行，相似子查询却正常？

有两个结构几乎完全一致的MySQL查询，仅WHERE子句不同：

执行异常的查询 #

SELECT np.name AS name_trips, 
    s.station_name AS station_name, 
    np.id AS id, 
    COUNT(*) AS num_trips, 
    COUNT(NULLIF(np.start_end, 'Start')) AS num_start, 
    COUNT(NULLIF(np.start_end, 'End')) AS num_end
FROM name_id_pairs np
JOIN stations s ON np.id=s.station_id
WHERE id IN(2498,3794)
GROUP BY name, id
ORDER BY id, num_trips DESC;

正常快速执行的查询 #

SELECT np.name AS name_trips, 
    s.station_name AS station_name, 
    np.id AS id, 
    COUNT(*) AS num_trips, 
    COUNT(NULLIF(np.start_end, 'Start')) AS num_start, 
    COUNT(NULLIF(np.start_end, 'End')) AS num_end
FROM name_id_pairs np
JOIN stations s ON np.id=s.station_id
WHERE id IN(
    SELECT id
    FROM name_id_pairs 
    GROUP BY id
    HAVING COUNT(DISTINCT name)>1)
GROUP BY name, id
ORDER BY id, num_trips DESC;

二者基于同一个视图name_id_pairs，视图定义如下：

CREATE VIEW name_id_pairs AS
SELECT checkout_station_name AS name, checkout_station_id AS id, "Start" AS start_end
FROM trips
UNION ALL
SELECT return_station_name AS name, return_station_id AS id, "End" AS start_end
FROM trips;

异常查询会持续执行直到服务器重启，而带子查询的查询几秒内就能完成。尝试将WHERE子句改为WHERE id = 2498 OR id = 3794，或者明确指定id为s.station_id/np.id，都无法解决问题，需要知道性能差异的原因。

• 视图展开后的执行逻辑不同：MySQL的视图本质是“虚拟表”，查询时会把视图定义直接展开到主查询里。带硬编码ID的查询展开后，会先对trips表做两次全表扫描（因为视图是UNION ALL了两个trips查询），生成视图的全部数据后再过滤ID为2498、3794的行，最后做JOIN和分组聚合。如果trips表数据量极大，全表扫描的开销会直接拖垮查询。
• 子查询被优先优化：带子查询的语句中，子查询SELECT id FROM name_id_pairs GROUP BY id HAVING COUNT(DISTINCT name)>1会被优化器优先处理。优化器会先对展开后的视图（两次trips扫描）做分组筛选，这个过程能利用trips表中checkout_station_id或return_station_id的索引快速分组统计，筛选出符合条件的少量ID。之后主查询只针对这些少量ID去扫描trips表的对应行，再做JOIN和聚合，速度自然快很多。
• 谓词下推失效：理论上，优化器应该把WHERE id IN(2498,3794)下推到视图的两个SELECT语句中，也就是扫描trips表时直接过滤ID，避免全表扫描。但可能因为视图是UNION ALL结构，或者MySQL特定版本的优化器存在逻辑缺陷，导致这个下推没生效。最终只能先生成视图的全部数据再过滤，数据量大时完全无法承受。
• 索引利用差异：子查询的分组操作能借助trips表的相关索引快速完成，而硬编码ID的查询因为谓词没下推，无法利用这些索引，只能走全表扫描，效率天差地别。

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