Optimalizacia a performance SQL dotazov

[xXx]

Ahojte, viete mi poradit nejaky dobry zdroj informacii pre tuto temu? Rad by som sa dozvedel aky je plan vykonania dotazu, ake klauzuly a kedy pouzit pre co najefektivnejsi dotaz, ako najefektivnejsie indexovat tabulky, atd?

Vacsina z vas to asi brala na VS, mna to obyslo pretoze som skocil do prace hned po strednej, a na vacsine projektov sme si query vyskladali pomocou nejakeho ORM, tak som to nikdy riesit ani nemusel.

Dakujem za tipy

[Reklama]

[blb]

High Performance Mysql a Mysql Internals od O'Reilly media, i posledni revize jsou dnes uz trochu starsi, ale pro zakladni prehled jsou porad nejucelenejsi zdroj.

[aaa158]

https://www.google.com/search?q=postgresql+explain+analyze+tutorial

[oss]

MySQL sa optimalizije prechodom na nieco ine.

[Trollopata]

Stojí za to si zakoupit:
https://sql-performance-explained.com/

[Reklama]

[x]

Mozes byt kludny, na ziadnej VS sa ladit DB nenaucis. Mimochodom je lahsie/casovo kratsie sa naucit riadne pisat optimalne SQL ako naucit sa troubleshooting, pretoze to prve je pre to druhe prerekvizitou.
Tvoja otazka vsak obsahuje vela nepodstatnych informacii, ale tie zasadne si neuviedol vobec, napr. Vendor a verzia DB.

[dannak]

Mozes byt kludny, na ziadnej VS sa ladit DB nenaucis. Mimochodom je lahsie/casovo kratsie sa naucit riadne pisat optimalne SQL ako naucit sa troubleshooting, pretoze to prve je pre to druhe prerekvizitou.
Tvoja otazka vsak obsahuje vela nepodstatnych informacii, ale tie zasadne si neuviedol vobec, napr. Vendor a verzia DB.

Přesně tak, je kriticky nutné si uvědomit, že ač některé teoretické koncepty platí vždy a všude, implementace může a pravděpodobně bude v různých DB jiná a z toho plynou jiná pravidla. Co může být optimální v MySQL nemusí být optimální v Oracle, a co může být OK v Oracle nemusí jiná DB vůbec umět/podporovat.
Dál je potřeba znát a chápat zátěž dané DB - je to spíš transakční DB, datawarehouse nebo reportovací/analytická DB ? To jsou pak dost jiné světy s trochu jinými pravidly. Nic z toho však nebylo definováno, takže se radí dost obtížně.

[Kamil Podlešák]

https://use-the-index-luke.com

[xXx]

Vdaka, pozriem materialy co ste sem postli

Mozes byt kludny, na ziadnej VS sa ladit DB nenaucis. Mimochodom je lahsie/casovo kratsie sa naucit riadne pisat optimalne SQL ako naucit sa troubleshooting, pretoze to prve je pre to druhe prerekvizitou.
Tvoja otazka vsak obsahuje vela nepodstatnych informacii, ale tie zasadne si neuviedol vobec, napr. Vendor a verzia DB.

pracujem primarne s postgresom, ale tak mal som namysli skor efektivne pisanie sql dotazov pomocou zakladnych klauzul, ako nejake speci prvky niektorej z db

[Pavel Stěhule]

Vdaka, pozriem materialy co ste sem postli

Mozes byt kludny, na ziadnej VS sa ladit DB nenaucis. Mimochodom je lahsie/casovo kratsie sa naucit riadne pisat optimalne SQL ako naucit sa troubleshooting, pretoze to prve je pre to druhe prerekvizitou.
Tvoja otazka vsak obsahuje vela nepodstatnych informacii, ale tie zasadne si neuviedol vobec, napr. Vendor a verzia DB.

pracujem primarne s postgresom, ale tak mal som namysli skor efektivne pisanie sql dotazov pomocou zakladnych klauzul, ako nejake speci prvky niektorej z db

Tam moc prostoru pro nějaké triky není - každá klauzule má přesně svůj význam - a stačí ji používat podle významu. Je zásadní si uvědomit, že pracujete s relační databází a používat ji jako relační databázi (to se týká hlavně návrhu schématu). Je dobré si něco přečíst o SQL antipatternech (pokusy o dědičnost, EAV) a nepoužívat je. Zbytečně nepoužívat korelované poddotazy (tam kde je databáze neumí převést na joiny), preferovat joiny.

Cílem je napsat čitelné SQL u kterého je zřetelné, co dělá, a které je na dané platformě dostatečně rychlé. Výkonnostní problémy s databázemi většinou nejsou o tom, jak jsou napsaná SQLka (což neznamená, že se SQL nedá napsat blbě, když se chce), ale jak se databáze jako celek používá. Pro Postgres jsem před lety napsal článek, který pořád platí - https://postgres.cz/wiki/Desatero

[x]

Nemozem s Vami uplne suhlasit pan Stehule, nakolko drviva vacsina problemov s "vykonom DB" je spojena s neoptimalne napisanymi SQL (aj vzhladom na vyber optimalneho planu), nespravnym indexovanim alebo neoptimalneho vyuzitia indexov. Samozrejme vela do toho hovori aj navrh datoveho modelu, od ktoreho sa to prve moze odvijat. Suhlasim s clankom, ktory Ste davnejsie napisal, ale "ladenie", na urovni disku, cpu, parametrickom nastaveni DB alebo aj networku tvori v sucte mozno 30% celku, 70% je to na urovni aplikacnej (design, SQL). To, ze aplikacia zle skaluje hadam nechceme zvalit vinu na (napr.) cpu alebo diskovy subsystem...(?)
Samozrejme pri pouziti ORM toho vela  clovek nevyriesi a v tomto bode suhlasim s Vami, ale v ostatnom. Samozrejme jeden comment v diskusnom fore z daleka nedokaze vyjadrit celu problematiku, ale
Vas comment by bol uplne pravdivy pri podmienkach, ak 1. Datovy model aplikacie je navrhnuty optimalne vzhladom na uspokojenie potrieb a vykon. 2. Optimalne statistiky 3. SQL su napisane adekvatne (vzdy je vybrany optimalny ex. plan samozrejme (aj) na zaklade bodov 1. a 2.).
Vas comment znie prilis genericky a zial (IMO) i nie prilis stastnym sposobom  intepretovany...

[x]

Aby som bol uplny a predisiel zbytocnemu neporozumeniu, toto je ta veta, s ktorou nemozem principialne suhlasit (s p. Stehulem).

Výkonnostní problémy s databázemi většinou nejsou o tom, jak jsou napsaná SQLka (což neznamená, že se SQL nedá napsat blbě, když se chce)

[Pavel Stěhule]

Aby som bol uplny a predisiel zbytocnemu neporozumeniu, toto je ta veta, s ktorou nemozem principialne suhlasit (s p. Stehulem).

Výkonnostní problémy s databázemi většinou nejsou o tom, jak jsou napsaná SQLka (což neznamená, že se SQL nedá napsat blbě, když se chce)

Samozřejmě, že každý můžeme mít vlastní názor - a vlastní zkušenosti .

Ono také extrémně záleží na kvalitě optimalizace té či oné databáze. S "ideálním" optimalizátorem by mělo být úplně jedno, jak dotaz napíšete. Ideální optimalizátor nemáme, ale zas pokud se trefí predikce, tak jsou dnešní optimalizátory poměrně slušné. Nesmíte brát v potaz starší verze MySQL - fakticky až do 8čky MySQL optimalizátor za moc nestojí.

Další věcí jsou mýty z dob, kdy optimalizátory byly rule based. Tam skutečně záleželo, jak se dotaz zapíše. Pokud ale vím, tak dnes se takový nikde masověji nepoužívá.

[x]

Aby som bol uplny a predisiel zbytocnemu neporozumeniu, toto je ta veta, s ktorou nemozem principialne suhlasit (s p. Stehulem).

Výkonnostní problémy s databázemi většinou nejsou o tom, jak jsou napsaná SQLka (což neznamená, že se SQL nedá napsat blbě, když se chce)

Samozřejmě, že každý můžeme mít vlastní názor - a vlastní zkušenosti .

Ono také extrémně záleží na kvalitě optimalizace té či oné databáze. S "ideálním" optimalizátorem by mělo být úplně jedno, jak dotaz napíšete. Ideální optimalizátor nemáme, ale zas pokud se trefí predikce, tak jsou dnešní optimalizátory poměrně slušné. Nesmíte brát v potaz starší verze MySQL - fakticky až do 8čky MySQL optimalizátor za moc nestojí.

Další věcí jsou mýty z dob, kdy optimalizátory byly rule based. Tam skutečně záleželo, jak se dotaz zapíše. Pokud ale vím, tak dnes se takový nikde masověji nepoužívá.

V prvom rade, neznasam myty a tie v IT (kde prakticky vsetko je trasovatelne a meratelne) nemaju co hladat. Napriek tomu je ich v tomto odvetvi mnoho. Napriklad jeden z najstarsich, ze je potrebne indexovat vseko to com mam v predikatoch alebo ze full table scan je zly...

Ako Ste spravne poznamenali "idealny optimalizator" neexistuje. Co sa tyka RBO suhlasim, nemal by sa pouzivat, ale zial velakrat je mozne vidiet ako je (napr. v Oracle) pouzity +RULE hint (napriek milionu upozorneni o supporte) a paradoxne v niektorych pripadoch ho interne (vramci data dictionary) vyuziva spominany Oracle sam :-). Iste kvalita algoritmov CB optimalizatorov sa vyvija milovymi krokmi dopredu, ale stale nie su idealne a este dlho potrva, kym sa o tuto temu nebudeme musiet v zasade zaujimat.
Samozrejme nemozme pominut fakt, ze je CB optimizer sa na zaklade "niecoho" rozhoduje. A jedno z toho "nieco" je zial aj samotne query. Napriek rozdielnosti skusenosti (ako ste sam spomenuli) nikdy sa Vam nestalo, ze problem bol za danych okolnosti riesitelne iba prepisom query? Alebo sa Vam niekdy nestalo, ze vyvojar napisal query, kde dany zapis/logika SQL query fungovala rychlo/OK do <10000 riadkov, ale pri milionoch zaznamov bol zrazu problem? Potom Ste naozaj stastny clovek.

K CBO, ked uz ste napisali, ze mame na dobrej urovni optimalizatory, opat dam priklad z Oracle (ked uz som s nim zacal pred tym priklad s nim). CBO prisiel vo verzii Oracle 7.x v 1998 (to sa cerstvo objavil PostreSQL 6.5 ak ma pamat neklame), ako tak OK, zacal byt CBO v vo verzii 10g R2, co hovorime cca o roku 2005, pricom sa zmenil zakladny costing model z IO na CPU (co znamenalo a aj rezultovalo do hodne problemov). V 11g prisiel adaptivny cursor sharing (co bol akysi fix pre CBO vyvedeny do feature, pretoze storovany/cachovany plan mohol byt suboptimalny pri rozdielnej distribucii hodnot v stlpci) a v 12c (R1) sa veci menili opat reps. priadali sa adaptivne plany (spolu s adaptivnymi statistikami), kde vybrany realny plan sa pocas svojho vykonavania moze zmenit. V 18c sa to cele este riadne vylepsilo. Napriek niekolkonasobnemu (a zasadnemu) zlepseniu algoritmov samotneho CBO u tohto vendora, po 18 rokoch pomerne dramatickeho vyvoja, stale potrebujete trasovat chovanie CBO aby ste si vyjasnili mnohe veci, pouzivat veci ako SPM alebo dokonca SQL profiles, precizne zbierat statistiky a histogramy a k tomu vsetkemu dokonca aj riadne pisat SQL query, dokonca ich rovno aj prepisat. Chcete snad podsunut myslienku, ze napr. Postgre, MySQL (, MariaDB, DB2 alebo MSSQL su niekde inde? Analogicky mi to pride obdobne ako ked L. Ellison vyhlasil, ze Oracle DB bude self-managed alebo self-tuned a stalo sa to uz 3x. 1. prave v roku ked prisla Oracle 7, 2. pri prichode 10g a 3. ked ohlasoval autonomous database (pri 18c engineered systeme). Hadajte co? Zatial vsetko ostalo pri starom a zial zla sprava pre vyvojarov je, ze stale je potrebne rozmyslat pri designe a aj pri pisani SQL a zial i pri tom ked sa snazime zistit preco to nejde tak ako by sme chceli alebo aky bol zamer.

Dalej beznemu designerovi/vyvojarovi obycajne nehovoria nic terminy ako kardinalita, selektivita, statistiky, histogramy atd., ale zial ani co je EXPLAIN alebo EXPLAIN ANALYZE (u Postgre) a aky je medzi nimi rozdiel.
Osobne mne by stacila aspon tak vedomost, ze co su bind variables a preco sa to bez mala 20 rokov stale dookola obmiela. Stacilo by mi svoj zapis query pisal/testoval na viac ako 10-timi vzorovymi zaznamami v tabulke/ach. Ze ked uz sa naozaj, ale naozaj nemozem vyhnut nad pouzitim funkcii nad stlpcami vo WHERE klauzule, tak ze existuje nieco ako FBI. Ze pouzitie monotonne rastucej hodnoty nemusi byt zrovna to prave orechove pre primarny kluc u vysokotranzakcnej tabulky v multisession prostredi. Ze nizky "Cost" nemusi byt vzdy  alfa a omega, ale ze mame tu nejaky Response time (Service+Wait time). A neposladnom rade spravne interpretovat vystup z explain...

[Pavel Stěhule]

Aby som bol uplny a predisiel zbytocnemu neporozumeniu, toto je ta veta, s ktorou nemozem principialne suhlasit (s p. Stehulem).

Výkonnostní problémy s databázemi většinou nejsou o tom, jak jsou napsaná SQLka (což neznamená, že se SQL nedá napsat blbě, když se chce)

Samozřejmě, že každý můžeme mít vlastní názor - a vlastní zkušenosti .

Ono také extrémně záleží na kvalitě optimalizace té či oné databáze. S "ideálním" optimalizátorem by mělo být úplně jedno, jak dotaz napíšete. Ideální optimalizátor nemáme, ale zas pokud se trefí predikce, tak jsou dnešní optimalizátory poměrně slušné. Nesmíte brát v potaz starší verze MySQL - fakticky až do 8čky MySQL optimalizátor za moc nestojí.

Další věcí jsou mýty z dob, kdy optimalizátory byly rule based. Tam skutečně záleželo, jak se dotaz zapíše. Pokud ale vím, tak dnes se takový nikde masověji nepoužívá.

V prvom rade, neznasam myty a tie v IT (kde prakticky vsetko je trasovatelne a meratelne) nemaju co hladat. Napriek tomu je ich v tomto odvetvi mnoho. Napriklad jeden z najstarsich, ze je potrebne indexovat vseko to com mam v predikatoch alebo ze full table scan je zly...
...

Já tu nevidím rozpor - přijde mi, že možná jinými slovy tvrdíte totéž co já. Nestačí jen umět psát dotazy, ale je potřeba umět správně pracovat s relační databází. Výkonnostní problémy Oracle a Postgresu jsou někdy hodně podobné - tam kde je podobná implementace. U Oracle mám ale pocit, že je jakoby úzus, že za ty peníze by to ta databáze měla dát sama a podceňuje se vývoj, a relativně hodně se klade důraz na administraci. U Postgresu se rovnou říká, že vývojář by měl být inteligentní, a pokud nemá základní znalosti, tak to nedopadne dobře. Také u Postgresu jsou spíš vyjímkou databázoví admini, a tudíž si vývojář musí většinou tuning udělat sám, případně si performance problémy vyžrat sám. Naopak u Oracle je častější striktní dělení mezi adminy a vývojáře - a také nasledné konflikty a osočování. U Oracle (a v korporátním prostředí) je vývojář dost často perfektně izolován od produkce, a vůbec netuší, jak co produkčně funguje nebo nefunguje.