MySQL查询指定national_id对应最新renewal_date的优化咨询

嘿，我来帮你搞定这个慢查询的问题！你的场景是从15万条记录的subscriptions表里，找出给定national_id列表中每个用户的最新订阅日期，原SQL之所以卡死，主要是写法导致了大量重复的全表扫描，而且还有逻辑上的小问题，咱们一步步来解决。

先说说原SQL的问题 #

你原语句里的子查询条件写得不对：d1.national_id = d.national_id IN ('A1111R','A11C11', 'A145E1' ....) 这个逻辑会被MySQL误解，而且就算逻辑改对，这种“关联子查询”会对每一条符合条件的记录都执行一次MAX()计算，15万条数据的话相当于重复扫描几十甚至上百万次，性能自然差到爆。

最优方案1：GROUP BY + 关联查询（兼容所有MySQL版本） #

先通过子查询聚合出目标national_id对应的最大renewal_date，再和原表关联获取完整记录，这样只需要一次聚合扫描，效率高很多：

SELECT s.name, s.renewal_date, s.national_id
FROM subscriptions s
INNER JOIN (
    -- 先算出每个目标ID的最新订阅日期
    SELECT national_id, MAX(renewal_date) AS max_renewal_date
    FROM subscriptions
    WHERE national_id IN ('A1111R','A11C11', 'A145E1') -- 替换成你的ID列表
    GROUP BY national_id
) AS latest_sub ON s.national_id = latest_sub.national_id 
               AND s.renewal_date = latest_sub.max_renewal_date;

最优方案2：窗口函数（MySQL 8.0+推荐，写法更简洁） #

如果你的MySQL版本是8.0及以上，用ROW_NUMBER()窗口函数会更直观，逻辑清晰易懂：

SELECT name, renewal_date, national_id
FROM (
    SELECT 
        name, 
        renewal_date, 
        national_id,
        -- 按national_id分组，每个组内按订阅日期倒序排，第一条就是最新的
        ROW_NUMBER() OVER (PARTITION BY national_id ORDER BY renewal_date DESC) AS row_rank
    FROM subscriptions
    WHERE national_id IN ('A1111R','A11C11', 'A145E1')
) AS ranked_subs
WHERE row_rank = 1;

重中之重：添加索引！ #

上面两种方法性能好不好，核心取决于有没有合适的索引。针对你的查询场景，一定要创建联合索引：

CREATE INDEX idx_national_renewal ON subscriptions(national_id, renewal_date);

这个索引能帮MySQL快速过滤出IN列表里的用户，同时计算MAX(renewal_date)或者排序的时候，直接从索引里取数据，不用扫描全表，性能会提升几个量级。

额外提醒 #

如果同一个用户的renewal_date有完全相同的最大值，方案1会返回多条记录，方案2只会返回一条（如果需要保留所有相同最大值的记录，可以把ROW_NUMBER()换成RANK()）。根据你的业务需求选择就行。

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