大表高效查询：按指定status各取3条唯一ID记录

我有一张超大型数据表，需求是：从WHERE子句指定的每个status中获取3条唯一ID的记录，一旦某个status凑够3条记录就立即停止检索该status，转而处理下一个。由于表数据量极大且这个查询会被频繁调用，对执行效率要求极高。目前用ROW_NUMBER()窗口函数实现的SQL耗时太长，急需更高效的替代方案。

原始数据表 #

现有SQL语句 #

SELECT a.* 
FROM (
  select
    id,
    status,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY status) as rn,
    ROW_NUMBER() OVER(PARTITION BY status ORDER BY id) as rne
  from x 
  where status in ('a', 'b' , 'e') 
) a 
WHERE rn <= 1 and rne <=3;

期望查询结果 #

高效优化方案 #

方案1：UNION ALL 分状态单独查询 #

针对每个目标status单独执行查询，结合DISTINCT和LIMIT快速获取3个唯一ID，再关联回原表获取对应记录，确保每个ID只返回一条对应status的结果：

SELECT t.id, t.status
FROM (
    -- 提取status='a'的前3个唯一ID
    SELECT DISTINCT id FROM x WHERE status = 'a' LIMIT 3
    UNION ALL
    -- 提取status='b'的前3个唯一ID
    SELECT DISTINCT id FROM x WHERE status = 'b' LIMIT 3
    UNION ALL
    -- 提取status='e'的前3个唯一ID
    SELECT DISTINCT id FROM x WHERE status = 'e' LIMIT 3
) AS ids
JOIN x t ON ids.id = t.id AND t.status IN ('a', 'b', 'e')
GROUP BY t.id, t.status;

方案2：精简版单状态查询 #

直接在每个子查询中过滤并返回符合要求的唯一记录，逻辑更直观：

-- 获取status='a'的前3个唯一ID记录
SELECT id, status
FROM (
    SELECT id, status, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS rn
    FROM x WHERE status = 'a'
    LIMIT 3
) a WHERE rn=1

UNION ALL

-- 获取status='b'的前3个唯一ID记录
SELECT id, status
FROM (
    SELECT id, status, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS rn
    FROM x WHERE status = 'b'
    LIMIT 3
) b WHERE rn=1

UNION ALL

-- 获取status='e'的前3个唯一ID记录
SELECT id, status
FROM (
    SELECT id, status, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS rn
    FROM x WHERE status = 'e'
    LIMIT 3
) e WHERE rn=1;

核心优化要点 #

• 强制索引优化：必须创建复合索引(status, id)，让数据库可以快速按status过滤数据，同时直接基于id去重排序，避免全表扫描。
• 提前终止扫描：每个子查询的LIMIT 3会在找到3个唯一ID后立即停止检索，彻底避免原方案中窗口函数需要扫描所有符合条件记录再分区排序的高成本操作。
• 用UNION ALL替代UNION：无需对结果集去重合并，各子查询结果独立返回，性能大幅提升。

效率对比说明 #

原方案的窗口函数需要先扫描所有status IN ('a','b','e')的记录，再执行两次全局分区排序，数据量极大时IO和计算成本极高。优化方案中每个status单独查询，利用索引快速定位目标数据，找到3条唯一ID就停止扫描，整体处理的数据量仅为原方案的极小部分，执行效率会有质的飞跃。

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