如何优化含GROUP BY与GROUP_CONCAT的MySQL慢查询

核心性能瓶颈定位 #

原查询耗时高的核心原因不是GROUP BY语法本身，而是存在4个明显的性能损耗点：

• 三次全表扫描开销：最外层直接以原表mytable作为驱动表，加上两个聚合子查询各做一次全表扫描，总共三次访问大表，IO和关联计算量极高
• 冗余分组计算：外层查询最后追加的GROUP BY results.idpatient完全多余——两个子查询均已按idpatient做唯一维度聚合，关联后idpatient天然唯一，额外分组平白增加排序/哈希计算开销
• 无适配聚合场景的覆盖索引：现有索引无法覆盖聚合需要的所有字段，查询过程中存在大量回表随机IO；同时indication_idx对当前聚合逻辑无任何增益，反而可能误导优化器选择低效执行路径
• 无效计算开销：用varchar类型存储dob日期字段，每次查询都要执行STR_TO_DATE函数做类型转换，浪费CPU资源；字符串拼接取分组极值对应字段的写法虽然适配MySQL 5.7无窗口函数的场景，但在无覆盖索引时计算成本被放大。

分步优化方案 #

1. 改写SQL消除冗余扫描和计算 #

去掉最外层多余的原表关联，将两次独立聚合子查询合并为单次表扫描聚合，删除外层无意义的GROUP BY，直接减少三分之二的表扫描开销：

SELECT 
    idpatient AS nhs_number,
    TIMESTAMPDIFF(YEAR, STR_TO_DATE(dob,'%Y-%m-%d'), CURDATE()) AS age,
    days_overdue,
    current_status,
    action,
    associated_indications
FROM (
    SELECT
        idpatient,
        MAX(days_overdue) as days_overdue,
        SUBSTRING(MAX(CONCAT(LPAD(days_overdue, 11, '0'), current_status)), 12) AS current_status,
        SUBSTRING(MAX(CONCAT(LPAD(days_overdue, 11, '0'), action)), 12) AS action,
        GROUP_CONCAT(
            IF(days_overdue = (SELECT MAX(days_overdue) FROM mytable m2 WHERE m2.idpatient = m1.idpatient), indication, NULL) 
            SEPARATOR ','
        ) as associated_indications,
        MIN(dob) as dob
    FROM mytable m1
    GROUP BY idpatient
) AS tmp
LIMIT 0,100;

注：业务上同一个患者的出生日期固定，同一个idpatient对应的dob不会出现多值，用MIN(dob)取值不会影响结果正确性。

2. 创建聚合场景专用覆盖索引 #

这是性能提升幅度最大的优化手段，创建覆盖所有聚合、查询字段的联合索引，让整个聚合过程完全在索引树上完成，不需要回表查询主键数据：

CREATE INDEX idx_patient_cov ON mytable(
    idpatient, 
    days_overdue DESC,
    action,
    current_status,
    indication,
    dob
);

索引设计逻辑：联合索引最左列放GROUP BY维度字段idpatient，第二列放极值计算字段days_overdue，后续列全部放查询需要读取的字段，完全满足覆盖索引要求。创建该索引后，优化器会直接选择该索引做有序扫描，GROUP BY过程不需要额外执行文件排序，所有需要的字段都能从索引中直接读取，随机IO开销可降低90%以上。

3. 可选长期结构优化 #

• 将dob字段从varchar(255)修改为DATE类型，彻底消除查询时的STR_TO_DATE函数计算开销，修改后年龄计算可直接写为TIMESTAMPDIFF(YEAR, dob, CURDATE())
• 删除无用的indication_idx索引，避免优化器选错执行路径，同时减少写入时的索引维护开销
• 如果GROUP_CONCAT拼接的适应症列表长度超过默认1024字节，可在查询前执行SET SESSION group_concat_max_len = 102400;调整会话级参数，避免结果截断。

预期效果 #

在千万级数据量的InnoDB表上，上述优化组合通常能将查询耗时从3-5秒降低至300ms-800ms，稳定满足1秒以内的性能要求。

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