效率排序:on>where>having
inner join比其他连接效率高
表与表之间的数据转移应灵活使用left join和is not null查询插入
连接越多的表并不代表效率就低,需结合多种情况,需考虑连接表的数据量,是否外键等问题,考虑恰当了反而会提升效率,越多的条件约束,速率会更快。
优化查询语句,有时可以通过界面查询条件,排查因为哪个条件选了全部,少了条件约束,导致查询变慢,找出来后,可以通过关联表找出更多的条件来约束它,或者直接添加限定查询选项,去除全部。
开发例子如下:
StringBuffer sql = new StringBuffer("select c.id,s.CODE studentCode,s.NAME studentName,co.SCHOOLYEAR,co.SEMESTER,cs.NAME courseName,cs.CREDIT,c.MONEY,c.ISPAID,c.ISDELETE,c.HAVEDETAIL from CEDUCOURSETUITIONDETAIL c ")
.append("inner join COURSEREGISTER co on c.COURSEREGISTER=co.ID and co.SCHOOLYEAR=? and co.SEMESTER=? ")
.append("inner join STUDENT s on co.STUDENT=s.ID and s.grade=? ")
.append("inner join SPECIALITY sp on s.SPECIALITY=sp.ID ")
.append("inner join DEPARTMENT d on sp.TEACHING_DEPT=d.ID and d.ID=9 ")
.append("inner join COURSE cs on c.COURSE=cs.ID where c.HAVEDETAIL=? and c.ISPAID=? ");
//额外添加的查询条件
List conditionList=new ArrayList();
if(speciality_id!=0){
sql.append("and s.speciality=? ");
conditionList.add(new Integer(speciality_id));
}
if(!code.equals("")){
sql.append("and s.code=? ");
conditionList.add(code);
}
sql.append("order by s.code");
preStmt = con.prepareStatement(sql.toString());
preStmt.setInt(1, Integer.parseInt(yearTerm.substring(0, 4)));
preStmt.setInt(2, Integer.parseInt(yearTerm.substring(4, 5)));
preStmt.setString(3, grade);
preStmt.setString(4, haveDetail);
preStmt.setString(5, isPaid);
ConnectionUtil.fillStatement(preStmt, 6, conditionList.toArray());
rs = preStmt.executeQuery();
上述语句中本来可以去除inner join DEPARTMENT d on sp.TEACHING_DEPT=d.ID and d.ID=9 就能查询出结果,因为CEDUCOURSETUITIONDETAIL作为查询主表,里面的数据只有部门id为9的数据,去除后发现当speciality_id没有进行约束查询时,速度会特别慢,因为没有了speciality_id和d.ID=9的约束,数据连接查询量会大大增加,所以需要再加上d.ID=9的约束,防止speciality_id没有进行约束导致查询变慢的情况。