按主表分组的子查询COUNT高效检索及分组统计最优方案

嘿，这两个问题都是日常写SQL时经常遇到的性能卡点，我结合实际业务场景给你拆解下最高效的处理方式：

1. 按主表分组的子查询COUNT最高效检索方式 #

首先要明确：相关子查询（每行主表数据都触发一次子查询）是性能瓶颈的重灾区，最优解法优先用JOIN替代，配合合适的索引，具体分几种场景：

场景1：统计主表每行关联的子表记录数 #

比如主表orders，子表order_items，要统计每个订单的商品数量：

• 低效写法（相关子查询）：

SELECT 
    o.id,
    (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
FROM orders o;

• 高效写法（JOIN + GROUP BY）：

SELECT 
    o.id,
    COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id;

为什么更高效？数据库可以一次性完成JOIN操作后分组统计，避免了逐行触发子查询的重复开销。

关键优化点 #

• 给子表的外键字段（比如order_items.order_id）建立单列索引，或者复合索引(order_id, id)（覆盖索引，避免回表）；
• 如果主表的分组字段（比如orders.id）是主键，本身已有索引，无需额外创建；
• 若仅需判断子表是否存在记录（而非统计数量），用EXISTS替代COUNT(*)会更快，比如：

SELECT 
    o.id,
    CASE WHEN EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id) THEN 1 ELSE 0 END AS has_items
FROM orders o;

2. 按类别统计有logger记录数据的组数量（仅统计最新记录） #

先结合你给出的规则，我先假设核心表结构（如果和实际有出入，调整关联字段即可）：

• data_records：id（代理键）、logger_id（关联logger）、category_name（关联类别）、created_at（记录时间，用于判断最新）
• loggers：id、group_id（关联group）
• groups：id（组主键）

最优解法思路 #

1. 先筛选出每个logger的最新数据记录（仅保留每个logger的最新一条）；
2. 将最新记录关联到logger和group表；
3. 按category_name分组，统计去重后的group数量（同一个组可能多个logger有记录，需避免重复统计）。

具体SQL实现（两种高效写法） #

写法1：用窗口函数筛选最新记录（推荐，可读性+性能均衡）

WITH latest_data AS (
    SELECT 
        dr.logger_id,
        dr.category_name,
        -- 按logger分组，按时间倒序排名，取第1条即最新记录
        ROW_NUMBER() OVER (PARTITION BY dr.logger_id ORDER BY dr.created_at DESC) AS rn
    FROM data_records dr
)
SELECT 
    ld.category_name,
    COUNT(DISTINCT g.id) AS group_count
FROM latest_data ld
-- 关联logger表获取所属组
JOIN loggers l ON ld.logger_id = l.id
-- 关联组表
JOIN groups g ON l.group_id = g.id
-- 仅保留每个logger的最新记录
WHERE ld.rn = 1
GROUP BY ld.category_name;

写法2：用子查询获取最大时间（部分数据库优化器可能更偏好）

SELECT 
    dr.category_name,
    COUNT(DISTINCT g.id) AS group_count
FROM data_records dr
-- 关联子查询，获取每个logger的最新记录时间
JOIN (
    SELECT logger_id, MAX(created_at) AS latest_time
    FROM data_records
    GROUP BY logger_id
) latest ON dr.logger_id = latest.logger_id AND dr.created_at = latest.latest_time
JOIN loggers l ON dr.logger_id = l.id
JOIN groups g ON l.group_id = g.id
GROUP BY dr.category_name;

关键性能优化点 #

• 给data_records创建复合索引(logger_id, created_at DESC)：窗口函数或MAX()子查询可以直接利用这个索引快速定位最新记录，无需全表扫描；
• 给loggers创建覆盖索引(id, group_id)：关联时无需回表查询group_id，提升JOIN效率；
• 若category_name重复率极高，可考虑提前将category_name映射为category_id（整数类型），减少分组时的字符串比较开销。

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