SQL查询优化：基于GPS坐标快速获取各等级最近3所学校

我有一张包含4000所学校的表，每所学校都带有纬度（lat）、经度（long）坐标及等级（共3种）。需要传入当前经纬度坐标，查询每个等级下距离最近的3所学校。现有SQL代码如下：

DECLARE @current_lat DECIMAL(12, 9)
DECLARE @current_long DECIMAL(12, 9)

SET @current_lat=55.151025 set @current_long=-7.455171

DECLARE @orig geography = geography::Point(@current_lat, @current_long, 4326);
SELECT * FROM
(
    (
        SELECT top 3 *, @orig.STDistance(geography::Point(s.Lat, s.Long, 4326)) AS distance
        FROM School s
        WHERE Level = 1
        order by distance asc
    )
    UNION
    (
        SELECT top 3 *, @orig.STDistance(geography::Point(s.Lat, s.Long, 4326)) AS distance
        FROM School s
        WHERE Level = 2
        order by distance asc
    )
    UNION
    (
        SELECT top 3 *, @orig.STDistance(geography::Point(s.Lat, s.Long, 4326)) AS distance
        FROM School s
        WHERE Level = 3
        order by distance asc
    )
) s

这段代码可正常运行，但执行耗时约1秒，这个时长对API调用来说不可接受。目前已经给lat、long及level列添加了非聚集索引，现寻求优化方案缩短执行时间。

执行计划相关信息：

• 执行计划可视化截图
• 执行计划详情

优化方案 #

1. 预计算地理空间列并创建空间索引 #

现在每次查询都要动态生成geography对象，这会产生额外计算开销。建议在School表中新增一个持久化的地理空间列：

ALTER TABLE School ADD Location AS geography::Point(Lat, Long, 4326) PERSISTED;

然后为该列创建空间索引：

CREATE SPATIAL INDEX IX_School_Location ON School(Location);

这样查询时可以直接使用预计算好的Location列，避免重复创建地理对象，同时空间索引能大幅提升距离排序的效率。

2. 改写查询逻辑，用窗口函数替代多子查询UNION #

原查询每个子查询都重复计算距离，还需要三次扫描表。可以用ROW_NUMBER()窗口函数一次性处理所有等级，减少重复操作：

DECLARE @current_lat DECIMAL(12, 9) = 55.151025;
DECLARE @current_long DECIMAL(12, 9) = -7.455171;

DECLARE @orig geography = geography::Point(@current_lat, @current_long, 4326);

SELECT *
FROM (
    SELECT 
        *,
        @orig.STDistance(s.Location) AS distance,
        ROW_NUMBER() OVER (PARTITION BY s.Level ORDER BY @orig.STDistance(s.Location) ASC) AS rn
    FROM School s
) t
WHERE rn <= 3;

这个写法只需要扫描一次表（或索引），按等级分组后取每组前3条，比三个子查询UNION的方式高效很多。

3. 调整现有非聚集索引结构 #

如果暂时不想新增空间列，可以优化现有非聚集索引，把Level作为索引键，Lat、Long作为包含列，这样筛选等级时直接从索引获取坐标，避免回表：

CREATE NONCLUSTERED INDEX IX_School_Level_LatLong ON School(Level) INCLUDE (Lat, Long);

4. 避免返回不必要的列 #

原查询用SELECT *会返回所有字段，如果API不需要全部数据，明确指定需要的列，减少数据传输和处理的开销。

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