MySQL 8.0.28慢查询优化：关联分组查询性能提升方案咨询

一、修正查询逻辑隐含问题 #

原查询使用LEFT JOIN table2但在WHERE子句中过滤t2.item_class，这会自动将LEFT JOIN转换为INNER JOIN（不满足t2条件的记录会被直接过滤，包括t2为NULL的情况），建议直接改为INNER JOIN让逻辑更清晰，避免数据库执行计划混淆。

另外，SELECT t1.*, t2.*配合GROUP BY t1.tag_id存在逻辑风险：同一tag_id对应多条t1/t2记录时，MySQL会随机选取一条返回，可能不符合业务预期。如果业务需要明确返回某条记录（如最新/最早），需调整SELECT部分。

二、调整索引（核心优化） #

当前单列索引无法高效支撑多条件过滤+关联+分组的场景，需创建联合索引：

针对table1： #

创建覆盖过滤、关联、分组的联合索引，避免回表查询：

CREATE INDEX idx_t1_status_type_tag ON table1 (activity_status, activity_type, tag_id);

该索引可快速筛选符合activity_status和activity_type的记录，同时直接获取tag_id用于关联和分组。

针对table2： #

创建覆盖关联与过滤条件的联合索引：

CREATE INDEX idx_t2_tag_class ON table2 (tag_id, item_class);

通过tag_id快速关联table1，同时过滤item_class条件，无需回表。

三、改写查询语句（优化分组逻辑） #

方案1：先过滤分组再关联 #

先在table1中筛选符合条件且tag_id唯一的记录，再关联table2，减少关联数据量：

SELECT t1.*, t2.*, t2.id AS t2_id, t1.id AS t1_id
FROM (
    SELECT *
    FROM table1
    WHERE activity_status = "AnonymousStatus"
      AND activity_type IN ("AnonymousType1", "AnonymousType2")
    GROUP BY tag_id
    HAVING COUNT(tag_id) = 1
) t1
INNER JOIN table2 t2 ON t1.tag_id = t2.tag_id
WHERE t2.item_class IN ("AnonymousClass1", "AnonymousClass2");

用IN替代OR（MySQL对IN优化更友好），子查询先完成过滤和分组，减少后续关联的数据量。

方案2：用窗口函数替代GROUP BY（逻辑更清晰） #

如果需要明确控制返回的记录（如取每个tag_id的第一条），可使用窗口函数：

SELECT t1.*, t2.*, t2.id AS t2_id, t1.id AS t1_id
FROM (
    SELECT *,
           COUNT(*) OVER (PARTITION BY tag_id) AS tag_count
    FROM table1
    WHERE activity_status = "AnonymousStatus"
      AND activity_type IN ("AnonymousType1", "AnonymousType2")
) t1
INNER JOIN table2 t2 ON t1.tag_id = t2.tag_id
WHERE t1.tag_count = 1
  AND t2.item_class IN ("AnonymousClass1", "AnonymousClass2");

窗口函数可避免GROUP BY带来的临时表开销，同时更灵活地控制返回结果。

四、其他注意事项 #

• 检查activity_status、activity_type、item_class字段类型是否与查询字符串匹配，避免隐式类型转换导致索引失效。
• 原EXPLAIN中的Using temporary是因为GROUP BY需要临时表存储分组结果，优化索引后可减少临时表大小甚至避免临时表。

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