Efektívne na MySQL, časť prvá


  • Zdieľať na Google+

Na admin oddelení sa často stretávame s rôznymi problémami našich zákazníkov, ktoré sú nie vždy chybou na strane samotného servera a hostingu ako takého. Preto sme sa rozhodli písať seriál o rôznych tipoch, ktoré by vám mali pomôcť pochopiť a odstrániť niektoré problémy so stránkami. V tejto časti sa hlavne začneme venovať MySQL databázam.


Jednou z častých otázok, ktorú dostávame od klientov, je napr. Prečo je rýchlosť načítania  stránky zrazu nižšia než po spustení? Alebo aj Pri porovnávaní rýchlosti s tým, ako mi to beží na lokálnom PC, je načítanie oveľa pomalšie, prečo? Existuje viacero príčin pomalšieho chodu stránok. Jednou z najčastejších je práve to, že dotazy na databázu, ktoré pri menšom počte návštevníkov a menšom rozsahu databázy neboli také kritické, zrazu zaberajú čoraz viac času.

 

Prvou vecou, ktorú musíme spraviť, je prejsť si, aké dotazy nám môžu spôsobovať problémy. Na sledovanie vykonávania jednotlivých dotazov môžeme použiť príkaz SHOW processlist; priamo v phpmyadmine. Pomocou tohto príkazu môžeme okrem iného vidieť, ako dlho už dotaz beží, jeho identifikačné číslo, ako aj momentálny stav. V rámci hostingov WebSupportu využívame možnosť zaznamenávania MySQL dotazov trvajúcich príliš dlho do slow query logu, ktorý môžete nájsť vo WebAdmine v sekcii Databázy. Pokiaľ teda hľadáte príčinu spomalenia stránok a podozrievate databázu, toto miesto je výhodné vždy pozrieť čo najskôr.

Keď máme podchytený dotaz, ktorý sa vykonáva dlhší čas, vieme nájsť aj detaily toho, čo robí. Postačí uložiť si číslo dotazu (pokiaľ prebieha) alebo jeho syntax a následne MySQL zaslať dotaz EXPLAIN (EXPLAIN číslo_procesu; alebo EXPLAIN  syntax_pomalého_query;). Následne môžeme rýchlo zistiť, aký objem dát (množstvo spracovaných riadkov)  či a aké sa používajú indexy, či je nutné vytvoriť temporary tabuľky na disku, lebo sa medzivýsledok nevojde do pamäte, ako aj medzi akými typmi premenných porovnávame.

 

Indexovanie tabuliek 

Jednou zo základných možností, ako optimalizovať databázu, sú práve indexy. Často sa dáta v rámci stĺpca dajú zapísať oveľa efektívnejšie z pohľadu vyhľadávania –  jednotlivé polia môžeme upraviť do podoby hashu alebo binárneho stromu a následne zoradiť –  ako do kartotéky. Keď MySQL následne napr. vyberá podľa kritérií z tabuľky, nemusí prechádzať celý obsah databázy. Najčastejšie to užívateľ databázy môže pocítiť, keď jeho query ide naprieč viacerými tabuľkami (JOIN) a hľadaní unikátnych záznamov (DISTINCT).

Ak chceme spojiť dáta z dvoch tabuliek, je to čo sa týka rozsahu (a teda aj objemu) spracovaných dát trocha ošemetná situácia, pokiaľ by sme chceli hľadať hrubou silou. Náročnosť akéhokoľvek JOIN totiž stúpa s počtom riadkov a v najhoršom prípade rastie až exponenciálne. (Pikoška: že to môže byť až také zlé, som zbadal, keď som nad databázou istého fóra videl ako dotaz prehľadával niekoľko desiatok biliónov polí. Biliónov!)

Na to, aby sme mohli vyberať naprieč viacerými poľami, potrebujeme totiž najprv získať takú tabuľku, kde sú ku každej vybranej kombinácii stĺpcov z prvej tabuľky pridelené podľa možnosti relevantné stĺpce z druhej tabuľky, aby sme potom mohli vôbec porovnávať, ktoré riadky spĺňajú podmienky vo WHERE.

Pokiaľ teda vidíme, že je v dotaze direktíva  JOIN (alebo aj LEFT JOIN, a OUTER JOIN) a vidíme, že sa nepoužíva index, pozrieme, aké stĺpce sú v jednotlivých tabuľkách používané, pre ktoré následne vytvoríme index. Napríklad na tabuľke TABLE so stĺpcami TABLE.id,  TABLE.stĺpce,  TABLE.ktoré, a  TABLE.indexovať :

           CREATE INDEX id_join on TABLE (stĺpce, ktoré, indexovať);

 

To isté vykonáme aj pri druhej tabuľke. Indexovanie tabuliek je len jedno z viacerých riešení, ako zrýchliť načítavanie údajov z databáz a v neposlednom rade aj zrýchliť odozvu vašich stránok. K ďalším obvyklým ťažkostiam, ako je nesprávne použitie subquery, nevhodné PHP nastavenia, optimalizáciu spúšťaných skriptov atď. sa budeme venovať v ďalších blogpostoch. Dúfame, že vám tento článok aspoň trochu objasnil, ako funguje indexovanie v databázach a prečo je jeho používanie vhodné. Neváhajte sa podeliť s vašimi tipmi v komentároch.

 

Pokiaľ by ste mali o túto tému hlbší záujem, rozhodne môžem odporučiť nasledovné odkazy:

http://www.slideshare.net/osscube/indexing-the-mysql-index-key-to-performance-tuning

http://www.cs.duke.edu/csl/docs/mysql-refman/optimization.html

Komentáre

  • sveta
    Odpovedať
    Autor
    sveta

    indexy su super, tie aj ja pouzivam casto alebo viewy.

  • Leachim
    Odpovedať
    Autor
    Leachim

    Ešte by som rovno pri joinoch a indexoch spomenul aj to, že nie je na škodu pri joinovaní mať stĺpce rovnakých typov, teda napr. int a int a ideálne aj s rovnakou dĺžkou, na čo veľa ľudí zabúda.

  • Michal
    Odpovedať
    Autor
    Michal

    Par bodov na rychlo co ma napadlo …
    1. Indexy v kazdom pripade. Najma pri stlpcoch kde sa bude pouzivat WHERE a ORDER.
    2. Tabulky v 3. norm. forme.
    3. Co najmensie datove typy. (bigint a pod. len za cenu useknutej ruky)
    4. Rovnake datove typy pri joinich. Aj velkosti.
    6. Vyvarovat sa „SELECT * …“
    7. Pri vracani len jedneho riadku pouzivat LIMIT 1
    8. DISTINCT a IN len v nutnych pripadoch
    9. kodovanie utf8
    10. Rozmyslat a kreslit si … nad prikazmi tohoto typu ani nerozmyslat :
    select …
    while …
    select …
    while …

  • dimitry
    Odpovedať
    Autor
    dimitry

    tiez by som dodal, ze su aj dalsie operacie ( napriklad NOT ) ktore spomaluju vyhodnocovanie, alebo spajanie viacerych podmienkov cez OR na rovnkych stlpcoch( cize a=b or a=c or a=d…) sa da kludne napisat a in (b,c,d) a tak. v kazdom pripade, optimalizacia dotazov by mala prebehnut vzdy po nejakom case zivnostni webu, lebo aj pri dobrom navrhu dotazov a tabuliek sa moze vyskytnut pomaly query (dotaz). tiez je vhodne rozbijanie tabuliek na mensie kusy, napriklad mat ciselne udaje v jednej tabulke a textove v druhej ( ak tie textove su premenlivej dlzky = varchar, (tiny,medium)text a pod. ). existuje vela sposobov ako rozbijat tabulky efektivne. tiez je dobre, ak nepouzivate variabilne dlzky ale pevne, teda namiesto varchar dajte char, pretoze potom sa rychlejsie prechadza DB, lebo vzdialnost od zaciaktu databazy je linearne vypocitatelna. sice pri pevnych dlzkach narasta velkost suboru ( pre tych co nevedia, data su ulozene v suboroch ) linearne ale rastie celkom rychlo aj na mensom pocte dat, lebo kazdy zabera pevny pocet bajtov. a na zaver je vhodne, napriklad by fulltext vyhladavani umoznujucom diakritiku (teda zadam červný a vyhlada mi to aj cerveny) nepouzivat regexp ale like, lebo je rychlejsi, ale vyzaduje si to ukladanie dat v standarzovanej forme ( teda jednak data ukladam ako original a jednak ako standardizovanu formu cize bez diakritiky ) nasledne robime vyhladavanie nie na orgios datach ale na tych upravenych, podobne ako aj vstupne hodnoty do vyhladavania musia byt standardizovane. takto som dokazal ( velice davno ) na DB pri 10 tis zaznamom zmensit dobu fulltext vyhladavania z 21 sekund na 0.12s a to uz je teda rozdiel. dnes mozem povedat, ze som neurobil ani jeden pomaly web (viac ako pol sekundy na vygenerovanie celeho webu ( a som ich vyrobil mrte ). skusenosti 🙂

    • Vladimir Chovanec
      Odpovedať
      Autor
      Vladimir Chovanec

      Ak sa bavime o MySQL tak engine MyISAM pouziva vzdy pevnu dlzku riadku. Nezalezi, ci sa ako datovy typ pouzije char alebo varchar. Preto je scan tabulky pomerne rychly. Kazdopadne je to urcite dobry postreh 🙂 Nie kazdy pouziva kombinaciu MySQL + MyISAM.

  • Michael
    Odpovedať
    Autor
    Michael

    Pri JOINoch je dobre spomenut, ze je citelne lepsie spajat uz prefiltrovane (selektovane) dotazy (projekcie). Tj. posunut selekcie co najblizsie k spajanym tabulkam. Narychlo napisany priklad:

    a) SELECT x.a, y.b FROM x JOIN y WHERE (x.a > 12 AND y.b 12) JOIN (SELECT b FROM y WHERE b 12) a (y.b < 10), co moze byt casto o niekolko radov mensi dotaz. 🙂

  • Michael
    Odpovedať
    Autor
    Michael

    Ehm, neviem cim kontrolujete a filtrujete prispevky na tomto blogu, ale vas filtrovaci system totalne sprasacil moj prispevok do absolutneho chaosu. 😀 Pisal som 2 SQL prikazy, priklad a a priklad b, a nejakym zazrakom ich to spojilo do jedneho a vyseklo cast textu zo zaveru mojho prispevku. 😀 Dajte si to dokopy. 😀

  • orsi
    Odpovedať
    Autor
    orsi

    Kde nájdem ten „slow query logu“ v admine ? Lebo pozerám a bohužial nič také tam nevidím.
    Ďikes

    • Frantisek Drojak
      Odpovedať
      Autor
      Frantisek Drojak

      orsi: sekcia databazy, v tabulke kde su zobrazene jednotlive databazy je to stvrty stlpec, bud tam je napisane, ze je prazdny alebo je k dispozicii na sitahnutie.
      slow query log btw. sa tyka len jednotlivych dlhych query, v pripade ak sa vyklonava velky pocet malych (a predsa pomalsich ) dotazov, nezachyti to.

  • Vladimir Chovanec
    Odpovedať
    Autor
    Vladimir Chovanec

    Pre lepsie pochopenie indexov odporucam http://www.sqlite.org/queryplanner.html
    Je to tam krasne vysvetlene na prikladoch aj s obrazkami. A uplne idealne si skusat tie priklady doma v konzole a sledovat exekucny plan (ci neklamu :D).

  • Frantisek Drojak
    Odpovedať
    Autor
    Frantisek Drojak

    @all: diky za mrte hintov, povodne mali vyjst o mysql este jeden diel, ale zrejme sa rozsiri o minimalne jeden, kam zapracujeme aj vase tipy.

  • Typhoon
    Odpovedať
    Autor
    Typhoon

    Beží Vám niekde aj MariaDB! ?

    • František Droják
      Odpovedať
      Autor
      František Droják

      Ano bezi, konkretne vo verzii 5.5, WebAdmin, sekcia Databazy, zalozka MariaDB 55 .
      Taktiez je moznost velmi rychlo vytvorit kopiu existujucej databazy uz na novy db server, postaci kliknut na vytvorit kopiu pri konkretnej databaze.

  • Stano
    Odpovedať
    Autor
    Stano

    Indexy sú síce super, ale ak máte pár miliónov položiek, a chcete vyhľadávať v názve a popise, aj stroju s 12GB RAM bude trvať pár sec. to prejsť.

    Pre väčšie náročnejšie aplikácie odporúčam Sphinx.

    • František Droják
      Odpovedať
      Autor
      František Droják

      sphinx je mega,mimochodom na vps/dedikovany server sa da bez problemov doinstalovat.

      samozrejme, indexy nie su samospasitelne a treba vediet, co pridanim indexu chceme dosiahnut. velmi casto sa vsak stretavame s tym, ze skutocne postaci pridat jeden index a rychlost sa dvihne o cely rad.
      maly hint pre fulltext vyhladavanie: vyhnite sa v query pouzitiu“ LIKE %hladany_string%‘, vtedy sa index nepouzije. LIKE „hladany_string%“ je vsak ok . (toto uskalie vychadza z principu akym su indexy v mysql robene)

  • lubos
    Odpovedať
    Autor
    lubos

    Maria DB + sphinx fulltext SE

    Mna by zaujimalo, ci planujete pre MariaDB v buducnosti enablovat aj sphinx search engine, ktory je s nou dodavany, avsak dla nedavnej odpovede z vasho supportu je disablovany?

    Dakujem.
    lubos

    • František Droják
      Odpovedať
      Autor
      František Droják

      nad nasadenim sphinxu sme uvazovali prakticky odkedy sme pridali MariaDB do produkcie.Zistili sme, ze budeme musiet dlhsie studovat a testovat, kym ho budeme moct zahrnut do sucasnej architektury .
      (najma oddelit bezpecne data jendotlivych uzivatelov, apod. bolo v tomto pripade problematicke )
      Dobre napady si vsak odkladame a cerpame z nich inspiraciu vzdy ked sa rozhodujeme, co zlepsime najblizsie.

  • Daniel Andraščík
    Odpovedať
    Autor
    Daniel Andraščík

    Vdaka za clanok. Aktualne sa mi to velmi hodi k jednemu projektu ktory robim. Nie je to sice web, ale klasicka win aplikacia pre MySQL ale myslim ze na tom az tak nezalezi. Mna by tiez zaujimali pohlady. Ako to s nimi je, kedy su vyhodne a ako sa spravaju s pohladu vykonu a zataze databazoveho servra. Co som tak letmym okom prebehol net, tak existuju tu okolo pohladov velmi rozporuplne nazory. (berme do uvahy MySQL 5 a vyssi. viem ze podpora pohladov u starych MySQL bud nebola vobec, alebo to bol len nejaky pseudopaskvil)

    • František Droják
      Odpovedať
      Autor
      František Droják

      VIEW ako taky je hlavne zjednodusenie pre citatelnost a pracu s kodom. Ich pouzitie ma na druhej strane dan v podobe vykonu. Mnohe veci vie dobre navrhnuty view ulahcit, sam ich rad pouzijem, ale tam, kde napr. nie je odozva kriticka.
      View ako taky nie je ziadna existujuca tabulka, takze pri jeho vykonani sa tiez vykona cele query. Existuju potom tzv. materialized views , kde sa vytvori sktuocna tabulka, vtedy si ale treba osetrit problem, ako casto ju updatovat, pricom ak sa ma po kazdom inserte do tabuliek z ktorych je zlozeny obnovit aj view, zacnete stracat vykon zas pri vkladani.

  • Jano
    Odpovedať
    Autor
    Jano

    Pre search vyuzivam Sphinx search engine, odpada z velkej casti problem vykonom databazy pri vyhladavani, akurat je zataz pri generovani indexu co vsak pri nocnom spustani nik nepociti.

    Ak mate velku databazu radove 100 tis zaznamov a chcete nad nou vykonavat nejaky kvalitny fulltext search tak ine ani neodporucam:

    http://sphinxsearch.com/