如何以最快方式关联两张表获取最新记录

现有表结构 #

1. bom表 #

建表与插入数据SQL：

CREATE TABLE bom
(
    Article VARCHAR(250)
);

INSERT INTO bom (Article)
 VALUES
 ('AB'),
 ('CD'),
 ('EF'),
 ('GH');

2. purchases表 #

建表与插入数据SQL：

CREATE TABLE purchases
(
    Article VARCHAR(250),
    OrderDate DATE,
    Price DOUBLE
);

INSERT INTO purchases (Article, OrderDate, Price)
 VALUES
 ('AB', '2020-01-10', 12.0),
 ('AB', '2020-01-05', 10.0),
 ('AB', '2020-01-03', 8.0),
 ('EF', '2020-01-01', 7.0);

需求 #

给定指定日期（示例：@evalDay = '2020-01-04'），提取bom表中每个Article在该日期及之前的最新采购价格，期望结果如下：

当前实现及性能问题 #

已通过窗口函数row_number()实现需求，但性能未达预期：实际场景中bom表有数百行，purchases表约100万行，执行耗时约50ms（已添加索引及复合索引）。当前代码：

set @evalDay = '2020-01-04';
with cte (Article, OrderDate, Price, rn) as (
    select purchases.*,
        row_number() over (
            partition by bom.article
            order by purchases.OrderDate desc
        ) as rn
    from bom
        join purchases on bom.Article = purchases.Article
    where purchases.OrderDate <= @evalDay
)
select *
from cte
where rn = 1;

最快实现方案 #

方案1：关联子查询定位最大日期（推荐） #

利用复合索引直接定位每个Article的最新有效记录，避免窗口函数的排序开销：

set @evalDay = '2020-01-04';

select 
    b.Article,
    p.OrderDate,
    p.Price
from bom b
join purchases p 
    on p.Article = b.Article
    and p.OrderDate = (
        select max(OrderDate) 
        from purchases 
        where Article = b.Article 
          and OrderDate <= @evalDay
    )
where exists (
    select 1 
    from purchases 
    where Article = b.Article 
      and OrderDate <= @evalDay
);

优势：子查询通过索引快速获取每个Article的最大有效日期，匹配时直接定位对应记录，大幅减少数据扫描量，适合百万级purchases表场景。

方案2：LIMIT子查询（适配MySQL等支持LIMIT的数据库） #

对每个Article直接取符合条件的第一条最新记录，索引命中后效率极高：

set @evalDay = '2020-01-04';

select 
    b.Article,
    (select OrderDate from purchases where Article = b.Article and OrderDate <= @evalDay order by OrderDate desc limit 1) as OrderDate,
    (select Price from purchases where Article = b.Article and OrderDate <= @evalDay order by OrderDate desc limit 1) as Price
from bom b
where exists (
    select 1 from purchases where Article = b.Article and OrderDate <= @evalDay
);

注意：需确保purchases表有(Article, OrderDate DESC)索引，让子查询直接返回第一条匹配记录。

方案3：优化窗口函数写法 #

调整执行顺序，先过滤purchases表再计算窗口函数，减少数据处理量：

set @evalDay = '2020-01-04';

with cte as (
    select 
        Article,
        OrderDate,
        Price,
        row_number() over (partition by Article order by OrderDate desc) as rn
    from purchases
    where OrderDate <= @evalDay
)
select cte.*
from bom b
join cte on b.Article = cte.Article
where cte.rn = 1;

优化点：先对purchases表做日期过滤，再计算窗口函数，避免原写法中先关联再过滤带来的无效数据处理。

关键索引建议 #

无论采用哪种方案，必须给purchases表创建复合索引：(Article, OrderDate DESC, Price)。该索引可直接支持子查询的max(OrderDate)计算、窗口函数的分区排序，同时覆盖Price字段，避免回表查询，是性能提升的核心前提。

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