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

Question

各位大侠, 帮帮俺吧, 实在没办法提高性能了, 插入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;
                }
            }
        }
你们帮帮忙, 看如何解决性能问题.. 谢谢大家

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

你这个“
//循环遍历pool,找出对应的regin以及IP段
”循环遍历也是影响性能的因素之一

Answer 5

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

Answer 6

看你用的什么表 
看你又没用索引
最好不要把逻辑写在jdbc里提醒你一下 还有mysql 是吧数据写到硬盘上的 最好不要和硬盘的盘针过不去 多线程对这种状况不适合的 嘿嘿

Answer 7

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

Answer 8

代码没有什么大的问题，prepareStatment，batchupdate你都用到了，从效率来看1分钟1W条也正常如果实在是想提高插入性能，用Mysql的dump工具吧，把效率问题交给数据库自身，你的应用层只需要调用mysqldump命令即可

Answer 9

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

Answer 10

MySql的JDBC驱动不支持批量操作意外的找到这个

Answer 11

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

Answer 12

把插入代码写进数据库的存储过程吧，这样可能快点。然后在java里面调用存储过程。
不知道mysql有没有批量处理。如果可以 换oracle吧，pl/sql里面批处理（BULK）插入20w调记录才十几秒。

Answer 13

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