MySQL左连接子查询性能优化问询：如何降低全表扫描耗时？

优化后的SQL写法 #

写法一：先过滤再关联聚合 #

select 
    A.reference,
    A.project_name,
    group_concat(C.state_name) as allstate
from A
left join B on B.id_A = A.id
left join C on C.state_id = B.state_id
where A.year_apply = '2023'
group by A.id, A.reference, A.project_name

写法二：提前缩小B表扫描范围 #

select 
    A.reference,
    A.project_name,
    D.allstate
from A
left join (
    select 
        B.id_A as id,
        group_concat(C.state_name) as allstate
    from B
    inner join (select id from A where year_apply = '2023') as A_2023 
        on B.id_A = A_2023.id
    left join C on C.state_id = B.state_id
    group by B.id_A
) as D on D.id = A.id
where A.year_apply = '2023'

索引优化建议 #

• 给表A创建索引：CREATE INDEX idx_A_year_apply ON A(year_apply);，快速筛选2023年的项目数据
• 给表B创建联合索引：CREATE INDEX idx_B_idA_stateId ON B(id_A, state_id);，同时满足关联A表和C表的查询需求，避免全表扫描
• 确保表C的state_id字段为主键或有索引，若无则添加：CREATE INDEX idx_C_stateId ON C(state_id);，加速状态名称的关联查询

优化说明 #

1. 原查询的子查询会全量扫描表B的59448条记录，优化后仅处理2023年9265个项目对应的B表记录，大幅减少数据处理量
2. 写法一将过滤、关联、聚合合并，让数据库优化器更易生成高效执行计划；写法二通过子查询提前锁定需要处理的B表范围，逻辑更清晰
3. 索引的核心作用是让数据库快速定位目标数据，避免全表扫描，是提升查询速度的关键

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