Leetcode 512题SQL查询超时问题排查：首次登录设备查询优化

表信息 #

(player_id, event_date) 是该表的主键。此表记录游戏玩家的活动情况，每一行代表玩家某天使用某设备登录、游玩若干游戏（可能为0）后登出的记录。

查询需求 #

编写SQL查询，返回每个玩家首次登录使用的设备。

原解决方案 #

select a1.player_id, a1.device_id
from Activity a1
where event_date = (
    select min(event_date) from Activity a2
    where a1.player_id = a2.player_id
    group by a2.player_id
)

问题描述 #

该方案能通过初始测试，但提交时出现"超时（Time Limit Exceeded）"错误。查询中是否存在低效逻辑？问题出在哪里？

执行计划（翻译后） #

低效原因分析 #

1. 嵌套全表扫描：主查询对a1做全表扫描，且每遍历一行a1的数据，就会触发一次对a2的全表扫描。数据量较大时，查询次数呈行数乘积级增长，时间复杂度极高。
2. 冗余分组操作：子查询中的group by a2.player_id完全多余——where条件已经限定了a1.player_id = a2.player_id，分组不会改变结果，反而会触发临时表创建（Using temporary），额外增加性能开销。

优化方案 #

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

通过窗口函数一次性计算每个玩家的首次登录标记，仅需扫描表一次：

SELECT player_id, device_id
FROM (
    SELECT 
        player_id, 
        device_id,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn
    FROM Activity
) t
WHERE rn = 1;

ROW_NUMBER()按玩家分组、登录日期排序，标记首次登录的行（rn=1），主查询直接筛选即可，效率远高于嵌套子查询。

方案2：先聚合再关联 #

先通过一次聚合得到每个玩家的首次登录日期，再与原表关联获取设备ID：

SELECT a.player_id, a.device_id
FROM Activity a
JOIN (
    SELECT player_id, MIN(event_date) AS first_login
    FROM Activity
    GROUP BY player_id
) t ON a.player_id = t.player_id AND a.event_date = t.first_login;

聚合操作仅扫描表一次，关联时依赖主键索引(player_id, event_date)可快速匹配数据，避免重复扫描。

方案3：利用主键特性（数据库特定） #

因(player_id, event_date)是主键，每个玩家的记录按日期天然有序，部分数据库（如MySQL）支持直接分组取首行：

SELECT player_id, device_id
FROM Activity
ORDER BY player_id, event_date
GROUP BY player_id;

注意：该写法依赖数据库特定行为，仅在MySQL特定模式下生效，不推荐跨数据库使用，但执行效率极高。

索引优化建议 #

确保表上已存在主键索引(player_id, event_date)（题目已说明这是主键，默认应存在），若缺失需手动创建：

ALTER TABLE Activity ADD PRIMARY KEY (player_id, event_date);

有序的主键索引可大幅加速分组、排序和关联操作，避免额外排序或临时表开销。

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