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 优化

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

• 聚合函数（SUM()， MIN()， MAX()， COUNT()，等等）

• DISTINCT

• GROUP BY

• HAVING

• LIMIT

• UNION 要么 UNION ALL

• 选择列表中的子查询

• 分配给用户变量

• 仅引用文字值（在这种情况下，没有基础表）

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

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

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

使用 having 来阻止合并

那么再用explain看看

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

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