MySQL大量データ挿入各種方法の性能分析と比較

日常業務データ処理中でも、データベースのインポート・エクスポートでも、大量のデータを処理する必要がある挿入が発生する可能性があります.挿入の方式とデータベースエンジンはいずれも挿入速度に影響を及ぼし、この文章は理論と実践の上で各種の方法に対して分析と比較を行い、後で応用する中で挿入方法の選択を便利にすることを目的としている.
解析の挿入
MySQLでレコードを挿入するのに必要な時間は、次の要素で構成されています.数字は約比例しています.

接続:(3)

クエリーをサーバに送信:(2)

分析クエリー:(2)

挿入記録:(1 x記録サイズ)

挿入インデックス:(1 xインデックス)

クローズ:(1)

1つを挿入するたびにSQL文を実行する場合は、接続と閉じる以外のすべてのステップをN回実行する必要があります.これは非常に時間がかかります.最適化の方法はいくつかあります.

各insert文に複数行を書き込み、

を一括挿入する.

すべてのクエリ文をトランザクションの

に書き込む

Load Dataを利用してデータ

をインポートする.
各方式で実行される性能は以下の通りである.
Innodbエンジン
InnoDBはMySQLにトランザクション(commit)、ロールバック(rollback)、クラッシュ修復能力(crash recovery capabilities)を持つトランザクションセキュリティ(transaction-safe(ACID compliant))型テーブルを提供します.InnoDBは、ロー・ロック(locking on row level)および外部キー制約(FOREIGN KEY constraints)を提供する.
InnoDBの設計目標は、大容量データベースシステムを処理することであり、そのCPU利用率は他のディスクベースのリレーショナルデータベースエンジンとは比べものにならない.技術的には、InnoDBはMySQLのバックグラウンドに配置された完全なデータベースシステムであり、InnoDBはメインメモリに専用のバッファプールを構築してデータとインデックスをキャッシュします.
テスト環境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
合計100 Wデータ
挿入後のデータベースサイズ38.6 MB(インデックスなし)、46.8(インデックスあり)

インデックスなしシングル挿入総消費時間:229 sピークメモリ:246 KB

インデックス付きシングル挿入総消費時間:242 sピークメモリ:246 KB

インデックスなしバッチ挿入総消費時間:10 sピークメモリ:8643 KB

インデックス付き一括挿入総消費時間:16 sピークメモリ:8643 KB

インデックスのないトランザクションの合計時間:78 sピークメモリ:246 KB

インデックス・トランザクション挿入の合計消費時間:82 sピーク・メモリ:246 KB

インデックスなしLoad Data挿入総消費時間:12 sピークメモリ:246 KB

インデックス付きLoad Data挿入総消費時間:11 sピークメモリ:246 KB

MyIASMエンジン
MyISAMはMySQLのデフォルトのストレージエンジンです.設計が簡単で、全文検索をサポートします.
テスト環境
Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16
合計100 Wデータ
挿入後のデータベースサイズ19.1 MB(インデックスなし)、38.6(インデックスあり)

インデックスなしシングル挿入総消費時間:82 sピークメモリ:246 KB

インデックス付きシングル挿入総消費時間:86 sピークメモリ:246 KB

インデックスなしバッチ挿入総消費時間:3 sピークメモリ:8643 KB

インデックス付き一括挿入総消費時間:7 sピークメモリ:8643 KB

インデックスなしLoad Data挿入総消費時間:6 sピークメモリ:246 KB

インデックス付きLoad Data挿入総消費時間:8 sピークメモリ:246 KB

まとめ
私がテストしたデータ量はそれほど大きくありませんが、このいくつかの挿入方式が速度に与える影響を大体理解することができます.最も速いのは必ずLoad Data方式です.ファイルの書き込みにかかわるため、メモリと速度を両立させることができます.
テストコード

<?php
$dsn = 'mysql:host=localhost;dbname=test';
$db = new PDO($dsn,'root','',array(PDO::ATTR_PERSISTENT => true));
//         
$db->query('delete from `test`');
//    
$start_time = time();
$sum = 1000000;
//     
$num = 1;

if ($num == 1){
    //     
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,'tsetssdf')");
    }
} elseif ($num == 2) {
    //     ，     max_allowed_packet，   10     
    for ($i = 0; $i < $sum; $i++) {
        if ($i == $sum - 1) { //    
            if ($i%100000 == 0){
                $values = "($i, 'testtest')";
                $db->query("insert into `test` (`id`, `name`) values $values");
            } else {
                $values .= ",($i, 'testtest')";
                $db->query("insert into `test` (`id`, `name`) values $values");
            }
            break;
        }
        if ($i%100000 == 0) { //             
            if ($i == 0){
                $values = "($i, 'testtest')";
            } else {
                $db->query("insert into `test` (`id`, `name`) values $values");
                $values = "($i, 'testtest')";
            }
        } else {
            $values .= ",($i, 'testtest')";    
        }
    }
} elseif ($num == 3) {
    //     
    $db->beginTransaction(); 
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,'tsetssdf')");
    }
    $db->commit();
} elseif ($num == 4) {
    //   load data
    $filename = dirname(__FILE__).'/test.sql';
    $fp = fopen($filename, 'w');
    for($i = 0; $i < $sum; $i++){
        fputs($fp, "$i,'testtest'\r
");    
    }
    $db->exec("load data infile '$filename' into table test fields terminated by ','");
}

$end_time = time();
echo "   ", ($end_time - $start_time), " 
";
echo "    ", round(memory_get_peak_usage()/1000), "KB
";

?>

 
参照先:

MySQL:InnoDBかMyISAMか?

mysqlストレージエンジン:InnoDBとMyISAMの違いと優劣

MySQLビッグデータ量クイック挿入方法と文最適化