MySQL空间JOIN场景下如何使用分组函数完成表更新操作？

直接在UPDATE语句的SET子句中使用未分组的聚合函数SUM()会触发R_INVALID_GROUP_FUNC_USE错误，聚合函数需要先明确分组维度才能返回计算结果，MySQL不支持在关联更新的顶层直接执行聚合计算。

现有可运行版本存在两个性能短板：

• 冗余扫描两次shapes表，逻辑冗余
• 未使用空间索引，15万条办公点数据每次空间判断都需要全量遍历，累计运算量极高

1. 前置空间索引创建（核心提速手段） #

给办公点坐标字段创建空间索引，可将空间匹配效率提升数十至上百倍：

CREATE SPATIAL INDEX idx_offices_coords ON offices(coords);

同步给shapes表的shape字段创建空间索引：

CREATE SPATIAL INDEX idx_shapes_shape ON shapes(shape);

2. 优化后的更新语句 #

去掉冗余表扫描，新增MBR粗过滤逻辑，先排除肯定不在区域范围内的办公点，再执行高精度空间判断，减少无效计算：

UPDATE shapes s
LEFT JOIN (
    SELECT 
        s.shape_id,
        SUM(o.revenue) AS total_rev
    FROM shapes s
    JOIN offices o 
    ON MBRContains(
        CASE 
            WHEN s.type = 'circle' THEN ST_Buffer(s.shape, s.radius)
            ELSE s.shape 
        END, o.coords
    )
    AND (
        CASE
            WHEN s.type = 'circle' THEN ST_Distance_Sphere(o.coords, s.shape) < s.radius
            ELSE ST_Contains(s.shape, o.coords)
        END
    )
    GROUP BY s.shape_id
) AS calc ON s.shape_id = calc.shape_id
SET s.totalRevenue = IFNULL(calc.total_rev, 0);

如果允许无办公点的区域营收字段为NULL，可去掉IFNULL函数直接赋值。

简化版语法（适合小体量区域数据） #

因shapes表仅有350条数据，可直接使用关联子查询实现，语法更简洁，性能与上述JOIN版本无显著差异：

UPDATE shapes s
SET s.totalRevenue = (
    SELECT SUM(o.revenue)
    FROM offices o
    WHERE MBRContains(
        CASE WHEN s.type = 'circle' THEN ST_Buffer(s.shape, s.radius) ELSE s.shape END,
        o.coords
    )
    AND (
        CASE
            WHEN s.type = 'circle' THEN ST_Distance_Sphere(o.coords, s.shape) < s.radius
            ELSE ST_Contains(s.shape, o.coords)
        END
    )
);

• MySQL 8.0以下版本ST_Buffer对地理坐标的计算可能存在偏差，可手动计算圆形外接矩形替代ST_Buffer做MBR过滤
• 执行更新操作前建议先备份shapes表的totalRevenue字段数据，避免逻辑异常导致数据错误

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