Alle der folgenden Punkte gelten für InnoDB.
Ich denke, es ist wichtig, die Geschwindigkeiten der 3 verschiedenen Methoden zu kennen.
Es gibt 3 Methoden:
- INSERT: INSERT mit ON DUPLICATE KEY UPDATE
- TRANSACTION: Hier führen Sie eine Aktualisierung für jeden Datensatz innerhalb einer Transaktion durch.
- CASE: In dem Sie ein case/when für jeden verschiedenen Datensatz innerhalb eines UPDATE
Ich habe dies gerade getestet, und die INSERT-Methode war 6.7x für mich schneller als die TRANSACTION-Methode. Ich habe es mit einem Satz von 3.000 und 30.000 Zeilen versucht.
Die TRANSACTION-Methode muss immer noch jede einzelne Abfrage ausführen, was Zeit in Anspruch nimmt, obwohl sie die Ergebnisse während der Ausführung im Speicher stapelt oder ähnliches. Die TRANSACTION-Methode ist auch ziemlich teuer, sowohl bei der Replikation als auch bei den Abfrageprotokollen.
Schlimmer noch, die CASE-Methode war 41.1x langsamer als die INSERT-Methode mit 30.000 Datensätzen (6,1x langsamer als TRANSACTION). Und 75x langsamer in MyISAM. Die INSERT- und CASE-Methoden erreichten die Gewinnschwelle bei ~1.000 Datensätzen. Selbst bei 100 Datensätzen ist die CASE-Methode GERINGSTENS schneller.
Im Allgemeinen halte ich die INSERT-Methode für die beste und am einfachsten zu verwendende Methode. Die Abfragen sind kleiner und leichter zu lesen und nehmen nur eine Abfrage in Anspruch. Dies gilt sowohl für InnoDB als auch für MyISAM.
Bonusmaterial:
Die Lösung für das INSERT-Nicht-Standardfeld-Problem besteht darin, die entsprechenden SQL-Modi vorübergehend zu deaktivieren: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES","") . Stellen Sie sicher, dass Sie die sql_mode wenn Sie es rückgängig machen wollen.
Wie für andere Kommentare, die ich gesehen habe, die sagen, dass die Auto-Inkrement geht mit der INSERT-Methode, scheint dies der Fall in InnoDB, aber nicht MyISAM sein.
Der Code zur Durchführung der Tests lautet wie folgt. Er gibt auch .SQL-Dateien aus, um den Aufwand für den PHP-Interpreter zu verringern
<?php
//Variables
$NumRows=30000;
//These 2 functions need to be filled in
function InitSQL()
{
}
function RunSQLQuery($Q)
{
}
//Run the 3 tests
InitSQL();
for($i=0;$i<3;$i++)
RunTest($i, $NumRows);
function RunTest($TestNum, $NumRows)
{
$TheQueries=Array();
$DoQuery=function($Query) use (&$TheQueries)
{
RunSQLQuery($Query);
$TheQueries[]=$Query;
};
$TableName='Test';
$DoQuery('DROP TABLE IF EXISTS '.$TableName);
$DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
$DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');
if($TestNum==0)
{
$TestName='Transaction';
$Start=microtime(true);
$DoQuery('START TRANSACTION');
for($i=1;$i<=$NumRows;$i++)
$DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);
$DoQuery('COMMIT');
}
if($TestNum==1)
{
$TestName='Insert';
$Query=Array();
for($i=1;$i<=$NumRows;$i++)
$Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
$Start=microtime(true);
$DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
}
if($TestNum==2)
{
$TestName='Case';
$Query=Array();
for($i=1;$i<=$NumRows;$i++)
$Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
$Start=microtime(true);
$DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
}
print "$TestName: ".(microtime(true)-$Start)."<br>\n";
file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}
