Hibernate查询性能优化（多对多关联查询）

之前网上购买了一套SpringMVC+Hibernate+mybatis+shiro等常用框架集成的系统源码。搭建好环境，跑起来后发现不错，办公的基本功能都有。

于是就把公司现在OA系统的用户导入了新系统。总共也就导入了1000多条数据，然后发现系统运行的没那么流畅了。

尤其是用户管理页面，分页查询才显示10条记录，需要耗时40秒左右。于是debug弄了一下午，终于找到原因了。

下面是调试发现的查询的Criteria：

CriteriaImpl(com.framework.sys.entity.User:this[Subcriteria(company:company), Subcriteria(office:office)][office.id=1 OR office.parentIds LIKE %,1,%, (), delFlag=0])

执行的SQL语句（相关参数处理过，把问号去掉了，可以在mysql客户端直接执行了）：

SELECT userlist0_.role_id AS role_id2_102_4_, userlist0_.user_id AS user_id1_106_4_, user1_.id AS id1_105_0_, user1_.create_by AS create_17_105_0_, user1_.create_date AS create_d2_105_0_, user1_.del_flag AS del_flag3_105_0_, user1_.remarks AS remarks4_105_0_, user1_.update_by AS update_18_105_0_, user1_.update_date AS update_d5_105_0_, user1_.company_id AS company19_105_0_, user1_.email AS email6_105_0_, user1_.login_date AS login_da7_105_0_, user1_.login_ip AS login_ip8_105_0_, user1_.login_name AS login_na9_105_0_, user1_.mobile AS mobile10_105_0_, user1_.name AS name11_105_0_, user1_.no AS no12_105_0_, user1_.office_id AS office_20_105_0_, user1_.password AS passwor13_105_0_, user1_.phone AS phone14_105_0_, user1_.photo AS photo15_105_0_, user1_.user_type AS user_ty16_105_0_, office2_.id AS id1_101_1_, office2_.create_by AS create_17_101_1_, office2_.create_date AS create_d2_101_1_, office2_.del_flag AS del_flag3_101_1_, office2_.remarks AS remarks4_101_1_, office2_.update_by AS update_18_101_1_, office2_.update_date AS update_d5_101_1_, office2_.address AS address6_101_1_, office2_.area_id AS area_id19_101_1_, office2_.code AS code7_101_1_, office2_.email AS email8_101_1_, office2_.fax AS fax9_101_1_, office2_.grade AS grade10_101_1_, office2_.master AS master11_101_1_, office2_.name AS name12_101_1_, office2_.parent_id AS parent_20_101_1_, office2_.parent_ids AS parent_13_101_1_, office2_.phone AS phone14_101_1_, office2_.type AS type15_101_1_, office2_.zip_code AS zip_cod16_101_1_, area3_.id AS id1_96_2_, area3_.create_by AS create_10_96_2_, area3_.create_date AS create_d2_96_2_, area3_.del_flag AS del_flag3_96_2_, area3_.remarks AS remarks4_96_2_, area3_.update_by AS update_11_96_2_, area3_.update_date AS update_d5_96_2_, area3_.code AS code6_96_2_, area3_.name AS name7_96_2_, area3_.parent_id AS parent_12_96_2_, area3_.parent_ids AS parent_i8_96_2_, area3_.type AS type9_96_2_, office4_.id AS id1_101_3_, office4_.create_by AS create_17_101_3_, office4_.create_date AS create_d2_101_3_, office4_.del_flag AS del_flag3_101_3_, office4_.remarks AS remarks4_101_3_, office4_.update_by AS update_18_101_3_, office4_.update_date AS update_d5_101_3_, office4_.address AS address6_101_3_, office4_.area_id AS area_id19_101_3_, office4_.code AS code7_101_3_, office4_.email AS email8_101_3_, office4_.fax AS fax9_101_3_, office4_.grade AS grade10_101_3_, office4_.master AS master11_101_3_, office4_.name AS name12_101_3_, office4_.parent_id AS parent_20_101_3_, office4_.parent_ids AS parent_13_101_3_, office4_.phone AS phone14_101_3_, office4_.type AS type15_101_3_, office4_.zip_code AS zip_cod16_101_3_ 
FROM sys_user_role userlist0_ 
INNER JOIN sys_user user1_ ON userlist0_.user_id=user1_.id 
INNER JOIN sys_office office2_ ON user1_.company_id=office2_.id 
LEFT OUTER JOIN sys_area area3_ ON office2_.area_id=area3_.id 
INNER JOIN sys_office office4_ ON user1_.office_id=office4_.id 
WHERE  ( user1_.del_flag='0') 
	AND userlist0_.role_id IN (
			SELECT role1_.id FROM sys_user_role rolelist0_ 
			INNER JOIN sys_role role1_ ON rolelist0_.role_id=role1_.id 
			LEFT OUTER JOIN sys_office office2_ ON role1_.office_id=office2_.id 
			LEFT OUTER JOIN sys_area area3_ ON office2_.area_id=area3_.id 
			WHERE  ( role1_.del_flag='0') AND rolelist0_.user_id IN (
						SELECT this_.id FROM sys_user this_ 
						INNER JOIN sys_office company1_ ON this_.company_id=company1_.id 
						LEFT OUTER JOIN sys_area area5_ ON company1_.area_id=area5_.id 
						INNER JOIN sys_office office2_ ON this_.office_id=office2_.id 
						WHERE (office2_.id=1 OR office2_.parent_ids LIKE '') AND 1=1 AND this_.del_flag=0 ) ) 
ORDER BY user1_.id

分析sql发现是从sys_user_role开始查询，而用户列表应该和角色没关系才对啊。并且主要是后面那些in条件导致查询变慢。但是根据CriteriaImpl，并没有设置那些筛选条件，为什么会出现多层嵌套的in条件过滤呢？

最后发现是因为定义User和Role实体中的注解@ManyToMany是两边都是定义的饥渴模式。把他们都改为lazy模式就好了。

改了后发现速度果然快了很多。并且点击下一页时是直接从缓存中获取，并没有显示sql。原来系统还对用户数据做了二级缓存。

但是@ManyToMany定义的饥渴模式时，好像即使有二级缓存，每次查询时依然会去数据库中查询，速度好慢。猜测可能饥渴模式会导致二级缓存失效？