mysqlplus 批量插入_[MyBatis]五分钟向MySql数据库插入一千万条数据 批量插入 用时5分左右...

我的数据库环境是mysql Ver 14.14 Distrib 5.6.45, for Linux (x86_64) using EditLine wrapper

这个数据库是安装在T440p的虚拟机上的，操作系统为CentOs6.5.

插入一千万条数据，一次执行时间是4m57s，一次是5m。

数据表的定义是这样的：

CREATE TABLE`emp` (

`Id`int(11) NOT NULLAUTO_INCREMENT,

`name`varchar(255) DEFAULT NULL,

`age`smallint(3) DEFAULT NULL,

`cdate`timestamp NULL DEFAULT NULL COMMENT 'createtime',PRIMARY KEY(`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=13548008 DEFAULT CHARSET=utf8;

这是一个以id为自增主键，包含了三种不同类型字段的简单表。

我使用MyBatis的Batch Insert功能给数据表插入数据，其SQL在Mapper中定义成这样：

/p>

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select id,name,age,cdate as ctime from emp where id=#{id}

insert into emp(name,age,cdate)

values(#{emp.name},#{emp.age},#{emp.ctime,jdbcType=TIMESTAMP})

与之对应的接口类是这样的：

packagecom.hy.mapper;importjava.util.List;importcom.hy.entity.Employee;public interfaceEmpMapper {

Employee selectById(longid);int batchInsert(Listemps);

}

实体类Employee如下：

packagecom.hy.entity;importjava.text.MessageFormat;public classEmployee {private longid;privateString name;private intage;privateString ctime;publicEmployee() {

}public Employee(String name,intage,String ctime) {this.name=name;this.age=age;this.ctime=ctime;

}publicString toString() {

Object[] arr={id,name,age,ctime};

String retval=MessageFormat.format("Employee id={0},name={1},age={2},created_datetime={3}", arr);returnretval;

}public longgetId() {returnid;

}public void setId(longid) {this.id =id;

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}public intgetAge() {returnage;

}public void setAge(intage) {this.age =age;

}publicString getCtime() {returnctime;

}public voidsetCtime(String ctime) {this.ctime =ctime;

}

}

如果插入数据不多可以这样书写：

packagecom.hy.action;importjava.io.Reader;importjava.util.ArrayList;importjava.util.List;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.apache.log4j.Logger;importcom.hy.entity.Employee;importcom.hy.mapper.EmpMapper;public classBatchInsert01 {private static Logger logger = Logger.getLogger(SelectById.class);public static void main(String[] args) throwsException{

Reader reader=Resources.getResourceAsReader("mybatis-config.xml");

SqlSessionFactory ssf=newSqlSessionFactoryBuilder().build(reader);

reader.close();

SqlSession session=ssf.openSession();try{

EmpMapper mapper=session.getMapper(EmpMapper.class);

List emps=new ArrayList();

emps.add(new Employee("Bill",22,"2018-12-25"));

emps.add(new Employee("Cindy",22,"2018-12-25"));

emps.add(new Employee("Douglas",22,"2018-12-25"));int changed=mapper.batchInsert(emps);

System.out.println("changed="+changed);

session.commit();

}catch(Exception ex) {

logger.error(ex);

session.rollback();

}finally{

session.close();

}

}

}

如果插入数据多，就必须采用分批提交的方式，我采用的是插入一千个数据后提交一次，然后重复一万次的方式：

packagecom.hy.action;importjava.io.Reader;importjava.util.ArrayList;importjava.util.List;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importorg.apache.log4j.Logger;importcom.hy.entity.Employee;importcom.hy.mapper.EmpMapper;public classBatchInsert1000 {private static Logger logger = Logger.getLogger(SelectById.class);public static void main(String[] args) throwsException{long startTime =System.currentTimeMillis();

Reader reader=Resources.getResourceAsReader("mybatis-config.xml");

SqlSessionFactory ssf=newSqlSessionFactoryBuilder().build(reader);

reader.close();

SqlSession session=ssf.openSession();try{

EmpMapper mapper=session.getMapper(EmpMapper.class);

String ctime="2017-11-01 00:00:01";for(int i=0;i<10000;i++) {

List emps=new ArrayList();for(int j=0;j<1000;j++) {

Employee emp=new Employee("E"+i,20,ctime);

emps.add(emp);

}int changed=mapper.batchInsert(emps);

session.commit();

System.out.println("#"+i+" changed="+changed);

}

}catch(Exception ex) {

session.rollback();

logger.error(ex);

}finally{

session.close();long endTime =System.currentTimeMillis();

logger.info("Time elapsed:" + toDhmsStyle((endTime - startTime)/1000) + ".");

}

}//format seconds to day hour minute seconds style//Example 5000s will be formatted to 1h23m20s

public static String toDhmsStyle(longallSeconds) {

String DateTimes= null;long days = allSeconds / (60 * 60 * 24);long hours = (allSeconds % (60 * 60 * 24)) / (60 * 60);long minutes = (allSeconds % (60 * 60)) / 60;long seconds = allSeconds % 60;if (days > 0) {

DateTimes= days + "d" + hours + "h" + minutes + "m" + seconds + "s";

}else if (hours > 0) {

DateTimes= hours + "h" + minutes + "m" + seconds + "s";

}else if (minutes > 0) {

DateTimes= minutes + "m" + seconds + "s";

}else{

DateTimes= seconds + "s";

}returnDateTimes;

}

}

最后查询数据库，结果如下：

当然插入过程中还有一些插曲，在后继篇章中我会说明。

--END-- 2019年10月12日16:52:14