Jooq批量记录插入

Question

我目前正在尝试批量插入许多记录（约2000条），但Jooq的batchInsert并没有做我想要的事情。

我正在将POJO转换为UpdatableRecords，然后执行batchInsert，它会为每个记录执行插入。因此，Jooq对于每个批量插入都执行了大约2000个查询，这损害了数据库性能。

执行的代码如下（jooq的批量插入）：

for (int i = 0; i < records.length; i++) {
            Configuration previous = ((AttachableInternal) records[i]).configuration();

            try {
                records[i].attach(local);
                executeAction(i);
            }
            catch (QueryCollectorSignal e) {
                Query query = e.getQuery();
                String sql = e.getSQL();

                // Aggregate executable queries by identical SQL
                if (query.isExecutable()) {
                    List<Query> list = queries.get(sql);

                    if (list == null) {
                        list = new ArrayList<Query>();
                        queries.put(sql, list);
                    }

                    list.add(query);
                }
            }
            finally {
                records[i].attach(previous);
            }
        }

我可以像这样实现它（因为Jooq内部也在做同样的事情）：

records.forEach(UpdatableRecord::insert);

替代方案：

jooq.batchInsert(records).execute();

我该如何让Jooq批量创建新记录？我应该将记录转换为绑定查询，然后调用batchInsert吗？有什么建议？ ;)

- hya

Answer 1

jOOQ的DSLContext.batchInsert()方法会为具有相同生成的SQL字符串的连续记录集创建一个JDBC批处理语句（遗憾的是，Javadoc没有正式定义这一点）。

当你的记录看起来像这样时，这可能会成为一个问题：

+------+--------+--------+
| COL1 | COL2   | COL3   |
+------+--------+--------+
| 1*   | {null} | {null} |
| 2*   | B*     | {null} |
| 3*   | {null} | C*     |
| 4*   | D*     | D*     |
+------+--------+--------+

因为在这种情况下，生成的 SQL 字符串将会是这样的：

INSERT INTO t (col1) VALUES (?);
INSERT INTO t (col1, col2) VALUES (?, ?);
INSERT INTO t (col1, col3) VALUES (?, ?);
INSERT INTO t (col1, col2, col3) VALUES (?, ?, ?);

这种默认行为的原因是这是唯一可以保证...DEFAULT行为的方法。就像在SQL中的DEFAULT一样。我在这里解释了这种行为的理由。

考虑到这一点，由于每个连续的SQL字符串都不同，所以插入操作不幸地不能作为单个批次进行。

解决方案1：确保所有更改标志均为true

强制所有INSERT语句相同的一种方法是将每个单独记录的所有更改标志设置为true：

for (Record r : records)
    r.changed(true);

现在，所有的SQL字符串都将是相同的。

解决方案2：使用Loader API

与批处理不同，您可以导入数据（并在那里指定批量大小）。有关详细信息，请参阅手册中有关导入记录的部分：

https://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-records

解决方案三：使用批处理语句

当使用TableRecords时，您可以方便地使用batchInsert()。但是，您也可以手动生成INSERT语句，并使用jOOQ的批处理语句API批量绑定变量：

https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution

关于性能的说明

关于 DSLContext.batchInsert() 和类似 API，有几个未解决的问题。客户端算法为每个单独的记录生成 SQL 字符串的效率低下，可能会在将来进行更改，直接依赖于 changed() 标志。一些相关问题：

• #4533：DSLContext.batchInsert()、batchUpdate()、batchDelete() 等生成太多单独的 SQL 语句
• #6294：DSLContext.batchInsert() 应考虑所有 Record.changed() 标志

- Lukas Eder

---

仅使用批处理大小。要更改提交大小，我需要禁用自动提交。也许我以后会尝试。 - hya

@hya：没错，你不应该提交每个单独的插入操作。通常，在某些数据库上，每次提交1000行可以提高效率。 - Lukas Eder

@SouravJha：无论何时jOOQ在其API中使用术语“batch”，它将尝试将其转化为支持的JDBC批处理。 - Lukas Eder

@LukasEder： 有没有一些配置可以调整batchXXX() API的大小，或者我们必须通过传递适当大小的列表来控制？ - Sourav Jha

@SouravJha: jOOQ 3.19将允许您在所有批处理API中使用Settings.batchSize：https://github.com/jOOQ/jOOQ/issues/14840。在jOOQ 3.19之前，您必须自己手动将列表拆分为块。 - Lukas Eder