关联表排序查询性能优化求助：按job_events.created_at排序过慢

核心问题分析 #

当前查询的瓶颈在于两点：

1. 多次嵌套的SELECT MAX(id)子查询，导致重复扫描job_events表，额外增加IO与计算开销；
2. 按job_events.created_at排序时，MySQL需先完成所有JOIN操作，再对全量符合条件的数据执行排序（大概率触发filesort），数据量较大时耗时陡增。

具体优化步骤 #

1. 调整索引结构 #

现有(type, jobId)索引无法覆盖查询中「按jobId取最大id」和「获取created_at」的需求，需补充以下联合索引：

• 给job_events创建(jobId, id, created_at)联合索引：快速获取每个job的最新事件（MAX(id)），同时直接从索引读取created_at，避免回表；
• 给job_events创建(type, jobId, id)联合索引：加速Created By和Job Completed/Complete类型事件的MAX(id)查询。

2. 改写子查询为预聚合JOIN #

将重复的SELECT MAX(id)子查询改为一次性预聚合，减少重复扫描：

WITH latest_main_events AS (
    SELECT jobId, MAX(id) AS max_id
    FROM job_events
    GROUP BY jobId
),
latest_created_by AS (
    SELECT jobId, MAX(id) AS max_id
    FROM job_events
    WHERE type = 'Created By'
    GROUP BY jobId
),
latest_completed AS (
    SELECT jobId, MAX(id) AS max_id
    FROM job_events
    WHERE type IN ('Job Completed', 'Job Complete')
    GROUP BY jobId
)
SELECT *
FROM jobs
LEFT JOIN job_events main_event 
    ON main_event.jobId = jobs.id 
    AND main_event.id = (SELECT max_id FROM latest_main_events WHERE jobId = jobs.id)
LEFT JOIN job_events created_by_events 
    ON created_by_events.jobId = jobs.id 
    AND created_by_events.id = (SELECT max_id FROM latest_created_by WHERE jobId = jobs.id)
LEFT JOIN job_events completed_job_events 
    ON completed_job_events.jobId = jobs.id 
    AND completed_job_events.id = (SELECT max_id FROM latest_completed WHERE jobId = jobs.id)
WHERE jobs.status IN (5, 6)
ORDER BY main_event.created_at
LIMIT 101;

3. 优先排序再关联（极致优化） #

如果上述方案仍不达标，可先筛选出需要排序的Top 101条数据，再关联其他表，大幅减少排序的数据量：

SELECT *
FROM (
    -- 先获取按created_at排序的Top101符合条件的job及对应最新事件
    SELECT 
        jobs.id AS job_id,
        main_event.created_at,
        main_event.id AS main_event_id
    FROM jobs
    INNER JOIN (
        SELECT jobId, MAX(id) AS max_id
        FROM job_events
        GROUP BY jobId
    ) latest_main 
        ON latest_main.jobId = jobs.id
    INNER JOIN job_events main_event 
        ON main_event.id = latest_main.max_id
    WHERE jobs.status IN (5, 6)
    ORDER BY main_event.created_at
    LIMIT 101
) sorted_job_list
-- 关联完整jobs数据
LEFT JOIN jobs ON jobs.id = sorted_job_list.job_id
-- 关联主事件详情
LEFT JOIN job_events main_event ON main_event.id = sorted_job_list.main_event_id
-- 关联Created By事件
LEFT JOIN (
    SELECT je.*
    FROM job_events je
    INNER JOIN (
        SELECT jobId, MAX(id) AS max_id
        FROM job_events
        WHERE type = 'Created By'
        GROUP BY jobId
    ) latest_cb ON je.id = latest_cb.max_id
) created_by_events ON created_by_events.jobId = sorted_job_list.job_id
-- 关联Completed事件
LEFT JOIN (
    SELECT je.*
    FROM job_events je
    INNER JOIN (
        SELECT jobId, MAX(id) AS max_id
        FROM job_events
        WHERE type IN ('Job Completed', 'Job Complete')
        GROUP BY jobId
    ) latest_comp ON je.id = latest_comp.max_id
) completed_job_events ON completed_job_events.jobId = sorted_job_list.job_id;

验证优化效果 #

执行优化后的SQL后，查看执行计划：

• 确认Using index出现在job_events的查询中，说明索引被有效利用；
• 确认排序阶段不再出现Using filesort，或仅对极小数据量排序。

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