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看看

image-20200911152244246

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

本作品采用《CC 协议》,转载必须注明作者和本文链接
marun
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!