公交路线查询SQL语句优化求助——半径关联性能瓶颈

针对你提到的基于半径的站点关联（t2与t3、t5与t6）性能问题，结合查询逻辑，给出以下优化策略及修改后的查询语句：

核心优化点 #

1. 半径过滤前置到JOIN阶段，减少无效关联 #

原查询在HAVING阶段才计算距离过滤，会先生成所有可能的关联结果再筛选，极大浪费资源。改为在关联t3、t5时直接加入距离条件，提前过滤掉超出半径的站点，从源头减少数据量。

2. 预计算禁止路线列表，避免重复子查询 #

原查询中rl的子查询会逐行执行，改为先一次性计算出禁止的车次ID列表，存入变量后复用，彻底消除重复计算的开销。

3. 针对性添加索引（关键优化项） #

• 给corse_fermate创建复合索引：CREATE INDEX idx_cf_sott_corsa_ordine ON corse_fermate(id_sott, id_corsa, ordine, stato, orario);
• 给tratte_sottoc添加空间索引（MySQL支持）：CREATE SPATIAL INDEX idx_ts_latlon ON tratte_sottoc(lat, lon); 若不支持空间索引，可建普通复合索引：CREATE INDEX idx_ts_sott_latlon ON tratte_sottoc(id_sott, lat, lon);
• 给changeover添加索引：CREATE INDEX idx_co_changeid ON changeover(changeid);
• 给regole_linee创建复合索引：CREATE INDEX idx_rl_az_date_days ON regole_linee(id_az, stato, da, a, giorni_sett);

4. 调整JOIN顺序，优先过滤小数据集 #

先从起点s1（id_sott=3）和终点s6（id_sott=85）的过滤条件入手，先筛选出符合条件的车次，再关联中间换乘环节，减少后续关联的数据量。

修改后的查询语句 #

-- 预计算禁止的车次列表，仅执行一次
SET @forbidden_corse = (
    SELECT GROUP_CONCAT(corse)
    FROM regole_linee
    WHERE '2023-02-24' BETWEEN da AND a
      AND FIND_IN_SET(DAYOFWEEK('2023-02-24') - 1, giorni_sett)
      AND id_az = 28 AND stato = 1
);

SELECT 
    '3' AS type,
    s1.id_sott AS id_sott1, s2.id_sott AS id_sott2, s3.id_sott AS id_sott3, s4.id_sott AS id_sott4,
    s5.id_sott AS id_sott5, s6.id_sott AS id_sott6, '0' AS id_sott7, '0' AS id_sott8,
    ch1.changeid AS changeid1, ch2.changeid AS changeid2, '0' AS changeid3,
    ABS(s2.distance - s1.distance) AS dist1,
    ABS(s4.distance - s3.distance) AS dist2,
    ABS(s6.distance - s5.distance) AS dist3,
    '0' AS dist4,
    (ABS(s2.distance - s1.distance) + ABS(s4.distance - s3.distance) + ABS(s6.distance - s5.distance)) AS km,
    s1.id_corsa AS id_corsa1, s3.id_corsa AS id_corsa2, s5.id_corsa AS id_corsa3, '0' AS id_corsa4,
    s1.orario AS orariostart1, s2.orario AS orariostop1,
    s3.orario AS orariostart2, s4.orario AS orariostop2,
    s5.orario AS orariostart3, s6.orario AS orariostop3, '0' AS orariostart4,
    IFNULL(@forbidden_corse, '0') AS rl,
    -- 用MySQL内置空间函数替代手动三角函数，效率更高
    ST_Distance_Sphere(POINT(t2.lon, t2.lat), POINT(t3.lon, t3.lat)) / 1000 AS dist_frompart1,
    ST_Distance_Sphere(POINT(t4.lon, t4.lat), POINT(t5.lon, t5.lat)) / 1000 AS dist_frompart2,
    '0' AS dist_frompart3
FROM 
    corse_fermate AS s1
    INNER JOIN corse_fermate AS s2 
        ON s1.id_corsa = s2.id_corsa 
        AND s2.ordine > s1.ordine 
        AND s1.id_sott != s2.id_sott
    INNER JOIN tratte_sottoc AS t2 ON t2.id_sott = s2.id_sott
    -- 关联t3时直接加入距离过滤，提前排除超半径站点
    INNER JOIN tratte_sottoc AS t3 
        ON ST_Distance_Sphere(POINT(t2.lon, t2.lat), POINT(t3.lon, t3.lat)) / 1000 < 5
    INNER JOIN corse_fermate AS s3 
        ON t3.id_sott = s3.id_sott 
        AND s3.stato = 1 
        AND s1.id_corsa != s3.id_corsa
    INNER JOIN corse_fermate AS s4 
        ON s3.id_corsa = s4.id_corsa 
        AND s4.ordine > s3.ordine 
        AND s2.id_sott != s4.id_sott
    INNER JOIN tratte_sottoc AS t4 ON t4.id_sott = s4.id_sott
    -- 关联t5时直接加入距离过滤
    INNER JOIN tratte_sottoc AS t5 
        ON ST_Distance_Sphere(POINT(t4.lon, t4.lat), POINT(t5.lon, t5.lat)) / 1000 < 5
    INNER JOIN corse_fermate AS s5 
        ON t5.id_sott = s5.id_sott 
        AND s5.stato = 1 
        AND s1.id_corsa != s5.id_corsa 
        AND s3.id_corsa != s5.id_corsa
    INNER JOIN corse_fermate AS s6 
        ON s5.id_corsa = s6.id_corsa 
        AND s6.id_sott = 85 
        AND s6.ordine > s5.ordine 
        AND s6.id_sott != s4.id_sott
    INNER JOIN tratte_sottoc AS t ON t.id_sott = s1.id_sott
    INNER JOIN tratte_sottoc AS t6 ON t6.id_sott = s6.id_sott
    INNER JOIN changeover AS ch1 ON s2.id_sott = ch1.changeid
    INNER JOIN changeover AS ch2 ON s4.id_sott = ch2.changeid
WHERE 
    s1.id_sott = 3 
    AND s1.stato = 1
    -- 时间条件提前过滤
    AND TIMESTAMPDIFF(MINUTE, s2.orario, s3.orario) BETWEEN 0 AND 180
    AND TIMESTAMPDIFF(MINUTE, s4.orario, s5.orario) BETWEEN 0 AND 180
    -- 禁止路线判断提前，减少后续分组排序的数据量
    AND FIND_IN_SET(s1.id_corsa, @forbidden_corse) = 0
    AND FIND_IN_SET(s3.id_corsa, @forbidden_corse) = 0
    AND FIND_IN_SET(s5.id_corsa, @forbidden_corse) = 0
GROUP BY 
    s1.id_sott, s2.id_sott, s3.id_sott, s4.id_sott, s5.id_sott, s6.id_sott,
    s1.id_corsa, s3.id_corsa, s5.id_corsa
ORDER BY km ASC 
LIMIT 5;

额外说明 #

• 若数据库不支持ST_Distance_Sphere，可换回原有的三角函数计算逻辑，但优先推荐使用内置空间函数，精度和效率更优。
• 若站点数据量极大，可考虑预计算站点间的距离表，把常用半径范围内的站点关联提前存储，查询时直接查表，彻底消除实时计算距离的开销。

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