mysql - 测试时间戳是否在范围内的最有效方法

Question

考虑到数据存储规模不断增长，我想知道测试时间戳是否在 至 日期之间的最有效方法是什么。

设置

基本上我必须 table 。一个(A) 用于传感器数据，其中密度为每分钟，另一个(B) 表仅监控变化。

表A是每分钟存储所有传感器数据的表，并且数据不断增长。要求数据以这种密度存储并且不能被压缩。

+-----------+------------------------+-------+
| sensor_id |        datetime        | value |
+-----------+------------------------+-------+
|         1 |    2016-08-22 17:26:00 |    23 |
|         1 |    2016-08-22 17:27:00 |     5 |
|         1 |    2016-08-22 17:28:00 |    12 |
|         1 |    2016-08-22 17:29:00 |     0 |
|         1 |    2016-08-22 17:30:00 |   150 |
|         1 |    2016-08-22 17:31:00 |     9 |
+-----------+------------------------+-------+

表B是监控传感器所有状态变化的表。这些事件可以随机发生，并且不是每分钟发生一次。

+-----------+------------------------+----------+
| sensor_id |        datetime        |  state   |
+-----------+------------------------+----------+
|         1 |    2016-08-22 17:26:00 | up       |
|         1 |    2016-08-22 17:29:00 | down     |
|         1 |    2016-08-22 17:31:00 | shutdown |
+-----------+------------------------+----------+

结果

现在我想为表 A 中的每个数据映射表 B 中的相应状态，如下所示

+-----------+------------------------+-------+----------+
| sensor_id |        datetime        | value |  state   |
+-----------+------------------------+-------+----------+
|         1 |    2016-08-22 17:26:00 |    23 | up       |
|         1 |    2016-08-22 17:27:00 |     5 | up       |
|         1 |    2016-08-22 17:28:00 |    12 | up       |
|         1 |    2016-08-22 17:29:00 |     0 | down     |
|         1 |    2016-08-22 17:30:00 |   150 | down     |
|         1 |    2016-08-22 17:31:00 |     9 | shutdown |
+-----------+------------------------+-------+----------+

问题

由于存在多个传感器，两个表中的数据都在不断增长。例如，仅将所有值保留一个月并使用一个传感器将产生 43200 数据点。增加传感器数量也会增加两个表中的数据点数量，这使得映射越来越慢。

那么当我的商店增长到数百万个数据点时，检查表 A 的时间戳是否介于表 B 的状态之间的最佳方法是什么？我必须从 A 中取出每个点，然后从表 B 中查找匹配状态，该表也可能有数百万个数据点，我的猜测是，这会变得非常缓慢且低效。我需要实时执行此操作以进行分析!

谢谢

Answer 1

好吧，根据当前的数据集，以下内容似乎是有效的 - 尽管我想知道您是否过度简化了问题，以及我是否过度简化了解决方案。

SELECT x.*
     , y.state -- or possibly COALESCE(y.state,'up') state
  FROM
     ( SELECT a.*
            , MAX(b.datetime) max_datetime
         FROM table_a a 
         LEFT 
         JOIN table_b b 
           ON b.sensor_id = a.sensor_id 
          AND b.datetime <= a.datetime 
        GROUP 
           BY a.sensor_id,a.datetime
     ) x
  LEFT
  JOIN table_b y
    ON y.sensor_id = x.sensor_id
   AND y.datetime = x.max_datetime;