MySQL千万级记录多列批量更新的最优性能方案

现有一张存储多年传感器采集数据的气象数据表，需新增soil_temp_2in（2英寸深度土壤温度）、soil_temp_8in（8英寸深度土壤温度）两个字段，回填过去数年的历史缺失土壤温度数据。本次回填覆盖约350个气象站点，时间跨度3年、数据采集频率为每15分钟1条，总计待更新记录约3600万条。

目标表结构 #

待填充字段的表结构示例如下：

+---------------------+------------+----------+----------+ --- +---------------+---------------+
|       tstamp        | station_id | air_temp | humidity | ... | soil_temp_2in | soil_temp_8in |
+---------------------+------------+----------+----------+ --- +---------------+---------------+
| 2021-01-01 00:00:00 |     124    |  45.5    |   56.7   | ... |     NULL      |      NULL     | 
+---------------------+------------+----------+----------+ --- +---------------+---------------+
| 2021-01-01 00:15:00 |     124    |  46.3    |   54.5   | ... |     NULL      |      NULL     | 
+---------------------+------------+----------+----------+ --- +---------------+---------------+
| 2021-01-01 00:30:00 |     124    |  45.9    |   55.6   | ... |     NULL      |      NULL     | 
+---------------------+------------+----------+----------+ --- +---------------+---------------+
|        ...          |     ...    |  ...     |   ...    | ... |     ...       |      ...      |
+---------------------+------------+----------+----------+ --- +---------------+---------------+

该表主键为tstamp与station_id组成的联合主键，两个字段同时建有独立索引。

初始实现方案 #

目前已获取包含全部缺失土壤温度数据的CSV文件，最朴素的实现方式是通过PHP等语言逐行遍历CSV内容，为每条记录执行单条更新语句，语句示例如下：

UPDATE weather_table
SET
    soil_temp_2in = $t_2in,
    soil_temp_8in = $t_8in
WHERE
      tstamp = '$tstamp'
  AND station_id = $station_id;

经评估该逐行更新方案执行耗时极长，现需验证可行的提速方案。本次更新可接受低峰期执行或临时停服，无需顾虑数据库并发锁影响，核心诉求为尽可能缩短更新总耗时。

待验证优化思路 #

• 是否存在批量上传、批量更新值的高效方案？
• 先将CSV中的缺失数据导入临时表，再通过临时表关联更新目标表，相比PHP逐行读取CSV执行更新是否效率更高？关联更新参考语法如下：

UPDATE table1
SET column1 = (SELECT expression1 FROM table2 WHERE conditions)
WHERE conditions;

• 使用MySQL侧的游标循环替代PHP层循环执行更新，是否能提升执行效率？
• 每数千条记录开启并提交一次事务，是否能提升更新速度？
• 在上述单条更新语句中添加LIMIT 1语法，是否能提升执行效率？

内容的提问来源于stack exchange，提问作者Stefano