如何用SQL统计用户表membergroupids各值数量？大数据量表优化方案

嘿，这个场景我太熟悉了——用PHP循环统计逗号分隔的分组ID，小数据量还行，数据一大就直接卡爆对吧？毕竟要把全表数据拉到应用层再逐条拆分统计，IO和内存开销都拉满了。直接在数据库层面处理才是最优解，把计算压力交给数据库，而且不用拉取全量数据，速度能快N倍！

下面针对主流数据库给出具体实现：

MySQL 解决方案 #

适用于MySQL 8.0+（支持CTE递归） #

递归CTE可以灵活拆分任意长度的逗号分隔字符串，不用提前预估分组数量：

WITH RECURSIVE split_groups AS (
    SELECT 
        userid,
        SUBSTRING_INDEX(membergroupids, ',', 1) AS membergroupid,
        SUBSTRING(membergroupids, LOCATE(',', membergroupids) + 1) AS remaining
    FROM user
    WHERE membergroupids IS NOT NULL AND membergroupids != ''
    UNION ALL
    SELECT 
        userid,
        SUBSTRING_INDEX(remaining, ',', 1) AS membergroupid,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_groups
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT membergroupid, COUNT(*) AS count
FROM split_groups
GROUP BY membergroupid
ORDER BY membergroupid;

适用于MySQL 5.x版本（无CTE） #

可以借助一个数字辅助表，数字数量覆盖你的最大分组数即可：

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(u.membergroupids, ',', n.n), ',', -1) AS membergroupid,
    COUNT(*) AS count
FROM user u
JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 -- 按需增加数字，覆盖最多分组数
) n ON CHAR_LENGTH(u.membergroupids) - CHAR_LENGTH(REPLACE(u.membergroupids, ',', '')) >= n.n - 1
GROUP BY membergroupid
ORDER BY membergroupid;

SQL Server 解决方案（2016+） #

SQL Server 2016及以上自带STRING_SPLIT函数，用法超简单：

SELECT 
    value AS membergroupid,
    COUNT(*) AS count
FROM user
CROSS APPLY STRING_SPLIT(membergroupids, ',')
GROUP BY value
ORDER BY value;

PostgreSQL 解决方案 #

PostgreSQL用string_to_array+unnest组合拆分字符串：

SELECT 
    unnest(string_to_array(membergroupids, ',')) AS membergroupid,
    COUNT(*) AS count
FROM user
GROUP BY membergroupid
ORDER BY membergroupid;

为什么这个方案更高效？ #

• 减少数据传输：数据库只返回最终统计结果，不用把全表的membergroupids字段传输到应用层，节省大量网络IO。
• 数据库级优化：数据库对分组统计的优化（比如索引支持）远优于应用层循环，尤其是大数据量下，能充分利用数据库的计算资源。
• 避免内存瓶颈：不用把几十万甚至几百万条数据加载到PHP内存中处理，彻底解决超时和内存溢出问题。

长期优化建议 #

如果这个统计需求是高频的，强烈建议把逗号分隔的membergroupids字段改成关联表（比如创建user_member_group表，包含userid和membergroupid两个字段）。这样不仅查询统计更高效，还符合数据库范式，避免数据冗余，后续维护也更方便。

内容的提问来源于stack exchange，提问作者Yi Zhou