MySQL中的MySQL插入语句的性能：批处理模式预处理语句与具有多个值的单个插入 [英] Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values

查看：93
本文介绍了MySQL中的MySQL插入语句的性能：批处理模式预处理语句与具有多个值的单个插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I am designing a MySQL database which needs to handle about 600 row inserts per second across various InnoDB tables. My current implementation uses non-batched prepared statements. However, writing to the MySQL database bottlenecks and my queue size increases over time.

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

The implementation is written in Java, I don't know the version off hand. It uses MySQL's Java connector. I need to look into switching to JDBC tomorrow. I am assuming these are two different connector packages.

我已经在这个问题上阅读了以下主题：

I have read the following threads on the issue:
  • 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

我的问题是：


  • 是否有人使用IN对性能差异有任何建议或经验在批处理模式下使用预处理语句进行SERT，而使用包含多个VALUE的单个 INSERT 语句。

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

What are the performance differences between the MySQL Java connector vs. JDBC. Should I be using one or the other?

这些表用于存档目的，并且可以看到约90％写入〜10％读数（甚至可能更少） ）。我正在使用InnoDB。这是MyISAM的正确选择吗？

The tables are for archive purposes, and will see ~90% write to ~10% read (maybe even less). I am using InnoDB. Is this the right choice over MyISAM?

提前感谢您的帮助。

推荐答案

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

JDBC is simply a Java SE standard of database access offering the standard interfaces so you're not really bound to a specific JDBC implementation. MySQL Java connector (Connector/J) is an implementation of the JDBC interfaces for MySQL databases only. Out of experience, I'm involved to a project that uses huge amount of data using MySQL, and we mostly prefer MyISAM for data that can be generated: it allows to achieve much higher performance losing transactions, but generally speaking, MyISAM is faster, but InnoDB is more reliable.

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

I wondered for the performance of the INSERT statements too about a year ago, and found the following old testing code in my code shelf (sorry, it's a bit complex and a bit out of your question scope). The code below contains examples of 4 ways of inserting the test data:


  • 单 INSERT s;

  • 批处理 INSERT s;

  • 手动批量 INSERT （永远不要使用它 - 这很危险）;

  • 和最后准备批量 INSERT ）。

  • single INSERTs;
  • batched INSERTs;
  • manual bulk INSERT (never use it - it's dangerous);
  • and finally prepared bulk INSERT).

It使用 TestNG 作为参赛者，并使用一些自定义代码遗产，如：

It uses TestNG as the runner, and uses some custom code legacy like:


  • runWithConnection（）方法 - 确保连接在回调后关闭或放回连接池执行（但下面的代码使用不可靠的语句关闭策略 - 即使没有尝试 / 最后来减少代码）;

  • IUnsafeIn< T，E extends Throwable> - 接受单个参数但可能的方法的自定义回调接口划分类型E的异常，如： void handle（T参数）抛出E; 。

  • the runWithConnection() method - ensures that the connection is closed or put back to the connection pool after the callback is executed (but the code below uses not reliable strategy of the statement closing - even without try/finally to reduce the code);
  • IUnsafeIn<T, E extends Throwable> - a custom callback interface for the methods accepting a single parameter but potentially throwing an exception of type E, like: 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） ：

Take a look at the methods annotated with the @Test annotation: they actually execute the INSERT statements. Also please take a look at the CREATE_TABLE_QUERY constant: in the source code it uses InnoDB producing the following results at my machine with MySQL 5.5 installed (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，你会看到显着的性能提升：

If you change the CREATE_TABLE_QUERY InnoDB to MyISAM, you'd see significant performance increase:

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

希望这会有所帮助。

UPD：

对于第4种方法，您必须在 mysql.ini 中正确定制 max_allowed_pa​​cket （ [ mysqld] section）足够大以支持真正的大包。

For the 4th way you must properly customize the max_allowed_packet in mysql.ini (the [mysqld] section) to be large enough to support really big packets.

这篇关于MySQL中的MySQL插入语句的性能：批处理模式预处理语句与具有多个值的单个插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆