如何优化按优先级取单条用户电话记录的慢SQL查询

• 原查询采用关联子查询逐行匹配的逻辑，每一条telephone_current表的记录都会触发一次子查询扫描，当表数据量较大时，会产生指数级的IO开销，是耗时过长的核心原因
• 额外的DISTINCT关键字增加了不必要的排序去重开销，实际只要保证每个用户仅返回一条电话记录，完全可以去掉该关键字
• 原查询中电话类型排序没有显式定义优先级，依赖字典序隐含排序的逻辑不稳定，不符合需求中"CA优先，其次MA/PR任意"的明确规则

用ROW_NUMBER()窗口函数一次性完成每个用户的电话优先级排序和筛选，仅需扫描一次telephone_current表，避免了高成本的自连接操作：

SELECT 
    igp.isu_id PersonnelNumber
    , igp.preferred_first_name FirstName
    , igp.current_last_name LastName
    , NULL Title
    , igp.current_mi MiddleInitial
    , pd.email_preferred_address
    , tc.phone_number_combined
    , igp.isu_username networkID
    , '0' GroupID
    , e.home_organization_desc GroupName
    , CASE
        WHEN substr(e.employee_class,1,1) in ( 'N', 'C') THEN 'staff'
        WHEN substr(e.employee_class,1,1) = 'F' THEN 'faculty'
        ELSE 'other'
    END GroupType
FROM isu_general_person igp
JOIN person_detail pd ON igp.person_uid = pd.person_uid
-- 用窗口函数预筛选每个用户优先级最高的电话
JOIN (
    SELECT 
        entity_uid,
        phone_number_combined,
        ROW_NUMBER() OVER (
            PARTITION BY entity_uid 
            ORDER BY CASE 
                WHEN phone_type = 'CA' THEN 1 
                WHEN phone_type IN ('MA','PR') THEN 2 
                ELSE 3 
            END
        ) AS rn
    FROM telephone_current
) tc ON igp.person_uid = tc.entity_uid AND tc.rn = 1
LEFT JOIN employee e ON igp.person_uid = e.person_uid
WHERE 1=1
AND e.employee_status = 'A'
AND substr(e.employee_class,1,1) in ( 'N', 'C', 'F')
AND igp.isu_username IS NOT NULL;

• 给telephone_current表创建联合索引：(entity_uid, phone_type, phone_number_combined)，覆盖窗口函数的分组、排序和取值逻辑，避免回表查询
• 给employee表的employee_status、employee_class字段创建索引，加快WHERE条件的过滤速度
• 如果telephone_current表存在大量非MA/PR/CA类型的无效记录，可以在子查询中提前加过滤条件WHERE phone_type IN ('CA','MA','PR')，减少窗口函数的处理数据量

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