Hibernate 安插海量数据时的性能 与 Jdbc 的比较

mysql> select version();
--------------
select version()
--------------

+----------------------+
| version()            |
+----------------------+
| 5.1.41-community-log |
+----------------------+
1 row in set (0.00 sec)

--launcher.XXMaxPermSize
256M
-showsplash
org.eclipse.platform
--launcher.XXMaxPermSize
256m
-vm
C:/Program Files/Java/jdk1.6.0_18/bin/javaw
-vmargs
-Dosgi.requiredJavaVersion=1.5
-Xms40m
-Xmx512m

package com.model;

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author <a href="liu.anxin13@gmail.com">Tony</a>
 */
@Entity
@Table(name = "T_USERINFO")
@org.hibernate.annotations.Entity(selectBeforeUpdate = true, dynamicInsert = true, dynamicUpdate = true)
public class UserInfo implements Serializable {

	private static final long serialVersionUID = -4855456169220894250L;

	@Id
	@Column(name = "ID", length = 32)
	private String id = java.util.UUID.randomUUID().toString().replaceAll("-", "");

	@Column(name = "CREATE_TIME", updatable = false)
	private Timestamp createTime = new Timestamp(System.currentTimeMillis());

	@Column(name = "UPDATE_TIME", insertable = false)
	private Timestamp updateTime = new Timestamp(System.currentTimeMillis());
	// setter/getter...
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans-2.5.xsd 
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context-2.5.xsd 
		http://www.springframework.org/schema/tx 
		http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">

	<context:component-scan base-package="com.dao,com.service" />

	<context:property-placeholder location="classpath:jdbc.properties" />

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		
		<property name="maxPoolSize" value="50" />
		<property name="minPoolSize" value="2" />
		<property name="initialPoolSize" value="5" />
		<property name="acquireIncrement" value="5" />
		<property name="maxIdleTime" value="1800" />
		<property name="idleConnectionTestPeriod" value="1800" />
		<property name="maxStatements" value="1000"/>
		<property name="breakAfterAcquireFailure" value="true" />
		<property name="testConnectionOnCheckin" value="true" />
		<property name="testConnectionOnCheckout" value="false" />
	</bean>
	
	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource"/>
		<property name="configLocation" value="classpath:hibernate.cfg.xml" />
	</bean>
	
	<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>
	
	<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>
	
	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="*" isolation="READ_COMMITTED" rollback-for="Throwable" />
		</tx:attributes>
	</tx:advice>
	
	<aop:config>
		<aop:pointcut id="services" expression="execution(* com.service.*.*.*.*(..))" />
		<aop:advisor advice-ref="txAdvice" pointcut-ref="services" />
	</aop:config>

</beans>

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

	<session-factory>

		<property name="hibernate.dialect">
			org.hibernate.dialect.MySQLDialect
		</property>
		
		<!--
			默认 15, 这是我个人不能完全理解的参数, 不明白这个参数的实际意义
		-->
		<!-- <property name="hibernate.jdbc.batch_size">50</property> -->
		
		<!-- 排序插入和更新, 避免出现 锁si -->
		<property name="hibernate.order_inserts">true</property>
		<property name="hibernate.order_updates">true</property>

		<property name="hibernate.hbm2ddl.auto">update</property>

		<property name="hibernate.show_sql">false</property>
		<property name="hibernate.format_sql">false</property>
		
		<property name="hibernate.current_session_context_class">
			org.hibernate.context.JTASessionContext
		</property>

		<mapping class="com.model.UserInfo" />

	</session-factory>

</hibernate-configuration>

log4j.rootLogger=INFO, CONS
log4j.appender.CONS=org.apache.log4j.ConsoleAppender
log4j.appender.CONS.layout=org.apache.log4j.PatternLayout
log4j.appender.CONS.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss sss} [%p] %r %l [%t] %x - %m%n

# log4j.logger.org.springframework=WARN
# log4j.logger.org.hibernate=WARN
# log4j.logger.com.mchange=WARN

package com.dao;

import java.io.Serializable;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateTemplate;

@Repository
public class HibernateDAO {

	private static final Logger log = Logger.getLogger(HibernateDAO.class);

	@Autowired
	private HibernateTemplate template;

	public Serializable save(Object entity) {
		try {
			return template.save(entity);
		} catch (Exception e) {
			log.info("save exception : " + e.getMessage());
			// 异常的目的只是为了记录日志
			throw new RuntimeException(e);
		}
	}

}

package com.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.dao.HibernateDAO;
import com.model.UserInfo;

@Service("commonService")
public class CommonService {

	private static final Logger log = Logger.getLogger(CommonService.class);

	@Autowired
	private HibernateDAO dao;

	public void testOcean(long num) {
		for (int i = 0; i < num; i++) {
			// 在循环中 new 大量对象. 此为 outOfMemory 的根源
			// 将 user 申明在 循环外面, 循环体内部才将其指向具体的对象, 这样可以提高一点效率
			UserInfo user = new UserInfo();
			dao.save(user);
			
			user = null;
			// 实际意义不大, 就算手动运行, gc 也是异步的, 没有办法控制
			if (i % 100000 == 0)
				System.gc();
			/*
			if (i % 30 == 0) {
				dao.flush();
				dao.clear();
			}
			*/
		}
	}
	
}

package com;

import org.apache.log4j.Logger;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.service.CommonService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "/applicationContext.xml" })
public class TestHibernate {

	@Autowired
	@Qualifier("commonService")
	private CommonService service;
	
	private static final Logger log = Logger.getLogger(TestHibernate.class);
	
	@Test
	public void testHibernate() {
		// 海量数据的条数. 分别以 1W, 10W, 50W, 100W 为数值
		// 虽然都算不上海量, 但做为测试也应该算够了吧
		long num = 10000;
		
		log.info("开始");
		long begin = System.currentTimeMillis();
		service.testOcean(num);

		long end = System.currentTimeMillis();
		log.info("耗费时间: " + num + " 条. " + (end - begin) / 1000.00000000 + " 秒");
		System.out.println("赶紧查看内存");
		try {
			Thread.sleep(10000);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}

Hibernate: 
    insert 
    into
        T_USERINFO
        (CREATE_TIME,  ID) 
    values
        (?, ?)

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource" />
</bean>

package com.dao;

import java.sql.Connection;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class JdbcDAO {
	
	private static final Logger log = Logger.getLogger(JdbcDAO.class);
	
	@Autowired
	private JdbcTemplate template;
	
	public Connection getConn() {
		try {
			return template.getDataSource().getConnection();
		} catch (Exception e) {
			log.info("获取连接时异常: " + e.getMessage());
			throw new RuntimeException(e);
		}
	}

}

package com.service;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.dao.JdbcDAO;
import com.model.UserInfo;

@Service("jdbcService")
public class JdbcService {
	
	private static final Logger log = Logger.getLogger(JdbcService.class);
	
	@Autowired
	private JdbcDAO dao;

	public void testOcean(long num) {
		Connection conn = dao.getConn();
		try {
			conn.setAutoCommit(false);
			String sql = "insert into T_USERINFO(CREATE_TIME, ID) values(?, ?)";
			PreparedStatement pstm = conn.prepareStatement(sql);
			for (int i = 0; i < num; i++) {
				// 要保证公平, 也在循环中 new 对象
				UserInfo user = new UserInfo();
				pstm.setTimestamp(1, user.getCreateTime());
				pstm.setString(2, user.getId());
				pstm.execute();
				
				user = null;
				if (i % 10000 == 0)
					System.gc();
				// 批处理
				/*
				if (i % 30 == 0) {
					pstm.executeBatch();
					conn.commit();
					pstm.clearBatch();
				}
				*/

			}
			// 将循环里面的批处理解开后, 就要将此处的commit注释, 并将下面注释的语句解开
			conn.commit();
			// pstm.executeBatch();
		} catch (Exception e) {
			log.info("异常: " + e.getMessage());
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				conn = null;
			}
		}
	}

}

package com;

import org.apache.log4j.Logger;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.service.JdbcService;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "/applicationContext.xml" })
public class TestJdbc {
	
	private static final Logger log = Logger.getLogger(TestJdbc.class);
	
	@Autowired
	@Qualifier("jdbcService")
	private JdbcService service;

	@Test
	public void testJdbc() {
		long num = 500000;
		
		log.info("开始");
		long begin = System.currentTimeMillis();
		service.testOcean(num);
		long end = System.currentTimeMillis();
		log.info("耗费时间: " + num + " 条. " + (end - begin) / 1000.00000000 + " 秒");
		
		System.out.println("赶紧查看内存");
		try {
			Thread.sleep(10000);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

// 实际意义不大, 就算手动运行, gc 也是异步的, 没有办法控制
if (i % 10000 == 0)   
    System.gc(); 

for (int i = 0; i < num; i++) {
	// 要保证公平, 也在循环中 new 对象
	UserInfo user = new UserInfo();
	pstm.setTimestamp(1, user.getCreateTime());
	pstm.setString(2, user.getId());

	pstm.addBatch();

	user = null;
	if (i % 10000 == 0)
		System.gc();

	// 批处理
	if (i % 30 == 0) {
		pstm.executeBatch();
		conn.commit();
		pstm.clearBatch();
	}

}
pstm.executeBatch();
conn.commit();

4 楼 bushkarl 2010-08-23