从MS SQL向MySQL插入大量行的最有效方法是什么？

Question

我每天都需要将大量行从SQL Server数据库传输到MySQL db (MyISAM)。我已经将MySQL实例设置为链接服务器。

我有一个简单的查询，它返回需要传输的行。行数将增长到大约40,000行，每行只有2个字符(11)列。这应该相当于大约0.8MB的大小。SELECT查询的执行时间可以忽略不计。在每天插入记录之前，我将截断MySQL表。

我在MySQL链接服务器中使用INSERT...SELECT查询来执行插入。对于40,000行，大约需要40秒。为了查看将这些行数从一个MySQL表移动到另一个表需要多长时间，我从MSSQL中执行了一个远程查询，耗时接近1秒。

我看不出SSMS中的执行计划发生了什么，但看起来好像正在为每一行执行一条INSERT语句，而不是执行一条语句来插入所有行。

我能做些什么来提高性能？如果是这样，我有没有办法在一条语句中强制将行插入到MySQL中？

Answer 1

问题是您从中选择的表在本地服务器上，而您要插入到的表在远程服务器上。因此，链接服务器将不得不将每一行转换为MySQL服务器上的INSERT INTO Table (Field1, Field2) VALUES ('VALUE1','VALUE2')或类似文件。您可以做的是在SQL服务器中的每一行上保留一个校验和。不需要截断并重新插入整个表，只需删除并重新插入已更改的记录和新记录。除非您的大多数记录每天都在更改，否则这应该会减少您必须大量传输的数据量，而不必纠结于导出和重新导入文本文件。

Answer 2

在MySQL中，LOAD DATA INFILE比INSERT快得多。如果您可以将MS SQL server设置为输出临时的CSV输出文件，则可以使用命令行mysqlimport工具或使用MySQL SQL语句中的LOAD DATA INFILE将其导入MySQL。

Answer 3

我不确定这是否使它更快，但批量下载和上传将是替代方案。

在mySQL端，您可以执行LOAD DATA INFILE

不知道如何在SQL Server端卸载它，但可能有类似的东西。