Wszystkie poniższe informacje dotyczą InnoDB.
Czuję, że znajomość prędkości 3 różnych metod jest ważna.
Istnieją 3 metody:
- INSERT: INSERT z ON DUPLICATE KEY UPDATE
- TRANSAKCJA: W przypadku dokonywania aktualizacji dla każdego rekordu w ramach transakcji
- PRZYPADEK: w którym masz przypadek / kiedy dla każdego innego rekordu w ramach AKTUALIZACJI
Właśnie to przetestowałem, a metoda INSERT wynosiła 6,7x dla mnie szybsza niż metoda TRANSAKCJA. Próbowałem na zestawie zarówno 3000, jak i 30 000 wierszy.
Metoda TRANSACTION nadal musi uruchamiać każde zapytanie indywidualnie, co zajmuje dużo czasu, chociaż podczas wykonywania powoduje zapisanie wyników w pamięci lub coś w tym stylu. Metoda TRANSACTION jest także dość droga zarówno w dziennikach replikacji, jak i dzienników zapytań.
Co gorsza, metoda CASE była o 41,1x wolniejsza niż metoda INSERT z 30 000 rekordów (6,1x wolniejsza niż TRANSACTION). I 75 razy wolniej w MyISAM. Metody INSERT i CASE pobiły nawet przy ~ 1000 rekordów. Nawet przy 100 rekordach metoda CASE jest BARELY szybsza.
Ogólnie rzecz biorąc, uważam, że metoda INSERT jest zarówno najlepsza, jak i najłatwiejsza w użyciu. Zapytania są mniejsze i łatwiejsze do odczytania i podejmują tylko 1 zapytanie działania. Dotyczy to zarówno InnoDB, jak i MyISAM.
Materiały dodatkowe:
Rozwiązaniem problemu non-default-polowego INSERT jest tymczasowo wyłączyć odpowiednie tryby SQL: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). Pamiętaj, aby zapisać sql_modepierwszy, jeśli planujesz go cofnąć.
Co do innych komentarzy, które widziałem, że auto_increment idzie w górę za pomocą metody INSERT, wydaje się, że tak jest w InnoDB, ale nie w MyISAM.
Kod do uruchomienia testów jest następujący. Wysyła również pliki .SQL, aby usunąć narzut interpretera 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).';');
}