Sybase查询性能调优：优化指定嵌套子查询语句性能

首先修正原SQL的语法错误（res.scrub_id后缺少比较运算符=），原SQL逻辑应为：

select 
    (select count(scrub_result_id)
     from ac_scrub_result res
     where res.scrub_id = (select scrub_individual 
                           from ac_scrub_individual indv
                           where ((t.scrub_list_id!=0 and indv.scrub_list_id=t.scrub_list_id)
                                  or (indv.opportunity_id =t.opportunity_id))
                           and (res.isg_id is not null or res.key_account is not null)) as total_match
from #tempacv1 t

核心调优措施 #

1. 替换相关子查询为JOIN操作 #

原SQL的内层子查询属于相关子查询，会随外部临时表t的每一行重复执行，效率极低。改为JOIN关联查询，将多表逻辑合并为一次扫描：

select 
    t.scrub_list_id,
    t.opportunity_id,
    count(res.scrub_result_id) as total_match
from #tempacv1 t
left join ac_scrub_individual indv 
    on (t.scrub_list_id != 0 and indv.scrub_list_id = t.scrub_list_id)
    or (indv.opportunity_id = t.opportunity_id)
left join ac_scrub_result res 
    on res.scrub_id = indv.scrub_individual
    and (res.isg_id is not null or res.key_account is not null)
group by t.scrub_list_id, t.opportunity_id

注：若#tempacv1有唯一主键，可按主键分组，确保每个t行对应唯一统计值

2. 创建针对性索引 #

针对关联条件和过滤条件创建索引，避免全表扫描：

• ac_scrub_individual：创建复合索引idx_scrub_indv_list_opp(scrub_list_id, opportunity_id, scrub_individual)，覆盖关联字段与查询所需的scrub_individual
• ac_scrub_result：创建复合索引idx_scrub_res_scrubid_isg_key(scrub_id, isg_id, key_account, scrub_result_id)，覆盖关联字段、过滤条件和统计字段
• #tempacv1：若scrub_list_id和opportunity_id为高频查询字段，创建非聚集索引idx_tempacv1_list_opp(scrub_list_id, opportunity_id)

3. 拆分OR条件优化执行逻辑 #

原SQL的OR条件易导致索引失效，可拆分为两个独立查询后用UNION ALL合并（需确保两个条件无交集）：

select 
    t.scrub_list_id, t.opportunity_id,
    count(res.scrub_result_id) as total_match
from #tempacv1 t
left join ac_scrub_individual indv 
    on t.scrub_list_id != 0 and indv.scrub_list_id = t.scrub_list_id
left join ac_scrub_result res 
    on res.scrub_id = indv.scrub_individual
    and (res.isg_id is not null or res.key_account is not null)
group by t.scrub_list_id, t.opportunity_id

union all

select 
    t.scrub_list_id, t.opportunity_id,
    count(res.scrub_result_id) as total_match
from #tempacv1 t
left join ac_scrub_individual indv 
    on indv.opportunity_id = t.opportunity_id
    and (t.scrub_list_id = 0 or t.scrub_list_id is null)
left join ac_scrub_result res 
    on res.scrub_id = indv.scrub_individual
    and (res.isg_id is not null or res.key_account is not null)
group by t.scrub_list_id, t.opportunity_id

4. 过滤临时表无效数据 #

若#tempacv1数据量较大，先过滤掉scrub_list_id和opportunity_id都为空的无效行，减少后续关联的行数：

with filtered_temp as (
    select scrub_list_id, opportunity_id
    from #tempacv1
    where scrub_list_id is not null or opportunity_id is not null
)
select 
    count(res.scrub_result_id) as total_match
from filtered_temp t
left join ac_scrub_individual indv 
    on (t.scrub_list_id != 0 and indv.scrub_list_id = t.scrub_list_id)
    or (indv.opportunity_id = t.opportunity_id)
left join ac_scrub_result res 
    on res.scrub_id = indv.scrub_individual
    and (res.isg_id is not null or res.key_account is not null)
group by t.scrub_list_id, t.opportunity_id

5. 验证执行计划 #

调优后通过Sybase的set showplan on命令查看执行计划，确认是否用到创建的索引，是否存在全表扫描、嵌套循环等低效操作，再针对性调整。

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