Hromadná aktualizace záznamů

Školení, která pořádám

MySQL podporuje tzv. vícehodnotový INSERT, kdy lze jedním příkazem vložit najednou víc záznamů. Jeho výhoda spočívá především ve vyšší rychlosti.

Stejné výhody se dá dosáhnout i při aktualizaci záznamů. Jde o to zkonstruovat jeden příkaz UPDATE, který aktualizuje všechny záznamy najednou. K tomu můžeme využít konstrukci CASE a pomocnou funkci, která nám z asociativního pole vytvoří vnitřek této konstrukce.

<?php
$ceny = array(); // $id => $cena
// zde bude naplnění pole
mysql_query("
    UPDATE produkty
    SET cena = CASE id" . make_when($ceny) . " END
    WHERE id IN (" . implode(", ", array_keys($ceny)) . ")
");
?>

Rychlost jsem změřil u InnoDB tabulek ve srovnání s případem, kdy se pro aktualizaci záznamů použije více příkazů uzavřených do transakce. Čas potřebný k aktualizaci stejného počtu záznamů byl zhruba poloviční. Bez využití transakce u jednotlivých aktualizací byl čas asi stonásobný. Srovnání si ale raději proveďte na vlastních datech.

Tento způsob aktualizace je rozumně použitelný při změně jediného sloupce, s více sloupci by bylo sestavení dotazu značně krkolomné. Stejně jako u vícehodnotového příkazu INSERT je potřeba dát také pozor na velikost proměnné max_allowed_packet (obvykle 1 MB).

Pokud máme jistotu, že záznamy existují, můžeme použít příkaz INSERT s klauzulí ON DUPLICATE KEY UPDATE a využitím funkce VALUES():

<?php
$values = array();
foreach ($ceny as $id => $cena) {
  $values[] = "($id, $cena)";
}
mysql_query("
    INSERT INTO produkty (id, cena) VALUES " . implode(", ", $values) . "
    ON DUPLICATE KEY UPDATE cena = VALUES(cena)
");
?>

Přijďte si o tomto tématu popovídat na školení Konfigurace a výkonnost MySQL.

Jakub Vrána, Dobře míněné rady, 25.11.2009, diskuse: 6 (nové: 0)

Diskuse

fos4:

Pri provadeni velke transakce by rychlost mela ovlivnit i velikost bufferu. Co kdyz se vsechny rollbacky nevlezou do logu. Nepletu se ?

krteQ:

to by musela byt opravdu hodne velka transakce treba obsahujici BLOB atd. Se zakladnim max_allowed_packet nemusis tohle vubec resit.

mcmatak:

Tohle jeslti se nemýlím už jsme spolu jednou řešili v diskusi, a opravdu ještě výhodnější se zdá, je použít mysqli rozšíření a multi_query.

http://www.webfaq.cz/clanek/Jeden-dotaz-do-…-je-to-stejne

ikona Pavel Stehule:

Zdar,

tady někdo objevil Ameriku :).

Je jasné, že hromadný INSERT je rychlejší - InnoDB pracuje s menším počtem transakcí - případně s menším počtem subtransakcí. Samotný INSERT by neměl mít tak velkou režii - v porovnání s uzavřením - potvrzením transakce.

V případě UPDATE je to totéž - navíc hodně záleží jestli se Vám chytil nebo nechytil index (pokud některý existuje). Je rozdíl jestli se volá 1x seq scan (i kdyby se aktualizovalo 40 řádků) nebo 40x seq scan. U index scanu to nemusí být tak zásadní - nicméně, v provozu může být index během opakovaní příkazů vytlačen z cache, takže se to také může projevit.

+ je tu docela velká režie na spuštění SQL příkazu z klienta - pokud se nejede v asynchronním režimu, tak se klient může i docela načekat - pošle se žádost, a čeká se na odpověď, přijme se odpověď.

Kajman_:

U více sloupcového updatu by se možná mohlo vyplatit sestavit něco takového...

update `produkty` join
       (select 1 id, 1000 cena, 1 sleva from dual
        union
        select 2 id, 2000 cena, 2 sleva from dual
        union
        select 3 id, 3000 cena, 3 sleva from dual
        union
        select 4 id, 4000 cena, 4 sleva from dual) dbtmp on `produkty`.id = dbtmp.id
set     `produkty`.cena = dbtmp.cena, `produkty`.sleva = dbtmp.sleva

Kajman:

Místo case jde v mysql využít i funkce elt a field, tedy vygenerovat něco jako

UPDATE produkty p
SET    p.cena = Elt(Field(p.id, 1, 2), 50.3, 1897)
WHERE  p.id IN ( 1, 2 )

Diskuse je zrušena z důvodu spamu.

avatar © 2005-2024 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.