Dotaz: PHP je lepší select z db pre všetky idečka alebo postupne načitavanie viacerymi selectmi?

[adrinko]

Ahojte, mám takú dilemu. Mám tabuľku užívateľov, ktorých je 3000. V crone spúšťam php skript, ktorý každý deň, načíta každého užívateľa a niečo vykoná. Moja otázka: Je lepšie pri spustení tohto php skriptu hneď načítať mysql selectom všetky polia všetkých užívateľov do premennej (poľa) a tie v cykle (napr. for) spracovať, alebo v cykle for radšej spraviť sólo select pre každé jedno ID užívateľa (t.j. vykonalo by sa cca 3000 selectov)?

Zaujíma ma, čo je lepšie, keďže každý užívateľ má v tabuľke cca 10 stĺpcov, s ktorými chcem ďalej pracovať (aby to náhodou nezahltilo systémové prostriedky pridelené phpčku). Vďaka!

---

---

Odpovědi

[David Watzke]

Pokud to půjde, určitě počet selectů minimalizuj. Když si to spočítáš, tak to nezabere zas tak moc paměti, pokud ty sloupce nejsou nějaký mamutí bloby.

[bazil]

Trošku teorie:
Většinou je lepší použít méně selektů než více. Pokud například pracuješ s velkými objemy dat a použiješ z nej jen pár záznamů, potom je ale lepší použít více selektů.

V tvém případě procházíš stejně všechny data a všechny je budeš zpracovávat, proto použij jeden selekt a pouze projekce (vybre jen ty sloupce, které potřebuješ).

Dále použij mysql_* pro procházení dat:

$res = mysql_query('select ...')
while(($row = mysql_fetch_array($res))
{

}

Tady je hezký příklad: http://www.php.net/manual/en/function.mysql-fetch-array.php

[Kit]

Určitě bude lepší provést jeden SELECT. Už proto, že kdyby během zpracování každého z těch 3000 mohlo docházet k modifikacím seznamu uživatelů, mohl by v datech vzniknout zmatek. Také by se mohlo případně stát, že by se to "niečo" provedlo jen pro některé uživatele a pro jiné ne.

Uvědom si, že každý SELECT se kompiluje do pseudokódu a teprve ten se provádí. Pokud se dá příkaz napsat jako jeden SELECT, je to k databázi mnohem šetrnější a výkonnější. Navíc celá akce proběhne atomicky.

Systémové prostředky by to zahltit nemělo, protože PHP si data z databáze odebírá postupně jak potřebuje. Obvykle bývá nastaven limit paměti pro proměnné v PHP na 32 MB, ale dá se zpracovat i select se stovkami MB.

Spíš bych se ale zamyslel nad tím: Co vlastně s těmi daty potom děláš? Neukládáš je po modifikaci opět do databáze? To bych v cyklu rozhodně nedělal, ale nechal bych to udělat přímo tu databázi.

[adrinko]

ďakujem Vám za objasnenie. Moje čítané dáta sú väčšinou len krátke textové veci, mená, emaily, čísla,dátumy,... Spravím to teda jedným selectom a jeho dáta postupne spracujem v php.

[Sten]

Asi nejlepší je provést méně selectů s předpřipraveným dotazem (Prepared Statement), kam se jenom doplňuje offset, ale při třech tisících řádků by bylo lepší, aby to nebyl jeden, ale aby to bylo třeba po stovce záznamů (pomocí limit a offset). Záleží také na použitém enginu, tohle platí pro InnoDB, kde jsou záznamy řazené podle ID, takže ten limit a offset jsou velmi rychlé, u MyISAM může být lepší udělat select jeden.

[l0gik]

A v čem je výhoda oproti jednomu dotazu?

[Šangala]

Ve spotřebě paměti?

[David Watzke]

U 3 tisíc uživatelů nelze mluvit o spotřebě paměti...

[l0gik]

Kdo říká, že musím celej výsledek načítat najednou? Budu ho normálně zpracovávat řádku po řádce. A pokud myslíš podmínku do databáze, tak ono php stejně při budování jednotlivejch subpodmínek s největší pravděpodobností ty starý řetěze jen tak neuvolní, takže taky bude spotřeba paměti podobná. V každym případě, kolik by jich muselo bejt, aby to bylo alespoň trochu strovnatelný s overheadem samotnýho PHP v řádech desítek mega? Tvejch 3 tisíce řádků je dotaz na nějakejch 15kB a výsledek ne o moc víc...

[Šangala]

Nejsou to moje řádky a ptal jsi se na rozdíl. Na 3000 řádků mně to přijde v tomto případě zbytečné, pokud to neběží na nějaké „maličkém“ systému (30k už to tak nemusí být).

dotaz na nějakejch 15kB a výsledek ne o moc víc... + len krátke textové veci, mená, emaily, čísla, dátumy

Může ve výsledku znamenat i více než 0.3 MiB.

Budu ho normálně zpracovávat řádku po řádce

Jestli myslíš fetch_xxx, tak to na věci nic nemění, že data jsou v result setu načtena všechna.

PHP-ko jej uvolní, jakmile je potřeba a k uvolnění externích zdrojů (result setu) slouží například mysqli_result::free či třeba mysqli_stmt::free_result
PS: Výše uvedeným způsobem jsem pomocí PHP s limitem 64MiB napravoval propojení dvou rozdílných databází na dvou rozdílných strojích, protože „Enterprise“ Java aplikace (díky chybnému návrhu) to nezvládla s přidělenými 3GiB.
A taky pokud máte limit 8-16MiB a potřebujete na základě selectu upravovat obrázky, tak je vhodné dobře počítat a uvolňovat paměť…, ale může to být cokoliv…

[l0gik]

Na to se dá odpovědět nejlépe odkazem: http://www.mysqlperformanceblog.com/2006/06/26/handling-of-big-parts-of-data/ aneb v mysql(i) se nemusí straně klienta cachovat celej dotaz, když nechceš, normálně to zpracuješ proudově.

Jinak PHP má jakejs takejs GC od verze 5.3 a ani tam nechodí moc dobře, takže na nějakou GC bych nespoléhal.

[Šangala]

Odkazovaný článek je sice hezký, ale například fce mysql_unbuffered_query() nedělá to, že by neukládala výsledek na straně klienta, ona ho jen nepřipravuje pro použití v PHP (nedělá z něj typy PHP) a umožňuje zpracování, hned jakmile už nějaká data dojdou. To znamená, že můžete získat rychlost, díky paralelnímu zpracování a ušetřit nějakou paměť díky „nepřekládání dat“, pokud budete dostatečně rychle zpracovávat (což ale v PHP asi těžko půjde předběhnout takový typ dotazu.), ale ty data na straně klienta uložena jsou, jen je hned odebíráte a nejsou tak velká (zvláště pokud je tam spousta smallint, tinyint apod.).

Na GC se spoléhat nemusíte, i když jsem s tím neměl nikdy žádné problémy a vždycky při nedostatku paměti, když jsem to měl dobře spočítané a případně jsem dobře unset-nul proměnné jsem byl schopen využívat ji celou opakovaně.
Jo když budete blbě používat(respektive pak již nepoužívat) nějaké grosse-big multi-array a více a vzájemně je budete provazovat, třeba odkazem, tak určite GC zamotáte hlavu.

A také je si třeba uvědomit, že fce jako mysqli_result::free(), mysqli_stmt::free_result() nebo i třeba imagedestroy() uvolňují prostě paměť ihned, řekněme mimo vyhledávací loop GC.

[l0gik]

IMHO prostě nemáš pravdu. Dokládá to jednoduchej test: tabulka invoices (18k řádků, 4,7MB dat). Jednoduchej program

//pripojení k db
echo memory_get_usage(),"\n";
//$q=mysql_query("SELECT * FROM invoices");
$q=mysql_unbuffered_query("SELECT * FROM invoices"); \\ LIMIT 4000");
sleep(2);
echo memory_get_usage(),"\n";
$s=0;
while($r=mysql_fetch_row($q))
    {
    $s=$s+$r[0];
    //echo memory_get_usage(),", ";
    }
echo memory_get_usage(),"\n";
echo memory_get_peak_usage(),"\n";
exit();

Výsledky s normálním dotazem:

685984
4825840
4825840
44789896
44796976

s buffered s limitem 4000

686000
1688464
1688464
11289208
11296256

výsledky s unbuffered query

685800
695904
695904
696632
716056

unbuffered s limitem 4000

686008
689696
689696
690256
715968

unbuffered s limitem 20

685816
695920
695920
696648
715216

Myslím, že tato data Tvoje tvrzení prostě vyvrací: spotřeba paměti je při unbuffered query defakto identická při 20 i 18000 záznamech.

Navíc, kdyby to bylo tak, jak tvrdíš, tak by se data během sleepu načetly a spotřebovali paměť, tam ale k žádnému nárůstu paměti nedochází, k tomu dochází až u buffered query během cyklu. Proto je evidentní, že ani buffered query netahá všechna data hned, pouze je na straně klienta cachuje v okamžiku, kdy jsou poprve přečtena, aby umožnil pohyb zpět po resultsetu (samozřejmě tahá data po "paketech", proto je spotřeba paměti během cyklu vždy pár kroku stejná a pak skočí).

[Šangala]

A zas podle mého názoru nemám pravdu.
Navíc když se zobrazí paměťová náročnost scriptu a db jsou rozdíly patrnější než s fce memory_get_usage() a nebo se použije v PHP něco jako:

$pid = getmypid();
exec("ps -eorss,pid | grep $pid", $output);
echo IntVal(trim($output[0])) * 1024,"\n";

Takže je blbost co jsem psal v 1. odstavci a o čem jsem byl přesvědčen (nevím proč) a musel jsem se podívat i do zdrojáků PHP-ka a to i do starších verzí (4.x), ale nenašel jsem nic co by odpovídalo tomu co jsem psal, PHP funkce mysql_unbuffered_query() prostě a jednoduše odpovídá C API mysql_use_result().

Takže dík za námahu s vyvrácením 1. odstavce (třetí odstavec se mimoděk podobným testem potvrdil ).
Zdrojový kód je zbytečný, hodnota je absolutní využití paměti přes funkci podobnou výše uvedené.

Conected and selected DB
  Exec ps:  6680576 
Start mysql_query()
Selected
  Exec ps: 41750528
Selected after sleep(2)
  Exec ps: 41750528
Fetched all rows, php sum(id): 16200090000
  Exec ps: 41750528
After mysql_free_result
  Exec ps:  6680576 
After mysql_close
  Exec ps:  6680576 

U mysql_buffered_query() to nabýšení na 40MiB nebylo.
Sledováním přes top se sice projevilo drobné navýšení na straně mysql, ale v zásadě nepodstatné.

[Sten]

Ve spotřebě paměti (i na straně MySQL) a v tom, že takový dotaz běží velmi dlouho a blokuje systémové prostředky. A navíc může vytimeoutovat.

[Kit]

Spotřeba paměti? Velmi dlouho? Blokování systémových prostředků? Jaký timeout? Na 3000 záznamech o celkové velikosti 15 KB? O jakém systému se bavíme? Na čem to běží? Na 8088? I na tom by to běželo svižně.

Myslím si, že select záznamů po skupinách je v daném případě docela overkill. Jeden select bude bohatě vyhovovat pro většinu běžných aplikací.

Bohužel jsme se nedozvěděli, co s tím získaným seznamem chce tazatel dělat. Pokud jenom chce vygenerovat tabulku v HTML, tak nemusí výstup ani zpracovávat cyklem, ale celou tabulku mu databáze může vypsat jako jediný řetězec, který PHP jen pošle na výstup.

To ale nevíme a místo toho diskutujeme o variantách, o kterých má obvykle význam uvažovat až při práci s tabulkami o miliónech záznamů. Prostě overkill.

[l0gik]

No generovat tabulku v databázi bych fakt nedooporučoval. Logika v databázi, to ano, ale view? To se bude tak obtížně spravovat, že fuj.

Ale jinak souhlas, v takovém množství je to overkill, smysl to má uvažovat pouze v případě, kdy by velikost dotazu narostla do nepatřičných mezí. Pak ale je zas na čase přesmýšlet, jestli není chyba v návrhu, málokdy je opravdu potřeba najednou upravovat statisíce záznamů.

[Kit]

No generovat tabulku v databázi bych fakt nedooporučoval. Logika v databázi, to ano, ale view? To se bude tak obtížně spravovat, že fuj.

Zrovna jsem to otestoval v SQLite na 300 000 záznamech. Vygenerování celé tabulky jedním selectem do jednoho řetězce bylo asi 3x rychlejší, než klasické procházení výsledků selectu cyklem.

Možná se to obtížněji spravuje, ale databáze udělá view rychleji než PHP.

[l4m4]

s/databáze/cokoli/

s/view/cokoli/

[Kit]

Že je PHP líné, to všichni víme. Proto vždycky hledám jakoukoli možnost, jak se v něm vyhnout cyklům.

Pro šťouraly: Cykly uvnitř vestavěných funkcí jsou zpravidla dobře optimalizovány, proto mě netrápí.

[l0gik]

- železo je daleko levnější než programátor. Takže pokud nejde o mission critical aplikaci, tak nějaký 3x rychlejší opravdu nehraje roli. A pokud je to mission critical, tak to nebudu psát v php.

- Jak často potřebuješ plivat tabulku s 300000 záznamy?

- Přiznám se, že ty výsledky dost podezírám: v php můžu řetězce postupně načítat a plivat, zatímco když to čteš z databáze, tak musíš najednou v paměti držet celej ten řetězec. Jak má mysql udělanou práci s řetězci se přiznam, že nevim, ale např. v postgresu je skládání řetězců zrovna výkonnostně nicmoc. A naopak - pokud už budu mít v PHP celé pole, tak zas můžu použít např. implode nebo jinou rychlou interní funkci. Dej sem schválně kód, kteryms to dělal (jak v databázi tak v php), schválně, jestli to dokážu napsat rychlejc...

[Kit]

- ten SQL dotaz je zvětšený jen o jednu funkci. Programátorovi by mělo být jedno, jestli agregační funkci napíše v SQL nebo PHP. Většinou použije to, co umí lépe nebo to co se mu jeví lepší.

- 300000 záznamů jsem zvolil proto, abych získal nějak měřitelná data. Pro 3000 záznamů bych toho moc nenaměřil. Ještě bych mohl zkusit 3000 záznamů stovkou paralelních procesů, což by mohlo být blíž realitě (webové stránky).

- zkusil jsem i postupné načítání a výstup. Výsledky byly ještě horší, protože příkaz echo je líný. Mnohem rychlejší bylo výsledky naskládat do pole a pomocí funkce implode() je spojit a teprve takto vzniklý řetězec poslat na výstup. Agregace v SQLite však byla 3x rychlejší. Připadalo mi férové použít v obou případech echo jednoho řetězce, jinak by cyklus dopadl ještě hůř.

Klasika (14 sekund):

foreach($dbh->query("SELECT value as val FROM pokus;") as $row) {
  echo 'trtd'.$row['val'].'/td/tr'."\n";
}

Skládání řádek v SQL (12 sekund):

foreach($dbh->query("SELECT 'trtd'||value||'/td/tr\n' as val FROM pokus;") as $row) {
  echo $row['val'];
}

Použití pole (9 sekund):

$out=array();
foreach($dbh->query("SELECT value as val FROM pokus;") as $row) {
  $out[]='trtd'.$row['val'].'/td/tr';
}
echo implode("\n",$out);

Použití řetězce (9 sekund):

$out='';
foreach($dbh->query("SELECT value as val FROM pokus;") as $row) {
  $out.='trtd'.$row['val'].'/td/tr'."\n";
}
echo $out;

Skládání řádek, kombinace s polem (9 sekund):

$out=array();
foreach($dbh->query("SELECT 'trtd'||value||'/td/tr' as val FROM pokus;") as $row) {
  $out[]=$row['val'];
}
echo implode("\n",$out);

Agregace v SQL (5 sekund):

foreach($dbh->query("SELECT group_concat('trtd'||value||'/td/tr','\n') as val FROM pokus;") as $row) {
  echo $row['val']."\n";
}

Agregace v SQL bez výstupu echo (2 sekundy):

foreach($dbh->query("SELECT group_concat('trtd'||value||'/td/tr','\n') as val FROM pokus;") as $row) {
  $line=$row['val']."\n";
}

To poslední měření jsem uvedl proto, aby bylo vidět, jak je echo líné.

Znaky tr a td znamenají <tr> a <td>, ale nechtělo se mi to nahrazovat entitami v celém příspěvku. Kdo chce, domyslí si.

[l0gik]

Skládání s polem máš naprosto zbytečně v cyklu, do implode můžeš hodit už ten query. Ale to je jedno, máš pravdu, opravdu je PHP tak zoufalý, že to je pomalejší.

Zapoměl jsi teda na podle mě nejrychlejší variantu: klasický echo se zapnutym output buffering - ale to tolik nezrychlí. Ale jinak máš pravdu.

Jen mi ten rozdíl v rychlosti nepřipadá tak velkej, by to bylo třeba řešit na úkor údržby kódu. Ale hodí se to vědět.

[Kit]

Skládání s polem máš naprosto zbytečně v cyklu, do implode můžeš hodit už ten query.

Asi mi něco uniká. Myslíš něco takového?

$result=$dbh->query("SELECT 'trtd'||value||'/td/tr' as val FROM pokus;",PDO::FETCH_COLUMN));
echo implode("\n",$result->fetchAll());

Zkusím to odladit, až budu na původním počítači. V této podobě by to mohlo být i rychlé.

Ohledně údržby kódu: Někteří vývojáři tvrdí, že SQL umí všechno. Dá i nedá se s tím souhlasit, někdy je to za cenu velkých obstrukcí. Souhlasím, že je dobré dělat aplikace jako vícevrstvé, kód se tím často zpřehlední.

V uvedeném případě mi však připadá přidání jedné funkce do SQL a ubrání z PHP jako jednoduchá úprava. Vychází mi však efektivnější. Můžu ještě zkusit porovnat i paměťové nároky jednotlivých řešení.

[l0gik]

No, já jsem myslel toto: implode($dbh->query("SELECT 'trtd'||value||'/td/tr' as val FROM pokus;",PDO::FETCH_COLUMN, "\n") ale to se omlouvám, zas jsem zapoměl na "debilitu" phpka, kde sice něco se tváří jako pole/iterátor, ale to ještě neznamená, že se to dá jako pole nebo iterátor používat, takže todle máš pravdu, to nejde. Zkusit to s FetchAll je zajímavý, jestli je to fetchAll tedy napsané v něčem rozumějším než PHP, jinak to bude stejné.

V tý udržovatelnosti IMHO nejde až tak o to, že se to udělá jednou, ale o to, že např. za tejden přijde šéf, že chce, aby si mohl člověk tu tabulky "skinovat". V tu chvíli začne db řešení dělat problémy, protože tam se takový věci implantujou daleko hůř.

[Kit]

Tak po drobných úpravách to funguje v této podobě (7 sekund):

$result=$dbh->query("SELECT 'trtd'||value||'/td/tr' as val FROM pokus;",PDO::FETCH_COLUMN,0);
echo implode("\n",$result->fetchAll());

Je to asi nejrychlejší řešení z těch, které dělají výstupní agregaci mimo SQL. Zároveň nevypadá příliš složitě, mělo by být obecně použitelné.

Předpokládám, že metoda fetchAll() je napsána v C nebo něčem podobném. Raději použiji takovou funkci než abych iteroval ve skriptu. Beru to od tebe jako dobrý nápad na rozumný kompromis. A že to má 2 řádky místo zamýšlené jedné? To mě vůbec netrápí.

Zpracování skriptu v polovičním čase pro mne znamená zpracování dvojnásobného počtu požadavků klientů nebo pronajaté jedno procesorové jádro místo dvou. V případě některých aplikací to může být docela důležité. Jak už bylo řečeno, cokoliv je rychlejší než PHP. Tím cokoliv můžou být nejen databáze, ale i vestavěné funkce PHP, které obvykle v PHP napsány nejsou.

[Josef Kufner]

Chybí ti tam escapování HTML znaků. Tedy pokud to nejsou jen čísla.

[l0gik]

Jo, jo, to jsou přesně ty detaily, které nakonec to řešení v databází zabíjejí I když samozřejmě v rozumný databázi jde nadefinovat UDF funkce

Jinak já v podstatě souhlasím. PHP je pomalá mrcha a samozřejmě užívat vestvavěné fce je veskrze doporučeníhodné. Jen ale, že to má i druhou stránku a že je poměrně tenká hranice mezi neoptimalizovaným a přeoptimalizovaným kódem...

[Šangala]

Jo, lze to udělat na úrovni SQL tak, že to vyplivne až celou tabulku nebo dokonce HTML stránku, ale otázka je: „¿Která vrstva má být k čemu kompetentní, kolik lidí to dělá a kolik lidí bude udržovat kód a s jakou zodpovědností?“
A když začnete honit milisekundy stejně to celé přepíšete do c(++)-éčka, ehm ano - někdo do Javy.

[Kit]

Správně. Vím o tom, už jsem to kdysi řešil. Nechtěl jsem ty příklady zbytečně komplikovat, ale jde to. V některých případech je však vhodné ukládat data do DB už escapovaná, při generování výstupu je pak menší režie.

[Josef Kufner]

Btw, echo bere libovolný počet parametrů. Takže není třeba spojovat stringy tečkou, což žere celkem dost (měřitelně) výkonu.