Kodėl jūsų MySQL duomenų bazė lėtėja ir ką su tuo daryti
Kiekvienas, kas dirba su MySQL, anksčiau ar vėliau susiduria su ta pačia problema – užklausos pradeda veikti lėčiau, serveris krūvio metu pradeda „kosėti”, o vartotojai skundžiasi, kad aplikacija stabdo. Dažniausiai tai nutinka ne dėl to, kad MySQL būtų prasta duomenų bazė, o dėl to, kad ji tiesiog nėra tinkamai sukonfigūruota arba optimizuota konkretiems poreikiams.
Realybė tokia, kad MySQL iš dėžės veikia su gana konservatyviomis nuostatomis, kurios tinka nedidelėms aplikacijoms. Kai duomenų kiekis auga, kai vartotojų skaičius didėja, o užklausos tampa sudėtingesnės, tos pradinės nuostatos tampa kliūtimi. Gera žinia – daugumą problemų galima išspręsti be papildomo aparatūros pirkimo ar migracijos į kitą duomenų bazių valdymo sistemą.
Indeksai – jūsų geriausias draugas arba blogiausias priešas
Indeksai yra viena iš svarbiausių MySQL optimizavimo priemonių, tačiau daugelis žmonių juos naudoja neteisingai. Paprasta analogija: indeksas duomenų bazėje veikia kaip turinys knygoje. Jei norite rasti informaciją apie konkretų žodį, naudojate rodyklę knygos gale, o ne skaitote visą knygą nuo pradžios iki pabaigos.
Problema prasideda tada, kai sukuriate per daug indeksų arba sukuriate juos ne tiems stulpeliams. Kiekvienas indeksas užima vietą diske ir lėtina INSERT, UPDATE bei DELETE operacijas, nes MySQL turi atnaujinti ne tik duomenis, bet ir visus susijusius indeksus.
Štai keletas praktinių patarimų darbui su indeksais:
Indeksuokite WHERE sąlygose naudojamus stulpelius. Jei jūsų užklausose dažnai matote WHERE user_id = X, user_id stulpelis tikrai turėtų turėti indeksą. Naudokite EXPLAIN komandą prieš kiekvieną užklausą, kad pamatytumėte, ar MySQL naudoja indeksus, ar ne.
Sudėtiniai indeksai turi tvarką. Jei sukuriate indeksą (last_name, first_name), jis veiks užklausoms, kurios filtruoja pagal last_name arba pagal abu laukus, bet neveiks užklausoms, kurios filtruoja tik pagal first_name. Stulpelių tvarka sudėtiniame indekse yra kritiškai svarbi.
Vengti funkcijų indeksuotuose stulpeliuose. Jei rašote WHERE YEAR(created_at) = 2024, MySQL negalės naudoti created_at indekso, nes funkcija taikoma kiekvienai eilutei. Geriau rašykite WHERE created_at >= ‘2024-01-01’ AND created_at < '2025-01-01'.
Query cache – pamirštas įrankis, kuris vis dar gali padėti
Nors MySQL 8.0 versijoje query cache buvo pašalintas dėl tam tikrų architektūrinių problemų, jei naudojate senesnę versiją, šis funkcionalumas gali žymiai pagreitinti skaitymo operacijas. Query cache saugo užklausų rezultatus atmintyje ir, jei ta pati užklausa vykdoma dar kartą, MySQL grąžina rezultatą iš cache, o ne vykdo užklausą iš naujo.
Tačiau yra keletas dalykų, kuriuos reikia žinoti. Query cache veikia gerai tik tada, kai jūsų duomenys nesikeičia labai dažnai. Jei lentelė nuolat atnaujinama, cache nuolat ištrinamas, ir jis tampa beveik nenaudingas. Be to, query cache turi globalų lock mechanizmą, kuris gali tapti butelio kakleliu didelio konkurencingumo sistemose.
Jei naudojate naujesnę MySQL versiją be query cache, galite pasiekti panašų efektą naudodami aplikacijos lygio cache sprendimus – Redis ar Memcached. Šie sprendimai dažnai yra lankstesni ir efektyvesni nei įmontuotas MySQL query cache.
Konfigūracijos parametrai, kurie tikrai turi įtakos
MySQL konfigūracijos failas (my.cnf arba my.ini) turi šimtus parametrų, bet dauguma jų niekada nereikalauja keitimo. Yra keletas pagrindinių parametrų, kurie turi didžiausią įtaką našumui, ir būtent juos reikia suprasti ir tinkamai nustatyti.
innodb_buffer_pool_size – tai turbūt svarbiausias parametras visoje MySQL konfigūracijoje. Jis nustato, kiek atminties MySQL gali naudoti InnoDB duomenims ir indeksams saugoti. Jei jūsų serveris skirtas tik MySQL, nustatykite šią reikšmę į maždaug 70-80% visos RAM. Pavyzdžiui, jei turite 16GB RAM serverį, nustatykite innodb_buffer_pool_size = 12G.
innodb_log_file_size kontroliuoja, kaip didelės yra InnoDB transakcijų log bylos. Didesni failai reiškia geresnį rašymo našumą, bet ilgesnį atkūrimo laiką po gedimo. Šiuolaikinėms sistemoms 256MB ar 512MB yra geros pradinės reikšmės.
max_connections nustato maksimalų vienu metu galimų prisijungimų skaičių. Daugelis žmonių mano, kad didesnis skaičius yra geresnis, bet tai ne visada tiesa. Kiekvienas connection sunaudoja atmintį, ir per daug connections gali išsemti serverio resursus. Geriau naudoti connection pooling aplikacijos pusėje ir laikyti šį skaičių pagrįstu – 150-300 dažniausiai pakanka.
query_cache_size (jei naudojate senesnę versiją) – pradėkite nuo 64MB ar 128MB. Jei pastebite, kad cache hit rate yra žemas, galbūt query cache jums nepadeda ir galite jį išjungti.
Lentelių struktūros optimizavimas ir normalizacija
Kartais problema slypi ne užklausose ar konfigūracijoje, o pačioje lentelių struktūroje. Duomenų bazių projektavimas turi didžiulę įtaką našumui, ir blogai suprojektuota schema gali padaryti net paprasčiausias užklausas lėtas.
Normalizacija yra svarbi, bet ne visada reikia eiti iki trečios normalinės formos. Kartais denormalizacija – duomenų dubliavimas – gali būti teisingas sprendimas našumo tikslais. Pavyzdžiui, jei turite e-parduotuvę ir dažnai rodote produkto kainą kartu su kategorijos pavadinimu, gali būti efektyviau saugoti kategorijos pavadinimą tiesiog produktų lentelėje, nors tai ir dubliuoja duomenis.
Stulpelių tipai taip pat svarbūs. Naudokite mažiausius galimus duomenų tipus, kurie talpina jūsų duomenis. Jei saugote skaičius nuo 1 iki 100, naudokite TINYINT, o ne INT. Jei saugote tekstą, kurio ilgis niekada neviršys 50 simbolių, naudokite VARCHAR(50), o ne TEXT. Mažesni duomenų tipai reiškia, kad daugiau eilučių telpa į atminties puslapį, o tai reiškia greitesnes užklausas.
Vengti NULL reikšmių, kur įmanoma. NULL reikalauja papildomo vietos ir gali apsunkinti indeksavimą bei užklausas. Jei galite naudoti numatytąją reikšmę (pavyzdžiui, 0 arba tuščią stringą), tai dažnai yra geresnis pasirinkimas.
Užklausų optimizavimas – kur prasideda tikrasis darbas
Galite turėti puikiai sukonfigūruotą MySQL serverį su tobulais indeksais, bet jei jūsų užklausos parašytos neefektyviai, niekas nepadės. Užklausų optimizavimas yra menas, kuris reikalauja supratimo, kaip MySQL vykdo užklausas.
EXPLAIN yra jūsų geriausias įrankis. Prieš kiekvieną užklausą, kuri atrodo lėta, pridėkite EXPLAIN prieš SELECT ir pažiūrėkite, ką MySQL planuoja daryti. Ieškokite šių dalykų:
– type: ALL reiškia full table scan – blogai, išskyrus labai mažas lenteles
– rows rodo, kiek eilučių MySQL planuoja peržiūrėti – mažiau yra geriau
– Extra: Using filesort reiškia, kad MySQL turi rūšiuoti rezultatus – gali būti lėta didelėms duomenų apimtims
– Extra: Using temporary reiškia, kad MySQL kuria laikinę lentelę – taip pat gali būti brangu
Vengti SELECT * – visada išvardinkite tik tuos stulpelius, kurių tikrai reikia. Tai sumažina duomenų kiekį, kurį MySQL turi perskaityti ir perduoti, ir leidžia naudoti covering indexes (indeksus, kurie turi visus reikalingus stulpelius).
JOIN operacijos gali būti lėtos, jei jos nėra tinkamai optimizuotos. Įsitikinkite, kad JOIN sąlygose naudojami stulpeliai turi indeksus. Taip pat pagalvokite apie JOIN tvarką – kartais užklausos pertvarka gali duoti žymiai geresnį našumą.
Subquery vietoj JOIN kartais gali būti lėtesni, ypač senesnėse MySQL versijose. MySQL 5.6 ir naujesnės versijos žymiai geriau optimizuoja subquery, bet vis tiek verta testuoti abi versijas.
Particionavimas ir sharding – kai viena lentelė nebepakanka
Kai jūsų lentelės auga iki milijonų ar milijardų eilučių, net su geriausiais indeksais ir optimizuotomis užklausomis našumas gali smukti. Čia į pagalbą ateina particionavimas ir sharding.
Particionavimas leidžia MySQL suskaidyti vieną loginę lentelę į kelias fizines dalis (partitions). Pavyzdžiui, jei turite logs lentelę su milijardais įrašų, galite ją suskaidyti pagal datą – kiekvienam mėnesiui atskira partija. Kai vykdote užklausą, kuri filtruoja pagal datą, MySQL gali ignoruoti visas kitas partijas ir žiūrėti tik į reikiamą.
Particionavimo strategijos:
RANGE partitioning – skaidymas pagal reikšmių intervalus, puikiai tinka datoms ar ID numeracijoms.
LIST partitioning – skaidymas pagal konkrečias reikšmes, tinka kategorizuotiems duomenims.
HASH partitioning – MySQL automatiškai paskirsto duomenis po partijas naudodamas hash funkciją.
Sharding yra dar radikalesnis žingsnis – duomenys skaidomi į visiškai atskiras duomenų bazes, dažnai net skirtinguose serveriuose. Tai sudėtinga implementuoti, bet leidžia horizontaliai skalėti duomenų bazę beveik be ribų. Pavyzdžiui, galite saugoti vartotojus su ID 1-1000000 vienoje duomenų bazėje, o vartotojus su ID 1000001-2000000 kitoje.
Tačiau sharding turi savo kainą – sudėtingesnė aplikacijos logika, sunkiau vykdyti JOIN operacijas tarp skirtingų shardų, sudėtingesnis duomenų bazės administravimas. Tai turėtų būti paskutinis žingsnis, kai visi kiti optimizavimo metodai jau išnaudoti.
Monitoring ir profilioavimas – kaip žinoti, kas vyksta
Negalite optimizuoti to, ko nematote. Monitoring yra kritiškai svarbus, kad suprastumėte, kur yra jūsų duomenų bazės butelio kaklelis. MySQL pateikia daug įrankių ir metrikų, kurias reikia stebėti.
Slow query log yra būtinas įrankis. Įjunkite jį ir nustatykite long_query_time į kažką apie 1-2 sekundes. Visos užklausos, kurios užtrunka ilgiau, bus įrašytos į log failą. Periodiškai peržiūrėkite šį failą ir optimizuokite lėčiausias užklausas.
Performance Schema (MySQL 5.5+) yra galingas įrankis, kuris leidžia matyti, kas vyksta MySQL viduje realiu laiku. Galite matyti, kurios užklausos naudoja daugiausiai resursų, kur yra lock contention, kiek laiko užtrunka įvairios operacijos.
SHOW STATUS ir SHOW VARIABLES komandos pateikia šimtus metrikų apie MySQL būseną. Stebėkite tokius dalykus kaip:
– Threads_connected – kiek connections šiuo metu aktyvių
– Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads – cache hit ratio
– Slow_queries – kiek lėtų užklausų įvyko
– Table_locks_waited – ar yra lock contention problemų
Naudokite išorinius monitoring įrankius kaip Prometheus + Grafana, PMM (Percona Monitoring and Management), arba Datadog. Šie įrankiai leidžia stebėti tendencijas laikui bėgant ir gauti alertus, kai kažkas negerai.
Kai optimizavimas tampa gyvenimo būdu
MySQL optimizavimas nėra vienkartinis projektas – tai nuolatinis procesas. Duomenys auga, vartotojų elgsena keičiasi, aplikacijos funkcionalumas plečiasi, ir tai, kas veikė gerai prieš metus, gali nebetikti šiandien.
Svarbiausia – pradėti nuo pagrindų. Įsitikinkite, kad turite tinkamus indeksus, kad jūsų konfigūracija atitinka serverio resursus, ir kad stebite, kas vyksta. Daugelis našumo problemų gali būti išspręstos paprastais veiksmais, nereikalaujančiais gilių žinių ar brangių įrankių.
Kai pagrindai tvarkoje, galite eiti giliau – optimizuoti sudėtingas užklausas, eksperimentuoti su particionavimu, svarstyti read replicas ar sharding. Bet neskubėkite į sudėtingus sprendimus, kol neišnaudojote paprastų.
Ir nepamirškite – kartais geriausias optimizavimas yra ne MySQL pusėje, o aplikacijos pusėje. Cache dažnai naudojamus duomenis, minimizuokite užklausų skaičių, naudokite batch operacijas vietoj individualių INSERT. MySQL yra greita duomenų bazė, bet net greičiausia duomenų bazė negali konkuruoti su duomenimis, kurie jau yra atmintyje.
Testuokite, matuokite, optimizuokite, ir testuokite vėl. Nėra vieno teisingo atsakymo, kas veiks geriausiai jūsų sistemai – tai priklauso nuo jūsų duomenų, jūsų užklausų, jūsų aparatūros. Bet su teisingu požiūriu ir kantrybe, galite paversti lėtą MySQL duomenų bazę į greitą ir efektyvų sistemos komponentą.
