如何高效实现同表按类型均分分页的多行查询？

现有表结构如下：

create table user_tasks
(
    id                      bigserial            primary key,
    type                    varchar(255)         not null,
    ...
);

需求：每页查询20条数据，按类型均分。例如存在10种不同type时，每种类型需返回2条；且每种类型有专属过滤条件。

当前实现（伪代码）：

array types = [...];

Collection typesSql = new Collection();
foreach (type of types) {
 typesSql.push('SELECT * FROM user_tasks WHERE type =: type AND ... LIMIT 10');
}

string sql = typesSql->implode(' UNION ALL ');

array result = DB::query(sql);

当前问题：无法提前知晓每种类型的可用数据量，因此先为每种类型查询10条，再从总结果（如10种类型则返回100条）中缩减至20条，但查询速度较慢，推测是UNION ALL合并多个查询导致的。

想知道是否存在更高效的实现方式，比如能否编写一个查询，仅当已选数据不足20条时才从新类型中选取数据？

方案1：窗口函数分组取数+全局限制 #

利用ROW_NUMBER()窗口函数给每个类型的记录编号，筛选出每个类型的前N条（N=20/类型总数），最后取前20条即可。如果部分类型不足N条，会自动由其他有剩余数据的类型补全（若有）。

示例SQL（假设已知类型列表及对应过滤条件）：

WITH ranked_tasks AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY id DESC) AS rn -- 按业务需求排序，比如id倒序
    FROM user_tasks
    WHERE 
        (type = 'type1' AND ...) -- 类型1专属过滤条件
        OR (type = 'type2' AND ...) -- 类型2专属过滤条件
        -- 依次添加所有目标类型的过滤条件
)
SELECT * FROM ranked_tasks
WHERE rn <= CEIL(20 / (SELECT COUNT(DISTINCT type) FROM user_tasks WHERE ...)) -- 计算单类型最大取数，10种类型则为2
ORDER BY type, rn
LIMIT 20;

若类型列表是动态传入的，注意用参数绑定拼接条件，避免SQL注入。

方案2：递归CTE动态凑数 #

用递归CTE依次从每个类型取数据，直到总数达到20条，避免一次性查询过多冗余数据。

示例SQL：

WITH RECURSIVE task_batch AS (
    -- 初始化：取第一个类型的前20条（先多取，后续会自动截断）
    SELECT 
        *,
        1 AS batch_num,
        COUNT(*) OVER () AS batch_count
    FROM user_tasks
    WHERE type = 'type1' AND ... -- 第一个类型的过滤条件
    LIMIT 20
    UNION ALL
    -- 递归：若已取总数不足20，取下一个类型的(20-已取数)条
    SELECT 
        t.*,
        tb.batch_num + 1,
        COUNT(*) OVER () AS batch_count
    FROM task_batch tb
    CROSS JOIN LATERAL (
        SELECT * FROM user_tasks
        WHERE type = 'type' || (tb.batch_num + 1) AND ... -- 下一个类型的过滤条件
        LIMIT GREATEST(20 - (SELECT COUNT(*) FROM task_batch), 0)
    ) t
    WHERE (SELECT COUNT(*) FROM task_batch) < 20
)
SELECT * FROM task_batch
LIMIT 20;

实际使用时可通过UNNEST处理数组类型的参数，动态传入类型列表。

方案3：先统计再按需查询 #

先查询每个类型满足条件的记录数，计算每个类型的合理取数上限，再针对性查询，减少不必要的数据读取。

伪代码示例：

// 1. 统计各类型可用数据量
$typeCounts = DB::query("
    SELECT type, COUNT(*) as count 
    FROM user_tasks 
    WHERE (type = :type1 AND ...) OR (type = :type2 AND ...) 
    GROUP BY type
");

// 2. 计算每个类型的取数上限
$totalNeed = 20;
$typeList = $typeCounts->pluck('type')->toArray();
$typeCount = count($typeList);
$baseNum = floor($totalNeed / $typeCount);
$remain = $totalNeed % $typeCount;
$takeMap = [];

foreach ($typeCounts as $tc) {
    $take = $baseNum;
    // 剩余额度优先分配给有足够数据的类型
    if ($remain > 0 && $tc['count'] > $baseNum) {
        $take++;
        $remain--;
    }
    // 取数不能超过类型实际可用数
    $takeMap[$tc['type']] = min($take, $tc['count']);
}

// 3. 按计算好的数量查询各类型数据
$queries = [];
foreach ($takeMap as $type => $num) {
    if ($num <= 0) continue;
    $queries[] = "SELECT * FROM user_tasks WHERE type = :{$type} AND ... LIMIT {$num}";
}

$sql = implode(' UNION ALL ', $queries);
$result = DB::query($sql);

关键优化注意点 #

• 给type字段建立索引，同时为各类型过滤条件涉及的字段添加组合索引，大幅提升单类型查询速度。
• 核心思路是按需取数，避免一次性查询远超需求的数据量。
• 递归CTE方案需确保数据库版本支持（PostgreSQL、MySQL 8+均支持）。

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