mysql中group by分组查询中查询最新字段值的sql语句怎么写？

背景

今天看代码的时候，看到一个比较复杂的sql语句，我知道这条sql语句最终想要实现的结果，所以我就想自己写sql来实现一下，看看速度会不会更好，然后发现别人的sql写法执行效率更高，由此打开了一个新的世界。

需求

在聊天消息记录表中查询该用户和其他消息发送者的“最近一条未读消息id、最近一条未读消息内容、最近一条未读消息接收时间、未读消息数量、消息发送者id、消息发送者名称”。其中表名是msg_record，相关字段如下：

列名	解释
id	消息记录id
toId	消息接收者id
fromId	消息发送者id
fromUsername	消息发送者名称
date	消息发送日期
hasRead	是否已读
msgText	消息内容

效率不高的sql写法

需求分析：

需要查询的结果中包含“最近一条未读消息id、最近一条未读消息内容、最近一条未读消息接收时间”，针对“最近一条未读消息接收时间”来说，可以使用聚合函数max()，但是剩余两个都是文本性质的内容，那就只能通过排序获取，针对Mysql数据库来说，如果我们获取非分组字段，那Mysql数据库会把分组中的第一条返回，所以利用这个特性，我们就先执行排序，然后在分组，之后利用Mysql的上述特性完成数据的获取工作

sql语句：

SELECT
	a.id,
	a.fromId,
	a.fromUsername,
	a.date,
	a.msgText lastMsg,
	count( a.id ) unReadCount
FROM
	(
-- 	子查询
	SELECT
		id,
		fromId,
		fromUserName,
		date,
		msgText 
	FROM
		msg_record
	WHERE
		fromId != "4ebd6f3485f140888ecc25c12e5105b1" 
		AND toId = "4ebd6f3485f140888ecc25c12e5105b1" 
		AND hasRead = FALSE 
	ORDER BY
		date DESC 
	) a 
GROUP BY
	a.fromId

执行分析：

在上述sql前面添加EXPLAIN字段即可，我们看下分析结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	空	空	空	空	7	Using temporary; Using filesort
2	DERIVED	imq_server_messagerecord	ALL	IDX_IMQ_SERVER_MESSAGERECORD_TOID,IDX_IMQ_SERVER_MESSAGERECORD_FROMID	IDX_IMQ_SERVER_MESSAGERECORD_TOID	403	空	117	Using where; Using filesort

上述第二行代表子查询使用的是全表扫描，但是使用到了索引；第一行代表全表扫描，但是没有使用索引，两个都是全表扫描，说明效率是真的不高

经测试发现，执行时间大致是0.024秒左右

效率高的sql写法

需求分析：

既然先排序的方式不行，那我们就采用先分组在排序的方式来得到最终结果，这就需要使用到SUBSTRING_INDEX、GROUP_CONCAT、CONCAT函数，我们来解释一下这几个函数的含义：

• SUBSTRING_INDEX：字符串切分函数
• GROUP_CONCAT：字符串分组连接函数
• CONCAT：字符串连接函数

详细分析可看：mysql取出每个分组中最新的记录(坑点勿踩)

sql语句：

SELECT
	SUBSTRING_INDEX( GROUP_CONCAT( CONCAT( id, '*splits*,' ) ORDER BY date DESC ), '*splits*,', 1 ) id,
	fromId,
	fromUsername,
	max( date ) date,
	SUBSTRING_INDEX( GROUP_CONCAT( CONCAT( msgText, '*splits*,' ) ORDER BY date DESC ), '*splits*,', 1 ) lastMsg,
	count( id ) unReadCount 
FROM
	msg_record
WHERE
	toId = "4ebd6f3485f140888ecc25c12e5105b1" 
	AND fromId != "4ebd6f3485f140888ecc25c12e5105b1" 
	AND hasRead = FALSE 
GROUP BY
	fromId

执行分析：

在上述sql前面添加EXPLAIN字段即可，我们看下分析结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	IMQ_SERVER_MESSAGERECORD	ref	IDX_IMQ_SERVER_MESSAGERECORD_TOID,IDX_IMQ_SERVER_MESSAGERECORD_FROMID	IDX_IMQ_SERVER_MESSAGERECORD_TOID	403	const	51	Using where; Using filesort

可以看到使用的是ref扫描，并且使用到了索引，并且是常量类型的索引，这肯定速度就比较快。

经测试发现，执行时间大致是0.015秒左右

我们对上面那几个函数的使用情况进行一个解释，我们先使用CONCAT()函数对属性id进行字符串拼接，然后使用GROUP_CONCAT()按照时间进行排序之后在进行字符串拼接，这样就是一个很大的字符串了，之后在使用SUBSTRING_INDEX()进行按照分隔符进行数据截取，就可以获取到结果了

但是这种处理方式也有一个弊端，如果数据量很大的话，通过这几个函数执行相关操作，那就可能会出现内存泄露的问题，但是大部分情况还好

拓展

• mysql取出每个分组中最新的记录(坑点勿踩)
• [Mysql] SUBSTRING函数 | SUBSTRING_INDEX函数