jOOQ批量插入1对N关联数据的最佳实践疑问

问题背景 #

后端开发中经常遇到需要一次性存储多表分散数据的场景，大多是1对N关系或递归数据结构。单条插入性能不足，必须用批量插入，但子表插入时需要依赖主表生成的ID来维护关联关系。

目前的做法是先批量插入主表，再把返回的ID分配给子表记录，但这种方式存在不少隐患：

• 返回的ID顺序是否可靠？会不会受数据库、配置、触发器或存储过程影响？
• 数据库是否严格按插入语句的顺序插入数据？
• 存在大量样板代码，有没有更优的实现方式？
• 最坏情况会出现数据关联错误的问题

基于此，提出以下问题：
使用jOOQ高效批量插入这类数据结构时，有没有不需要盲目依赖数据顺序或数据库处理逻辑的最佳实践？

• 是不是应该稳妥起见，放弃批量改用顺序插入（比如先插作者再插其书籍）？
• 要不要新增前置关联列（比如UUID）来避免依赖自动生成的ID？我们考虑过用UUID，但不确定性能影响。

批量示例 #

表关系 #

+------------+        关联关系        +-------------+
|  Authors   |---------------------<|   Books     |
+------------+        (1:N)         +-------------+
| author_id  |                      | book_id     |
| name       |                      | title       |
| bio        |                      | author_id*  |
+------------+                      +-------------+

MySQL表结构 #

create table author
(
    id    bigint auto_increment primary key,
    name  varchar(32) not null,
    bio   varchar(32) not null
);

create table book
(
    id bigint auto_increment primary key,
    title varchar(32) not null,
    author_id bigint not null,
    constraint fk_author_book
        foreign key (author_id) references author (id)
            on delete cascade
);

当前实现代码 #

List<Author> authors =
        List.of(
                new Author("作者1", "简介1", List.of(new Book("书籍1"), new Book("书籍2"))),
                new Author("作者2", "简介2", List.of(new Book("书籍3"), new Book("书籍4"))),
                new Author("作者3", "简介3", List.of(new Book("书籍5"), new Book("书籍6")))
        );

// 批量插入作者并返回生成的ID
var authorIDs = jooq.insertInto(AUTHOR,
                                AUTHOR.ID,
                                AUTHOR.NAME,
                                AUTHOR.BIO
                    )
                    .valuesOfRecords(authors.stream()
                                            .map(author -> new AuthorRecord((Long) null, author.name, author.bio))
                                            .collect(Collectors.toList()))
                    .returningResult(AUTHOR.ID)
                    .fetchInto(Long.class);

// 构造书籍插入记录，依赖ID顺序关联作者和书籍
var bookRecords = new LinkedList<BookRecord>();
for (int i = 0; i < authorIDs.size(); i++) {
    var authorId = authorIDs.get(i);
    for (var book : authors.get(i).books) {
        // 这里是风险点：完全依赖ID和插入行的顺序一致，否则会关联错误数据
        bookRecords.add(new BookRecord((Long) null, book.name, authorId));
    }
}

// 批量插入书籍
jooq.insertInto(BOOK,
                BOOK.ID,
                BOOK.TITLE,
                BOOK.AUTHOR_ID
    )
    .valuesOfRecords(bookRecords)
    .execute();

预编译语句的顺序插入方案 #

我们还试过用两条预编译语句的顺序插入方案，但大数据量下性能极差（20000个作者各带2本书，开启keepStatement(true)耗时约45秒，未开启时约56秒）：

private void preparedStatement(List<Author> authors) {  
    try (var insertAuthorQuery =  
                 jooq.insertInto(AUTHOR,  
                                 AUTHOR.ID,  
                                 AUTHOR.NAME,  
                                 AUTHOR.BIO)  
                     .values((Long) null, null, null)  
                     .returningResult(AUTHOR.ID)  
                     .keepStatement(true)  
    ) {  
        try (var insertBookQuery =  
                     jooq.insertInto(BOOK,  
                                     BOOK.ID,  
                                     BOOK.TITLE,  
                                     BOOK.AUTHOR_ID  
                         ).values((Long) null, null, null)  
                         .returningResult(BOOK.ID)  
                         .keepStatement(true)) {  
            authors.forEach(author -> {  

                insertAuthorQuery.getBindValues();  
                insertAuthorQuery.bind(2, author.name);  
                insertAuthorQuery.bind(3, author.bio);  

                // 插入单个作者并获取ID
                var authorID = insertAuthorQuery.fetchOneInto(Long.class);  

                // 插入该作者的所有书籍
                author.books.forEach(book -> {  
                    insertBookQuery.bind(2, book.name);  
                    insertBookQuery.bind(3, authorID);  
                    insertBookQuery.execute();  
                });  
            });  
        }  
    }  
}

最佳实践解答 #

1. 优先选择批量插入+返回关联标识字段 #

你当前依赖ID顺序的做法在MySQL下其实是可靠的——MySQL的AUTO_INCREMENT在批量插入时会按插入顺序生成ID，且RETURNING返回的结果顺序和插入顺序完全一致，除非表上有改变插入顺序的触发器（比如强制排序的触发器）。但要彻底消除顺序依赖，可以让RETURNING返回能唯一标识原插入记录的字段+生成的ID，比如返回name、bio和id，再通过这些标识字段把ID和原作者对象关联，而非依赖索引顺序。

修改后的示例代码：

// 批量插入作者并返回name、bio和生成的id
var authorResults = jooq.insertInto(AUTHOR, AUTHOR.NAME, AUTHOR.BIO)
        .valuesOfRecords(authors.stream()
                .map(author -> new AuthorRecord(null, author.name, author.bio))
                .collect(Collectors.toList()))
        .returning(AUTHOR.ID, AUTHOR.NAME, AUTHOR.BIO)
        .fetch();

// 构建原作者到生成ID的映射，用name+bio作为唯一键（需确保业务上该组合唯一）
Map<String, Long> authorIdMap = authorResults.stream()
        .collect(Collectors.toMap(
                r -> r.get(AUTHOR.NAME) + ":" + r.get(AUTHOR.BIO),
                r -> r.get(AUTHOR.ID)
        ));

// 构造书籍记录时通过映射获取正确的作者ID
var bookRecords = authors.stream()
        .flatMap(author -> author.books.stream()
                .map(book -> new BookRecord(null, book.name, 
                        authorIdMap.get(author.name + ":" + author.bio))))
        .collect(Collectors.toList());

// 批量插入书籍
jooq.insertInto(BOOK, BOOK.TITLE, BOOK.AUTHOR_ID)
        .valuesOfRecords(bookRecords)
        .execute();

如果name+bio不唯一，可以在插入前给每个作者生成临时UUID作为标识，插入时将UUID存入临时字段（或用数据库会话变量、临时表），返回时关联UUID和生成的ID，就能100%确保关联正确，完全不依赖顺序。

2. UUID方案的取舍 #

如果业务允许用UUID作为主键或关联键，确实能避免依赖自增ID的顺序问题，但需注意：

• 性能影响：MySQL中用CHAR(36)存储UUID作为主键会导致索引碎片化，因为UUID无序，插入时会频繁分裂B+树。可以用有序UUID（比如UUIDv1时间戳前置、UUIDv7），插入顺序接近自增ID，碎片化问题会大幅缓解。
• 存储成本：UUID比BIGINT占用更多存储空间（36字符 vs 8字节），但多数场景下这不是瓶颈。
• 业务兼容性：如果下游系统依赖自增ID的连续性，UUID可能需要额外适配。

3. 顺序插入的适用场景 #

顺序插入（先插一个作者，再插其所有书籍）的性能问题核心是多次网络往返——每插入一组作者+书籍都会和数据库交互一次。大数据量（比如20k作者）场景下，性能远不如批量插入，除非业务对一致性要求极高且能接受性能损失，否则不建议放弃批量插入。

若必须用顺序插入优化性能，可以尝试：

• 在MySQL JDBC URL中开启rewriteBatchedStatements=true，驱动会将多条单条插入合并为批量语句发送；
• 用jOOQ的Batch API批量处理单个作者的书籍，减少网络往返次数。

总结 #

• 优先选择批量插入+返回关联标识字段的方案，既保证性能，又消除顺序依赖风险；
• UUID是可行方案，但建议用有序UUID降低性能损耗；
• 顺序插入仅适合小数据量场景，大数据量下务必用批量插入优化。

内容的提问来源于stack exchange，提问作者xnn