如何優化查詢，使其首先查找一個索引，然後再查找另一個索引

我有兩組來自衛星數據的地球測量值，每組都有時間場（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) ) )

執行的計劃是：

排序後，有 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 公里以下，因此優化器一定是在嘗試一些次優的東西。該計劃看起來類似於上面的距離過濾器（我猜）。

我可以通過以下方式強制使用空間索引：

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) ) )

然後需要 3 分鐘才能找到 5 個匹配項。

我如何告訴查詢優化器首先使用 MJD 索引搜尋，然後是空間索引（或者它已經在做什麼），有什麼方法可以通過告訴它預期有多少匹配來幫助它？如果它可以在 8 秒內計算出 100,000 個匹配距離，其中有 9 個在 200 公里以下，那麼添加空間索引不應該讓它更快而不是更慢嗎？

感謝您提供任何其他提示或想法。

編輯：要回答沒有提示的計劃是什麼樣子的問題，這個（而且它需要永遠）：

可能還值得一提的是，一個表中有近 1M 條記錄，另一張表中有 8M 條記錄

問題是它可能（並且知道空間索引，可能會）假設空間過濾器將比時間過濾器更具選擇性。

但如果你在 200 公里內有幾百萬條記錄，那麼情況可能會更糟。

您要求它查找 200 公里內的記錄，這將返回按空間順序排序的數據。在那裡找到時間接近的記錄意味著檢查每一個。

否則，您將按時間查找記錄，並按時間順序獲得結果。然後，將此列表過濾到 200 公里半徑是檢查每一個的問題。

如果像這樣在兩個範圍內過濾數據，則很難使用索引應用第二個過濾器。如果時間過濾器更嚴格，您最好告訴它不要使用空間索引。

如果兩者都單獨很大，並且只有在一起才緊密，那麼您有一個更複雜的問題，人們已經嘗試解決了很長時間，並且可以通過涵蓋 3D（及以上）的索引很好地解決空間。除了 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) ) );

請注意，在與 200 進行比較之前，我故意通過除以 1000 來打破 sargability。我希望在 Key Lookup 中完成這項工作。

請注意，您可以通過在兩個 ix_MJD 索引中包含 GeoPoint 和 Time 來避免查找（和提示）的需要。這肯定會從查詢計劃中消除一些熱量。

引用自：https://dba.stackexchange.com/questions/100778