MySQL 有效地将所有记录从一个表复制到另一个表 [英] MySQL efficiently copy all records from one table to another

问题描述

有没有一种更有效、更省力的方法将所有记录从一个表复制到另一个表:

Is there a more-efficent, less laborious way of copying all records from one table to another that doing this:

INSERT INTO product_backup SELECT * FROM product

通常，product 表将保存大约 50,000 条记录.两个表的结构相同，有 31 列.我想指出这不是我的数据库设计，我继承了旧系统.

Typically, the product table will hold around 50,000 records. Both tables are identical in structure and have 31 columns in them. I'd like to point out this is not my database design, I have inherited a legacy system.

推荐答案

你只缺少一件事.特别是，如果您使用 InnoDB，是否要在 SELECT 语句中显式添加 ORDER BY 子句以确保您以主键(聚集索引)顺序插入行:

There's just one thing you're missing. Especially, if you're using InnoDB, is you want to explicitly add an ORDER BY clause in your SELECT statement to ensure you're inserting rows in primary key (clustered index) order:

INSERT INTO product_backup SELECT * FROM product ORDER BY product_id

如果不需要，请考虑删除备份表上的二级索引.这也将节省服务器上的一些负载.

Consider removing secondary indexes on the backup table if they're not needed. This will also save some load on the server.

最后，如果您使用的是 InnoDB，请减少所需的行锁数量并显式锁定两个表:

Finally, if you are using InnoDB, reduce the number of row locks that are required and just explicitly lock both tables:

LOCK TABLES product_backup WRITE;
LOCK TABLES product READ;
INSERT INTO product_backup SELECT * FROM product ORDER BY product_id;
UNLOCK TABLES;

锁定的东西可能不会有很大的不同，因为行锁定非常快(虽然不如表锁快)，但正如你所问的那样.

The locking stuff probably won't make a huge difference, as row locking is very fast (though not as fast as table locks), but since you asked.

这篇关于MySQL 有效地将所有记录从一个表复制到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！