含多JOIN、子查询及MIN/MAX的SELECT语句优化求助

看来你的查询因为多次全表扫描history_gt拖慢了速度，我来帮你拆解问题并给出优化方案：

先说说原查询的核心问题 #

1. 冗余的自连接：LEFT JOIN survey AS s ON survey.id = s.id完全没必要，这相当于把survey表和自己做了个无意义的连接，直接用survey表即可。
2. 三次重复扫描大表：你写了三个子查询，每个都单独遍历一次history_gt（从EXPLAIN看这表有百万级数据），这是速度慢的头号元凶。
3. 多余的GROUP BY：原查询里的GROUP BY s.request_id没有实际聚合逻辑，只是可能想去重，但如果survey里每个request_id对应唯一行，这一步完全是多余的，还会触发临时表和文件排序（EXPLAIN里的Using temporary; Using filesort就是这个原因）。

优化方案1：条件聚合（兼容MySQL 5.7+） #

我们可以把三次子查询合并成一次对history_gt的扫描，用条件聚合同时拿到所有需要的最值关联值：

EXPLAIN SELECT 
    s.ticket_number, 
    s.request_id, 
    j.owner_perception_min,
    j.tl_perception_min,
    j.tl_perception_max,
    j.owner_perception_max
FROM survey s
LEFT JOIN (
    SELECT 
        request_id,
        -- 取owner_perception非空的最小id对应的字段值
        SUBSTRING_INDEX(MIN(CASE WHEN owner_perception != '' THEN CONCAT(id, '|', owner_perception) END), '|', -1) AS owner_perception_min,
        -- 取tl_perception非空的最小id对应的字段值
        SUBSTRING_INDEX(MIN(CASE WHEN tl_perception != '' THEN CONCAT(id, '|', tl_perception) END), '|', -1) AS tl_perception_min,
        -- 取最大id对应的tl_perception
        SUBSTRING_INDEX(MAX(CONCAT(id, '|', tl_perception)), '|', -1) AS tl_perception_max,
        -- 取最大id对应的owner_perception
        SUBSTRING_INDEX(MAX(CONCAT(id, '|', owner_perception)), '|', -1) AS owner_perception_max
    FROM history_gt
    GROUP BY request_id
) j ON s.request_id = j.request_id
ORDER BY s.id ASC
LIMIT 50;

原理：用CONCAT(id, '|', 字段值)把id和字段拼接，利用MIN/MAX按id排序的特性，再拆分出对应最值id的字段内容，全程只扫描一次history_gt。

优化方案2：窗口函数（推荐，MySQL 8.0+） #

如果你的MySQL版本是8.0及以上，窗口函数会让逻辑更清晰，性能也更优：

EXPLAIN SELECT DISTINCT
    s.ticket_number, 
    s.request_id,
    -- 筛选owner_perception非空的行，取最小id对应的字段值
    FIRST_VALUE(h.owner_perception) OVER (
        PARTITION BY h.request_id 
        ORDER BY CASE WHEN h.owner_perception != '' THEN h.id ELSE 999999999 END
    ) AS owner_perception_min,
    -- 筛选tl_perception非空的行，取最小id对应的字段值
    FIRST_VALUE(h.tl_perception) OVER (
        PARTITION BY h.request_id 
        ORDER BY CASE WHEN h.tl_perception != '' THEN h.id ELSE 999999999 END
    ) AS tl_perception_min,
    -- 取最大id对应的tl_perception
    FIRST_VALUE(h.tl_perception) OVER (
        PARTITION BY h.request_id 
        ORDER BY h.id DESC
    ) AS tl_perception_max,
    -- 取最大id对应的owner_perception
    FIRST_VALUE(h.owner_perception) OVER (
        PARTITION BY h.request_id 
        ORDER BY h.id DESC
    ) AS owner_perception_max
FROM survey s
LEFT JOIN history_gt h ON s.request_id = h.request_id
ORDER BY s.id ASC
LIMIT 50;

原理：用PARTITION BY request_id分组，ORDER BY按我们需要的规则排序（非空字段的最小id、最大id），然后用FIRST_VALUE直接拿到每组的目标字段值，同样只扫描一次history_gt。

原EXPLAIN结果解读 #

你贴的EXPLAIN里几个关键问题：

• 三个派生表（derived2/derived3/derived4）都在全表扫描history_gt，其中两个扫描了100多万行，这就是慢的核心原因。
• survey表出现Using temporary; Using filesort，是因为GROUP BY s.request_id和ORDER BY s.id ASC的排序字段不一致，导致数据库需要创建临时表来排序，优化后去掉多余的GROUP BY就能解决这个问题。

额外索引优化 #

为了进一步提速，给history_gt加个复合索引，覆盖分组、排序和需要查询的字段：

CREATE INDEX idx_history_request_id_perceptions ON history_gt(request_id, id, owner_perception, tl_perception);

这个索引能让数据库直接从索引里获取所有需要的数据，不用回表查询，大幅提升查询效率。

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