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屋！