mysql - 缓存非常慢的查询的结果集

Question

我有一个大约有 1300 万行的表。每行代表特定项目在特定日期的特定小时内对特定类型的测量。

我有一个查询，根据测量类型查找这些值的总和或平均值。速度很慢，大概几分钟。

我们有某些报告页面利用此查询的结果，但页面需要数分钟才能加载，这是 Not Acceptable 。到目前为止，我的解决方案是将查询结果缓存在我所说的汇总表中。

问题是刷新汇总表的夜间运行脚本运行时间太长。我什至不想立即刷新整个汇总表，但仍然需要很长时间。 (“太长”是指出现错误并且刷新作业未完成。)

我有一种预感，我面临的挑战是由于以错误的方式处理事情造成的，解决方案可能不是调整一些东西来将查询运行时间减少 1% 在这里，但要以完全不同的方式解决问题。

如有任何建议，我们将不胜感激。如果我没有以很好的方式提出这个问题，我深表歉意；我不知道如何更好地表述它。很高兴提供澄清或更多详细信息。

这是一个需要永远运行的查询的简化版本。 (即使是这个简化版本也需要相当长的时间。)

  select date(calc_dt),
         project_id,
         calculation_type_cd,
         sum(result)
    from calc_calculation_results
group by date(calc_dt),
         project_id,
         calculation_type_cd

夜间作业基本上是一个SELECT INTO，它获取此查询的结果并将它们放入我的汇总表中。 结果 列是我们在报告中感兴趣的值。

Answer 1

汇总表——很好。重建它们——糟糕。相反，每晚逐渐增加它们。

使用汇总表，主表需要很少的索引，从而提高加载效率。

汇总表具有适合查询的任何索引。

More discussion of Summary Tables

您的简化版本可能会变成

INSERT INTO Summary ( date, project_id, type_cd, sum_result )
    select CURDATE() - INTERVAL 1 DAY,
           project_id,
           calculation_type_cd,
           sum(result)
    from calc_calculation_results
    WHERE calc_dt >= CURDATE() - INTERVAL 1 DAY
      AND calc_dt  < CURDATE()
    group by project_id,
             calculation_type_cd

可能有

PRIMARY KEY(date, project_id, type_cd),
INDEX(project_id, date),
INDEX(type_cd, date)