mysql JDBC 批量插入大量数据(100w),性能

孤独剑客 2011-02-24 04:59:14
各位大侠, 帮帮俺吧, 实在没办法提高性能了, 插入1W条, 用了1分钟
下面是我的插入代码

StringBuffer sql = new StringBuffer();
sql.append("insert into ").append(tableName)
.append(" values(null,?,?,?,?,?,?,?,?,?,?,?,?);");
ByteArrayOutputStream baos = null;
DataOutputStream dos = null;
ByteArrayInputStream bais = null;

try {
preStmt = conn.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

//循环遍历pool,找出对应的regin以及IP段
//插入到域名表中
for (RegionBean region : regions) {
//获取pool的属性
int regionId = (int)region.getId();
int poolId = region.getPoolId();
String regionName = region.getName();

PoolBean pool = queryPool(poolId, conn);

String poolName = pool.getName();
byte lbId = pool.getLbId();
byte type = pool.getType();
int ttl = pool.getTtl();
int returnNum = pool.getMaximum();
String cname = pool.getCname();
byte verify = pool.getVerify();

List<PoolmemberBean> members = null;
if (GlobalUtil.POOL_TYPE_A == type) {
//如果不需要探测vip的可用性
members = queryAvaMemberByPoolType(poolId, lbId, verify, conn);
if (null == members) {
continue;
}
}

//取得与pool关联上的region以及region下的ip段
Date date1 = new Date();
List<IpsetBean> ipsets = queryIpsetByRegion(regionId, conn);
Date date2 = new Date();
System.out.println("get ipsets :" + (date2.getTime() - date1.getTime()));
if (null == ipsets) {
continue;
}

Date date3 = new Date();
int register = 0;
for (IpsetBean ipset : ipsets) {
register ++;
try {
long beginIp = ipset.getBeginIp();
long endIp = ipset.getEndIp();

//如果ip段被之前插入的ip包含了, 则此ip段不需要插入
if (ipIsIncludedBefore(beginIp, endIp, beginIps, endIps)) {
continue;
} else {
beginIps.add(beginIp);
endIps.add(endIp);
}

preStmt.setLong(1, beginIp);
preStmt.setLong(2, endIp);
preStmt.setInt(3, poolId);
preStmt.setString(4, poolName);
preStmt.setByte(5, lbId);
preStmt.setByte(6, type);
preStmt.setInt(7, ttl);

//如果类型是A记录,即cname为空
if (GlobalUtil.POOL_TYPE_A == type) {
//data字段按照格式插入该pool下可用的poolmember的ip和权重组合
if (GlobalUtil.POOL_LB_GA != lbId) {
//重新指定poolmember的ratio值
//计算方法是,每一个poolmember的ratio/所有poolmember的ratio综合*10000
resortRatio(members);
}

baos = new ByteArrayOutputStream();
dos = new DataOutputStream(baos);

for (PoolmemberBean bean : members) {
String ip = bean.getIp();
int ratio = bean.getRatio();
int position = bean.getPosition();

int intIp = GlobalUtil.ipToInt(ip);
short weight = 0;
if (GlobalUtil.POOL_LB_GA == lbId) {
weight = (short)position;
} else {
weight = (short)ratio;
}
dos.write(GlobalUtil.intToByteArray(intIp));
dos.write(GlobalUtil.shortToByteArray(weight));
}

bais = new ByteArrayInputStream(baos.toByteArray());

//取poolmember和pool的maximum的最小一个
preStmt.setInt(8, Math.min(members.size(), returnNum));
preStmt.setBinaryStream(9, bais, baos.size());
} else {
//否则的话, data字段直接插入域名就行
//return_num为1
preStmt.setInt(8, 1);
preStmt.setString(9, cname);
}
preStmt.setInt(10, zoneId);
preStmt.setInt(11, regionId);
preStmt.setString(12, regionName);

preStmt.addBatch();

if (register % 100 == 0) {
preStmt.executeBatch();
conn.commit();
preStmt.clearBatch();
}
} catch (IOException ioe) {
ioe.printStackTrace();
return false;
} finally {
if (null != bais) {
try {
bais.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}

if (null != dos) {
try {
dos.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}

if (null != baos) {
try {
baos.close();
} catch (IOException e) {
e.printStackTrace();
return false;
}
}
}
}
Date date4 = new Date();
System.out.println("add batch :" + (date4.getTime() - date3.getTime()));

Date date5 = new Date();
preStmt.executeBatch();
Date date6 = new Date();
System.out.println("execute batch :" + (date6.getTime() - date5.getTime()));
}
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (null != preStmt) {
try {
preStmt.close();
preStmt = null;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}

你们帮帮忙, 看如何解决性能问题.. 谢谢大家
...全文
3530 32 打赏 收藏 转发到动态 举报
写回复
用AI写文章
32 条回复
切换为时间正序
请发表友善的回复…
发表回复
dmy1432 2011-06-09
  • 打赏
  • 举报
回复
问一下,你使用这种方法的结果是多少秒呀[Quote=引用 31 楼 liuyuanshijie 的回复:]
好的, 谢谢大家
我最好用的是load data infile
先把数据写入文件, 然后把文件load 进去, OK了
[/Quote]
孤独剑客 2011-03-01
  • 打赏
  • 举报
回复
好的, 谢谢大家
我最好用的是load data infile
先把数据写入文件, 然后把文件load 进去, OK了
想喝咖啡的貓 2011-02-25
  • 打赏
  • 举报
回复
MySql的JDBC驱动不支持批量操作

意外的找到这个。。。
yuwenbao 2011-02-25
  • 打赏
  • 举报
回复
呵呵,mysql如果楼主是用的innodb的存储引擎,那么默认提交方式是执行一条dml语句,就提交一次,这样肯定会很慢。楼主需要将conn.setAutocommit设置成false。因为如果你执行一条就提交一次,那么等于执行一次就执行了一次io。而设置成false后,则可以先将数据放到缓存中,然后批量提交。如果是myisam引擎的话,则此速度比较正常,但是建议将myisam换成innodb存储引擎。
qingyuan18 2011-02-25
  • 打赏
  • 举报
回复
代码没有什么大的问题,prepareStatment,batchupdate你都用到了,从效率来看1分钟1W条也正常

如果实在是想提高插入性能,用Mysql的dump工具吧,把效率问题交给数据库自身,你的应用层只需要调用mysqldump命令即可
想喝咖啡的貓 2011-02-25
  • 打赏
  • 举报
回复
这是某csdn网友对mysql插入数据的一个简单测试,看看他的结果:

测试结果:
数据量(条) 插入所需时间(ms)
1 0
10 15
100 62
1,000 422
10,000 2,922
100,000 26,922
1000,000 272,219

原址:http://blog.csdn.net/hn1232/archive/2009/07/12/4341111.aspx

我觉得是不是这个问题? "批处理要conn.setAutoCommit(false)(默认会自动提交,不能达到批处理的目的,速度极慢!)。"
cw_tkong 2011-02-25
  • 打赏
  • 举报
回复
就是 多正常呀 1w条数据 60s 还行呀
cshonest814 2011-02-25
  • 打赏
  • 举报
回复
建数据库索引!
kangojian 2011-02-25
  • 打赏
  • 举报
回复
[Quote=引用 18 楼 runer 的回复:]
引用 16 楼 kangojian 的回复:
看你用的什么表
看你又没用索引
最好不要把逻辑写在jdbc里

提醒你一下 还有mysql 是吧数据写到硬盘上的 最好不要和硬盘的盘针过不去 多线程对这种状况不适合的 嘿嘿


兄弟,别误导别人

[/Quote]
请高人指教 好的思想要大家分享 一句没有理由的话 等于什么都没说 浮云啊
runer 2011-02-25
  • 打赏
  • 举报
回复
[Quote=引用 16 楼 kangojian 的回复:]
看你用的什么表
看你又没用索引
最好不要把逻辑写在jdbc里

提醒你一下 还有mysql 是吧数据写到硬盘上的 最好不要和硬盘的盘针过不去 多线程对这种状况不适合的 嘿嘿
[/Quote]

兄弟,别误导别人
kangojian 2011-02-25
  • 打赏
  • 举报
回复
[Quote=引用 16 楼 kangojian 的回复:]
看你用的什么表
看你又没用索引
最好不要把逻辑写在jdbc里

提醒你一下 还有mysql 是吧数据写到硬盘上的 最好不要和硬盘的盘针过不去 多线程对这种状况不适合的 嘿嘿
[/Quote]
你要把 硬件软件的交互时间算进去。。。。
kangojian 2011-02-25
  • 打赏
  • 举报
回复
看你用的什么表
看你又没用索引
最好不要把逻辑写在jdbc里

提醒你一下 还有mysql 是吧数据写到硬盘上的 最好不要和硬盘的盘针过不去 多线程对这种状况不适合的 嘿嘿
runer 2011-02-25
  • 打赏
  • 举报
回复
这么多数据,你循环一条一条insert,这个速度算是不错了


想要快用bulk insert
abaochan 2011-02-25
  • 打赏
  • 举报
回复
这个方法好像行, 不过实际操作起来还是蛮复杂的
无聊找乐 2011-02-25
  • 打赏
  • 举报
回复
你这个


//循环遍历pool,找出对应的regin以及IP段


循环遍历也是影响性能的因素之一
无聊找乐 2011-02-25
  • 打赏
  • 举报
回复
这么多数据就不应该通过应用服务器来处理
钱不是问题 2011-02-25
  • 打赏
  • 举报
回复
你数据哪里来的?
mysql 有个LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中.
100万条只要1秒
ddyouyue 2011-02-25
  • 打赏
  • 举报
回复
以前写过从一个数据库读数据更新插入到另一个数据库2w多 60s sqlserver的
也是jdbc批处理及preparedstatement和事务处理
孤独剑客 2011-02-25
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 fskjb01 的回复:]
分几个线程做看看有没有用
[/Quote]
这个方法好像行, 不过实际操作起来还是蛮复杂的
UPC_思念 2011-02-25
  • 打赏
  • 举报
回复
差不多啦,毕竟是免费滴
加载更多回复(11)

62,612

社区成员

发帖
与我相关
我的任务
社区描述
Java 2 Standard Edition
社区管理员
  • Java SE
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧