多关联多字段查询场景下按唯一用户分页的高性能实现方案咨询

解决方案 #

方案1：分三步查询（最通用，大数据量下性能最优） #

• 第一步：先做符合所有过滤条件的唯一用户ID分页
  把Query1和Query2的过滤条件合并，用半连接查询拿到当前页的用户ID列表，不会出现重复用户，性能远高于全量关联后去重：

SELECT DISTINCT u.user_id
FROM 用户主表 u
-- 写入Query1所有的关联逻辑
LEFT JOIN 关联表1 t1 ON u.user_id = t1.user_id
LEFT JOIN 关联表2 t2 ON u.user_id = t2.user_id
WHERE 
    -- 写入Query1所有参数化过滤条件
    t1.xxx = ${参数1}
    AND t2.yyy = ${参数2}
    -- 加入Query2的过滤条件，确保用户存在符合要求的订单
    AND EXISTS (
        SELECT 1 FROM 订单表 o
        -- 写入Query2所有的关联逻辑
        JOIN 订单关联表 ot ON o.order_id = ot.order_id
        WHERE 
            o.user_id = u.user_id
            -- 写入Query2所有参数化过滤条件
            AND ot.zzz = ${参数3}
    )
-- 必须加稳定排序字段，避免分页漏数据/重复数据
ORDER BY u.user_id ASC
LIMIT ${每页数量} OFFSET ${偏移量}

• 第二步：用拿到的用户ID列表分别拉取明细数据
  分别执行Query1和Query2，新增过滤条件user_id IN (第一步拿到的用户ID列表)，分别得到当前页用户的基础关联数据、符合条件的订单数据。
• 第三步：业务层组装
  在应用层按user_id为键，把用户数据和订单数据做关联组装即可，完全避免单查询出现的一用户多订单重复行问题。

方案2：窗口函数单查询（适合中等数据量场景） #

如果你的数据库支持DENSE_RANK窗口函数，可以直接单查询实现按唯一用户分页，不需要多次查询：

SELECT * FROM (
    SELECT
        -- 写入所有需要返回的用户字段、订单字段
        u.*, t1.*, o.*, ot.*,
        -- 按用户唯一标识排序生成排名，相同用户排名一致
        DENSE_RANK() OVER(ORDER BY u.user_id ASC) AS user_rank
    FROM 用户主表 u
    -- 写入Query1所有关联逻辑
    LEFT JOIN 关联表1 t1 ON u.user_id = t1.user_id
    -- 写入Query2所有关联逻辑，INNER JOIN直接过滤掉无符合条件订单的用户
    INNER JOIN 订单表 o ON u.user_id = o.user_id
    JOIN 订单关联表 ot ON o.order_id = ot.order_id
    WHERE
        -- 写入Query1和Query2所有参数化过滤条件
        t1.xxx = ${参数1}
        AND ot.zzz = ${参数3}
) AS full_data
-- 按用户排名取当前页范围，比如第一页30个用户就是1到30
WHERE user_rank BETWEEN ${(当前页-1)*每页数量 + 1} AND ${当前页*每页数量}

查询结果中user_rank相同的行即为同一个用户的多条订单数据，业务层按user_rank或者user_id分组即可得到固定数量的唯一用户。

性能优化建议 #

• 所有关联字段、过滤条件用到的字段必须建立联合索引，尤其是user_id、订单表的user_id字段，可大幅降低关联和过滤的耗时。
• 数据量较大时避免使用大偏移量OFFSET，推荐改用游标分页：记录上一页最后一个user_id，下一次查询新增条件u.user_id > ${上一页最大user_id}，直接LIMIT ${每页数量}即可，避免跳过大量数据的性能损耗。
• 若数据量达到千万级以上、过滤条件极复杂，可预先将符合条件的用户ID同步到ES、ClickHouse等OLAP引擎做分页查询，再回源关系型数据库拉取明细数据，性能会提升数倍。

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