分组查询取每组最新的数据

需求背景

MySQL数据库的表和数据为：

id	name	time
1	aaa	    2023-02-17 09:02:09
2	aaa	    2023-02-17 09:06:22
3	aaa	    2023-02-17 09:09:43
4	bbb	    2023-02-17 09:03:00
5	bbb	    2023-02-17 09:09:11
6	bbb	    2023-02-17 09:16:29
7	ccc	    2023-02-17 09:03:48
8	ccc	    2023-02-17 09:10:58
9	ccc	    2023-02-17 09:15:12

现在的需求是要根据name找出最新的一条数据。

SQL

思路：先进行排序，然后再进行分组，获取每组的第一条。通常我们想到的是SQL如下：

SELECT
	* 
FROM
	( SELECT * FROM groupby_test ORDER BY time DESC ) t1 
GROUP BY
	t1.NAME

执行上面的SQL我们发现，找到的数据并不是我们想要的结果，取的数据都是每个name的第一条数据，执行结果如下：

id	name	time
1	aaa	    2023-02-17 09:02:09
4	bbb	    2023-02-17 09:03:00
7	ccc	    2023-02-17 09:03:48

通过分析数据库中的原始数据，正确的数据应该是3、6、9才对，给我们的感觉就是SQL语句中的ORDER BY time DESC并没有起作用。那我们explain一下，看下数据库到底是怎么执行的。explain的结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	groupby_test		     ALL					                  9	     100.00	    Using temporary; Using filesort

通过分析可以看出，我们写的是两个select语句但是MySQL在执行的时候，一步就执行完了，并没有按照我们写的SQL执行，这说明我们这里写的SQL有问题。改成如下SQL：

SELECT
	* 
FROM
	( SELECT DISTINCT(id),NAME,time FROM groupby_test ORDER BY time DESC ) t1 
GROUP BY
	t1.NAME

执行上面的SQL，返回的结果如下：

id	NAME	time
3	aaa	    2023-02-17 09:09:43
6	bbb	    2023-02-17 09:16:29
9	ccc	    2023-02-17 09:15:12

这个SQL返回的结果才符合我们的预期，返回的结果是我们期望的数据，为什么加了一个 DISTINCT 产生的区别这么大呢？我们先explain看下MySQL的执行结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>		        ALL					                     9	    100.00	    Using temporary; Using filesort
2	DERIVED	groupby_test		    ALL					                     9	    100.00	    Using filesort

这里可以看出，MySQL在执行的时候分两步执行，第一步先执行内部的SQL，第二步基于派生表做统计。接下来我们分析一下产生的原因。主要是参考MySQL官方文档。

原因分析(主要是翻译一下MySQL官方文档)

使用合并或物化优化派生表和视图引用

优化器可以使用两种策略来处理派生表(同时也适用于视图)

将派生表合并到外部查询块中
将派生表物化到内部临时表中

例1：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

通过合并派生表derived_t1，这个查询类似于下面的查询方式：

SELECT * FROM t1;

例2：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

通过合并派生表derived_t2，这个查询类似于下面的查询方式：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

通过物化，derived_t1和derived_t2在各自的查询中都被视为单独的表。

优化器以相同的方式处理派生表和视图引用:它尽可能避免不必要的物化，从而支持将条件从外部查询下推到派生表，并产生更有效的执行计划。

如果合并将导致引用超过61个基表的外部查询块，优化器将选择物化。

如果这些条件都为真，优化器将派生表或视图引用中的ORDER BY子句传播到外部查询块:

外部查询没有分组或聚合。
外部查询不指定DISTINCT、HAVING或ORDER BY。
外部查询将此派生表或视图引用作为FROM子句中的唯一源。

否则，优化器将忽略ORDER BY子句。

以下方法可以影响优化器是否尝试将派生表和视图引用合并到外部查询块中:

可以使用optimizer_switch系统变量的derived_merge标志，假设没有其他规则阻止合并。参见第8.9.2节“可切换优化”。默认情况下，该标志是启用的，允许合并。禁用该标志可以防止合并并避免ER_UPDATE_TABLE_USED错误。

derived_merge标志也适用于不包含ALGORITHM子句的视图。因此，如果对于使用与子查询等价的表达式的视图引用发生ER_UPDATE_TABLE_USED错误，将ALGORITHM=TEMPTABLE添加到视图定义中可以防止合并，并优先于derived_merge值。

可以通过在子查询中使用任何防止合并的构造来禁用合并，尽管这些构造对物化的影响没有那么明显。防止合并的构造对于派生表和视图引用是相同的:
- Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION or UNION ALL
- Subqueries in the select list
- Assignments to user variables
- Refererences only to literal values (in this case, there is no underlying table)

derived_merge标志也适用于不包含ALGORITHM子句的视图。因此，如果对于使用与子查询等价的表达式的视图引用发生ER_UPDATE_TABLE_USED错误，将ALGORITHM=TEMPTABLE添加到视图定义中可以防止合并，并优先于当前的derived_merge值。

如果优化器选择物化策略而不是对派生表进行合并，它将按照以下方式处理查询:

优化器将派生表物化推迟到查询执行期间需要其内容时。这可以提高性能，因为延迟实体化可能会导致根本不需要这样做。考虑一个将派生表的结果连接到另一个表的查询:如果优化器首先处理另一个表，并且发现它没有返回任何行，则不需要进一步执行连接，优化器可以完全跳过派生表的实体化。
在查询执行期间，优化器可以向派生表添加索引，以加快从中检索行的速度。

考虑下面的EXPLAIN语句，对于一个包含派生表的SELECT查询:

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

优化器通过将派生表延迟到SELECT执行期间需要结果时才实现它，从而避免物化派生表。在这种情况下，查询不会执行(因为它发生在EXPLAIN语句中)，因此永远不需要该结果。

即使对于已执行的查询，派生表物化的延迟也可能使优化器完全避免物化。当发生这种情况时，查询执行速度比执行实体化所需的时间要快。考虑下面的查询，它将一个派生表的结果连接到另一个表:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

如果优化首先处理t1，而WHERE子句产生的结果为空，则连接必须为空，派生表不需要物化。

对于派生表需要物化的情况，优化器可以向物化表添加索引，以加快对它的访问。如果这样的索引允许对表的ref访问，它可以大大减少查询执行期间读取的数据量。考虑以下查询:

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

优化器从derived_t2构造列f1上的索引，如果这样做将允许对成本最低的执行计划使用ref访问。添加索引之后，优化器可以像对待带索引的普通表一样对待物化派生表，并且可以从生成的索引中获得类似的好处。与没有索引的查询执行成本相比，创建索引的开销可以忽略不计。如果ref访问将导致比其他访问方法更高的成本，优化器不会创建索引，也不会损失任何东西。

总结

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

外部查询禁止分组或者聚合
外部查询未指定having，HAVING， order by
外部查询将派生表或者视图作为from句中唯一指定源不满足这三个条件，order by会被忽略。

一旦外部表使用了group by，那么临时表（派生表 derived table）将不会执行filesort操作（即order by 会被忽略），所以在临时表中加了 DISTINCT。加了之后就相当于关闭了该特性，所以也就生效了。

参考文档

https://blog.csdn.net/u013066244/article/details/116461584

https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html

分组查询取每组最新的数据 #

需求背景 #

SQL #

原因分析(主要是翻译一下MySQL官方文档) #

使用合并或物化优化派生表和视图引用 #

总结 #

参考文档 #

分组查询取每组最新的数据

需求背景

SQL

原因分析(主要是翻译一下MySQL官方文档)

使用合并或物化优化派生表和视图引用

总结

参考文档