mysql hibernate.jdbc.fetch_size_Hibernate 抓取策略fetch-2 (批量抓取batch-size以及hibernate.jdbc.fetch_size、hib...

类关系:　　User N~1 Group

测试代码:

System.out.println("1");

List stuList = session.createQuery("from User s where s.password ='123'").list(); //(1) System.out.println("2");

for(Iterator it = stuList.iterator(); it.hasNext();){

User stu = (User)it.next();

System.out.println("3");

System.out.println("[value]"+stu.getName()); //(2) System.out.println("[value]"+stu.getGroup().getName()); //(3) }

执行效果:

(1)查询符合条件的User集合. 发出的SQL为 select  * from test_User where password='123'

(2)不再发出SQL. 使用缓存中的User数据.

(3)迭代User时,检查是否加载过对应User的Group,若无则发出一次SQL:  select * from test_group where group_id=?

如果User有10个.对应在3个group中. 则发出3条select * from test_group where group_id=?

1

Hibernate:

select

user0_.id as id0_,

user0_.name as name0_,

user0_.password as password0_,

user0_.createTime as createTime0_,

user0_.updateTime as updateTime0_,

user0_.group_id as group6_0_

from

TEST_User user0_

where

user0_.password='123'

2

3

[value]jordan

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团2

3

[value]kobe

[value]集团2

3

[value]james

[value]集团2

//以下继续开始循环遍历

3

[value]张三111

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团1

3

[value]李四111

[value]集团1

3

[value]李四111

[value]集团1

3

[value]AAA

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团3

3

[value]BBB

[value]集团3

3

[value]CCC

[value]集团3

3

[value]a1

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团4

3

[value]a11

[value]集团4

3

[value]a111

[value]集团4

3

[value]b1

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团5

3

[value]b11

[value]集团5

3

[value]b111

[value]集团5

配置batch-size

执行效果

(1)(2)同上

(3)在遍历User,并且获取对应的Group时.检测是否加载过对应的数据.若无根据设置的batch-size=2

发出SQL语句: select * from test_group where group_id in (?,?)  一次查询2个Group放到内存中.

在此遍历User及其Group时,若内存中无加载过对应数据,则再次发出SQL  select * from test_group where group_id in (?,?)  一次查询2个Group放到内存中.

如Group的数据最后不足.则使用select * from test_group where group_id =? 进行加载

即如Group 有5个. 则通过2个2个1个的方式进行查询获取到.

1

Hibernate:

select

user0_.id as id0_,

user0_.name as name0_,

user0_.password as password0_,

user0_.createTime as createTime0_,

user0_.updateTime as updateTime0_,

user0_.group_id as group6_0_

from

TEST_User user0_

where

user0_.password='123'2

3

[value]jordan

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id in (

?, ?

)[value]集团2

3

[value]kobe

[value]集团2

3

[value]james

[value]集团2

3

[value]张三111

[value]集团1

3

[value]李四111

[value]集团1

3

[value]李四111

[value]集团1

3

[value]AAA

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id in (

?, ?

)[value]集团3

3

[value]BBB

[value]集团3

3

[value]CCC

[value]集团3

3

[value]a1

[value]集团4

3

[value]a11

[value]集团4

3

[value]a111

[value]集团4

3

[value]b1

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_ where

group0_.id=?[value]集团5

3

[value]b11

[value]集团5

3

[value]b111

[value]集团5

P.S 注意如果使用iterate进行迭代而非List ,则Batch-size无效.

如下

System.out.println("1");

Iterator iter = session.createQuery(" from Group g where g.name like'集团%'").iterate(); //(1) System.out.println("2");

while (iter.hasNext()) {

System.out.println("3");

Group g = (Group) iter.next();

System.out.println("4");

System.out.println("[value]" + g.getName()); //(2) System.out.println("5");

System.out.println("[value]" + g.getUsers().size()); //(3) }

执行效果

原因的出现是因为

(1)处使用了iterate返回集合.iterate默认会开始发出一条语句：查询所有记录符合条件的user的ID. select id from user where password =?

因为(1)只查询出id , 所以在(2)时. 需要再发出一条SQL获取user的属性数据. select * from user where id=?

这就是iterate很出名的N+1问题.

至于(3)处, 如果group没加载过,每次都会发出一条SQL.select * from group where id=?

所以此情况下, batch-size已无法达到预期效果.

1

Hibernate:

select

user0_.id as col_0_0_

from

TEST_User user0_

where

user0_.password='123'2

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]jordan

5

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团2

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]kobe

5

[value]集团2

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]james

5

[value]集团2

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]张三111

5

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团1

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]李四111

5

[value]集团1

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]李四111

5

[value]集团1

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]AAA

5

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团3

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]BBB

5

[value]集团3

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]CCC

5

[value]集团3

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]a1

5

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团4

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]a11

5

[value]集团4

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]a111

5

[value]集团4

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]b1

5

Hibernate:

select

group0_.id as id1_0_,

group0_.name as name1_0_

from

TEST_Group group0_

where

group0_.id=?

[value]集团5

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]b11

5

[value]集团5

3

4

Hibernate:

select

user0_.id as id0_0_,

user0_.name as name0_0_,

user0_.password as password0_0_,

user0_.createTime as createTime0_0_,

user0_.updateTime as updateTime0_0_,

user0_.group_id as group6_0_0_

from

TEST_User user0_

where

user0_.id=?

[value]b111

5

[value]集团5View Code

hibernate.jdbc.fetch_size 50 //读

hibernate.jdbc.batch_size 30 //写

hiberante.cfg.xml(Oracle ,sql server 支持，mysql不支持)

配置文件参考如下：

50

30

这两个选项非常非常非常重要！！！将严重影响Hibernate的CRUD性能!

C = create, R = read, U = update, D = delete

Fetch Size 是设定JDBC的Statement读取数据的时候每次从数据库中取出的记录条数。

例如一次查询1万条记录，对于Oracle的JDBC驱动来说，是不会1次性把1万条取出来的，而只会取出Fetch Size条数，当纪录集遍历完了这些记录以后，再去数据库取Fetch Size条数据。因此大大节省了无谓的内存消耗。当然Fetch Size设的越大，读数据库的次数越少，速度越快；Fetch Size越小，读数据库的次数越多，速度越慢。这有点像平时我们写程序写硬盘文件一样，设立一个Buffer，每次写入Buffer，等Buffer满了以后，一次写入硬盘，道理相同。

Oracle数据库的JDBC驱动默认的Fetch Size=10，是一个非常保守的设定，根据我的测试，当Fetch Size=50的时候，性能会提升1倍之多，当Fetch Size=100，性能还能继续提升20%，Fetch Size继续增大，性能提升的就不显著了。

因此我建议使用Oracle的一定要将Fetch Size设到50。

不过并不是所有的数据库都支持Fetch Size特性，例如MySQL就不支持。

MySQL就像我上面说的那种最坏的情况，他总是一下就把1万条记录完全取出来，内存消耗会非常非常惊人！这个情况就没有什么好办法了 :(

Batch Size是设定对数据库进行批量删除，批量更新和批量插入的时候的批次大小，有点相当于设置Buffer缓冲区大小的意思。

Batch Size越大，批量操作的向数据库发送sql的次数越少，速度就越快。我做的一个测试结果是当Batch Size=0的时候，使用Hibernate对Oracle数据库删除1万条记录需要25秒，Batch Size = 50的时候，删除仅仅需要5秒！！！

我们通常不会直接操作一个对象的标识符(identifier)， 因此标识符的setter方法应该被声明为私有的(private)。这样当一个对象被保存的时候，只有Hibernate可以为它分配标识符。 你会发现Hibernate可以直接访问被声明为public，private和protected等不同级别访问控制的方法(accessor method)和字段(field)。 所以选择哪种方式来访问属性是完全取决于你，你可以使你的选择与你的程序设计相吻合。

所有的持久类(persistent classes)都要求有无参的构造器(no-argument constructor)； 因为Hibernate必须要使用Java反射机制(Reflection)来实例化对象。构造器(constructor)的访问控制可以是私有的(private)， 然而当生成运行时代理(runtime proxy)的时候将要求使用至少是package级别的访问控制，这样在没有字节码编入 (bytecode instrumentation)的情况下，从持久化类里获取数据会更有效率一些。

而 hibernate.max_fetch_depth设置外连接抓取树的最大深度

取值. 建议设置为0到3之间

就是每次你在查询时，会级联查询的深度，譬如你对关联vo设置了eager的话，如果fetch_depth值太小的话，会发多很多条sql