MySQL 8.0.34：基于变量设置的分段查询优化咨询

我们系统中的函数接收动态JSON参数，由于函数特性无法构建动态SQL，需要编写能在SQL代码内处理参数设置的高效查询。现有查询示例如下：

DECLARE p_array_source_type smallint DEFAULT JSON_EXTRACT(in_JSON,'$.source_type');
DECLARE p_array_source_record_id int DEFAULT JSON_EXTRACT(in_JSON,'$.source_record_id');
DECLARE p_array_ref_type smallint DEFAULT JSON_EXTRACT(in_JSON,'$.ref_type');
DECLARE p_array_ref_record_id int DEFAULT JSON_EXTRACT(in_JSON,'$.ref_record_id');

SELECT aa.ID
FROM association_avatar aa
WHERE 
1 = (IF(p_array_source_type IS NULL,1,aa.avatar_source_type_c = p_array_source_type))
AND
1 = (IF(p_array_source_record_id IS NULL,1,aa.avatar_source_record_id = p_array_source_record_id))
AND
1 = (IF(p_array_ref_type IS NULL,1,aa.avatar_ref_type_c = p_array_ref_type))
AND
1 = (IF(p_array_ref_record_id IS NULL,1,aa.avatar_ref_record_id = p_array_ref_record_id))            
ORDER BY aa.ID DESC
LIMIT count_limit;

当前问题是无论参数是否设置，查询都会检查所有潜在匹配项，无法让解析器根据变量动态值忽略特定条件。以下是优化后的写法：

DECLARE p_array_source_type smallint DEFAULT JSON_EXTRACT(in_JSON,'$.source_type');
DECLARE p_array_source_record_id int DEFAULT JSON_EXTRACT(in_JSON,'$.source_record_id');
DECLARE p_array_ref_type smallint DEFAULT JSON_EXTRACT(in_JSON,'$.ref_type');
DECLARE p_array_ref_record_id int DEFAULT JSON_EXTRACT(in_JSON,'$.ref_record_id');

SELECT aa.ID
FROM association_avatar aa
WHERE 
    (p_array_source_type IS NULL OR aa.avatar_source_type_c = p_array_source_type)
    AND (p_array_source_record_id IS NULL OR aa.avatar_source_record_id = p_array_source_record_id)
    AND (p_array_ref_type IS NULL OR aa.avatar_ref_type_c = p_array_ref_type)
    AND (p_array_ref_record_id IS NULL OR aa.avatar_ref_record_id = p_array_ref_record_id)
ORDER BY aa.ID DESC
LIMIT count_limit;

优化说明 #

• 这种写法直接用OR结合参数是否为NULL的判断，让查询优化器能清晰识别：当参数为NULL时，该条件直接成立，无需对对应字段做匹配检查。
• 如果association_avatar表在avatar_source_type_c、avatar_source_record_id等字段上建有索引，优化器可以在参数非空时直接利用索引过滤数据，大幅提升查询效率，避免全表扫描。
• 相比原写法的IF嵌套，这种逻辑更直观，也更符合SQL优化器的执行逻辑，能最大化利用现有索引资源。

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