使用 GROUP 加入子查詢很慢

誰能幫我完成以下查詢？

SELECT
 voc.username AS Medewerker,
 voc.bestvastonbep_count AS Doorgezet,
 IFNULL(cc.call_count, 0) AS Telefoongesprekken,
 voc.bestvastonbep_count / IFNULL(cc.call_count, 0) AS Ratio
FROM (
 SELECT
   u.id AS user_id,
   CONCAT(u.firstname, ' ', u.lastname) AS username,
   COUNT(vo.id) AS bestvastonbep_count
 FROM `bestvastonbep`       vo
 JOIN `best`                b   ON b.id = vo.best_id
 JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
 JOIN `user`                u   ON u.id = b.user_id
 WHERE vos.type = 2
 AND   vos.status = 2
 AND  (b.date BETWEEN '2017-02-04' AND '2017-05-04')
 GROUP BY u.id
) voc
LEFT JOIN (
 SELECT
   user_id,
   COUNT(id) AS call_count
 FROM `call` c
 WHERE  (c.start BETWEEN '2017-02-04' AND '2017-05-04')
 AND c.duration > 0
 AND c.calltype = 'out'
 GROUP BY c.user_id
) cc
ON voc.user_id = cc.user_id;

它產生了預期的結果，但速度非常慢。

我讀過其他人關於類似查詢的問題。對他們來說，事實證明加入子查詢是沒有必要的。對我而言，不同之處在於我同時使用GROUP了兩個子查詢。希望有辦法，我只是沒有看到它。

編輯以響應 RDFozz

EXPLAIN 查詢給出以下結果。

第一個子查詢非常快。(0.1 s)

第二個子查詢大約需要 6 秒。

完整的查詢大約需要 57 秒。

call.user_id以及上有一個索引call.start。

但是呼叫表目前大約有 3200 萬行。

答案 我採用了 Rick James 所描述的 A 計劃。查詢現在如下所示：

SELECT
   voc.username AS Medewerker,
   voc.bestvastonbep_count AS Doorgezet,
   IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
   IFNULL(voc.bestvastonbep_count / count(c.user_id), "∞") AS Ratio
FROM (
   SELECT u.id AS user_id, CONCAT(u.firstname, ' ', u.lastname) as username, COUNT(vo.id) AS bestvastonbep_count
   FROM `bestvastonbep`       vo
   JOIN `best`                b   ON b.id = vo.best_id
   JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
   JOIN `user`                u   ON u.id = b.user_id
   WHERE vos.type = 2
   AND   vos.status = 2
   AND  (b.date BETWEEN '2017-02-04' AND '2017-05-04')
   GROUP BY u.id
) voc 
LEFT JOIN `call` c on
   (c.start BETWEEN '2017-02-04' AND '2017-05-04')
   AND c.duration > 0
   AND c.calltype = 'out'
   AND voc.user_id = c.user_id
GROUP BY voc.user_id;

大約需要 7 秒，具體取決於大小。更快會很好，但這對於我的目的來說是可以接受的。

爆炸-內爆

在第一個派生表 ( voc) 中，您有所謂的“explode-implode”綜合症。首先JOINs生成很多行，然後將GROUP BY其縮減為幾行。

有時解決方案是將 aJOIN變成子查詢：

SELECT  ...,
       b.foo
   FROM a
   LEFT JOIN b
   GROUP BY a.id

–>

SELECT  ...,
       ( SELECT foo FROM b WHERE ... = a... ) AS foo
   FROM a

請注意，GROUP BY消失。此範例特別適用，因為保留LEFT JOIN了 的“可選”性質。foo

（GROUP BYincc情況不一樣。）

派生 JOIN 派生

MySQL 在處理JOINing兩個子查詢（voc和）方面很糟糕（5.6 之前）或很差（5.6+ cc）。

計劃 A：將其中一個子查詢變成JOIN另一個。

計劃 B：將其中一個子查詢放入 aTEMPORARY TABLE並給它一個適當的索引：(user_id)。

索引

在沒有 的情況下SHOW CREATE TABLE，必須做一些猜測……

vos: INDEX(type, status, best_id)
b:   INDEX(date)
c:   INDEX(calltype, start, duration, user_id)

並更改COUNT(id)為COUNT(*).

注： start BETWEEN '2017-02-04' AND '2017-05-04'是“包容”。考慮改為

   start >= '2017-02-04'
AND start  < '2017-02-04' + INTERVAL 3 MONTH

請EXPLAIN SELECT ...在添加這些索引後提供。

我看到可能被零除；也許你想要

IFNULL(voc.bestvastonbep_count / cc.call_count, 0)

引用自：https://dba.stackexchange.com/questions/172723