求大神帮优化下这个sql,这个sql用到导出数据中,这个sql是先查查1600条课程,然后子查询查出该门课程的分数返回,1600条数据全部导出需要快5分钟。主要应该是评价统计主表有evaluation_statistical9万条数据原因,有时导出甚至直接请求超时了,求大神帮优化。
SELECT DISTINCT
j2.KCDM,
j2.KCMC,
j2.JSZGH,
j2.JSXM,( SELECT
( SELECT
sum(e5.level_score)
FROMevaluation_statistical
e4
JOIN evaluation_level e5 ON e4.level_id = e5.level_id
WHERE
e4.bpjr_id=j2.JSZGH and
e4.semester_id = '44a4424321114e7499715d0b1f46ed96'
AND e4.course_id = j2.KCDM
) /
(
SELECT
count(1)
FROMevaluation_statistical
e3
WHERE
e3.bpjr_id=j2.JSZGH and
e3.semester_id ='44a4424321114e7499715d0b1f46ed96'
AND e3.course_id = j2.KCDM
)
) as score
FROM
jw_jskcb j2 WHERE CONCAT(j2.JSZGH,j2.KCDM) IN
(SELECT DISTINCT CONCAT(e.bpjr_id,e.course_id) FROM evaluation_statistical e
WHERE e.isinvalid = '1'
AND e.evaluation_state = '1')