构建支持数据变更的滚动求和表的性能优化咨询

背景与原查询 #

我有一张存储每日指标的daily_reports表，结构为(parent_id, object_id, report_date, metric_1, metric_2, ...)，其中(object_id, report_date)为唯一键，每个对象每天最多一条记录。应用中频繁执行以下聚合查询：

SELECT object_id,
       SUM(metric_1)    AS metric_1,
       SUM(metric_2)    AS metric_2,
       ...
FROM daily_reports
WHERE parent_id IN (...)
  AND report_date BETWEEN :start_date AND :end_date
GROUP BY object_id
order by metric_1 desc

该查询因需对大量object_id做求和聚合，执行速度缓慢。

当前滚动求和实现方案及问题 #

为优化查询性能，我尝试构建每个object_id自首次出现以来的滚动求和表cumulative_reports，需满足以下约束：

• 并非每个object_id每日都有记录，可能存在任意时长的断档
• 会有新的object_id加入daily_reports
• daily_reports近30天的数据可能变更，更早数据不会变更

滚动求和表需要“延续”未出现在当日报告中的object_id，且近30天的滚动求和需每日重新计算。我编写了逐日构建滚动求和表的存储过程：

DELIMITER //
CREATE OR REPLACE PROCEDURE update_cumul_reports(
    IN p_start_date DATE,
    IN p_end_date DATE,
    IN p_parent_id BIGINT
)
BEGIN
    DECLARE current_report_date DATE;
    DECLARE latest_cumul_date DATE;

    SET current_report_date = p_start_date;

    WHILE current_report_date <= p_end_date DO
        -- 查找已存在滚动求和的最近日期
        SELECT MAX(report_date) INTO latest_cumul_date
        FROM cumulative_reports
        WHERE report_date < current_report_date
          AND parent_id = p_parent_id;

        INSERT INTO cumulative_reports
        (object_id, parent_id, report_date, metric_1, metric_2, metric_3, metric_4)
        SELECT
            -- 延续已存在的object_id
            cumul.object_id,
            COALESCE(raw.parent_id, cumul.parent_id),
            current_report_date AS report_date,
            COALESCE(cumul.metric_1, 0) + COALESCE(raw.metric_1, 0) AS metric_1,
            COALESCE(cumul.metric_2, 0) + COALESCE(raw.metric_2, 0) AS metric_2,
            ...
        FROM cumulative_reports AS cumul
                 LEFT JOIN daily_reports AS raw
                           ON cumul.object_id = raw.object_id
                               AND raw.report_date = current_report_date
                               AND raw.parent_id = p_parent_id
        WHERE cumul.report_date = latest_cumul_date
          AND cumul.parent_id = p_parent_id

        UNION ALL

        SELECT
            -- 新增当日首次出现的object_id
            raw.object_id,
            raw.parent_id,
            current_report_date,
            raw.metric_1,
            raw.metric_2,
            raw.metric_3,
            raw.metric_4
        FROM daily_reports AS raw
        WHERE raw.report_date = current_report_date
          AND raw.parent_id = p_parent_id
          AND NOT EXISTS (
            SELECT 1
            FROM cumulative_reports c
            WHERE c.report_date = latest_cumul_date
              AND c.parent_id = p_parent_id
              AND c.object_id = raw.object_id
        );

        -- 切换至下一日
        SET current_report_date = DATE_ADD(current_report_date, INTERVAL 1 DAY);
    END WHILE;
END;
//
DELIMITER ;

cumulative_reports表设置了两个索引：(parent_id, report_date, object_id)主键和(report_date, object_id)普通索引。查询该表的速度极快，但构建速度随表规模增长而变慢：单父ID的31天数据构建需2分钟，全量历史数据构建需10-15分钟。如果不延续无报告的object_id，查询逻辑会变得复杂且缓慢。

补充信息 #

• 使用数据库版本：10.6.18-MariaDB-log，无法使用ColumnStore
• daily_reports表有(parent_id, report_date)索引，共约20亿行，包含近2亿唯一object_id，数据回溯5-6年且无删除操作
• 原查询单父ID两个月区间需20秒，要求优化至5秒以内

优化咨询 #

请问有哪些优化思路或更优的滚动求和表实现方案？

内容的提问来源于stack exchange，提问作者Zakaria