如何优化含数十亿行的零售数据仓库Sales事实表查询性能？

问题背景 #

某零售企业数据仓库中，Sales事实表存储数十亿行交易级数据，包含transaction_id、product_id、customer_id、store_id、date、quantity_sold、total_amount字段，关联Products、Customers、Stores、Dates维度表。执行以下查询计算指定日期范围产品总销售额时，耗时超10分钟：

SELECT 
    p.product_name, 
    SUM(s.total_amount) AS total_sales
FROM 
    Sales s
JOIN 
    Products p ON s.product_id = p.product_id
WHERE 
    s.date BETWEEN '2014-01-01' AND '2024-12-31'
GROUP BY 
    p.product_name
ORDER BY 
    total_sales DESC;

已实施的优化措施：

• 按date列对Sales表进行分区
• 为product_id、customer_id、date等常用查询列添加索引
• 将历史数据聚合至汇总表以实现快速报表

进一步优化建议 #

1. 构建联合覆盖索引 #

单字段索引无法覆盖查询全流程需求，建议创建包含过滤、关联、聚合字段的联合覆盖索引，避免回表读取原始数据：

CREATE INDEX idx_sales_date_product_total ON Sales(date, product_id, total_amount);

该索引可直接支持date范围过滤、product_id关联、total_amount聚合操作，大幅降低IO开销。

2. 预聚合并冗余维度字段 #

Products维度表中product_id与product_name为一一对应关系，可预先将product_name冗余到Sales事实表，或创建包含维度字段的预聚合表，消除查询时的JOIN操作：

-- 按月份预聚合的示例表
CREATE TABLE Sales_Product_Month_Agg AS
SELECT 
    date_trunc('month', date) AS month_date,
    p.product_name,
    SUM(total_amount) AS total_sales
FROM Sales s
JOIN Products p ON s.product_id = p.product_id
GROUP BY month_date, p.product_name;

后续查询直接基于预聚合表筛选日期范围并求和，无需再扫描全量交易数据。

3. 细化分区粒度 #

若当前为按年分区，可调整为按月/按季度分区，针对10年的查询范围，更细的分区粒度能让数据库精准裁剪无关分区，减少扫描的数据量。同时确保date条件能触发分区裁剪逻辑，避免扫描所有分区。

4. 调整聚合顺序 #

先按product_id在Sales表完成聚合，再关联Products表获取名称，减少聚合和JOIN的数据量：

SELECT 
    p.product_name, 
    agg.total_sales
FROM (
    SELECT 
        product_id,
        SUM(total_amount) AS total_sales
    FROM Sales
    WHERE date BETWEEN '2014-01-01' AND '2024-12-31'
    GROUP BY product_id
) agg
JOIN Products p ON agg.product_id = p.product_id
ORDER BY total_sales DESC;

product_id的基数远低于可能存在重复的product_name，先聚合再关联能大幅降低计算压力。

5. 切换为列存储格式 #

若使用支持列存储的数据库（如Snowflake、BigQuery、Vertica等），将Sales表转换为列存储格式。列存储针对聚合、过滤类查询的性能远高于行存储，能显著提升数十亿行数据的扫描和聚合效率。

6. 分析并优化执行计划 #

查看数据库执行计划，确认以下关键点：

• 是否触发了分区裁剪
• 是否使用了预期的索引
• JOIN操作是否采用了最优连接方式（如哈希连接而非嵌套循环）
  根据执行计划调整索引、分区规则或查询语句，比如在执行计划异常时强制指定索引（需谨慎操作）。

内容的提问来源于stack exchange，提问作者Michael M.M