mysql - 优化隐式自连接查询

Question

假设我有下表log。它描述了一系列与序列号关联的时间戳事件。有各种事件类型列，这里只显示 2 列 - session_started 和 voltage_changed。在每一行中，只会有一个非空的事件类型。所有行都有非空的 serial 和 time_stamp 字段。第一行将两种事件类型都设置为 NULL，这意味着其他事件类型列之一(未显示)包含一个值(它有助于代表性样本)。

我想为每个发生的 session_started 事件找到记录的下一个 voltage_changed 值(按时间戳)。这是数据:

serial ||     time_stamp        || session_started || voltage_changed

BBBB    |  2017-12-15 03:05:55  |        NULL      |        NULL    |
AAAA    |  2017-12-15 04:05:55  |        1         |        NULL    |
AAAA    |  2017-12-15 04:30:55  |        NULL      |        127     |
AAAA    |  2017-12-15 05:15:55  |        NULL      |        75      |
BBBB    |  2017-12-15 05:20:55  |        1         |        NULL    |
BBBB    |  2017-12-15 06:00:55  |        NULL      |        10      |

以及期望的结果:

serial ||     time_stamp        ||    voltage

AAAA    |  2017-12-15 04:05:55  |       127        |
BBBB    |  2017-12-15 05:20:55  |       10         |

这是我试过的查询。它在这个示例表上运行并产生了正确的结果，但是在整个表上运行需要很长时间(我厌倦了等待查询完成执行......)整个表有 190,000 行并且有一个索引在 time_stamp 上。

SELECT 
   h.serial, 
   h.time_stamp,
   hh.voltage_changed AS voltage 
FROM 
   log h, 
   log hh 
WHERE 
   h.serial = hh.serial 
   AND hh.time_stamp = (SELECT MIN(hh.time_stamp) 
                        FROM  log hh 
                        WHERE (hh.time_stamp >= h.time_stamp) 
                        AND hh.voltage_changed IS NOT NULL 
                        AND (h.session_started = 1));

有没有办法优化此查询以在大型表上更有效地工作？在 time_stamp 上有一个索引是否足够，或者我们应该在这种情况下考虑其他列？

Answer 1

因为你只想要一列，我认为自连接是不必要的。我首先将其写为相关子查询:

select l.*,
       (select l2.voltage_changed
        from log l2
        where l2.serial = l.serial and
              l2.time_stamp >= l.time_stamp and
              l2.voltage_changed is not null
        order by l2.time_stamp asc
        limit 1
       ) as voltage_changed
from log l
where l.session_started = 1;

为此，您需要两个索引。比较重要的是log(serial, voltage_changed, time_stamp)。第二个是 log(session_started, serial)。