MySQL 5.7双分组+Max条件查询性能劣化的优化方案求助

问题根因 #

MySQL 5.7优化器对派生表的处理逻辑与5.6不同，原查询中派生表pv_temp会被物化为无索引的临时表，导致关联时全表扫描，再加上外层GROUP BY触发临时表和文件排序，最终造成查询性能骤降。

优化方案 #

方案1：两层子查询消除外层GROUP BY #

通过将版本最大值的计算移至子查询，避免外层分组操作：

SELECT 
    pv1.program_id,
    (SELECT MAX(version) 
     FROM program_versions pv2 
     WHERE pv2.program_id = pv1.program_id 
       AND pv2.start_date = pv1.max_start_date
       AND pv2.start_date <= NOW()) AS version,
    pv1.max_start_date AS start_date
FROM (
    SELECT program_id, MAX(start_date) AS max_start_date
    FROM program_versions
    WHERE start_date <= NOW()
    GROUP BY program_id
) pv1;

该方案利用idx_pi_sd_vs索引（program_id, start_date, version）快速定位每个program_id对应最新start_date的最大版本，避免了外层分组的临时表和文件排序开销。

方案2：自连接直接筛选目标行 #

通过自连接排除所有比当前行更新的记录，直接获取每个program_id下最新start_date且版本最高的行：

SELECT pv.program_id, pv.version, pv.start_date
FROM program_versions pv
LEFT JOIN program_versions pv2
ON pv.program_id = pv2.program_id
AND (
    pv.start_date < pv2.start_date 
    OR (pv.start_date = pv2.start_date AND pv.version < pv2.version)
)
WHERE pv.start_date <= NOW()
AND pv2.id IS NULL;

此方案依赖idx_pi_sd_vs索引高效完成自连接匹配，无需分组操作，结果直接符合需求。

方案3：带索引的临时表优化关联 #

若之前尝试临时表时未添加索引，可建立复合索引加速关联：

-- 创建带复合索引的临时表
CREATE TEMPORARY TABLE pv_temp (
    program_id INT,
    recent_date DATETIME,
    PRIMARY KEY (program_id, recent_date)
) ENGINE=InnoDB;

-- 插入最新日期数据
INSERT INTO pv_temp
SELECT program_id, MAX(start_date) recent_date
FROM program_versions
WHERE start_date <= NOW()
GROUP BY program_id;

-- 关联查询并获取最大版本
SELECT pv.program_id, MAX(pv.version) AS version, pv.start_date
FROM program_versions pv
JOIN pv_temp 
    ON pv.program_id = pv_temp.program_id 
    AND pv.start_date = pv_temp.recent_date
GROUP BY pv.program_id, pv.start_date;

临时表的复合索引(program_id, recent_date)可将关联操作的性能大幅提升，避免全表扫描。

索引建议 #

当前已存在的idx_pi_sd_vs（program_id, start_date, version）是覆盖索引，能满足上述所有优化方案的查询需求，无需额外创建新索引。建议确保该索引状态正常，且表统计信息已更新（已执行ANALYZE TABLE，符合要求）。

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