MySql insert into select 查询太慢，无法复制 1 亿行

我有一个由 100 多百万行组成的表，并且想要将数据复制到另一个表中。我有1个要求， 1. 查询执行不能阻塞对这些数据库表的其他操作， 我写了一个存储过程如下

我计算源表中的行数，然后进行循环，但在每次迭代中复制 10000 行，启动事务并提交它。然后按偏移量读取下一个 10000。

CREATE PROCEDURE insert_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE iterations INT DEFAULT 0;
  DECLARE rowOffset INT DEFAULT 0;
  DECLARE limitSize INT DEFAULT 10000;
  SET iterations = (SELECT COUNT(*) FROM Table1) / 10000;

  WHILE i <= iterations DO
    START TRANSACTION;
        INSERT IGNORE INTO Table2(id, field2, field3)
            SELECT f1, f2, f3
            FROM Table1
            ORDER BY id ASC
            LIMIT limitSize offset rowOffset;
    COMMIT;
    SET i = i + 1;
    SET rowOffset = rowOffset + limitSize;
  END WHILE;
END$$
DELIMITER ;

查询在不锁定表的情况下执行，但在复制几百万行后，它变得太慢了。 请建议任何更好的方法来完成这项任务。 感谢您！

Any INSERT ... SELECT ...查询确实获取共享锁 https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html在 SELECT 中从源表读取的行上。但通过处理较小的行块，锁不会持续太久。

查询与LIMIT ... OFFSET当您在源表中前进时，速度会越来越慢。如果每个块有 10,000 行，则需要运行该查询 10,000 次，每次都必须重新开始并扫描表以到达新的 OFFSET。

无论您做什么，复制 1 亿行都需要一段时间。它正在做很多工作。

我会用pt-归档器 https://www.percona.com/doc/percona-toolkit/LATEST/pt-archiver.html，一个为此目的设计的免费工具。它处理“块”（或子集）中的行。它将动态调整块的大小，以便每个块需要 0.5 秒。

您的方法和 pt-archiver 之间最大的区别是 pt-archiver 不使用LIMIT ... OFFSET，它沿着主键索引行走，按值而不是按位置选择行块。因此每个块的读取效率都会更高。

回复您的评论：

我预计，减小批量大小并增加迭代次数将会导致性能问题worse，不是更好。

原因是当你使用LIMIT with OFFSET，每个查询都必须从表的开头重新开始，并将行数计算到OFFSET价值。当您迭代表时，它会变得越来越长。

使用以下命令运行 20,000 个昂贵的查询OFFSET比运行 10,000 个类似查询需要更长的时间。最昂贵的部分不是读取 5,000 或 10,000 行，也不是将它们插入到目标表中。昂贵的部分将一遍又一遍地跳过约 50,000,000 行。

相反，您应该通过以下方式迭代表values不是通过偏移量。

INSERT IGNORE INTO Table2(id, field2, field3)
        SELECT f1, f2, f3
        FROM Table1
        WHERE id BETWEEN rowOffset AND rowOffset+limitSize;

循环之前，查询MIN(id)和MAX(id)，然后开始rowOffset为最小值，然后循环至最大值。

这就是 pt-archiver 的工作方式。