Hibernate查询优化 HQL提高效率

Hibernate查询性能优化

1、在使用Hibernate持久化框架在方便解决dao层数据操作的同时，在高并发下其sql执行效率也是应该多考虑的问题。最近在调优过程中发现，其实大部分简单的HQL都可以优化和简化，达到程序处理业务逻辑的同时，也能大大提高程序的并发性能。

要点1：Hibernate默认使用Session范围的一级缓存对同一Session生命周期的持久化对象进行缓存，以key-value形式存在，直到生命周期的结束，比如通过save的对象，同一个session请求范围内，再次通过id就能直接在缓存中得到该对象。至于Session生命周期，在注解类的应用中，Sesseion在进入事务后拦截创建，直到事务结束。

要点2：Hibernate的懒加载价值应该注意，带来方便的同时，也应该防止在较多关联应用中带来的复杂SQL查询二降低性能。比如以下的几个对象：

机构：

public class Institution extends Unit implements Serializable {@Column(name = "[RELEASE]")@Versionprivate Integer release = Integer.valueOf(0);/** * 唯一标识 */@Id@Column(name = "SID", length = 32)@GeneratedValue(generator = "system-uuid")@GenericGenerator(name = "system-uuid", strategy = "uuid.hex")private String id;/** * 机构代码 */@Column(length = 60)private String code;/** * 机构名称 */@Column(length = 150)private String name;/** * 描述 */@Column(name = "[desc]")private String desc;@ManyToOne@Index(name = "INSTITUTION_P_IDX")private Institution parent;@OneToMany(cascade = CascadeType.ALL, mappedBy = "parent")private Set<Institution> children = new HashSet<Institution>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionUser> users = new HashSet<InstitutionUser>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionRole> roles = new HashSet<InstitutionRole>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionSystemManager> systemManagers = new HashSet<InstitutionSystemManager>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionTaskGroup> taskGroups = new HashSet<InstitutionTaskGroup>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionTask> tasks = new HashSet<InstitutionTask>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionSheet> sheets = new HashSet<InstitutionSheet>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<MenuInstitution> menus = new HashSet<MenuInstitution>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionConnectionPool> institutionConnectionPools = new HashSet<InstitutionConnectionPool>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionSimpleSQLModel> institutionSimpleSQLModels = new HashSet<InstitutionSimpleSQLModel>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionTreeMenu> institutionTreeMenus = new HashSet<InstitutionTreeMenu>();@OneToMany(cascade = CascadeType.ALL, mappedBy = "institution")private Set<InstitutionRoleType> institutionRoleTypes = new HashSet<InstitutionRoleType>();

用户：略

用户机构：InstitutionUser

@Entitypublic class InstitutionUser implements Serializable {/** * 唯一标识 */@Id@Column(name = "SID", length = 32)@GeneratedValue(generator = "system-uuid")@GenericGenerator(name = "system-uuid", strategy = "uuid.hex")private String id;@ManyToOne@Index(name = "INSTITUTION_USER_IDX")private Institution institution;@ManyToOne@Index(name = "USER_INST_IDX")private User user;public InstitutionUser(){}public InstitutionUser(String institutionId){this.institution = new Institution();this.institution.setId(institutionId);}

在根据用户查询机构等信息时，如果简单些为：

public List<Institution> getInstitution(User user) {String queryString = "From InstitutionUser IU where IU.user.id='" + user.getId() + "'";List<Institution> institutions = new ArrayList<Institution>();List<InstitutionUser> institutionUsers = this.query(queryString);for (InstitutionUser instUser : institutionUsers) {Institution institution = instUser.getInstitution();if (institution != null) {institutions.add(institution);}}return institutions;}

那么调用上述方法时打印的SQL将会有三条，而且查询的数据量比较大：

Hibernate: select institutio0_.SID as SID79_, institutio0_.institution_SID as institut2_79_, institutio0_.user_SID as user3_79_ from JER_INSTITUTIONUSER institutio0_ where institutio0_.user_SID='402855b942099b1b0142099e148d0002'Hibernate: select institutio0_.SID as SID69_1_, institutio0_.cls as cls69_1_, institutio0_.code as code69_1_, institutio0_.COMES as COMES69_1_, institutio0_.`desc` as desc5_69_1_, institutio0_.endData as endData69_1_, institutio0_.icon as icon69_1_, institutio0_.leaf as leaf69_1_, institutio0_.LEVEL_ as LEVEL9_69_1_, institutio0_.name as name69_1_, institutio0_.parent_SID as parent18_69_1_, institutio0_.path as path69_1_, institutio0_.`RELEASE` as RELEASE12_69_1_, institutio0_.startData as startData69_1_, institutio0_.text as text69_1_, institutio0_.upcode as upcode69_1_, institutio0_.url as url69_1_, institutio0_.useless as useless69_1_, institutio1_.SID as SID69_0_, institutio1_.cls as cls69_0_, institutio1_.code as code69_0_, institutio1_.COMES as COMES69_0_, institutio1_.`desc` as desc5_69_0_, institutio1_.endData as endData69_0_, institutio1_.icon as icon69_0_, institutio1_.leaf as leaf69_0_, institutio1_.LEVEL_ as LEVEL9_69_0_, institutio1_.name as name69_0_, institutio1_.parent_SID as parent18_69_0_, institutio1_.path as path69_0_, institutio1_.`RELEASE` as RELEASE12_69_0_, institutio1_.startData as startData69_0_, institutio1_.text as text69_0_, institutio1_.upcode as upcode69_0_, institutio1_.url as url69_0_, institutio1_.useless as useless69_0_ from JER_INSTITUTION institutio0_ left outer join JER_INSTITUTION institutio1_ on institutio0_.parent_SID=institutio1_.SID where institutio0_.SID=?Hibernate: select user0_.SID as SID38_0_, user0_.accountRlace as accountR2_38_0_, user0_.birthDate as birthDate38_0_, user0_.bloodType as bloodType38_0_, user0_.code as code38_0_, user0_.COMES as COMES38_0_, user0_.comparePassword as compareP7_38_0_, user0_.computerCapacity as computer8_38_0_, user0_.dateReserve1 as dateRese9_38_0_, user0_.dateReserve2 as dateRes10_38_0_, user0_.demo as demo38_0_, user0_.desc_ as desc12_38_0_, user0_.eMail as eMail38_0_, user0_.education as education38_0_, user0_.endData as endData38_0_, user0_.entryDate as entryDate38_0_, user0_.foreignLanguageAbility as foreign17_38_0_, user0_.gender as gender38_0_, user0_.healthStatus as healthS19_38_0_, user0_.height as height38_0_, user0_.hobby as hobby38_0_, user0_.idType as idType38_0_, user0_.idValue as idValue38_0_, user0_.instituion as instituion38_0_, user0_.LEVEL_ as LEVEL25_38_0_, user0_.marriageType as marriag26_38_0_, user0_.mobilePhone as mobileP27_38_0_, user0_.name as name38_0_, user0_.nation as nation38_0_, user0_.nativePlace as nativeP30_38_0_, user0_.officePhone as officeP31_38_0_, user0_.password as password38_0_, user0_.passwordUpdata as passwor33_38_0_, user0_.politicsStatus as politic34_38_0_, user0_.position as position38_0_, user0_.post as post38_0_, user0_.speciality as speciality38_0_, user0_.startData as startData38_0_, user0_.state as state38_0_, user0_.strReserve1 as strReserve40_38_0_, user0_.strReserve2 as strReserve41_38_0_, user0_.strReserve3 as strReserve42_38_0_, user0_.strReserve4 as strReserve43_38_0_, user0_.strReserve5 as strReserve44_38_0_, user0_.useless as useless38_0_ from JER_TUSER user0_ where user0_.SID=?

但实际上，我们只要返回他的机构，或者实际应用中机构会在二级缓存中保存，业务操作时我们可能只要机构的id。那么该为以下方式将会大大提高查询效率。

String queryString = "select new InstitutionUser(IU.institution.id) From InstitutionUser IU where IU.user.id='" + user.getId() + "'";

Hibernate: select institutio0_.institution_SID as col_0_0_ from JER_INSTITUTIONUSER institutio0_ where institutio0_.user_SID='402855b942099b1b0142099e148d0002'

当然，上面只是一个简单的实例，但实际需要什么属性，还是根据程序来定，而且还要考虑是否该实体使用了缓存等因素。

对于一个大的应用而言，每个功能模块都尽量优化一下，那么整个应用的并发性能将会大大提高。