MySql 统计子表多个字段的查询优化

有一张用户表 t_users，存储着用户的信息。还有一张朋友信息表t_user_friends，存储着用户的朋友信息。用户表和朋友信息表通过user_uid进行关联。朋友分好几种类型，现在需要统计每个用户每种类型朋友的数量。

这个查询语句该如何写呢？

查看历史版本，发现了好几个版本。

第一个版本

SELECT A.user_name,

COUNT(DISTINCT B.friend_uid) as friendType0,

COUNT(DISTINCT C.friend_uid) as friendType1,

COUNT(DISTINCT D.friend_uid) as friendType2

FROM t_users as A

LEFT JOIN t_user_friends as B on A.user_uid = B.user_uid and B.friend_type= 0 and B.is_delete = 0

LEFT JOIN t_user_friends as C on A.user_uid = C.user_uid and C.friend_type= 1 and C.is_delete = 0

LEFT JOIN t_user_friends as D on A.user_uid = D.user_uid and D.friend_type= 2 and D.is_delete = 0

where A.is_delete = 0

group by A.user_uid

这个版本存在一个明显问题，多次 JOIN 导致性能问题。

对同一个大表进行多次 LEFT JOIN 会导致性能下降，尤其是在 t_user_friends 表记录较多时。这种设计不是很高效，数据量大时可能会导致查询执行时间过长。

第二个版本

SELECT A.user_name,

IF(B.friend_type = 0, COUNT(B.friend_uid), 0) AS friendType0,

IF(B.friend_type = 1, COUNT(B.friend_uid), 0) AS friendType1,

IF(B.friend_type = 2, COUNT(B.friend_uid), 0) AS friendType2

FROM t_users AS A # 用户表

LEFT JOIN t_user_friends AS B ON A.user_uid = B.user_uid AND B.is_delete = 0 # 朋友表

WHERE A.is_delete = 0

GROUP BY A.user_uid;

这一版想精简语句，但统计结果不对，让我们分析一下：

1. COUNT() 的错误用法

COUNT(B.friend_uid) 计算的是匹配到的 friend_uid 的数量，而不考虑 B.friend_type 的值。由于 COUNT() 作用于整个 GROUP BY 后的结果，这样每次都会计算 B.friend_uid 的总数，而不会对不同的 friend_type 进行细分统计。

假设，如果一个用户有 5 个好友，且其中 2 个 friend_type = 0，1 个 friend_type = 1，2 个 friend_type = 2，那么每个 IF 判断都会计算 5，因为 COUNT() 对所有关联到该用户的好友进行计数。

2. IF() 的位置错误

IF(B.friend_type = X, COUNT(B.friend_uid), 0) 的设计方式也是错误的。COUNT() 计算的是分组后的聚合结果，而不是每一行的数据，所以 IF 不会在 GROUP BY 之前对每一行进行判断，而是在已经计算过 COUNT() 之后才执行。

第三个版本

SELECT A.user_name,

IFNULL(SUM(B.friend_type = 0), 0) as friendType0,

IFNULL(SUM(B.friend_type = 1), 0) as friendType1,

IFNULL(SUM(B.friend_type = 2), 0) as friendType2

FROM t_users as A #用户表

LEFT JOIN t_user_friends as B on A.user_uid = B.user_uid and B.is_delete = 0 #朋友表

where A.is_delete = 0

group by A.user_uid

简化 JOIN操作，查询的执行时间将大大缩短，能够提升性能。

这次统计结果对了，分析一下：

SUM(B.friend_type = 0) 是一个布尔条件，当 B.friend_type = 0 时，它返回 1，否则返回 0。通过 SUM() 计算每个用户的 friend_type = 0 的好友数量。

IFNULL(SUM(B.friend_type = 0), 0) 是为了处理没有朋友记录的情况。如果 SUM() 返回 NULL，那么将其设置为 0，确保结果不会为空。

这个查询有意义，还考虑了没有朋友的情况。

第四个版本

SELECT A.user_name,

SUM(IF(B.friend_type = 0, 1, 0)) AS friendType0,

SUM(IF(B.friend_type = 1, 1, 0)) AS friendType1,

SUM(IF(B.friend_type = 2, 1, 0)) AS friendType2

FROM t_users AS A # 用户表

LEFT JOIN t_user_friends AS B ON A.user_uid = B.user_uid AND B.is_delete = 0 # 朋友表

WHERE A.is_delete = 0

GROUP BY A.user_uid;

保持JOIN的简化。一起分析一下：

SUM(IF()) 逻辑：IF(B.friend_type = 0, 1, 0) 会对每个 B.friend_type = 0 的记录计数 1，否则计数 0。然后 SUM() 累加这些值，从而计算出每种好友类型的数量。

使用 IF 语句直接进行条件判断，逻辑清晰易读。SUM(IF()) 的语法简单、直观，执行速度较快。

第五个版本

SELECT A.user_name,

SUM(CASE WHEN B.friend_type = 0 THEN 1 ELSE 0 END) as friendType0,

SUM(CASE WHEN B.friend_type = 1 THEN 1 ELSE 0 END) as friendType1,

SUM(CASE WHEN B.friend_type = 2 THEN 1 ELSE 0 END) as friendType2

FROM t_users as A

LEFT JOIN t_user_friends as B

ON A.user_uid = B.user_uid

AND B.is_delete = 0

WHERE A.is_delete = 0

GROUP BY A.user_uid;

保持JOIN的简化。一起分析一下：

使用 SUM(CASE WHEN ... THEN ... ELSE ... END) 直接计算每个类别的数量，并通过 THEN 1 ELSE 0 确保结果是整数值。

CASE WHEN 语句在处理复杂逻辑时非常灵活，可以方便地扩展为更多条件判断。更容易维护，尤其是当条件判断变得复杂时，CASE 的可读性较好。

对于简单的条件判断，使用 CASE WHEN 语句相比 IF 显得冗长，书写量较大。由于 CASE 的复杂性，在大数据量下的性能可能不如 IF 快速。

最后总结

第一个版本的写法比较冗余，并且影响性能，肯定是不建议的。第二个版本查询的数据都不对，更不用考虑了。

后面三个版本，哪种写法更优呢？让我们再过一遍。

使用 IFNULL(SUM(...))

SELECT A.user_name,

IFNULL(SUM(B.friend_type = 0), 0) as friendType0,

IFNULL(SUM(B.friend_type = 1), 0) as friendType1,

IFNULL(SUM(B.friend_type = 2), 0) as friendType2

FROM t_users as A

LEFT JOIN t_user_friends as B on A.user_uid = B.user_uid and B.is_delete = 0

WHERE A.is_delete = 0

GROUP BY A.user_uid;

SUM(B.friend_type = 0) 是一个逻辑表达式。对于每一行，如果 friend_type 为 0，返回 1；否则返回 0。通过 SUM() 对这些值进行累加，得到 friend_type = 0 的好友数量。

IFNULL() 用来处理结果为 NULL 的情况，将其转换为 0。

优点：

简洁：通过逻辑表达式直接返回值，结构简洁清晰。

处理 NULL 情况：使用 IFNULL 确保即使没有匹配的好友类型，也返回 0。

缺点：

IFNULL 额外增加了一点计算成本，虽然影响不大，但在大规模数据处理时可能稍微拖慢性能。

一些数据库引擎可能对 IFNULL 的优化不如其他函数高效。

2. 使用 SUM(IF(...))

SELECT A.user_name,

SUM(IF(B.friend_type = 0, 1, 0)) AS friendType0,

SUM(IF(B.friend_type = 1, 1, 0)) AS friendType1,

SUM(IF(B.friend_type = 2, 1, 0)) AS friendType2

FROM t_users AS A

LEFT JOIN t_user_friends AS B ON A.user_uid = B.user_uid AND B.is_delete = 0

WHERE A.is_delete = 0

GROUP BY A.user_uid;

IF(B.friend_type = 0, 1, 0) 的逻辑是：如果 friend_type 为 0，返回 1，否则返回 0。通过 SUM() 来累计 1 的次数，以统计每种好友类型的数量。

优点：

更广泛的兼容性：IF 是标准 SQL 函数，在很多数据库系统中都有较好的支持。

IF 逻辑表达式明确，易于理解和维护。

缺点：

代码稍微冗长：每个判断都需要一个完整的 IF 语句，相较于逻辑表达式的写法，代码更长。

与 IFNULL 相比，虽然处理逻辑清晰，但性能上并无显著提升。

SELECT A.user_name,

SUM(CASE WHEN B.friend_type = 0 THEN 1 ELSE 0 END) as friendType0,

SUM(CASE WHEN B.friend_type = 1 THEN 1 ELSE 0 END) as friendType1,

SUM(CASE WHEN B.friend_type = 2 THEN 1 ELSE 0 END) as friendType2

FROM t_users as A

LEFT JOIN t_user_friends as B

ON A.user_uid = B.user_uid

AND B.is_delete = 0

WHERE A.is_delete = 0

GROUP BY A.user_uid;

使用 CASE WHEN 来进行条件判断：如果 B.friend_type = 0，则返回 1，否则返回 0。然后通过 SUM() 累计这些值，计算好友类型的数量。

优点：

可读性高：CASE WHEN 语法结构清晰，容易扩展和维护，尤其是在需要更多条件时。

标准 SQL：CASE WHEN 是 SQL 标准的一部分，几乎适用于所有数据库系统。

缺点：

稍显冗长：CASE WHEN 语句比简单的 IF 和逻辑表达式稍微冗长一些。

综合对比：

性能：

在小规模数据下，三者性能差异不大。

在大数据量的情况下，SUM(B.friend_type = X) 的写法稍微占优，因为逻辑表达式直接返回布尔值，效率较高。而 CASE WHEN 相对复杂，可能稍慢。

可读性：

CASE WHEN 是最具可读性和扩展性的写法，特别是当逻辑复杂时，CASE WHEN 语法可以处理更多的条件分支。

SUM(B.friend_type = X) 是最简洁的写法，但不如 CASE WHEN 易读。

兼容性：

CASE WHEN 和 SUM(IF(...)) 是标准 SQL，更具兼容性。IFNULL 是特定于某些数据库系统的扩展（如 MySQL）。

看来这三种查询方式各有千秋，先收藏了。