如何加快 SQL Server 中的空间搜索速度？
我有一个数据库，其中包含地球上大约 100 万个位置（坐标）。我的网站有一个地图（Google 地图），用户可以通过放大地图来找到这些地点。
数据库是 SQL Server 2008 R2，我为每个标记的位置创建了一个空间列。
问题是我需要大幅减少查询时间。一个例子是覆盖几平方公里的地图区域，它可能返回 20000 个点 - 该查询在非常快的四核处理器上需要大约 6 秒的 CPU 时间。
我从地图的可见区域构建了一个形状，如下所示：
DECLARE @shape GEOGRAPHY = geography::STGeomFromText('POLYGON((' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @sw_lat) + ', ' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @ne_lat) + ', ' +
CONVERT(varchar, @sw_lng) + ' ' + CONVERT(varchar, @ne_lat) + ', ' +
CONVERT(varchar, @sw_lng) + ' ' + CONVERT(varchar, @sw_lat) + ', ' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @sw_lat) + '))', 4326)
然后查询基于此进行选择：
@shape.STIntersects(MyTable.StartPoint) = 1
a）我已确保索引确实被使用（检查了实际的执行计划）。还尝试使用索引提示。 b）我还尝试通过选择距地图中心特定距离内的所有内容来进行查询。好一点了，但仍然需要很多秒。
空间索引如下所示：
CREATE SPATIAL INDEX [IX_MyTable_Spatial] ON [dbo].[MyTable]
(
[MyPoint]
)USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
可以采取哪些措施来显着改进此搜索？我应该使用基于几何的索引吗？或者是否有其他索引设置选择不当（它们是默认设置）？
编辑------------------
我最终根本没有使用 SQL Server 空间索引。由于我只需要在地图的一个正方形内进行简单的搜索，因此使用十进制数据类型和正常的 <= 和 >= 搜索速度要快得多，并且完全足以达到目的。感谢大家对我的帮助！
I have a database with about 1 million places (coordinates) placed out on the Earth. My web site has a map (Google Maps) that lets users find those places by zooming in on the map.
The database is a SQL Server 2008 R2 and I have created a spatial column for the location of each marker.
Problem is I need to cut down query time drastically. An example is a map area covering a few square kilometers which returns maybe 20000 points - that query takes about 6 seconds of CPU time on a very fast quad core processor.
I contruct a shape out of the visible area of the map, like this:
DECLARE @shape GEOGRAPHY = geography::STGeomFromText('POLYGON((' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @sw_lat) + ', ' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @ne_lat) + ', ' +
CONVERT(varchar, @sw_lng) + ' ' + CONVERT(varchar, @ne_lat) + ', ' +
CONVERT(varchar, @sw_lng) + ' ' + CONVERT(varchar, @sw_lat) + ', ' +
CONVERT(varchar, @ne_lng) + ' ' + CONVERT(varchar, @sw_lat) + '))', 4326)
And the query then makes the selection based on this:
@shape.STIntersects(MyTable.StartPoint) = 1
a) I have made sure the index is really used (checked the actual execution plan). Also tried with index hints.
b) I have also tried querying by picking everything in a specific distance from the center of the map. It's a little bit better, but it still takes many seconds.
The spatial index looks like this:
CREATE SPATIAL INDEX [IX_MyTable_Spatial] ON [dbo].[MyTable]
(
[MyPoint]
)USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
What can be done to dramatically improve this search? Should I have a geometry-based index instead? Or are there other settings for the index that are badly chosen (they are the default ones)?
EDIT------------------
I ended up not using SQL Server Spatial indexes at all. Since I only need to do simple searches within a square of a map, using decimal data type and normal <= and >= search is so much faster, and totally enough for the purpose. Thanks everyone for helping me!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQL Server 2008（及更高版本）支持
SPATIAL索引。请参阅：http://technet.microsoft.com/en-us/library/bb895373 .aspx
获取可以在仍然能够使用索引的同时使用的函数列表。
如果使用任何其他函数，TSQL 将无法使用索引，从而降低性能。
请参阅：http://technet.microsoft.com/en-us/library/bb964712 .aspx
有关空间索引的一般信息。
SQL server 2008 (and later) supports
SPATIALindexes.See: http://technet.microsoft.com/en-us/library/bb895373.aspx
for a list of functions that can be used whilst still being able to use an index.
If you use any other function TSQL will not be able to use an index, killing performance.
See: http://technet.microsoft.com/en-us/library/bb964712.aspx
For general info on spatial indexes.
您是否尝试过使用“索引提示”？例如：
Have you tried using an "Index hint"? For Example: