MySQL优化：如何提升归档表指定日期状态查询效率

原查询通过左连接排除存在更晚生效日期的记录，以此获取每个id在指定日期前的最新状态，但数据量增大时，自连接会产生大量中间结果，导致性能下滑。以下是几种更高效的优化方案：

方案一：窗口函数（推荐） #

利用ROW_NUMBER()窗口函数按id分组，对valid_from_date降序排序，直接取每组第一条（即最新生效且不超过指定日期的记录）。这种方式无需自连接，能充分利用主键索引的有序性，计算效率更高。

SELECT id, valid_from_date, foo, bar
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY valid_from_date DESC) AS rn
    FROM data
    WHERE valid_from_date <= '2023-01-01'
) t
WHERE rn = 1;

方案二：聚合子查询关联 #

先通过子查询找出每个id在指定日期前的最大valid_from_date，再关联原表获取对应记录。这种方式仅需聚合每个id的最大日期，计算量远小于全表自连接。

SELECT d.*
FROM data d
INNER JOIN (
    SELECT id, MAX(valid_from_date) AS max_valid_date
    FROM data
    WHERE valid_from_date <= '2023-01-01'
    GROUP BY id
) t ON d.id = t.id AND d.valid_from_date = t.max_valid_date;

原表主键(id, valid_from_date)可让子查询快速完成分组聚合，后续关联也能通过主键索引直接定位记录。

方案三：单ID场景的极简查询（MySQL适用） #

如果仅查询单个id的最新状态，可直接通过ORDER BY+LIMIT获取结果，性能最优：

SELECT *
FROM data
WHERE id = ? AND valid_from_date <= '2023-01-01'
ORDER BY valid_from_date DESC
LIMIT 1;

索引优化补充 #

原表主键(id, valid_from_date)已具备良好的索引基础，需注意：

• 避免对valid_from_date进行函数运算，否则会导致索引失效
• 窗口函数和聚合子查询的WHERE条件均能利用主键索引快速筛选符合日期要求的记录

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