如何提升MySQL与SQL Server中批量UPDATE操作的执行速度？

结合你给出的Schema和执行计划，咱们先明确核心问题：MySQL中单条UPDATE的批量提交性能远低于INSERT，根源在于单条UPDATE的触发开销（触发器、自动更新字段）+ JDBC驱动默认未优化批量UPDATE的网络传输，而SQL Server对批量单条UPDATE的处理更高效。下面给出几个兼容两种数据库的优化方案，按优先级排序：

1. 调整JDBC连接配置（最快见效，兼容双库） #

针对MySQL驱动的批量处理短板，在JDBC URL中添加以下参数——这些参数对SQL Server驱动无影响（会被忽略），可以统一配置：

jdbc:mysql://your-host:port/your-db?rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048

• rewriteBatchedStatements=true：让MySQL驱动把批量提交的单条UPDATE语句合并为更少的网络请求（类似批量INSERT的处理方式），直接减少网络往返开销；
• useServerPrepStmts=true + cachePrepStmts=true：开启预编译语句缓存，避免重复编译相同模板的UPDATE语句，降低服务器端开销；
• prepStmtCacheSize和prepStmtCacheSqlLimit：设置合理的缓存大小，适配你的批量语句数量和长度。

开启这些参数后，MySQL的批量UPDATE性能会快速接近SQL Server的水平，同时完全兼容现有代码逻辑。

2. 使用兼容双库的批量UPDATE语法（通用SQL） #

如果JDBC配置优化后仍达不到预期，可以把多条单条UPDATE合并为一条CASE WHEN风格的批量UPDATE语句，这种语法同时支持MySQL和SQL Server：

UPDATE data
SET value = CASE
    WHEN parentId = 52005 AND seriesDate = '2018-04-20' AND valueDate = '2000-02-11' THEN -2367.0
    WHEN parentId = 52031 AND seriesDate = '2013-01-10' AND valueDate = '2010-04-20' THEN 1.12344
    -- 继续添加更多更新规则
END
WHERE 
    (parentId = 52005 AND seriesDate = '2018-04-20' AND valueDate = '2000-02-11')
    OR (parentId = 52031 AND seriesDate = '2013-01-10' AND valueDate = '2010-04-20')
    -- 匹配所有要更新的行

注意事项： #

• 触发器和自动更新字段：这条语句会对每个匹配的行触发BEFORE UPDATE触发器（versionstamp自增），同时last_modified的ON UPDATE规则也会正常生效，完全符合你的Schema要求；
• 分批次处理：避免单条语句过长超过数据库的max_allowed_packet（MySQL）或类似限制，建议每500-1000条更新合并为一个批次；
• 空值/无变化处理：如果某个行的value不需要更新，不要加入CASE分支，避免不必要的行更新。

这种方式把1000次网络请求压缩为1次，性能提升非常明显，且完全兼容两种数据库。

3. 业务允许的情况下，用DELETE + INSERT替代UPDATE #

如果你的业务逻辑允许（不需要保留原createdDate，且versionstamp的自增规则可以接受从1开始），可以继续沿用你现有的批量DELETE + 批量INSERT方案：

1. 先批量删除需要更新的行：DELETE FROM data WHERE parentId = ? AND seriesDate = ? AND valueDate = ?（批量提交）；
2. 再用批量INSERT插入新数据。

这种方式的性能和批量INSERT完全一致，因为避免了UPDATE的触发器和字段更新开销，但要注意：

• 会丢失原createdDate的值，替换为INSERT时的时间；
• versionstamp会被重置为默认值1，如果你用这个字段做乐观锁，这个方案不适用。

为什么你的现有UPDATE在MySQL中慢？ #

从执行计划来看，UPDATE已经命中了PRIMARY KEY，索引没问题。慢的核心原因是：

• 每条UPDATE都要单独触发BEFORE UPDATE触发器和last_modified的自动更新，1000次更新就有1000次触发开销；
• 未开启rewriteBatchedStatements时，MySQL驱动会把每条UPDATE单独发送到服务器，1000次更新就有1000次网络往返，而批量INSERT是合并为一条语句发送，网络开销可以忽略。

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