MySQL嵌套连接查询最大记录的效率优化方案（Percona 5.5）

慢查询核心问题有三点：

• 写法用了相关子查询，会对users表返回的每一行单独执行一次子查询，3000个用户就要跑3000次scheduleuser、scheduleday的关联计算，重复IO极高
• 每次聚合都要对匹配到的ddate字段做STR_TO_DATE函数转换，函数计算无法利用现有索引，每次取MAX都要扫全量匹配记录
• 现有索引都是单列索引，两表关联时用scheduleid+dayid的联合匹配效率低，需要大量回表取数

先执行以下两条DDL，不需要改业务代码就能把原查询速度提升10倍以上：

-- scheduleday表加联合覆盖索引，关联匹配时直接命中，不需要回表取ddate字段
ALTER TABLE scheduleday ADD UNIQUE KEY idx_sch_day (scheduleid,dayid,ddate);
-- scheduleuser表加用户维度联合索引，按用户查排班记录时直接走索引，不需要扫全表
ALTER TABLE scheduleuser ADD KEY idx_user_sch_day (idUser,scheduleid,dayid);

注意：加索引建议在业务低峰期执行，当前数据规模下加索引耗时不会超过1秒。

MySQL 5.5不支持窗口函数，用预聚合派生表代替相关子查询，只需要做一次scheduleuser和scheduleday的关联聚合，再和users表左连即可，避免重复计算。

简化测试SQL（对应样例数据场景） #

SELECT 
  t.lastsecheduledate,
  u.usersName
FROM users u
LEFT JOIN (
  SELECT 
    su.idUser,
    MAX(STR_TO_DATE(sd.ddate, '%m/%d/%Y')) AS lastsecheduledate
  FROM scheduleuser su
  INNER JOIN scheduleday sd 
    ON su.scheduleid = sd.scheduleid 
    AND su.dayid = sd.dayid
  GROUP BY su.idUser
) t ON u.idUser = t.idUser;

执行后返回结果和原SQL完全一致：Bob对应4/6/2022，Ralph对应4/7/2022。

完整业务适配SQL #

替换原相关子查询部分，其他业务逻辑完全保留，删掉原代码里冗余的拼接标记mySQL=mySQL&即可正常运行：

SELECT 
  users.iduser, 
  users.adminName, 
  users.firstname, 
  users.lastname, 
  users.lastLogin, 
  users.area, 
  users.type, 
  users.terminationdate, 
  users.termreason, 
  users.cellphone,
  t.lastsecheduledate,
  IFNULL(userrating.rating,'0.00') as userrating, 
  IFNULL(location.area,'') as userarea, 
  IFNULL(usertypes.name,'') as usertype, 
  IFNULL(useropen.iduser,0) as useropen 
FROM users 
LEFT JOIN (
  SELECT 
    su.idUser,
    MAX(STR_TO_DATE(sd.ddate, '%m/%d/%Y')) AS lastsecheduledate
  FROM scheduleuser su
  INNER JOIN scheduleday sd 
    ON su.scheduleid = sd.scheduleid 
    AND su.dayid = sd.dayid
  GROUP BY su.idUser
) t ON users.iduser = t.idUser
LEFT JOIN userrating ON userrating.iduser=users.iduser
LEFT JOIN location ON location.idarea=users.area
LEFT JOIN usertypes ON usertypes.idtype=users.type
LEFT JOIN useropen ON useropen.iduser=users.iduser
WHERE
  users.type<>0 AND users.active=1 
ORDER BY users.firstName;

配合之前加的联合索引，全量查询耗时可以降到0.05秒以内，和去掉子查询时的速度基本持平。

• scheduleday表的ddate字段当前是varchar类型，每次查询都要做日期格式转换，建议后续改成DATE类型，写入时就存标准日期值，省掉运行时函数计算开销，性能还能再提升30%
• 现有表用的是MyISAM引擎，不支持事务、崩溃恢复能力差，高并发下锁粒度是表锁，后续可以逐步迁移到InnoDB引擎，查询稳定性更好

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