MySQL地理空间查询：批量获取表内所有点的5个最近邻点

• 现有存储50000条点位记录的MySQL数据表，需为每个点位查询距离最近的5个点位供网站展示
• 点位数据基本静态、极少更新，原始实时查询计算开销极高、耗时过长无法满足需求
• 最终目标为全量计算所有点位的最近邻结果，新增专用列存储每个点对应的最近邻记录ID列表，通过GROUP_CONCAT生成逗号分隔的ID串，查询时直接读取预计算结果
• 当前已完成单条记录的最近邻查询逻辑验证，单条查询示例SQL如下：

SELECT c.ID, GROUP_CONCAT(items.ID) as list 
FROM cities as c, 
(SELECT ID FROM cities ORDER BY ST_Distance_Sphere(cord, POINT(-81.9242,32.5521), 3440) asc LIMIT 5) as items 
WHERE c.ID = 5185

表中cord字段为存储经纬度信息的地理空间POINT类型，上述示例中ST_Distance_Sphere()的第二个点位参数，即为ID=5185的记录存储的坐标值。

原嵌套子查询写法无法直接获取外层查询的点位坐标，需要通过正确的关联逻辑实现全量点位的最近邻批量计算。

点位数据静态场景下，全量预计算后落库是性价比最高的方案，具体实现步骤如下：

1. 先为空间字段创建索引，大幅降低距离计算开销

CREATE SPATIAL INDEX idx_cities_cord ON cities(cord);

2. 新增字段存储预计算的最近邻ID列表

ALTER TABLE cities ADD COLUMN nearest_ids VARCHAR(100) DEFAULT NULL COMMENT '最近5个点位ID，逗号分隔';

3. 执行全量计算前先调整当前会话参数，避免ID串被截断

SET SESSION group_concat_max_len = 1024;

4. 根据MySQL版本选择对应SQL执行全量计算并更新字段：

MySQL 8.0及以上版本（支持CTE、窗口函数，性能最优） #

通过窗口函数给每个点位的所有关联点位按距离排序，取前5个后聚合为逗号分隔串，直接关联更新即可：

UPDATE cities c
JOIN (
    WITH point_distance_calc AS (
        SELECT 
            a.ID AS origin_id,
            b.ID AS neighbor_id,
            ROW_NUMBER() OVER (
                PARTITION BY a.ID 
                ORDER BY ST_Distance_Sphere(a.cord, b.cord, 3440) ASC
            ) AS distance_rank
        FROM cities a
        INNER JOIN cities b ON a.ID <> b.ID
        -- 点位密度高的话可以加半径过滤减少计算量，比如仅计算50公里范围内的点位，数值根据实际业务调整
        -- AND ST_Distance_Sphere(a.cord, b.cord, 3440) < 50000
    )
    SELECT 
        origin_id,
        GROUP_CONCAT(neighbor_id ORDER BY distance_rank ASC) AS nearest_list
    FROM point_distance_calc
    WHERE distance_rank <= 5
    GROUP BY origin_id
) calc_res ON c.ID = calc_res.origin_id
SET c.nearest_ids = calc_res.nearest_list;

MySQL 5.x版本（不支持窗口函数） #

直接通过GROUP_CONCAT的排序能力聚合所有关联点位，再用SUBSTRING_INDEX截取前5个ID即可，5万条数据规模下全量计算耗时也在可接受范围内：

UPDATE cities c
JOIN (
    SELECT 
        a.ID AS origin_id,
        SUBSTRING_INDEX(
            GROUP_CONCAT(b.ID ORDER BY ST_Distance_Sphere(a.cord, b.cord, 3440) ASC),
            ',',
            5
        ) AS nearest_list
    FROM cities a
    INNER JOIN cities b ON a.ID <> b.ID
    -- 同样可加半径过滤减少计算量
    -- AND ST_Distance_Sphere(a.cord, b.cord, 3440) < 50000
    GROUP BY a.ID
) calc_res ON c.ID = calc_res.origin_id
SET c.nearest_ids = calc_res.nearest_list;

后续点位有新增/修改时，单独触发对应记录的重新计算即可，不需要全表重跑。

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