直接用 GROUP BY 商户号加 SUM 会导致磁盘临时表、filesort 或 OOM,因单次聚合未分片预处理;应结合分区表、汇总表或物化视图优化。

为什么不能直接用 GROUP BY 商户号加 SUM?
上亿级流水表做 GROUP BY merchant_id 时,MySQL 或 PostgreSQL 往往会触发磁盘临时表、大量 Sort Merge 操作,甚至 OOM;Oracle 可能走全表扫描+HASH GROUP BY 占满 PGA。根本问题不是 SQL 写法错,而是单次聚合没做数据分片预处理,让数据库扛了全部压力。
- 单表超 2 亿行时,
SUM(amount)+GROUP BY merchant_id在普通 OLTP 实例上常卡住 10 分钟以上 - 若表无
merchant_id索引,执行计划大概率是全表扫描 + filesort - 即使有索引,若
merchant_id区分度低(比如 80% 记录集中在 100 个商户),索引选择性差,优化器仍可能弃用
建议先确认 EXPLAIN ANALYZE 是否出现 Using temporary; Using filesort 或 HashAggregate 占用内存 > 2GB。
用分区表 + 按天归档把“日终轧差”变成增量计算
日终轧差本质是“当天所有流水按商户汇总”,不是“历史全量重算”。只要流水表按 trade_date 做范围分区(如 MySQL 的 PARTITION BY RANGE (TO_DAYS(trade_date))),就能让查询天然落到单个分区,避免扫全表。
- 分区键必须是
trade_date或其导出列(如YEAR(trade_date)*100+MONTH(trade_date)),不能是merchant_id - 每日归档脚本需在凌晨 2 点后执行:
ALTER TABLE trade_log REORGANIZE PARTITION p_old INTO (PARTITION p_20240601 VALUES LESS THAN (TO_DAYS('2024-06-02'))) - 查询当日轧差时强制指定分区:
SELECT merchant_id, SUM(amount) FROM trade_log PARTITION (p_20240601) GROUP BY merchant_id,MySQL 8.0+ 支持,PostgreSQL 需用ONLY关键字或继承表
注意:PostgreSQL 分区表默认不剪枝,需确保 enable_partition_pruning = on,且 WHERE 条件中 trade_date = '2024-06-01' 不能写成 trade_date::date = '2024-06-01'(函数导致无法下推)。
用物化视图或汇总表替代实时 GROUP BY
真正扛住上亿量级的方案,是放弃“每次查都算”,改用“流水写入时异步更新汇总”。例如:
新建汇总表
daily_merchant_summary,主键为(trade_date, merchant_id)流水入库时(或通过 binlog/cdc 同步),用
INSERT ... ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount)(MySQL)或INSERT ... ON CONFLICT DO UPDATE(PG)实时累加日终任务只需查这张表,响应在毫秒级
若已有存量流水,可用
INSERT INTO daily_merchant_summary SELECT trade_date, merchant_id, SUM(amount) FROM trade_log WHERE trade_date = '2024-06-01' GROUP BY trade_date, merchant_id补全首日汇总表务必建联合索引:
CREATE INDEX idx_date_mid ON daily_merchant_summary (trade_date, merchant_id)别忘了加
trade_date分区,否则这张表自己也会膨胀
这个方案的代价是写放大和最终一致性——但对“日终轧差”场景完全可接受,毕竟业务只要求 T+1 准确。
当必须跑 ad-hoc GROUP BY 时,怎么压测和调优?
线上不敢直接跑?先用小样本验证执行效率:
- 抽样 1% 数据:
SELECT * FROM trade_log TABLESAMPLE SYSTEM (1) WHERE trade_date = '2024-06-01'(PostgreSQL),MySQL 可用LIMIT+ORDER BY RAND()(仅测试) - 强制走索引:
SELECT /+ USE_INDEX(trade_log idx_trade_date_mid) / merchant_id, SUM(amount) FROM trade_log WHERE trade_date = '2024-06-01' GROUP BY merchant_id(MySQL hint) - 调大临时内存:MySQL 调
sort_buffer_size到 8M~32M(勿超物理内存 10%),PG 调work_mem到 64MB
最容易被忽略的是字符集和 collation —— 如果 merchant_id 是 VARCHAR(32) 且用 utf8mb4_0900_as_cs,比较开销比 utf8mb4_bin 高 3 倍,GROUP BY 速度直降 40%。










