행을 MySQL 데이터베이스에 삽입하는 가장 효율적인 방법

행을 MySQL 데이터베이스에 삽입하는 가장 효율적인 방법

나는 그것에 대해 많은 질문을 읽었지만 충분히 빠른 질문을 찾을 수 없었다.MySQL 데이터베이스에 행을 많이 삽입할 수 있는 더 좋은 방법이 있을 것 같습니다.

다음 코드를 사용하여 MySQL-Database에 100k를 삽입합니다.

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();
        }
    }
}

이 작업은 10만 행에서 약 40초 걸립니다.어떻게 하면 더 빠르고 효율적으로 만들 수 있을까요?

DataTable/DataAdapter를 통해 또는 한 번에 여러 행을 삽입하는 것이 더 빠를 수 있습니다.

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

보안상의 문제로 데이터를 파일에 로드할 수 없고 MySQLBulkLoad를 실행할 수 없습니다.

여기 "복수 삽입" 코드가 있습니다.

10만 행 삽입은 40초에서 3초밖에 걸리지 않았습니다!!

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<string> Rows = new List<string>();
        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();
        }
    }
}

작성된 SQL 문은 다음과 같습니다.

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

업데이트: Salman A가 추가했습니다.MySQLHelper.EscapeString매개 변수를 사용할 때 내부적으로 사용되는 코드 주입을 방지하기 위해 사용합니다.

MySqlDataAdapter, transactions, UpdateBatchSize 세 가지를 사용하여 작은 테스트를 했습니다.첫 번째 예보다 약 30배 빠릅니다.Mysql은 별도의 상자에서 실행되므로 지연이 발생합니다.배치 크기를 조정해야 할 수 있습니다.코드는 다음과 같습니다.

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");
    }
}

를 실행하다Transaction각 반복에 대해 동일한 명령어인스턴스를 재사용합니다.성능 최적화를 위해 100개의 쿼리를 1개의 명령어로 전송합니다.더 을 얻을 수 있습니다.Parallel.For단, 각 한 것을 합니다.) , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , .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();
            }
        }
    }
}

ifAddAddWithValue을 사용하다SQL 주입 및 구문 오류를 피하기 위해 사전에 이스케이프 문자열을 실행해야 합니다.

★★INSERT'1000' 입니다.의 10배 (/10행)보다 수 .INSERT 동시에 수 있습니다.크기 등 정도 수 큰 , huge(패패)가 가 느려집니다.또한 대량의 패킷이 필요하기 때문에 속도가 느려집니다.ROLLBACK로그.COMMIT후 ""를 사용합니다.autocommit=1.

이 방법은 stringbuilder 접근법보다 빠르지 않을 수 있지만 다음과 같이 파라미터화 됩니다.

/// <summary>
    /// Bulk insert some data, uses parameters
    /// </summary>
    /// <param name="table">The Table Name</param>
    /// <param name="inserts">Holds list of data to insert</param>
    /// <param name="batchSize">executes the insert after batch lines</param>
    /// <param name="progress">Progress reporting</param>
    public void BulkInsert(string table, MySQLBulkInsertData inserts, int batchSize = 100, IProgress<double> 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(",");
                }
            }
        }
    }

여기에는 다음과 같은 도우미 클래스가 사용됩니다.

/// <summary>
/// Helper class to builk insert data into a table
/// </summary>
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<object[]>
{
    public MySQLBulkInsertData(params MySQLFieldDefinition[] fieldnames)
    {
        Fields = fieldnames;
    }

    public MySQLFieldDefinition[] Fields { get; private set; }
}

이 도우미 방법은 다음과 같습니다.

    /// <summary>
    /// Return a CSV string of the values in the list
    /// </summary>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    private string ToParameterCSV(IEnumerable<MySQLFieldDefinition> 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);
    }

아주 우아하지는 않지만 잘 작동한다.나는 그것을 포함시키기 위해 진척 추적이 필요하니, 그 부분은 자유롭게 제거해 주세요.

그러면 원하는 출력과 유사한 SQL 명령이 생성됩니다.

편집: ToCSV:

        /// <summary>
    /// Return a CSV string of the values in the list
    /// </summary>
    /// <param name="intValues"></param>
    /// <param name="separator"></param>
    /// <param name="encloser"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    public static string ToCSV<T>(this IEnumerable<T> 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;
    }

가속화하는 한 가지 방법은 모든 삽입물을 ONE 트랜잭션으로 래핑하는 것입니다(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();
}

CSV-파일을 데이터 테이블에 로드하여 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();

여기서 좋은 예를 찾을 수 있습니다.

또는 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.");

1개의 명령어에 여러 개의 삽입을 수행하는 경우에도 문자열 대신 String Builder를 사용하여 1~2인치씩 삽입할 수 있습니다.

EF - MySQL을 사용하다가 비슷한 문제를 발견했습니다.EF 인서트는 너무 느려서 fubo가 언급한 접근법을 사용했습니다.처음에는 성능이 대폭 향상되었지만(최대 10초 만에 20,000개의 레코드가 삽입됨), 테이블 크기가 커짐에 따라 성능이 저하되어 테이블 내에 최대 100만 개의 레코드가 삽입되는 데 최대 250초가 소요되었습니다.

드디어 문제를 알아냈어!테이블의 PK는 GUID 유형(UUID - char(36)입니다.UUID는 순차적으로 인덱싱할 수 없고 삽입할 때마다 인덱스를 재구축해야 하므로 속도가 느려졌습니다.

수정은 PK를 bigint(또는 int)로 대체하고 ID 열로 설정하는 것이었습니다.이로 인해 성능이 향상되었고 삽입에는 평균 12초 정도 걸렸고 테이블에는 약 200만 개 이상의 레코드가 있습니다!

누군가 비슷한 문제에 휘말릴 경우를 대비해서 이 연구 결과를 여기서 공유하려고요!

Stefan Steiger의 말대로 Bulk Insert는 고객의 상황에 적합합니다.

또 다른 방법은 스테이징 테이블을 사용하는 것이기 때문에 프로덕션 테이블에 직접 쓰는 대신 스테이징 테이블에 쓰는 것입니다(구조가 동일).모든 정보를 작성하면 표를 바꿀 수 있습니다.스테이징 aproach를 사용하면 삽입을 위한 테이블 잠금(업데이트 및 삭제에도 사용 가능)을 피할 수 있으며 일부 프로젝트에서 이 패턴이 MySQL에서 많이 사용됩니다.

또, 테이블 키를 무효로 하면, 삽입이 고속이 되는 경우가 있습니다만, 유효하게 하면(MyISAM 엔진 전용) 몇개의 문제가 발생할 가능성이 있습니다.

추가:

밥상이 해 보겠습니다.Products:

• 프로덕트 아이디
• 제품명
• 제품 가격

.ProductsStaging의 줄 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄, 줄.

스테이징 테이블에서 수행하는 모든 작업:

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

스왑 준비 테이블에 새 데이터가 없으면 동일한 메서드를 다시 호출하기 때문입니다. »SwapTables()하나의 SQL 문을 실행하는 방법:

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

데이터 조작 속도는 MySql 엔진(InnoDB, MyISAM 등)에 따라 다르므로 엔진을 변경하여 삽입 속도를 높일 수도 있습니다.

대량 삽입에 파일을 사용하지 않는 방법을 찾았습니다.이 커넥터에는 스트림으로부터의 구현자 부하가 포함되어 있습니다.로딩은 이와 같은 작업을 수행할 수 있습니다.

  public void InsertData(string table, List<string> columns, List<List<object>> data) {

  using (var con = OpenConnection() as MySqlConnection) {
    var bulk = new MySqlBulkLoader(con);
    using (var stream = new MemoryStream()) {
      bulk.SourceStream = stream;
      bulk.TableName = table;
      bulk.FieldTerminator = ";";
      var writer = new StreamWriter(stream);

      foreach (var d in data)
        writer.WriteLine(string.Join(";", d));

      writer.Flush();
      stream.Position = 0;
      bulk.Load();
    }
  }
}

제 제안은 아이디어이지 예시나 해결책이 아닙니다.INSERT를 사용하지 않고 데이터를 여러 매개 변수로 전달하면(예를 들어 한 번에 100K를 모두 사용할 필요는 없으며 1K 번들을 사용할 수 있음) INSERT를 수행하는 STOREDOCESURE에 데이터를 전달할 수 있습니다.

그렇게 하는 것은 대량 작업이 좋은 매너입니다.사용자의 속성을 읽은 다음 대량 쿼리를 생성하는...

유용한 메서드 BulkInsert와 MySql 및 EF6+를 사용한 BulkUpdate를 모두 포함하는 github 저장소가 있습니다.

BulkUpdate/BulkInsert는 기본적으로 범용 엔티티에서 모든 속성을 읽은 후 대량 쿼리를 만듭니다.

추신: 이것은 제 요구에 맞게 개발되었으며, 이 프로젝트는 커뮤니티에 가치 있는 더 나은 솔루션을 위해 개선하거나 변경할 수 있는 사용자에게 개방되어 있습니다.

Ps²: 문제가 해결되지 않으면 프로젝트를 변경하여 원하는 것을 달성해 보십시오.적어도 좋은 시작입니다.

여기 좀 봐주세요

언급URL : https://stackoverflow.com/questions/25323560/most-efficient-way-to-insert-rows-into-mysql-database