如何优化大表上缓慢的聚合查询？

Question

如何优化大表上缓慢的聚合查询？

IT Interview Practice · Accepted Answer

优化慢聚合查询前，先确认**结果代表什么，以及哪一步消耗时间**。聚合将多行合成摘要，例如每个商户的收入。成本取决于需要读取和合并多少数据。

以展示上个月收入的仪表盘为例，判断应该优化查询、保存预计算摘要，还是将报表迁移到其他系统。

## 1. 先保证结果正确，再提升速度

明确收入按已支付订单还是已创建订单计算，退款如何处理，包括哪些币种。也要确认仪表盘需要多及时。这些决定会改变查询及可用的优化方式。

常见错误是把一笔付款关联到多条订单明细后，再对付款金额求和：

```text
[Payment: 100] -> [Join with 3 items] -> [100, 100, 100]
                                               |
                                          [SUM = 300]
```

数字仅作示例。查询重复计算了付款，提速只会更快地给出错误结果。先在正确的业务粒度上聚合，或修正关联，再评估性能。

## 2. 找出执行过程中的高成本部分

执行计划显示得到结果所使用的操作。检查耗时步骤及其输入规模，不要直接认定表缺少一个索引。

| 现象 | 应调查的问题 |
| --- | --- |
| 读取大量无关行 | 过滤条件或分区裁剪能否排除它们？ |
| 实际行数与估算差距很大 | 统计信息是否代表当前数据？ |
| 关联反复执行大量工作 | 输入是否过大，或访问路径不合适？ |
| 分组、排序将临时数据写入磁盘 | 增加内存限制前，能否先减少输入？ |

PostgreSQL 的 EXPLAIN (ANALYZE, BUFFERS) 会实际执行查询并报告运行信息。昂贵的调查应在受控环境进行，或为生产执行明确设置资源预算。[PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)。

## 3. 先减少当前数据库中的工作

只选取仪表盘需要的月份和列。检查计划是否真正跳过无关分区，以及关联是否带入摘要不需要的数据。

索引只有在减少足够读取时才有价值。如果报表需要大多数行，顺序扫描可能合理。判断依据应是实际计划和测得的成本，而不是有没有使用索引。

排序溢出到磁盘时，减少输入可能就能解决。为每条查询增加内存，反而可能在多个报表并发时耗尽 RAM。要用有代表性的并发负载测试。

## 4. 判断是否提前准备结果

如果很多用户请求相同总数，而且允许一定延迟，反复扫描原始订单就是重复劳动。可以按日期、商户和币种保存汇总表，让仪表盘合并这些较小的结果。

```text
[Orders] -> [Scheduled / incremental calculation]
                              |
                       [Daily summaries] -> [Dashboard]
```

收益来自共享计算只做一次，而非每次请求重做。物化视图也是保存查询结果的方法，但有自己的刷新机制。

节省计算的同时，需要承担以下责任：

- 定义刷新频率，并向用户展示相关的数据更新时间。
- 处理退款、迟到订单和修正，避免重复计算。
- 保证重复处理安全，并保留重建摘要的方法。
- 合并平均值时保存总和与数量，直接对平均值再求平均可能错误。

对于这个仪表盘，只有摘要的时效性和修正规则满足第一步要求，才适合采用预计算。

## 5. 测量结果有依据时，再分离报表系统

如果大范围分析扫描仍然昂贵并影响购买，考虑适合该负载的分析存储。把同步延迟、恢复和额外系统的运维成本计算在内。

只读副本可以隔离报表负载，但同一查询在副本上仍可能昂贵。分离只是改变成本发生的位置，不一定消除成本。

每次修改都用可信结果和相同测试负载对比，检查响应时间、资源消耗、数据时效及写入影响。

**记住：**正确结果 → 实测瓶颈 → 减少无用工作 → 合适时预计算 → 仍有必要再分离分析系统。

现象	应调查的问题
读取大量无关行	过滤条件或分区裁剪能否排除它们？
实际行数与估算差距很大	统计信息是否代表当前数据？
关联反复执行大量工作	输入是否过大，或访问路径不合适？
分组、排序将临时数据写入磁盘	增加内存限制前，能否先减少输入？

1. 先保证结果正确，再提升速度

2. 找出执行过程中的高成本部分

3. 先减少当前数据库中的工作

4. 判断是否提前准备结果

5. 测量结果有依据时，再分离报表系统

相关问题