大数据量更新某个字段的值如何提高效率.

最新推荐文章于 2023-06-05 09:05:48 发布
最新推荐文章于 2023-06-05 09:05:48 发布
阅读量2.2k 收藏 3
点赞数
CC 4.0 BY-SA版权
分类专栏： net
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/phker/article/details/103166977
为响应安全等保要求，本文分享了一种高效的数据库批量加密更新方案。通过将数据按月分批加载至内存，利用SqlBulkCopy批量插入及优化的更新语句，显著提升处理速度，避免业务长时间中断。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

近期由于安全等保测评要求必须提高数据库信息的安全等级, 需要对已有数据进行加密处理… 表W_Report 有1千万左右的数据, 如果用ado.net 批量查出来一批,for循环内存中逐条加密后再update回去的方式. 初步测试了一下每秒 21 条.

这种肯定 是最原始的方案,1千万条估算了下要80小时, 全部处理完要3天. 升级过程中得停止系统. 业务停3天是不可能的. 再加上其它的服务器硬件给力, 算每秒100条, 最起码也要停16个小时. 这肯定不行.

毛主席教导我们: 只要脑袋不熄火,办法总比困难多.

下面采用了新的方案批量处理,

新方案的思路是
第一步:将数据按月份分批批量加载到系统内存中,一次性加载1个月的. 大概有20万条数据. 使用Ado.net 读取到 DataReader,
第二步:逐行处理,在内存中计算加密后的数据,然后保存到DataTable
第三步:使用SqlBulkCopy 批量将加密后的DataTable 插入到一张临时表
第四步:使用Update语句一次性将临时表的数据更新到老表
第五步:清空临时表中的数据, 准备处理下一个月的数据.

此方案每一步的隐患都很多
第一步:不知道加载时间多少.
第二步:少不了的计算时间
第三步:SqlBulkCopy 批量插入10万条数据据说只要1-2秒.
第四步:Update语句用一个表去更新另外一个表的性能不知如何.
第五步:清空临时表,可以使用直接删表的方式, 速度会很快.秒秒钟.

为了不做无用功, 先测验第四步的性能如何:
1.先取一个月的数据插入到临时表当作测试用. 共取了 157375条数据 用时不到1秒
这里只取了3个需要加密的字段和主键.

select  Id, PatientName,PatientIDCard,PatientPhone  into TempReportNewData from W_Report WHERE ReportTime  BETWEEN '2019-11-01' and '2019-11-30'

第一次测试. 无主键 再更新回去到老表 用时 157375条数据 272.486s 平均每秒578条数据

update W_Report 
set 
PatientName = tmp.PatientName,
PatientIDCard = tmp.PatientIDCard,
PatientPhone = tmp.PatientPhone
from TempReportNewData as tmp
where W_Report.Id = tmp.Id

第二次测试. 加主键后 再更新回去 用时 157375条数据 279.281s 平均每秒576条数据
比原来的还慢了点, 影响不大.先不管了, 当作没变化.

第三次测试,. 在主表的更新条件中加时间索引字段作为条件.157375条数据 用时 4.055s秒
我当时就傻眼了. 这么快的速度.

update W_Report 
set 
PatientName = tmp.PatientName,
PatientIDCard = tmp.PatientIDCard,
PatientPhone = tmp.PatientPhone
from TempReportNewData as tmp
where W_Report.Id = tmp.Id 
and W_Report.ReportTime  BETWEEN '2019-11-01' and '2019-11-30'

至此,第四步的效率从一定的程度上来看,还是可以接受的,
时间范围半个月应该更快, 因为有时间索引做过滤条件可以大幅提高更新速度. 毕竟时间索引可以跳过好多数据的排查过程. 灵活运用索引很有必要.
在这次的测试过后,又冒出来一个新的想法, 可以先把前面的3步提前做了.先逐条计算好放到临时表中. 然后等升级切换的时候再批量分批一个月一个月的更新到W_report
那就是分分钟的事情了. 毕竟这些3个月之前的老数据这些字段的数据是不会变的. 可以提前做.

明天再继续测试 取数据的效率和加密的效率.

反正步骤1,2,3可以提前做, 慢点也无所谓, 压力不大了.

又过了一天.经过同事测试,发现我昨天的写的代码效率变低了. 1000多条就要50多秒, 这是为何?
后来同事排查了一下发现

UPDATE W_Report
SET PatientName = tmp.PatientName,
 PatientIDCard = tmp.PatientIDCard,
 PatientPhone = tmp.PatientPhone
FROM
 TempReportNewData AS tmp
WHERE
 W_Report.Id = tmp.Id
AND W_Report.ReportTime BETWEEN '2019-09-27 00:00:00' AND '2019-09-28 00:00:00'
AND tmp.ReportTime BETWEEN '2019-09-27 00:00:00' AND '2019-09-28 00:00:00'

他的语句中加了时分秒, 然后就慢了好几个数量级.

经过测试前面3步 按天从数据库读出来,再计算,然后再批量使用SqlBulkCopy 插入,经过测试34万条数据用了30秒. 速度也是非常快的.

ES 在数据量很大的情况下（数十亿级别）如何提高查询效率？
悟已往之不谏，知来者之可追
03-27 3678
背景 这个问题是肯定要问的，说白了，就是看你有没有实际干过 es，因为啥？其实 es 性能并没有你想象中那么好的。很多时候数据量大了，特别是有几亿条数据的时候，可能你会懵逼的发现，跑个搜索怎么一下 5-10s ，坑爹了。第一次搜索的时候，是 5~10s ，后面反而就快了，可能就几百毫秒。 你就很懵，每个用户第一次访问都会比较慢，比较卡么？所以你要是没玩儿过 es，或者就是自己玩玩儿 demo，被问到这个问题容易懵逼，显示出你对 es 确实玩儿的不怎么样？ 说实话，es 性能优化是没有什么银弹的，啥意思呢？就
MySQL海量数据单一字段更新
Return_head的博客
05-31 830
前言 昨天发了一篇MySQL中索引对千万级数据查询效率优化实践的博文 文章链接：https://blog.csdn.net/Return_head/article/details/90699140 所以手上有着5000万的数据，很多以前想做的实验也有机会去尝试了。今天上课突然想起来一篇数据库的面试题。 大概是说：某公司有一张亿级的用户表，用户表有一个积分字段。这个字段会在每年新年凌晨0点的...
Mysql批量更新字段的数值
weixin_53855006的博客
06-06 1481
MySQL批量更新字段数值/Mysql concat函数
大数据量时更新某字段的两种方案 哪个更好？
whiller
05-30 642
场景描述： 现在有两个表分别为ip和data表， ip表：ipId，startIp，endIp 都是整型 数据量为千级，可以按照1000左右来算 data表：dataId，ip，status 都是整型(另外还有其他字段) 数据量为百万级 可以按照2 000 000左右来算 因为业务上的需要，现在需要更新data表的status值为0或1，规则是：如果ip在【startI...
Mysql 根据id update慢_千万数据量的表, 执行根据ID去update一条数据不会很慢吧
weixin_29056701的博客
01-30 2007
create temporary table tmp(id int(4) primary key,dr varchar(50));insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy');update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;注意：这种方法需要...
数据库批量更新高效的方法--1分钟变5秒
qq_63033995的博客
06-05 788
在 UPDATE 语句中，通过将 UPDATE 和 FROM 关键字结合使用，来关联临时表和要更新的表，并根据 row_col进行匹配和更新需要更新的字段。使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。在使用 WITH AS 语句时，可以提前定义一些公共的表达式，并将其命名，在后续的操作中可以通过这些表达式进行查询、筛选、聚合等操作，从而让 SQL 语句更加简洁高效。需要执行数据3W多条，总体来说执行效果显著，原来58秒，减少到5秒。
mysql批量更新多条记录的同一个字段为不同值的方法
12-16
在MySQL中，批量更新多条记录的同一个字段为不同的值是一项常见的操作，特别是在处理大量数据时，为了提高效率，我们需要避免循环执行单独的UPDATE语句。以下是一些关于如何高效地进行批量更新的方法。 首先，最...
使用tp框架和SQL语句查询数据表中的某字段包含某值
10-16
在IT开发过程中，使用tp框架和SQL语句查询数据表中的某字段包含特定值是一种常见的需求。本文主要讲解如何在tp（ThinkPHP）框架环境下，利用SQL的特定函数来实现此类查询，并提供了代码示例和注意事项。 首先，tp...
根据字段值批量输出shp
09-20
此外，如果数据量大或者字段值多，可能需要调整ArcGIS的内存设置或采用分布式处理策略，以优化性能和资源使用。 总的来说，根据字段值批量输出shp文件是GIS工作中的一项基础技能，它涉及到数据查询、条件筛选、空间...
解析mysql不重复字段值求和
12-15
其原因是distinct只能返回它的目标字段，而无法返回其它字段，这个问题让我困扰了很久，用distinct不能解决的话，我只有用二重循环查询来解决，而这样对于一个数据量非常大的站来说，无疑是会直接影响到效率的。...
oracle 大表新增列 慢_oracle 11g在大表中添加字段及默认值--加速
weixin_39954464的博客
12-19 1557
今天遇到这个问题了。简单的增加语句，默认SQLPLUS执行，却会超时。要增加客户端的TIMEOUT时间才可以解决。(感觉超过两三分钟，默认超时30秒)另外，也可以用两步操作(1，增加字段，2，修改默认值)，或是加以NOT NULL参数。参考URL：http://blog.csdn.net/mikean/article/details/24872955http://blog.csdn.net/wei...
【MySQL】 update 大量数据批量更新
热门推荐
星辰大海
04-20 8万+
文章目录更新字段，多种操作更新一条数据的某个字段，一般这样写：更新同一字段为同一个值，mysql也很简单，修改下where即可：更新多条数据为不同的值，可能很多人会这样写：那么能不能一条sql语句实现批量更新呢？使用case 更新字段，多种操作 mysql更新语句很简单， 更新一条数据的某个字段，一般这样写： UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value'; 更新同一字段为同一个值，mysql也很简单，修改下wh
mysql百万级数据字段更新
qq_35010942的博客
12-13 1555
或许可以用存储过程试试。每次更新1w条，执行264次。 create procedure updateLmData() begin declare i int; set i = 0; while i < 264 do update lm_product_info set ess_onsale = 0 where ess_onsale is null order by id asc limit 10000; set
存储过程，百万级数据数据条件更新
m0_47743175的博客
02-09 362
存储过程，百万级数据数据条件更新
批量修改/批量更新数据库某一个字段的值
随记
03-28 1万+
用字符串替换函数replace update 表名 set 字段名 = replace(字段名,'http://zencart.me','zencart.me') replace(url, 'aaa', 'bbb') 【将url字段中的aaa批量更改为bbb】 例子： 我要
如何提高数据库update更新的速度
达叔
08-05 7159
不用不知道，一用吓一跳。。看下面这条SQL语句 String sql="update cats set name_alias='"+rs.getString(1)+"'where name='"+rs.getString(1)+"'"; 如果数据量很小的话，肯定不会存在什么问题，一眨眼就更新好了。但如果是大一点的表，不用说几百万条，几万条记录就已经要耗费小时级别的时间了。 解决方法就是whe
Datatable当列非常多时更新数据耗时[解决方案]
遥远的心
12-14 3052
Datatable是.net下一个非常好用的数据结构，一般情况下当列的数据量再1000以内的数量级，操作不需要考虑性能。这里为什么提到列，因为Datatable对行的处理非常好，如果列不是很多的时候，插入几万行乃至几十万行数据都是非常快速的。 但是当列过多时，这里就有可能带来非常大的性能问题。现在我们建立了一个10000列的table，并添加了10行的数据，来试验一下不同的插入数据方式会带来怎样的性能影响。 方法1：代码如下，这个一种非常好的方式，总共耗时0.104秒 //M
大字段优化
04stone37
11-14 621
大字段类型   Mysql中的text/blob/longtext/longblob/varchar(8192)等占用较大存储空间的字段类型； 影响 占用大量的存储空间。因为每个大字段溢出的数据都会存储在单独页中（存储独享），现象就是占用的存储空间远超过实际的数据量； 读写性能下降。相同的内存能够缓存的记录变少（占用的页变多）； 优化方式 方式1： 压缩&压缩：将多个大字段合并成一个，...
update字段执行效率慢的解决办法
m0_37625704的博客
03-14 2万+
update字段时，执行效率慢可以换成下边这种方式： 解决办法： 用merge into tableA using (括号里面是你需要的字段，来自于其它表的数据 比如 tableB) on(tableA 跟tableB 的关联条件) when matched then update set tableA.id=tableB.id ; 这是句式，下边用案列来证明及 这个句式的具体使用...
若依批量更新某个字段值
最新发布
06-05
<think>我们正在讨论若依框架（RuoYi）中如何批量更新某个字段值。根据引用内容，我们知道若依框架内置了代码生成工具，并且整合了MyBatis-Plus，而MyBatis-Plus提供了强大的CRUD操作功能。在MyBatis-Plus中，批量更新操作可以通过使用`updateBatchById`方法实现，但需要注意的是，该方法默认情况下需要配置事务管理，并且要求主键存在。此外，还可以通过自定义SQL的方式实现批量更新。结合若依框架的结构，通常我们会通过Service层来调用批量更新的方法。以下是一个示例步骤：1.在Service接口中定义批量更新方法。2.在ServiceImpl中实现该方法，可以使用MyBatis-Plus提供的方法或自定义Mapper方法。假设我们有一个实体类`Student`，对应数据库表`my_student`，我们要批量更新`status`字段。###示例代码####1.Mapper接口```java@MapperpublicinterfaceStudentMapperextendsBaseMapper<Student>{//如果使用MyBatis-Plus的BaseMapper，默认已经提供了一些方法，但批量更新需要自定义//可以在此处定义批量更新的方法intupdateBatch(@Param("list")List<Student>list);}```####2.MapperXML文件在对应的MapperXML文件中，编写批量更新的SQL语句：```xml<updateid="updateBatch"><foreachcollection="list"item="item"separator=";">UPDATEmy_studentSETstatus=#{item.status}WHEREid=#{item.id}</foreach></update>```注意：上述SQL语句使用了动态SQL的foreach标签，但这种方法在MySQL中需要支持多条语句执行（在连接字符串中添加`allowMultiQueries=true`）。另一种方式是使用CaseWhen的方式：```xml<updateid="updateBatch">UPDATEmy_studentSETstatus=<foreachcollection="list"item="item"open="CASEid"close="END">WHEN#{item.id}THEN#{item.status}</foreach>WHEREidIN<foreachcollection="list"item="item"open="("separator=","close=")">#{item.id}</foreach></update>```####3.Service接口```javapublicinterfaceIStudentServiceextendsIService<Student>{booleanupdateBatch(List<Student>list);}```####4.Service实现```java@ServicepublicclassStudentServiceImplextendsServiceImpl<StudentMapper,Student>implementsIStudentService{@Override@Transactional(rollbackFor=Exception.class)//添加事务publicbooleanupdateBatch(List<Student>list){returnthis.baseMapper.updateBatch(list)>0;}}```####5.使用事务确保在SpringBoot启动类上添加`@EnableTransactionManagement`（若依框架默认已配置），并且在方法上使用`@Transactional`注解。###注意事项-批量操作时，注意SQL语句的长度，避免一次性更新太多数据导致数据库性能问题。-使用事务保证操作的原子性。-在MySQL中，使用CaseWhen方式更新比多条语句执行效率更高。另外，如果更新的是同一个字段为同一个值，可以使用以下方式：```java//在Service中publicbooleanupdateStatusByIds(List<Long>ids,Stringstatus){LambdaUpdateWrapper<Student>updateWrapper=newLambdaUpdateWrapper<>();updateWrapper.set(Student::getStatus,status).in(Student::getId,ids);returnthis.update(updateWrapper);}```这样就不用写MapperXML，直接使用MyBatis-Plus的条件构造器即可。###总结根据需求选择合适的方法：-如果需要批量更新的每条记录字段值不同，则使用自定义的批量更新SQL（如CaseWhen方式）。-如果批量更新的是同一个字段为同一个值，则使用MyBatis-Plus的UpdateWrapper更加方便。§§相关问题§§1.在若依框架中，如何配置事务以保证批量更新的原子性？2.使用MyBatis-Plus的UpdateWrapper批量更新有什么注意事项？3.除了上述方法，若依框架还有哪些支持批量操作的方式？</think> ### 若依框架批量更新字段值方法 在若依框架中批量更新字段值，可以通过 **MyBatis-Plus 的 UpdateWrapper** 或 **BaseMapper 的批量更新方法** 实现。以下是具体方法和示例代码： --- #### 🛠️ 方法一：使用 UpdateWrapper（推荐） 适用于更新多个实体到相同的字段值： ```java // ServiceImpl 实现类中 @Transactional(rollbackFor = Exception.class) public int batchUpdateStatus(List<Long> ids, String status) { UpdateWrapper<Student> updateWrapper = new UpdateWrapper<>(); updateWrapper .set("status", status) // 设置要更新的字段值 .in("id", ids); // WHERE id IN (...) return studentMapper.update(null, updateWrapper); } ``` --- #### 🧩 方法二：批量执行更新语句 使用 MyBatis-Plus 的批量操作方法： ```java // ServiceImpl 实现类中 @Transactional(rollbackFor = Exception.class) public boolean batchUpdate(List<Student> list) { return studentMapper.updateBatchById(list) > 0; } // 调用示例 List<Student> students = students.stream() .map(s -> { s.setStatus("ACTIVE"); // 设置新值 return s; }).collect(Collectors.toList()); batchUpdate(students); ``` --- #### 📌 关键注意事项 1. **事务控制** 必须添加 `@Transactional` 注解确保原子性 ```java @Transactional(rollbackFor = Exception.class) // 关键注解 ``` 2. **字段映射配置** 确认实体类字段名与数据库列名一致（或通过 `@TableField` 注解映射）： ```java @TableField("status") // 数据库字段名 private String userStatus; ``` 3. **批量大小限制** 建议单次更新数据量不超过 1000 条，可分割执行： ```java List<List<Long>> partitions = Lists.partition(idList, 500); partitions.forEach(part -> batchUpdate(part, "ACTIVE")); ``` 4. **索引优化** 确保 WHERE 条件的字段（如 `id`）有索引，避免全表扫描 --- #### 🔍 完整调用示例 ```java // Controller 层 @PostMapping("/batchUpdateStatus") public AjaxResult batchUpdate(@RequestBody BatchUpdateDTO dto) { return success(studentService.batchUpdateStatus(dto.getIds(), dto.getStatus())); } // DTO 对象 @Data public class BatchUpdateDTO { private List<Long> ids; private String status; } ``` 📌 **最佳实践建议**：优先使用 `UpdateWrapper` 方式（方法一），它会在数据库层面生成单条 SQL 语句，效率更高： ```sql /* 实际执行SQL */ UPDATE my_student SET status = ? WHERE id IN (?,?,...); ``` ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值