Postgres与Mybatis高效批量操作实战：从基础到高级冲突处理

1. 为什么需要批量操作？

做过数据库开发的朋友都知道，单条SQL反复执行和批量操作完全是两个概念。想象一下你要往图书馆录入1000本新书，如果每本书都单独执行一次INSERT语句，就像是一个图书管理员每次只拿一本书从办公室走到书架，来回跑1000趟。而批量操作相当于一次性推着小推车把1000本书都运过去，效率差距显而易见。

PostgreSQL作为企业级关系型数据库，在处理批量数据时有着天然优势。结合Mybatis这个Java生态中最流行的ORM框架，我们可以实现各种高效的批处理方案。实测下来，批量操作相比单条执行通常能有10-100倍的性能提升，特别是在网络延迟较高的分布式系统中，这个优势会更加明显。

2. 环境准备与基础配置

2.1 数据库表设计

我们先从最基础的图书管理案例开始。假设我们需要管理一个图书数据库，每本书包含以下信息：

CREATE TABLE book ( id bigserial, name varchar(100), author varchar(50), price integer, CONSTRAINT book_pk PRIMARY KEY (id) );

这里使用bigserial作为自增主键，name字段我们后面会用来演示唯一约束冲突的场景。

2.2 Java实体类映射

对应的Java实体类也很简单：

@Data public class Book { private Long id; private String name; private String author; private Integer price; }

建议使用Lombok的@Data注解自动生成getter/setter，减少样板代码。在实际项目中，你可能还需要添加创建时间、更新时间等审计字段。

2.3 Mybatis基础配置

确保你的pom.xml中包含必要的依赖：

<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.0</version> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency>

在application.properties中配置数据源：

spring.datasource.url=jdbc:postgresql://localhost:5432/book_db spring.datasource.username=postgres spring.datasource.password=yourpassword spring.datasource.driver-class-name=org.postgresql.Driver

3. 基础批量操作实战

3.1 批量插入实现

批量插入是最常见的场景，比如新书入库、用户批量导入等。Mybatis提供了非常灵活的批量插入方式。

3.1.1 Mapper接口定义

public interface BookMapper { int batchInsert(List<Book> books); }

3.1.2 XML映射文件

关键点在于使用<foreach>标签动态生成VALUES子句：

<insert id="batchInsert"> INSERT INTO book (name, author, price) VALUES <foreach collection="list" item="item" separator=","> (#{item.name}, #{item.author}, #{item.price}) </foreach> </insert>

这种写法会生成类似INSERT INTO book (...) VALUES (...),(...),(...)的SQL，一次性插入所有记录。

3.1.3 性能优化建议

• 适当控制单次批量插入的数据量，建议每批1000-5000条
• 对于超大批量数据，考虑分批次提交
• 在事务中执行批量操作，但注意不要使事务过大

3.2 批量更新技巧

批量更新比插入更复杂一些，PostgreSQL提供了几种实现方式。

3.2.1 使用UNNEST实现

<update id="batchUpdate"> UPDATE book b SET name = a.name, author = a.author, price = a.price FROM ( SELECT UNNEST(ARRAY[<foreach collection="list" item="item" separator=",">#{item.id}</foreach>]) AS id, UNNEST(ARRAY[<foreach collection="list" item="item" separator=",">#{item.name}</foreach>]) AS name, UNNEST(ARRAY[<foreach collection="list" item="item" separator=",">#{item.author}</foreach>]) AS author, UNNEST(ARRAY[<foreach collection="list" item="item" separator=",">#{item.price}</foreach>]) AS price ) AS a WHERE b.id = a.id </update>

这种写法利用了PostgreSQL的数组和UNNEST函数，将Java集合转换为临时表进行关联更新。

3.2.2 使用CASE WHEN实现

另一种方式是使用CASE WHEN语句：

<update id="batchUpdate"> UPDATE book SET name = CASE id <foreach collection="list" item="item"> WHEN #{item.id} THEN #{item.name} </foreach> END, author = CASE id <foreach collection="list" item="item"> WHEN #{item.id} THEN #{item.author} </foreach> END, price = CASE id <foreach collection="list" item="item"> WHEN #{item.id} THEN #{item.price} </foreach> END WHERE id IN ( <foreach collection="list" item="item" separator=","> #{item.id} </foreach> ) </update>

这种方式生成的SQL更长，但在某些场景下可能更易读。

4. 高级冲突处理策略

4.1 唯一约束与冲突场景

在实际业务中，我们经常会遇到唯一约束冲突的问题。比如在我们的图书案例中，假设书名应该是唯一的：

CREATE UNIQUE INDEX unique_book_name ON book (name);

当尝试插入重复书名的记录时，PostgreSQL会抛出唯一约束冲突错误。我们需要有策略地处理这种情况。

4.2 忽略冲突记录

第一种策略是忽略冲突记录，只插入不冲突的部分。这在数据去重场景中非常有用。

4.2.1 ON CONFLICT DO NOTHING

<insert id="batchInsertIgnoreConflict"> INSERT INTO book (name, author, price) VALUES <foreach collection="list" item="item" separator=","> (#{item.name}, #{item.author}, #{item.price}) </foreach> ON CONFLICT (name) DO NOTHING </insert>

这个操作是原子性的，要么全部成功，要么全部失败。但只会插入那些不违反唯一约束的记录。

4.3 冲突时更新记录

第二种策略是在冲突时更新已有记录，也就是所谓的"upsert"操作。

4.3.1 ON CONFLICT DO UPDATE

<insert id="batchInsertOrUpdate"> INSERT INTO book (name, author, price) VALUES <foreach collection="list" item="item" separator=","> (#{item.name}, #{item.author}, #{item.price}) </foreach> ON CONFLICT (name) DO UPDATE SET author = EXCLUDED.author, price = EXCLUDED.price </insert>

这里的EXCLUDED表示冲突的记录原本要插入的值。这个语法是PostgreSQL特有的，非常强大。

4.3.2 条件更新

你还可以在更新时添加条件：

ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price WHERE EXCLUDED.price > book.price

这样只有当新价格更高时才更新，实现了价格保护逻辑。

5. 性能优化与实战建议

5.1 批量操作性能对比

我做过一个简单的性能测试，插入10000条记录：

可以看到批量操作相比单条操作有数量级的性能提升，而冲突处理的额外开销很小。

5.2 事务与批处理

• 批量操作应该在事务中执行，确保数据一致性
• 但要注意事务不要太大，否则会导致锁竞争和内存问题
• 对于超大批量数据，考虑分批次提交事务

5.3 连接池配置

批量操作对连接池有些特殊要求：

# 增大连接池大小 spring.datasource.hikari.maximum-pool-size=20 # 允许长事务 spring.datasource.hikari.max-lifetime=1800000

5.4 监控与调优

建议监控以下指标：

• 批量操作的执行时间
• 数据库CPU和IO使用率
• 锁等待情况

根据监控结果调整批量大小、并发度等参数。

6. 常见问题排查

6.1 批量操作超时问题

如果遇到超时，可以尝试：

1. 增加JDBC超时设置
2. 减小批量大小
3. 优化数据库配置

6.2 内存溢出问题

处理大批量数据时，注意：

1. 使用分页或流式处理
2. 及时清理中间集合
3. 调整JVM堆大小

6.3 唯一约束冲突诊断

当冲突处理不符合预期时：

1. 检查唯一索引定义
2. 确认ON CONFLICT子句正确指定了冲突目标
3. 检查EXCLUDED引用的字段是否正确

在实际项目中，我遇到过因为字段名大小写不匹配导致冲突处理失效的情况，所以一定要仔细检查这些细节。