sql相关子查询的一个小知识点

最近遇到一个耗时很长的sql,如下

    SELECT u.login_name , u.name name ,CONCAT( u.idcard,'') idcard
        ,u.phone
        phone,s.code code,c.name classroomName,d.name deptName ,
        (SELECT d.name FROM dept d WHERE d.id = getDeptHospitalId(u.dept_id) )
        hospitalName,p.name paperName,examset.name examsetName,exam.name
        examName
        ,e.mark/100 mark ,exam.start_dt examTime,
        (CASE e.state WHEN
        'A' THEN '是'
        ELSE '否' END) state
        FROM examinee e
        LEFT JOIN USER u ON u.id
        = e.user_id
        LEFT JOIN dept d ON d.id = u.dept_id
        LEFT JOIN plan p ON
        p.id = e.plan_id
        LEFT JOIN seat s ON s.id = e.seat_id
        LEFT JOIN
        classroom c ON c.id = s.classroom_id
        LEFT JOIN exam on exam.id =
        e.exam_id
        LEFT JOIN examset on examset.id = exam.examset_id
        WHERE
        exam_id =  '7bee06c7-8e0d-4af0-86eb-efed0edd2075'

该sql用时约10s，这还只是数据量少的时候，数据量庞大起来甚至是以分钟计时。

一开始我以为是因为索引没有添加，但是查看后发现连接字段都有索引，继续分析，我explain这句sql出来一看，终于找到问题

问题出在这里...(SELECT d.name FROM dept d WHERE d.id = getDeptHospitalId(u.dept_id) ) hospitalName,…

这里这句相关子查询依赖于外部查询，也就是外部查询执行一行，子查询就执行一次。假如只是如此也没啥问题，添加索引仍能使它变快，但是在这里连接部分使用了自定义函数，这就导致了mysql将不会使用索引而全表查询。导致查询异常的缓慢。甚至拆成两个独立查询顺序执行更为快捷。

我的解决方式：临时表联表查询

SELECT a.*,dept.`name` hospitalName from (SELECT u.login_name , u.name name ,CONCAT( u.idcard,'') idcard
        ,u.phone phone,getDeptHospitalId(u.dept_id) d_id,
        s.code code,c.name classroomName,d.name deptName,p.name paperName,examset.name examsetName,exam.name
        examName
        ,e.mark/100 mark ,exam.start_dt examTime,
        (CASE e.state WHEN
        'A' THEN '是'
        ELSE '否' END) state
        FROM examinee e 
        LEFT JOIN USER u ON u.id
        = e.user_id
        LEFT JOIN dept d ON d.id = u.dept_id
        LEFT JOIN plan p ON
        p.id = e.plan_id
        LEFT JOIN seat s ON s.id = e.seat_id
        LEFT JOIN
        classroom c ON c.id = s.classroom_id
        LEFT JOIN exam on exam.id =
        e.exam_id
        LEFT JOIN examset on examset.id = exam.examset_id
        WHERE
        exam_id =  '7bee06c7-8e0d-4af0-86eb-efed0edd2075'
) a LEFT JOIN dept on dept.id=a.d_id

查询时间降至10ms左右
参考资料：[慢查优化]慎用MySQL子查询，尤其是看到DEPENDENT SUBQUERY标记时