优化MySQL ON DUPLICATE KEY UPDATE：单次条件判断减少I/O操作

你当前使用的SQL语句如下：

INSERT INTO test 
    (id, name, customerId, timestamp, status, lastTimestamp, testId, testCapacity)
    values(1, 'test1', 2, 1717415401000, 'SAFE', 1717415401000, 'test123', 50)
ON DUPLICATE KEY UPDATE
    lastTimestamp = CASE WHEN timestamp < VALUES(timestamp) THEN VALUES(timestamp) ELSE lastTimestamp END,
    status = CASE WHEN timestamp < VALUES(timestamp) THEN VALUES(status) ELSE status END,
    testCapacity = CASE WHEN timestamp < VALUES(timestamp) THEN VALUES(testCapacity) ELSE testCapacity END,
    timestamp = CASE WHEN timestamp < VALUES(timestamp) THEN VALUES(timestamp) ELSE timestamp END

由于每个更新字段都重复执行timestamp < VALUES(timestamp)的判断，随着查询量增加会额外消耗数据库资源。可以通过用户变量复用条件判断结果的方式优化，只执行一次条件校验，代码如下：

INSERT INTO test 
    (id, name, customerId, timestamp, status, lastTimestamp, testId, testCapacity)
VALUES(1, 'test1', 2, 1717415401000, 'SAFE', 1717415401000, 'test123', 50)
ON DUPLICATE KEY UPDATE
    @should_update := (timestamp < VALUES(timestamp)),
    lastTimestamp = IF(@should_update, VALUES(timestamp), lastTimestamp),
    status = IF(@should_update, VALUES(status), status),
    testCapacity = IF(@should_update, VALUES(testCapacity), testCapacity),
    timestamp = IF(@should_update, VALUES(timestamp), timestamp);

优化说明 #

• 先通过@should_update := (timestamp < VALUES(timestamp))将条件判断结果存储到用户变量中，仅执行一次比较操作
• 后续所有字段的更新逻辑直接复用@should_update变量的结果，避免重复计算和I/O开销
• 用户变量@should_update的作用域为当前会话，不会产生跨请求的冲突问题

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