原来的系统性能慢,今天在做性能调优,发现有一个多表查询存在性能问题。
我的hbm.xml配置文件
[code="java"]
[/code]
查询语句:
[code="java"] Criteria criteria = getSession().createCriteria(Shop.class, "s")
.createAlias("s.shopHours", "h", JoinFragment.LEFT_OUTER_JOIN)
.createAlias("s.shopRate", "r", JoinFragment.LEFT_OUTER_JOIN)
.createAlias("s.shopConditions", "c", JoinFragment.LEFT_OUTER_JOIN)
.add(Restrictions.eq("s.shopCity", cityName))
.add(Restrictions.eq("s.shopActived", true))
.add(Restrictions.eq("s.shopPublic", true))
.addOrder(Order.asc("s.shopNum"))
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
return criteria.list();[/code]
调试中的sql 日志:
[code="java"]
Hibernate: /* load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions / select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?
Hibernate: / load wm.model.ShopRate / select shoprate0_.shop_num as shop1_36_0_, shoprate0_.taste_rating as taste2_36_0_, shoprate0_.service_rating as service3_36_0_, shoprate0_.response_rating as response4_36_0_, shoprate0_.total_rating as total5_36_0_, shoprate0_.reviews_count as reviews6_36_0_ from shop_rate shoprate0_ where shoprate0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopHours / select shophours0_.shop_num as shop2_1_, shophours0_.shop_hour_id as shop1_1_, shophours0_.shop_hour_id as shop1_23_0_, shophours0_.shop_num as shop2_23_0_, shophours0_.time_from as time3_23_0_, shophours0_.time_to as time4_23_0_ from shop_hour shophours0_ where shophours0_.shop_num=?
Hibernate: / load one-to-many wm.model.Shop.shopConditions */ select shopcondit0_.shop_num as shop2_1_, shopcondit0_.condition_id as condition1_1_, shopcondit0_.condition_id as condition1_37_0_, shopcondit0_.shop_num as shop2_37_0_, shopcondit0_.condition_mile as condition3_37_0_, shopcondit0_.condition_money as condition4_37_0_, shopcondit0_.condition_amount as condition5_37_0_, shopcondit0_.condition_response_time as condition6_37_0_ from shop_condition shopcondit0_ where shopcondit0_.shop_num=?[/code]
从上述日志,我发现hibernate join查询后,还是重新做了select查询,调试了半天,没有什么眉目,希望有高人指导,谢谢!
补充:
两个与查询有关的配置参数
hibernate.max_fetch_depth 3
hibernate.default_batch_fetch_size 8
[b]问题补充:[/b]
fetch 方式 select,subselect,join均试过,打印出来的sql语句相同,均存在n+1查询问题。
另外,一对多是可空的,即多的一端可以有一个或多个,也可以不存在。
另外,在创建critia时,已经设定了以做左连接方式join查询(JoinFragment.LEFT_OUTER_JOIN),应该来说会覆盖hbm配置文件中的设定。
[b]问题补充:[/b]
现在的我的使用场景中,必须设置lazy=false,否则代码调整量比较大的。
[b]问题补充:[/b]
我的问题主要有一个疑惑:从上述SQL语句中我们发现,hibernate确实已经使用了join方式查询,为什么还需要重新select呢?