数十万数据下，如何高效多表关联查询商品最新有效价格？

问题概述 #

现有两张核心表：

• itens：存储商品基础信息
• tpreco：存储商品价格历史，每次更新价格新增一条带生效日期dtvalida的记录，需取dtvalida <= 当前时间的最大日期对应的价格作为当前有效价格

在25万商品+大量历史价格的场景下，已尝试的LATERAL JOIN、子查询匹配最大日期、自定义函数三种方案均耗时1-2分钟，无法满足API响应效率要求，需优化。

核心优化方案 #

1. 创建针对性复合覆盖索引 #

查询慢的核心原因通常是缺少合适的索引，针对tpreco的查询模式，创建复合覆盖索引，让数据库无需回表即可获取所需数据：

CREATE INDEX idx_tpreco_latest_price ON tpreco (empresa, tabela, codigo, unidade, dtvalida DESC) INCLUDE (valor);

• 索引前缀empresa, tabela, codigo, unidade是查询的过滤条件，确保快速定位分组
• dtvalida DESC直接按生效日期倒序排序，避免额外排序开销
• INCLUDE (valor)将价格字段包含在索引中，无需回表查询原表

同时检查关联表的索引：

• itens表确保item字段为主键或有唯一索引
• empitens表创建(item, empresa)复合索引（如果用到该表关联）
• paemp表确保empresa字段有索引

2. 使用窗口函数优化查询写法 #

窗口函数ROW_NUMBER()在分组取最新记录场景下，性能远优于LATERAL JOIN或嵌套子查询，只需扫描tpreco一次即可完成分组排序：

WITH ranked_prices AS (
    SELECT 
        t.codigo,
        t.unidade,
        t.empresa,
        t.valor,
        t.dtvalida,
        -- 按商品、单位、企业、价格表分组，取最新生效价格
        ROW_NUMBER() OVER (
            PARTITION BY t.codigo, t.unidade, t.empresa, t.tabela 
            ORDER BY t.dtvalida DESC
        ) AS rn
    FROM tpreco t
    JOIN paemp pa ON t.tabela::text = pa.fattabavis::text
    WHERE t.dtvalida <= NOW()
      AND t.empresa = 1265 -- 固定企业值提前过滤，减少数据量
)
SELECT 
    i.item,
    i.unidade,
    rp.empresa,
    rp.valor,
    rp.dtvalida
FROM itens i
JOIN ranked_prices rp 
    ON i.item = rp.codigo 
    AND i.unidade::text = rp.unidade::text
WHERE rp.rn = 1; -- 只保留每组最新的价格记录

如果需要关联empitens过滤商品，调整CTE部分：

WITH ranked_prices AS (
    SELECT 
        t.codigo,
        t.unidade,
        t.empresa,
        t.valor,
        t.dtvalida,
        ROW_NUMBER() OVER (
            PARTITION BY t.codigo, t.unidade, t.empresa, t.tabela 
            ORDER BY t.dtvalida DESC
        ) AS rn
    FROM tpreco t
    JOIN empitens ei ON t.codigo = ei.item
    JOIN paemp pa ON t.empresa = pa.empresa AND t.tabela::text = pa.fattabavis::text
    WHERE t.dtvalida <= NOW()
      AND ei.precocodificado <> 'S'
)
SELECT 
    i.item,
    i.descricao,
    i.unidade,
    rp.empresa,
    rp.valor,
    rp.dtvalida
FROM itens i
JOIN ranked_prices rp 
    ON i.item = rp.codigo 
    AND i.unidade = rp.unidade
WHERE rp.rn = 1;

3. 物化视图预计算（非实时场景） #

如果API对价格实时性要求不高（允许5-10分钟延迟），可以创建物化视图预计算最新价格，查询时直接读取物化视图：

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_latest_item_prices AS
WITH ranked_prices AS (
    SELECT 
        t.codigo,
        t.unidade,
        t.empresa,
        t.valor,
        t.dtvalida,
        ROW_NUMBER() OVER (
            PARTITION BY t.codigo, t.unidade, t.empresa, t.tabela 
            ORDER BY t.dtvalida DESC
        ) AS rn
    FROM tpreco t
    JOIN paemp pa ON t.tabela::text = pa.fattabavis::text
    WHERE t.dtvalida <= NOW()
      AND t.empresa = 1265
)
SELECT 
    i.item,
    i.unidade,
    i.descricao,
    rp.empresa,
    rp.valor,
    rp.dtvalida
FROM itens i
JOIN ranked_prices rp 
    ON i.item = rp.codigo 
    AND i.unidade::text = rp.unidade::text
WHERE rp.rn = 1;

-- 给物化视图创建索引，加速查询
CREATE UNIQUE INDEX idx_mv_item_unidade ON mv_latest_item_prices (item, unidade);

定期刷新物化视图（可通过定时任务执行）：

-- 普通刷新（会锁表）
REFRESH MATERIALIZED VIEW mv_latest_item_prices;

-- 并发刷新（不锁表，需提前创建唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_latest_item_prices;

4. 修正并优化自定义函数（若必须使用函数） #

原函数存在变量名错误（into max_valor对应last_valor），且PL/pgSQL函数执行开销高于SQL函数，优化为SQL函数并添加STABLE标记（允许数据库缓存结果）：

CREATE OR REPLACE FUNCTION ult_preco_f(_emp bigint, _tab varchar, _item bigint, _un varchar)
RETURNS float
LANGUAGE sql STABLE
AS $$
    SELECT valor
    FROM tpreco
    WHERE empresa = _emp
      AND tabela = _tab
      AND codigo = _item
      AND unidade = _un
      AND dtvalida <= NOW()
    ORDER BY dtvalida DESC
    LIMIT 1;
$$;

验证方法 #

使用EXPLAIN ANALYZE执行优化后的查询，检查执行计划：

• 确认是否走了创建的复合索引
• 避免全表扫描、排序等耗时操作
• 对比优化前后的查询耗时，调整索引或查询逻辑

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