java-批量更新优化

往往在我们的系统中会有一些批量更新的业务，我们公司现在的业务场景如下:

有如下的excel需要导入更新

| 食材名称 | 食材价格 |
| — | — |
| 猪肉 | 20.61 |
| 白菜 | 1.06 |
| 土豆 | 4.57 |
| ..省略10W条 | |

需要通过表格中的食材名称和系统做匹配，更新系统中的猪肉价格

许多人的实现逻辑如下

// list中保存表格中的行的对象
List list
for(Object o : list){
    xxxMapper.updateUnitPriceByName(xxx);
}

当数据量比较小时，如上代码并没有问题，当我们系统在测试环境用大数据量测试时，发现大几千条excel数据就要跑7-8分钟,通过日志可以看出日志一直在输出update xxx set xxx where name = xxx ,于是定位到了循环中的update导致请求太慢

通过百度一些大佬的文章后知道，虽然我们现在有了数据库连接池，但是每操作一次数据库，只要是sql正常的情况下，每次操作数据库到最终数据库反馈给系统这段时间中,40%的时间会花在获取数据库连接和连接初始化以及数据库连接归还于关闭，所以循环中做update操作很不理智

解决方案，先将excel的数据存入临时表，然后将食材表与临时表join后关联批量更新

具体实现如下

1. 创建食材id与对应的价格的临时表

   CREATE TEMPORARY TABLE temp ( `ingredient_id` BIGINT NOT NULL, `price` DECIMAL ( 10, 2 ) )
   

2. 批量插入食材与之对应的价格

   INSERT INTO temp (ingredient_id, price)
   VALUES
   (ingredientId,price),(ingredientId,price),(ingredientId,price)...
   

3. 联表批量更新

   UPDATE tablename tn 
   INNER JOIN temp t ON tn.id = t.ingredient_id
   SET tn.price = t.price
   

4. 删除临时表

   DROP TEMPORARY TABLE IF EXISTS temp