过大的子查询用临时表处理效果会好的多
-- /*以学生做题本为基础，加载某一天各班级的做题信息*/
1 insert into tmp_bas_class_do_list(problem_id,class_id,subject_id,do_type,error_persons,do_persons,count_date)
2
3 select bs.problem_id,bs.class_id,bs.subject_id,bs.do_type,
4
5 sum(case bs.if_error when 1 then 0 else 1 end ) error_cnt,
6
7 count(*) do_cnt,
8
9 date_format(bs.do_date,'%Y-%m-%d')
10
11 from bas_student_do_list bs
12
13 group by bs.problem_id,bs.class_id,bs.subject_id,bs.do_type,date_format(bs.do_date,'%Y-%m-%d');
14
15 create index idx_tmp on tmp_bas_class_do_list(class_id, problem_id);
16
17 update tmp_bas_class_do_list bs,
18
19 (select t3.problem_id,t3.do_type, t3.class_id,date_format(t3.do_date,'%Y-%m-%d') as do_date,group_concat(t1.name) as std_name
20
21 from bas_person t1, bas_student_do_list t3
22
23 where t1.person_id = t3.person_id
24
25 and t3.if_error = 0
26
27 group by t3.problem_id,t3.do_type, t3.class_id,date_format(t3.do_date,'%Y-%m-%d')) t2
28
29 set bs.error_students = t2.std_name
30
31 where bs.problem_id = t2.problem_id
32
33 and bs.class_id = t2.class_id
34
35 and count_date = t2.do_date
36
37 and bs.do_type = t2.do_type;
2
3 select bs.problem_id,bs.class_id,bs.subject_id,bs.do_type,
4
5 sum(case bs.if_error when 1 then 0 else 1 end ) error_cnt,
6
7 count(*) do_cnt,
8
9 date_format(bs.do_date,'%Y-%m-%d')
10
11 from bas_student_do_list bs
12
13 group by bs.problem_id,bs.class_id,bs.subject_id,bs.do_type,date_format(bs.do_date,'%Y-%m-%d');
14
15 create index idx_tmp on tmp_bas_class_do_list(class_id, problem_id);
16
17 update tmp_bas_class_do_list bs,
18
19 (select t3.problem_id,t3.do_type, t3.class_id,date_format(t3.do_date,'%Y-%m-%d') as do_date,group_concat(t1.name) as std_name
20
21 from bas_person t1, bas_student_do_list t3
22
23 where t1.person_id = t3.person_id
24
25 and t3.if_error = 0
26
27 group by t3.problem_id,t3.do_type, t3.class_id,date_format(t3.do_date,'%Y-%m-%d')) t2
28
29 set bs.error_students = t2.std_name
30
31 where bs.problem_id = t2.problem_id
32
33 and bs.class_id = t2.class_id
34
35 and count_date = t2.do_date
36
37 and bs.do_type = t2.do_type;
-- 临时表扶正
truncate table bas_class_do_list;
drop table bas_class_do_list;
rename table tmp_bas_class_do_list to bas_class_do_list;
大家可以看到，我是先把select的结果insert到临时表里，建立索引后，又用derived表t2更新临时表的数据，最后临时表替换成正式表。最初的时候，我是直接用select表和derived表t2做关联，直接insert到正式表里，这样写的select和t2做join的速度非常慢。改成这种写法后，速度由8分钟减少到40秒。
通过这个例子我们也可以看到，大批量插入前先删除索引，插入后再建立索引，效果要比直接插入好的多。
