mysql derived_merge_group by排序，derived_merge优化的坑

一个简单的表

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从小到大排序：

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

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

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

SELECT

*

FROM

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

GROUP BY spu_id

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

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

EXPLAIN

SELECT

*

FROM

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

GROUP BY spu_id;

如图所示：

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

解决方法：

1. 将derived_merge 关闭

SET optimizer_switch='derived_merge=off';

SET GLOBAL optimizer_switch='derived_merge=off';

2. 使用特殊的查询阻止 derived_merge 优化

可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响不那么明显。防止合并的构造与派生表和视图引用相同：

DISTINCT

GROUP BY

HAVING

LIMIT

选择列表中的子查询

分配给用户变量

仅引用文字值(在这种情况下，没有基础表)

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

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

SELECT

*

FROM

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

GROUP BY spu_id;

使用 having 来阻止合并

那么再用explain看看

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

本作品采用《CC 协议》，转载必须注明作者和本文链接