Viena iš labiausiai paplitusių ir populiarių reliacinių duomenų bazių yra MySQL. Internetinės programos ir paslaugos dažnai kuriamos remiantis šia duomenų baze. Tačiau norint pasiekti stabilų veikimą ir didelį našumą, reikia tinkamai sukonfigūruoti MySQL, ypač kai jis veikia VPS. MySQL serverio optimizavimas leidžia efektyviai naudoti išteklius ir pagerinti atsaką į užklausas, kas ypač svarbu didelės apkrovos programoms.
MySQL: diegimas VPS
MySQL diegimas jūsų serveryje yra būtinas prieš pradedant optimizavimą. Norėdami suprasti, kaip tai padaryti, pateiksime jums žingsnis po žingsnio instrukciją šiam procesui dviem operacinėms sistemoms.
Diegimo procesas Ubuntu/Debian
-
Įdiekite naujausią paketų tvarkyklės versiją:
sudo apt update
2. Atlikite MySQL diegimą:
sudo apt install mysql-server
3. Kitas žingsnis - suaktyvinti MySQL, po to sukonfigūruoti automatinį paleidimą, kai įkeliama jūsų operacinė sistema:
sudo systemctl start mysql
sudo systemctl enable mysql
Diegimo procesas CentOS/RHEL
-
Reikia įdiegti MySQL iš saugyklos:
sudo yum install mysql-server -
Tada reikia suaktyvinti MySQL ir sukonfigūruoti automatinį paleidimą:
sudo systemctl start mysqld
sudo systemctl enable mysqld -
Paleiskite pradinę konfigūraciją, kad pagerintumėte saugumą:
sudo mysql_secure_installation
MySQL: optimizavimo procesas
Norint padidinti našumą, reikia atlikti MySQL konfigūracijos pakeitimus po diegimo proceso. Optimizavimas apima kelias sritis: talpinimą, indeksavimą ir konfigūracijos nustatymus.
1. Talpinimo nustatymai
Talpinimas MySQL leidžia saugoti dažnai užklausiamus duomenis, taip sumažinant apkrovą duomenų bazei ir pagreitindamas atsako laiką. Pateikiame pagrindinių parametrų sąrašą talpinimo nustatymui:
-
query_cache_size — užklausų talpyklos dydis. Didesnis dydis pagerina našumą.
-
query_cache_limit — apribojimas talpinamo rezultato dydžiui.
-
innodb_buffer_pool_size — pagrindinis parametras InnoDB, nustatantis atminties kiekį, skiriamą duomenų ir indeksų saugojimui talpykloje.
Konfigūruojant MySQL talpinimą, pakeitimai dažniausiai atliekami faile my.cnf, esančiame kataloge /etc/mysql/ arba /etc/ daugumoje Linux serverių.
Štai išsami instrukcija, kaip taikyti talpinimo parametrus:
1 žingsnis: Atidarykite MySQL konfigūracijos failą
Atidarykite my.cnf failą redagavimui. Priklausomai nuo sistemos, gali prireikti vykdyti komandą administratoriaus teisėmis:
sudo nano /etc/mysql/my.cnf
2 žingsnis: Suraskite skyrių [mysqld]
Norint, kad talpinimo parametrai veiktų MySQL serveryje, juos reikia įtraukti į skyrių [mysqld].
3 žingsnis: Talpinimo parametrų įvedimas
Kopijuokite žemiau nurodytas konfigūracijas ir pritaikykite jas dokumente my.cnf po skyriumi [mysqld]:
[mysqld]
query_cache_type = 1 # Užklausų talpyklos aktyvavimas
query_cache_size = 64M # Talpyklos dydžio nustatymas
query_cache_limit = 1M # Maksimalus užklausos rezultato dydis talpinimui
table_open_cache = 2000 # Bendras talpinamų lentelių skaičius
thread_cache_size = 50 # Talpinamų gijų skaičius
Atkreipkite dėmesį: Nustatyti dydžiai, pavyzdžiui, 64M už query_cache_size, gali būti keičiami pagal jūsų poreikius.
4 žingsnis: Perkraukite MySQL, kad pakeitimai įsigaliotų
Po redagavimo reikia išsaugoti failą ir išeiti iš redaktoriaus. Tam reikia perkrauti MySQL naudojant komandą:
sudo systemctl restart mysql
Talpinimo nustatymo patikrinimas
Norint patikrinti, ar pakeitimai įsigaliojo, reikia vykdyti komandą:
SHOW VARIABLES LIKE 'query_cache%';
SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'thread_cache_size';
Šios komandos parodys dabartinę parametrų būseną, leidžiančią patikrinti jų teisingumą ir, jei reikia, patikslinti nustatymus.
2. Lentelių indeksavimas
Indeksavimas MySQL — tai našumo gerinimo metodas, leidžiantis optimizuoti paiešką ir informacijos gavimą iš DB. Indeksai kuriami lentelių stulpeliams ir padeda MySQL serveriui greičiau rasti eilutes, atitinkančias užklausos sąlygas, sumažinant peržiūrimų eilučių skaičių. Panašiai kaip knygos turinys, indeksas nurodo, kur galima rasti reikiamus duomenis, neperžiūrint visos informacijos masyvo.
Kodėl reikalingas indeksavimas?
-
Užklausų pagreitinimas: indeksai užtikrina greitesnį informacijos aptikimą ir gavimą MySQL serveriu, kas žymiai padidina užklausų apdorojimo efektyvumą.
-
Serverio apkrovos sumažinimas: įvesties-išvesties skaičius, dėka indeksavimo, sumažėja, palengvindamas užklausų apdorojimą ir taip sumažindamas serverio apkrovą.
-
Sujungimo (JOIN) operacijų optimizavimas: indeksai padeda greičiau rasti ir atitikti eilutes, sujungiant kelias lenteles.
-
Rūšiavimo ir grupavimo gerinimas: naudojant indeksus, MySQL gali greičiau vykdyti ORDER BY ir GROUP BY operacijas.
Kada ir kokius indeksus naudoti?
-
Pirmasis raktas (PRIMARY KEY): sukuriamas unikaliam kiekvienos įrašo identifikatoriui (pavyzdžiui, vartotojo identifikatorius). MySQL automatiškai sukuria indeksą pirmam raktui.
-
Unikalūs indeksai (UNIQUE): taikomi laukams, kurių reikšmės turi būti unikalios (pavyzdžiui, el. paštas).
-
Įprasti indeksai (INDEX): naudingi stulpeliams, dažnai naudojamiems WHERE sąlygose arba JOIN sujungimuose.
-
Kompozitiniai indeksai: indeksuojami keli stulpeliai, kas patogu optimizuojant sudėtingas užklausas su keliais kriterijais.
Indeksavimo naudojimo pavyzdžiai
Pažvelkime į keletą realių atvejų, kur naudojami indeksai.
1. Indeksavimas stulpelio su dažnomis užklausomis
Tarkime, kad turime užsakymų lentelę, kurioje yra stulpeliai id, customer_id ir status. Jei užklausos dažnai pasirenka užsakymus pagal status, tai norint žymiai padidinti tokių užklausų vykdymo greitį, jums reikia sukurti indeksą stulpeliui status.
CREATE INDEX idx_status ON orders(status);
Po užklausos vykdymo:
SELECT * FROM orders WHERE status = 'completed';
MySQL naudos indeksą greitam eilučių su status = 'completed' paieškai, neperžiūrint visų lentelės eilučių.
2. Unikalūs indeksai duomenų ribojimui
Jei norime apriboti dublikatų atsiradimą el. pašto lauke vartotojų lentelėje, galime sukurti unikalų indeksą:
CREATE UNIQUE INDEX idx_email ON users(email);
Dabar bandymas įterpti pasikartojantį el. paštą sukels klaidą, nes unikalus indeksas draudžia dublikatus.
3. Indeksai sudėtingoms užklausoms, sudaryti iš kelių komponentų
Kai turime užklausas, kurios dažnai pasirenka duomenis pagal kelis laukus, pavyzdžiui, pagal firstname ir lastname darbuotojų lentelėje, galime sukurti kompozitinį indeksą:
CREATE INDEX idx_name ON employees(firstname, lastname);
Dabar, vykdant komandą:
SELECT * FROM employees WHERE firstname = 'John' AND lastname = 'Doe';
Pasirinkimas bus atliekamas greičiau, nes MySQL naudos kompozitinį indeksą ir nebereikės tikrinti visų įrašų.
Indeksai: palaikymas ir atnaujinimas
Reikėtų prisiminti, kad pagrindinė rekomendacija yra ta, kad indeksavimas turėtų būti atliekamas tik stulpeliams, kurie dažnai dalyvauja užklausose, ir periodiškai peržiūrėti jų aktualumą. Indeksai aktyviai naudoja disko erdvę, žymiai sulėtina atnaujinimų, įrašų šalinimo ir įterpimo procesus. Priežastis ta, kad kiekvieną kartą keičiant duomenis reikia perskaičiuoti.
Indeksai: stebėjimo pavyzdys
Galite naudoti šią komandą, kad pamatytumėte indeksus:
SHOW INDEX FROM orders;
Tai suteikia mums informaciją apie tai, kokie indeksai buvo sukurti, jų tipą ir kokiems stulpeliams jie taikomi.
3. Jungčių parametrų nustatymas
Parametrai, atsakingi už tuo pačiu metu vykdomų jungčių skaičių, leidžia lanksčiai valdyti serverio apkrovą. Pagrindiniai parametrai:
-
max_connections — maksimalus jungčių skaičius.
-
wait_timeout — laukimo laikas, kol neaktyvios jungtys bus uždarytos.
[mysqld]
max_connections = 200
wait_timeout = 600
MySQL palaikymas ir stebėjimas
Norint išlaikyti MySQL našumą, svarbu stebėti jo būklę ir periodiškai atlikti optimizavimą. Naudokite šiuos metodus:
-
MySQL procesų stebėjimas: norint stebėti vykdomas užklausas, galima naudoti komandas SHOW PROCESSLIST arba specializuotas programas.
Reguliariai atlikite lentelių optimizavimo procesą, naudodami atitinkamą komandą:
OPTIMIZE TABLE table_name;
-
Automatizuotas stebėjimas: nustatykite stebėjimą naudojant tokius įrankius kaip MySQL Workbench, Percona Monitoring and Management arba Grafana, kad nuolat stebėtumėte našumą.
Išvada
MySQL konfigūravimas ir optimizavimas VPS padeda padidinti programų efektyvumą ir stabilumą. Taikydami aprašytus metodus, galite pagerinti duomenų apdorojimo greitį, sumažinti vėlavimus ir padidinti MySQL serverio patikimumą. Reguliarus nustatymų atnaujinimas ir stebėjimas padeda išlaikyti aukštą našumą ilgalaikėje perspektyvoje.
Indeksavimas — svarbi MySQL našumo optimizavimo technika. Ji įgauna ypatingą efektyvumą dirbant su užklausomis, turinčiomis filtravimą ir rūšiavimą, taip pat su didelėmis lentelėmis. Teisingai taikant indeksus, žymiai pagerėja duomenų bazės darbo greitis. Tačiau svarbu atsiminti apie atsargų jų taikymą, su tikrai svarbiais stulpeliais, skirtais paieškos, rūšiavimo ar sujungimo sąlygoms vykdyti.