mysql函數取代相關子查詢(Correlated subquery)
Sql代碼
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
www.2cto.com
教師表,裡面有1000個教師,隨機分布在40個學校裡
Sql代碼
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能把每個教師的授課情況顯示出來
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,
那麼把這個作為一個子查詢呢?似乎很容易想到
Sql代碼
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這個字段了,
只能拿到外面一層
Sql代碼
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
所以可以建個函數
Sql代碼
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
然後這樣用
Sql代碼
select SQL_NO_CACHE teacher_id,
20121105f(teacher_id)
from 20121105_teacher t1 where school_id=2
馬上成瞬時的了.
不用子查詢,也可以用左連接的方法
Sql代碼
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左右
加上條件
Sql代碼
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)執行了兩次,
如果這個條件比較耗資源,應該就更慢了
