面试题：MySQL同表更新优化：批量数据调整

Question

有一张商品库存表products，包含字段id（商品编号）, stock（库存数量）, category（商品类别）。现在需要将电子产品类别的商品库存数量在原有基础上增加10%，但要保证操作过程中尽量减少锁的影响和对性能的冲击。请设计优化的更新策略并写出对应的SQL语句，同时解释优化点和可能存在的风险。

Answer 1

更新策略

1. 分批更新：将电子产品类别数据按一定数量分成多个批次进行更新，避免一次性更新大量数据导致长时间锁表。
2. 使用行级锁：通过WHERE条件精准定位要更新的行，减少锁的范围。

SQL语句

假设数据库为MySQL，示例代码如下：

-- 确定每次更新的批次大小
SET @batchSize = 1000;
-- 定义偏移量
SET @offset = 0;

-- 循环更新
WHILE TRUE DO
    UPDATE products
    SET stock = stock * 1.1
    WHERE category = '电子产品'
    LIMIT @batchSize OFFSET @offset;
    
    -- 如果更新的行数小于批次大小，说明已更新完所有数据，退出循环
    IF ROW_COUNT() < @batchSize THEN
        LEAVE;
    END IF;
    
    -- 更新偏移量
    SET @offset = @offset + @batchSize;
END WHILE;

优化点

1. 分批更新：减少每次更新的数据量，降低锁的持有时间，对其他事务的影响更小，提升系统整体性能。
2. 行级锁：通过精准的WHERE条件，只锁定需要更新的行，而不是整个表，减少锁冲突。

可能存在的风险

1. 事务一致性问题：由于是分批更新，如果在更新过程中系统出现故障，部分数据更新成功，部分未更新，可能导致数据不一致。可通过引入事务回滚机制或使用数据库自带的恢复功能来解决。
2. 并发问题：虽然采用行级锁减少锁冲突，但在高并发场景下，仍可能存在更新丢失或数据不一致的情况。可以通过合理设置事务隔离级别或使用乐观锁机制来处理。
3. 性能问题：分批更新会带来额外的循环开销，如果批次设置过小，可能导致更新次数过多，影响性能；批次设置过大，又可能无法有效减少锁的影响。需要根据实际数据量和系统性能进行调优。