MySQL查询性能优化求助：多子查询执行耗时超1分钟

原查询的核心问题 #

1. 三个相关子查询会对occupations的每一行（共1033行）各执行一次，总计3099次查询，重复计算严重拖慢速度。
2. 子查询内的ORDER BY s.rate DESC LIMIT 15如果没有对应索引，每次都要全表扫描+排序，occupation_skill_rate有3万多行，单次排序成本就很高。
3. 第三个子查询的别名重复设为knowledge，属于语法错误，会导致字段覆盖，先修正为abilities。

优化步骤 #

1. 添加针对性索引 #

给关联表创建复合索引，让过滤、排序、关联一步到位：

-- 给技能关联表加索引
CREATE INDEX idx_occupation_skill_rate ON occupation_skill_rate (occupation_id, rate DESC, hard_skill_id);

-- 给知识关联表加索引
CREATE INDEX idx_occupation_knowledge_rate ON occupation_knowledge_rate (occupation_id, rate DESC, knowledge_id);

-- 给能力关联表加索引
CREATE INDEX idx_occupation_abilities_rate ON occupation_abilities_rate (occupation_id, rate DESC, ability_id);

这些索引可以让数据库直接通过occupation_id过滤数据，同时按rate降序排列取前15条，不用额外排序，还能直接拿到关联字段去匹配主表。

2. 用预聚合JOIN替代相关子查询 #

先提前计算每个职业对应的TOP15技能、知识、能力，再和occupations表关联，避免重复查询：

SELECT
    t.id,
    t.name,
    t.description,
    s.skills,
    k.knowledge,
    a.abilities
FROM
    occupations t
LEFT JOIN (
    SELECT
        s.occupation_id,
        GROUP_CONCAT(CONCAT(hs.name, '|', s.rate)) AS skills
    FROM (
        -- 先取每个职业的TOP15技能
        SELECT occupation_id, hard_skill_id, rate
        FROM occupation_skill_rate
        ORDER BY occupation_id, rate DESC
        LIMIT 1033 * 15 -- 按职业数*15预估，确保取全所有职业的TOP15
    ) s
    INNER JOIN hard_skills hs ON s.hard_skill_id = hs.id
    GROUP BY s.occupation_id
) s ON t.id = s.occupation_id
LEFT JOIN (
    SELECT
        s.occupation_id,
        GROUP_CONCAT(CONCAT(hs.name, '|', s.rate)) AS knowledge
    FROM (
        SELECT occupation_id, knowledge_id, rate
        FROM occupation_knowledge_rate
        ORDER BY occupation_id, rate DESC
        LIMIT 1033 * 15
    ) s
    INNER JOIN knowledge hs ON s.knowledge_id = hs.id
    GROUP BY s.occupation_id
) k ON t.id = k.occupation_id
LEFT JOIN (
    SELECT
        s.occupation_id,
        GROUP_CONCAT(CONCAT(hs.name, '|', s.rate)) AS abilities
    FROM (
        SELECT occupation_id, ability_id, rate
        FROM occupation_abilities_rate
        ORDER BY occupation_id, rate DESC
        LIMIT 1033 * 15
    ) s
    INNER JOIN ability hs ON s.ability_id = hs.id
    GROUP BY s.occupation_id
) a ON t.id = a.occupation_id;

3. 可选优化：调整GROUP_CONCAT参数 #

如果拼接的字符串过长，可能触发截断，可临时调整group_concat_max_len参数：

SET SESSION group_concat_max_len = 10240; -- 根据实际需求设置合适长度

优化原理 #

• 预聚合只对每个关联表执行一次TOP15筛选+聚合，替代原查询的3099次重复查询，大幅减少计算量。
• 复合索引让数据库直接通过索引完成过滤、排序，避免全表扫描和文件排序。
• LEFT JOIN确保即使职业没有对应技能/知识/能力，也能正常返回数据，和原查询逻辑一致。

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