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.
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
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ěď.
Pavel Stehule: 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 jakoUPDATE 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.
© 2005-2024 