MySQL分组过滤查询：统计仅拥有红色衬衫的用户衬衫数量

一、查询仅拥有红色衬衫的用户的衬衫数量 #

我猜你大概率一开始写了类似这样的查询：

SELECT user_id, COUNT(*) AS red_shirt_count
FROM Wardrobe
WHERE color = 'red'
GROUP BY user_id;

但这个结果不符合预期——它会把那些同时拥有红色和其他颜色衬衫的用户也统计进来，而我们要的是只有红色衬衫的用户。下面给你几种靠谱的解法：

方法1：分组后用HAVING过滤全红用户 #

这是最直观的方法，利用分组后的聚合函数判断该用户所有衬衫的颜色是否都是红色：

SELECT user_id, COUNT(*) AS red_shirt_count
FROM Wardrobe
GROUP BY user_id
HAVING MIN(color) = 'red' AND MAX(color) = 'red';

原理很简单：如果一个用户的衬衫颜色最小值和最大值都是red，说明他所有衬衫都是红色，没有其他颜色。这个方法在color字段有索引或者是枚举类型时，效率很高。

方法2：排除法——先去掉有非红色衬衫的用户 #

先找出所有拥有非红色衬衫的用户ID，再在主查询里排除他们，只统计剩下的用户的红色衬衫数量：

SELECT user_id, COUNT(*) AS red_shirt_count
FROM Wardrobe
WHERE user_id NOT IN (
    SELECT DISTINCT user_id
    FROM Wardrobe
    WHERE color != 'red'
)
AND color = 'red'
GROUP BY user_id;

如果你的Wardrobe表很大，记得给user_id和color字段加索引，这样子查询和主查询的过滤都会快很多。

方法3：窗口函数（适合复杂场景） #

如果之后需要扩展逻辑（比如统计仅拥有某几种颜色的用户），窗口函数会更灵活：

WITH user_color_stats AS (
    SELECT 
        user_id,
        color,
        -- 统计每个用户拥有的颜色种类数
        COUNT(DISTINCT color) OVER (PARTITION BY user_id) AS total_color_types
    FROM Wardrobe
)
SELECT user_id, COUNT(*) AS red_shirt_count
FROM user_color_stats
WHERE color = 'red' AND total_color_types = 1
GROUP BY user_id;

先给每个用户计算他们的颜色种类数，再筛选出颜色为红色且种类数仅为1的用户，最后统计数量。

二、百万级多表关联的高效查询技术 #

当数据量达到百万级，关联多张表时，核心思路是减少磁盘IO、利用索引、避免不必要的计算，以下是最实用的优化手段：

• 优先优化索引

  • 确保所有JOIN关联字段（比如user_id）都建立了B-tree索引，等值关联时索引能快速定位数据。
  • 给WHERE条件里的过滤字段加索引，提前过滤掉不需要的数据，减少后续关联的数据量。
  • 使用覆盖索引：只查询需要的字段，让索引包含所有查询所需的列，避免回表查询（比如SELECT user_id, name FROM users WHERE age > 30，如果索引是(age, user_id, name)，就不需要再去查主表）。

• 选择合适的JOIN类型

  • 大表关联小表：用嵌套循环JOIN，把小表放在内层，利用索引快速匹配大表的数据，减少扫描次数。
  • 两个大表关联：用哈希JOIN（MySQL 8.0+、PostgreSQL等都支持），数据库会在内存中构建小表的哈希表，然后扫描大表进行匹配，比嵌套循环高效得多。
  • 绝对要避免笛卡尔积——确保每个JOIN都有明确的关联条件，不然数据量会爆炸。

• 替换低效子查询为JOIN

  • 相关子查询（比如WHERE子句里的子查询依赖外层表字段）会被执行N次（N是外层表行数），效率极低。改成LEFT JOIN + IS NULL或者INNER JOIN，能一次性完成关联。比如把NOT IN改成：

    SELECT w.user_id, COUNT(*) AS red_shirt_count
    FROM Wardrobe w
    LEFT JOIN (SELECT DISTINCT user_id FROM Wardrobe WHERE color != 'red') non_red
        ON w.user_id = non_red.user_id
    WHERE non_red.user_id IS NULL AND w.color = 'red'
    GROUP BY w.user_id;
    

    这种写法在处理NULL值时比NOT IN更可靠，效率也更高。

• 数据分区

  • 如果表有明显的分区键（比如时间、用户ID范围），可以对表进行分区。查询时数据库只会扫描相关分区，大幅减少IO操作。比如按月份分区的订单表，查询2024年1月的数据时，只扫描1月的分区即可。

• 物化视图（Materialized Views）

  • 如果你的查询是重复执行的，且数据不需要实时更新，可以创建物化视图——预先计算好关联后的结果并存储起来，查询时直接读取物化视图，速度比每次关联快几个数量级。注意要定期刷新物化视图来保证数据新鲜度。

• 减少排序和分组的开销

  • 不需要排序就去掉ORDER BY，如果必须排序，确保排序字段有索引，让数据库利用索引排序，避免临时表排序。
  • GROUP BY的字段如果有索引，数据库可以直接用索引分组，不需要额外的排序和临时表。

• 调整数据库配置

  • 适当调大数据库的内存参数（比如PostgreSQL的work_mem、MySQL的join_buffer_size），让数据库能在内存中处理更多的JOIN、排序操作，减少磁盘IO。

内容的提问来源于stack exchange，提问作者Peter Trang