java - Java 中 MySQL 插入语句的性能 : Batch mode prepared statements vs single insert with multiple values

Question

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

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

我已经阅读了关于这个问题的以下主题:

• Optimizing MySQL inserts to handle a data stream
• MyISAM versus InnoDB
• Inserting Binary data into MySQL (without PreparedStatement's)

来自 mysql 网站:

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

我的问题是:

• 是否有人对在批处理模式下使用 INSERT 和准备好的语句与使用具有多个 VALUE 的单个 INSERT 语句的性能差异有任何建议或经验。

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

• 这些表用于存档目的，大约 90% 的写入和大约 10% 的读取(可能甚至更少)。我正在使用 InnoDB。这是不是 MyISAM 的正确选择？

提前感谢您的帮助。

Answer 1

JDBC 只是一个 Java SE 数据库访问标准，提供标准接口(interface)，因此您不必真正绑定(bind)到特定的 JDBC 实现。 MySQL Java 连接器 (Connector/J) 是仅用于 MySQL 数据库的 JDBC 接口(interface)的实现。出于经验，我参与了一个使用 MySQL 使用大量数据的项目，对于可以生成的数据，我们更喜欢 MyISAM:它可以实现更高的性能丢失事务，但一般来说，MyISAM 更快，但 InnoDB 更可靠。

大约一年前，我也想知道 INSERT 语句的性能，并在我的代码架中找到了以下旧测试代码(抱歉，它有点复杂，有点超出你的问题范围)。下面的代码包含了 4 种插入测试数据的示例:

• 单例 INSERT s;
• 批量 INSERT s;
• 手动批量 INSERT (永远不要使用它——它很危险)；
• 最后准备散装 INSERT )。

它使用 TestNG作为运行者，并使用一些自定义代码遗留，如:

• runWithConnection()方法 - 确保在执行回调后关闭连接或放回连接池(但下面的代码使用了不可靠的语句关闭策略 - 即使没有 try/finally 以减少代码)；
• IUnsafeIn<T, E extends Throwable> - 接受单个参数但可能抛出 E 类型异常的方法的自定义回调接口(interface)，例如: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_QUERY从 InnoDB 到 MyISAM，您会看到性能显着提升:

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

希望这会有所帮助。

更新:

对于第 4 种方式，您必须正确自定义 max_allowed_packet在 mysql.ini ([mysqld] 部分)足够大以支持非常大的数据包。