多个左连接的 MySQL COUNT - 优化

Question

我有一个查询，它通过使用 LEFT JOIN 和子查询从多个表中获取计数。这个想法是统计一个成员参加过的各种事件。

架构如下所示:

成员PK member_id

表1PK tbl1_idFK member_id

表2PK tbl2_idFK member_id

表3PK tbl3_idFK member_id

我的查询是这样的:

SELECT t1.num1,t2.num2,t3.num3
FROM member m
LEFT JOIN
(
   SELECT member_id,COUNT(*) as num1
   FROM table1
   GROUP BY member_id
) t1 ON t1.member_id = m.member_id
LEFT JOIN
(
   SELECT member_id,COUNT(*) as num2
   FROM table2
   GROUP BY member_id
) t2 ON t2.member_id = m.member_id
LEFT JOIN
(
   SELECT member_id,COUNT(*) as num3
   FROM table3
   GROUP BY member_id
) t3 ON t3.member_id = m.member_id
WHERE m.member_id = 27

其中 27 是测试 ID。实际查询连接了三个以上的表，并且查询运行了多次，并且 member_id 被更改。问题是这个查询运行得很慢。我得到了我需要的信息，但我想知道是否有人可以建议一种优化方法。非常感谢任何建议。非常感谢。

Answer 1

您应该重构您的查询。您可以通过重新排序查询收集数据的方式来做到这一点。怎么办？

• 首先应用 WHERE 子句
• 最后应用 JOIN

这是您的原始查询:

SELECT t1.num1,t2.num2,t3.num3 
FROM member m 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num1 
   FROM table1 
   GROUP BY member_id 
) t1 ON t1.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num2 
   FROM table2 
   GROUP BY member_id 
) t2 ON t2.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num3 
   FROM table3 
   GROUP BY member_id 
) t3 ON t3.member_id = m.member_id 
WHERE m.member_id = 27 

这是你的新查询

SELECT
   IFNULL(t1.num1,0) num1,
   IFNULL(t1.num2,0) num2,
   IFNULL(t1.num3,0) num3
FROM
(
   SELECT * FROM member m 
   WHERE member_id = 27
) 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num1 
   FROM table1 
   WHERE member_id = 27
   GROUP BY member_id 
) t1 ON t1.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num2 
   FROM table2 
   WHERE member_id = 27
   GROUP BY member_id 
) t2 ON t2.member_id = m.member_id 
LEFT JOIN 
( 
   SELECT member_id,COUNT(*) as num3 
   FROM table3 
   WHERE member_id = 27
   GROUP BY member_id 
) t3 ON t3.member_id = m.member_id 
;

顺便说一句，我将 member m 更改为 SELECT * FROM member m WHERE member_id = 27 以防您需要有关成员 27 的任何信息。我​​还添加了 IFNULL 在计数为 NULL 的情况下对每个结果生成 0 的函数。

你需要绝对确定

• member_id是member表的主键
• member_id在table1、table2、table3中有索引

试一试!!!