在大型数据集中查找给定查找日期的最后一个已知事件

Question

我有一个特定的设计问题，这个问题已经困扰我很长一段时间了。我有一个大型的实时GPS定位日志表，其中包含数千个资产的点条目。每个资产每天都会有数百个点数进入数据库。我有一个索引IX(资产asc，EventTime asc)来加速目标资产的点查询。我有一个LastKnownLocation表，用于将每个资产与其最新的点相关联。这只为我提供了最近一次已知的查找。我的问题是，有没有人知道一种有效的方法，能够在给定特定查找日期的情况下一次查询位置日志表中的最近已知位置？“问:2012年7月1日结束时，我的所有资产在哪里？”

顺便说一句，由于每个资产都使用自己的内部单调eventtime戳报告其点数，因此与每个资产相关的表的LocationLog.LocationLogID auto inc主键之间存在隐含的单调关系。这就是我可以使用MAX聚合的原因。

SELECT MAX(LocationLog.LocationLogID) FROM LocationLog 
WHERE LocationLog.fk_AssetID IN 
(
    //LIST OF required assets for report
) 
AND LocationLog.EventTime <= '2012/07/01 23:59:59'
GROUP BY LocationLog.fk_AssetID

问题在于，数据库索引IX提供了对单个资产的所有点的快速访问。然后，这些点在索引中按eventtime排序，因此dbengine可能会在索引中的eventtime内执行数据扫描，以查找日期为<=查找日期的最大LocationLogID。资产在查找日期之前报告的时间越长，查找匹配的扫描时间就越长。

因为我的位置日志是90+百万行，所以像这样增加1000个资产查询需要50秒。

查找给定查找日期的最后一个已知事件必须是一个众所周知的设计模式，但是我无法搜索到它。

PS:运行MSSQL2000，但迁移到Postgres

Answer 1

它可能有助于消除(...)中。

试试像这样的东西

--
--  index on AssetID, EventTime desc
--
select
      AssetID
    , LocationID
    , EventTime
from LocationLog as a
join (
        select AssetID_1
        union
        select AssetID_2
        union
        select AssetID_3
        -- etc, list of assets needed in report
      ) as b on b.AssetID = a.AssetID
where a.EventTime = (select max(xx.EventTime) 
                       from LocationLog as xx 
                      where xx.AssetID = a.AssetID
                        and xx.EventTime <= '2012-08-28 12:05:00')
;

Answer 2

两件事浮现在脑海中：

• 维护您的位置的实体化视图，存储每天每项资产的结束位置；或
• 分区、分区、分区！比方说，如果你在每个分区中只有一周的数据，那么这样的搜索就会便宜得多。

我可能会创建一个end_of_day_location表，而不是每天结束时只清空我的last_known_location表，而是首先将其内容INSERT到end_of_day_location中。