使用between限制连接查询

wfsdck30 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(251)

我尝试使用另一组定义有效时间戳周期的结果来过滤一些由时间戳索引的结果。
当前查询：

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` 已经是索引。既然这些实际上是timeseries数据，有没有办法将连接限制在一个匹配项上以提高性能？
样本数据

+-------------+---------------------+------------+
| 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` 在同一分钟内进行了测量（在高级版本中，分钟可以是动态的）。

mysql performance time-series

来源：https://stackoverflow.com/questions/52870804/limit-on-join-query-using-between

3条答案

按热度按时间

ddrv8njm1#

所需综合指数：

Measurements:  INDEX(resourceId, moment)  -- in this order

你可能想要 AND (Measurements.moment BETWEEN ? AND ?) 在子查询中
在“派生表”（您拥有的子查询）中，优化器可以随意忽略 ORDER BY . 但是，如果您添加 LIMIT ，的 ORDER BY 我会很荣幸的。

赞(0）回复(0）举报 2021-06-21

6tdlim6h2#

当您使用独立的子查询（本例）时，它将在外部查询之前执行。在您的例子中，这可能是巨大的，而且可能大多数行都不是真正需要的。
如果使用内部 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

赞(0）回复(0）举报 2021-06-20

xnifntxz3#

我找到了一个使用表取消激励的解决方案：

SELECT moment, value
FROM (SELECT IF(resourceId = ? AND @previousValue = 0, NULL, actualValue)       AS value,
             measurements.moment,
             resourceId,
             @previousValue := IF(resourceId <> ?, actualValue, @previousValue) AS enabled
      FROM (SELECT *
            FROM (SELECT moment,
                         Measurements.actualValue,
                         Measurements.resourceId AS resourceId
                  FROM Measurements
                  WHERE Measurements.resourceId = ?
                    AND moment BETWEEN ? AND ?
                  UNION (SELECT start,
                                periods.actualValue AS actualValue,
                                resourceId
                         FROM (SELECT COALESCE(@previousValue <> M3.actualValue,                                            1)                                                              AS "changed",
                                      (COALESCE(@previousMoment, ?))                                           AS "start",
                                      @previousMoment := M3.moment                                             AS "stop",
                                      COALESCE(@previousValue, IF(M3.actualValue = 1, 0, 1)) AS "actualValue",
                                      M3.resourceId                                                            AS resourceId,
                                      @previousValue := M3.actualValue
                               FROM Measurements `M3`
                                      INNER JOIN (SELECT @previousValue := NULL,
                                                         @previousMoment := NULL) `d`
                               WHERE (M3.moment BETWEEN ? AND ?)
                               ORDER BY M3.resourceId ASC, M3.moment ASC) AS periods
                         WHERE periods.changed)) AS measurements
            ORDER BY moment ASC) AS measurements
             INNER JOIN (SELECT @previousValue := NULL) `k`) AS mixed
WHERE value IS NOT NULL
  AND resourceId = ?;

这实际上是每次选择运行一次表，在100ms内运行~40k x ~4k行。

赞(0）回复(0）举报 2021-06-20

我来回答

使用between限制连接查询

3条答案

相关问题

热门标签

最新问答