mysql函数取代相关子查询(Correlated subquery)

814247614

CREATE TABLE `20121105_teacher` (
`teacher_id` int(11) NOT NULL,
`school_id` int(11) NOT NULL,
PRIMARY KEY (`teacher_id`),
KEY `20121105_teacher_idx_school` (`school_id`)
) ENGINE=InnoDB
教师表,里面有1000个教师,随机分布在40个学校里
CREATE TABLE `20121105_subject_teacher_class` (
`teacher_id` int(11) NOT NULL,
`subj` varchar(10) NOT NULL,
`class` varchar(10) NOT NULL,
PRIMARY KEY (`teacher_id`,`subj`,`class`)
) ENGINE=InnoDB
教师任课科目表,教师随机在24个班级内随机教三个科目.为了方便演示,直接将科目名称和班级名称放到数据库中
假设要查询教师的授课情况,每个教师这样显示
英语:11班,12班,8班##语文:13班,1班,21班,6班##数学:12班,14班,6班,7班
很容易想到这个sql能把每个教师的授课情况显示出来
select tid,GROUP_CONCAT( cls SEPARATOR '##') c1  from
(
select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls
from 20121105_subject_teacher_class stc
GROUP BY teacher_id,subj
) t  GROUP BY tid,
那么把这个作为一个子查询呢?似乎很容易想到
select teacher_id,
(
select GROUP_CONCAT( cls SEPARATOR ' ## ') from
(
select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls
from 20121105_subject_teacher_class stc where stc.teacher_id=t1.teacher_id
GROUP BY teacher_id,subj) t GROUP BY tid
)
from 20121105_teacher t1 where school_id=2

不过可惜在最里面那层子查询已经无法引用最外层的t1表的teacher_id这个字段了,
只能拿到外面一层
select  teacher_id,
(
select GROUP_CONCAT( cls SEPARATOR ' ## ') from
(
select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls
from 20121105_subject_teacher_class stc
GROUP BY teacher_id,subj) t where t.tid=t1.teacher_id GROUP BY tid
)
from 20121105_teacher t1 where school_id=2
不过因为这样无法高效利用索引,这个sql花了0.05s
所以可以建个函数
CREATE  FUNCTION `20121105f`(p_teacher_id int) RETURNS varchar(2000)
READS SQL DATA
BEGIN
DECLARE v_result VARCHAR(2000);
DECLARE EXIT HANDLER for not found return null;
select GROUP_CONCAT( cls SEPARATOR ' ## ') into v_result from
(
select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls
from 20121105_subject_teacher_class stc where stc.teacher_id=p_teacher_id
GROUP BY teacher_id,subj
) t GROUP BY tid;
return v_result;
END
然后这样用
select SQL_NO_CACHE teacher_id,
20121105f(teacher_id)
from 20121105_teacher t1 where school_id=2
马上成瞬时的了.

不用子查询,也可以用左连接的方法
select  t1.teacher_id,t2.c1
from  20121105_teacher t1
left join (
select tid,GROUP_CONCAT( cls SEPARATOR '##') c1  from
(
select teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls
from 20121105_subject_teacher_class stc
GROUP BY teacher_id,subj
) t  GROUP BY tid
) t2
on t1.teacher_id=t2.tid
where school_id=2

这种情况下因为20121105_subject_teacher_class表没用索引,是0.04s左右
加上条件
select  t1.teacher_id,t2.c1
from  20121105_teacher t1
left join (
select tid,GROUP_CONCAT( cls SEPARATOR '##') c1  from
(
select  stc.teacher_id tid,CONCAT(subj,':',GROUP_CONCAT(class)) cls
from 20121105_subject_teacher_class stc ,20121105_teacher te
where stc.teacher_id=te.teacher_id and te.school_id=2
GROUP BY  stc.teacher_id,subj
) t  GROUP BY tid
) t2
on t1.teacher_id=t2.tid
where school_id=2
这样这个也成了瞬时的,不过筛选teacher的条件(school_id=2)执行了两次,
如果这个条件比较耗资源,应该就更慢了