如何提升MySQL JOIN查询获取账号最新登录记录的运行速度

原查询存在的问题 #

首先原查询不仅运行效率低，还存在逻辑错误和细节问题：

• 表名拼写错误：子查询中使用的accounthistory和实际表名account_history不一致
• 逻辑错误：子查询GROUP BY AccountId时直接查询非分组、非聚合字段Os，在MySQL开启ONLY_FULL_GROUP_BY默认模式时会直接报错，即使关闭该模式，返回的Os是分组内随机数据，不是最新登录时间对应的操作系统信息，完全不符合需求
• 性能问题：子查询对account_history全表做分组聚合，数据量大时全表扫描+临时表分组的开销极高，导致查询速度慢

优化方案 #

1. 新增覆盖索引 #

给account_history表新增联合覆盖索引，无需回表即可直接获取所有需要的字段，同时可以快速定位每个账号的最新登录记录：

CREATE INDEX idx_acc_his_acc_logt_os ON account_history(AccountId, LoginTime DESC, Os);

2. 改写SQL语句 #

根据你使用的MySQL版本选择对应写法：

MySQL 8.0及以上版本（推荐，逻辑最严谨）

使用窗口函数ROW_NUMBER准确筛选每个账号的最新登录记录：

SELECT 
  a.AccountId,
  a.RegDate,
  b.LoginTime AS LatestLoginTime,
  b.Os
FROM account a
LEFT JOIN (
  SELECT 
    AccountId,
    LoginTime,
    Os,
    ROW_NUMBER() OVER(PARTITION BY AccountId ORDER BY LoginTime DESC) AS rn
  FROM account_history
) b ON a.AccountId = b.AccountId AND b.rn = 1;

添加索引后，子查询会直接走覆盖索引，无需扫描全表，性能提升非常明显。

所有MySQL版本兼容写法

因为account表数据量极小（仅3条），使用关联子查询性能最优：

SELECT 
  a.AccountId,
  a.RegDate,
  (SELECT LoginTime FROM account_history WHERE AccountId = a.AccountId ORDER BY LoginTime DESC LIMIT 1) AS LatestLoginTime,
  (SELECT Os FROM account_history WHERE AccountId = a.AccountId ORDER BY LoginTime DESC LIMIT 1) AS Os
FROM account a;

该写法会触发索引查找，每个账号仅需要1次索引查询即可拿到结果，几乎没有额外开销。

优化效果说明 #

• 完全解决原查询的逻辑错误，返回的Os确实是最新登录时间对应的设备信息
• 避免了account_history全表扫描，所有查询都走覆盖索引，无回表开销
• 百万级以上account_history表场景下，查询速度可以从秒级/分钟级提升到毫秒级

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