mysql - 我如何优化此查询的性能？

Question

我有三个表，分别是用户、事件和购买。

用户有很多事件和购买，事件有4种类型。

我需要这样查询用户:

[
    {
        user_id: 1,

        // from activities
        post_count: 2,
        updated_count: 3,
        print_count: 4,
        share_count: 5,

        // from purchases
        purchase_count: 6
    },
    ...
]

我使用这个 sql:

SELECT u.id, post.post_count, updated.update_count, print.print_count, share.share_count, purchase.purchase_count

FROM users as u

LEFT JOIN (
  SELECT user_id, activity_type, count(*) as post_count 
  FROM activities 
  WHERE activity_type = 1
  GROUP BY user_id
  ) post

ON u.id = post.user_id

LEFT JOIN (
  SELECT user_id, activity_type, count(*) as update_count
  FROM activities 
  WHERE activity_type = 2
  GROUP BY user_id
  ) updated

ON u.id = updated.user_id

LEFT JOIN (
  SELECT user_id, activity_type, count(*) as print_count 
  FROM activities 
  WHERE activity_type = 3
  GROUP BY user_id
  ) print

ON u.id = print.user_id

LEFT JOIN (
  SELECT user_id, activity_type, count(*) as share_count 
  FROM activities 
  WHERE activity_type = 4
  GROUP BY user_id
  ) share

ON u.id = share.user_id


LEFT JOIN (
  SELECT user_id, count(*) AS purchase_count 
  FROM purchases 
  GROUP BY user_id
  ) purchase

ON u.id = purchase.user_id

我如何优化此查询的性能？

---

非常感谢Eugen Rieck

我将他的查询修改为这个，然后就可以了。

SELECT
  users.id AS user_id,
  SUM(IF((activities.activity_type=1),1,0)) AS post_count,
  SUM(IF((activities.activity_type=2),1,0)) AS update_count,
  SUM(IF((activities.activity_type=3),1,0)) AS print_count,
  SUM(IF((activities.activity_type=4),1,0)) AS share_count,
  IFNULL(purchase.count,0) AS purchase_count
FROM
  users
  LEFT JOIN activities ON activities.user_id=users.id
  LEFT JOIN (
    SELECT user_id, count(*) AS count 
    FROM purchases 
    GROUP BY user_id
  ) purchase
  ON users.id = purchase.user_id
GROUP BY users.id

Answer 1

目前您运行事件表 4 次 - 这可以合并为一个:

SELECT
  users.id AS user_id,
  SUM(IF(activites.activity_type=1,1,0)) AS post_count,
  SUM(IF(activites.activity_type=2,1,0)) AS update_count,
  SUM(IF(activites.activity_type=3,1,0)) AS print_count,
  SUM(IF(activites.activity_type=4,1,0)) AS share_count,
  IFNULL(COUNT(purchases.id),0) AS purchase_count
FROM
  users
  INNER JOIN activities ON activities.user_id=users.id
  LEFT JOIN purchases ON purchases.user_id=users.id
GROUP BY users.id