大量のデータの下での MySQL 挿入メソッドのパフォーマンスの比較

日常のビジネスデータ処理であっても、データベースのインポートおよびエクスポートであっても、大量のデータの処理を必要とする挿入が発生することがあります。挿入方法とデータベース エンジンの両方が挿入速度に影響します。この記事は、将来のアプリケーションでの挿入方法の選択を容易にするために、理論的および実用的な観点からさまざまな方法を分析および比較することを目的としています。

挿入分析

MySQL にレコードを挿入するのに必要な時間は次の要素で構成されます。数値はおおよその割合を表します:

• 接続: (3)

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

• 分析クエリ: (2)

• レコードの挿入: (レコードサイズの1倍)

• インデックスの挿入: (インデックスの1倍)

• 閉じる: (1)

実行するSQL ステートメントを挿入するたびに、接続とクローズを除くすべてのステップを N 回実行する必要がありますが、これには非常に時間がかかります:

1. 各挿入ステートメントに複数の行を書き込む、バッチ挿入

2. 。

   すべてのクエリステートメントをトランザクションに書き込みます

3. Load Dataを使用してデータをインポートします

各メソッドのパフォーマンスは次のとおりです。

Innodb エンジン

InnoDB は、トランザクション (コミット)、ロールバック (ロールバック)、およびクラッシュ回復機能 (クラッシュ回復機能) を備えたトランザクションセーフ (ACID 準拠) テーブルを MySQL に提供します。 InnoDB は、行ロック (行レベルでのロック) と外部キー制約 (FOREIGN KEY 制約) を提供します。

InnoDB は大容量データベース システムを処理できるように設計されており、その CPU 使用率は他のディスクベースのリレーショナル データベース エンジンに匹敵しません。技術的には、InnoDB は MySQL バックグラウンド上に配置された完全なデータベース システムであり、データとインデックスをキャッシュするためにメイン メモリ内に独自の専用バッファ プールを確立します。

テスト環境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

データ合計100万個

挿入後のデータベースサイズは38.6MB(インデックスなし)、46.8MB(インデックスあり)です

• インデックスなし 単一ピース 合計挿入時間: 229 秒 ピークメモリ: 246KB

• インデックスあり 単一挿入の合計時間: 242 秒 ピークメモリ: 246KB

• インデックスなしのバッチ挿入の合計時間: 10 秒 ピークメモリ: 8643KB

• インデックスあり バッチ挿入に費やされた合計時間: 16 秒 ピーク メモリ: 8643KB

• インデックスなしの挿入に費やされた合計時間: 78 秒 ピーク メモリ: 246 KB

• インデックスを使用した挿入に費やされた合計時間: 82 秒 ピーク メモリ: 246 KB

• なしインデックスロードデータ挿入にかかった合計時間: 12秒ピークメモリ: 246KB

• インデックスロードデータ挿入にかかった合計時間: 11秒ピークメモリ: 246KB

MyIASMエンジン

MyISAMはデフォルトのストレージですMySQLのエンジン。シンプルなデザインで全文検索にも対応しています。

テスト環境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

データ合計100万個

挿入後のデータベースサイズは19.1MB(インデックスなし)、38.6MB(インデックスあり)です

• インデックスなし 単体 合計挿入時間: 82秒 ピークメモリ: 246KB

• インデックスあり 合計挿入時間: 86秒 ピークメモリ: 246KB

• インデックスなし バッチ挿入合計時間: 3秒 ピークメモリ: 8643KB

• ありインデックス バッチ挿入にかかった合計時間: 7 秒 ピーク メモリ: 8643 KB

• インデックスなしのデータのロード挿入に費やされた合計時間: 6 秒 ピーク メモリ: 246 KB

• インデックス付きのデータのロードの挿入に費やされた合計時間: 8 秒 ピーク メモリ: 246 KB

概要

私がテストしたデータの量はそれほど多くありませんが、これらの挿入メソッドが速度に与える影響は大まかに理解できます。最も速いのは Load Data メソッドです。この方法はファイルの書き込みが必要なため比較的面倒ですが、メモリと速度のバランスをとることができます。

テストコード

<?php
$dsn = &#39;mysql:host=localhost;dbname=test&#39;;
$db = new PDO($dsn,&#39;root&#39;,&#39;&#39;,array(PDO::ATTR_PERSISTENT => true));
//删除上次的插入数据
$db->query(&#39;delete from `test`&#39;);
//开始计时
$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,&#39;tsetssdf&#39;)");
    }
} elseif ($num == 2) {
    // 批量插入，为了不超过max_allowed_packet，选择每10万插入一次
    for ($i = 0; $i < $sum; $i++) {
        if ($i == $sum - 1) { //最后一次
            if ($i%100000 == 0){
                $values = "($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            } else {
                $values .= ",($i, &#39;testtest&#39;)";
                $db->query("insert into `test` (`id`, `name`) values $values");
            }
            break;
        }
        if ($i%100000 == 0) { //平常只有在这个情况下才插入
            if ($i == 0){
                $values = "($i, &#39;testtest&#39;)";
            } else {
                $db->query("insert into `test` (`id`, `name`) values $values");
                $values = "($i, &#39;testtest&#39;)";
            }
        } else {
            $values .= ",($i, &#39;testtest&#39;)";    
        }
    }
} elseif ($num == 3) {
    // 事务插入
    $db->beginTransaction(); 
    for($i = 0; $i < $sum; $i++){
        $db->query("insert into `test` (`id`,`name`) values ($i,&#39;tsetssdf&#39;)");
    }
    $db->commit();
} elseif ($num == 4) {
    // 文件load data
    $filename = dirname(__FILE__).&#39;/test.sql&#39;;
    $fp = fopen($filename, &#39;w&#39;);
    for($i = 0; $i < $sum; $i++){
        fputs($fp, "$i,&#39;testtest&#39;\r\n");    
    }
    $db->exec("load data infile &#39;$filename&#39; into table test fields terminated by &#39;,&#39;");
}

$end_time = time();
echo "总耗时", ($end_time - $start_time), "秒\n";
echo "峰值内存", round(memory_get_peak_usage()/1000), "KB\n";

?>

上記は、MySQL に大量のデータを挿入するためのさまざまな方法のパフォーマンス分析と比較です。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) に注目してください。