mysql5.7版本关于使用derived table 分组查询

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

表结构
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