如何加速基于索引列的GROUP BY查询？SQL性能优化求助

我有一条带GROUP BY子句的查询语句，执行耗时3-4秒；若移除GROUP BY子句，执行时间仅需0.05秒。该GROUP BY已使用索引值（为某表的主键），且我必须保留GROUP BY子句，无法移除。这是我应用中最常用的查询语句之一，请问该如何优化以提升查询速度？

查询语句 #

SELECT col1, col2, (...)
FROM table1 AS t1
JOIN table2 AS t2
   ON t2.fk_id = t1.a_id
JOIN table3 AS t3
   ON t3.fk_id = t2.id
JOIN table4 AS t4
   ON t4.fk_id = t1.b_id
JOIN table5 AS t5
   ON t5.fk_id = t4.id
LEFT JOIN table6 AS t6
   ON t6.fk_id = t1.c_id
LEFT JOIN table7 AS t7
   ON t7.fk_id = t6.id
WHERE t3.user_id = 12345
  AND t5.slug NOT IN ( 'slug_a','slug_b')
GROUP BY t1.id

EXPLAIN执行结果（表名对应关系：p=t3, o=t2, i=t1, v=t4, pr=t5, d=t6, inv=t7） #

优化方案 #

1. 消除临时表与文件排序 #

从EXPLAIN结果看，p表（对应t3）出现Using temporary; Using filesort，这是核心性能瓶颈。当前执行顺序是从t3开始关联，导致MySQL需要先收集全量关联结果再分组。可以通过以下方式优化：

• 给t1创建覆盖索引，包含分组字段、关联字段及查询列：CREATE INDEX idx_t1_group_covering ON table1(id, a_id, b_id, c_id, col1, col2);（将SELECT中所有需要的列都加入索引）
• 用子查询先筛选出符合条件的t1记录，再关联其他表，减少分组时的数据量：

SELECT t1.col1, t1.col2, (...)
FROM (
    SELECT id, a_id, b_id, c_id, col1, col2 FROM table1 
    WHERE EXISTS (
        SELECT 1 FROM table2 t2 
        JOIN table3 t3 ON t3.fk_id = t2.id 
        WHERE t2.fk_id = table1.a_id AND t3.user_id = 12345
    )
    AND EXISTS (
        SELECT 1 FROM table4 t4 
        JOIN table5 t5 ON t5.fk_id = t4.id 
        WHERE t4.fk_id = table1.b_id AND t5.slug NOT IN ('slug_a','slug_b')
    )
) AS t1
JOIN table2 AS t2 ON t2.fk_id = t1.a_id
JOIN table3 AS t3 ON t3.fk_id = t2.id
JOIN table4 AS t4 ON t4.fk_id = t1.b_id
JOIN table5 AS t5 ON t5.fk_id = t4.id
LEFT JOIN table6 AS t6 ON t6.fk_id = t1.c_id
LEFT JOIN table7 AS t7 ON t7.fk_id = t6.id
GROUP BY t1.id

2. 优化LEFT JOIN的性能 #

inv表（t7）单次扫描99503行，会大幅增加数据量：

• 给t7的fk_id和查询中用到的过滤字段创建联合索引，比如CREATE INDEX idx_t7_fk_condition ON table7(fk_id, [过滤列]);
• 如果查询结果不需要t7的列，直接移除该LEFT JOIN；若需要，用子查询只关联必要的t7数据。

3. 强制指定执行计划 #

虽然GROUP BY的是t1.id主键，但MySQL可能未选择最优执行顺序，可强制调整：

• 在GROUP BY后添加FORCE INDEX (PRIMARY)，强制使用主键索引分组
• 用STRAIGHT_JOIN指定表的关联顺序，让MySQL先处理t1：

SELECT col1, col2, (...)
FROM table1 AS t1
STRAIGHT_JOIN table2 AS t2 ON t2.fk_id = t1.a_id
STRAIGHT_JOIN table3 AS t3 ON t3.fk_id = t2.id
JOIN table4 AS t4 ON t4.fk_id = t1.b_id
JOIN table5 AS t5 ON t5.fk_id = t4.id
LEFT JOIN table6 AS t6 ON t6.fk_id = t1.c_id
LEFT JOIN table7 AS t7 ON t7.fk_id = t6.id
WHERE t3.user_id = 12345
  AND t5.slug NOT IN ( 'slug_a','slug_b')
GROUP BY t1.id FORCE INDEX (PRIMARY)

4. 优化NOT IN的性能 #

将t5.slug NOT IN ('slug_a','slug_b')替换为t5.slug != 'slug_a' AND t5.slug != 'slug_b'，部分场景下后者索引利用效率更高；同时给t5创建(fk_id, slug)联合索引：CREATE INDEX idx_t5_fk_slug ON table5(fk_id, slug);，关联时直接过滤不符合条件的记录。

内容的提问来源于stack exchange，提问作者Charles.C