java - Hibernate + MySQL简单批量插入极慢

Question

我将 2500 条记录从 Hibernate 插入到一个完全空的 MySQL 表中。插入需要 5 分钟!

我在谷歌上搜索了几个小时并尝试了一些东西，比如自动生成的主键，但似乎没有任何东西可以提高性能。

我的程序的早期版本正在并发执行插入操作(每个线程 1 个，大约 100 个线程)，这需要大约 2 分钟的时间。我认为批处理应该将性能提高约 10 倍，但它似乎适得其反。

我正在使用 Google Cloud's MySQL用db-f1-micro instance

这是我的表的样子(只有数据库中的表!):

CREATE TABLE `categories` (
`browse_node` varchar(60) NOT NULL,
`name` varchar(60) DEFAULT NULL,
`path` varchar(400) DEFAULT NULL,
`url` varchar(200) NOT NULL,
`level` int(11) NOT NULL,
PRIMARY KEY (`browse_node`)
)

这是 POJO:

package example.com;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * Represents a category from the categories table
 */
@Entity
@Table(name = "categories")
public class Category {

    @Id
    @Column(name = "browse_node")
    private String browseNode;
    @Column(name = "name")
    private String name;
    @Column(name = "path")
    private String path;
    @Column(name = "url")
    private String url;
    @Column(name = "level")
    private int level;

    public Category() {

    }

    public Category(String browseNode, String name, String path, String url, int level) {
        this.browseNode = browseNode;
        this.name = name;
        this.path = path;
        this.url = url;
        this.level = level;
    }
    // Omitting setters/getters
}

这是执行插入的代码:

private static void writeCategoriesToDb(Map<String, Category> categories) {
    StatelessSession session = sessionFactory.openStatelessSession();
    // Session session = sessionFactory.openSession();
    session.beginTransaction();

    int i = 0;
    int batchSize = 50;

    for (Category category : categories.values()) {
        session.insert(category);
//        if (i % batchSize == 0) {
//            session.flush();
//            session.clear();
//        }
//        i++;
    }

    session.getTransaction().commit();
    session.close();
}

这是配置文件:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://someIp/myDB</property>
        <property name="connection.username">root</property>
        <property name="connection.password">password</property>
        <property name="connection.useSSL">false</property>
        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">20</property>
        <property name="hibernate.jdbc.batch_size">3000</property>
        <property name="hibernate.id.new_generator_mappings">false</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <mapping class="example.com.Category"/>
    </session-factory>
</hibernate-configuration>

Answer 1

找到 answer here.

将 rewriteBatchedStatements=true 添加到我的 JDBC url 修复了它!

现在插入所有记录大约需要 2.2 秒。

<property name="connection.url">jdbc:mysql://someIp/myDB?rewriteBatchedStatements=true</property>