我可以创建一个“覆盖，空间”吗？ SQL Server 2008 中的索引？
我目前有一个网站，其中有一个表，其中包含纬度/经度浮动列，以及这两列的索引以及我需要检索的另一列。
我不断地查询这个表来获取从某个点开始的半径范围内的行（我实际上得到了一个速度的平方），但我只需要已经索引的字段，所以这个索引实际上覆盖了，执行计划只有 2 个步骤：
Index Seek (cost: 100%) and SELECT (cost: 0%)
现在，我正在尝试利用 SQL 2008 的空间功能。我创建了 Geography 列，填充它，创建了空间索引，一切顺利。
一切都工作正常，除了执行计划有一百万个步骤，并且 74% 的时间花费在聚集索引查找上，它将在空间索引中找到的行连接到实际表中，以获取其余的内容数据...
（空间索引查找占用执行计划成本的 1%）
因此，显然，它正确地使用了空间索引，并通过纬度/经度上的“常规”索引比以前更快地找到我需要的记录，但是加入主表简直要了我的命，空间查询花费的时间是我旧表的 7 倍。
有没有办法向空间索引添加更多列，以便它能够覆盖并且可以一步完成操作，就像以前一样？
我还可以做其他事情来改善这种情况吗？
更新：我发现“常规”索引可以使用 INCLUDE 关键字“包含”其他列（我不知道，我过去只包含索引本身中的列）
根据文档此处，该子句不是空间索引的选项... 有什么想法吗？
谢谢！
丹尼尔
I currently have a site with a table that has Lat/Long float columns, and an index over those 2 columns plus another one I need to retrieve.
I'm constantly querying this table to get the rows that fall within a radius from a certain point (I'm actually getting a square for speed), but I only need the fields that are already indexed, so this index is in fact covering, and the execution plan has only 2 steps:
Index Seek (cost: 100%) and SELECT (cost: 0%)
Now, I'm trying to take advantage of the spatial features of SQL 2008. I've created the Geography column, filled it, created the spatial index, the works.
And it all works fine, except that the execution plan has a million steps, and 74% of the time is spent on a Clustered Index Seek, where it joins the rows it found in the Spatial Index to the actual table, to get the rest of the data...
(The Spatial Index Seek takes 1% of the Execution Plan Cost)
So, apparently, it IS using the Spatial index appropriately and finding the records I need much faster than before with my "regular" index over Lat/Long, but the joining to the main table is KILLING me, the Spatial query takes 7 times as long as my old one.
Is there any way to add more columns to the spatial index so that it'll be covering and it can do things in one step, just like it was doing before?
Are there other things I could do to improve this situation?
UPDATE: I found that "regular" indexes can "include" other columns using the INCLUDE keyword (which I didn't know about, I used to just include the columns in the index itself)
According to the documentation here, that clause is not an option for Spatial Indexes...
Any ideas?
Thanks!
Daniel
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不，不幸的是，目前无法创建覆盖空间索引 - 查询将始终对基表进行书签查找，以便获取该行的地理值。
对于 STIntersects，这显然始终是必需的，因为我们仍然需要对实际地理对象执行二次过滤以验证它实际上与参数对象相交。但是，如果您不需要精确的答案并且可以使用 Filter()，那么可以从索引中提供主键列，而根本不需要查找基表。我们正在考虑在下一个版本中支持这一点。
在加速当前查询方面，您是否尝试过使用 Filter() 并使用 sp_help_geography_index 的输出调整索引？
No, unfortunately there is currently no way to create a covering spatial index - the query will always do the bookmark lookup to the base table in order to get the geography value for the row.
For STIntersects, this is clearly always required as we still need to perform the secondary filter on the actual geography object to verify that it actually intersects the parameter object. However, if you do not require exact answers and can use Filter(), then it would be possible to provide the primary key columns from the index without doing the lookup to the base table at all. Supporting this is something we are considering for the next release.
In terms of speeding up your current query, have you tried using Filter() and tuning your index with the output from sp_help_geography_index?