mysql left join 优化

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

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

img


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;

单个速度肯定快的，使用 left join 之后，每次连接查询都需要耗时的

一个查询sql有这么多关联一般来说是不合理的，关联的越多，逻辑越复杂就会越慢，通常超过三个关联关系就得设置视图，或者中间关联表来实现查询逻辑

两种方法
1、你的sql不变，把关联的字段加上索引看一下
2、阿里巴巴规范说禁止三个表以上的join，你的描述说主要是最后一个left join慢，很好办，前面sql保持不变，返回一个list，最后一个返回list后转map，遍历list，从map取值，不知道你可懂我表达的意思

最后一个left join 先过滤再关联试试