mysql derived_mysql5.7版本关于使用derived table 分组查询取每组最新的数据（order by 和group by使用问题）...

常见错误：分组查询取每组最新的数据

表结构

CREATE TABLE cy_biz_message

(

id INT AUTO_INCREMENT

COMMENT '主键'

PRIMARY KEY,

send_id INT NOT NULL

COMMENT '发送者',

receive_id INT NOT NULL

COMMENT '接收者',

content VARCHAR(500) NULL

COMMENT '信息内容',

`read` TINYINT(1) DEFAULT '0' NOT NULL

COMMENT '信息已读',

type INT DEFAULT '2' NOT NULL

COMMENT '类型(1：系统信息；2：普通信息)',

sort INT NULL

COMMENT '排序',

deleted CHAR DEFAULT '0' NOT NULL

COMMENT '是否删除 0否 1是',

enabled CHAR DEFAULT '1' NOT NULL

COMMENT '是否启用： 0否， 1是',

creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

COMMENT '创建时间',

created_by INT NULL

COMMENT '创建人',

last_updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

COMMENT '最后修改时间',

last_updated_by INT NULL

COMMENT '最后修改人',

login_count INT DEFAULT '0' NULL

)

COMMENT '消息记录'

ENGINE = InnoDB;

CREATE INDEX cy_biz_message_send_id_index

ON cy_biz_message (send_id);

CREATE INDEX cy_biz_message_receive_id_index

ON cy_biz_message (receive_id);

错误写法：

SELECT * FROM

(SELECT a.id, a.send_id, b.fullname AS sendName, a.receive_id, c.fullname AS receiveName, a.content, a.creation_date

FROM cy_biz_message a

LEFT JOIN sys_user b ON a.send_id = b.id

LEFT JOIN sys_user c ON a.receive_id = c.id

WHERE a.receive_id = 1025

ORDER BY a.creation_date DESC) AS t

GROUP BY t.send_id ORDER BY t.creation_date DESC;

正确写法:

SELECT * FROM

(SELECT a.id, a.send_id, b.fullname AS sendName, a.receive_id, c.fullname AS receiveName, a.content, a.creation_date

FROM cy_biz_message a

LEFT JOIN sys_user b ON a.send_id = b.id

LEFT JOIN sys_user c ON a.receive_id = c.id

WHERE a.receive_id = 1025

ORDER BY a.creation_date DESC LIMIT 10000) AS t

GROUP BY t.send_id ORDER BY t.creation_date DESC;

原理分析：

我们这里使用了临时表排序，继而对其结果进行分组，结果显示失败，加了limit后结果正确，原因是因为临时表(派生表derived table)中使用order by且使其生效，必须满足三个条件：

1：外部查询禁止分组或者聚合

2：外部查询未指定having，HAVING， order by

3：外部查询将派生表或者视图作为from句中唯一指定源

不满足这三个条件，order by会被忽略。

根据下面几张图可以看出，一旦外部表使用了group by，那么临时表(派生表 derived table)将不会执行filesort操作(即order by 会被忽略)。之后我使用了limit可以使其生效，原因是因为要使派生表order by生效，派生表可以通过使用group by、limit、having、distinct等等使其生效(方法有好多，详情可看文档https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html)

(如有发现文章错误，请在评论处指出，谢谢。)

EXPLAIN SELECT * FROM

(SELECT a.id, a.send_id, b.fullname AS sendName, a.receive_id, c.fullname AS receiveName, a.content, a.creation_date

FROM cy_biz_message a

LEFT JOIN sys_user b ON a.send_id = b.id

LEFT JOIN sys_user c ON a.receive_id = c.id

WHERE a.receive_id = 1025

ORDER BY a.creation_date DESC) AS t

GROUP BY t.send_id ORDER BY t.creation_date DESC;

(对应下图)

image.png

EXPLAIN SELECT * FROM

(SELECT a.id, a.send_id, b.fullname AS sendName, a.receive_id, c.fullname AS receiveName, a.content, a.creation_date

FROM cy_biz_message a

LEFT JOIN sys_user b ON a.send_id = b.id

LEFT JOIN sys_user c ON a.receive_id = c.id

WHERE a.receive_id = 1025

ORDER BY a.creation_date DESC) AS t

GROUP BY t.send_id ORDER BY NULL;

(对应下图)

image.png

EXPLAIN SELECT * FROM

(SELECT a.id, a.send_id, b.fullname AS sendName, a.receive_id, c.fullname AS receiveName, a.content, a.creation_date

FROM cy_biz_message a

LEFT JOIN sys_user b ON a.send_id = b.id

LEFT JOIN sys_user c ON a.receive_id = c.id

WHERE a.receive_id = 1025

ORDER BY a.creation_date DESC LIMIT 10000) AS t

GROUP BY t.send_id ORDER BY t.creation_date DESC;

(对应下图)

image.png