MySQL - nástroj na test návrhu databázy
Už dlhšiu dobu rozmýšľam, či neexistuje niečo také, čo by sa pripojilo na databázový server, ja by som zadal SQL príkaz a ono by mi vypísalo, že kde mi chýba vhodný index, kde sú veci navyše atď. Stačí mi len táto jedna funkcia. Existuje niečo také? Ideálne by bolo, keby je to freeware.
to se bojím, že je trochu nesmysl. Každý SQL dotaz vytěžuje databázi trochu jinak, a tedy z jednoho dotazu nic nevydedukuješ, protože nemáš jeho statistickou četnost.
Obecně lze říci, že pole podle kterých budeš často vyhledávat (to hlavně) a řadit by měla být indexována. Nezapomeň, že každé oindexované pole zpomaluje přidávání a updaty tabulky databáze, ale zrychluje prohledávání. To je podstata indexování.
Z výše uvedeného vyplývá, že indexovat bys měl ta pole, podle kterých se velmi často vyhledává (např. ve velké tabulce uživatelů to bude bezesporu kromě primárního klíče i příjmení) - to vše ale vždy při znalosti všech tvým systémem běžně používaných SQL dotazů, včetně jejich četnosti (např. vyhledávání uživatele probíhá téměř s každou operací přidání události do systému, ale vyhledávání data narození v průměru jen jednou týdně)
Niekde som čítal článok, kde autor vytvoril jednoduchú tabuľku a do konzoly zadával SQL príkazy. Mal povedzme vytvorení index podľa dátumu vytvorenia článku a index podľa autora článku. Zobraziť dal všetko s tridením podľa dátumu a autora a index nebol použitý, pretože nemal index podľa dátumu a autora súčasne. Pri návrhu databázy sa môže stať práve takýto detail a tomu sa chcem vyhnúť. Žiaľ už neviem, kde som to čítal, ale viem len to, že príkazy zadával do konzoly a niečo mu vyhodnocovalo, či sa použije index.
Robím v súčastnosti na projekte, ktorý bude raz za čas vykonávať dosť náročnú operáciu, ktorá bude pozostávať z niekoľkých krokov a chcel by som to nejak časovo optimalizovať. Teraz to časovo náročné nebude, ale v budúcnosti určite áno. Bude tam aj dosť prepojených tabuliek a update s využitím join, takže taký nástroj by na to padol vhod.
Samozrejme indexy používam, ale chcel by som to ešte vyladiť hlavne pre tú časovo náročnú operáciu. Jedným slovom povedané, bude to niečo ako uzávierka a dát tam bude dosť veľa.
Na to nepotřebuješ žádný speciální nástroj, to je přímo součástí databázového serveru.
Execution Plan
Ale i tak ti to řekne pouze jestli byl či nebyl použitý index. Neřekne ti to ale, jestli by ten index bylo vhodné mít nebo ne.
tak základ je, že oindexuješ všechna propojená pole (primární klíč je jasný, ale jedná se o pravé strany). Spíše si pohlídej ty updaty, aby proběhly nějak rozumně, ale to je téma na dobře postavený update, nikoli na indexy.
Pokud to s indexy přeženeš, tak zase zpomalíš veškeré insert/update/delete operace.
Ladění databáze je trochu alchymie. Musíš vědět, jak se chová databázový stroj, jak používá indexy - tohle je základ.
Pak musíš navrhnout indexy podle použití - snažit se co nejoptimálněji.
A za nějakou dobu se k tomu vrátit a zkontrolovat, protože databáze se chová jinak, pokud je v tabulce pár vět a když jsou tam statisíce nebo miliony vět - mění se statistické rozložení dat. A některý index pak třeba vůbec nemá smysl - např. na sloupci, který nabývá 3 hodnot a v tabulce je velké množství dat.
no ale to jsem psal už nahoře, že to je jasný tradeoff a že to není nic na "automatizovaný program"..
Vím kam míříš, ale 3hodnoty ve velkém množství dat ještě rozhodně neznamená, že tam neni index opravněně. Stále to může být legální použítí, stále ušetření zdrojů a využívání takového indexu.
Legalni pouziti, ktere mne napada je referencni integrita.
Jinak toho takovym indexem moc neusetris. Cilem pouziti indexu je eliminovat co nejvic vet, ktere te nezajimaji.
Pokud mam dotaz, ktery pouztim jednou za pul roku, muze si uzivatel 2 minuty pockat. Pokud je to dotaz, ktery se pouzti nekolikrat denne, pak se musi optimalizovat, protoze jeho caste pouzivani je schopne ti zastavit databazi.
A pak uz je to skutecne prace pro profiller, execution plan a podobne nastroje - zjistit, co tam trva dlouho. Nekdy pomuze zmena indexu, jindy pomuze prepsani dotazu tak, aby se indexy vyuzily lepe jindy zase prepsani s pomoci docasnych tabulek, kdy si napred vyberu zakladni mnozinu dat jednoduchym dotazem a az pak k takovemu vysledku lepim 20 joinu z ostatnich tabulek.
Optimalizatory dotazu se nekdy chovaji divne a delaji uplne neco jineho, nez bys ocekaval. Nekdy staci pridat jeden join a dotaz se zpomali 100x, prestoze tam je referencni integrita.
Mne jich napadá mnohem víc, ale jednoznačně je to covering index - select count(*) from marta where azor is not null* (podobně select azor, sysdate, 'Haf' from marta where azor is not null*) - pokud mam na azorovi index, tak tenhle sql přes něj může jít (respektive pouze do něj), což bude patrně rychlejší než aby ten count udělal přes full-table scan (větší počet datových bloků) a v podobných příkladech se jde do indexu nezávisle na tom jestli má nebo nemá nízkou selectivitu, pokud to co potřebuji dokážu vyčíst pouze z indexu, jdu pochopitelně jen tam a proto pořád může být oprávněný index nad sloupcem kde jsou 3 disticnt hodnoty ale řádků spoustu
jop souhlas, me spis zaujala neopravnenost indexu, nevidel jsem to tak jasně.
S optimalizátory (vzhledem k tomu ze do nich vcelku dost vidím) nějak problém nemám, neb typicky vím proč tak jednají a ve většině případů si to dokáži zdůvodnit. Ačkoliv když je nouze používám hint parallel/dynamic sampling, protože to hustě naboří exekuční plány a jsou uplně jiné, což je fajn jedno slovo to dokáže celé překopat, ne vždy má člověk čas a chut koukat do exekučního plánu s >10 tabulkama a je tam vcelku rychlá šance na "výhru"
* -podmínka tam musí být neb do indexu nejdou nully (alternativně to tam být nemusí pokud nad tim sloupcem je constraint not null), pouze do bitmapového, ale tam se zase soupeří o bloky při parallením zpracování.
Akorád teda nerozumim té referenční integritě, co jsi psal v první větě, constrainty jsou (alespon za mne) na refrenčníní integritu a uplně jiné než indexy..
Constrainty jsou kvůli referenční integritě, ale jsou používány optimalizátorem i jako indexy.
(a bere ta veda v potaz, ze kdyz zalozim nejaky "hezky" index s uniquie/not null parametrem, ze to pro optimalizator sice ma efekt, ale realne jsou zalozeny constrainty samostatne, podobne jako primarni klic neni nic jineho nez kombinace index+not null constraint+ uniquie constraint a tedy pokud zalozis tuhle trojku tak mas to same jako primary key, pro optimalizator je to naprosto to samé. Jedinej rozdil je v tom, ze pri "primary key" se to chová jinak při dropu - jde to pryč dohromady. Pokud jsi to založil zvlášt musíš to dropovat postupně (oracle, mysql nevim)
Při běhání s naším psíkem po zahradě mi napadla jen jedna* možnost jak vynutit refrenční integritu přes index a nejde to jinak než přes samostatný constraint. Index je pro výkon a nema a tedy nemuze slouzit k vynuceni refrencni integrity.
* - a to když potřebuju vynutit integritu na základě funkce, kterou nemužu použít v CHECK (USER,USERENV..), ale mužu jíž použít ve funkčním indexu.
Samostatny constraint nemyslim. Myslim contraint, ktery slouzi jako referencni integrita. Ref.integritu muzes propojit pouze na nějaký index.
A MS SQL jej pouziva k optimalizaci dotazu. Nevim, jak je na tom Oracle, se kterým máš asi zase víc zkušeností ty.
Ok, to bude patrně nějaká fung. myssql, protoze i tahle veta je pro mne nepochopitelna, tedy patrne rozdilnost implementaci cons/indexu v oracle mssql, svedem to na to.
Nie je to nezmysel, staci sa trochu orientovat v databazach. MS SQL ma priamo v management studiu zobrazenie execution planu a pripadne vie navrhnut vhodny index. Netvrdim, ze navrhy su vzdy 100%, ale poziadavka od msx. nie je vobec nezmysel.
ano ale to je poměrová statistika nad nějakým jedním konkrétním dotazem. V podstatě je to optimalizace na jeden KONKRÉTNÍ dotaz. A to je něco trochu jiného, než chce msx, který poptává nějaký obecný "optimalizátor". To je, jednoduše řečeno, naprostý nesmysl - toto lze provést až se znalostí počtu jednotlivých dotazů za jednotku času, velikosti tabulek atp. a vždy se bude jednat o určitý trade-off (něco za něco).
Přiznám se, že jak tu ostatní poslouchám, tak mi to jako databázistu rve uši ;)
Odpověď je, že pochybuju, že něco takového existuje pro mysql (klíčové slovo je autotuning sql) - Oracle něco takového má k dispozici a ten nástroj (a že věnují hooodně usilí ho zdokonalovat) dokáže poradit jen se založením indexu, nahrazením mw, přepsáním dotazu a ještě asi dvěma a to je konec. Tim chci říct, že ani sofistikovaný nástroj, který je pro to určen, podle mne nepodová dobré výsledky (zatím)
Zase mohu jen z Oracle - ale tohole ale bude jednoduchá úloha i na MYSQL, prostě tam ty indexy dej a po té sleduji výkon, když tam nejsou ;), a především jesti je databáze reálně používá Oracle má na to ALTER INDEX marta MONITORING USAGE, podle google jde něco podobného na MYSQL asi timto nástrojem: mysqlindexanalyzer pak mluví ještě o nějakém patchy Percona a tabulce INDEX_STATISTICS . http://www.percona.com/docs/wiki/percona-server:fe atures:userstatv2?redirect=1