【求优化】MySQL批量插入5W条左右数据

减肥啊啊啊啊啊 2012-07-05 10:26:39


     Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtils.getConnection();
conn.setAutoCommit(false);

long s = System.currentTimeMillis();

StringBuffer sb = new StringBuffer();
sb.append(" INSERT INTO TEST1 ");
sb.append(" ( ");
sb.append(" A , B , C , ");
sb.append(" D , E , F , ");
sb.append(" G , H , I ");
sb.append(" J , K , K ");
sb.append(" M , N , O ");
sb.append(" P , Q , R ");
sb.append(" S , T , U ");
sb.append(" V , W , X ");
sb.append(" Y , Z ");
sb.append(" ) ");
sb.append(" VALUES");
sb.append(" (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? (SELECT NAME FROM TEST2 WHERE ID =? ) ");

ps = conn.prepareStatement(sb.toString(),ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
int count = 0 ;

for (int i=0,n=50000;i<n;i++) {

ps.setString(1, "value1");
ps.setString(2, "value2");
ps.setString(3, "value3");
ps.setString(4, "value4");
ps.setString(5, "value5");
ps.setString(6, "value6");
ps.setString(7, "value7");
ps.setString(8, "value8");
ps.setString(9, "value9");
ps.setString(10, "value10");
ps.setString(11, "value11");
ps.setString(12, "value12");
ps.setString(13, "value13");
ps.setString(14, "value14");
ps.setString(15, "value15");
ps.setString(16, "value16");
ps.setString(17, "value17");
ps.setString(18, "value18");
ps.setString(19, "value19");
ps.setString(20, "value20");
ps.setString(21, "value21");
ps.setString(22, "value22");
ps.setString(23, "value23");
ps.setString(24, "value24");
ps.setString(25, "value25");

ps.setInt(26, i);

ps.addBatch();
count ++ ;
if(count % 10000 == 0){
//1W条一提交
ps.executeBatch();
conn.commit();
count = 0;
}

}

ps.executeBatch();
conn.commit();
ps.clearBatch();

long e = System.currentTimeMillis();
System.out.println("共用时:" + (e - s)/1000.00000000 + "秒");

} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}



Mysql数据库连接的URL 附加了
rewriteBatchedStatements=true

jar包是5.1.17的jar

引擎是InnoDB的
具体的测试时间 每多少条记录一提交
只有插入(就是只有insert into table values(?,?,?))
5W条数据计时 :13.591秒 5W条数据计时 :13591ms ---100提交
5W条数据计时 :8.681秒 5W条数据计时 :8681ms ---200一提交
5W条数据计时 :6.617秒 5W条数据计时 :6617ms ----500一提交
5W条数据计时 :5.173秒 5W条数据计时 :5173ms ----1000一提交
5W条数据计时 :4.766秒 5W条数据计时 :4766ms ----2000一提交
5W条数据计时 :5.242秒 5W条数据计时 :5242ms ----2222一提交
5W条数据计时 :4.938秒 5W条数据计时 :4938ms ----2500一提交
5W条数据计时 :5.017秒 5W条数据计时 :5017ms ----3000一提交
5W条数据计时 :5.208秒 5W条数据计时 :5208ms ----5000一提交
5W条数据计时 :5.027秒 5W条数据计时 :5027ms ----10000一提交
5W条数据计时 :5.771秒 5W条数据计时 :5771ms ----20000一提交

插入加查询(在插入的时候带入查询后的值在插入)
5W条数据计时 :20.955秒 5W条数据计时 :20955ms -100一提交
5W条数据计时 :13.436秒 5W条数据计时 :13436ms -500一提交
5W条数据计时 :12.999秒 5W条数据计时 :12999ms -1000一提交
5W条数据计时 :17.855秒 5W条数据计时 :17855ms -2000一提交
5W条数据计时 :11.952秒 5W条数据计时 :11952ms -3000一提交
5W条数据计时 :11.818秒 5W条数据计时 :11818ms -5000一提交
5W条数据计时 :10.498秒 5W条数据计时 :10498ms -10000一提交
5W条数据计时 :9.668秒 5W条数据计时 :9668ms -20000一提交
5W条数据计时 :15.843秒 5W条数据计时 :15843ms -30000一提交



能不能5万条数据库在插入的时间减少些、或者能使最优?
需要其他参数和环境请告知、
先谢谢各位了、
...全文
486 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 的回复:]

引用 4 楼 的回复:

引用 1 楼 的回复:

只需要set global innodb_flush_log_at_trx_commit=0就行

set global innodb_flush_log_at_trx_commit= 0 1 2 不同的情况、

0
5W条数据计时 :10.832秒 5W条数据计时 :10832ms
5W条数据计时 :13.0……
[/Quote]


# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=0

修改my.ini后 在服务里重启了mysql服务、
程序没改动、
执行的是插入后查询在赋值
执行了3次 10000一提交
5W条数据计时 :10.857秒 5W条数据计时 :10857ms
5W条数据计时 :10.491秒 5W条数据计时 :10491ms
5W条数据计时 :10.72秒 5W条数据计时 :10720ms

5000一提交
5W条数据计时 :12.238秒 5W条数据计时 :12238ms
5W条数据计时 :12.069秒 5W条数据计时 :12069ms

2000
5W条数据计时 :11.678秒 5W条数据计时 :11678ms
5W条数据计时 :11.653秒 5W条数据计时 :11653ms
1000
5W条数据计时 :11.91秒 5W条数据计时 :11910ms
5W条数据计时 :11.496秒 5W条数据计时 :11496ms


怎么感觉没有变化呢、
MySQL Server 5.5.15
是不是我哪里配置错了?
rucypli 2012-07-05
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 的回复:]

引用 1 楼 的回复:

只需要set global innodb_flush_log_at_trx_commit=0就行

set global innodb_flush_log_at_trx_commit= 0 1 2 不同的情况、

0
5W条数据计时 :10.832秒 5W条数据计时 :10832ms
5W条数据计时 :13.013秒 5W条数据计时 :130……
[/Quote]
感觉你没改innodb_flush_log_at_trx_commit这个参数

试试在你配置文件里更改这个参数 然后重启数据库
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 的回复:]

打开事务
OR
用 LOAD DATA FIIFILE 导入
[/Quote]


我上面有打开事物、
conn.setAutoCommit(false); 设置手动设置事物、
之后按每多少条来提交事物、

load data infile 的话、应该还要先对io做操作文件插入、性能有损耗吗?
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 的回复:]

用load data infile导入速度还是比较快的。
[/Quote]


情况是这样的 有个批量增加的功能选取好数据后 点击才会存入数据库的、
如果使用load data infile 的话 好像还要做io操作文件
是不是对性能又有些增加?
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 的回复:]

只需要set global innodb_flush_log_at_trx_commit=0就行
[/Quote]
set global innodb_flush_log_at_trx_commit= 0 1 2 不同的情况、

0
5W条数据计时 :10.832秒 5W条数据计时 :10832ms
5W条数据计时 :13.013秒 5W条数据计时 :13013ms
1
5W条数据计时 :11.867秒 5W条数据计时 :11867ms
5W条数据计时 :12.358秒 5W条数据计时 :12358ms
2
5W条数据计时 :10.609秒 5W条数据计时 :10609ms
5W条数据计时 :10.749秒 5W条数据计时 :10749ms

怎么感觉会出现反效果?
在网上查了查2会安全些、以后还要部署到集群环境、
Rotel-刘志东 2012-07-05
  • 打赏
  • 举报
回复
用load data infile导入速度还是比较快的。
wwwwb 2012-07-05
  • 打赏
  • 举报
回复
打开事务
OR
用 LOAD DATA FIIFILE 导入
rucypli 2012-07-05
  • 打赏
  • 举报
回复
只需要set global innodb_flush_log_at_trx_commit=0就行
ACMAIN_CHM 2012-07-05
  • 打赏
  • 举报
回复
问题应该不是innodb_flush_log_at_trx_commit, 这个参数是控制写redo log写的时机。

感觉上时间还是消耗在每个语句的提交上。
rucypli 2012-07-05
  • 打赏
  • 举报
回复
我晕 手动批量写事务commit这样对于0 1 2 这个参数基本不失去作用了

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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