Mysql 主表10W数据左右，left join 3个表 非常慢 急求救

[黎sir]

Mysql 主表10-20WW数据左右，left join 3个表 非常慢 急求救,求大哥大姐指导

SELECT a.`province_name`, ##省份
		a.`city_name`, ##城市
		a.`mange_name`, ##管理区域
		a.`school_name`, ##校区
		a.teacher_name, ##教师姓名 
		a.student_name,  ##学生姓名
		a.grade_group_name, ##学段
		a.grade_name, ##年级
		a.subject_name, ##学科
		a.course_name, ##课程
		IFNULL(a.`consume_lession_count`, 0) AS consume_lession_count, ##周期内消耗课时
		IFNULL(b.`surplus_lession_count`, 0) AS surplus_lession_count, ##月末剩余课时
		a.`start_time`,
		CASE WHEN !ISNULL(b.`student_id`) THEN '在读' ELSE '' END AS on_study, ##在读
		CASE WHEN !ISNULL(c.`student_id`) THEN '在册' ELSE '' END AS on_registered, ##在册 
		CASE WHEN !ISNULL(d.`student_id`) THEN '新接' ELSE '' END AS new_receive, ## 周期新接
		
		CASE WHEN a.`special_state` = 1 THEN '高三'
		WHEN IFNULL(a.`consume_lession_count`, 0) <= 10 AND !ISNULL(d.`student_id`)
		AND ISNULL(b.`student_id`) THEN '短单'
		WHEN a.`special_state` = 3 OR DATE_ADD(a.`start_time`, INTERVAL 12 MONTH) < '2016-12-01' THEN '长期停课' 
		WHEN a.`special_state` = 4 AND IFNULL(b.`surplus_lession_count`, 0) > 0  THEN '毕业跨段' 
		ELSE '正常' END AS special_state
		FROM temp_base_data AS a ##数据集合
		LEFT OUTER JOIN temp_on_study AS b 
		ON a.`teacher_id` = b.`teacher_id` AND a.`student_id` = b.`student_id` AND a.`course_id` = b.`course_id` ##月末在读
		LEFT OUTER JOIN temp_on_registered AS c ON a.`teacher_id` = c.`teacher_id` AND a.`student_id` = c.`student_id` AND a.`course_id` = c.`course_id` ##上周期在册
		LEFT OUTER JOIN temp_new_receive AS d ON a.`teacher_id` = d.`teacher_id` AND a.`student_id` = d.`student_id` AND a.`course_id` = d.`course_id` ##周期内新接
		WHERE !ISNULL(b.`student_id`) OR !ISNULL(c.`student_id`) OR !ISNULL(d.`student_id`)
	
	CREATE TEMPORARY TABLE `temp_base_data` (
  `province_name` varchar(50) NOT NULL COMMENT '显示',
  `city_name` varchar(50) COMMENT '显示',
  `mange_name` varchar(50) DEFAULT NULL,
  `school_name` varchar(50) DEFAULT NULL,
  `teacher_id` varchar(38) DEFAULT NULL COMMENT '教师ID',
  `teacher_name` varchar(32) DEFAULT NULL,
  `student_id` varchar(38) DEFAULT NULL COMMENT '学生ID',
  `student_name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `grade_group_id` varchar(38) DEFAULT NULL COMMENT '学段ID',
  `grade_group_name` varchar(50) COMMENT '显示',
  `grade_id` varchar(38) DEFAULT NULL COMMENT '年级ID',
  `grade_name` varchar(50) COMMENT '显示',
  `subject_id` varchar(38) DEFAULT NULL COMMENT '学科ID',
  `subject_name` varchar(50) COMMENT '显示',
  `course_id` varchar(38) DEFAULT NULL COMMENT '课程ID',
  `course_name` varchar(50) DEFAULT NULL COMMENT '课程名称',
  `consume_lession_count` decimal(32,2) NOT NULL DEFAULT '0.00',
  `special_state` int(11) DEFAULT NULL COMMENT '特殊状态：0-正常 1-高三 2-短单 3-长期停课 4-毕业跨段',
  `start_time` datetime DEFAULT NULL COMMENT '最近上课时间',
  KEY `temp_index_grade_group_id` (`grade_group_id`),
  KEY `temp_index_grade_id` (`grade_id`),
  KEY `temp_index_subject_id` (`subject_id`),
  KEY `temp_index_course_id` (`course_id`),
  KEY `temp_index_student_id` (`student_id`),
  KEY `temp_index_teacher_id` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TEMPORARY TABLE `temp_on_study` (
  `course_id` varchar(38) DEFAULT NULL COMMENT '课程ID',
  `student_id` varchar(38) DEFAULT NULL COMMENT '学生ID',
  `teacher_id` varchar(38) DEFAULT NULL COMMENT '教师ID',
  `surplus_lession_count` decimal(32,2) DEFAULT NULL,
  KEY `temp_index_course_id` (`course_id`),
  KEY `temp_index_student_id` (`student_id`),
  KEY `temp_index_teacher_id` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TEMPORARY TABLE `temp_on_registered` (
  `course_id` varchar(38) DEFAULT NULL COMMENT '课程ID',
  `student_id` varchar(38) DEFAULT NULL COMMENT '学生ID',
  `teacher_id` varchar(38) DEFAULT NULL COMMENT '教师ID',
  KEY `temp_index_course_id` (`course_id`),
  KEY `temp_index_student_id` (`student_id`),
  KEY `temp_index_teacher_id` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8




CREATE TEMPORARY TABLE `temp_new_receive` (
  `course_id` varchar(38) DEFAULT NULL COMMENT '课程ID',
  `student_id` varchar(38) DEFAULT NULL COMMENT '学生ID',
  `teacher_id` varchar(38) DEFAULT NULL COMMENT '教师ID',
  KEY `temp_index_course_id` (`course_id`),
  KEY `temp_index_student_id` (`student_id`),
  KEY `temp_index_teacher_id` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[ACMAIN_CHM]

以文本方式贴出(不要贴图!) explain select .... 及 show index from 以供分析

[rucypli]

加连接条件的联合索引 单个索引用处不大 alter table temp_base_data add index(teacher_id,student_id,course_id); alter table temp_on_study add index(teacher_id,student_id,course_id); alter table temp_on_registered add index(teacher_id,student_id,course_id); alter table temp_new_receive add index(teacher_id,student_id,course_id);