Leistungsvergleich von MySQL-Einfügungsmethoden bei großen Datenmengen

Ganz gleich, ob es sich um die tägliche Verarbeitung von Geschäftsdaten oder um den Import und Export von Datenbanken handelt, Sie können auf Einfügungen stoßen, die die Verarbeitung großer Datenmengen erfordern. Sowohl die Einfügemethode als auch die Datenbank-Engine wirken sich 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)

• Abfrage analysieren: (2)

• Datensatz einfügen: (1x Datensatzgröße)

• Index einfügen: (1x Index)

• Schließen: (1)

Wenn wir jedes Mal, wenn wir ein Element einfügen, eine SQL-Anweisung ausführen, müssen wir alle Schritte außer dem Verbinden und Schließen N-mal ausführen. Es gibt mehrere Optimierungsmethoden:

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

2. Schreiben Sie alle Abfrageanweisungen in die Transaktion

3. Verwenden Sie „Daten laden“, um Daten zu importieren

Die Leistung jeder 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

• Einzelnes Einfügen mit Index Gesamtzeit: 242 s Spitzenspeicher: 246 KB

• Gesamtzeit für Stapeleinfügung ohne Index: 10 s Spitzenspeicher: 8643 KB

• Batch-Einfügung mit Index Gesamtzeit: 16 s Spitzenspeicher: 8643 KB

• Gesamtzeit für das Einfügen von Transaktionen ohne Index: 78 s Spitzenspeicher: 246 KB

• Einfügung mit indizierter Transaktion Gesamtzeit: 82 Sekunden Spitzenspeicher: 246 KB

• Laden ohne Index Gesamtzeit für das Einfügen von Daten: 12 Sekunden Spitzenspeicher: 246 KB

• Laden mit Index Gesamtdateneinfügungszeit: 11 s Spitzenspeicher: 246 KB

MyIASM-Engine

MyISAM ist die Standardspeicher-Engine von 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 ist 19,1 MB (kein Index), 38,6 (mit Index)

• Gesamtzeit für einzelnes Einfügen ohne Index: 82 s Spitzenspeicher: 246 KB

• Einzelnes Einfügen mit Index Gesamtzeit: 86 s Spitzenspeicher: 246 KB

• Gesamtzeit für Stapeleinfügung ohne Index: 3 s Spitzenspeicher: 8643 KB

• Stapeleinfügung mit Index Gesamtzeit: 7 s Spitzenspeicher: 8643 KB

• Laden ohne Index Gesamtzeit für das Einfügen von Daten: 6 s Spitzenspeicher: 246 KB

• Mit Index laden Gesamte Dateneinfügungszeit: 8 s Spitzenspeicher: 246 KB

Zusammenfassung

Die von mir getestete Datenmenge ist nicht sehr groß, aber ich kann sie ungefähr schätzen Verstehen Sie die Geschwindigkeit dieser Einfügemethoden. Die schnellste Methode muss die Methode „Daten laden“ sein. 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";

?>

Das Obige ist die Leistungsanalyse und der Vergleich verschiedener Methoden zum Einfügen großer Datenmengen in MySQL. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!