MySQL查询性能优化求助：多层子查询执行过慢

你的查询存在冗余嵌套子查询，最内层已经按ip, filter, eventday分组计算了各字段的MAX值，外层却重复执行了完全相同的分组和MAX计算，这会额外消耗大量计算资源，是导致查询缓慢的核心原因之一。以下是具体优化步骤：

1. 简化查询结构 #

直接去掉重复的子查询层，将查询简化为单层子查询+最终分组统计：

SELECT
    eventday,
    SUM(recipients) as totalrecipients,
    SUM(greatest(rcpt - recipients, 0)) as totalbounces,
    SUM(traphits) as totaltraphits,
    toUInt32(round(sum(recipients * complaintrate) / 100, 0)) AS totalcomplaints,
    SUM(CASE WHEN filter = 'GREEN' THEN recipients ELSE 0 END) AS green,
    SUM(CASE WHEN filter = 'YELLOW' THEN recipients ELSE 0 END) AS yellow,
    SUM(CASE WHEN filter = 'RED' THEN recipients ELSE 0 END) AS red
FROM 
    (SELECT
        snds.ip,
        snds.filter,
        snds.eventday,
        MAX(snds.recipients) as recipients,
        MAX(snds.rcpt) as rcpt,
        MAX(snds.traphits) as traphits,
        MAX(snds.complaintrate) as complaintrate
     FROM monitor.sndsdata snds
     WHERE snds.eventday >= :startdate 
       AND snds.eventday <= :enddate 
       AND snds.snds_id in (:snds_id)
    GROUP BY snds.ip, snds.filter, snds.eventday) s
GROUP BY 
    eventday
ORDER BY 
    eventday

2. 添加针对性索引 #

针对monitor.sndsdata表，创建复合覆盖索引，覆盖查询的过滤条件、分组字段和需要计算的字段，避免回表查询：

CREATE INDEX idx_sndsdata_snds_event_ip_filter ON monitor.sndsdata (snds_id, eventday, ip, filter) INCLUDE (recipients, rcpt, traphits, complaintrate);

如果你的MySQL版本不支持INCLUDE子句（低于8.0.13），可以将需要的字段直接加入索引：

CREATE INDEX idx_sndsdata_snds_event_ip_filter ON monitor.sndsdata (snds_id, eventday, ip, filter, recipients, rcpt, traphits, complaintrate);

3. 额外优化建议 #

• 验证MAX()函数的必要性：如果每个ip, filter, eventday组合下，recipients、rcpt等字段本身就只有一条记录，可直接去掉MAX()，仅保留GROUP BY，进一步减少计算开销。
• 简化数据类型转换：如果recipients本身是整数类型，可去掉toDecimal64(s.recipients, 0)转换，直接计算recipients * complaintrate，减少类型转换的性能损耗。

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