主页 / dba / 问题 / 342958
Accepted
J.D.
Asked: 2024-10-12 01:46:33 +0800 CST

如何正确创建空间索引并编写利用该空间索引的 SARGable 查询？

  • 772

我似乎无法让 SQL Server 利用对象上的基本空间索引GEOGRAPHY来执行最简单的查询。

这里是dbfiddle.uk 复制版GEOGRAPHY，演示了如何创建一个带有名为 的列的简单表Coordinates，向该表添加 1 个带有多边形的行，然后在该Coordinates列上创建空间索引。

该查询看起来应该是 SARGable，但我仍然得到聚集索引扫描，而不是在空间索引上进行非聚集索引查找：

聚集索引扫描

如果我尝试强制索引提示，我会收到经典错误：

消息 8635，级别 16，状态 4，第 15 行

查询处理器无法为带有空间索引提示的查询生成查询计划。原因：空间索引不支持谓词中提供的比较器。尝试删除索引提示或删除 SET FORCEPLAN。

因此，并不是 SQL Server 认为聚集索引扫描性能更高，而是它甚至无法为空间索引提出查询计划。

根据微软关于空间索引的文档，该STDistance()函数应该适用于空间索引可优化的谓词：

空间索引支持以下谓词形式：

  • 地理1.STDistance(地理2) <= 数字

我是不是太笨了？

sql-server

1 个回答

  1. Best Answer

    您链接的文档说

    空间索引支持以下谓词形式：

    地理 1.STIntersects(地理 2) = 1

    地理1.STEquals（地理2）= 1

    地理1.STDistance(地理2) <数字

    地理1.STDistance(地理2) <=数字

    因此，只要您正在寻找距离函数的结果小于（或小于或等于）某个数字的情况，您就不应该看到有关“谓词中提供的比较器”无效的消息

    （条件也必须如上表达 = 例如，1 > Coordinates.STDistance(@Point)当明确尝试强制命名空间索引时失败，原因是“原因：在条件中找不到所需的二进制空间方法”）

    FORCESEEK可以使用或显式INDEX提示（Fiddle ）强制使用空间索引。显式索引提示似乎在发生故障时提供更多有用的信息。

    如果您确实强制使用空间索引，则执行计划将调用 [GeodeticTessellation]表值函数传递参数@Point, 768, 9, 1, @Distance以获取要在空间索引查找中使用的值，768这是默认值SPATIAL_WINDOW_MAX_CELLS，可以用相应的提示覆盖。我不确定它们9,1对应什么。

    这将发出 8 行，然后用于查找空间索引，聚合将折叠此连接的结果以为每个 PK 生成一行，然后将其用于针对基表的聚集索引查找，最后有一个带有残差谓词的过滤器。负责此操作的优化器规则是SpatialIntersectFilterOverGridIndex。

    就您的示例数据而言，这比仅扫描表并针对每一行评估谓词的成本高出 45 倍（子树成本为0.0042838vs 0.193235）

    您原来的小提琴有一个表达式WHERE Coordinates.STDistance(@Point) IS NOT NULL。这不是可以使用空间索引的两个受支持的比较器谓词之一（除非查询还满足“最近邻居”排序STDistance的所有条件）。

    1. 空间索引必须存在于其中一个空间列上，并且该方法必须在and子句STDistance()中使用该列。WHEREORDER BY
    2. TOP 子句不能包含PERCENT语句。
    3. WHERE 子句必须包含STDistance()方法。
    4. 如果子句中有多个谓词WHERE，则包含方法的谓词STDistance()必须通过 AND 连接词与其他谓词连接。STDistance()方法不能位于 WHERE 子句的可选部分。
    5. ORDER BY 子句中的第一个表达式必须使用该STDistance()方法。
    6. STDistance()子句中第一个表达式的排序顺序ORDER BY必须是ASC。
    7. 必须过滤掉所有STDistance返回的行。NULL

    这实际上最终添加了一个隐式谓词。

    执行计划

    SELECT TOP(@N) Coordinates.STDistance(@Point), * FROM dbo.GeoTest WITH (INDEX =IX_TestSpatial) 
WHERE Coordinates.STDistance(@Point) IS NOT NULL
ORDER BY Coordinates.STDistance(@Point)

    工作原理与此伪代码类似

    SELECT TOP (@N) *
FROM (VALUES 
(0,      86.153535551610716e0),
(1,     172.30707110322143e0),
(2,     344.61414220644286e0),
(3,     689.22828441288573e0),
(4,    1378.4565688257715e0),
(5,    2756.9131376515429e0),
(6,    5513.8262753030858e0),
(7,   11027.652550606172e0),
(8,   22055.305101212343e0),
(9,   44110.610202424687e0),
(10,  88221.220404849373e0),
(11, 176442.44080969875e0),
(12, 352884.88161939749e0),
(13, 705769.76323879499e0),
(14,1411539.5264775900e0),
(15,2823079.0529551799e0),
(255,1.7976931348623157e+308 /*MAX_FLOAT*/)
) V(Level1003, MaxDistance1004)
CROSS APPLY 
(
SELECT TOP (@N) *,
                Coordinates.STDistance(@Point) AS Dist,
                COUNT(*) OVER (PARTITION BY Level1003) AS CountMatchedThisLevel
FROM dbo.GeoTest WITH(FORCESEEK)
WHERE Coordinates.STDistance(@Point) <= MaxDistance1004
ORDER BY Dist
) CA
WHERE CountMatchedThisLevel >= @N OR Level1003 = 255
ORDER BY Level1003, Dist;

    它会按顺序尝试以指数方式增加距离，并可以在迭代中停止找到至少@N结果并返回TOP @N该级别的结果（如果尝试的所有距离均未找到@N结果，则返回最终级别的结果）

    • 4

相关问题