Jeden dotaz do databáze nebo tisíce? ... je to stejné?

Na tento článek mne přivedla diskuse k jednomu z předchozích článků. Popravdě vůbec nevím co z něho vyjde a také očekávám, že mé fámy budou vyvráceny. Alespoň po těch rocích programování konečně získám správné techniky a třeba se tady něco přiučíte, tak jako já.


Jak jsem řekl, k tomuto článku mne přivedl jeden diskutující p. Miloslav Ponkrác, autor knihy o mysql, doufám, že mu jeho jmenování nebude vadit (sám se v diskusních příspěvcích podepisuje), pokud ano, rád jeho jméno odstraním.

Tedy zdroj problému najdete zde INSERT INTO ... ON DUPLICATE UPDATE, ale co když nechceme, aby se nový záznam vytvořil, ale jen upravil existující?, stručně se pokusím popsat fámu, které věřím: Dle mého je lepší položit jeden dotaz do databáze než tisíce dotazů. Už nevím, kde jsem k tomuto tvrzení přišel, ale věřím tomu už pár let. Myslím, že někdy před 5 lety jsem si udělal test a porovnal výkonnost databáze. Z toho jsem došel k závěru, že čím méně dotazů, tím lépe a tím se řídím.

Kdyby nic jiného z tohoto článku nevzniklo, tak snad i jiní co trpí podobnou vadou zde najdou rozhřešení.

Udělal jsem si malý test, ale upozorňuji, že je šitý horkou jehlou a v dlouhodobé přepracovanosti, kdyby to byl úplný nesmysl upozorněte mne! Ať článek stáhnu dokud někomu nezpůsobí újmu.

  1. <?
  2. include("Debug.php");
  3. include("Timing.php");
  4. Debug::$logReport['timing']=1;
  5. $conn = mysql_pconnect("localhost", "root", "");
  6. if (!$conn) echo mysql_error($conn);
  7. $db = mysql_select_db("examples", $conn);
  8.  
  9. /**
  10. * 1. More query inserts
  11. **/
  12. Debug::go("More query inserts");
  13. $q="INSERT INTO example_update (t1,t2,t3,t4) VALUES ";
  14. for ($i=0;$i<100000;$i++) {
  15. mysql_query($q."(1,2,3,4)", $conn);
  16. }
  17. Debug::stop("More query inserts");
  18. echo Debug::showTiming();
  19. /**
  20. * 2. More inserts by one query
  21. **/
  22. Debug::go("More inserts by one query");
  23. $q="INSERT INTO example_update (t1,t2,t3,t4) VALUES ";
  24. $arr=array();
  25. for ($i=0;$i<100000;$i++) {
  26. $arr[]="(1,2,3,4)";
  27. }
  28. mysql_query($q.implode(",",$arr), $conn);
  29. Debug::stop("More inserts by one query");
  30. echo Debug::showTiming();
  31. /**
  32. * 3. More query updates
  33. **/
  34. Debug::go("More query updates");
  35. for ($i=0;$i<100000;$i++) {
  36. $q="UPDATE example_update SET t1=1, t2=2, t3=3, t4=4 WHERE id='".$i."' ";
  37. mysql_query($q, $conn);
  38. }
  39. Debug::stop("More query updates");
  40. echo Debug::showTiming();
  41. /**
  42. * 4. More updates by one query
  43. **/
  44. Debug::go("More updates by one query");
  45. $arr=array();
  46. for ($i=0;$i<100000;$i++) {
  47. $arr[]="($i,1,2,3,4)";
  48. }
  49. mysql_query("INSERT INTO example_update (id,t1,t2,t3,t4) VALUES ".implode(",",$arr)." ON DUPLICATE KEY UPDATE t1=VALUES(t1), t2=VALUES(t2), t3=VALUES(t3), t4=VALUES(t4);", $conn);
  50. Debug::stop("More updates by one query");
  51. echo Debug::showTiming();
  52.  
  53. Debug::printReport();
  54. ?>

Celý test se skládá ze čtyř částí, ještě před tím se připojím k databázi a naincluduji knihovny pro práci s časem apod.

Databáze

  1. CREATE TABLE `example_update` (
  2. `id` int(11) unsigned NOT NULL auto_increment,
  3. `t1` int(11) NOT NULL,
  4. `t2` int(11) NOT NULL,
  5. `t3` int(11) NOT NULL,
  6. `t4` int(11) NOT NULL,
  7. PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1. část - 100 000x insert

V této části se v cyklu pokouším do databáze vložit do každého řádku hodnoty 1,2,3,4.

Celý cyklus zabral 189 sekund, podotýkám je to mašina v kanceláři, žádný server.

2. část - 1x insert

Tato část provede to samé jako předchozí, ale použije pouze jednoho insertu.

Celý cyklus zabral 1.89 sekundy.

3. část - 100 000x update

Tady jsem se pokusil testovat update, 100 000x provedený update zabral pouhých 24 sekund.

4. část - 1x insert into ... on duplicate update

A tady je metoda, které věřím, provede to samé jako část 3., ale za 0.21 sekundy.

Nevím jestli mé závěry jsou správné nebo ne a rád se nechám poučit. Ještě dodám, že před pěti lety bylo mé měření rozsáhlejší a hlavně na reálném příkladě, rozdíly u jednotlivých přístupů se pohybovali v řádek desítek minut a to na celkem hodně výkoném serveru.

Co vy na to?

podobné články

08.12.2008INSERT INTO ... ON DUPLICATE UPDATE, ale co když nechceme, aby se nový záznam vytvořil, ale jen upravil existující?(100%)
24.02.2009MSSQL UPSERT, INSERT INTO ... ON DUPLICATE KEY UPDATE(14%)