基于Table2指定时间区间对超大规模Table1聚合查询的最优方案

场景明确 #

• Table1：数十亿行时序明细，date+symbol+time组合唯一，需按区间聚合value字段
• Table2：5000万行区间任务表，date+symbol可重复，每条记录含starttime/endtime区间
• 核心需求：为Table2每条记录计算对应date+symbol下，Table1在starttime至endtime内的value总和

基础前提：必须到位的索引优化 #

所有方案的核心基础是索引，没有索引一切都是空谈：

• Table1：创建复合覆盖索引 (date, symbol, time) INCLUDE (value)（MySQL无INCLUDE，直接建(date, symbol, time, value)），确保查询时直接从索引获取数据，无需回表
• Table2：创建复合索引 (date, symbol)，用于快速分组或关联时定位数据

最优方案选型 #

1. 预聚合+分区裁剪（通用跨数据库方案） #

针对时序数据的特性，提前按date+symbol+时间粒度（小时/分钟）预聚合sum(value)，将数十亿行明细压缩为百万级聚合数据，是效率提升最显著的方案。

步骤1：创建预聚合表

-- 示例：按小时粒度预聚合
CREATE TABLE Table1_agg (
    date DATE,
    symbol VARCHAR(32),
    hour_start DATETIME, -- 格式如'2024-05-20 10:00:00'
    sum_value DECIMAL(18,4),
    PRIMARY KEY (date, symbol, hour_start)
);

步骤2：定时刷新预聚合数据

用定时任务（如数据库事件、Airflow）按小时刷新：

INSERT INTO Table1_agg (date, symbol, hour_start, sum_value)
SELECT 
    date,
    symbol,
    DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS hour_start,
    SUM(value) AS sum_value
FROM Table1
WHERE date = CURRENT_DATE() -- 仅刷新当天数据，避免全表扫描
GROUP BY date, symbol, hour_start
ON DUPLICATE KEY UPDATE sum_value = VALUES(sum_value);

步骤3：查询关联预聚合表

将Table2的区间拆分为整周期聚合值+首尾零散明细值，避免全明细扫描：

SELECT 
    t2.date,
    t2.symbol,
    t2.starttime,
    t2.endtime,
    -- 整小时聚合值
    COALESCE(SUM(a.sum_value), 0) +
    -- 区间开头不足1小时的明细求和
    COALESCE((SELECT SUM(value) FROM Table1 
              WHERE date = t2.date 
                AND symbol = t2.symbol 
                AND time >= t2.starttime 
                AND time < DATE_FORMAT(t2.starttime, '%Y-%m-%d %H:00:00') + INTERVAL 1 HOUR), 0) +
    -- 区间结尾不足1小时的明细求和
    COALESCE((SELECT SUM(value) FROM Table1 
              WHERE date = t2.date 
                AND symbol = t2.symbol 
                AND time >= DATE_FORMAT(t2.endtime, '%Y-%m-%d %H:00:00') 
                AND time < t2.endtime), 0) AS total_sum
FROM Table2 t2
LEFT JOIN Table1_agg a 
    ON a.date = t2.date
    AND a.symbol = t2.symbol
    AND a.hour_start >= DATE_FORMAT(t2.starttime, '%Y-%m-%d %H:00:00')
    AND a.hour_start < DATE_FORMAT(t2.endtime, '%Y-%m-%d %H:00:00')
GROUP BY t2.date, t2.symbol, t2.starttime, t2.endtime;

2. 数据库特定区间关联优化 #

如果无法做预聚合，可利用数据库原生特性做高效关联，核心是避免笛卡尔积，逐条处理Table2记录：

PostgreSQL：LATERAL JOIN

利用LATERAL逐条关联Table2记录，直接扫描Table1的索引区间：

SELECT 
    t2.*,
    COALESCE(t1_total.sum_val, 0) AS total_sum
FROM Table2 t2
LEFT JOIN LATERAL (
    SELECT SUM(value) AS sum_val
    FROM Table1
    WHERE date = t2.date
      AND symbol = t2.symbol
      AND time BETWEEN t2.starttime AND t2.endtime
) t1_total ON true;

MySQL：STRAIGHT_JOIN强制执行顺序

强制先扫描小表（Table2），再逐条查询Table1的索引区间：

SELECT 
    t2.date,
    t2.symbol,
    t2.starttime,
    t2.endtime,
    COALESCE(SUM(t1.value), 0) AS total_sum
FROM Table2 t2
STRAIGHT_JOIN Table1 t1
    ON t1.date = t2.date
    AND t1.symbol = t2.symbol
    AND t1.time BETWEEN t2.starttime AND t2.endtime
GROUP BY t2.date, t2.symbol, t2.starttime, t2.endtime;

3. 分区表强化优化 #

若数据库支持分区（MySQL/PostgreSQL/Oracle），将Table1按date做分区（如每天一个分区），查询时自动裁剪分区，仅扫描对应日期的分区数据，将数十亿行的全表扫描缩小为单日百万级数据扫描。

避坑要点 #

• 禁止使用RIGHT JOIN后过滤：会先生成两张表的笛卡尔积，再过滤，完全无法处理大表
• 禁止在WHERE中对Table1的time字段做函数运算：会导致索引失效，比如DATE(time) = t2.date，应直接用t1.date = t2.date
• 优先用预聚合方案：是唯一能将数十亿行明细查询复杂度降至O(N)的方案

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