mysql - 从 MySQL 中的临时表替换表中数据的最快方法

Question

我需要“更新”从外部源接收的一些表数据(每次我收到“所有”数据，更新某些记录的某些字段)。没有唯一的字段或字段组合，因此我认为最好的方法是每次都清除数据库中的所有数据并再次写入所有(现已更新)数据。最多有 1000 条记录(永远不会超过这个数)，每条记录大约有 15 个短字段:文本、数字、日期时间。我正在将其写入远程数据库(因此，速度很慢)。

目前我正在做:

delete from `table` where `date_dt` > ?

然后对于每一行

INSERT INTO `table` ( `field_0`,`field_1`,... ) VALUES (?,?,...)

这不仅慢，而且当我仍在插入时，最终用户可能看不到完整的数据。

我想我可以这样做:

CREATE TEMPORARY TABLE `temp_table` ( ... ); -- same structure as in main table
INSERT INTO `temp_table` ( `field_0`,`field_1`,... ) VALUES (?,?,...)   -- repeat 1000x

START TRANSACTION;
DELETE FROM `table`;
INSERT INTO `table` SELECT * FROM `temp_table`;
DROP `temp_table`;
COMMIT;

这有什么意义吗？解决这个问题的更好方法是什么？

用数据填充临时表的速度并不重要，但用数据填充主表的速度才是关键(这样用户就不会看到不完整的数据，或者他们看到的时间很短)。

Answer 1

mysqlimport --delete 将首先截断表，然后从 CSV 文件加载外部数据。它的运行速度比一次 INSERT 快很多倍。

参见https://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html

我在 2017 年 4 月做了一个关于 MySQL 批量数据加载性能的演示: https://www.slideshare.net/billkarwin/load-data-fast

P.S.:如果您有 MySQL 复制环境，请不要使用临时表解决方案。这是一种众所周知的破坏复制的方法。如果从属设备在创建临时表和从临时表读取 INSERT...SELECT 之间重新启动，则从属设备将发现临时表已消失，这将导致错误并停止复制。这看起来不太可能，但最终确实会发生。