mysql - 使用 Between 进行连接查询的限制

Question

我正在尝试使用另一组定义有效时间戳周期的结果来过滤按时间戳索引的一些结果。

当前查询:

SELECT Measurements.moment AS "moment",
       Measurements.actualValue,
       start,
       stop
FROM Measurements
       INNER JOIN (SELECT COALESCE(@previousValue <> M.actualValue AND @previousResource = M.resourceId, 1) AS "changed",
                          (COALESCE(@previousMoment, ?)) AS "start",
                          M.moment AS "stop",
                          @previousValue AS "actualValue",
                          M.resourceId,
                          @previousMoment := moment,
                          @previousValue := M.actualValue,
                          @previousResource := M.resourceId
                   FROM Measurements `M`
                          INNER JOIN (SELECT @previousValue := NULL, @previousResource := NULL, @previousMoment := NULL) `d`
                   WHERE (M.moment BETWEEN ? AND ?) AND
                         (M.actualValue > ?)
                   ORDER BY M.resourceId ASC, M.moment ASC) `changes` ON Measurements.moment BETWEEN changes.start AND changes.stop
WHERE (Measurements.resourceId = 1) AND
      (Measurements.moment BETWEEN ? AND ?) AND
      (changes.changed)
ORDER BY Measurements.moment ASC;

resourceId, moment 已经是索引。由于这些实际上是时间序列数据，有什么方法可以限制仅 1 个匹配的连接以提高性能？

示例数据

+-------------+---------------------+------------+
| actualValue | moment              | resourceId |
+-------------+---------------------+------------+
|        0.01 | 2018-09-26 07:50:25 |        1   |
|        0.01 | 2018-09-26 07:52:35 |        1   |
|        0.01 | 2018-09-26 07:52:44 |        2   |
|        0.01 | 2018-09-26 07:52:54 |        1   |
|        0.01 | 2018-09-26 07:53:03 |        1   |
|        0.01 | 2018-09-26 07:53:13 |        2   |
|        0.01 | 2018-09-26 07:53:22 |        1   |
|        0.01 | 2018-09-26 07:54:32 |        1   |
|        0.01 | 2018-09-26 07:55:41 |        1   |
|        0.01 | 2018-09-26 07:56:51 |        1   |
+-------------+---------------------+------------+

预期输出:使用 resourceId=1 进行的所有测量，其中 resourceId=2 在同一分钟内进行了测量(在高级版本中，分钟可以是动态的)。

+-------------+---------------------+------------+
| actualValue | moment              | resourceId |
+-------------+---------------------+------------+
|        0.01 | 2018-09-26 07:52:35 |        1   |
|        0.01 | 2018-09-26 07:52:54 |        1   |
|        0.01 | 2018-09-26 07:53:03 |        1   |
|        0.01 | 2018-09-26 07:53:22 |        1   |
+-------------+---------------------+------------+

Answer 1

当您使用独立子查询(本例)时，它完全在外部查询之前执行。在您的情况下，这可能会很大，并且可能大多数行并不是真正需要的。

如果您使用内部JOIN 重新表述查询，则对表的辅助访问将立即被过滤掉，从而无需对表进行全面扫描。

尝试以下查询:

select 
    m.moment,
    m.actualValue,
    c.moment as start,
    timestampadd(minute, 1, c.moment) as stop
  from Measurements m
  join Measurements c on m.moment
    between c.moment and timestampadd(minute, 1, c.moment)
  where m.resourceId = 1
    and c.resourceId = 2
    and m.moment between ? and ?
  order by m.moment