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

q0qdq0h2 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(1009)

我有一个由1亿多行组成的表，想将数据复制到另一个表中。我有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 ;

查询执行时没有锁定表，但是在复制了几百万行之后，它变得太慢了。请提出更好的方法来完成这项任务。谢谢你！

mysql Insert nonblocking

来源：https://stackoverflow.com/questions/52099389/mysql-insert-into-select-query-is-too-slow-to-copy-100-million-rows

4条答案

按热度按时间

fnvucqvd1#

任何 INSERT ... SELECT ... 查询在select中从源表读取的行上获取共享锁。但是通过处理较小的行块，锁不会持续太长时间。
使用 LIMIT ... OFFSET 当您在源表中前进时，速度会越来越慢。对于每个块10000行，您需要运行10000次该查询，每个查询都必须重新开始并扫描整个表以达到新的偏移量。
不管您做什么，复制1亿行都需要一段时间。它做了很多工作。
我会使用pt-archiver，一个为这个目的而设计的免费工具。它处理“块”（或子集）中的行。它将动态调整块的大小，使每个块需要0.5秒。
您的方法和pt-archiver之间最大的区别是pt-archiver不使用 LIMIT ... OFFSET ，它沿着主键索引遍历，按值而不是按位置选择行块。因此，每一个块都可以更有效地读取。
请回复您的意见：
我预计，减小批处理大小（以及增加迭代次数）将使性能问题变得更糟，而不是更好。
原因是当你使用 LIMIT 与 OFFSET ，每个查询必须从表的开始处重新开始，并将行计数到 OFFSET 价值观。当您遍历表时，这会变得越来越长。
使用运行20000个昂贵的查询 OFFSET 将比运行10000个类似查询花费更长的时间。最昂贵的部分不是读取5000或10000行，也不是将它们插入到目标表中。昂贵的部分将跳过约50000000行，一次又一次。
相反，应该按值而不是偏移量在表上迭代。

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的工作方式。

赞(0）回复(0）举报 2021-06-21

5w9g7ksd2#

为了我的设置和需要-我必须复制3亿到5亿行。很快。在一个不规范的服务器上。
转储到“csv”；将结果拆分为多个文件（我的案例中，最好是20万行）导入拆分文件

SELECT a.b.c INTO OUTFILE '/path/dumpfile.csv'   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'   LINES TERMINATED BY '\n';

split -a 6 -l 200000 dumpfile.csv FileNamePrefix.  // . allows numbering as ext

for f in ./*;
do 
mysql -uUser -pPassword dbname -e "set autocommit = 0; set unique_checks = 0; set foreign_key_checks = 0; set sql_log_bin=0; LOAD DATA CONCURRENT INFILE '/path/to/split/files/"$f"' IGNORE  INTO TABLE InputTableName FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '\"'  (a, b, c);commit;"; 
echo "Done: '"$f"' at $(date)"; 
done

赞(0）回复(0）举报 2021-06-21

91zkwejq3#

谢谢@bill karvin我按照你的建议删除了补偿。下面的查询非常有效，

DROP PROCEDURE IF EXISTS insert_identifierdataset;
DELIMITER $$
CREATE PROCEDURE insert_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE limitSize INT DEFAULT 2000;
  DECLARE maxId INT DEFAULT 0;

  SET maxId = (SELECT MAX(id) FROM Table1);

  WHILE i <= maxId DO
    START TRANSACTION;
        INSERT IGNORE INTO Table2(id, field1, field2)
            SELECT id, field3, field4
                FROM Table1
                WHERE id> i
                ORDER BY id ASC
                LIMIT limitSize;
    COMMIT;
    SET i = i + limitSize;
  END WHILE;
END$$

赞(0）回复(0）举报 2021-06-20

7hiiyaii4#

块是最有效的词。希望您使用的是innodb（记录级别的块），而不是myisam（表级别的块）。由于不知道数据或硬件的复杂性，每个循环的10k记录可能太大了。

赞(0）回复(0）举报 2021-06-20