如何在MYSQL 6.2中不锁定表的情况下从另一个表复制数据？

Question

我有一张MySQL服务器中保存所有历史数据的表格，它非常庞大（约有7亿行）。我正在创建一个具有相同列但具有分区的新表格，然后需要将旧表格中的所有数据复制到新的分区表格中。我已经得到了正确的脚本来完成这个任务，但我认为这可能会锁定表格。由于这是在生产服务器上进行操作，我不希望发生这种情况。我该怎么做才能避免锁定表格？

- Jack

---

@Wistar，我正在使用MySQL分支6.2。 - Jack

运行 SHOW TABLE STATUS WHERE Name = 'name-of-your-table'。引擎列中是什么？InnoDB？MyISAM？ - Wistar

@Wistar InnoDB 是数据库引擎。 - Jack

6.2不存在。也许你引用的是Workbench版本？ - Rick James

你要添加什么“分区”？很多版本都没有提供任何好处。 - Rick James

Answer 1

假设表格具有完全相同的列，您可以这样做：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
INSERT INTO NEW_TABLE (SELECT * FROM OLD_TABLE);
COMMIT ;

根据Wistar的评论，我添加了一些额外的解释。可用的阅读级别包括：

• READ COMMITTED: 一种类似于Oracle隔离级别的隔离级别，对于一致（非锁定）读取：每个一致的读取，即使在同一个事务中，也会设置并读取自己的新快照。
• READ UNCOMMITTED: SELECT语句以非锁定方式执行，但可能使用早期版本的行。因此，使用此隔离级别，这些读取是不一致的。这也称为脏读。否则，此隔离级别的工作方式与READ COMMITTED相同。
• REPEATABLE READ: 这是InnoDB的默认隔离级别。对于一致的读取，与READ COMMITTED隔离级别有一个重要的区别：同一事务中的所有一致读取都读取第一次读取所建立的快照。这个约定意味着如果您在同一个事务中发出多个普通（非锁定）SELECT语句，则这些SELECT语句在彼此之间也是一致的。
• SERIALIZABLE: 此级别类似于REPEATABLE READ，但如果禁用了自动提交，则InnoDB会将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE。如果启用了自动提交，则SELECT是它自己的事务。因此，它被认为是只读的，并且如果作为一致（非锁定）读取执行，则可以进行序列化而不需要阻止其他事务。（如果要强制一个普通SELECT在其他事务修改所选行时阻塞，请禁用自动提交。）

我希望这可以帮到你。

- Ayman

---

这是一次脏读，可能会使用行的早期版本。 - Wistar

如果在已经复制的行上发生插入/删除/更新操作会发生什么？隔离模式可能在某些情况下有所帮助，但并非所有情况都适用。 - Rick James

Answer 2

我不知道你的脚本是什么，但我建议你使用分块插入。

看这个例子

如果你使用 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED，你可能会插入错误版本的行。如果你用一个select插入所有行，那么不仅会有锁，而且性能也不是最好的。

你可以像这样做：

 INSERT INTO NEW TABLE (SELECT * FROM OLD_TABLE LIMIT 1000 OFFSET 0 )
 INSERT INTO NEW TABLE (SELECT * FROM OLD_TABLE LIMIT 1000 OFFSET 1000 )
 INSERT INTO NEW TABLE (SELECT * FROM OLD_TABLE LIMIT 1000 OFFSET 2000 )
 ...

或者使用准备语句和while

CREATE PROCEDURE myproc()
BEGIN
    @rows :=0
    SELECT COUNT(*) FROM OLD_TABLE into @rows
    DECLARE i int DEFAULT 0;
    WHILE i <= @rows DO
        PREPARE stmt1 FROM 'INSERT INTO NEW TABLE (SELECT * FROM OLD_TABLE LIMIT 1000 OFFSET ? )'
        EXECUTE stmt1 USING @i;
        DEALLOCATE PREPARE stmt1;
        SET i = i + 1000;
    END WHILE;
END

当然，您可以通过更改LIMIT大小来根据您的配置调整块大小。

- Wistar

---

非常感谢，我正在使用批量插入，并在每个批次插入之后提交它，因此我将继续使用读取未提交。 - Jack

不！“OFFSET”会降低性能！因为要跳过N行数据，将一个O（N）操作（复制N行）转化为一个O（N * N）操作。 - Rick James

LIMIT和OFFSET 必须 与ORDER BY一起使用，排序不能保证。如果ORDER BY是按索引列排序，则性能损失非常小。 - xmedeko

Answer 3

分块复制。你有一个AUTO_INCREMENTPRIMARY KEY吗？如果是这样，那么执行以下操作：

 WHERE id >= $x AND id < $x + 1000

如果有很多间隔或其他问题，请参见其他有效分块技术。

避免使用OFFSET进行分页的弊端。

更好的方法是使用Percona的pt-online-schema-alter。它可以完成我所描述的大部分工作，而且在复制过程中允许您向表中写入数据。(它使用TRIGGERs来实现。)

- Rick James

Answer 4

为了减少使用OFFSET的劣势，this article描述了一种可能的方法，即在有数值主键id的情况下使用JOIN来强制使用正确的索引。请注意，为了跟踪进程，在处理批量后，会创建一个"procedure_log"表，并逐步更新该表：

对于MySQL：

DROP PROCEDURE IF EXISTS copyTable;

DELIMITER |
CREATE PROCEDURE copyTable()
BEGIN

    DECLARE batchSize INT DEFAULT 100;
    DECLARE i INT DEFAULT 0;
    DECLARE rowCount INT;

    # Note that we use a WHERE clause to prevent a full table scan / use the index properly
    SET rowCount = (SELECT COUNT(id) FROM my_table WHERE id IS NOT NULL);

    CREATE TABLE IF NOT EXISTS my_table_copy LIKE my_table;
    CREATE TABLE IF NOT EXISTS procedure_log ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, entry TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );

    WHILE i <= rowCount DO
        INSERT IGNORE INTO my_table_copy (
          SELECT source.* FROM (
            SELECT id FROM my_table ORDER BY id LIMIT i, batchSize
          ) tmp
          JOIN my_table source ON source.id = tmp.id
          ORDER BY source.id
        );
        SET i = i + batchSize;

        INSERT INTO procedure_log (entry) VALUES (CONCAT('Copied batch from my_table => my_table_copy, batch: ', batchSize, ', offset: ', i, ', rowCount: ', rowCount));
    END WHILE;
END |
DELIMITER ;

- Holtkamp