mysql - 基于列计算非连续行集的优化方法

Question

原文:Counting non-contiguous values

我将稍微更改一下结构，以便更清楚地说明我正在尝试做的事情。

给定:

+------+---------------+---------------+----+
| guid | current_level | current_value | pk |
+------+---------------+---------------+----+
| a    |           100 |            12 |  1 |
| a    |           200 |            12 |  2 |
| a    |           200 |            12 |  3 |
| a    |           200 |            12 |  4 |
| a    |           300 |            14 |  7 |
| a    |           300 |            12 |  9 |
| a    |           200 |            14 | 12 |
| b    |           100 |            10 |  5 |
| b    |           100 |            10 |  8 |
| b    |           200 |            12 | 11 |
| b    |           100 |            12 | 13 |
| b    |           200 |            12 | 14 |
| b    |           300 |            12 | 15 |
| b    |           200 |            12 | 16 |
+------+---------------+---------------+----+

我想计算每个 guid 进入 200 级的总次数，忽略它停留在 200 级的行。所以 200 级的连续行应该被视为 1，而转换从 200 -> 100(或 300) -> 200 算作 2。

鉴于上述结构，我正在寻找的结果是:

+------+-------+-------+
| guid | level | times |
+------+-------+-------+
| a    |   200 |     2 |
| b    |   200 |     3 |
+------+-------+-------+

原始问题(上面链接)在技术上确实有效，但是当解决方案用于具有 1.8M 行的表时，它需要大约 30 秒，这不是最佳的。

注意:解决方案 (sq) 的内部子查询往往花费不到一秒钟，但整个查询执行不佳(如果有人能解释为什么会这样，我将不胜感激，可能是因为临时表？)

问题是在给定表格大小的情况下，什么是完成我正在尝试的工作的有效方法。

供引用的旧查询:

SELECT guid, SUM(TIMES) FROM (
    SELECT  guid, current_level ,
            if(@id <> guid, @lev := 10, 0) AS useless,
            if(@id <> guid, @id := guid, 0) AS useless2,
            (case when (current_level = 200
                    AND current_level <> @lev) then 1 else 0 end) as TIMES,
            if(current_level <> @lev, @lev := current_level, 0) AS useless3

     FROM sensor_logs
     , (SELECT @id := 'none', @lev := 10) var_init_subquery
     ORDER BY guid
) sq
GROUP BY guid

Answer 1

您在这里提到性能是您关心的问题，随着记录数量的增加，尝试任何类型的选择查询同样会花费时间。

在我看来，一种方法是

1. 在表上为“插入后”创建一个触发器
2. 根据NEW.guid，找到最后一条记录，看是否为200
3. 只用 guid 和计数维护一个单独的表
4. 当最后一条记录不是 200 时，更新 guid 的新表计数(如果没有记录则插入)

总体感觉，这样会优化性能