MySQL 在處理 GROUP BY 時選擇 index 的效率問題

Percona 的「Speed up GROUP BY queries with subselects in MySQL」這篇講了 MySQL 在處理 GROUP BY 的效率問題。

舉原文的例子,也就是這樣的 SQL query:(我把 command 都改成大寫,其他部份都改成小寫)

SELECT a.name, SUM(a.count) a_sum, AVG(a.position) a_avg, b.col1, c.col2, d.col3
FROM
a JOIN
b ON (a.bid = b.id) JOIN
c ON (a.cid = c.id) JOIN
d ON (a.did = d.id) GROUP BY a.name, b.id, c.id, d.id

其中 TABLE a 有 1.3M rows,而 b、c、d 都只有 5 rows。

由於會需要計算每組 (a.name, b.id, c.id, d.id)SUM(a.count)AVG(a.position),不可避免的是需要對 TABLE a 完整的掃一次。所以效能的改善會在於可以減少 temporily table 的大小。

上面這組 SQL query 會先 JOIN 完後再 GROUP BY,也就是會全部先展開後再 GROUP BY

由於 GROUP BY 所使用到的條件都可以在 TABLE a 裡面找到,所以這邊可以先 GROUP BY 後再 JOIN,降低 temporily table 的大小:

SELECT a.name, a_sum, a_avg, b.col1, c.col2, d.col3
FROM
(SELECT name, SUM(count) a_sum, AVG(position) a_avg, bid, cid, did
 FROM a
 GROUP BY name, bid, cid, did) a JOIN
 b ON (a.bid = b.id) JOIN
 c ON (a.cid = c.id) JOIN
 d ON (a.did = d.id)

原文測試出來的結果是從 2.3 秒降到 1.8 秒:

The first query took 2.3 sec avg and the optimized query took 1.8 sec average, half a second faster.

另外一個改善是再加上 covering index:

ALTER TABLE a ADD INDEX (name, bid, cid, did, count, position);

加上去之後,原來的 query 變成 1.9 秒,而改善後的變成 0.7 秒,速度快很多。不過這是 trade-off,多了這個 index 在寫入時的成本也會增加。

One thought on “MySQL 在處理 GROUP BY 時選擇 index 的效率問題”

Leave a Reply

Your email address will not be published. Required fields are marked *