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

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

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

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

而下面的查詢(xún)方式則更有效：

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

而使用 actor.actor_id 進(jìn)行分組會(huì)比 film_actor.actor_id更高效。

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

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

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

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

然而，子查詢(xún)中創(chuàng)建和填充臨時(shí)表的代價(jià)可能比理論上看起來(lái)的死辦法更高。需要記住的是，子查詢(xún)構(gòu)建的臨時(shí)表是沒(méi)有索引的，這會(huì)導(dǎo)致性能上的下降。

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

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

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

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

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

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

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