MariaDB社区

标题: 本条语句如何优化 [打印本页]

作者: liyihongcug 时间: 2009-5-4 16:27
标题: 本条语句如何优化
本条语句如何优化

mysql5+linux(centos)
PreparedStatement prepared = null;

            Connection conn = null;
            ResultSet result = null;
            try {
                     conn = DBUtil.getConnection();
                     conn.setAutoCommit(false);

                     prepared = conn.prepareStatement(INSERT_TOKEN_SQL);

                     int size = tokenLst.size();

                     for(Token t: tokenLst){
                              Long disciplinedId = t.getDisciplineId() == null? 0L: t.getDisciplineId();
                              Integer typeId = t.getTypeId() == null? 0:t.getTypeId();
                              Long participantId = t.getParticipantId() == null? 0:t.getParticipantId();

                              if(t.getText().length() >= 100){
                                    System.out.println(t.getText());
                                    continue;
                              }

                              prepared.setInt(1, storyId);
                              prepared.setLong(2, disciplinedId);
                              prepared.setInt(3, typeId);
                              prepared.setString(4, t.getText());
                              prepared.setBoolean(5, t.getIsReservedKeyWord());
                              prepared.setDouble(6, t.getFrequency());
                              prepared.setString(7, t.getHashCode()+"");
                              prepared.setBoolean(8, t.isInHeadLine());
                              prepared.setBoolean(9, t.getIsReservedKeyWord());
                              prepared.setLong(10, participantId);
                              prepared.setString(11, t.getStandardName());
                              prepared.setBoolean(12, t.isTearmParticipant());
                              prepared.setBoolean(13, t.isHomeOrAway());
                              prepared.addBatch();
                     }
                     prepared.executeBatch();

                     conn.commit();
            } catch (Exception e) {
                     e.printStackTrace();
            } finally {
                     DBUtil.JDBCUtil(conn, prepared, result);
            }
本表有130万记录，现在发现批处理插入1万条记录发现需要 3-5秒。
经过观察该表有主键（id,storyId）其中id是系统自动增长。
问如何调节性能，可以动索

作者: kider 时间: 2009-5-5 13:01
考虑一下批量插入了再一起提交...

欢迎光临 MariaDB社区 (http://123.56.88.72/)