Kategórie
Technológie Tipy a triky

Efektívne na MySQL, časť prvá

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

20 odpovedí na “Efektívne na MySQL, časť prvá”

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.

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 …

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 🙂

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.

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. 🙂

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. 😀

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

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.

@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.

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.

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.

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)

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

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.

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)

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.

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/

Pridaj komentár

Vaša e-mailová adresa nebude zverejnená.