SQL内查询无返回时外查询全表扫描导致执行极慢的原因与优化

问题原因 #

1. 原查询无匹配行时全表扫描的原因 #

当export表中不存在schedulable=1的行时，内层子查询返回NULL，export_id = NULL的判断结果恒为不成立。但由于子查询中包含非确定性函数RAND()，MySQL优化器无法预判该子查询的返回结果是固定值，因此无法使用export_id上的索引做等值匹配，只能对export_chunk执行全表扫描逐行判断条件，在数据量数百万的场景下速度极慢。

2. COALESCE改造依然慢的原因 #

RAND()函数的特性是查询过程中每一行都会重新计算一次值，属于非确定性表达式。你将包含RAND()的子查询放在COALESCE的参数中时，优化器依然会认为整个COALESCE表达式的结果是逐行变化的，不会提前一次性计算出结果作为常量，因此每扫描export_chunk的一行就会重新执行一次内层子查询，相当于数百万次子查询叠加全表扫描，速度自然没有提升。

解决方案 #

方案1：分两次查询（最稳妥） #

因为export表只有1000行左右，单独执行内层子查询的开销可以忽略不计：

1. 先执行子查询获取目标export_id：

SELECT id FROM export WHERE schedulable=1 ORDER BY -LOG(1 - RAND())/export.weight LIMIT 1;

2. 如果第一步没有返回结果，直接返回空即可；如果有返回结果，再代入export_id查询export_chunk：

SELECT * FROM export_chunk WHERE export_id = ? AND status='PENDING' LIMIT 2;

该方案逻辑最简单，不会出现优化器判断错误的问题。

方案2：单SQL强制预计算子查询结果 #

如果必须写为单条SQL，可以将子查询放在FROM从句中作为派生表，派生表的结果只会在查询开始时计算一次：

SELECT ec.* FROM export_chunk ec
JOIN (
    -- 内层子查询只会计算一次，得到固定的target_id
    SELECT COALESCE(
        (SELECT id FROM export WHERE schedulable=1 ORDER BY -LOG(1 - RAND())/export.weight LIMIT 1),
        -1
    ) AS target_id
) t ON ec.export_id = t.target_id
WHERE ec.status = 'PENDING'
LIMIT 2;

当没有符合条件的export记录时，target_id固定为-1，优化器可以直接用export_id的索引做等值匹配，不会触发全表扫描。

必要优化：添加联合索引 #

无论使用哪种方案，都建议给export_chunk添加(export_id, status)联合索引，查询时可以直接命中索引，不需要回表，性能提升最明显。

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