by Qingdou


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

1.mysql批量插入优化。

一直自认为对sql语句的数量使用,完全绝对的低估了现实问题的难度。100w的产品基础数据插入用掉了10个小时的时间。很挫…第一批实验数据100w插入后,让我久久不能释怀,这10个小时让我很纠结。
找原因吧,之前先入为主,一颗天真烂漫的心被一篇jdbc批处理survey的文章所蒙蔽,一直以为批处理的性能不会比单独insert要更快,那就试一下吧。【本文不谈java代码的优化】


Java代码

1.  PreparedStatement pstmt = null;
2.          Connection con = null;
3.          try {

4.              con = JdbcUtil.getConnection();
5.              con.setAutoCommit(false);
6.              pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
7.                      ResultSet.CONCUR_READ_ONLY);
8. 
9.              String[] lines = temp.split(ConstUtil.DELIM_ENTER, -1);
10.              for (int i = 0; i < lines.length; i++) {

11.                  String[] pdArr = lines[i].split(ConstUtil.DELIM_ONE, -1);
12. 
13.                  if (pdArr.length < 5)
14.                      return;
15. 
16.                  pstmt.setString(1, pdArr[0]);
17.                  if (pdArr[1].length() > 13)
18.                      continue;
19.                  pstmt.setString(2, pdArr[1]);// isbn
20.                  pstmt.setString(3, pdArr[2]);
21.                  pstmt.setString(4, pdArr[3]);
22.                  pstmt.setString(5, pdArr[4]);
23.                  pstmt.addBatch();
24.              }
25. 
26.              pstmt.executeBatch();
27.              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)

真正启用预编译:

   默认的PrearedStatement不能开启MySQL预编译功能:
 我们通过JDBC手工指定MySQL进行预编译,但是PrearedStatement却并不自动帮我们做这件事

只有使用了useServerPrepStmts=true才能开启Mysql的预编译。

    上面的代码其它不变,只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true";

如果useServerPrepStmts=true,ConneciontImpl在prepareStatement时会产生一个ServerPreparedStatement.在这个ServerPreparedStatement对象构造时首先会把当前SQL语句发送给MySQL进行预编译,然后将返回的结果缓存起来,其中包含预编译的名称(我们可以看成是当前SQL语句编译后的函数名),签名(参数列表),然后执行的时候就会直接把参数传给这个函数请求MySQL执行这个函数。否则返回的是客户端预编译语句,它仅做参数化工作,见第五节。
    ServerPreparedStatement在请求预编译和执行预编译后的SQL 函数时,虽然和我们上面手工预编译工作相同,但它与MySQL交互使用的是压缩格式,如prepare指令码是22,这样可以减少交互时传输的数据量。

想对同一SQL语句多次执行不是每次都预编译,就要使用cachePrepStmts=true,这个选项可以让JVM端缓存每个SQL语句的预编译结果,说白了就是以SQL语句为key, 将预编译结果缓存起来,下次遇到相同的SQL语句时作为key去get一下看看有没有这个SQL语句的预编译结果,有就直接合出来用。我们还是以事实来说明:
    上面的代码只修改String url = "jdbc:mysql://localhost:3306/mysql?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=25&prepStmtCacheSqlLimit=256";

 注意仅发生一次预编译,尽管代码本身在第一次执行后关闭了ps.close();但因为使用了cachePrepStmts=true,底层并没有真实关闭。

    千万注意,同一条SQL语句尽量在一个全局的地方定义,然后在不同地方引用,这样做一是为了DBA方便地对SQL做统一检查和优化,就象IBatis把SQL语句定义在XML文件中一样。二是同一语句不同写法,即使空格不同,大小写不同也会重新预编译,因为JVM端缓存是直接以SQL本身为key而不会对SQL格式化以后再做为key。


即使没有开启MySQL的预编译,坚持使用PreparedStatement仍然非常必要。
    在第三节的最后我说到"注意我的第一条语句select * from axman_test where name = 'axman\' or 1==1',下面还会说到它。",现在我们回过头来看,即使没有开启MySQL端的预编译,我们仍然要坚持使用PreparedStatement,因为JVM端对PreparedStatement的SQL语句进行了参数化,即用占位符替换参数,以后任何内容输入都是字符串或其它类型的值,而不会和原始的SQL语句拚接产生SQL注入,对字符串中的任何字符都会做检查,如果可能是SQL语句使用的标识符,会进行转义。然后发送一个合法的安全的SQL语句给数据库执行。