mysql5.7先分组再取最新一条记录时order by失效

1. 业务场景

项目中有个小型的私信功能，需要查询出聊天列表且展示与所有聊天对象的最后一条聊天记录； 因此需要按照聊天对象分组，找出与每个聊天的最后一条消息，group by和order by就得同时使用；

2. SQL

先排序再分组取第一条 SELECT p.id, COUNT( p.friend ) AS msgCnt, p.user, p.friend, p.sender, p.receiver, p.send_time AS sendTime, p.content, u.nick_name AS receiverName, u.avatar_url AS receiverImage FROM ( SELECT * FROM private_message WHERE user= '1291250952749318144' AND STATUS != 2 ORDER BY send_time DESC) p INNER JOIN USER u ON u.id = p.friend GROUP BY p.friend ORDER BY p.send_time DESC;

查询结果看起来似乎和预想一样，但发现排序desc并没有生效，msgCnt=4的那条记录中content并不是时间最新的记录

问题原因

原因：在mysql5.7中，如果不加limit，系统会把order by优化掉。 在mysql5.7手册的8.2.2.1中有解释： 子查询的优化是使用半连接的策略完成的(The optimizer uses semi-join strategies to improve subquery execution) 使用半连接进行优化，子查询语句必须满足一些标准(In MySQL, a subquery must satisfy these criteria to be handled as a semi-join)。 其中一个标准是:必须不是一个包含了limit和order by的语句(It must not have ORDER BY with LIMIT.)

于是我加上了limit限制

SELECT p.id, COUNT( p.friend ) AS msgCnt, p.user, p.friend, p.sender, p.receiver, p.send_time AS sendTime, p.content, u.nick_name AS receiverName, u.avatar_url AS receiverImage FROM ( SELECT * FROM private_message WHERE user= '1291250952749318144' AND STATUS <> 2 ORDER BY send_time DESC LIMIT 0, 100 ) p INNER JOIN USER u ON u.id = p.friend GROUP BY p.friend ORDER BY p.send_time DESC;

加上了limit防止mysq将其优化，确实可以实现查询最新一条的效果；

但是也带来一个问题，这个limit如果太小就会出问题；

explain查询加limit与否的前后区别 未加limit 加limit后 2. 子查询部分使用group by分组找到每组最新那条数据，外层查询使用多字段的in查询。

SELECT t.* FROM private_message t WHERE t.user= '1291250952749318144' AND t.status <> 2 and (t.friend,t.send_time) in ( select p.friend , max(p.send_time) from ( SELECT * FROM private_message WHERE USER = '1291250952749318144' AND STATUS != 2) p group by p.friend )

但是感觉这种方式可能效率很不好，只有后续再根据实际再进行调整吧