使用MySqlConnector C#批量插入大量数据的优化方案咨询

针对你20万条数据插入耗时过长的问题，完全可以通过单次ExecuteNonQuery()插入多组Values，这是最适合你场景的优化方式，同时结合事务、预编译等手段，能大幅提升插入效率。以下是具体实现和优化细节：

一、核心方案：批量参数化插入 #

MySQL支持INSERT INTO 表名(列1,列2...) VALUES (...), (...), (...)的批量插入语法，配合参数化查询，既可以避免SQL注入，又能减少网络请求次数（从20万次降到几百次），同时自动处理特殊字符、多语言内容，完美适配你的数据场景。

实现思路 #

1. 按批次拆分数据（比如每1000条为一批，可根据实际情况调整，避免单条SQL过长触发max_allowed_packet限制）
2. 为每批次的所有数据生成对应的参数占位符
3. 一次性添加所有参数，执行批量插入
4. 每批次执行时包裹事务，减少事务提交开销

二、优化后的完整代码 #

// 每批次插入的数量，可根据服务器性能调整，建议500-2000之间
int batchSize = 1000;
string baseSql = "INSERT INTO `Mail` (`threadID`, `mailID`, `UserID`, `UserName`, `mailTime`, `body`) VALUES ";

using (MySqlConnection mySqlConnection2 = new MySqlConnection(sqlconnect))
{
    mySqlConnection2.Open();
    // 禁用自动提交，手动控制事务
    mySqlConnection2.AutoCommit = false;
    MySqlTransaction transaction = mySqlConnection2.BeginTransaction();

    try
    {
        for (int i = 0; i < List.Count; i += batchSize)
        {
            // 截取当前批次的数据
            var batchData = List.Skip(i).Take(batchSize).ToList();
            if (batchData.Count == 0) break;

            // 生成当前批次的参数占位符，比如(@p0_0,@p0_1,...),(@p1_0,@p1_1,...)
            List<string> valueGroups = new List<string>();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            for (int j = 0; j < batchData.Count; j++)
            {
                var data = batchData[j];
                // 为每条数据生成参数名，避免重复
                string paramPrefix = $"@p{j}_";
                valueGroups.Add($"({paramPrefix}threadID, {paramPrefix}mailID, {paramPrefix}UserID, {paramPrefix}UserName, {paramPrefix}mailTime, {paramPrefix}body)");

                // 添加参数
                parameters.Add(new MySqlParameter($"{paramPrefix}threadID", MySqlDbType.Int32) { Value = data.threadID });
                parameters.Add(new MySqlParameter($"{paramPrefix}mailID", MySqlDbType.Int32) { Value = data.mailID });
                parameters.Add(new MySqlParameter($"{paramPrefix}UserID", MySqlDbType.Int32) { Value = data.UserID });
                parameters.Add(new MySqlParameter($"{paramPrefix}UserName", MySqlDbType.VarChar) { Value = data.UserName });
                parameters.Add(new MySqlParameter($"{paramPrefix}mailTime", MySqlDbType.DateTime) { Value = data.mailTime });
                parameters.Add(new MySqlParameter($"{paramPrefix}body", MySqlDbType.Text) { Value = data.body });
            }

            // 拼接完整SQL
            string sql = baseSql + string.Join(", ", valueGroups);
            using (MySqlCommand cmd = mySqlConnection2.CreateCommand())
            {
                cmd.Transaction = transaction;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameters.ToArray());
                // 预编译SQL，提升重复执行效率
                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }
        }

        // 提交所有批次的事务
        transaction.Commit();
    }
    catch (Exception ex)
    {
        // 出错回滚事务
        transaction.Rollback();
        throw ex;
    }
    finally
    {
        mySqlConnection2.AutoCommit = true;
    }
}

三、额外优化建议 #

• 调整批次大小：如果服务器性能较好，可以适当增大batchSize（比如2000）；如果出现SQL过长报错，就减小该值，同时可以检查MySQL的max_allowed_packet参数，适当调大（默认是4M，可设置为16M或更大）。
• 连接字符串优化：确保连接字符串包含AllowBatch=true（MySQL Connector/NET默认开启），可以提升批量操作效率；添加UseAffectedRows=false避免额外的行计数开销。
• 关闭索引（可选）：如果插入完成后再重建索引更快，可以先临时关闭表的非主键索引，插入完成后再开启，但注意这会影响插入期间的查询性能。

内容的提问来源于stack exchange，提问作者Gaben