无论如何，我不能在Hibernate中批处理MySQL INSERT语句

Question

我目前面临着众所周知的、常见的Hibernate插入批处理问题。

我需要保存批次5百万行长。我首先尝试更轻的有效载荷。因为我必须插入只有2种类型的实体（首先是类型A的所有记录，然后是类型B的所有记录，都指向常见的类型CManyToOne父实体），所以我想从JDBC批量插入中获得最大的优势。

我已经阅读了很多文档，但我尝试过的都没有成功。

• 我知道为了使用批处理插入，我不能使用实体生成器。所以我删除了AUTO_INCREMENTID，我用一个技巧设置这个ID：SELECT MAX（ID）from ENTITIES并每次递增。
• 我知道我必须定期冲洗会话。我会提前发布代码，但无论如何，我每500个元素执行一个事务。
• 我知道我必须设置hibernate.jdbc.batch_size与我的应用程序的批量大小一致，所以我在LocalSessionFactoryBean（Spring ORM集成）中设置了它
• 我知道我必须在连接URL中启用重写批处理语句。

这里是我的实体

公共父实体。它首先插入到单个事务中。我不关心这里的自动增量列。每个批处理作业只有一条记录

@Entity
@Table(...)
@SequenceGenerator(...)
public class Deal
{

    @Id
    @Column(
            name = "DEAL_ID",
            nullable = false)
    @GeneratedValue(
            strategy = GenerationType.AUTO)
    protected Long id;

    ................
}

其中一个孩子（假设每批250万条记录）

@Entity
@Table(
        name = "TA_LOANS")
public class Loan
{

    @Id
    @Column(
            name = "LOAN_ID",
            nullable = false)
    protected Long id;

    @ManyToOne(
            optional = false,
            targetEntity = Deal.class,
            fetch = FetchType.LAZY)
    @JoinColumn(
            name = "DEAL_ID",
            nullable = false)
    protected Deal deal;


    .............
}

其他孩子打字。比如说其他250万张唱片

@Entity
@Table(
        name = "TA_BONDS")
public class Bond
{

    @Id
    @Column(
            name = "BOND_ID")

    @ManyToOne(
            fetch = FetchType.LAZY,
            optional = false,
            targetEntity = Deal.class)
    @JoinColumn(
            name = "DEAL_ID",
            nullable = false,
            updatable = false)
    protected Deal deal;

}

插入记录的简化代码

    long loanIdCounter = loanDao.getMaxId(), bondIdCounter = bondDao.getMaxId(); //Perform SELECT MAX(ID)

    Deal deal = null;

    List<Bond> bondList = new ArrayList<Bond>(COMMIT_BATCH_SIZE); //500 constant value
    List<Loan> loanList = new ArrayList<Loan>(COMMIT_BATCH_SIZE);

    for (String msg: inputStreamReader)
    {
        log.debug(msg.toString());

        if (this is a deal)
        {
            Deal deal = parseDeal(msg.getMessage());

            deal = dealManager.persist(holder.deal); //Called in a separate transaction using Spring annotation @Transaction(REQUIRES_NEW)

        }
        else if (this is a loan)
        {

            Loan loan = parseLoan(msg.getMessage());
            loan.setId(++loanIdCounter);
            loan.setDeal(deal);

            loanList.add(loan);

            if (loanList.size() == COMMIT_BATCH_SIZE)
            {
                loanManager.bulkInsert(loanList); //Perform a bulk insert in a single transaction, not annotated but handled manually this time
                loanList.clear();
            }
        }
        else if (this is a bond)
        {
            Bond bond = parseBond(msg.getMessage());
            bond.setId(++bondIdCounter);
            bond.setDeal(deal);

            bondList.add(bond);



            if (bondList.size() == COMMIT_BATCH_SIZE) //As above
            {
                bondManager.bulkInsert(bondList);
                bondList.clear();

            }
        }
    }

    if (!bondList.isEmpty())
        bondManager.bulkInsert(bondList);
    if (!loanList.isEmpty())
        loanManager.bulkInsert(loanList);
    //Flush remaining items, not important

bulkInsert的实现：

@Override
public void bulkInsert(Collection<Bond> bonds)
{
    // StatelessSession session = sessionFactory.openStatelessSession();
    Session session = sessionFactory.openSession();
    try
    {
        Transaction t = session.beginTransaction();
        try
        {
            for (Bond bond : bonds)
                // session.persist(bond);
                // session.insert(bond);
                session.save(bond);
        }
        catch (RuntimeException ex)
        {
            t.rollback();
        }
        finally
        {
            t.commit();
        }
    }
    finally
    {
        session.close();
    }

}

正如您从注释中看到的，我已经尝试了几种有状态/无状态会话的组合。都没有奏效。

我的dataSource是一个带有以下URL的ComboPooledDataSource

<b:property name="jdbcUrl" value="jdbc:mysql://server:3306/db?autoReconnect=true&amp;rewriteBatchedStatements=true" />

我的会话工厂

<b:bean id="sessionFactory" class="class.that.extends.org.springframework.orm.hibernate3.LocalSessionFactoryBean" lazy-init="false" depends-on="dataSource">
        <b:property name="dataSource" ref="phoenixDataSource" />
        <b:property name="hibernateProperties">
            <b:props>
                <b:prop key="hibernate.dialect">${hibernate.dialect}</b:prop> <!-- MySQL5InnoDb-->
                <b:prop key="hibernate.show_sql">${hibernate.showSQL}</b:prop>
                <b:prop key="hibernate.jdbc.batch_size">500</b:prop>
                <b:prop key="hibernate.jdbc.use_scrollable_resultset">false</b:prop>
                <b:prop key="hibernate.cache.use_second_level_cache">false</b:prop>
                <b:prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</b:prop>
                <b:prop key="hibernate.cache.use_query_cache">false</b:prop>
                <b:prop key="hibernate.validator.apply_to_ddl">false</b:prop>
                <b:prop key="hibernate.validator.autoregister_listeners">false</b:prop>
                <b:prop key="hibernate.order_inserts">true</b:prop>
                <b:prop key="hibernate.order_updates">true</b:prop>
            </b:props>
        </b:property>
</b:bean>

即使我的项目范围的类扩展了LocalSessionFactoryBean，它也不会覆盖它的方法（只添加了几个项目范围的方法）

几天后我就开始生气了。我读了一些文章，但没有一篇帮助我启用批插入。我使用Spring上下文运行JUnit测试中的所有代码（因此我可以@Autowire我的类）。我所有的尝试都只产生了大量独立的INSERT语句

• https://stackoverflow.com/questions/12011343/how-do-you-enable-batch-inserts-in-hibernate
• https://stackoverflow.com/questions/3469364/faster-way-to-batch-saves-with-hibernate
• https://forum.hibernate.org/viewtopic.php?p=2374413
• https://stackoverflow.com/questions/3026968/high-performance-hibernate-insert

我错过了什么？

Answer 1

很可能您的查询正在被重写，但通过查看Hibernate SQL日志，您不知道是否已被重写。Hibernate不会重写insert语句——MySQL驱动程序会重写它们。换句话说，Hibernate将向驱动程序发送多个insert语句，然后驱动程序将重写它们。所以Hibernate日志只显示Hibernate发送给驱动程序的SQL，而不是驱动程序发送给数据库的SQL。

您可以通过在连接url中启用MySQL的profileSQL参数来验证这一点：

<b:property name="jdbcUrl" value="jdbc:mysql://server:3306/db?autoReconnect=true&amp;rewriteBatchedStatements=true&amp;profileSQL=true" />

使用与您类似的示例，我的输出如下所示：

insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
insert into Person (firstName, lastName, id) values (?, ?, ?)
Wed Feb 05 13:29:52 MST 2014 INFO: Profiler Event: [QUERY]  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) duration: 1 ms, connection-id: 81, statement-id: 33, resultset-id: 0, message: insert into Person (firstName, lastName, id) values ('person1', 'Name', 1),('person2', 'Name', 2),('person3', 'Name', 3),('person4', 'Name', 4),('person5', 'Name', 5),('person6', 'Name', 6),('person7', 'Name', 7),('person8', 'Name', 8),('person9', 'Name', 9),('person10', 'Name', 10)

前10行是由Hibernate记录的，尽管这不是实际发送到MySQL数据库的内容。最后一行来自MySQL驱动程序，它清楚地显示了一个具有多个值的单批插入，这就是实际发送到MySQL数据库的内容。