你的位置:首页 > 数据库

[数据库]空间索引应用


前篇: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 或者其他条件加强筛选,减少待计算的数据量,也有助于提高性能。