如何用CTE重写Join查询并解决数据不匹配与性能优化问题

一、修复CTE查询的数据不匹配问题 #

你的CTE查询存在多处逻辑错误，导致结果与原查询不符，修正后的正确CTE写法如下：

with cte_s as (
    select asi
    from ue_summary.summary_funnel_1066 s
    where s.seq_no = 1
      and s.created_at between '2022-09-27 00:00:00' and '2022-10-27 00:00:00'
),
cte_ut as (
    select id
    from user_tasks ut
    where ut.is_ragetap = 1
)
select count(1) as rage_tap
from cte_s
inner join user_tasks_metadata utm on utm.asi = cte_s.asi
inner join cte_ut on cte_ut.id = utm.user_task_id;

原CTE的错误点说明： #

• cte1中未给summary_funnel_1066表指定别名s，却使用了s.seq_no，导致语法错误
• cte2中count(*)是统计所有is_ragetap=1的user_tasks记录数，而非关联后的有效匹配数，逻辑完全偏离原查询
• 最后关联时使用未定义的别名b，且错误地将cte1的计数与cte2的计数直接拼接，不符合原查询统计关联后结果的需求

二、查询性能优化方案 #

1. 调整查询执行顺序（从过滤条件更严格的表开始） #

原执行计划从user_tasks表开始扫描841万行，建议改为从summary_funnel_1066表开始，利用时间范围和seq_no=1的过滤先缩小数据集，再关联其他表：

select count(1) as rage_tap
from ue_summary.summary_funnel_1066 s
inner join user_tasks_metadata utm on utm.asi = s.asi
inner join user_tasks ut on ut.id = utm.user_task_id
where s.seq_no = 1
  and s.created_at between '2022-09-27 00:00:00' and '2022-10-27 00:00:00'
  and ut.is_ragetap = 1;

2. 创建针对性复合索引 #

• summary_funnel_1066表：现有idx_seq_created_asi(seq_no,created_at,asi)已经是最优覆盖索引，能直接过滤并获取关联所需的asi字段，无需回表
• user_tasks_metadata表：创建复合索引idx_asi_usertaskid(asi,user_task_id)，替换现有冗余的单字段索引，关联时能通过asi快速找到对应的user_task_id，无需回表查询

  CREATE INDEX idx_asi_usertaskid ON user_tasks_metadata(asi, user_task_id);
  

• user_tasks表：将现有idx_ir(is_ragetap)升级为覆盖索引idx_ir_id(is_ragetap,id)，查询is_ragetap=1时能直接获取id字段，避免回表

  CREATE INDEX idx_ir_id ON user_tasks(is_ragetap, id);
  

3. 优化统计逻辑（避免重复计数） #

如果summary_funnel_1066表中存在同一asi对应多条满足条件的记录，或者user_tasks_metadata中同一asi对应多个user_task_id，可使用count(distinct s.asi)确保计数准确性（根据业务需求选择）：

select count(distinct s.asi) as rage_tap
from ue_summary.summary_funnel_1066 s
inner join user_tasks_metadata utm on utm.asi = s.asi
inner join user_tasks ut on ut.id = utm.user_task_id
where s.seq_no = 1
  and s.created_at between '2022-09-27 00:00:00' and '2022-10-27 00:00:00'
  and ut.is_ragetap = 1;

4. 检查数据分布与索引有效性 #

• 定期分析表统计信息，让优化器生成更优执行计划：

  ANALYZE TABLE ue_summary.summary_funnel_1066, user_tasks_metadata, user_tasks;
  

• 清理冗余索引，比如user_tasks_metadata表中重复的asi索引，减少维护成本

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