MySQL 在處理 GROUP BY 和 DISTINCT 查詢的方式在大多數(shù)情況下類似，事實上，在優(yōu)化過程中有時候會把在這兩種方式中轉換。兩類查詢都能夠從索引中受益，通常，這也是優(yōu)化這兩種查詢最為重要的方式。

在無法使用索引時，MySQL 對于 GROUP BY 查詢有兩種策略：使用臨時表或者 filesort 執(zhí)行分組。對于給定的查詢，兩種方式都沒法更高效。我們可以通過配置 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 來指定優(yōu)化器選擇其中一個方式。

通常，對查詢表的id 進行分組比使用值進行分組效率更高，例如下面的查詢效率就比較低：

SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakila.file_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY actor.first_name, actor.last_name;

而下面的查詢方式則更有效：

SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakila.file_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;

而使用 actor.actor_id 進行分組會比 film_actor.actor_id更高效。

這個查詢能夠發(fā)揮其優(yōu)勢的依據(jù)是演員(actor)的姓名是依賴于 actor_id 的，因此會返回相同的結果，但是如果返回的結果不同的話就不能這么做了。甚至有些時候服務端通過 SQL_MODE 配置禁用了 GROUP BY。此時如果不關心獲取的值，而且用于分組的列的值是唯一的，這可以使用 MIN和 MAX 來解決這個問題。

SELECT MIN(actor.first_name), MAX(actor.last_name), ...;

對于完美主義者，他們會認為你的分組是錯誤的，他們也是對的。一個虛擬的 MIN 或 MAX 的結果是查詢并不會正確地組裝。然而，有時候你只是為了讓 MySQL 更快地執(zhí)行查詢。完美主義者對于下面的查詢會滿意：

SELECT actor.fisrt_name, actor.last_name, c.cnt
FROM sakila.actor
	INNER JOIN (
    SELECT actor_id, COUNT(*) AS cnt
    FROM sakila.film_actor
    GROUP BY actor_id
  ) AS c USING(actor_id);

然而，子查詢中創(chuàng)建和填充臨時表的代價可能比理論上看起來的死辦法更高。需要記住的是，子查詢構建的臨時表是沒有索引的，這會導致性能上的下降。

通常在分組查詢中，選擇沒有分組的列是一個糟糕的主意。這是因為查詢結果是不確定的，一旦改變了索引或優(yōu)化器使用了不同的策略都會導致結果被改變。事實上，我們建議將服務端的 SQL_MODE 設置為 ONLY_FULL_GROUP_BY，這時寫了一個糟糕的分組查詢時，系統(tǒng)會產(chǎn)生一個錯誤而不是直接執(zhí)行。開啟 ONLY_FULL_GROUP_BY 后，SELECT 的字段只能是 GROUP BY 指定的字段，此時可以通過構建分步查詢或子查詢的方式，先分組查出分組的列，再做二次查詢。

MySQL 會根據(jù) GROUP BY 指定的列次序自動分組，除非是使用了 ORDER BY 指定排序規(guī)則。如果不在乎次序并且發(fā)現(xiàn)了這導致了一個 filesort，這時候可以使用 ORDER BY NULL 來跳過自動排序。也可以通過在 GROUP BY 后面增加 DESC 或 ASC 來指定結果按指定的方向排序。

有時候可以在分組查詢時要求 MySQL 在結果中做一次超級聚合。這可以通過在 GROUP BY 后面增加WITH ROLLUP 子句完成，但是這不一定能夠達到優(yōu)化的預期?？梢酝ㄟ^ EXPLAIN 檢查執(zhí)行的方法，注意分組有沒有通過 filesort 或臨時表完成。然后在對相同的查詢移除 WITH ROLLUP 后進行對比。通過對比也許可以找到優(yōu)化的辦法。

有些時候通過增加聚合查詢會使得效率更高，雖然這種方式會返回更多的行。也可以通過在 FROM 后面嵌套子查詢來保持中間查詢結果，然后再使用 UNION 獲取最終結果。

但是注意的是，在應用程序中最好是移除 WITH ROLLUP，而通過優(yōu)化來完成分組查詢。

結語：使用 GROUP BY 進行分組查詢時最好是使用索引列分組，若無需指定次序可以使用 ORDER BY NULL 進行優(yōu)化。倘若不按索引列分組的時候，則需要考慮變通的辦法，并且考慮是否要使用子查詢或使用 WITH ROLLUP 檢查性能后再做優(yōu)化。同時，為了防止分組查詢出現(xiàn)不可預料的錯誤，最好是開啟 ONLY_FULL_GROUP_BY。

以上就是MySQL 分組查詢的優(yōu)化方法的詳細內容，更多關于MySQL 分組查詢的優(yōu)化的資料請關注腳本之家其它相關文章！

最新評論