时间戳

首页 > 解决方案 > 是否可以索引运行总计以加快窗口功能？

mysql - 是否可以索引运行总计以加快窗口功能？

问题描述

我想执行：

    SELECT cust_id, SUM(cost)
    FROM purchases
    WHERE purchase_time BETWEEN('2018-01-01', '2019-01-01')
    GROUP BY cust_id

但是对于大量行，我希望必须加载每条记录以聚合适当的 SUM。我想要做的是有一个像这样的索引：

    CREATE INDEX purchase_sum 
    ON purchases(cust_id, purchase_time, 
    SUM(cost) OVER (PARTITION BY cust_id 
    ORDER BY purchase_time) AS rolling_cost)

我想要一个看起来像这样的索引：

 cust_id    time    rolling_cost
--------   ------  --------------
   1        Jan 1       5
   1        Jan 2       12
   1        Jan 3       14
   1        Jan 4       20
   1        Jan 5       24
   2        Jan 1       1
   2        Jan 2       7
   2        Jan 3       11
   2        Jan 4       14
   2        Jan 5       19
   3        Jan 1       4
   3        Jan 2       5
   3        Jan 3       10
   3        Jan 4       21
   3        Jan 5       45

由此，我的原始查询可以通过简单地为每个 cust_id 减去 2 个已知值来计算，松散地为cost_in_window = rolling_cost('2019-01-01') - rolling_cost('2018-01-01')，这不需要从源表中加载任何内容。

这可以作为索引吗？还是有另一种方法可以实现相同的目标？

标签： mysqlsqlmariadb

解决方案

您可能会发现这样更快：

select c.cust_id,
       (select sum(p.cost)
        from purchases p
        where p.cust_id = c.cust_id and
              p.purchase_time >= '2018-01-01' and
              p.purchase_time < '2019-01-01' and
       ) as total_cost
from customers c
having total_cost is not null;

然后，这可以使用上的索引purchases(cust_id, purchase_time, cost)。 计算金额只需要索引。那是一种节省。更重要的是，没有整体聚合——这可以节省更多，弥补对所有客户的计算。

但是，使用相同的索引，这可能会好一些：

select c.cust_id,
       (select sum(p.cost)
        from purchases p
        where p.cust_id = c.cust_id and
              p.purchase_time >= '2018-01-01' and
              p.purchase_time < '2019-01-01'
       ) as total_cost
from customers c
where exists (select 1
              from purchases p
              where p.cust_id = c.cust_id and
                    p.purchase_time >= '2018-01-01' and
                    p.purchase_time < '2019-01-01' 
             );

编辑：

实现所需内容的唯一方法是在数据中显式包含累积总和列。这将需要改写查询（进行所需的减法）并使用触发器来维护该值。

如果历史数据从不改变，这可能是一种合理的方法。但是，更新或插入较早的行可能会变得非常昂贵。

推荐阅读