MySQL duomenų bazės optimizavimas

Svarbiausias sistemos veikimo spartos veiksnys yra jos teisingas projektas. Jūs turite žinoti, kokios rūšies veiksmus jūsų sistema atlieka ir kokie jos trūkumai. Daugeliu atvejų, problemos gali kilti dėl:

• Informacijos radimas diske. Reikia laiko, kad diske rastume konkrečius duomenis. Šiuolaikiniuose diskuose tai užtrunka greičiau nei 10ms, todėl teoriškai galima atlikti apie 100 informacijos paieškos veiksmų per sekundę. Šis laikas naujesniuose diskuose trumpėja ganėtinai iš lėto, todėl optimizuoti, jei turime vieną lentelę, nelabai išeina. Norint pagerinti informacijos radimo laiką, reikėtų duomenis paskirstyti keliuose diskuose.
• Disko skaitymo ir rašymo greitis. Kai disko skaitiklis yra teisingoje padėtyje, reikia perskaityti duomenis. Šiuolaikiniai diskai teikia mažiausiai 10-20MB/s pralaidumą. Tai lengvai optimizuojama naudojant kelis diskus.
• Procesoriaus darbo ciklų kiekis. Kai duomenis turime pagrindinėje atmintyje, mums reikia juos apdoroti, kad gautume rezultatą. Atsižvelgdama mažos lentelės, palyginti su atminties yra dažniausia ribojantis veiksnys. Bet mažas lenteles, greitis paprastai yra ne problema.
• Atminties pralaidumas. Kai procesoriui reikia daugiau duomenų, nei gali tilpti jo vidinėje atmintyje, pagrindinės atminties pralaidumas tampa daug lemiančiu veiksniu. Tai nėra dažnai pasitaikantis sistemos veikimo spartos trukdis, tačiau į jį taip pat turi būti atsižvelgta.

Duomenų saugojimo būdai

MySQL palaiko keletą duomenų saugojimo būdų (angl. storage engines), kurie veikia kaip duomenų lentelių tvarkyklės. Vieni iš jų palaiko transakcijas, kiti ne:

• MyISAM valdo ne transakcines lenteles. Tai - didelės spartos duomenų įrašymo ir paieškos variklis, palaikantis pilnos tekstinės paieškos galimybę. MyISAM palaikoma visose MySQL konfigūracijose, ir yra duomenų saugojimo būdas naudojamas pagal nutylėjimą, nebent sukonfigūruosite kitaip.
• MEMORY saugojimo būdas naudoja lenteles saugomas atminyje (angl. in-memory tables). MERGE saugojimo būdas leidžia tapačių MyISAM lentelių rinkinį tvarkyti kaip vieną lentelę. Kaip MyISAM, MEMORY ir MERGE saugojimo varikliai palaiko netransakcines lenteles, ir abu yra įtraukti į MySQL pagal nutylėjimą.
  Pastaba: MEMORY saugojimo variklis anksčiau buvo žinomas kaip Heap.
• InnoDB ir BDB saugojimo varikliai dirba su transakcinėmis lenteles. Siekiant išlaikyti duomenų vientisumą, InnoDB palaiko FOREIGN KEY.
• EXAMPLE saugojimo variklis "nebaigtas" variklis, kuris nieko nedaro. Jūs galite kurti lenteles, bet duomenys į jas negali būti įrašomi arba nuskaitomi. Šio variklio tikslas yra būti pavyzdžiu MySQL kode, kuris iliustruotų kaip pradėti rašyti naujus saugojimo variklius. Juo suinteresuoti nebent programuotojai.
• NDBCLUSTER (taip pat žinomas kaip NDB) yra saugojimo variklis naudojamas kurti lenteles MySQL Cluster'yje, kurios paskirstytos daugelyje kompiuterių. Jis atsirado nuo MySQL 5.0. Saugojimo būdas palaikomas daugelyje Unix platformų. Eksperimentinis palaikymas ant Windows atsirado nuo MySQL Cluster NDB 7,0.
• ARCHIVE saugojimo variklis naudojamas saugoti didelius duomenų kiekius be indeksų, kad užimtų kuo mažiau vietos.
• CSV saugojimo variklis saugo duomenis atskirtus kableliais tekstiniuose failuose.
• BLACKHOLE saugojimo variklis priima duomenis, bet jų nesaugo, o paieška visada grįžta tuščią aibę.
• FEDERATED saugojimo variklis atsirado nuo MySQL 5.0.3. Šis variklis saugo duomenis nuotolinėje duomenų bazėje. Šiuo metu jis veikia tik su MySQL naudojančiu MySQL C Client API. Ateityje ketinama, kad jis galėtų prisijungti prie duomenų šaltinių, naudodamas tvarkykles arba kliento prisijungimo metodus.

Jei paskaičius šiuos aprašus jums kyla klausimas, kurį saugojimo variklį naudoti, naudokite MyISAM. :)

Duomenų bazės veikimo spartos optimizavimas

• Naudokite patvarias jungtis (angl. persistent connections) į duomenų bazę, kad būtų išvengta bereikalingos apkrovos. Jei negalite naudoti patvarių jungčių ir esate priversti kaskart jungtis prie duomenų bazės, jums gali tekti pakeisti thread_cache_size kintamojo reikšmę.
• Visada patikrinkite, ar visos užklausos naudoja indeksus, kuriuos esate sukūrę. MySQL tai galima padaryti su EXPLAIN komanda.
• Venkite sudėtingų SELECT užklausų su MyISAM lentelėmis, kuriose dažnai atnaujinama informacija, kad išvengtumėte problemų su lentelės užrakinimais, kurie atsiranda dėl skaitymo ir rašymo į tą lentelę.
• MyISAM palaiko lygiagrečius įterpimo veiksmus: jei lentelėje nėra tusčių blokų duomenų failo viduryje, galima įterpti naujas eilutes, kai kitas procesas tuo pačiu metu skaito iš lentelės. Jei tokia galimybė yra svarbi, turėtumėte apsvarstyti galimybę naudoti lentelę taip, kad nebūtų trinamos eilutes. Dar viena galimybė - paleisti OPTIMIZE TABLE duomenų defragmentavimui lentelėje po to, kai buvo ištrinta daug eilučių. Toks veikimas yra nustatomas keičiant concurrent_insert kintamąjį. Jūs galite priversti įterpinėti naujas eilutes net ir lentelėse, kuriose yra ištrintų eilučių.
• Norėdami išspręsti suspaudimo problemas, kurios galėjo atsirasti su ARCHYVE saugojimo būdo lentelėmis, taip pat galite naudoti OPTIMIZE TABLE komandą.
• Naudokite ALTER TABLE ... ORDER BY expr1, expr2, ... jei iš lentelės nuskaitinėjate stulpelius tokia tvarka: expr1, expr2, .... Naudodami tokius pakeitimus lentelėje, galite pagerinti našumą.
• Kai kuriais atvejais prasminga susikurti "koduotą" (angl. hashed) stulpelį, kuriame būtų užkoduota likusių stulpelių informacija. Jeigu stulpelis yra trumpas ir pakankamai unikalus, ir, žinoma, indeksuotas, jis veiks greičiau, nei "didelis" indeksas iš daug stulpelių. MySQL labai lengva sukurti tokį stulpelį:
  SELECT * FROM table_name WHERE hash_col = md5 (Concat (col1, col2)) AND col1 = constant AND col2 = constant;
• MyISAM lentelėse, kurios dažnai kinta, turėtumėte vengti kintamo ilgio stulpelių (VARCHAR, BLOB ir TEXT). Skaitoma, jog lentelė naudoja dinaminį eilutės formatą, kai yra bent vienas kintamo ilgio stulpelis.
• Nėra naudinga dalinti lentelę į kelias tik todėl, kad stulpelių kiekis tampa labai didelis. Kai ieškoma tam tikros eilutės, daugiausia laiko sunaudojama rasti pirmam eilutės baitui. Radus duomenis, dauguma šiuolaikinių diskų, gali pakankamai greitai perskaityti visą eilutę. Išimtį galima daryti tik tais atvejais, kai išskaidoma MyISAM lentelė, kuri naudoja dinaminį eilutės formatą ir jis pakeičiamas į fiksuotą arba kai labai dažnai naudojama tik dalis lentelės stulpelių.
• Jei dažnai reikia skaičiuoti rezultatus, pavyzdžiui: kiekį arba sumą iš kelių stulpelių, tada gali būti geriau sukurti naują lentelę ir atnaujinti skaitliukus realiu laiku. Toks atnaujinimas vyksta labai sparčiai:
  UPDATE table_name SET count_col = count_col 1 WHERE key_col = constant;
  Tai labai svarbu, kai naudojate MySQL duomenų saugojimo būdą, tokį kaip MyISAM, kuris turi lentelės lygio užrakinimą (angl. table-level locking) (daug skaitytojų, vienas įrašinėtojas). Taip geriau veikia dauguma duomenų bazių sistemų, nes eilutės užraktas, šiuo atveju, turi mažiau darbo.
• Jeigu reikia rinkti statistinius duomenis iš didelių žurnalinių (angl. log) lentelių, naudokite lenteles-suvestines vietoj viso žurnalinės lentelės skenavimo. Tokių lentelių palaikymas turėtų būti kur kas greitesnis, nei statistinių duomenų skaičiavimas "gyvai". Atnaujinti lentelę-suvestinę iš visų duomenų, kai jie pasikeičia (priklausomai nuo verslo logikos), yra greičiau.
• Jei įmanoma, klasifikuokite ataskaitas kaip "live" arba "statistines", kur duomenys reikalingi statistikos ataskaitoms yra imami tik iš suvestinių lentelių, kurios generuojamos periodiškai iš "live" duomenų.
• Pasinaudokite tuo, jog stulpeliai turi reikšmes pagal nutylėjimą. Įterpkite reikšmes tik tada, kai ji skiriasi nuo numatytosios. Tai sumažina užklausos analizės laiką ir pagerina įterpimą.
• Kai kuriais atvejais patogu pakuoti ir laikyti duomenis BLOB stulpelyje. Tokiu atveju, programoje turite pateikti kodą duomenų pakavimui ir išpakavimui, tam tikru metu tai gali padėti išvengti daug kreipinių. Praktiška naudoti, kai turite duomenis, kurie netinka į eilučių ir stulpelių lentelės struktūrą.
• Paprastai, jūs turėtumėte nelaikyti perteklinių duomenų (trečia normalinė forma). Tačiau, gali būti situacijų, kuriose gali būti naudinga dubliuoti informaciją arba kurti suvestines lenteles, kad paspartinti veikimą.
• Saugomos paprogramės ar funkcijos UDF (angl. user-defined function) yra geras būdas tam tikrose užduotyse pagreitinti sistemos darbą.
• Jūs galite padidinti našumą naudodami talpyklas (angl. cache) užklausoms ir vykdyti daug įterpimų (angl. INSERT) ar atnaujinimų (angl. UPDATE) kartu. Jei duomenų bazė palaiko lentelių rakinimą, tai užtikrins, jog indeksas bus atnaujintas tik vieną kartą, po pakeitimų. Taip pat galite pasinaudoti MySQL užklausų talpykla panašiems rezultatams pasiekti.
• Naudokite INSERT DELAYED, kai jums nereikia žinoti, kada jūsų duomenys bus įrašyti. Tai sumažina rašymo poveikį sistemai, nes daug eilučių gali būti įrašytos vienu priėjimu prie disko.
• Naudokite INSERT LOW_PRIORITY, kai norite suteikti SELECT užklausoms aukštesnį prioritetą negu įterpimui.
  Naudokite SELECT HIGH_PRIORITY gauti rezultatams be eilės. Tai reiškia, kad SELECT yra vykdomas, net jei kitas klientas jau anksčiau laukia įrašymo.
  LOW_PRIORITY ir HIGH_PRIORITY įtakoja tik saugojimo variklius, kurie naudoja lentelės lygio užrakinimą (angl.table-level locking) (pvz.: MyISAM, MEMORY ir MERGE).
• Naudokite keleto eilučių INSERT užklausas didesniam duomenų kiekiui įrašyti vienu SQL sakiniu. Daugelis SQL serverių palaiko tokias operacijas, įskaitant MySQL.
• Naudokite LOAD DATA INFILE įkelti dideliam duomenų kiekiui. Tai veikia greičiau nei naudojant INSERT užklausas.
• Naudokite AUTO_INCREMENT stulpelius taip, kad kiekviena lentelės eilutė būtų identifikuojama pagal vienintelę unikalią reikšmę.
• Kartas nuo karto naudokite OPTIMIZE TABLE, taip išvengsite fragmentacijos dinamiško formato MyISAM lentelėse.
• Naudokite MEMORY (HEAP) lenteles, kai galima gauti daugiau greičio. MEMORY lentelėse naudinga saugoti nekritinius duomenis, kurie yra dažnai skaitomi, pvz.: informaciją apie paskutinę rodytą reklamą tiems vartotojams, kurie neturi slapukų (angl. cookies) savo naršyklėje.
• Interneto serveriuose, paveiksliukai ir kitos dvejetainės bylos turėtų būti saugomos failinėje sistemoje. Tai reiškia, kad reikėtų saugoti tik nuorodą į bylą, o ne ją pačią duomenų bazėje. Dauguma interneto serverių geriau buferizuoja (angl. cache) bylas nei duomenis iš duomenų bazės, todėl naudoti bylas yra greičiau.
• Stulpeliai su identiška informacija skirtingose lentelėse turi būti aprašomi vienodais tipais, kad jų sujungimas (angl. join) vyktų greičiau.
• Naudokite paprastus stulpelių pavadinimus. Pavyzdžiui, lentelėje "klientas", naudokite stulpelį "vardas" vietoj "kliento_vardas". Norėdami, kad duomenų bazė būtų lengvai pernešama ant kitų SQL serverių, turėtumėte pavadinimus daryti trumpesniais nei 18 simbolių.
• Jei tikrai reikia didelio greičio, jūs turėtumėte pasidomėti žemo lygio sąsajomis duomenų saugojimui, kurias palaiko skirtingi SQL serveriai. Pavyzdžiui, kreipdamiesi į MySQL MyISAM saugojimo variklį tiesiogiai, galite pagreitinti užklausas nuo dviejų iki penkių kartų, lyginant su standartine SQL sąsaja. Norint tai padaryti, duomenys turi būti tame pačiame serveryje kaip ir programa, ir, paprastai, turėtų būti prieinami tik vienam procesui (nes išorinis failo rakinimas yra labai lėtas). Šias problemas galima pašalinti naudojant žemo lygio MyISAM komandas MySQL serveryje (tai yra paprastas būdas gauti daugiau spartos).
• Jei naudojate skaitmeninius duomenis, juos, daugeliu atvejų, iš duomenų bazės gauti yra greičiau nei iš tekstinės bylos. Informacija duomenų bazėje saugoma kompaktiškiau nei tekstinėje byloje ir ją gauti reikia mažiau užklausų diske. Taip sumažinate programos kodo apimtį, nes nereikės apdoroti tekstinių bylų norint rasti duomenis.
• Replikacija kai kuriose operacijose gali duoti naudos. Galite padalinti kliento užklausas tarp replikacijos serverių. Norėdami išvengti pagrindinio serverio stabdymo duomenų kopijavimo metu, galite daryti atsargines kopijas naudodami pagalbinį serverį.
• Aprašius MyISAM lentelę su DELAY_KEY_WRITE = 1 galima greičiau atnaujinti indeksą, nes pakeitimai nėra įrašomi į diską, kol lentelė neuždaroma. Trūkumas: jei kas nors išjungia serverį kol lentelės yra atidarytos, reikia patikrinti, jog duomenys lentelėje yra geri, paleidžiant serverį su nustatymu --myisam-recover, arba įvykdant myisamchk prieš prieš paleidžiant serverį. (Tačiau, net ir šiuo atveju, neprarasite informacijos, nes ji visada gali būti sugeneruojama iš duomenų eilučių)

Naudota literatūra

1. MySQL 5.0 Reference Manual :: 7 Optimization