mysql中 group by排序，derived

一个简单的表

1
2
3
4
5
6


CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `spu_id` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

大概内容

id	spu_id	price
1	100	200
2	100	100
3	200	400
4	200	200

对spu_id进行分组，按price从小到大排序：

1

SELECT * FROM `test`  GROUP BY spu_id ORDER BY price

直接使用group by 查出来的数据是按id顺序分组的，并未达到预期

尝试使用子查询，先排序再分组

1
2
3
4
5


SELECT
	* 
FROM
	( SELECT * FROM `test` ORDER BY price ) AS tmp 
GROUP BY spu_id

注意：这个方式在低版本中有效。在5.7版本中引入新特性 derived_merge优化过后无效了。

具体无效原因我们可使用explain 分析

1
2
3
4
5
6


EXPLAIN
SELECT
	* 
FROM
	( SELECT * FROM `test` ORDER BY price) AS tmp 
GROUP BY spu_id;

如图所示：

MySQL 将子查询优化成了一个简单查询，子查询中的排序无效~

解决方法：

将derived_merge 关闭

SET optimizer_switch='derived_merge=off';

SET GLOBAL optimizer_switch='derived_merge=off';
使用特殊的查询阻止 derived_merge 优化

可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响不那么明显。防止合并的构造与派生表和视图引用相同：
- 聚合函数（SUM()， MIN()， MAX()， COUNT()，等等）
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION 要么 UNION ALL
- 选择列表中的子查询
- 分配给用户变量
- 仅引用文字值（在这种情况下，没有基础表）

以上内容参考文档：mysql文档

那么我们可以将上面的那条sql语句修改为：

1
2
3
4
5


SELECT
	* 
FROM
	( SELECT * FROM `test` HAVING 1=1 ORDER BY price   ) AS tmp 
GROUP BY spu_id;

使用 having 来阻止合并

那么再用explain看看

如有错误请指正~ 请多包涵