Mysql多表JOIN关联查询执行耗时0.14s以上如何优化

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

当前查询SELECT d.* ... GROUP BY c.institute不符合SQL标准的ONLY_FULL_GROUP_BY规则，本质需求是获取关联后c.institute对应的去重cm_institute表记录，原始写法会先生成多表关联的全量大中间表再做分组，是性能损耗的核心原因。

二、查询改写方案 #

优先过滤小结果集，再关联获取d表数据，避免生成冗余中间数据：

SELECT d.* 
FROM cm_institute d
WHERE d.institute_id IN (
    SELECT DISTINCT c.institute
    FROM cm_stream a 
    INNER JOIN cm_courses b ON a.stream_id = b.stream 
    INNER JOIN cm_institute_courses c ON b.course_id = c.course
    WHERE a.path = 'engineering'
)

如果使用的MySQL版本支持CTE，也可以用CTE写法提升可读性，性能和上述子查询写法基本一致。

三、索引优化建议 #

现有索引基本覆盖查询需求，补充1个联合索引可进一步消除回表损耗：

• 给cm_institute_courses（c表）添加联合索引idx_course_institute(course, institute)，子查询通过b.course_id关联c.course时，可直接从索引中拿到对应的c.institute值，不需要回表查询c表行数据。

其余表的现有索引已经可以支撑查询：

• a表的path唯一索引可直接命中a.path = 'engineering'过滤条件，快速拿到对应stream_id
• b表的stream联合索引(stream, course_id)可在关联时直接返回course_id，不需要回表
• d表的主键索引institute_id可直接命中institute_id过滤条件，返回d表全量字段

四、额外优化方向 #

• 确认所有关联字段的类型、字符集完全一致，避免隐式转换导致索引失效
• 如果查询结果允许短时间缓存，可在应用层添加缓存，避免重复请求频繁查询数据库

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