数据库批量更新高效的方法--1分钟变5秒

数据库批量更新高效的方法--1分钟变5秒

使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。oracle 、postgres、dm等数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到一定的优化作用。

修改前语句

利用foreach 批量执行多条语句方式。

 <foreach collection="list" item="entity" separator=";">
    update  GRID_FORECAST_V3
    <trim prefix="set" suffixOverrides=",">
        <if test="entity.qianqi_level != null">QIANQI_L = #{entity.qianqi_level,jdbcType=INTEGER},</if>
        <if test="entity.yubao_level != null"> YUBAO_L = #{entity.yubao_level,jdbcType=INTEGER},</if>
        <if test="entity.youfa_level != null">YOUFA_L = #{entity.youfa_level,jdbcType=INTEGER},</if>
    </trim>
    where ROW_COL = #{entity.row_col,jdbcType=VARCHAR} and xzqdm like CONCAT( #{entity.xzqdm,jdbcType=VARCHAR},'%')
</foreach> 

修改后语句

在使用 WITH AS 语句时，可以提前定义一些公共的表达式，并将其命名，在后续的操作中可以通过这些表达式进行查询、筛选、聚合等操作，从而让 SQL 语句更加简洁高效。

在批量执行 update 语句时，使用 WITH AS 语句可以减少重复update数据库的次数，从而提高效率。这里，grid_update_values 是公共表表达式，包含了需要更新的 row_col 和新值。在 UPDATE 语句中，通过将 UPDATE 和 FROM 关键字结合使用，来关联临时表和要更新的表，并根据 row_col进行匹配和更新需要更新的字段。如下所示：

 WITH grid_update_values (row_col, qianqi_l, yubao_l, youfa_l) AS (
VALUES
    <foreach collection="list" item="entity" separator="," >
       (
        #{entity.row_col,jdbcType=VARCHAR},
        #{entity.qianqi_level,jdbcType=INTEGER},
        #{entity.yubao_level,jdbcType=INTEGER},
        #{entity.youfa_level,jdbcType=INTEGER}
        )
    </foreach>
)
UPDATE grid_forecast_v3  t
SET
qianqi_l = uv.qianqi_l,
yubao_l = uv.yubao_l,
youfa_l = uv.youfa_l
FROM
grid_update_values uv
WHERE t.row_col = uv.row_col

执行效果

需要执行数据3W多条，总体来说执行效果显著，原来58秒，减少到5秒。

优化前： StopWatch '': running time (millis) = 58616

ms % Task name

01362 002% 查询网格数据

00960 002% 查询预警模型配置配置

00022 000% 网格模型计算

56272 096% 数据更新入库

优化后： StopWatch '': running time (millis) = 5198

ms % Task name

01233 024% 查询网格数据

01076 021% 查询预警模型配置配置

00029 001% 网格模型计算

02860 055% 数据更新入库

注意事项

批次数据量

在实际使用中使用 WITH AS 语句时，需要根据具体情况进行调整批次数据量的大小，以达到最佳的效果。在测试环境 中虚拟表每次数量 1000时 ，入库时间 2.8s ， 2000 时候 6s,并不是批次量越多越好，要根据数据库环境验证最佳参数。

本项目中使用1000条/次。

MyBatis-Plus设置

遇到net.sf.jsqlparser.parser.ParseException异常，首先排查SQL语句是否出错，再去排查是否是框架的限制。MyBatis-Plus使用了 jsqlparser 执行sql检查时候的误报。

Options.FlushCachePolicy.FALSE 指示 MyBatis-Plus 在执行 SQL 时不进行参数的检查。

// 诱发降雨级别修改 
@SqlParser(filter = true) 
@Options(flushCache = Options.FlushCachePolicy.FALSE) 
int jyyfdjUpdate(@Param("list") List<GridrainEntity> list);