如何优化5000万行大表的SQL分组统计查询性能？

你写的原始SQL存在无意义冗余，会额外增加执行开销，先修正基础语法问题：

• DISTINCT完全多余：已经做了GROUP BY region，分组后region天然去重，额外加DISTINCT会增加不必要的排序开销，直接删除即可
• FILTER条件重复无意义：insert_status = 'success' AND insert_status = 'success'、insert_status IS NULL AND insert_status IS NULL都是重复判断，直接删掉重复条件即可

修正后的SQL参考：

SELECT
    region,
    COUNT(*) as total,
    COUNT(*) FILTER(WHERE insert_status = 'success') as completed,
    COUNT(*) FILTER(WHERE insert_status IS NULL) as waiting,
    COUNT(*) FILTER(WHERE insert_status = 'failed') as insert_failed
FROM xml_files t1
WHERE 
    t1.section_name = 'payments'
    AND processed_date BETWEEN '2010-07-28' AND '2021-08-28'
GROUP BY region

1. 覆盖索引优化（优先级最高） #

5000万数据量下全表扫描必然缓慢，创建复合覆盖索引可以直接走索引扫描，不需要回表查询原数据，性能提升最明显。
索引字段按照「等值过滤>范围过滤>查询/分组字段」的顺序创建：

• 等值过滤字段放最前：section_name，对应WHERE里的固定等值判断
• 其次放范围过滤字段：processed_date，对应时间范围过滤条件
• 最后放查询、分组用到的所有字段：region、insert_status，确保所有需要的数据都在索引内，无需回表

建索引语句参考：

-- PostgreSQL/MySQL通用语法，不同数据库可根据特性调整参数
CREATE INDEX idx_xml_files_stats ON xml_files (section_name, processed_date, region, insert_status);

2. 分区表优化 #

如果该类统计是按时间范围高频查询的业务场景，建议给xml_files表按processed_date做范围分区（按年/按月分区均可），查询时会直接跳过不在时间范围内的分区，大幅减少需要扫描的数据量。

3. 统计信息更新 #

执行查询前先更新表的统计信息，让数据库优化器能选择最优执行计划：

• PostgreSQL：ANALYZE xml_files;
• MySQL：ANALYZE TABLE xml_files;

4. 预聚合优化（适用于高频查询场景） #

如果这个统计是需要频繁执行的业务查询，建议做预聚合处理：

• 新增预聚合结果表，按天/周维度提前计算好每个region对应的各状态计数，查询时直接聚合预计算好的结果，性能可提升几十到上百倍
• 也可以用物化视图实现预聚合，定时刷新即可，不需要自行维护数据同步逻辑

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