.Net Core を使用して数千万のデータを MySQL にインポートする手順

事前準備

テスト注文フォーム

テーブル「trade」を作成します（
  `id` VARCHAR(50) NULL デフォルト NULL COLLATE 'utf8_unicode_ci',
  `trade_no` VARCHAR(50) NULL デフォルト NULL COLLATE 'utf8_unicode_ci',
  ユニークインデックス `id` (`id`)、
  インデックス `trade_no` (`trade_no`)
）
コメント = '注文'
照合='utf8_unicode_ci'
エンジン=InnoDB;

テスト環境

オペレーティングシステム: Windows 10 Professional
CPU: Intel(R) Core(TM) i7-8650U CPU @1.90GHZ 2.11GHZ
メモリ: 16G
MySQL バージョン: 5.7.26

実施方法：

1. 単一のデータを挿入する

これは最も一般的な方法で、ループを通じてデータを 1 つずつインポートします。この方法の明らかな欠点は、毎回データベースに接続する必要があることです。

実装コード:

//開始時刻 var startTime = DateTime.Now;
(var conn = new MySqlConnection(connsql)) を使用します
{
    接続を開きます。

    // 100,000 個のデータを挿入します for (var i = 0; i < 100000; i++)
    {
        //挿入 var sql = string.Format("insert into trade(id,trade_no) values('{0}','{1}');",
            Guid.NewGuid().ToString()、"trade_" + (i + 1)
            );
        var sqlComm = 新しい MySqlCommand();
        sqlComm.Connection は接続です。
        sqlComm.CommandText = sql;
        sqlComm.ExecuteNonQuery();
        sqlComm.Dispose();
    }

    接続を閉じる();
}

//完了時間 var endTime = DateTime.Now;

//時間がかかります var spanTime = endTime - startTime;
Console.WriteLine("ループ挿入メソッドには次の時間がかかります: " + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "ミリ秒");

100,000 回のテストには次の時間がかかります:

上記の例では、100,000 件のレコードをバッチでインポートしており、データベースに 100,000 回接続する必要があります。 SQL ステートメントを 1000 に変更し、それらを 1 つに連結することで、データベース接続を減らすことができます。コードは次のように変更されます。

//開始時刻 var startTime = DateTime.Now;
(var conn = new MySqlConnection(connsql)) を使用します
{
    接続を開きます。

    // 100,000 個のデータを挿入します var sql = new StringBuilder();
    (var i = 0; i < 100000; i++) の場合
    {
        //挿入 sql.AppendFormat("insert into trade(id,trade_no) values('{0}','{1}');",
            Guid.NewGuid().ToString()、"trade_" + (i + 1)
            );

        //マージして挿入 if (i % 1000 == 999)
        {
            var sqlComm = 新しい MySqlCommand();
            sqlComm.Connection は接続です。
            sqlComm.CommandText = sql.ToString();
            sqlComm.ExecuteNonQuery();
            sqlComm.Dispose();
            sql.Clear();
        }
    }

    接続を閉じる();
}

//完了時間 var endTime = DateTime.Now;

//時間がかかります var spanTime = endTime - startTime;
Console.WriteLine("ループ挿入メソッドには次の時間がかかります: " + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "ミリ秒");

100,000 回のテストには次の時間がかかります:

最適化後は、元々 100,000 回必要だったデータベース接続時間が 100 回の接続で済みます。最終的な動作結果から判断すると、データベースは同一サーバー上にあり、ネットワーク転送を伴わないため、パフォーマンスの向上は明らかではありません。

2. マージデータ挿入

MySQL は、データをマージすることによるバッチデータインポートもサポートしています。実装コード:

//開始時刻 var startTime = DateTime.Now;
(var conn = new MySqlConnection(connsql)) を使用します
{
    接続を開きます。

    // 100,000 個のデータを挿入します var sql = new StringBuilder();
    (var i = 0; i < 100000; i++) の場合
    {
        (i % 1000 == 0)の場合
        {
            sql.Append("trade(id,trade_no) の値に挿入");
        }

        // 連結 sql.AppendFormat("('{0}','{1}'),", Guid.NewGuid().ToString(), "trade_" + (i + 1));

        // 一度に 1000 件のレコードを挿入します if (i % 1000 == 999)
        {
            var sqlComm = 新しい MySqlCommand();
            sqlComm.Connection は接続です。
            sqlComm.CommandText = sql.ToString().TrimEnd(',');
            sqlComm.ExecuteNonQuery();
            sqlComm.Dispose();
            sql.Clear();
        }
    }

        
    接続を閉じる();
}

//完了時間 var endTime = DateTime.Now;

//時間がかかります var spanTime = endTime - startTime;
Console.WriteLine("マージ データ挿入メソッドには時間がかかります: " + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "ミリ秒");

100,000 回のテストには次の時間がかかります:

このように操作を挿入すると、プログラムの挿入効率が大幅に向上します。最初の方法でも最適化後のデータベース接続数を減らすことができますが、2 番目の方法ではマージ後のログの量 (MySQL binlog と innodb トランザクションログ) が減り、ログフラッシュの量と頻度が減るため、効率が向上します。同時に、SQL ステートメントの解析回数を減らし、ネットワーク転送 IO を削減することもできます。

3. MySqlBulkLoader 挿入

MySQLBulkLoader は LOAD DATA INFILE とも呼ばれます。その原理はファイルからデータを読み取ることです。したがって、データセットをファイルに保存し、ファイルから読み取る必要があります。

実装コード:

//開始時刻 var startTime = DateTime.Now;
(var conn = new MySqlConnection(connsql)) を使用します
{
    接続を開きます。
    var テーブル = 新しい DataTable();
    table.Columns.Add("id", typeof(string));
    table.Columns.Add("trade_no", typeof(string));

    // 100,000 個のデータを生成する (var i = 0; i < 100000; i++)
    {
        (i % 500000 == 0)の場合
        {
            テーブル行をクリアします。
        }

        //レコード var row = table.NewRow();
        行[0] = Guid.NewGuid().ToString();
        行[1] = "trade_" + (i + 1);
        テーブルに行を追加します。

        // 500,000 件のレコードを一括で挿入します if (i % 500000 != 499999 && i < (100000 - 1))
        {
            続く;
        }
        Console.WriteLine("挿入を開始します: " + i);

        //データをcsv形式に変換します。var tradeCsv = DataTableToCsv(table);
        var tradeFilePath = System.AppDomain.CurrentDomain.BaseDirectory + "trade.csv";
        File.WriteAllText(tradeFilePath、tradeCsv);

        #region データベースに保存 var bulkCopy = new MySqlBulkLoader(conn)
        {
            フィールドターミネータ = ",",
            フィールド引用文字 = '"',
            エスケープ文字 = '"',
            行終端文字 = "\r\n",
            ファイル名 = tradeFilePath、
            スキップする行数 = 0、
            テーブル名 = "trade"
        };

        bulkCopy.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => column.ColumnName).ToList());
        一括コピー.Load();
        #終了領域
    }

    接続を閉じる();
}

//完了時間 var endTime = DateTime.Now;

//時間がかかります var spanTime = endTime - startTime;
Console.WriteLine("MySqlBulk メソッドには、" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "ミリ秒" かかります);

100,000 回のテストには次の時間がかかります: