含CASE与多表JOIN的MySQL慢查询性能优化及Eloquent实现咨询

现有SQL问题分析 #

原查询存在2个核心性能瓶颈：

• 冗余的两层嵌套子查询，数据库需要先全量计算所有cohort的起止时间、状态，再做过滤，浪费大量算力
• GROUP BY仅指定cohort_id不符合SQL标准，开启only_full_group_by的MySQL环境会直接报错，同时无对应索引支撑分组、关联操作，导致大量全表扫描和回表查询

第一步：原生SQL语句优化 #

直接合并冗余子查询，将状态过滤逻辑下推，减少参与计算的数据量，改写后SQL如下：

SELECT 
    schedules.cohort_id, 
    schedules.subject_id, 
    skills.name AS skills_name, 
    cohorts.name AS cohort_name,
    MAX(schedules.date) as end_date, 
    MIN(schedules.date) as start_date,
    CASE
        WHEN MAX(schedules.date) < '2021-08-26' THEN 'ended'
        WHEN MIN(schedules.date) > '2021-08-26' THEN 'upcoming'
        ELSE 'running'
    END AS course_status
FROM schedules
JOIN subjects ON schedules.subject_id = subjects.id
JOIN skills ON subjects.skill_id = skills.id
JOIN cohorts ON schedules.cohort_id = cohorts.id
GROUP BY schedules.cohort_id, schedules.subject_id, skills.name, cohorts.name
HAVING course_status IN ('running', 'upcoming')
ORDER BY schedules.cohort_id DESC
LIMIT 4

第二步：索引优化（立竿见影的降本方案） #

新增以下覆盖索引，避免回表查询，优化后本地耗时可降至100ms以内：

• schedules表新增联合索引：(cohort_id, subject_id, date)，覆盖分组、关联、聚合需要的所有字段
• subjects表新增索引：(skill_id)，优化和skills表的关联性能
• 主键字段默认自带索引，无需额外配置

第三步：Eloquent适配实现 #

可以直接用Laravel查询构造器实现上述优化逻辑，无需嵌套子查询，代码如下：

$referenceDate = '2021-08-26';
$courseList = DB::table('schedules')
    ->select(
        'schedules.cohort_id',
        'schedules.subject_id',
        'skills.name as skills_name',
        'cohorts.name as cohort_name',
        DB::raw('MAX(schedules.date) as end_date'),
        DB::raw('MIN(schedules.date) as start_date'),
        DB::raw("CASE
            WHEN MAX(schedules.date) < ? THEN 'ended'
            WHEN MIN(schedules.date) > ? THEN 'upcoming'
            ELSE 'running'
        END as course_status", [$referenceDate, $referenceDate])
    )
    ->join('subjects', 'schedules.subject_id', '=', 'subjects.id')
    ->join('skills', 'subjects.skill_id', '=', 'skills.id')
    ->join('cohorts', 'schedules.cohort_id', '=', 'cohorts.id')
    ->groupBy('schedules.cohort_id', 'schedules.subject_id', 'skills.name', 'cohorts.name')
    ->havingRaw("course_status IN ('running', 'upcoming')")
    ->orderByDesc('schedules.cohort_id')
    ->limit(4)
    ->get();

两个备选方案适配性评估 #

• 存储过程写入新字段：不推荐。业务逻辑耦合在数据库层，不利于后续迭代维护，Eloquent也无法灵活适配存储过程逻辑，且课程状态随时间动态变化，需要频繁触发更新逻辑，维护成本很高
• 每日快照表：适合查询QPS高、允许课程状态有1天以内延迟的场景。如果业务对实时性要求不高，可通过Laravel调度任务每日生成全量数据到专用快照表，直接查询快照表耗时可低至几毫秒，Eloquent可以直接创建对应快照表的模型，适配成本极低。

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