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

近期由于安全等保测评要求必须提高数据库信息的安全等级, 需要对已有数据进行加密处理… 表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秒. 速度也是非常快的.