> For the complete documentation index, see [llms.txt](https://bohans.gitbook.io/ji-chu/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://bohans.gitbook.io/ji-chu/mysql/sql-you-hua/cha-xun-you-hua/chang-yong-sql-de-you-hua/you-hua-group-by-yu-ju.md).

# 优化 GROUP BY 语句

## ORDER BY NULL

<mark style="color:blue;">**默认情况下，MySQL 会根据分组字段进行排序。**</mark>对于含有 GROUP BY col1, col2, … 的 SQL 语句，相当于隐式地包含了一个 ORDER BY col1, col2, … 子句。

如果查询<mark style="color:blue;">**包括 GROUP BY**</mark> 但<mark style="color:blue;">**想要避免排序**</mark>结果的消耗，则可以指定 <mark style="color:blue;">**ORDER BY NULL**</mark> 来禁止排序。

<pre class="language-sql"><code class="lang-sql">EXPLAIN
SELECT payment_date, SUM(amount)
FROM payment
GROUP BY payment_date;

<strong>+-------------+-------------------------------+
</strong>|id           |1                              |
+-------------+-------------------------------+
|select_type  |SIMPLE                         |
+-------------+-------------------------------+
|table        |payment                        |
+-------------+-------------------------------+
|partitions   |NULL                           |
+-------------+-------------------------------+
|type         |ALL                            |
+-------------+-------------------------------+
|possible_keys|NULL                           |
+-------------+-------------------------------+
|key          |NULL                           |
+-------------+-------------------------------+
|key_len      |NULL                           |
+-------------+-------------------------------+
|ref          |NULL                           |
+-------------+-------------------------------+
|rows         |16086                          |
+-------------+-------------------------------+
|filtered     |100                            |
+-------------+-------------------------------+
|Extra        |Using temporary; Using filesort|
+-------------+-------------------------------+
</code></pre>

在 Extra 列中的 “Using filesort” 表明了上述 SQL 语句含有排序语义，若想要去除排序语义，在 SQL 语句后面添加一个 ORDER BY NULL 子句：

<pre class="language-sql"><code class="lang-sql">EXPLAIN
SELECT payment_date, SUM(amount)
FROM payment
GROUP BY payment_date
ORDER BY NULL; 

<strong>+-------------+---------------+
</strong>|id           |1              |
+-------------+---------------+
|select_type  |SIMPLE         |
+-------------+---------------+
|table        |payment        |
+-------------+---------------+
|partitions   |NULL           |
+-------------+---------------+
|type         |ALL            |
+-------------+---------------+
|possible_keys|NULL           |
+-------------+---------------+
|key          |NULL           |
+-------------+---------------+
|key_len      |NULL           |
+-------------+---------------+
|ref          |NULL           |
+-------------+---------------+
|rows         |16086          |
+-------------+---------------+
|filtered     |100            |
+-------------+---------------+
|Extra        |Using temporary|
+-------------+---------------+
</code></pre>

## WITH ROLLUP

分组查询的一个变种就是要求 MySQL 对返回的分组结果再做一次<mark style="color:blue;">**超级聚合**</mark>。可以使用 <mark style="color:blue;">**WITH ROLLUP**</mark> 子句来实现这种逻辑。

> ```sql
> SELECT 
>     YEAR(rental_date) y, 
>     MONTH(rental_date) m, 
>     COUNT(*) num
> FROM rental
> GROUP BY y, m
> WITH ROLLUP;
> ```
>
> ```
> +----+----+-----+
> |y   |m   |num  |
> +----+----+-----+
> |2005|5   |1156 |
> |2005|6   |2311 |
> |2005|7   |6709 |
> |2005|8   |5686 |
> |2005|NULL|15862|
> |2006|2   |182  |
> |2006|NULL|182  |
> |NULL|NULL|16044|
> +----+----+-----+
> ```
>
> 注：根据上面的实例，可以推断出 <mark style="color:blue;">**GROUP BY y, m WITH ROLLUP**</mark>，相当于 <mark style="color:blue;">**GROUP BY y**</mark>，<mark style="color:blue;">**GROUP BY y, m**</mark> 和 <mark style="color:blue;">**GROUP BY NULL（无 GROUP BY）**</mark>三者的 <mark style="color:blue;">**UNION（并集），**</mark>即等价于以下语句：
>
> ```sql
> (
>     SELECT YEAR(rental_date) y, MONTH(rental_date) m, COUNT(*) num
>     FROM rental
>     GROUP BY y, m
> ) UNION ALL (
>     SELECT YEAR(rental_date) y, NULL, COUNT(*) num
>     FROM rental
>     GROUP BY y
> ) UNION ALL (
>     SELECT NULL, NULL, COUNT(*) num
>     FROM rental
> );
> ```

{% hint style="info" %} <mark style="color:orange;">**应该尽可能地将 WITH ROLLUP 功能转移到应用程序中处理，在应用程序中做超级聚合是更好的，虽然这需要给客户端返回更多的结果。**</mark>
{% endhint %}