group by排序，derived_merge最佳化的坑

marun發表於2020-09-11
排序

一個簡單的表


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 協議》，轉載必須註明作者和本文連結

相關文章