mysql多表join 优化_mysql和java多表join业务优化？

写作业的思考。假如有这么一个签到系统

按照范式设计有

学院表

课表(学院课表一对多)

老师表

学生表

教课表(老师课表多对多)

上课表(学生课表多对多)

排课表(课表排课一对多)

签到表(排课签到一对多)

签到记录表(签到，签到记录一对多)

假如设定两个查询

学生由学号和课号查询签到

教师由教工号和课号查询

而在我实现这个系统时，随便一个查询动作都建立了好多张表，为了获取排课，课程，老师，学院信息。

我觉得这样做，，有点麻烦？可是把它放到代码实现也要不断发起查询很多次呀。。

比如这是我刚刚写的，，一个获取这个学生本周需要签到的课程，我想看看瞎几把写可以联立成什么样子，。功能的目的我达到了

SELECT

att.user_usr_id, att.course_coz_id,

sch.sch_id, sch.sch_year, sch.sch_term, sch.sch_start_week, sch.sch_end_week,

sch.sch_fortnight, sch.sch_day, sch.sch_start_time, sch.sch_end_time, sch.course_coz_id, sch.location_loc_id,

loc.loc_id, loc.loc_name,

coz.coz_id, coz.coz_name, coz.coz_size, coz.coz_act_size, coz.coz_att_rate,

tea.user_usr_id tea_user_usr_id, tea.course_coz_id,

tch.usr_name tea_user_usr_name,

coz_sch.sch_id coz_sch_id, coz_sch.sch_year coz_sch_year, coz_sch.sch_term coz_sch_term,

coz_sch.sch_start_week coz_sch_start_week, coz_sch.sch_end_week coz_sch_end_week, coz_sch.sch_fortnight

coz_sch_fortnight,

coz_sch.sch_day coz_sch_day, coz_sch.sch_start_time coz_sch_start_time, coz_sch.sch_end_time coz_sch_end_time,

coz_sch.course_coz_id coz_sch_course_coz_id, coz_sch.location_loc_id,

coz_sch_loc.loc_id coz_sch_loc_id, coz_sch_loc.loc_name coz_sch_loc_name,

si_id, si_week, si_time, si_auto,

sir_id, sir_time, sir_leave, sir_approve, sir_voucher, sir.sign_in_${curYear}_${curTerm}_si_id sir_id,

sir.user_usr_id sir_user_usr_id

FROM attendance att

JOIN schedule sch ON att.course_coz_id = sch.course_coz_id

LEFT OUTER JOIN location loc ON loc.loc_id = sch.location_loc_id

<!-- sch coz -->

JOIN course coz ON sch.course_coz_id = coz.coz_id

LEFT OUTER JOIN teaching tea ON coz.coz_id = tea.course_coz_id

LEFT OUTER JOIN schedule coz_sch ON coz.coz_id = coz_sch.course_coz_id

LEFT OUTER JOIN location coz_sch_loc ON coz_sch.location_loc_id = coz_sch_loc.loc_id

JOIN user tch ON tea.user_usr_id = tch.usr_id

<!-- history sign in -->

JOIN sign_in_${curYear}_${curTerm} si ON sch.sch_id = si.schedule_sch_id

LEFT OUTER JOIN sign_in_rec_${curYear}_${curTerm} sir ON

si.si_id = sir.sign_in_${curYear}_${curTerm}_si_id AND

att.user_usr_id = sir.user_usr_id

<where>

sir.sir_id IS NULL <!-- 还没签到de  -->

AND att.user_usr_id = #{usrId} <!-- 用户id  -->

</where>