Month: June 2015

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 在寫入時的成本也會增加。