大量のデータの下での MySQL 挿入メソッドのパフォーマンスの比較-mysql チュートリアル-php.cn

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

黄舟

リリース： 2017-02-23 10:53:17

オリジナル

1225 人が閲覧しました

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

挿入分析

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

接続: (3)
サーバーにクエリを送信: (2) )
分析クエリ: (2)
レコードの挿入: (レコードサイズの1倍)
インデックスの挿入: (インデックスの1倍)
閉じる: (1)

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

各挿入ステートメントに複数の行を書き込む、バッチ挿入
。
すべてのクエリステートメントをトランザクションに書き込みます
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) に注目してください。