MySQL关联查询仅匹配单ID避免全表扫描的优化方法咨询

优化方案 #

核心逻辑是先查询到最新的job_run记录，再仅针对该条记录的id统计关联的records数量，避免全表扫描关联。

写法1：子查询统计关联数

SELECT
    run.id,
    run.start_time,
    (SELECT COUNT(*) FROM records WHERE job_run_id = run.id) AS last_run_record_count
FROM job_runs run
ORDER BY run.id DESC
LIMIT 1

写法2：派生表先取最新记录再关联

SELECT
    latest_run.id,
    latest_run.start_time,
    COUNT(record.id) AS last_run_record_count
FROM (
    SELECT id, start_time 
    FROM job_runs 
    ORDER BY id DESC 
    LIMIT 1
) latest_run
LEFT JOIN records record ON record.job_run_id = latest_run.id

配套索引优化 #

要达到最优性能，需要提前给records表的job_run_id字段建立普通索引，统计数量时可以直接走索引返回结果，不需要扫描records的全表数据。

原写法潜在问题补充 #

你原本的SQL没有添加GROUP BY子句，在MySQL开启ONLY_FULL_GROUP_BY严格模式时会直接执行报错，上述优化后的写法可以同时规避这个问题。

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