MySQL 批量更新时用 CASE WHEN 还是 ON DUPLICATE KEY UPDATE？一次压测后的选择
最近在做一个订单状态同步的定时任务，需要把上游系统推送的一批订单状态批量落库。单次处理量大概在 500 到 2000 条之间，表上除了主键 id 还有唯一索引 order_no，状态更新时必须同时刷新 updated_at 字段。最初图省事直接用 foreach 循环逐条 update，结果在并发跑两个任务时出现了明显的行锁等待，慢查询日志里 update 语句的耗时从几毫秒涨到几百毫秒。后来改成批量 SQL，在 CASE WHEN 和 ON DUPLICATE KEY UPDATE 之间犹豫了一阵，因为两者在锁行为、自增主键消耗和 SQL 长度上的表现并不一样。 这里把实际使用的两种写法和压测中观察到的现象记录下来，用的是一张简化后的订单表。 ```sql CREATE TABLE `order_sync` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL, `status` tinyint NOT NULL DEFAULT 0, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` 用 CASE WHEN 做批量更新，本质是把多条 update 合并成一条，靠主键或唯一键定位行： ```sql UPDATE order_sync SET status = CASE order_no WHEN 'NO20240501001' THEN 2 WHEN 'NO20240501002' THEN 3 WHEN 'NO20240501003' THEN 2 ELSE status END, updated_at = NOW() WHERE order_no IN ('NO20240501001','NO20240501002','NO20240501003'); ``` 这种写法在 1000 条以内执行计划是 range 扫描唯一索引，然后回表更新，行锁范围取决于 where 条件命中的索引区间。因为 `order_no` 上有唯一索引，命中的是等值区间，没有出现间隙锁扩散到全表的情况。但要注意 ELSE status 不能省，否则没匹配到的行会被置成 NULL，而 status 是 NOT NULL 的，会直接报错。 换成 ON DUPLICATE KEY UPDATE 则是另一套逻辑： ```sql INSERT INTO order_sync (order_no, status, updated_at) VALUES ('NO20240501001', 2, NOW()), ('NO20240501002', 3, NOW()), ('NO20240501003', 2, NOW()) ON DUPLICATE KEY UPDATE status = VALUES(status), updated_at = VALUES(updated_at); ``` 它在插入冲突时走更新分支。压测时发现两个问题。一是自增主键会被消耗，即使最终走的是 update 分支，InnoDB 也会先分配一个自增值，对于自增 id 用得很紧的表，批量 upsert 会让 id 出现大量空洞。二是发生死锁的概率比 CASE WHEN 高，当同一批数据里 order_no 顺序不一致、且并发任务处理的数据集有交集时，加锁顺序不同就容易互相等待。 实际测试环境里，1000 条数据、两个并发任务、每批随机打乱 order_no 顺序跑了 50 轮，CASE WHEN 写法没有出现死锁，ON DUPLICATE KEY UPDATE 出现了 3 次 Deadlock found when trying to get lock。这不是严谨的基准测试，但足以说明在已知全部是更新、且能保证 order_no 唯一的情况下，CASE WHEN 的锁行为更可控。 代码里最终选的是 CASE WHEN，用 Python 拼 SQL 时注意两点：单条 SQL 的 max_allowed_packet 限制，以及按 500 条一批切分，避免 SQL 过长导致解析开销上升。 ```python def build_batch_update(rows, batch_size=500): for i in range(0, len(rows), batch_size): chunk = rows[i:i + batch_size] cases = " ".join( f"WHEN %s THEN %s" for _ in chunk ) params = [] order_nos = [] for order_no, status in chunk: params.extend([order_no, status]) order_nos.append(order_no) placeholders = ",".join(["%s"] * len(order_nos)) sql = ( "UPDATE order_sync SET status = CASE order_no " + cases + " ELSE status END, updated_at = NOW() WHERE order_no IN (" + placeholders + ")" ) yield sql, params + order_nos ``` 这个函数配合 pymysql 的 executemany 或者直接 execute 都可以，参数顺序是先 CASE 里的 WHEN/THEN 对，再是 IN 里的 order_no。线上跑下来单批 500 条耗时稳定在 15ms 上下，跟逐条 update 相比减少了网络往返次数，锁等待也降下来了。如果业务场景里存在部分记录不存在需要插入的情况，那 ON DUPLICATE KEY UPDATE 仍然更合适，只是要接受自增 id 空洞和潜在的锁顺序问题，或者在应用层先把数据按 order_no 排序再提交。 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1201.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1202.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1203.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1204.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1205.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1206.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1207.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1208.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1209.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1210.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1211.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1212.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1213.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1214.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1215.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1216.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1217.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1218.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1219.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1220.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1221.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1222.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1223.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1224.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1225.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1226.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1227.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1228.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1229.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1230.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1231.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1232.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1233.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1234.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1235.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1236.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1237.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1238.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1239.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1240.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1241.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1242.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1243.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1244.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1245.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1246.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1247.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1248.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1249.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1250.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1251.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1252.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1253.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1254.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1255.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1256.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1257.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1258.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1259.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1260.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1261.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1262.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1263.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1264.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1265.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1266.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1267.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1268.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1269.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1270.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1271.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1272.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1273.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1274.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1275.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1276.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1277.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1278.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1279.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1280.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1281.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1282.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1283.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1284.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1285.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1286.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1287.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1288.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1289.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1290.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1291.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1292.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1293.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1294.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1295.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1296.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1297.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1298.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1299.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1300.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1301.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1302.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1303.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1304.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1305.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1306.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1307.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1308.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1309.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1310.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1311.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1312.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1313.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1314.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1315.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1316.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1317.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1318.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1319.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1320.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1321.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1322.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1323.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1324.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1325.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1326.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1327.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1328.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1329.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1330.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1331.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1332.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1333.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1334.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1335.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1336.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1337.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1338.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1339.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1340.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1341.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1342.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1343.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1344.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1345.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1346.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1347.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1348.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1349.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1350.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1351.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1352.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1353.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1354.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1355.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1356.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1357.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1358.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1359.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1360.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1361.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1362.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1363.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1364.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1365.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1366.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1367.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1368.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1369.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1370.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1371.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1372.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1373.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1374.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1375.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1376.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1377.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1378.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1379.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1380.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1381.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1382.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1383.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1384.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1385.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1386.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1387.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1388.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1389.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1390.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1391.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1392.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1393.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1394.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1395.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1396.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1397.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1398.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1399.html 推荐查看天气：http://www.tianqi98.com/jizhuanwan/1400.html
