空间索引,一个专门用来处理空间数据计算的索引,目前支持 STContains()、STDistance()、STEquals()、STIntersects()、STOverlaps()、STTouches() 和 STWithin()
然而通常我们平常使用的,通常就是 STDistance() 求两点之间的距离。
 
更加详细の资料,请戳:https://msdn.microsoft.com/zh-cn/library/bb895265(v=sql.120).x 
先看下空间索引的创建语法,简单的必填项就是酱~
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 或者其他条件加强筛选,减少待计算的数据量,也有助于提高性能。