TSQL瓦片场景下高效空间查询优化问题咨询

问题根源回顾 #

当前强制使用空间索引后，虽然空间扫描仅耗时0.11秒返回2万条记录，但由于空间索引未包含BeginNetworkId、EndNetworkId和RouteHierarchy字段，必须回表执行2万次聚集索引查找以验证过滤条件，这是性能瓶颈的核心原因。

针对性优化方案 #

1. 创建覆盖型空间索引

直接将过滤所需的时间维度字段和道路层级字段加入空间索引的包含列，使空间扫描后无需回表即可完成所有条件验证：

CREATE SPATIAL INDEX IX_RoadLinks_Geometry_Covering 
ON RoadLinks(Geometry)
INCLUDE (BeginNetworkId, EndNetworkId, RouteHierarchy)
WITH (CELLS_PER_OBJECT = 8); -- 根据道路数据密度调整，线性数据建议4-16

• 优势：空间索引扫描后直接获取过滤所需字段，彻底避免聚集索引查找操作；通过CELLS_PER_OBJECT调整索引分割粒度，适配道路线性数据的存储特性。

2. 利用Id分段特性实现分区表优化

针对数据中Id<10000和Id>10000000的分段特性，按Id范围创建分区表，缩小查询时的扫描范围：

-- 创建分区函数
CREATE PARTITION FUNCTION PF_RoadLinks_Id(int)
AS RANGE LEFT FOR VALUES (10000);

-- 创建分区方案
CREATE PARTITION SCHEME PS_RoadLinks_Id
AS PARTITION PF_RoadLinks_Id
ALL TO ([PRIMARY]);

-- 重建主键绑定分区
ALTER TABLE RoadLinks
DROP CONSTRAINT PK_RoadLinks_Id;

ALTER TABLE RoadLinks
ADD CONSTRAINT PK_RoadLinks_Id PRIMARY KEY CLUSTERED (Id)
ON PS_RoadLinks_Id(Id);

-- 重建空间索引绑定分区
DROP INDEX IX_RoadLinks_Geometry_Covering ON RoadLinks;
CREATE SPATIAL INDEX IX_RoadLinks_Geometry_Covering 
ON RoadLinks(Geometry)
INCLUDE (BeginNetworkId, EndNetworkId, RouteHierarchy)
ON PS_RoadLinks_Id(Id);

• 优势：查询时SQL Server仅扫描目标Id范围对应的分区，减少无效数据扫描量，尤其在大Bounding Box场景下效果显著。

3. 避免SELECT *，使用列推式查询

瓦片服务仅需渲染所需字段（如Geometry、RouteHierarchy、道路名称等），明确指定列而非返回全表数据：

SELECT 
    r.Geometry,
    r.RouteHierarchy,
    r.RoadName -- 仅保留瓦片服务需要的字段
FROM [RoadLinks] AS r
WHERE r.[Geometry].STIntersects(geometry::STGeomFromText('POLYGON ((528601 164864, 528601 172032, 535769 172032, 535769 164864, 528601 164864))', 27700)) = 1
AND r.[BeginNetworkId] <= 2
AND r.[EndNetworkId] >= 2
AND r.[RouteHierarchy] IN (0,1,2,3)

• 优势：配合覆盖型空间索引，实现索引覆盖扫描，完全消除回表操作的开销。

4. 动态层级过滤的索引适配

针对瓦片缩放层级对应不同RouteHierarchy的需求，可创建带层级过滤的空间索引（若层级范围固定），或在查询时动态传入层级列表，让SQL Server在索引扫描阶段就完成层级过滤：

-- 若低缩放层级仅需高速路（RouteHierarchy=0,1），可创建专用过滤索引
CREATE SPATIAL INDEX IX_RoadLinks_Geometry_Highway
ON RoadLinks(Geometry)
INCLUDE (BeginNetworkId, EndNetworkId)
WHERE RouteHierarchy IN (0,1);

• 优势：不同缩放层级使用对应索引，进一步减少扫描返回的记录数。

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