含HAVING子句的SQL查询执行过慢问题排查与优化咨询

问题根因 #

首先明确：不是网络传输问题，是查询本身执行效率低+phpmyadmin的执行结果误导导致的认知偏差

• 慢查询日志已经明确记录Query_time: 15.594386，这个时间是MySQL从接收请求到执行完成准备返回结果的耗时，说明所有耗时都发生在数据库执行阶段，和PHP侧逻辑、网络传输无关。你看到的phpmyadmin执行快，是因为phpmyadmin默认会给所有手动执行的查询自动追加LIMIT子句（默认返回前25/30条），你看到的0.0113秒是截断后的查询耗时，和PHP中执行的全量查询不是同一个执行逻辑。
• 原查询的性能缺陷：
  1. 多余的表关联：你的需求仅需要返回比赛ID，也就是matchattributes表的game_id字段，完全不需要关联matchoverview表，原查询先扫描279万行matchoverview全表，再逐行关联matchattributes，产生了大量无效的关联开销，慢日志中1500多万的扫描行就是这么来的。
  2. 聚合时机不合理：原查询先关联再聚合，所有关联产生的中间行都要参与分组、SUM计算，哪怕最后只有少量符合条件的结果，也需要完成全量的计算工作，这就是你返回24条的同类查询也很慢的核心原因。
  3. 索引不够高效：目前matchattributes仅用了game_id的单字段索引，查询时需要回表读取attribute_id和attribute_value，额外增加了IO开销。

可行解决方案 #

1. 优先优化查询逻辑（性能提升最明显） #

去掉多余的matchoverview表关联，直接对matchattributes做聚合过滤，优化后SQL如下：

SELECT game_id AS id
FROM matchattributes
WHERE attribute_id IN (3,4,5,6)
GROUP BY game_id
HAVING SUM(CASE WHEN attribute_id IN (3,4) THEN attribute_value ELSE 0 END) > 3
    OR SUM(CASE WHEN attribute_id IN (5,6) THEN attribute_value ELSE 0 END) > 3;

如果业务上要求game_id必须在matchoverview中存在，再追加关联即可，这时关联的仅为聚合后符合条件的少量ID，开销可以忽略：

SELECT m.id
FROM matchoverview m
INNER JOIN (
    SELECT game_id
    FROM matchattributes
    WHERE attribute_id IN (3,4,5,6)
    GROUP BY game_id
    HAVING SUM(CASE WHEN attribute_id IN (3,4) THEN attribute_value ELSE 0 END) > 3
        OR SUM(CASE WHEN attribute_id IN (5,6) THEN attribute_value ELSE 0 END) > 3
) ma_filtered ON m.id = ma_filtered.game_id;

2. 新增覆盖索引 #

给matchattributes创建联合覆盖索引，避免查询回表，同时加速分组、过滤计算：

CREATE INDEX idx_game_attr_val ON matchattributes (game_id, attribute_id, attribute_value);

这个索引覆盖了查询需要的所有字段，整个查询可以完全在索引中完成，不需要访问主表数据，性能可以提升数倍。

3. 长期场景优化 #

如果这类属性聚合查询是高频业务场景，建议新增预汇总表，提前计算好每个比赛的属性3+4、属性5+6的和，写入汇总表，查询时直接过滤汇总表即可，查询耗时可以降到毫秒级，适合读多写少的业务场景。

内容的提问来源于stack exchange，提问作者Marc-9