如何优化查询，使其首先在一个索引上搜索，然后在另一个索引上搜索。

Question

如何优化查询，使其首先在一个索引上搜索，然后在另一个索引上搜索。

sql-serverindexperformancejoinspatialquery-performance

12

我有两组来自卫星数据的地球测量值，每组都有时间字段（平均儒略日期mjd）和地理位置（GeoPoint，空间），我希望找到这两组之间的巧合，使它们的时间在3小时（或0.125天）的阈值内相符，并且它们的距离相互之间在200公里以内。

我已经为两个表的mjd字段和空间表建立了索引。

当我只根据时间约束进行连接时，数据库在8秒钟内计算出100,000个匹配项，并在此期间计算出所有100,000个匹配项的距离。查询如下所示：

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

执行的计划如下：

Only mjd constraint

当排序后，有9个距离在200公里以下，所以存在匹配。问题是，当我添加距离约束并运行这个时，

select top 10 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
and h.GeoPoint.STDistance(m.GeoPoint)<200000
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

它消失了很长一段时间。显然，在8秒内，它可以找到100,000个时间匹配，其中有9个距离在200公里以下，所以优化器肯定正在尝试一些次优的方法。计划看起来与上述类似，只是增加了一个距离过滤器（我猜的）。

with spatial constrant, no spatial filter

我可以通过这个来强制使用空间索引：

select top 5 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0 
from L2V5.dbo.header h join L2.dbo.MLS_Header m 
on h.GeoPoint.STDistance(m.GeoPoint)<200000
and h.mjd between m.mjd-.125 and m.mjd+.125 
option( table hint ( h, index(ix_MJD), index(ix_GeoPoint) ), table hint( m, index(ix_MJD) ) )

both constraints with both indexes

然后需要3分钟来查找5个匹配项。

我要如何告诉查询优化器首先使用MJD索引查找，然后再使用空间索引（或者它已经这样做了），并且有没有办法通过告诉它期望多少个匹配项来帮助它？如果它能在8秒内计算出具有9个小于200公里距离的100,000个匹配项，那么加入空间索引不应该使它更快而不是更慢吗？

感谢任何其他提示或想法。

编辑：回答没有提示时计划是什么样子的，就是这样的（而且花费很长时间）：

no hints

也许值得一提的是，一个表中有近100万条记录，另一个表中有800万条记录。

- user261963

如果您移除这些提示，您的查询计划会是什么样子？ - Zane

@Zane，我编辑了帖子并添加了无提示查询计划。它用扫描替换了查找操作，而且执行时间非常糟糕。 - user261963

1个回答

- Rob Farley · Accepted Answer

问题在于它可能（而且考虑到空间索引，很可能）会假设空间过滤器比时间过滤器更具选择性。

但是如果你有几百万条记录在200公里范围内，那么情况可能会更糟糕。

你要求它找到200公里范围内的记录，这将返回按某种空间顺序排序的数据。在其中找到接近时间的记录意味着要逐个检查每一条记录。

或者你按时间找到记录，并按时间顺序获取结果。然后，将此列表筛选为200公里半径就需要逐个检查每一条记录。

如果你以这样的方式对数据进行两次过滤，那么使用索引来应用第二个过滤条件将变得困难。如果时间过滤器更为严格，最好告诉它不要使用空间索引。

如果两者都很大，只有同时满足时才紧密，那么你面临的是一个更复杂的问题，人们长期以来一直试图解决这个问题，可以通过覆盖三维（及以上）空间的索引来很好地解决。但是SQL Server没有这样的索引。

抱歉。

编辑：更多信息...

这个问题类似于找到覆盖特定时间点的时间范围。当你搜索在该时间点之前开始的记录时，你会得到一堆无序的结束时间 - 反之亦然。如果你在电话簿中寻找姓氏以F开头的人，你很难找到名字以R开头的人。而且，名字的索引也无法帮助你，原因是同样的。当你的第一个索引不是相等的时候，在下一个索引中找到东西是困难的。

现在，如果你能将日期过滤器改为相等过滤器（或一系列相等过滤器），那么你就有机会了，只是空间索引是一种特殊类型的索引，不能用作复合索引的第二级。

所以，恐怕你面临着尴尬的局面。:(

编辑：试试这个：

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
where h.GeoPoint.STDistance(m.GeoPoint)/1000.0 < 200
option( table hint ( h, index(ix_MJD) ) );

请注意，我故意通过在比较之前除以1000来破坏可搜索性。我希望这项工作在关键查找中完成。

顺便说一下，您可以通过在ix_MJD索引中都包含GeoPoint和Time来避免查找（和提示）的需要。这肯定会减轻查询计划的压力。