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

各位大侠, 帮帮俺吧, 实在没办法提高性能了, 插入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;
                }
            }
        }

你们帮帮忙, 看如何解决性能问题.. 谢谢大家

32 个解决方案

#1

1分钟插入10000也不是太差，正常

#2

引用 1 楼 xys_777 的回复:

1分钟插入10000也不是太差，正常

啊???
 你是想说我的代码有问题呢? 还是???
 诚心求教, 谢谢!

#3

引用 1 楼 xys_777 的回复:

1分钟插入10000也不是太差，正常

1分钟1w正常？ 我觉的慢呢?

#4

差不多吧

#5

引用 3 楼 afer198215 的回复:

引用 1 楼 xys_777 的回复:

1分钟插入10000也不是太差，正常

1分钟1w正常？ 我觉的慢呢?

是啊, 我觉得是超级慢....
   

#6

mysql一分钟1w就够意思了，我直接写存储过程王表里插入数据，也就这个速度吧

#7

分几个线程做看看有没有用

#8

引用 7 楼 fskjb01 的回复:

分几个线程做看看有没有用

这个方法好像行, 不过实际操作起来还是蛮复杂的

#9

以前写过从一个数据库读数据更新插入到另一个数据库2w多 60s sqlserver的
也是jdbc批处理及preparedstatement和事务处理

#10

你数据哪里来的?
mysql 有个LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中.
100万条只要1秒

#11

这么多数据就不应该通过应用服务器来处理

#12

你这个

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

循环遍历也是影响性能的因素之一

#13

该回复于2011-02-25 13:32:43被版主删除

#14

这个方法好像行, 不过实际操作起来还是蛮复杂的

#15

这么多数据，你循环一条一条insert，这个速度算是不错了


想要快用bulk insert 

#16

看你用的什么表 
看你又没用索引
最好不要把逻辑写在jdbc里

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

#17

引用 16 楼 kangojian 的回复:

看你用的什么表 
看你又没用索引
最好不要把逻辑写在jdbc里

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

你要把 硬件软件的交互时间算进去。。。。

#18

引用 16 楼 kangojian 的回复:

看你用的什么表 
看你又没用索引
最好不要把逻辑写在jdbc里

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

兄弟，别误导别人

#19

引用 18 楼 runer 的回复:

引用 16 楼 kangojian 的回复:
看你用的什么表
看你又没用索引
最好不要把逻辑写在jdbc里

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


兄弟，别误导别人

请高人指教 好的思想要大家分享 一句没有理由的话 等于什么都没说 浮云啊

#20

建数据库索引！

#21

就是 多正常呀 1w条数据 60s 还行呀 

#22

这是某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)（默认会自动提交，不能达到批处理的目的，速度极慢！）。"

#23

代码没有什么大的问题，prepareStatment，batchupdate你都用到了，从效率来看1分钟1W条也正常

如果实在是想提高插入性能，用Mysql的dump工具吧，把效率问题交给数据库自身，你的应用层只需要调用mysqldump命令即可

#24

呵呵，mysql如果楼主是用的innodb的存储引擎，那么默认提交方式是执行一条dml语句，就提交一次，这样肯定会很慢。楼主需要将conn.setAutocommit设置成false。因为如果你执行一条就提交一次，那么等于执行一次就执行了一次io。而设置成false后，则可以先将数据放到缓存中，然后批量提交。如果是myisam引擎的话，则此速度比较正常，但是建议将myisam换成innodb存储引擎。

#25

MySql的JDBC驱动不支持批量操作

意外的找到这个。。。

#26

引用 24 楼 yuwenbao 的回复:

呵呵，mysql如果楼主是用的innodb的存储引擎，那么默认提交方式是执行一条dml语句，就提交一次，这样肯定会很慢。楼主需要将conn.setAutocommit设置成false。因为如果你执行一条就提交一次，那么等于执行一次就执行了一次io。而设置成false后，则可以先将数据放到缓存中，然后批量提交。如果是myisam引擎的话，则此速度比较正常，但是建议将myisam换成innodb存储引……

我是这么干的

#27

引用 20 楼 cshonest814 的回复:

建数据库索引！

这个不用想, 会更加慢!

#28

ok，如果楼主你已经优化到尽头了，那么建议如下：
1.将innodb的缓存参数调大，大到内存的百分之六十到七十
2.如果你急着在数据库用，建议先把数据插入到memory存储引擎当中，然后晚上写个定时器，把数据再放入真正需要的表。
3.多线程插入可以解决点问题，不过应该是一个线程建一个数据库连接，然后按照主键值的大小范围，让每个线程去插入一部分值。当然，前提是你的运行程序的机器要是多核。

#29

把插入代码写进数据库的存储过程吧，这样可能快点。然后在java里面调用存储过程。
不知道mysql有没有批量处理。

如果可以 换oracle吧，pl/sql里面批处理（BULK）插入20w调记录才十几秒。

#30

差不多啦，毕竟是免费滴

#31

好的, 谢谢大家
 我最好用的是load data infile 
先把数据写入文件, 然后把文件load 进去, OK了

#32

问一下，你使用这种方法的结果是多少秒呀

引用 31 楼 liuyuanshijie 的回复:

好的, 谢谢大家
 我最好用的是load data infile 
先把数据写入文件, 然后把文件load 进去, OK了