jdbc 执行批量 update 的效率问题

[qee]

最近客户要对数据库存储的数据做国密改造，提供了相关的加密 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();
    

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

[lqw3030]

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

[ZhanXinjia]

这么搞肯定慢。
第一点：不要用框架，框架比较耗时，直接用 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]

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

[cubecube]

id 上的索引你得留着呀

[wwwz]

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

[akira]

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

[so2back]

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

[xiwh]

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

[qizheng22]

在连接加上：rewriteBatchedStatements=true

[BBCCBB]

楼上说了 加 rewriteBatchedStatements 参数

[kaf]

写临时表然后重命名

[150530]

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

[liprais]

接口是接口，实现是实现

[mringg]

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

[ZhanXinjia]

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

[kestrelBright]

楼上说了加 rewriteBatchedStatements 参数

[150530]

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

[matepi]

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

[litchinn]

postgresql 有 rewriteBatchedStatements 参数吗

[cnoder]

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

[codingbody]

@litchinn 有的

[litchinn]

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

[qee]

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

[qee]

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

[qee]

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

[qee]

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

[ZhanXinjia]

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

[souryou]

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

[qee]

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