如何优化筛选满足空间范围条件的Relations数据的SQL存储过程？

我有两张数据表：

• Entities表：id（UNSIGNED INT，主键）、pos（POINT）、其他字段
• Relations表：id（UNSIGNED INT，主键）、srcId（UNSIGNED INT，外键关联Entities.id）、dstId（UNSIGNED INT，外键关联Entities.id）、其他字段

需要编写存储过程，筛选出满足以下任一条件的所有Relations记录：

1. src关联的Entity在newRect内且在oldRect外，同时dst关联的Entity也在newRect内且oldRect外；
2. src关联的Entity在newRect内且oldRect外，同时dst关联的Entity在newRect外且oldRect外；
3. src关联的Entity在newRect外且oldRect外，同时dst关联的Entity在newRect内且oldRect外。

最初尝试的SQL代码：

DECLARE oldRect DEFAULT ENVELOPE(LINESTRING(POINT(0, 0), POINT(500, 500)));
DECLARE newRect DEFAULT ENVELOPE(LINESTRING(POINT(0, 0), POINT(1000, 1000)));

SELECT DISTINCT r.*
FROM Entities AS e
JOIN Relations AS r ON e.id IN (r.srcId, r.dstId)
WHERE
    ST_CONTAINS(newRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = srcId
    )
AND NOT
    ST_CONTAINS(oldRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = srcId
    )
AND
    ST_CONTAINS(newRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = dstId
    )
AND NOT
    ST_CONTAINS(oldRect,
        SELECT re.pos
        FROM Entities AS re
        WHERE re.id = dstId
    )
.... ?;

后来想到用自定义函数简化调用，但想知道有没有更优方案：

CREATE FUNCTION getPos(id INT UNSIGNED) RETURNS POINT
BEGIN
    DECLARE pos POINT DEFAULT NULL;
    SELECT re.pos INTO pos
    FROM Entities AS re
    WHERE re.id = id;
RETURN pos;
END

不建议使用自定义函数，因为函数在每条记录调用时都会单独查询Entities表，会带来重复查询的性能损耗。更优的方式是通过两次JOIN关联Entities表，分别获取src和dst对应的位置信息，利用主键索引一次性完成关联查询，效率更高。

具体SQL实现如下：

-- 定义范围
DECLARE oldRect DEFAULT ENVELOPE(LINESTRING(POINT(0, 0), POINT(500, 500)));
DECLARE newRect DEFAULT ENVELOPE(LINESTRING(POINT(0, 0), POINT(1000, 1000)));

SELECT DISTINCT r.*
FROM Relations r
-- 关联src对应的Entity
JOIN Entities src_e ON r.srcId = src_e.id
-- 关联dst对应的Entity
JOIN Entities dst_e ON r.dstId = dst_e.id
WHERE
    -- 先统一判断：src或dst至少有一个在newRect内且oldRect外
    (
        (ST_CONTAINS(newRect, src_e.pos) AND NOT ST_CONTAINS(oldRect, src_e.pos))
        OR
        (ST_CONTAINS(newRect, dst_e.pos) AND NOT ST_CONTAINS(oldRect, dst_e.pos))
    )
    -- 再确保两个Entity都不在oldRect内（对应三个条件的共同前提）
    AND NOT ST_CONTAINS(oldRect, src_e.pos)
    AND NOT ST_CONTAINS(oldRect, dst_e.pos);

逻辑说明 #

原有的三个条件可以合并简化：

• 所有条件的共同要求：src和dst都不在oldRect范围内
• 同时满足：src或dst至少有一个在newRect范围内

合并后的逻辑和原需求完全一致，且查询效率更高——JOIN操作可以利用Entities.id的主键索引，避免了子查询或函数带来的重复查询开销。

额外优化建议 #

如果Entities.pos字段创建了空间索引，ST_CONTAINS的查询速度会大幅提升，建议执行以下语句创建索引：

CREATE SPATIAL INDEX idx_entities_pos ON Entities(pos);

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