mysql left join 优化

Question

mySql LEFT JOIN 优化 求帮我指出问题

每个LEFT JOIN 单个查询很快，但是整体运行查询6.7s（主要是最后一个LEFT JOIN 加上就会很慢）

下面是执行计划

SELECT a.user_id,a.user_name,a.name ,REPLACE(GROUP_CONCAT(DISTINCT b.class_name),',','、') AS v1 
,REPLACE(GROUP_CONCAT(DISTINCT c.class_name),',','、') AS v2 
,REPLACE(GROUP_CONCAT(DISTINCT d.class_subject_name),',','、') AS v3
 FROM yf_user a
 LEFT JOIN (
 SELECT a.school_id,a.teacher_id1,a.class_id,a.class_name
 FROM yf_class a
 INNER JOIN yf_grade b
 ON a.school_id=b.school_id AND a.grade_id=b.grade_id
 WHERE a.school_id=1 AND b.grade_code<>'199' AND a.class_type<>'199' AND b.status=1 )b
 ON a.school_id=b.school_id AND a.user_id=b.teacher_id1
 LEFT JOIN (
 SELECT a.school_id,a.teacher_id2,a.class_id,a.class_name
 FROM yf_class a
 INNER JOIN yf_grade b
 ON a.school_id=b.school_id AND a.grade_id=b.grade_id
 WHERE a.school_id=1 AND b.grade_code<>'199' AND a.class_type<>'199' AND b.status=1 )c
 ON a.school_id=c.school_id AND a.user_id=c.teacher_id2
 LEFT JOIN (
 SELECT a.school_id,a.user_id,CONCAT(c.class_name,d.subject_name) AS class_subject_name
 FROM yf_tea a
 INNER JOIN yf_grade b
 ON a.school_id=b.school_id AND a.grade_id=b.grade_id
 INNER JOIN yf_class c
 ON a.school_id=c.school_id AND a.class_id=c.class_id
 INNER JOIN yf_subject d
 ON a.school_id=d.school_id AND a.subject_id=d.subject_id
 WHERE a.school_id=1 AND b.grade_code<>'199' AND c.class_type<>'199' AND b.status=1 ) d
 ON a.school_id=d.school_id AND a.user_id=d.user_id
 WHERE a.school_id=1 AND a.user_type=1 
 GROUP BY a.user_id HAVING (v1 IS NOT NULL OR v2 IS NOT NULL OR v3 IS NOT NULL) 
 ORDER BY a.sort ASC,a.user_name ASC,a.name ASC;

Answer 1

• 这个问题的回答你可以参考下: https://ask.csdn.net/questions/7525698
• 我还给你找了一篇非常好的博客，你可以看看是否有帮助，链接：mysql left join执行异常缓慢优化案例
• 你还可以看下mysql参考手册中的 MySQL 优化