求助：优化因GROUP BY与HAVING条件导致缓慢的SQL查询

1. 修正变量赋值笔误 #

原语句中@lon错误使用了latitude字段赋值，应改为longitude；同时子查询结果唯一，用=替代IN更高效：

set @lat = (select latitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39));
set @lon = (select longitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39));

2. 移除冗余的GROUP BY，改用提前过滤 #

原查询中GROUP BY distance完全多余——distance是每行的计算值，并非聚合结果。用HAVING会先计算所有行的距离再过滤，效率极低，改为子查询提前计算后过滤，或直接在WHERE中判断：

写法1：直接在WHERE中过滤 #

set @lat = (select latitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39));
set @lon = (select longitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39));

SELECT 
    sp2.rkd_property_zip,
    sp1.code,
    round(6371.393 * ACOS(
        COS(RADIANS(@lat)) * COS(RADIANS(sp1.latitude)) * COS(RADIANS(sp1.longitude) - RADIANS(@lon)) 
        + SIN(RADIANS(@lat)) * SIN(RADIANS(sp1.latitude))
    ), 0) as distance
FROM hp0.t_zip sp1
INNER JOIN t_rdk sp2 ON sp1.code = sp2.rkd_property_zip
WHERE 
    round(6371.393 * ACOS(
        COS(RADIANS(@lat)) * COS(RADIANS(sp1.latitude)) * COS(RADIANS(sp1.longitude) - RADIANS(@lon)) 
        + SIN(RADIANS(@lat)) * SIN(RADIANS(sp1.latitude))
    ), 0) < 10;

写法2：子查询计算后过滤（可读性更强） #

set @lat = (select latitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39));
set @lon = (select longitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39));

SELECT *
FROM (
    SELECT 
        sp2.rkd_property_zip,
        sp1.code,
        round(6371.393 * ACOS(
            COS(RADIANS(@lat)) * COS(RADIANS(sp1.latitude)) * COS(RADIANS(sp1.longitude) - RADIANS(@lon)) 
            + SIN(RADIANS(@lat)) * SIN(RADIANS(sp1.latitude))
        ), 0) as distance
    FROM hp0.t_zip sp1
    INNER JOIN t_rdk sp2 ON sp1.code = sp2.rkd_property_zip
) temp
WHERE temp.distance < 10;

3. 添加索引提速 #

• 给t_zip的code字段加索引：CREATE INDEX idx_t_zip_code ON hp0.t_zip(code);
• 给t_rdk的rkd_property_zip字段加索引：CREATE INDEX idx_t_rdk_rkd_zip ON t_rdk(rkd_property_zip);
• 若频繁做地理距离查询，可给t_zip的latitude、longitude加联合索引，或使用数据库空间索引（如MySQL的SPATIAL索引）。

4. 减少重复计算 #

提前计算目标坐标的三角函数值，避免每行重复运算：

set @lat_rad = RADIANS((select latitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39)));
set @lon_rad = RADIANS((select longitude from t_zip where code = (SELECT id_house FROM hp0.t_house_config where id_house=39)));
set @cos_lat = COS(@lat_rad);
set @sin_lat = SIN(@lat_rad);

SELECT *
FROM (
    SELECT 
        sp2.rkd_property_zip,
        sp1.code,
        round(6371.393 * ACOS(
            @cos_lat * COS(RADIANS(sp1.latitude)) * COS(RADIANS(sp1.longitude) - @lon_rad) 
            + @sin_lat * SIN(RADIANS(sp1.latitude))
        ), 0) as distance
    FROM hp0.t_zip sp1
    INNER JOIN t_rdk sp2 ON sp1.code = sp2.rkd_property_zip
) temp
WHERE temp.distance < 10;

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