mysql - 在 select where 语句中展开查询结果

Question

我需要获取同一张表上两个日期之间的所有值，而且还包括紧接在最早日期之前的值:

为了更清楚，我有下表(表 1。大约有 4600 万行):

Table 1. (Table I have)
updatetime            | value
        .                   .
        .                   .
2018-01-01 08:32:02   |     2
2018-01-01 09:23:12   |     5
2018-01-01 10:45:00   |     8 * value to include on the result.
2018-01-01 11:10:44   |    10 * <-- earliest date.
2018-01-01 13:11:54   |    12 *
2018-01-01 16:14:57   |    16 *
2018-01-01 20:00:55   |    22 *
2018-01-01 22:34:43   |    23 *
2018-01-01 23:55:23   |    23 * <-- latest date.
2018-01-02 01:03:57   |    25
2018-01-02 03:39:07   |    28
        .                   .
        .                   .

我有累积值，因此，下一个值将始终等于或大于前一个值。我需要获取 2018-01-01 11:10:44 和 2018-01-02 01:03:57 之间的所有值，但是，我还需要最早日期之前的值，这意味着我需要这个结果:(表 2.)

Table 2. (Result I need)
2018-01-01 10:45:00   |     8
2018-01-01 11:10:44   |    10
2018-01-01 13:11:54   |    12
2018-01-01 16:14:57   |    16
2018-01-01 20:00:55   |    22
2018-01-01 22:34:43   |    23
2018-01-01 23:55:23   |    23

正如您在表 1 中看到的，日期空间不相同，因此，最早日期的前一个日期对我来说是未知的，并且值递增也是随机的。

我已经尝试过这两个查询，但我对性能有疑问:

第一个只是两个查询，因为表的顺序改变，性能非常差。

SELECT * FROM his 
WHERE updatetime>=(
  SELECT updatetime 
  FROM definition 
  WHERE updatetime<"2018-01-01 11:10:44" 
  ORDER BY updatetime DESC 
  LIMIT 1
  ) and updatetime<="2018-01-02 01:03:57";

第二个应该比第一个慢，但是如果我将列定义为增量列以使 MySQL 从条件的最后一个值开始遍历表而不是遍历整个表？

SELECT * FROM his 
WHERE updatetime>=(
  SELECT MAX(updatetime) 
  FROM definition 
  WHERE updatetime<"2018-01-01 11:10:44" 
  LIMIT 1
  ) AND updatetime<="2018-01-02 01:03:57";

Answer 1

首先，要使这些查询高效执行，您需要在列 updatetime 上建立索引:

CREATE INDEX idx_updatetime ON mytable(updatetime);

一种方法是在 WHERE 中使用相关子查询选择上一条记录的子句:

SELECT t.*
FROM mytable t
WHERE 
    t.updatetime < '2018-01-02 01:03:57'
    AND NOT EXISTS (
      SELECT 1 
      FROM mytable t1 
      WHERE t1.updatetime < '2018-01-01 11:10:44' AND t1.updatetime > t.updatetime
    )

Demo on DB Fiddle :

| updatetime          | value |
| ------------------- | ----- |
| 2018-01-01 11:10:44 | 10    |
| 2018-01-01 13:11:54 | 12    |
| 2018-01-01 16:14:57 | 16    |
| 2018-01-01 20:00:55 | 22    |
| 2018-01-01 22:34:43 | 23    |
| 2018-01-01 23:55:23 | 23    |

---

另一种选择是计算 updatetime子查询中的前一条记录，然后用它来过滤表。这可能比第一个查询执行得更好。

SELECT t.*
 FROM mytable t
 INNER JOIN (
     SELECT MAX(updatetime) updatetime 
     FROM mytable 
     WHERE updatetime < '2018-01-01 11:10:44'
 ) x ON t.updatetime >= x.updatetime AND t.updatetime < '2018-01-02 01:03:57';

Demo on DB Fiddle