mysql 批量查询、批量安插优化

mysql 批量查询、批量插入优化

这段时间一直在参与产品库的设计和实现，中间和mysql的恩恩怨怨给广大喜欢交流学习的网友们，提供一些借鉴的机会。首先从mysql的批量插入开始吧。

1.mysql批量插入优化。

一直自认为对sql语句的数量使用，完全绝对的低估了现实问题的难度。100w的产品基础数据插入用掉了10个小时的时间。很挫…第一批实验数据100w插入后，让我久久不能释怀，这10个小时让我很纠结。

找原因吧，之前先入为主，一颗天真烂漫的心被一篇jdbc批处理survey的文章所蒙蔽，一直以为批处理的性能不会比单独insert要更快，那就试一下吧。【本文不谈java代码的优化】

PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = JdbcUtil.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			String[] lines = temp.split(ConstUtil.DELIM_ENTER, -1);
			for (int i = 0; i < lines.length; i++) {
				String[] pdArr = lines[i].split(ConstUtil.DELIM_ONE, -1);

				if (pdArr.length < 5)
					return;

				pstmt.setString(1, pdArr[0]);
				if (pdArr[1].length() > 13)
					continue;
				pstmt.setString(2, pdArr[1]);// isbn
				pstmt.setString(3, pdArr[2]);
				pstmt.setString(4, pdArr[3]);
				pstmt.setString(5, pdArr[4]);
				pstmt.addBatch();
			}

			pstmt.executeBatch();
			con.commit();

还是100w的实验数据：

首先批处理Threshold=100

引用

Time consuming： 8h

然后批处理Threshold=500

引用

Time consuming： 6.7h

然后批处理Threshold=1000

引用

Time consuming： 5.4h

然后批处理Threshold=2000

引用

Time consuming： 5.3h

看来批处理还是能节省相当的时间。不过Threshold在大也没有多少优化空间了。不过5个多小时的插入时间还是让心情沉重。再想想别的方法，记得jdbc-mysql的实现的新版本中增加了对批处理的支持的优化，那可以试一下嘛。

jdbc driver 版本 5.1.8 及以上支持rewriteBatchedStatements=true参数，该参数帮主mysql打开批处理状态，只需在 jdbc url 后跟一个参数rewriteBatchedStatements=true即可(jdbc:mysql:///test?rewriteBatchedStatements=true)。

引用

下载地址： http://www.mysql.com/products/connector/

然后批处理Threshold=1000

引用

Time consuming： 0.5h

oha，到此批处理的结果才令人满意（600 records/s）

2.批量插入中使用了select查询功能的优化。

还遇到一个非常非常有意思，并且非常

【抱歉，忙起来了，过几天再将这块的实际应用结论整理出来！】

1 楼 finallygo 2010-09-04

你用的数据库引擎是innodb的吗？我记得官方的文档上好像说可以达到800 records/s的

2 楼 binma85 2010-09-06

finallygo 写道

你用的数据库引擎是innodb的吗？我记得官方的文档上好像说可以达到800 records/s的

用的MyISAM 单追求查询和插入性能，因为短时间内不会用用到事务。官方说的应该是均值，我测试过本地的mysql插入，10w数据插入用时91s，速率1099 records/s。

相关文章：

你感兴趣的文章：

标签云：