单张百万级大表如何合并关联查询、复用子查询优化性能？

前置修正 #

首先你给出的第4条查询存在笔误：按mythic分组却写了group by keystone，优化前需要先修正为group by mythic。

1. 合并查询减少往返与重复扫描 #

你可以通过一次数据库查询拿到所有需要的统计结果，仅扫描2次表（全表计数1次，目标champion数据1次），完全避免重复扫描和多次网络往返：

WITH global_total AS (
    -- 计算全表总条数
    SELECT count(*) AS total_participant_count
    FROM "Participant"
),
champion_stats AS (
    -- 仅扫描一次目标champion的数据集，生成所有维度统计
    SELECT
        CASE
            WHEN grouping(keystone) = 1 AND grouping(mythic) = 1 THEN 'champion_total'
            WHEN grouping(mythic) = 1 THEN 'keystone_group'
            WHEN grouping(keystone) = 1 THEN 'mythic_group'
        END AS stat_type,
        CASE
            WHEN grouping(keystone) = 1 AND grouping(mythic) = 1 THEN NULL
            WHEN grouping(mythic) = 1 THEN keystone::text
            WHEN grouping(keystone) = 1 THEN mythic::text
        END AS dimension_key,
        count(*) AS stat_value
    FROM "Participant"
    WHERE "championId" = n -- 替换为实际传入的championId参数
    GROUP BY GROUPING SETS (
        (), -- 统计目标champion总条数
        (keystone), -- 按keystone分组统计
        (mythic) -- 按mythic分组统计
    )
)
-- 合并所有结果返回
SELECT g.total_participant_count, c.stat_type, c.dimension_key, c.stat_value
FROM global_total g
CROSS JOIN champion_stats c;

返回结果的解析规则：

• total_participant_count 字段为全表总条数，所有行该值相同
• stat_type = 'champion_total' 行的stat_value为目标champion的总条数
• stat_type = 'keystone_group' 行的dimension_key为keystone值，stat_value为对应分组条数
• stat_type = 'mythic_group' 行的dimension_key为mythic值，stat_value为对应分组条数

2. 索引优化进一步降低开销 #

针对你的查询场景创建覆盖索引，不需要回表查询原数据，查询速度可以提升10倍以上：

CREATE INDEX idx_participant_champion_cover ON "Participant" ("championId") INCLUDE ("keystone", "mythic");

如果全表计数不需要完全精确，PostgreSQL等数据库还可以用系统表近似查询替代全表扫描，速度可以达到微秒级：

SELECT reltuples::bigint AS total_participant_count
FROM pg_class
WHERE relname = 'Participant';

3. 后续新增统计需求的扩展方案 #

如果后续还有大量同类统计需求，可以按场景选择进阶优化：

• 允许非实时统计的场景：创建预聚合物化视图，按championId预计算所有维度的统计值，查询时直接读取物化视图即可，不需要每次扫描原表
• 需要实时统计的场景：将统计逻辑封装为SQL存储函数，传入championId参数即可直接返回所有结果，避免重复编写SQL
• 统计维度非常多的场景：可以将championId=n的数据集导出到应用内存中做自定义统计，适合单次需要10个以上维度统计的场景

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