MySQL .Net/连接器6.5.4/6.6.4的批处理更新速度非常慢

Question

我使用MySQL5.6.9-rc和.net连接器6.5.4将数据插入到一个有两个字段(整数ID，整数数据，ID是主键)的表中。将2000行插入到表中非常慢(大约35秒)(对于UpdateBatchSize =1和UpdateBatchSize =500没有太大区别)，我也尝试了连接器6.6.4，问题仍然存在。

然而，在MySQL5.4.3和连接器6.20中速度很快，如果将UpdateBatchSize设置为500，则只需要一秒钟就可以将2000行插入到表中(如果UpdateBatchSize =1，速度也会很慢)。然后我用MySQL5.4.3和连接器6.5.4或6.6.4测试它，它很慢！

我像下面这样写了插入数据的代码，用mysql6.6.9和连接器6.54、Windows XP和VS2008运行它。

    public void Test()
    {
        MySqlConnection conn = new MySqlConnection("Database=myDatabase;Server=localhost;User Id=root;Password=myPassword");

        string sql = "Select * from myTable";
        MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);

        adapter.UpdateBatchSize = 500; 

        MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);

        DataTable table = new DataTable();
        adapter.Fill(table); //it is an empty table
        Add2000RowsToTable(table);

        int count = adapter.Update(table); //It took 35 seconds to complete.
        adapter.Dispose();
        conn.Close();
    }


    private void Add2000RowsToTable(DataTable table)
    {
        DataRow row;
        for (int i = 0; i < 2000; i++)
        {
            row = table.NewRow();
            row[0] = i;
            row[1] = i;

            table.Rows.Add(row);
        }
    }

在我看来，MySqlDataAdapter.UpdateBatchSize对于连接器6.5.4和6.64不起作用，是不是我的代码出了什么问题？

提前感谢

Answer 1

虽然这需要一些初始编码(并且不能直接解决您的问题)，但我强烈建议对超过100条记录使用LOAD DATA INFILE。

实际上，在我自己的系统中，我只编写了一次代码，并且在我的所有插入和更新中都重用了它，无论是否批量。

LOAD DATA INFILE的可伸缩性要好得多:我已经用它插入了一亿行，而没有明显的性能下降。

Answer 2

做了更多的测试...

检查mysql服务器中的日志，对于连接器6.20，它会生成sql语句，用于批量更新，如下所示：

插入到mytable (id，data)值(0,0)，(1,1)，(2,2) ...

但是对于连接器6.54和6.64，语句是不同的：

插入mytable (id，data)值(0,0)；插入mytable (id，data)值(1，1)；插入mytable (id，data)值(2,2)；...

我认为这就是为什么连接器6.5.4/6.6.4的批处理更新如此缓慢的原因，这是6.5.4/6.6.4的bug吗？或者服务器(尝试过mysql 5.5.29/5.6.9)应该更智能地处理语句？

Answer 3

我使用的解决方案是将批量行数据作为CSV写入文件中，然后使用以下命令导入：

LOAD DATA LOCAL INFILE  'C:/path/to/file.csv'
INTO TABLE <tablename>
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(<field1>,<field2>);

30000行只用了大约4秒。它类似于上面的建议，但允许您使用系统本地的文件，而不是服务器。