面试题：MySQL批量更新性能调优

Question

现有一张MySQL表，其中有10万条记录，需要根据另一张表中的关联数据对其进行批量更新。如何在更新过程中通过合理使用索引、事务等手段，确保更新操作的高效性，同时避免锁争用等性能问题？请给出具体的SQL语句示例及优化思路。

Answer 1

优化思路

1. 索引：
   • 确保关联表的连接字段上都有索引。如果更新的表 table_to_update 与关联表 related_table 通过 id 字段关联，那么在 table_to_update 的 id 字段和 related_table 的 id 字段上都应该创建索引。这可以加快连接查询的速度，从而提高更新效率。
   • 如果更新涉及到其他条件字段，也应该在这些字段上创建索引，但要注意索引过多会增加写操作的开销，需权衡。
2. 事务：
   • 采用批量提交事务的方式。不要在每次更新一条记录后就提交事务，这样会产生大量的日志写入和锁操作。可以将一定数量（如1000条）的更新操作放在一个事务中，完成这一批更新后再提交事务。这样可以减少事务的次数，提高整体更新效率。同时，批量事务也有助于减少锁争用，因为单个事务的执行时间相对较短，锁的持有时间也相应缩短。
   • 在事务开始前，尽量确保相关数据已经加载到内存中（通过合适的缓存策略或预读操作），以减少事务执行过程中的磁盘I/O操作，进一步提高事务执行效率。

SQL语句示例

假设要更新的表为 table_to_update，关联表为 related_table，它们通过 id 字段关联，并且要将 table_to_update 中的 column_to_update 字段更新为 related_table 中的 new_value 字段值。

-- 创建必要的索引（如果尚未创建）
CREATE INDEX idx_table_to_update_id ON table_to_update(id);
CREATE INDEX idx_related_table_id ON related_table(id);

-- 定义每次批量更新的数量
SET @batch_size = 1000;
-- 定义偏移量初始值
SET @offset = 0;

-- 循环进行批量更新
WHILE TRUE DO
    START TRANSACTION;
        UPDATE table_to_update tu
        INNER JOIN related_table rt ON tu.id = rt.id
        SET tu.column_to_update = rt.new_value
        LIMIT @batch_size OFFSET @offset;
    COMMIT;
    -- 更新偏移量
    SET @offset = @offset + @batch_size;
    -- 如果更新的行数小于批量大小，说明更新完成，退出循环
    IF (SELECT ROW_COUNT() < @batch_size) THEN
        LEAVE;
    END IF;
END WHILE;

上述代码中，首先创建了必要的索引，然后通过 WHILE 循环进行批量更新。每次循环中开启一个事务，执行一次批量更新并提交事务，同时根据更新的行数判断是否完成所有记录的更新。