MySQL慢查询排查：索引齐全仍耗时超30秒取10条数据

以下查询仅获取10条数据却耗时超30秒，已确认涉及字段均已建立索引，users表数据量不足10万条：

SELECT
    `u`.*,`f`.`functional_area`,`f`.`is_active` AS `is_ft_active`,`pe`.`title`,`pe`.`company`,
    (
        SELECT
            cv_file
        FROM
            profile_cvs
        WHERE
            profile_cvs.user_id = u.id AND profile_cvs.is_active = 1 AND deleted_at IS NULL
        ORDER BY
            profile_cvs.id
        DESC
        LIMIT 1
    ) AS cv_file
FROM
    `users` AS `u`
LEFT JOIN `functional_areas` AS `f` ON `f`.`id` = `u`.`functional_area_id`
LEFT JOIN `profile_experiences` AS `pe` ON `pe`.`user_id` = `u`.`id`
LEFT JOIN `profile_cvs` AS `pc` ON `pc`.`user_id` = `u`.`id`
LEFT JOIN `profile_summaries` AS `ps` ON `ps`.`user_id` = `u`.`id`
LEFT JOIN `profile_educations` AS `ped` ON `ped`.`user_id` = `u`.`id`
LEFT JOIN `profile_skills` AS `psk` ON `psk`.`user_id` = `u`.`id`
LEFT JOIN `job_skills` AS `js` ON `js`.`id` = `psk`.`job_skill_id`
LEFT JOIN `admins` AS `ad` ON `ad`.`id` = `u`.`created_by`
WHERE
    (`js`.`job_skill` IN("HVAC")) OR(`f`.`functional_area` LIKE "%HVAC %") OR(`u`.`search` LIKE "%HVAC%") OR(`ps`.`summary` LIKE "% HVAC%") OR(`ped`.`degree_title` LIKE "HVAC")
GROUP BY
    `u`.`id`
ORDER BY  
    CASE WHEN js.job_skill LIKE '%HVAC %' THEN 1 ELSE 0 END + 
    CASE WHEN f.functional_area LIKE '%HVAC %' THEN 1 ELSE 0 END + 
    CASE WHEN ps.summary LIKE '% HVAC%' THEN 1 ELSE 0 END + 
    CASE WHEN u.search LIKE '% HVAC %' THEN 1 ELSE 0 END DESC,
    `u`.`is_blocked` ASC,
    `js`.`job_skill` DESC,
    `f`.`functional_area` DESC,
    `u`.`search` DESC,
    `u`.`id` DESC
LIMIT 10;

核心耗时原因拆解 #

• 多表LEFT JOIN引发数据膨胀：查询对users关联了7张子表，LEFT JOIN会将主表每条记录与子表匹配的所有记录展开。比如一个用户有3条工作经验、2条简历，该用户记录会被扩展为6条重复数据。后续GROUP BY u.id需要合并这些重复记录，实际处理的数据量远大于users表的10万条，直接拉高计算开销。
• OR条件与模糊匹配失效索引：虽然字段都建了索引，但跨多表的OR条件让优化器无法选择最优索引，只能走低效的索引合并或全表扫描。另外，%HVAC %这类前缀模糊匹配完全无法利用B-tree索引，会触发对应表的全表扫描，进一步拖慢查询。
• 关联子查询重复执行：SELECT列表中的cv_file子查询会针对每个用户单独执行，次数等于GROUP BY后的用户数。如果中间结果集有上万条用户记录，就会触发上万次子查询，累积耗时极高。
• ORDER BY动态计算无索引可用：ORDER BY中的权重是通过CASE表达式动态计算的，无法利用索引，必须对所有符合条件的膨胀后数据逐一计算权重，再执行排序操作（大概率触发filesort），占用大量CPU和磁盘IO资源。
• GROUP BY去重开销过大：JOIN导致的数据膨胀让GROUP BY u.id需要对大量重复记录去重聚合，若没有合适的覆盖索引，还需频繁回表读取数据，进一步增加耗时。

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