大量のデータを挿入または変更するためのいくつかの MySQL メソッドの比較

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

挿入分析

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

接続: (3)
サーバーへのクエリの送信: (2)
分析クエリ: (2)
レコードの挿入: (レコード サイズの 1 倍)
インデックスの挿入: (インデックス 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 秒 ピーク メモリ: 246 KB
インデックスを使用した単一挿入に費やされた合計時間: 242 秒 ピーク メモリ: 246 KB
インデックスなしのバッチ挿入に費やされた合計時間: 10 秒 ピーク メモリ: 8643 KB
インデックスを使用したバッチ挿入に費やされた合計時間インデックス: 16 秒 ピーク メモリ: 8643 KB
インデックスなしのトランザクション挿入に要した合計時間: 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

概要

テストしたデータの量はそれほど多くありません大きいですが、これら 3 つの挿入メソッドが速度に与える影響について大まかに把握できます。最も速いのは 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";

これは、単純な SQL ステートメントの挿入および変更ステートメントのスプライシング形式です。 INSERT INTO user (id, name) VALUES (2, '李思');

INSERT INTO user (id, name) VALUES (3, '王五');

は次のように結合できます:

INSERT INTO user ( id, name) VALUES
(1, '张三'),

(2, '李四'),

(3, '王五');

(2) ステートメントのスプライシングを更新:

update user set name ='Zhang San' where id='1';
update user set name='Li Si' where id='2';

update user set name='Wang Wu' where id='3';

スプライス可能対象:

ユーザーを更新
set name = CASE id

when 1 then '张三'

when 2 then '李思'

when 3 then '王五'

end
where id IN (1,2, 3);

上記は、大量のデータを挿入または変更するためのいくつかの MySQL メソッドの比較です。

関連チュートリアル:

mysql への大量のデータの挿入に関するディスカッション (量的変化は質的変化を引き起こす)_MySQL

解決策: データをデータベースに挿入できません。解決策

phpはmysqlにデータを挿入できません

以上が大量のデータを挿入または変更するためのいくつかの MySQL メソッドの比較の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。