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

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

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

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 進(jìn)行分組會(huì)比 film_actor.actor_id更高效。

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

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

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

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)建和填充臨時(shí)表的代價(jià)可能比理論上看起來的死辦法更高。需要記住的是，子查詢構(gòu)建的臨時(shí)表是沒有索引的，這會(huì)導(dǎo)致性能上的下降。

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

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

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

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

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

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

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

最新評(píng)論