hibernate N+1 问题的总结

ゝ一纸荒年。 2022-07-03 14:57 130阅读 0赞

## hibernate N+1 问题的总结 ##

在hibernate中的一对多或者多对一中查询一的一方或者多的一方都会出现N+1的问题。比如Customer和Linkman为一对多的问题。查询Customer的时候也会吧Customer对应的Linkman也查出来，对查询Linkman也一样。  
iterator 查询时,一定先去缓存中找（1条sql查集合,只查出ID），在没命中时，会再按ID到库中逐一查找， 产生1+n条SQL

@Test
        public void testListCustomer() {
            SessionFactory sessionFactory = null;
            Session session = null;
            try {
                //得到sessionFactory
                sessionFactory = HibernateUtils.getSessionFactory();
                //得到session
                session = sessionFactory.openSession();
                /*<set name="setLinkMan" cascade="save-update,delete" lazy="false">  <key column="clid"></key>  <one-to-many class="cn.itcast.entity.LinkMan" /> </set>*/
                //这样的配置会有N+1问题。lazy="false"设置不使用懒加载。 hibernate3以后的版本默认为lazy="true"
                Query query = session.createQuery("from Customer");
                List<Customer> customerList = (List<Customer>) query.list();
                System.out.println(customerList);
            }catch(Exception e) {
                e.printStackTrace();
            }finally {
                session.close();
                //sessionFactory不需要关闭
                sessionFactory.close();
            }
        }

打印出的sql语句:

Hibernate:  
select  
customer0\_.cid as cid1\_0\_,  
customer0\_.custName as custName2\_0\_,  
customer0\_.custLevel as custLeve3\_0\_,  
customer0\_.custSource as custSour4\_0\_,  
customer0\_.custPhone as custPhon5\_0\_,  
customer0\_.custMobile as custMobi6\_0\_  
from  
t\_customer customer0\_  
Hibernate:  
select  
setlinkman0\_.clid as clid5\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_1\_,  
setlinkman0\_.lkm\_name as lkm\_name2\_1\_1\_,  
setlinkman0\_.lkm\_gender as lkm\_gend3\_1\_1\_,  
setlinkman0\_.lkm\_phone as lkm\_phon4\_1\_1\_,  
setlinkman0\_.clid as clid5\_1\_1\_  
from  
t\_linkman setlinkman0\_  
where  
setlinkman0\_.clid=?  
Hibernate:  
select  
setlinkman0\_.clid as clid5\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_1\_,  
setlinkman0\_.lkm\_name as lkm\_name2\_1\_1\_,  
setlinkman0\_.lkm\_gender as lkm\_gend3\_1\_1\_,  
setlinkman0\_.lkm\_phone as lkm\_phon4\_1\_1\_,  
setlinkman0\_.clid as clid5\_1\_1\_  
from  
t\_linkman setlinkman0\_  
where  
setlinkman0\_.clid=?  
Hibernate:  
select  
setlinkman0\_.clid as clid5\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_1\_,  
setlinkman0\_.lkm\_name as lkm\_name2\_1\_1\_,  
setlinkman0\_.lkm\_gender as lkm\_gend3\_1\_1\_,  
setlinkman0\_.lkm\_phone as lkm\_phon4\_1\_1\_,  
setlinkman0\_.clid as clid5\_1\_1\_  
from  
t\_linkman setlinkman0\_  
where  
setlinkman0\_.clid=?  
Hibernate:  
select  
setlinkman0\_.clid as clid5\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_1\_,  
setlinkman0\_.lkm\_name as lkm\_name2\_1\_1\_,  
setlinkman0\_.lkm\_gender as lkm\_gend3\_1\_1\_,  
setlinkman0\_.lkm\_phone as lkm\_phon4\_1\_1\_,  
setlinkman0\_.clid as clid5\_1\_1\_  
from  
t\_linkman setlinkman0\_  
where  
setlinkman0\_.clid=?  
Hibernate:  
select  
setlinkman0\_.clid as clid5\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_0\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_1\_,  
setlinkman0\_.lkm\_name as lkm\_name2\_1\_1\_,  
setlinkman0\_.lkm\_gender as lkm\_gend3\_1\_1\_,  
setlinkman0\_.lkm\_phone as lkm\_phon4\_1\_1\_,  
setlinkman0\_.clid as clid5\_1\_1\_  
from  
t\_linkman setlinkman0\_  
where  
setlinkman0\_.clid=?  
这里只需要设置lazy=”true”，在hibernate3以后的版本  
默认lazy=”true”，所以不需要再配置。  
还可以设置：

batch-size=”10”来确定一次性抓取的条数

sql语句：  
Hibernate:  
select  
customer0\_.cid as cid1\_0\_,  
customer0\_.custName as custName2\_0\_,  
customer0\_.custLevel as custLeve3\_0\_,  
customer0\_.custSource as custSour4\_0\_,  
customer0\_.custPhone as custPhon5\_0\_,  
customer0\_.custMobile as custMobi6\_0\_  
from  
t\_customer customer0\_  
Hibernate:  
select  
setlinkman0\_.clid as clid5\_1\_1\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_1\_,  
setlinkman0\_.lkm\_id as lkm\_id1\_1\_0\_,  
setlinkman0\_.lkm\_name as lkm\_name2\_1\_0\_,  
setlinkman0\_.lkm\_gender as lkm\_gend3\_1\_0\_,  
setlinkman0\_.lkm\_phone as lkm\_phon4\_1\_0\_,  
setlinkman0\_.clid as clid5\_1\_0\_  
from  
t\_linkman setlinkman0\_  
where  
setlinkman0\_.clid in (  
?, ?, ?, ?, ?  
)

在多的一方。

@Test
        public void testLinkMan() {
            SessionFactory sessionFactory = null;
            Session session = null;
            try {
                //得到sessionFactory
                sessionFactory = HibernateUtils.getSessionFactory();
                //得到session
                session = sessionFactory.openSession();
                /*Query query = session.createQuery("from Customer"); List<Customer> customerList = (List<Customer>) query.list();*/
                Query query = session.createQuery("from LinkMan l left join fetch l.customer");
                List<LinkMan> linkManList = (List<LinkMan>) query.list();
                /*for(Customer customer : customerList){ Set<LinkMan> linkManList = customer.getSetLinkMan(); System.out.println(linkManList); }*/
                /*SQLQuery query = session.createSQLQuery("select * from Customer").addEntity(Customer.class); List<Customer> customerList = query.list(); System.out.println(customerList);*/
    
            }catch(Exception e) {
                e.printStackTrace();
            }finally {
                session.close();
                //sessionFactory不需要关闭
                sessionFactory.close();
            }
        }

打印出来的sql语句：

Hibernate:  
select  
linkman0\_.lkm\_id as lkm\_id1\_1\_0\_,  
customer1\_.cid as cid1\_0\_1\_,  
linkman0\_.lkm\_name as lkm\_name2\_1\_0\_,  
linkman0\_.lkm\_gender as lkm\_gend3\_1\_0\_,  
linkman0\_.lkm\_phone as lkm\_phon4\_1\_0\_,  
linkman0\_.clid as clid5\_1\_0\_,  
customer1\_.custName as custName2\_0\_1\_,  
customer1\_.custLevel as custLeve3\_0\_1\_,  
customer1\_.custSource as custSour4\_0\_1\_,  
customer1\_.custPhone as custPhon5\_0\_1\_,  
customer1\_.custMobile as custMobi6\_0\_1\_  
from  
t\_linkman linkman0\_  
left outer join  
t\_customer customer1\_  
on linkman0\_.clid=customer1\_.cid

![这里写图片描述][SouthEast]  
我在set上面lazy=”true” 在上面lazy=”proxy”但是还是会出现N+1的问题，这个还有带探索哈！

[SouthEast]: /images/20220618/ce5f87b16e924cccba86cce6238723cb.png