这是一个创建于 738 天前的主题，其中的信息可能已经有所发展或是发生改变。
最近客户要对数据库存储的数据做国密改造，提供了相关的加密 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(); 这个哪位有好的优化思路吗，或者别的方案
 | 1 xyooyx 2023-11-27 08:45:42 +08:00 整个表读出来改（高性能机器/分布式计算），改完写到 table_modified,然后重命名下表 |
 | 2 ZhanXinjia 2023-11-27 08:48:21 +08:00 这么搞肯定慢。 第一点：不要用框架，框架比较耗时，直接用 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; 第三点：多线程干。 |
| 3 ZhanXinjia 2023-11-27 08:49:32 +08:00 之前做过类似的加密，一分钟可以加密 50 万条左右 |
 | 4 cubecube 2023-11-27 08:57:46 +08:00 id 上的索引你得留着呀 |
 | 5 wwwz 2023-11-27 09:00:05 +08:00 之前好像搞过，用 replace into 效率比较高 |
 | 6 akira 2023-11-27 09:07:10 +08:00 加密 ，更新 分别耗时多少。 |
 | 7 so2back 2023-11-27 09:10:38 +08:00 试试 update case when 的写法，拼一条语句更新 2000 条记录，前些天用 mysql 试过，1 分钟可以更新 100w |
 | 8 WayneXxx PRO 慢的主要原因是你没提前开一个事务, (貌似 pgsql 关了自动提交，executeBatch 每条语句都是一个独立的事务)，所以执行前可以提前开一个事务 还有两种更快方案： 依然还是用 batchexecute 1. 基于 pgsql INSERT...ON CONFLICT DO UPDATE （主键冲突则更新）实现批量更新 2. 复制一张表，在这张表的基础上批量插入，执行完了再把名字改回去（相比第一种更快） |
 | 9 qizheng22 2023-11-27 09:33:10 +08:00 在连接加上：rewriteBatchedStatements=true |
 | 10 BBCCBB 2023-11-27 09:57:02 +08:00 楼上说了 加 rewriteBatchedStatements 参数 |
 | 11 kaf 2023-11-27 10:07:07 +08:00 写临时表然后重命名 |
 | 12 150530 2023-11-27 10:11:36 +08:00 @ZhanXinjia 第二点的这个 UPDATE 是什么写法，有点看不懂啊 |
 | 13 F281M6Dh8DXpD1g2 2023-11-27 10:13:28 +08:00 接口是接口，实现是实现 |
 | 14 ming2050 2023-11-27 10:15:33 +08:00 话说只给了部分代码不好分析，还是得统计下每一部分的时间，在做调整。 1. 每次只查询 1000 条数据的时间 2. SM 算法每次只加密 1000 条数据时间 3. 每次只更新 100 条数据的时间 |
| 15 ZhanXinjia 2023-11-27 10:25:30 +08:00 @150530 就是用你原始的 id （唯一索引）和更新的结果（加密后的字符串）用 union all 拼接成一个临时表，然后根据原始表和临时表有一样的 id 来一一对应起来更新。 |
 | 16 kestrelBright 2023-11-27 10:29:10 +08:00 楼上说了加 rewriteBatchedStatements 参数 |
| 17 150530 2023-11-27 10:32:32 +08:00 @ZhanXinjia 懂了懂了 UNION ALL 组虚拟表学到了 |
 | 18 matepi 2023-11-27 10:36:09 +08:00 insert 一张空表效率先看看？ 如果空表效率可以，那么就可以 insert 完，再做联表 update 如果空表效率不可以，说明本身 batch 形式用法还存在问题 |
 | 19 litchinn 2023-11-27 10:49:11 +08:00 postgresql 有 rewriteBatchedStatements 参数吗 |
 | 20 cnoder 2023-11-27 11:30:19 +08:00 直接 update 吗，不应该是先双写嘛 |
 | 21 codingbody 2023-11-27 12:32:16 +08:00 via iPhone @litchinn 有的 |
| 22 litchinn 2023-11-27 12:41:02 +08:00 @codingbody 那我还真不知道，我只见过 reWriteBatchedInserts |
 | 25 qee OP @xiwh connection.setAutoCommit(false);然后再 commit ，这是个整体提交的事务，但是我看到最终连接后，执行再数据库的连接慢，至于数据库里面怎么执行慢的不确定了 |
| 26 qee OP @ZhanXinjia 上面就是用的原始 jdbc ；如果用这个 update 拼接的写法，单个 SQL 很长，我有点担心 sql 能否执行下去；我先得把单线程的效率干上去，才能去考虑多线程。 |
| 27 ZhanXinjia 2023-11-27 14:29:26 +08:00 @qee 我的实践是一次刷 1000 条，这个 size 效果比较好。四个线程一起刷。 |
 | 28 souryou 2023-11-27 15:05:31 +08:00 我记得 pg 事务更新底层是全量拷贝，而且在处理 mvcc 就更慢了。建议按照 1 楼老哥的方法，不过可以试试边查边改 |
| 29 qee OP 事实证明，1.rewriteBatchedStatements 参数作为 url 的传参并未生效，pg 的执行方式还是单条导致慢，2.用 2 楼的方式使用单次 update 效率是可以接受的，不过具体的更新条目量得根据实际情况调整； 3. update case when 的写法不推荐，特别是多参数大量更新时可能出现超长的问题。 |
Select Language