sql-server - 查询空间索引没有响应

Question

我有一个位于 SQL Server 2008 上的数据库，其中包含约 120 亿行，所有行都包含纬度、经度和相应的地理字段。我最近需要添加查询地理字段的功能。我添加了空间索引，这花了 6 天时间来处理超过 4TB 的数据。

CREATE SPATIAL INDEX IX_Location_Geo ON Location
(
    Geo
) 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

添加预期使用这样的查询...

SELECT TOP 100 
    ci.LocationID, ci.Geo.STDistance(@g)  
FROM Location ci WITH(INDEX(IX_Location_Geo))
WHERE ci.Geo.Filter(@region) = 1 
ORDER BY ci.Geo.STDistance(@g)

这是估计执行计划...

我在 100 行的样本集上测试了这个查询，它运行得非常好。但是在 12 个账单行上，查询在大约 4 小时后没有响应，最终因磁盘写入错误而失败，这很奇怪，因为磁盘有 5TB 未使用。

Msg 1101, Level 17, State 10, Line 4 Could not allocate a new page 
for database 'TEMPDB' because of insufficient disk space in filegroup 
'DEFAULT'. Create the necessary space by dropping objects in the filegroup, 
adding additional files to the filegroup, or setting autogrowth on for 
existing files in the filegroup.

希望有人能看出我的明显疏忽。非常感谢!

Answer 1

与其使用垂直可扩展性(添加更多内存、CPU、硬盘空间 - 打造一台功能强大的机器)，不如考虑使用水平可扩展性(在许多商品服务器之间分配负载).任何操作都需要时间和空间。 Big-O 表示法描述的是，对于任何花费时间超过 O(N) 的计算，您注定要计算这样的 体积。这就是为什么从高层次上看，您会遇到错误并花费大量时间来完成查询。

可能的解决方案

改变数据访问模式。使用分片——将数据拆分成更小的 block 。广泛使用 WHERE 子句和 Skip/Take 分页模式(我不确定 T-SQL 中的正确语法)。还有 Map-Reduce 模式引起轰动。简而言之，停止在 那个 体积上垂直缩放。