La forma más eficiente de insertar filas en la base de datos MySQL

He leído muchas preguntas sobre eso pero no pude encontrar uno que sea lo suficientemente rápido. Creo que hay mejores formas de insertar muchas filas en una base de datos MySQL

Utilizo el siguiente código para insertar 100k en mi base de datos MySQL:

public static void CSVToMySQL() { string ConnectionString = "server=192.168.1xxx"; string Command = "INSERT INTO User (FirstName, LastName ) VALUES (@FirstName, @LastName);"; using (MySqlConnection mConnection = new MySqlConnection(ConnectionString)) { mConnection.Open(); for(int i =0;i< 100000;i++) //inserting 100k items using (MySqlCommand myCmd = new MySqlCommand(Command, mConnection)) { myCmd.CommandType = CommandType.Text; myCmd.Parameters.AddWithValue("@FirstName", "test"); myCmd.Parameters.AddWithValue("@LastName", "test"); myCmd.ExecuteNonQuery(); } } } 

Esto toma 100k filas alrededor de 40 segundos. ¿Cómo puedo hacer esto más rápido o un poco más eficiente?

Sería más rápido insertar múltiples filas a través de un DataTable / DataAdapter o de una sola vez:

 INSERT INTO User (Fn, Ln) VALUES (@Fn1, @Ln1), (@Fn2, @Ln2)... 

Debido a problemas de seguridad, no puedo cargar los datos en un archivo y MySQLBulkLoad.

Aquí está mi código de “múltiples inserciones”.

¡La inserción de 100k filas llevó en lugar de 40 segundos solo 3 segundos !

 public static void BulkToMySQL() { string ConnectionString = "server=192.168.1xxx"; StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES "); using (MySqlConnection mConnection = new MySqlConnection(ConnectionString)) { List Rows = new List(); for (int i = 0; i < 100000; i++) { Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test"))); } sCommand.Append(string.Join(",", Rows)); sCommand.Append(";"); mConnection.Open(); using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection)) { myCmd.CommandType = CommandType.Text; myCmd.ExecuteNonQuery(); } } } 

La statement SQL creada se ve así:

 INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ; 

Actualización : Gracias Salman A He agregado MySQLHelper.EscapeString para evitar la inyección de código que se usa internamente cuando se usan parámetros.

Hice una pequeña prueba usando tres cosas: MySqlDataAdapter, transacciones y UpdateBatchSize. Es aproximadamente 30 veces más rápido que su primer ejemplo. Mysql se está ejecutando en un cuadro separado por lo que hay latencia involucrada. El tamaño del lote puede necesitar un poco de ajuste. El código sigue:

 string ConnectionString = "server=xxx;Uid=xxx;Pwd=xxx;Database=xxx"; string Command = "INSERT INTO User2 (FirstName, LastName ) VALUES (@FirstName, @LastName);"; using (var mConnection = new MySqlConnection(ConnectionString)) { mConnection.Open(); MySqlTransaction transaction = mConnection.BeginTransaction(); //Obtain a dataset, obviously a "select *" is not the best way... var mySqlDataAdapterSelect = new MySqlDataAdapter("select * from User2", mConnection); var ds = new DataSet(); mySqlDataAdapterSelect.Fill(ds, "User2"); var mySqlDataAdapter = new MySqlDataAdapter(); mySqlDataAdapter.InsertCommand = new MySqlCommand(Command, mConnection); mySqlDataAdapter.InsertCommand.Parameters.Add("@FirstName", MySqlDbType.VarChar, 32, "FirstName"); mySqlDataAdapter.InsertCommand.Parameters.Add("@LastName", MySqlDbType.VarChar, 32, "LastName"); mySqlDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; var stopwatch = new Stopwatch(); stopwatch.Start(); for (int i = 0; i < 50000; i++) { DataRow row = ds.Tables["User2"].NewRow(); row["FirstName"] = "1234"; row["LastName"] = "1234"; ds.Tables["User2"].Rows.Add(row); } mySqlDataAdapter.UpdateBatchSize = 100; mySqlDataAdapter.Update(ds, "User2"); transaction.Commit(); stopwatch.Stop(); Debug.WriteLine(" inserts took " + stopwatch.ElapsedMilliseconds + "ms"); } } 

Ejecute el comando en una Transaction y reutilice la misma instancia de comando para cada iteración. Para una mayor optimización del rendimiento, envíe 100 consultas en un solo comando. La ejecución paralela podría ofrecer un mejor rendimiento ( Parallel.For ), pero asegúrese de que cada bucle paralelo obtenga su propia instancia de MySqlCommand .

 public static void CSVToMySQL() { string ConnectionString = "server=192.168.1xxx"; string Command = "INSERT INTO User (FirstName, LastName ) VALUES (@FirstName, @LastName);"; using (MySqlConnection mConnection = new MySqlConnection(ConnectionString)) { mConnection.Open(); using (MySqlTransaction trans = mConnection.BeginTransaction()) { using (MySqlCommand myCmd = new MySqlCommand(Command, mConnection, trans)) { myCmd.CommandType = CommandType.Text; for (int i = 0; i < = 99999; i++) { //inserting 100k items myCmd.Parameters.Clear(); myCmd.Parameters.AddWithValue("@FirstName", "test"); myCmd.Parameters.AddWithValue("@LastName", "test"); myCmd.ExecuteNonQuery(); } trans.Commit(); } } } } 

Si Add of AddWithValue no escapa de las cadenas, debe hacerlo con anticipación para evitar la inyección de SQL y los errores de syntax.

Cree INSERT con solo 1000 filas a la vez. Eso debería ejecutarse fácilmente 10 veces más rápido que con lo que comenzó (1 fila por INSERT ). Hacer todos los 100K a la vez es arriesgado y posiblemente más lento. Arriesgado porque puede explotar un límite (tamaño del paquete, etc.); más lento debido a la necesidad de un enorme registro ROLLBACK . COMMIT después de cada lote, o usar autocommit=1 .

De esta manera, puede no ser más rápido que el enfoque del generador de cadenas, pero está parametrizado:

 ///  /// Bulk insert some data, uses parameters ///  /// The Table Name /// Holds list of data to insert /// executes the insert after batch lines /// Progress reporting public void BulkInsert(string table, MySQLBulkInsertData inserts, int batchSize = 100, IProgress progress = null) { if (inserts.Count < = 0) throw new ArgumentException("Nothing to Insert"); string insertcmd = string.Format("INSERT INTO `{0}` ({1}) VALUES ", table, inserts.Fields.Select(p => p.FieldName).ToCSV()); StringBuilder sb = new StringBuilder(); using (MySqlConnection conn = new MySqlConnection(ConnectionString)) using (MySqlCommand sqlExecCommand = conn.CreateCommand()) { conn.Open(); sb.AppendLine(insertcmd); for (int i = 0; i < inserts.Count; i++) { sb.AppendLine(ToParameterCSV(inserts.Fields, i)); for (int j = 0; j < inserts[i].Count(); j++) { sqlExecCommand.Parameters.AddWithValue(string.Format("{0}{1}",inserts.Fields[j].FieldName,i), inserts[i][j]); } //commit if we are on the batch sizeor the last item if (i > 0 && (i%batchSize == 0 || i == inserts.Count - 1)) { sb.Append(";"); sqlExecCommand.CommandText = sb.ToString(); sqlExecCommand.ExecuteNonQuery(); //reset the stringBuilder sb.Clear(); sb.AppendLine(insertcmd); if (progress != null) { progress.Report((double)i/inserts.Count); } } else { sb.Append(","); } } } } 

Esto usa las clases de ayuda de la siguiente manera:

 ///  /// Helper class to builk insert data into a table ///  public struct MySQLFieldDefinition { public MySQLFieldDefinition(string field, MySqlDbType type) : this() { FieldName = field; ParameterType = type; } public string FieldName { get; private set; } public MySqlDbType ParameterType { get; private set; } } /// ///You need to ensure the fieldnames are in the same order as the object[] array /// public class MySQLBulkInsertData : List { public MySQLBulkInsertData(params MySQLFieldDefinition[] fieldnames) { Fields = fieldnames; } public MySQLFieldDefinition[] Fields { get; private set; } } 

Y este método de ayuda:

  ///  /// Return a CSV string of the values in the list ///  ///  ///  private string ToParameterCSV(IEnumerable p, int row) { string csv = p.Aggregate(string.Empty, (current, i) => string.IsNullOrEmpty(current) ? string.Format("@{0}{1}",i.FieldName, row) : string.Format("{0},@{2}{1}", current, row, i.FieldName)); return string.Format("({0})", csv); } 

Quizás no sea súper elegante pero funciona bien. Requiero seguimiento de progreso para que esté incluido, no dude en eliminar esa parte.

Esto producirá comandos SQL similares a su salida deseada.

EDITAR: ToCSV:

  ///  /// Return a CSV string of the values in the list ///  ///  ///  ///  ///  ///  public static string ToCSV(this IEnumerable intValues, string separator = ",", string encloser = "") { string result = String.Empty; foreach (T value in intValues) { result = String.IsNullOrEmpty(result) ? string.Format("{1}{0}{1}", value, encloser) : String.Format("{0}{1}{3}{2}{3}", result, separator, value, encloser); } return result; } 

Como dice Stefan Steiger, Bulk Insert es adecuado para sus situaciones.

Otro truco es usar tablas de etapas, por lo que en lugar de escribir directamente en la tabla de producción, escribirás en la etapa uno (que tiene la misma estructura). Después de haber escrito toda la información, simplemente intercambia tablas. Con el enfoque de etapas, evitará bloquear tablas para la inserción (también se puede usar para actualizar y eliminar), y este patrón se usa mucho con MySQL en algunos proyectos.

Además, deshabilitar las teclas de tabla puede acelerar la inserción, pero también puede presentar algunos problemas cuando las habilita (solo para el motor MyISAM).

Agregado :

Supongamos que tiene Products mesa:

  • ID del Producto
  • Nombre del producto
  • Precio del producto

Para fines de etapas, crea una tabla de etapas llamada ProductsStaging , con el mismo conjunto de columnas.

Todas las operaciones que realizas en la tabla de etapas:

 UpdateStagingTable(); SwapTables(); UpdateStagingTable(); 

porque después de cambiar su tabla de etapas no tiene los datos nuevos, vuelve a invocar el mismo método. En el método SwapTables() , ejecuta una instrucción SQL:

 RENAME TABLE Products TO ProductsTemp, ProductsStaging TO Products, ProductsTemp TO ProductsStagin; 

La velocidad de las manipulaciones de datos depende del motor MySql (por ejemplo, InnoDB, MyISAM, etc.), por lo que también puede acelerar las inserciones cambiando el motor.

Una forma de acelerar sería envolviendo todas las inserciones en UNA transacción (código de SQL-Server):

 using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString"))) { conn.Open(); SqlTransaction transaction = conn.BeginTransaction(); try { foreach (string commandString in dbOperations) { SqlCommand cmd = new SqlCommand(commandString, conn, transaction); cmd.ExecuteNonQuery(); } transaction.Commit(); } // Here the execution is committed to the DB catch (Exception) { transaction.Rollback(); throw; } conn.Close(); } 

Otra forma es cargar el archivo CSV en una tabla de datos y usar la función de procesamiento por lotes de DataAdapter

  DataTable dtInsertRows = GetDataTable(); SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("sp_BatchInsert", connection); command.CommandType = CommandType.StoredProcedure; command.UpdatedRowSource = UpdateRowSource.None; // Set the Parameter with appropriate Source Column Name command.Parameters.Add("@PersonId", SqlDbType.Int, 4, dtInsertRows.Columns[0].ColumnName); command.Parameters.Add("@PersonName", SqlDbType.VarChar, 100, dtInsertRows.Columns[1].ColumnName); SqlDataAdapter adpt = new SqlDataAdapter(); adpt.InsertCommand = command; // Specify the number of records to be Inserted/Updated in one go. Default is 1. adpt.UpdateBatchSize = 2; connection.Open(); int recordsInserted = adpt.Update(dtInsertRows); connection.Close(); 

Encuentra un buen ejemplo aquí .

O puede usar la clase MySQL BulkLoader C #:

 var bl = new MySqlBulkLoader(connection); bl.TableName = "mytable"; bl.FieldTerminator = ","; bl.LineTerminator = "\r\n"; bl.FileName = "myfileformytable.csv"; bl.NumberOfLinesToSkip = 1; var inserted = bl.Load(); Debug.Print(inserted + " rows inserted."); 

Si realiza varias inserciones en un comando, aún puede exprimir una pulgada o dos utilizando StringBuilder en lugar de cadena.

Mi sugerencia es una idea, no ejemplo o solución. ¿Qué sucede si no utiliza INSERT pero transfiere datos como múltiples parámetros (no necesariamente todos los 100K a la vez, puede usar paquetes de 1K, por ejemplo) para PROCEDIMIENTO ALMACENADO que a su vez realiza INSERTOS.

Me encontré con un problema similar mientras trabajaba con EF – MySQL. Los insertos de EF eran demasiado lentos y, por lo tanto, utilizaron el enfoque mencionado por fubo . Para empezar, el rendimiento mejoró drásticamente (~ 20K registros se insertaron en ~ 10 segundos) pero se redujo a medida que la tabla aumentaba de tamaño, con ~ 1M registros en la tabla, la inserción tomó ~ 250 segundos.

¡Finalmente resolvió el problema! El PK de la tabla era de tipo GUID (UUID – char (36)). Como los UUID no pueden indexarse ​​secuencialmente y cada inserción requirió que los índices se reconstruyan, se ralentizó.

La solución fue reemplazar el PK con bigint (o int) y establecerlo como una columna de identidad. Esto mejoró el rendimiento, las inserciones tomaron un promedio de ~ 12 segundos con ~ 2M + registros en la tabla.

¡Pensé en compartir este hallazgo aquí por si alguien se queda atrapado en un problema similar!