Group concat查询性能优化：耗时22秒的多表关联SQL如何提升性能

该查询耗时过长的核心原因是多表左联产生笛卡尔积放大了数据集，且全量数据分组排序后才取前25条，大部分运算资源被浪费在无关数据上，可通过以下方案优化：

1. 优先过滤分页再关联，大幅缩小运算数据集（收益最高） #

先从主表筛选出符合条件的25条目标数据，再关联其他表拉取附属信息，避免全表关联所有导师的关联数据，修改后SQL参考：

SELECT 
    TP.*, 
    GROUP_CONCAT(DISTINCT TS.`subject_name`) AS subjects, 
    GROUP_CONCAT(DISTINCT TC.`class_name`) AS classes, 
    GROUP_CONCAT(DISTINCT TT.`tution_name`) AS tution_type, 
    GROUP_CONCAT(DISTINCT TL.`name`) AS locations 
FROM (
    -- 先筛选符合条件的前25条导师数据，把数据集从全表缩小到25行
    SELECT * FROM `tutor_profile` 
    WHERE `status` = 1 
    ORDER BY `date_added` DESC LIMIT 0, 25
) TP
LEFT JOIN `tutor_to_subject` TTS ON TP.`tutor_id`=TTS.`tutor` 
LEFT JOIN `tutor_subjects` TS ON TS.`subject_id`=TTS.`subject` 
LEFT JOIN `tutor_to_class` TTC ON TP.`tutor_id`=TTC.`tutor` 
LEFT JOIN `tutor_classes` TC ON TC.`class_id`=TTC.`class` 
LEFT JOIN `tutor_to_tution_type` TTTT ON TP.`tutor_id`=TTTT.`tutor` 
LEFT JOIN `tution_types` TT ON TT.`tution_id`=TTTT.`tution_type` 
LEFT JOIN `tutor_to_locality` TTL ON TP.`tutor_id`=TTL.`tutor` 
LEFT JOIN `tutor_locality` TL ON TL.`id`=TTL.`locality` 
GROUP BY TP.`tutor_id`

常规数据量下该改动可直接把耗时降到百毫秒级别。

2. 补充必要索引，加速过滤和关联 #

所有过滤、排序、关联字段都需要加索引：

• tutor_profile表添加联合索引：idx_status_date(tutor_id, status, date_added)，覆盖主表的过滤、排序、关联查询需求
• 所有中间关联表（tutor_to_subject/tutor_to_class/tutor_to_tution_type/tutor_to_locality）添加联合索引，比如tutor_to_subject加idx_tutor_subject(tutor, subject)，其余中间表同理，给tutor字段和对应关联目标ID字段加联合索引
• 字典表（tutor_subjects/tutor_classes/tution_types/tutor_locality）的主键ID默认是索引，未创建的补建即可

3. 可选进阶优化：提前聚合关联表避免笛卡尔积 #

如果前两步优化后仍有性能问题，可以把每个关联维度单独聚合后再和主表关联，彻底避免多表关联产生的行放大问题，参考SQL：

SELECT 
    TP.*,
    IFNULL(T.subjects, '') AS subjects,
    IFNULL(C.classes, '') AS classes,
    IFNULL(TT.tution_type, '') AS tution_type,
    IFNULL(L.locations, '') AS locations
FROM (
    SELECT * FROM `tutor_profile` 
    WHERE `status` = 1 
    ORDER BY `date_added` DESC LIMIT 0, 25
) TP
LEFT JOIN (
    SELECT TTS.tutor, GROUP_CONCAT(DISTINCT TS.subject_name) AS subjects
    FROM tutor_to_subject TTS
    JOIN tutor_subjects TS ON TS.subject_id = TTS.subject
    GROUP BY TTS.tutor
) T ON TP.tutor_id = T.tutor
LEFT JOIN (
    SELECT TTC.tutor, GROUP_CONCAT(DISTINCT TC.class_name) AS classes
    FROM tutor_to_class TTC
    JOIN tutor_classes TC ON TC.class_id = TTC.class
    GROUP BY TTC.tutor
) C ON TP.tutor_id = C.tutor
LEFT JOIN (
    SELECT TTTT.tutor, GROUP_CONCAT(DISTINCT TT.tution_name) AS tution_type
    FROM tutor_to_tution_type TTTT
    JOIN tution_types TT ON TT.tution_id = TTTT.tution_type
    GROUP BY TTTT.tutor
) TT ON TP.tutor_id = TT.tutor
LEFT JOIN (
    SELECT TTL.tutor, GROUP_CONCAT(DISTINCT TL.name) AS locations
    FROM tutor_to_locality TTL
    JOIN tutor_locality TL ON TL.id = TTL.locality
    GROUP BY TTL.tutor
) L ON TP.tutor_id = L.tutor

该写法适合关联维度多、每个维度关联数据量大的场景。

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