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 在寫入時的成本也會增加。
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
