批量主键如何优化获取？

访客性能优化 2026-06-08 04:37:38 1

本文目录导读：

场景一：批量 INSERT 后获取自增主键
场景二：用一批已知主键批量查询
总结建议

针对“批量主键获取”的优化，这个问题通常出现在两种场景下：

数据写入后返回自增主键（INSERT 多条记录，需要立即获取每条记录生成的 ID）。
按照一批已知的主键查询数据（SELECT * FROM table WHERE id IN (1,2,3...)）。

下面分别针对这两种场景给出优化方案。

批量 INSERT 后获取自增主键

这是最典型的痛点,简单循环逐条 INSERT 再 SELECT LAST_INSERT_ID() 性能极差。

JDBC 原生批量获取（最推荐，性能极高）

绝大多数数据库驱动（MySQL、PostgreSQL）都支持在批量插入时返回生成的主键。

MySQL JDBC 示例：

String sql = "INSERT INTO user (name, email) VALUES (?, ?)";
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (User user : userList) {
    ps.setString(1, user.getName());
    ps.setString(2, user.getEmail());
    ps.addBatch(); // 添加到批处理
}
ps.executeBatch(); // 一次性执行
// ★ 关键：获取所有生成的主键
ResultSet rs = ps.getGeneratedKeys();
List<Long> ids = new ArrayList<>();
while (rs.next()) {
    ids.add(rs.getLong(1)); // 顺序与插入顺序一致
}

原理：数据库会一次性返回所有新行的自增 ID，无需额外查询，网络开销极小。

使用数据库的 RETURNING 子句（PostgreSQL、SQLite、DB2）

在 INSERT 语句末尾加上 RETURNING id，数据库会直接返回这行记录插入后的主键。

PostgreSQL 示例：

INSERT INTO user (name, email) 
VALUES ('A', 'a@x.com'), ('B', 'b@x.com') 
RETURNING id;

执行后直接得到一个包含 [1, 2] 的结果集。

使用 MyBatis-Plus / JPA 的批量插入

MyBatis-Plus：使用 saveBatch() 方法，它的底层实现（取决于版本和配置）会尝试将多条 INSERT 合并为一条 SQL，并利用 useGeneratedKeys 和 keyProperty 属性将生成的主键回填到实体对象中。
```
List<User> userList = ...;
userService.saveBatch(userList); // 执行后，userList 里的每个对象的 id 字段都会被自动赋值
```
JPA / Hibernate：使用 saveAll() 方法，并确保生成器配置为 IDENTITY 或 SEQUENCE，Hibernate 6.x 在批量模式下有优化，但要注意设置 hibernate.jdbc.batch_size，并开启 hibernate.jdbc.batch_versioned_data。

手动维护 ID（分布式场景）

如果你使用的是分布式 ID 生成器（如雪花算法、Redis INCR、Leaf、UidGenerator），则在 INSERT 前就可以预先分配好 ID。

优点：完全无需回查，速度快，无状态。

做法：

List<User> userList = new ArrayList<>();
for (int i = 0; i < batchSize; i++) {
    User user = new User();
    user.setId(idGenerator.nextId()); // 预先设置主键
    user.setName("...");
    userList.add(user);
}
// 直接批量 INSERT（不需要返回主键）
userMapper.insertBatch(userList);

这是性能最高的方案，且避免了数据库自增锁。

用一批已知主键批量查询

当我们已经有一个 ID 列表（如 [100, 101, 102, ...]），需要查询它的全部数据时。

控制 IN 子句的大小（非常重要）

数据库对 IN 子句中的参数数量没有硬上限，但性能会随着数量增大急剧下降。

建议：将 ID 列表分批，每批 500-1000 个。
- 如果列表有 10 万个 ID，拆成 100 批。
- 原因：MySQL 的 IN 子句会涉及索引扫描和 CPU 比较，单次处理太多会撑爆内存排序或导致索引选择错误。

优先使用 JOIN 临时表（极大量数据时的最优解）

当 ID 数量非常大（例如数万、数十万）时，IN 查询性能很差，应改为：

创建临时表：CREATE TEMPORARY TABLE temp_ids (id INT PRIMARY KEY);
批量 INSERT 到临时表：使用多行 INSERT 将 ID 批量灌入临时表。
JOIN 查询：SELECT t.* FROM target_table t JOIN temp_ids tmp ON t.id = tmp.id;

为什么快？ 数据库优化器可以为临时表生成高效的哈希连接（Hash Join）或索引连接（Index Join），远比逐条处理 IN 列表高效。

使用字段排序和 IN 子句的顺序匹配（MySQL 特定优化）

如果你的业务需要结果按照传入 ID 的顺序返回，可以在 MySQL 中使用 FIELD() 函数：

SELECT * FROM user 
WHERE id IN (3, 1, 2) 
ORDER BY FIELD(id, 3, 1, 2);

这比用 ORDER BY id 再在应用层排序更高效，且避免了多次排序。

使用 EXISTS 替代 IN（某些场景下更高效）

在某些数据库（如 PostgreSQL）或子查询嵌套多层时，EXISTS 可能比 IN 更快。

-- 假设你有另外一个表 user_ids 包含你想要查询的 id
SELECT * FROM target_table t
WHERE EXISTS (SELECT 1 FROM user_ids u WHERE u.id = t.id);

应用层缓存（根本性优化）

如果同一批主键被反复查询（例如最近浏览的商品列表），不要每次都走 DB。

使用 Guava Cache、Caffeine 或 Redis。
策略：查 DB 前先查缓存，只查询缓存中未命中的 ID，对于热点数据，命中率可能达到 90% 以上。

总结建议

场景	优化方案	适用场景
写入后获取主键	JDBC `getGeneratedKeys` `RETURNING` 子句 MyBatis-Plus `saveBatch`	高并发写入、自增主键
	预分配 ID（雪花算法等）	分布式系统、高并发、避免自增瓶颈
批量查询	拆分 `IN` 列表（500-1000一批）	ID 在几千以内
	JOIN 临时表	ID 数万、数十万（大数据量场景）
	应用层缓存	重复查询同一批热数据