Java中MySQL插入语句的性能：批处理方式准备的语句与具有多个值的单个插入

Question

我正在设计一个MySQL数据库，该数据库每秒需要处理各种InnoDB表大约600行的插入。我当前的实现使用非批处理的预处理语句。但是，MySQL随着时间的推移，写入数据库瓶颈和队列大小会增加。

该实现是用Java编写的，我不知道它的版本。它使用MySQL的Java连接器。我需要考虑改用JDBC明天。我假设这是两个不同的连接器包。

我已阅读有关该问题的以下主题：

• 优化MySQL插入以处理数据流
• MyISAM与InnoDB
• 将二进制数据插入MySQL（不带PreparedStatement）

并从mysql网站：

• http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

我的问题是：

• 有没有人对以批处理方式使用带准备语句的INSERT与使用INSERT带多个VALUE 的单个语句的性能差异有任何建议或经验。

• 什么是之间的性能差异MySQL的Java连接器对JDBC。我应该使用另一个吗？

• 这些表用于存档目的，将看到〜90％的写入量到〜10％的读取量（甚至更少）。我正在使用InnoDB。这是MyISAM的正确选择吗？

预先感谢您的帮助。

Answer 1

JDBC只是数据库访问的Java SE标准，提供了标准接口，因此您实际上并不局限于特定的JDBC实现。MySQL Java连接器（Connector /
J）仅是MySQL数据库的JDBC接口的实现。根据经验，我参与了一个使用MySQL使用大量数据的项目，对于可生成的数据，我们最喜欢使用MyISAM：它可以实现更高的性能损失交易，但总的来说，MyISAM更快，但是InnoDB更可靠。

我也想知道大约一年前INSERT语句的性能，并且在我的代码架中找到了以下旧测试代码（对不起，它有点复杂，而且超出了您的问题范围）。以下代码包含4种插入测试数据的方式的示例：

• 单 INSERT s;
• 分批 INSERT的
• 手动散装 INSERT（切勿使用-危险）；
• 最后 准备好批量 INSERT）。

它使用TestNG作为运行程序，并使用一些自定义代码，例如：

• 该runWithConnection()方法-确保在执行回调后连接被关闭或放回连接池（但低于用途声明关闭不可靠的战略的代码-即使没有try/ finally减少代码）;

• IUnsafeIn<T, E extends Throwable>-一个自定义的回调接口，用于接受单个参数但可能会引发E类型异常的方法，例如： void handle(T argument) throws E;。

  package test;

  import test.IUnsafeIn;

  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  
  

  import static java.lang.String.format;
  import static java.lang.String.valueOf;
  import static java.lang.System.currentTimeMillis;

  import core.SqlBaseTest;
  import org.testng.annotations.AfterSuite;
  import org.testng.annotations.BeforeSuite;
  import org.testng.annotations.BeforeTest;
  import org.testng.annotations.Test;

  public final class InsertVsBatchInsertTest extends SqlBaseTest {

  private static final int ITERATION_COUNT = 3000;
  
  private static final String CREATE_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS ttt1 (c1 INTEGER, c2 FLOAT, c3 VARCHAR(5)) ENGINE = InnoDB";
  private static final String DROP_TABLE_QUERY = "DROP TABLE ttt1";
  private static final String CLEAR_TABLE_QUERY = "DELETE FROM ttt1";
  
  private static void withinTimer(String name, Runnable runnable) {
      final long start = currentTimeMillis();
      runnable.run();
      logStdOutF("%20s: %d ms", name, currentTimeMillis() - start);
  }
  
  @BeforeSuite
  public void createTable() {
      runWithConnection(new IUnsafeIn<Connection, SQLException>() {
          @Override
          public void handle(Connection connection) throws SQLException {
              final PreparedStatement statement = connection.prepareStatement(CREATE_TABLE_QUERY);
              statement.execute();
              statement.close();
          }
      });
  }
  
  @AfterSuite
  public void dropTable() {
      runWithConnection(new IUnsafeIn<Connection, SQLException>() {
          @Override
          public void handle(Connection connection) throws SQLException {
              final PreparedStatement statement = connection.prepareStatement(DROP_TABLE_QUERY);
              statement.execute();
              statement.close();
          }
      });
  }
  
  @BeforeTest
  public void clearTestTable() {
      runWithConnection(new IUnsafeIn<Connection, SQLException>() {
          @Override
          public void handle(Connection connection) throws SQLException {
              final PreparedStatement statement = connection.prepareStatement(CLEAR_TABLE_QUERY);
              statement.execute();
              statement.close();
          }
      });
  }
  
  @Test
  public void run1SingleInserts() {
      withinTimer("Single inserts", new Runnable() {
          @Override
          public void run() {
              runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                  @Override
                  public void handle(Connection connection) throws SQLException {
                      for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                          final PreparedStatement statement = connection.prepareStatement("INSERT INTO ttt1 (c1, c2, c3) VALUES (?, ?, ?)");
                          statement.setInt(1, i);
                          statement.setFloat(2, i);
                          statement.setString(3, valueOf(i));
                          statement.execute();
                          statement.close();
                      }
                  }
              });
          }
      });
  }
  
  @Test
  public void run2BatchInsert() {
      withinTimer("Batch insert", new Runnable() {
          @Override
          public void run() {
              runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                  @Override
                  public void handle(Connection connection) throws SQLException {
                      final PreparedStatement statement = connection.prepareStatement("INSERT INTO ttt1 (c1, c2, c3) VALUES (?, ?, ?)");
                      for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                          statement.setInt(1, i);
                          statement.setFloat(2, i);
                          statement.setString(3, valueOf(i));
                          statement.addBatch();
                      }
                      statement.executeBatch();
                      statement.close();
                  }
              });
          }
      });
  }
  
  @Test
  public void run3DirtyBulkInsert() {
      withinTimer("Dirty bulk insert", new Runnable() {
          @Override
          public void run() {
              runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                  @Override
                  public void handle(Connection connection) throws SQLException {
                      final StringBuilder builder = new StringBuilder("INSERT INTO ttt1 (c1, c2, c3) VALUES ");
                      for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                          if ( i != 0 ) {
                              builder.append(",");
                          }
                          builder.append(format("(%s, %s, '%s')", i, i, i));
                      }
                      final String query = builder.toString();
                      final PreparedStatement statement = connection.prepareStatement(query);
                      statement.execute();
                      statement.close();
                  }
              });
          }
      });
  }
  
  @Test
  public void run4SafeBulkInsert() {
      withinTimer("Safe bulk insert", new Runnable() {
          @Override
          public void run() {
              runWithConnection(new IUnsafeIn<Connection, SQLException>() {
                  private String getInsertPlaceholders(int placeholderCount) {
                      final StringBuilder builder = new StringBuilder("(");
                      for ( int i = 0; i < placeholderCount; i++ ) {
                          if ( i != 0 ) {
                              builder.append(",");
                          }
                          builder.append("?");
                      }
                      return builder.append(")").toString();
                  }
  
                  @SuppressWarnings("AssignmentToForLoopParameter")
                  @Override
                  public void handle(Connection connection) throws SQLException {
                      final int columnCount = 3;
                      final StringBuilder builder = new StringBuilder("INSERT INTO ttt1 (c1, c2, c3) VALUES ");
                      final String placeholders = getInsertPlaceholders(columnCount);
                      for ( int i = 0; i < ITERATION_COUNT; i++ ) {
                          if ( i != 0 ) {
                              builder.append(",");
                          }
                          builder.append(placeholders);
                      }
                      final int maxParameterIndex = ITERATION_COUNT * columnCount;
                      final String query = builder.toString();
                      final PreparedStatement statement = connection.prepareStatement(query);
                      int valueIndex = 0;
                      for ( int parameterIndex = 1; parameterIndex <= maxParameterIndex; valueIndex++ ) {
                          statement.setObject(parameterIndex++, valueIndex);
                          statement.setObject(parameterIndex++, valueIndex);
                          statement.setObject(parameterIndex++, valueIndex);
                      }
                      statement.execute();
                      statement.close();
                  }
              });
          }
      });
  }
  

  }

看一下用@Test注释注释的方法：它们实际上执行INSERT语句。还请看一看CREATE_TABLE_QUERY常量：在源代码中，它使用InnoDB在装有MySQL
5.5（MySQL Connector / J 5.1.12）的计算机上产生以下结果：

InnoDB
Single inserts: 74148 ms
Batch insert: 84370 ms
Dirty bulk insert: 178 ms
Safe bulk insert: 118 ms

如果将CREATE_TABLE_QUERYInnoDB 更改为MyISAM，则会看到显着的性能提升：

MyISAM
Single inserts: 604 ms
Batch insert: 447 ms
Dirty bulk insert: 63 ms
Safe bulk insert: 26 ms

希望这可以帮助。

UPD：

对于第四种方法，您必须适当地自定义max_allowed_packetin
mysql.ini（本[mysqld]节），使其足够大以支持真正的大数据包。