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

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)执行了两次,
如果这个条件比较耗资源,应该就更慢了