兼容MySQL 5.7.41的百万级坐标匹配最优查询需求

核心思路 #

针对百万级locations表，优先利用MySQL空间函数提升性能，先过滤1500米内的地点减少计算量，再匹配最近地点，最后按规则格式化结果。

第一步：优化places表（必做，提升性能） #

由于要处理百万级数据，必须给places表添加空间索引，否则关联查询会异常缓慢：

-- 添加空间字段存储经纬度点
ALTER TABLE places ADD COLUMN point POINT;
-- 更新空间字段值（注意POINT格式是POINT(经度, 纬度)）
UPDATE places SET point = POINT(lon, lat);
-- 创建空间索引
CREATE SPATIAL INDEX idx_places_point ON places(point);

第二步：查询语句（兼容MySQL 5.7.41） #

SELECT
    loc.id, -- 替换为locations表实际主键或需要返回的字段
    CASE
        WHEN nearest.distance < 300 THEN nearest.place
        WHEN nearest.distance BETWEEN 300 AND 1500 THEN CONCAT('Near ', nearest.place, ' ~ ', ROUND(nearest.distance), 'm')
        ELSE 'N/A'
    END AS location_info
FROM locations loc
LEFT JOIN (
    -- 子查询获取每个location的最近地点及距离
    SELECT
        loc_inner.id,
        p.place,
        ST_Distance_Sphere(
            -- 拆分locations的coords为经纬度，转为POINT
            POINT(
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', -1) AS DECIMAL(10,6)),
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', 1) AS DECIMAL(10,6))
            ),
            p.point
        ) AS distance
    FROM (
        -- 提前拆分coords，避免重复计算
        SELECT
            id,
            coords
        FROM locations
    ) loc_inner
    JOIN places p
    -- 先过滤1500米内的地点，减少后续计算
    ON ST_DWithin(
        POINT(
            CAST(SUBSTRING_INDEX(loc_inner.coords, ',', -1) AS DECIMAL(10,6)),
            CAST(SUBSTRING_INDEX(loc_inner.coords, ',', 1) AS DECIMAL(10,6))
        ),
        p.point,
        1500
    )
    -- 筛选每个location的最近地点（MySQL 5.7不支持窗口函数，用NOT EXISTS实现）
    AND NOT EXISTS (
        SELECT 1
        FROM places p2
        WHERE ST_DWithin(
            POINT(
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', -1) AS DECIMAL(10,6)),
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', 1) AS DECIMAL(10,6))
            ),
            p2.point,
            1500
        )
        AND ST_Distance_Sphere(
            POINT(
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', -1) AS DECIMAL(10,6)),
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', 1) AS DECIMAL(10,6))
            ),
            p2.point
        ) < ST_Distance_Sphere(
            POINT(
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', -1) AS DECIMAL(10,6)),
                CAST(SUBSTRING_INDEX(loc_inner.coords, ',', 1) AS DECIMAL(10,6))
            ),
            p.point
        )
    )
) nearest ON loc.id = nearest.id;

替代方案（无需修改places表结构） #

如果无法修改places表，可使用Haversine公式计算距离，但性能会比空间函数差，适合数据量较小的场景：

SELECT
    loc.id,
    CASE
        WHEN nearest.distance < 300 THEN nearest.place
        WHEN nearest.distance BETWEEN 300 AND 1500 THEN CONCAT('Near ', nearest.place, ' ~ ', ROUND(nearest.distance), 'm')
        ELSE 'N/A'
    END AS location_info
FROM locations loc
LEFT JOIN (
    SELECT
        loc_inner.id,
        p.place,
        -- Haversine公式计算距离（单位：米）
        6371000 * 2 * ASIN(SQRT(
            POWER(SIN((loc_inner.lat - p.lat) * PI()/180 / 2), 2) +
            COS(loc_inner.lat * PI()/180) * COS(p.lat * PI()/180) *
            POWER(SIN((loc_inner.lon - p.lon) * PI()/180 / 2), 2)
        )) AS distance
    FROM (
        -- 提前拆分coords为数值类型
        SELECT
            id,
            CAST(SUBSTRING_INDEX(coords, ',', 1) AS DECIMAL(10,6)) AS lat,
            CAST(SUBSTRING_INDEX(coords, ',', -1) AS DECIMAL(10,6)) AS lon
        FROM locations
    ) loc_inner
    JOIN places p
    -- 过滤1500米内的地点
    ON 6371000 * 2 * ASIN(SQRT(
        POWER(SIN((loc_inner.lat - p.lat) * PI()/180 / 2), 2) +
        COS(loc_inner.lat * PI()/180) * COS(p.lat * PI()/180) *
        POWER(SIN((loc_inner.lon - p.lon) * PI()/180 / 2), 2)
    )) <= 1500
    -- 筛选最近地点
    AND NOT EXISTS (
        SELECT 1
        FROM places p2
        WHERE 6371000 * 2 * ASIN(SQRT(
            POWER(SIN((loc_inner.lat - p2.lat) * PI()/180 / 2), 2) +
            COS(loc_inner.lat * PI()/180) * COS(p2.lat * PI()/180) *
            POWER(SIN((loc_inner.lon - p2.lon) * PI()/180 / 2), 2)
        )) <= 1500
        AND 6371000 * 2 * ASIN(SQRT(
            POWER(SIN((loc_inner.lat - p2.lat) * PI()/180 / 2), 2) +
            COS(loc_inner.lat * PI()/180) * COS(p2.lat * PI()/180) *
            POWER(SIN((loc_inner.lon - p2.lon) * PI()/180 / 2), 2)
        )) < 6371000 * 2 * ASIN(SQRT(
            POWER(SIN((loc_inner.lat - p.lat) * PI()/180 / 2), 2) +
            COS(loc_inner.lat * PI()/180) * COS(p.lat * PI()/180) *
            POWER(SIN((loc_inner.lon - p.lon) * PI()/180 / 2), 2)
        ))
    )
) nearest ON loc.id = nearest.id;

性能优化说明 #

1. 空间索引优先：ST_Distance_Sphere结合空间索引的查询效率远高于Haversine公式，是百万级数据的首选方案。
2. 提前过滤范围：用ST_DWithin（或Haversine的距离条件）先筛选1500米内的地点，避免对所有地点进行距离计算。
3. 减少重复计算：子查询提前拆分coords字段，避免在多个地方重复调用SUBSTRING_INDEX和类型转换。
4. 主键关联：确保locations表的主键（如id）有索引，提升关联查询速度。

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