Vergleich mehrerer MySQL-Methoden zum Einfügen oder Ändern großer Datenmengen

Ob es sich um die tägliche Verarbeitung von Geschäftsdaten oder den Import und Export von Datenbanken handelt, kann es vorkommen, dass Sie große Datenmengen einfügen oder ändern müssen. Die Einfüge- oder Änderungsmethode und die Datenbank-Engine wirken sich alle auf die Einfügegeschwindigkeit aus. Ziel dieses Artikels ist es, verschiedene Methoden aus theoretischer und praktischer Sicht zu analysieren und zu vergleichen, um die Auswahl von Einfügemethoden in zukünftigen Anwendungen zu erleichtern.

Einfügungsanalyse

Die zum Einfügen eines Datensatzes in MySQL benötigte Zeit setzt sich aus folgenden Faktoren zusammen, wobei die Zahlen ungefähre Proportionen darstellen:

Verbindung: (3)
Abfrage an Server senden: (2)
Analyseabfrage: (2)
Datensatz einfügen: (1x Datensatzgröße)
Index einfügen: (1x Index)
Schließen: (1)

Wenn wir für jede Einfügung eine SQL-Anweisung ausführen, müssen wir alle Schritte außer dem Verbinden und Schließen N-mal ausführen, was sehr zeitaufwändig ist. Es gibt mehrere Möglichkeiten zur Optimierung:

(1) Schreiben Sie mehrere Zeilen in jede Einfügeanweisung, Batch-Einfügung

(2) Schreiben Sie alle Abfrageanweisungen in die Transaktion

(3) Verwenden Sie Load Data, um Daten zu importieren

Die Leistung von Jede Methode ist wie folgt.

Innodb-Engine

InnoDB stellt MySQL transaktionssichere (ACID-kompatible) Transaktions- (Commit-), Rollback- (Rollback-) und Crash-Recovery-Funktionen (Crash Recovery Capabilities) zur Verfügung. InnoDB bietet Zeilensperren (Sperren auf Zeilenebene) und Fremdschlüsseleinschränkungen (FOREIGN KEY-Einschränkungen).

InnoDB ist für den Umgang mit Datenbanksystemen mit großer Kapazität konzipiert und seine CPU-Auslastung wird von anderen festplattenbasierten relationalen Datenbank-Engines nicht erreicht. Technisch gesehen ist InnoDB ein vollständiges Datenbanksystem auf dem MySQL-Hintergrund. InnoDB richtet im Hauptspeicher einen eigenen Pufferpool für die Zwischenspeicherung von Daten und Indizes ein.

Testumgebung

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

Insgesamt 1 Million Datenstücke

Nach dem Einfügen die Datenbankgröße ist 38,6 MB (kein Index), 46,8 (mit Index)

Gesamtzeit für einzelnes Einfügen ohne Index: 229 s Spitzenspeicher: 246 KB
Gesamtzeit für einzelnes Einfügen mit Index: 242 s Spitzenspeicher: 246 KB
Batch ohne Index Gesamteinfügezeit: 10 s Spitzenspeicher: 8643 KB
Gesamtstapeleinfügezeit mit Index: 16 s Spitzenspeicher: 8643 KB
Gesamteinfügezeit ohne Indextransaktion: 78 s Spitzenspeicher: 246 KB
Gesamteinfügezeit mit Indextransaktion Zeitaufwendig: 82 s Spitzenspeicher: 246 KB
Gesamtzeitaufwand für das Einfügen von Ladedaten ohne Index: 12 s Spitzenspeicher: 246 KB
Gesamtzeitaufwand für das Einfügen von indizierten Ladedaten: 11 s Spitzenspeicher: 246 KB

MyIASM-Engine

MyISAM ist die Standardspeicher-Engine für MySQL. Das Design ist einfach und unterstützt die Volltextsuche.

Testumgebung

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

Insgesamt 1 Million Datenstücke

Nach dem Einfügen die Datenbankgröße beträgt 19,1 MB (kein Index), 38,6 (mit Index)

Gesamtzeit für einzelnes Einfügen ohne Index: 82 s Spitzenspeicher: 246 KB
Gesamtzeit für einzelnes Einfügen mit Index: 86 s Spitzenspeicher: 246 KB
Batch ohne Index Gesamteinfügezeit: 3 s Spitzenspeicher: 8643 KB
Gesamtzeit des Stapeleinfügens mit Index: 7 s Spitzenspeicher: 8643 KB
Gesamteinfügezeit ohne Index Ladedaten: 6 s Spitzenspeicher: 246 KB
Laden Daten mit Index Gesamteinfügezeit: 8 s Spitzenspeicher: 246 KB

Zusammenfassung

Die von mir getestete Datenmenge ist nicht sehr groß, aber ich kann den Einfluss dieser Einfügemethoden auf die Geschwindigkeit ungefähr verstehen. Die schnellste Methode muss sein. Es ist die Load Data-Methode. Diese Methode ist relativ mühsam, da sie das Schreiben von Dateien erfordert, kann jedoch sowohl Speicher als auch Geschwindigkeit berücksichtigen.

Testcode

<?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";

Hier ist eine kurze Beschreibung der Spleißform von Anweisungen zum Einfügen und Ändern von SQL-Anweisungen:

(1) Spleißen von Einfügungsanweisungen:

INSERT INTO user (id, name) VALUES (1, '张三');
INSERT INTO user (id, name) VALUES (2, '李思');
INSERT INTO user (id , name) VALUES (3, '王五');

kann gespleißt werden in:

INSERT INTO user (id, name) VALUES
(1, '张三'),
(2, '李思'),
(3, '王五');

(2) Update-Anweisungsspleißung:

Benutzersatznamen aktualisieren ='Zhang San' where id='1';
update user set name='李思' where id='2';
update user set name='Wang Wu' where id='3';

Kann gespleißt werden als:

Benutzer aktualisieren
Name festlegen = CASE-ID
bei 1 dann '张三'
bei 2 dann '李思'
wenn 3 then '王五'
end
where id IN (1,2,3);

Das Obige ist ein Vergleich mehrerer MySQL-Methoden zum Einfügen oder Ändern großer Datenmengen . Ich hoffe, es wird für alle hilfreich sein.

Verwandte Tutorials:

Diskussion über das Einfügen großer Datenmengen in MySQL (quantitative Änderungen führen zu qualitativen Änderungen)_MySQL

Lösung: Daten können nicht in die Datenbank eingefügt werden. Lösung

PHP kann keine Daten in MySQL einfügen

Das obige ist der detaillierte Inhalt vonVergleich mehrerer MySQL-Methoden zum Einfügen oder Ändern großer Datenmengen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!