PostgreSQL 14.6：按校获取指定数量学生最新身份证高效查询

核心需求拆解 #

• 针对指定的多所学校，每校最多返回100个最新加入学生的最新身份证
• 保证查询性能，适配每校200~20000学生的规模

方案一：先筛选目标学生，再取对应最新身份证（推荐） #

利用窗口函数先过滤出每校前100名最新加入的学生（依赖school_students.id自增特性，id越大代表加入时间越晚），再关联身份证表取每个学生的最新身份证：

WITH top_students AS (
    SELECT 
        ss.id AS school_student_id,
        ss.school_id
    FROM school_students ss
    WHERE ss.school_id IN ('1', '2', '3') -- 替换为应用层传入的school_ids列表，注意是字符串类型
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ss.school_id ORDER BY ss.id DESC) <= 100
)
SELECT MAX(c.id) AS id_card_id
FROM id_cards c
JOIN top_students ts ON c.school_student_id = ts.school_student_id
GROUP BY ts.school_id, ts.school_student_id;

注：如果使用PostgreSQL 12及以下版本，不支持QUALIFY语法，可改用子查询实现：

WITH top_students AS (
    SELECT 
        ss.id AS school_student_id,
        ss.school_id
    FROM (
        SELECT 
            ss.*,
            ROW_NUMBER() OVER (PARTITION BY ss.school_id ORDER BY ss.id DESC) AS rn
        FROM school_students ss
        WHERE ss.school_id IN ('1', '2', '3')
    ) ss
    WHERE ss.rn <= 100
)
SELECT MAX(c.id) AS id_card_id
FROM id_cards c
JOIN top_students ts ON c.school_student_id = ts.school_student_id
GROUP BY ts.school_id, ts.school_student_id;

方案二：先取所有学生最新身份证，再按学校筛选前100 #

先计算所有目标学校学生的最新身份证，再通过窗口函数限制每校返回数量：

WITH student_latest_idcard AS (
    SELECT 
        ss.school_id,
        ss.id AS school_student_id,
        MAX(c.id) AS id_card_id
    FROM id_cards c
    JOIN school_students ss ON c.school_student_id = ss.id
    WHERE ss.school_id IN ('1', '2', '3')
    GROUP BY ss.school_id, ss.id
),
top_idcards AS (
    SELECT id_card_id
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY school_id ORDER BY school_student_id DESC) AS rn
        FROM student_latest_idcard
    ) t
    WHERE rn <= 100
)
SELECT id_card_id FROM top_idcards;

性能优化：添加针对性索引 #

为了让窗口函数和关联查询高效执行，建议添加以下两个索引：

1. 加速school_students按学校分组取最新学生的查询：

CREATE INDEX idx_school_students_school_id_id_desc ON school_students(school_id, id DESC);

这个索引可以让数据库直接按school_id分组，按id降序快速取前100条，避免全表扫描和排序。

2. 加速id_cards获取每个学生最新身份证的查询：

CREATE INDEX idx_id_cards_school_student_id_id_desc ON id_cards(school_student_id, id DESC);

通过这个索引，数据库可以直接定位到每个school_student_id对应的最大id（最新身份证），不需要扫描该学生的所有身份证记录。

性能对比 #

• 方案一优先筛选出少量目标学生（每校100），再关联身份证表，处理的数据量更小，在学校学生数较多时性能更优。
• 方案二先处理所有目标学校的学生数据，再筛选，适合学校学生数较少的场景，但整体性能略逊于方案一。

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