jdbc 执行批量 update 的效率问题

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

For Existing Member Sign In

This topic created in 882 days ago, the information mentioned may be changed or developed.

最近客户要对数据库存储的数据做国密改造，提供了相关的加密 sdk ，原来的数据库表存储的数据要升级成密文。现在就想用原生的 jdbc 读出数据原文加密后存再进去，但是执行 batchexecute()的方法一次 1000 条，发现巨慢，按我查到都是推荐批量更新，但我这个就是巨慢。然后搞了测试表，结构里的索引什么的都删了还是慢。数据库情况：postgre ，单表有 100+W 的数据，加密更新四五个字段代码大致：

    connection.setAutoCommit(false);
    PreparedStatement preparedStatement = connection.prepareStatement("update users set name = ? where id = ?");

	for(int =i;i<res.length;i<1000){
    	preparedStatement.setString(1, "John");
    	preparedStatement.setInt(2, 1);
    	preparedStatement.batchadd()
    }
    

    preparedStatement.executeBatch();
    connection.commit();

这个哪位有好的优化思路吗，或者别的方案

jdbc

MIT

加密

数据库

29 replies • 2023-11-30 11:16:41 +08:00

xyooyx

Nov 27, 2023

整个表读出来改（高性能机器/分布式计算），改完写到 table_modified,然后重命名下表

ZhanXinjia

Nov 27, 2023

这么搞肯定慢。
第一点：不要用框架，框架比较耗时，直接用 jdbc 手写 sql 注入。（要看国密是否有转移字符问题，如果没有直接注入）
第二点：换一个方式写 sql ，做临时表 m：
就是把你之前这样的语句：
begin;
update t1 set c2=2 where c1=1;
update t1 set c2=3 where c1=2;
update t1 set c2=4 where c1=3;
update t1 set c2=5 where c1=4;
update t1 set c2=6 where c1=5;
commit;
优化成：
UPDATE t1 m, (
SELECT 1 AS c1, 2 AS c2
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 3, 4
UNION ALL
SELECT 4, 5
UNION ALL
SELECT 5, 6
) r
SET m.c1 = r.c1, m.c2 = r.c2
WHERE m.c1 = r.c1;
第三点：多线程干。

ZhanXinjia

Nov 27, 2023

之前做过类似的加密，一分钟可以加密 50 万条左右

cubecube

Nov 27, 2023

id 上的索引你得留着呀

wwwz

Nov 27, 2023

之前好像搞过，用 replace into 效率比较高

akira

Nov 27, 2023

加密，更新分别耗时多少。

so2back

Nov 27, 2023

试试 update case when 的写法，拼一条语句更新 2000 条记录，前些天用 mysql 试过，1 分钟可以更新 100w

WayneXxx

PRO

Nov 27, 2023

慢的主要原因是你没提前开一个事务, (貌似 pgsql 关了自动提交，executeBatch 每条语句都是一个独立的事务)，所以执行前可以提前开一个事务
还有两种更快方案：
依然还是用 batchexecute
1. 基于 pgsql INSERT...ON CONFLICT DO UPDATE （主键冲突则更新）实现批量更新
2. 复制一张表，在这张表的基础上批量插入，执行完了再把名字改回去（相比第一种更快）

qizheng22

Nov 27, 2023

在连接加上：rewriteBatchedStatements=true

BBCCBB

Nov 27, 2023

楼上说了加 rewriteBatchedStatements 参数

kaf

Nov 27, 2023

写临时表然后重命名

150530

Nov 27, 2023

@ZhanXinjia 第二点的这个 UPDATE 是什么写法，有点看不懂啊

F281M6Dh8DXpD1g2

Nov 27, 2023

接口是接口，实现是实现

ming2050

Nov 27, 2023

话说只给了部分代码不好分析，还是得统计下每一部分的时间，在做调整。
1. 每次只查询 1000 条数据的时间
2. SM 算法每次只加密 1000 条数据时间
3. 每次只更新 100 条数据的时间

ZhanXinjia

Nov 27, 2023

@150530 就是用你原始的 id （唯一索引）和更新的结果（加密后的字符串）用 union all 拼接成一个临时表，然后根据原始表和临时表有一样的 id 来一一对应起来更新。

kestrelBright

Nov 27, 2023

楼上说了加 rewriteBatchedStatements 参数

150530

Nov 27, 2023

@ZhanXinjia 懂了懂了 UNION ALL 组虚拟表学到了

matepi

Nov 27, 2023

insert 一张空表效率先看看？
如果空表效率可以，那么就可以 insert 完，再做联表 update
如果空表效率不可以，说明本身 batch 形式用法还存在问题

litchinn

Nov 27, 2023

postgresql 有 rewriteBatchedStatements 参数吗

cnoder

Nov 27, 2023

直接 update 吗，不应该是先双写嘛

Reminders

Nov 27, 2023 via iPhone

@litchinn 有的

litchinn

Nov 27, 2023

@codingbody 那我还真不知道，我只见过 reWriteBatchedInserts

qee

Nov 27, 2023

@cubecube 主键 id 我留了，测试表其他的索引我为了排除影响都干掉了

qee

Nov 27, 2023

@akira 加密很快，问题在于数据库的 update 操作

qee

Nov 27, 2023

@xiwh connection.setAutoCommit(false);然后再 commit ，这是个整体提交的事务，但是我看到最终连接后，执行再数据库的连接慢，至于数据库里面怎么执行慢的不确定了

qee

Nov 27, 2023

@ZhanXinjia 上面就是用的原始 jdbc ；如果用这个 update 拼接的写法，单个 SQL 很长，我有点担心 sql 能否执行下去；我先得把单线程的效率干上去，才能去考虑多线程。

ZhanXinjia

Nov 27, 2023

@qee 我的实践是一次刷 1000 条，这个 size 效果比较好。四个线程一起刷。

souryou

Nov 27, 2023

我记得 pg 事务更新底层是全量拷贝，而且在处理 mvcc 就更慢了。建议按照 1 楼老哥的方法，不过可以试试边查边改

qee

Nov 30, 2023

事实证明，1.rewriteBatchedStatements 参数作为 url 的传参并未生效，pg 的执行方式还是单条导致慢，2.用 2 楼的方式使用单次 update 效率是可以接受的，不过具体的更新条目量得根据实际情况调整； 3. update case when 的写法不推荐，特别是多参数大量更新时可能出现超长的问题。