空间索引应用

前篇：http://www.cnblogs.com/Gin-23333/p/4995199.html  _(:з」∠)_

空间索引，一个专门用来处理空间数据计算的索引，目前支持 STContains()、STDistance()、STEquals()、STIntersects()、STOverlaps()、STTouches() 和 STWithin()

然而通常我们平常使用的，通常就是 STDistance() 求两点之间的距离。

 

更加详细の资料，请戳：https://msdn.microsoft.com/zh-cn/library/bb895265(v=sql.120).aspx 

先看下空间索引的创建语法，简单的必填项就是酱~

CREATE SPATIAL INDEX IndexName ON #Tmp(GeographyCoordinate)  

    WITH (BOUNDING_BOX=(XMIN=-180,YMIN=-90,XMAX=180,YMAX=90))   

 

*最好要保证最大最小值要覆盖整个坐标系的值域，否则不能最有效使用索引

 

好，上个不太严谨的Sample

然后我们测试一下。创建了一个模拟酒店表 (数据量大概是45W左右)

    Hotel，只有主键，其他没有任何东西，坐标存在 GeographyCoordinate 的栏位里面

    Tmp_Hotel1 ，结构和Hotel完全一致，并在GeographyCoordinate 里面创建了一个空间索引。

 

DECLARE @DT DATETIME=GETDATE()
DECLARE @GEO GEOMETRY=GEOMETRY::STGeomFromText('POINT(39.8666 20.017)',0)
 
SELECT DATEDIFF(ms,@DT,GETDATE())
SELECT *
    FROM Hotel
        WHERE GeographyCoordinate.STDistance(@GEO) < 0.43       
SELECT DATEDIFF(ms,@DT,GETDATE())
SELECT *
    FROM Tmp_Hotel1
        WHERE GeographyCoordinate.STDistance(@GEO) < 0.43      SELECT DATEDIFF(ms,@DT,GETDATE())

结果如下 ，可见第一句执行了1320ms ,第二句仅仅执行了 50ms，这是一个坐标在45W数据里面的大概分析情况

然后我们把比对的数据扩大10条，比如说我要找这10个酒店5公里之内的其他酒店。首先我会建个临时表存放那10间酒店的位置。

--随机取10条，避免被读取顺序误导
SELECT TOP 10 ID,
              GeographyCoordinate
        INTO #Tmp
    FROM dbo.Hotel 
        WHERE ORDER BY NEWID()

顺手开个统计看看

SET STATISTICS IO ON

--不使用索引直接关联
SELECT a.*,b.ID
    FROM dbo.Hotel a
        INNER JOIN #Tmp b ON a.GeographyCoordinate.STDistance(b.GeographyCoordinate) < 0.43  

--使用索引关联
SELECT a.*,b.ID
    FROM dbo.Tmp_Hotel1 a
        INNER JOIN #Tmp b ON a.GeographyCoordinate.STDistance(b.GeographyCoordinate) < 0.43        

结果 28s,还有8次的Hotel物理扫描 ╮(╯_╰)╭

表 '#Tmp'。扫描计数 1，逻辑读取 9 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Hotel'。扫描计数 8，逻辑读取 241960 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

第二位空间索引选手的表现呢，执行时间18s。看起来比没有索引好不了多少

表 'Tmp_Hotel1'。扫描计数 0，逻辑读取 1332493 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Workfile'。扫描计数 48，逻辑读取 1384 次，物理读取 96 次，预读 1440 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'extended_index_907150277_384000'。扫描计数 5413，逻辑读取 18390 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#Tmp'。扫描计数 1，逻辑读取 9 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

然而，根据资料，欲用空间索引，必先在Where 和 OrderBy 使用效果更佳。加上Order By 试下

SELECT a.*,b.ID
    FROM dbo.Tmp_Hotel1 a
        INNER JOIN #Tmp b ON a.GeographyCoordinate.STDistance(b.GeographyCoordinate) < 0.43
        ORDER BY a.GeographyCoordinate.STDistance(b.GeographyCoordinate)

执行时间6S，比没有OrderBy 选手好多了~

表 '#Tmp'。扫描计数 1，逻辑读取 9 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Tmp_Hotel1'。扫描计数 0，逻辑读取 1332549 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Workfile'。扫描计数 48，逻辑读取 1440 次，物理读取 96 次，预读 1440 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'extended_index_907150277_384000'。扫描计数 5413，逻辑读取 18525 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

看IO其实两边差不多，为什么时间差辣么远？我看了一下才发现，加上了OrderBy 之后，执行计划会使用并行，所以速度刷一下上去了

--尝试一下看看帮#Tmp加个空间索引会不会有更大提升
ALTER TABLE #Tmp ADD CONSTRAINT PK_Tmp PRIMARY KEY (ID)
CREATE SPATIAL INDEX #IX_Tmp ON #Tmp(GeographyCoordinate)
    WITH (BOUNDING_BOX=(XMIN=-180,YMIN=-90,XMAX=180,YMAX=90))

再执行一下第二句，执行时间也是差不多，应该是临时表数据量太少，没有走到索引，╮(╯_╰)╭。

表 '#Tmp'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Tmp_Hotel1'。扫描计数 0，逻辑读取 1332553 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Workfile'。扫描计数 48，逻辑读取 1376 次，物理读取 96 次，预读 1440 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'extended_index_907150277_384000'。扫描计数 5413，逻辑读取 18546 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

其实到这里，我觉得有个定性的认识就差不多了。

总结：要使用空间索引

1、主要对付查询 STDistance  函数等问题。

2、建索引的时候边界范围要禁可能包含出现的值域

3、在Where条件和 OrderBy 都要使用 STDistance 函数

4、特别注意Null 值会影响空间索引的性能

写在最后：

其实我也想过一下其他替代STDistance建空间索引的方式。

1、建一个对照表，预先做个笛卡尔积，这样就可以不用调度函数  （想到此方法先给自己2个耳光，维护成本太高了）

2、可以把XY值抽取出来，用个游标来做数值计算，不使用 STDistance 进行计算。这样我测试过性能还算可以。（但是逼格不够高，而且看起来有点蠢╮(╯_╰)╭）

3、一般来说可以用Top 或者其他条件加强筛选，减少待计算的数据量，也有助于提高性能。