语言学习平台用户推荐排序查询性能优化咨询

开发者阿泽

2026-7-11

语言学习平台用户推荐查询优化

背景与数据表结构

我正在构建一个语言学习平台，现有数据表如下：

create table users
(  
    id                  bigserial primary key,
    ...
)

create table users_languages
(
    id                   bigserial primary key,
    user_id              bigint    not null
        constraint users_languages_user_id_foreign references users,
    level                varchar(255)         not null,
    lang_code            varchar(10)          not null,
    ...
)

users_languages表用于存储用户掌握的语言：

level仅取值NATIVE（母语）或LEARN（学习中）
lang_code为ISO标准语言编码

现有查询与性能问题

已编写查询语句如下（逻辑正确，但在3万行数据的表上耗时约2秒）：

nativeCode := 当前用户的母语lang_code
langCodes := 当前用户正在学习的lang_codes集合

WITH user_lang_priority AS NOT MATERIALIZED (
    SELECT l.user_id, MIN(CASE 
        WHEN l.level = 'NATIVE' AND l.lang_code IN(:langCodes) THEN
          CASE
            WHEN EXISTS (
               SELECT ll.id FROM users_languages ll
               WHERE ll.level != 'NATIVE'
               AND ll.lang_code = :nativeCode
               AND ll.user_id = l.user_id
            ) THEN 1 ELSE 2
          END
        WHEN l.lang_code IN(:langCodes) THEN 3
    END) AS priority
    FROM users_languages l
    GROUP BY l.user_id
)
SELECT u.*
FROM users u
INNER JOIN user_lang_priority lp ON u.id = lp.user_id
GROUP BY u.id
ORDER BY lp.priority ASC, u.id DESC

查询执行计划

更新后的执行计划：

Sort  (cost=777435.38..777508.24 rows=29145 width=762)
"  Sort Key: (min(CASE WHEN (((l.level)::text = 'NATIVE'::text) AND ((l.lang_code)::text = ANY ('{fin,fre,ger}'::text[]))) THEN CASE WHEN (hashed SubPlan 2) THEN 1 ELSE 2 END WHEN ((l.lang_code)::text = ANY ('{rus,fre,ger}'::text[])) THEN 3 ELSE NULL::integer END)), u.id DESC"
  ->  Hash Join  (cost=1677.14..775274.14 rows=29145 width=762)
        Hash Cond: (l.user_id = u.id)
        ->  GroupAggregate  (cost=0.29..773229.32 rows=29145 width=12)
              Group Key: l.user_id
              ->  Index Scan using users_languages_user_id_index on users_languages l  (cost=0.29..3005.29 rows=84970 width=19)
              SubPlan 2
                ->  Bitmap Heap Scan on users_languages ll  (cost=299.15..1487.36 rows=14990 width=8)
                      Recheck Cond: ((lang_code)::text = 'eng'::text)
                      Filter: ((level)::text <> 'NATIVE'::text)
                      ->  Bitmap Index Scan on users_languages_lang_code_index  (cost=0.00..295.40 rows=22814 width=0)
                            Index Cond: ((lang_code)::text = 'eng'::text)
        ->  Hash  (cost=1253.60..1253.60 rows=33860 width=758)
              ->  Seq Scan on users u  (cost=0.00..1253.60 rows=33860 width=758)

优化方案

1. 重构查询逻辑，消除重复子查询

原查询中EXISTS子查询会对每个用户重复执行，是主要性能瓶颈。改为一次性聚合用户的语言属性，避免重复计算：

WITH current_user_langs AS (
    SELECT 
        MAX(CASE WHEN level = 'NATIVE' THEN lang_code END) AS native_code,
        ARRAY_AGG(CASE WHEN level = 'LEARN' THEN lang_code END) AS learn_codes
    FROM users_languages
    WHERE user_id = :current_user_id  -- 替换为当前用户ID
),
user_lang_stats AS (
    SELECT 
        l.user_id,
        -- 判断是否满足规则1的两个条件
        BOOL_OR(l.level = 'NATIVE' AND l.lang_code IN (SELECT learn_codes FROM current_user_langs)) AS matches_learn_as_native,
        BOOL_OR(l.level = 'LEARN' AND l.lang_code = (SELECT native_code FROM current_user_langs)) AS matches_native_as_learn,
        -- 判断是否满足规则3
        BOOL_OR(l.lang_code IN (SELECT native_code FROM current_user_langs) OR l.lang_code IN (SELECT learn_codes FROM current_user_langs)) AS shares_common_lang
    FROM users_languages l
    GROUP BY l.user_id
)
SELECT u.*
FROM users u
JOIN user_lang_stats s ON u.id = s.user_id
WHERE s.shares_common_lang  -- 过滤掉无共同语言的用户
ORDER BY 
    CASE
        WHEN s.matches_learn_as_native AND s.matches_native_as_learn THEN 1
        WHEN s.matches_learn_as_native THEN 2
        ELSE 3
    END ASC,
    u.id DESC;

2. 添加复合索引，加速过滤与聚合

根据查询的过滤、关联逻辑，创建针对性复合索引：

-- 按用户ID聚合时，快速获取该用户的所有语言等级和编码
CREATE INDEX idx_users_languages_user_level_lang ON users_languages (user_id, level, lang_code);

-- 按语言编码+等级过滤，加速判断用户是否学习了指定语言
CREATE INDEX idx_users_languages_lang_level ON users_languages (lang_code, level);

3. 离线缓存优先级（高频场景）

如果推荐查询是高频操作，可定期离线计算用户的优先级分数，存储到专用表中，实时查询时直接读取：

创建表user_recommendation_priority(user_id bigint primary key, priority int)
每天定时运行优化后的查询，更新该表的priority字段
查询时只需关联该表并排序，性能会大幅提升

优化预期

消除重复子查询，将用户语言统计合并为单次聚合
利用索引减少全表扫描范围，降低GroupAggregate的计算成本
提前过滤无共同语言的用户，减少排序的数据量

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

语言学习平台用户推荐排序查询性能优化咨询

背景与数据表结构 #

推荐排序规则 #

现有查询与性能问题 #

查询执行计划 #

优化方案 #

1. 重构查询逻辑，消除重复子查询 #

2. 添加复合索引，加速过滤与聚合 #

3. 离线缓存优先级（高频场景） #

优化预期 #