Lėtos SQL Užklausos? (Kol kas) nekaltinkite MySQL

Lėtos SQL užklausos yra vienas iš pagrindinių pagalių, kišamų į MySQL ratus. Kodėl užklausos lėtos? Kas daro SQL užklausas lėtomis? Kaip padaryti užklausas greitesnėmis? Šiame įraše panagrinėsime kelis būdus, kurie gali nukreipti programuotojus tinkama linkme – apžvelgsime visų keturių CRUD operacijų – kūrimo (Create), skaitymo (Read), atnaujinimų (Update) ir naikinimo (Delete) optimizavimą.

Supraskite su Kuo Dirbate

Kalbant apie MySQL, programuotojai turi kelis pasirinkimus, susijusius su šia RDBVS. Jie gali pasirinkti variklius naudojamus duomenims saugoti. Teisingas variklio pasirinkimas yra tiesiog būtinas – neteisingas pasirinkimas gali ilgainiui kainuoti ir laiko, ir serverio resursų. MySQL turi septynis dažnai naudojamus variklius:

• InnoDB – numatytasis MySQL 5.5 ir naujesnių versijų variklis. Palaiko operacijas, eilučių lygio užrakinimus ir svetimus raktus;
• MyISAM – šis variklis valdo ne transakcijų lenteles ir palaiko “Full-text” paiešką;
• MEMORY – Šis variklis visus duomenis saugo atmintyje;
• BLACKHOLE – /dev/null saugojimo variklis. Priima duomenis, bet jų nesaugo;
• CSV – variklis, kuris duomenis saugo tekstiniuose failuose naudodamas formatą, perskiriantį laukus kableliais;
• ARCHIVE – variklis, kuris gamina specialios paskirties lenteles, kuriose kaupiami dideli kiekiai neindeksuotų duomenų. Vartotojai gali naudoti šį variklį norėdami sukurti lentelę, naudojamą archyvams.
• FEDERATED – variklis, kuris leidžia pasiekti duomenis iš nuotolinės MySQL duomenų bazės nenaudojant replikacijos ar grupiavimo.

Giliai į variklius nežiūrėsime, tačiau MySQL administratorių tarpe yra plačiai priimtas faktas – dirbdami su svarbiais duomenimis turėtume naudoti InnoDB variklį. Vieninteliai MyISAM variklio pranašumai yra tai, kad MyISAM lentelės užima mažiau vietos diske palyginus su nesuspaustomis InnoDB lentelėmis, ir kad COUNT(*) užklausa MyISAM variklyje yra greitesnė nei InnoDB.

Supraskite Užklausos Gyvavimo Ciklą

Prieš klausdami, kodėl jūsų užklausos lėtos, atminkite, kad užklausos yra užduotys, kurias sudaro užduotys. Trumpai tariant, norėdami optimizuoti užklausos greitį, turite optimizuoti tą užklausą sudarančių užduočių atlikimą padarydami užduotis greitesnėmis, priversdami jas būti atliekamas mažiau kartų arba jų išvengdami.

Atlikdama visas šias užduotis, užklausa pereina kelis “etapus” – tinklą, procesorių, kai kurias operacijas (planavimą ir kt. dalykus), eilučių užrakinimą ir gavimą (parodymą). Šių operacijų greitis priklauso nuo atminties, procesoriaus ir disko greičio – taip pat svarbios ir I/O operacijos.

Užklausų Optimizavimas 101.1 – Prašykite Duomenų Bazės tik to, ko Jums Reikia

Kai užklausa yra lėta, dažnai taip yra dėl to, nes užklausa grąžina daug daugiau įrašų arba naudoja daugiau stulpelių nei būtina. Taip gali atsitikti tada, kai kode naudojama SELECT užklausa, kuri naudoja visus lentelėje esančius stulpelius – kalbame apie SELECT *. Vietoj to turėtumėte prašyti tik tų duomenų, kurių jums iš tikrųjų reikia – pavyzdžiui, jei jūsų lentelėje yra ID, pirkėjo vardo ir pirkimo datos stulpeliai ir jūs ieškote tik kliento vardo, naudokite SELECT vardas, o ne SELECT *. Jei vengsite ieškoti nereikalingų dalykų, duomenų bazė išvengs nereikalingų operacijų ir jūsų užklausos vykdymas bus spartesnis.

Kita vertus, jei jūsų naudojamoje duomenų bazėje nėra daug įrašų ir jūs norite supaprastinti savo projekto kūrimą ar plėtrą, pavyzdžiui, naudodami tą patį kodo bloką keliose vietose naudodami SELECT *, tai nėra taip blogai. Šiuo atveju tiesiog įsitikinkite, kad pilnai suprantate jūsų daromų veiksmų poveikį duomenų bazei.

Užklausų Optimizavimas 101.2 – Ar Jūsų Duomenų Bazėje yra Nereikalingų Duomenų?

Kitas dalykas, kurį jūs turėtumėte apsvarstyti yra tai, kad jūsų duomenų bazėje gali būti nereikalingų duomenų. Jei jums reikia tik duomenų be dublikatų, o dublikatų yra ne vienas, ne du ir ne trys, užklausos gali būti lėtesnės. Tokia problema gali išlikti nepastebėta dirbant su mažais duomenų kiekiais, tačiau duomenų kiekiams didėjant problema gali išryškėti.

Tam, kad nesaugotumėte nereikalingų duomenų, iš anksto išnagrinėkite ko reikia jūsų projektui ir nelaikykite duomenų bazėje nereikalingų duomenų. Tą pasiekti galite išnaikinę dublikatus (tam galima naudoti unikalų indeksą – UNIQUE INDEX) arba į SELECT užklausą įtraukiant DISTINCT teiginį.

Užklausų Optimizavimas 101.3 – Ar Profiliuojate Savo Užklausas?

MySQL užklausų profiliavimas gali padėti išsiaiškinti kuriame užklausos etape užklausa užstringa. Norėdami pasinaudoti profiliavimo galimybėmis, atlikite šiuos veiksmus:

1. Įterpkite naują įrašą į performance_schema lentelę “setup_actors” tam, kad vartotojui, kuris vykdys užklausą, būtų galima vykdyti užklausų profiliavimą:
   INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES ('localhost', 'root', '%', 'YES', 'YES');
2. Įsitikinkite, kad kai kurie reikalingi instrumentai (lentelė “setup_instruments”) yra įgalinti (tikriausiai jie bus įgalinti pagal nutylėjimą, tačiau tai patikrinti nepakenks):
   UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
3. Įsitikinkite, kad instrumentai “events_statements_” ir “events_stages_” yra įgalinti:
   UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
4. Vykdykite norimą profiliuoti užklausą paskyroje, kurią nustatėte “setup_actors” lentelėje. Pavyzdžiui:
   SELECT * FROM test_table WHERE id = 1;
5. Nurodykite įvykdyto įvykio ID šioje užklausoje:
   SELECT EVENT_ID, TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%Event ID%';
6. Galiausiai pateikite užklausą “events_stages_history_long” lentelėje tam, kad sužinotumėte visą užklausos kelią:
   SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID = Event ID;

Kai atliksite visus šiuos veiksmus, turėtumėte matyti maždaug tokį rezultatą:

Etapas (“Stage”)	Trukmė (“Duration”)
stage/sql/starting	0.000142
stage/sql/checking permissions	0.000010
stage/sql/Opening tables	0.000163
stage/sql/init	0.000036
stage/sql/System lock	0.000007
stage/sql/optimizing	0.000011
stage/sql/statistics	0.010762
stage/sql/preparing	0.000022
stage/sql/executing	0.000000

Kitas (šis būdas bus panaikintas MySQL 5.6.7 versijoje) profiliavimo būdas yra:

1. Duomenų bazėje įvykdykite užklausą SET profiling = 1;
2. Įvykdykite užklausas, kurias norite profiliuoti;
3. Gaukite užklausų ID su užklausa SHOW PROFILES;
4. Žvilgtelėkite į užklausos profiliavimo informaciją su užklausa SHOW PROFILE FOR QUERY x kur x yra užklausos ID.

Užklausų Optimizavimas 101.4 – ar naudojate EXPLAIN?

EXPLAIN užklausa MySQL yra skirta naudoti kartu su užklausa, kurios veikimą norite paaiškinti. EXPLAIN veikia su SELECT, INSERT, UPDATE, REPLACE ir DELETE užklausomis. Ataskaitoje bus pateikiama informacija apie tai, kaip ir kuo pasinaudodama MySQL duomenų bazė įvykdė užklausą – tai gali būti puikus būdas suprasti kas trukdo užklausai būti efektyviai.

EXPLAIN užklausa gali būti puikus būdas nustatyti kodėl užklausos yra lėtos – pasinaudoję EXPLAIN teikiama informacija galite ganėtinai lengvai optimizuoti užklausas greičiui, tačiau kai tai nepadeda, laikas nerti giliau.

Ką mes Optimizuojame?

Prieš optimizuodami užklausas greičiui, turite žinoti, kokią užklausą optimizuojate ir koks rezultatas yra pakankamai geras. Kas yra greitai? Ar užklausa, įvykdyta per sekundę, yra greita? Kas jei užklausai reikės penkių sekundžių? Dešimties sekundžių? Minutės?

Kai nusprendėte koks rezultatas yra pakankamai geras, nuspręskite, kokią užklausą norite optimizuoti. Ar tai SELECT užklausa? INSERT užklausa? UPDATE užklausa? DELETE užklausa? Gal reikia optimizuoti ALTER TABLE užklausą?

Kiekviena iš šių užklausų atlieka skirtingas užduotis, todėl kiekviena iš jų turi būti optimizuojama naudojant skirtingus metodus. Pradėsime nuo SELECT užklausos optimizavimo.

SELECT užklausų Optimizavimas – Indeksavimas

Indeksavimas yra vienas pirmųjų dalykų kuriuos išgirsime kalbėdami apie SELECT užklausų optimizavimą. Indeksas, papraščiau tariant, yra duomenų struktūra, skirta operacijų lentelėje pagerinimui. Yra visokių rūšių indeksų, kiekvienas iš jų tinkamas skirtingiems dalykams. Verta paminėti ir tai, jog indeksavimas kiekviename variklyje veikia šiek tiek skirtingai – šiame skyriuje nenagrinėsime visko, tik pagrindus.

Yra penki indeksų tipai:

1. INDEX – indekso rūšis kuriai nebūtinos unikalios reikšmės. Priima NULL reiškmes;
2. UNIQUE INDEX – visi įrašai, esantys lentelės stulpelyje su šiuo indeksu, privalo būti unikalūs. Toks indekso tipas dažnai naudojamas besidubliuojančių įrašų lentelėje panaikinimui;
3. FULLTEXT INDEX – indeksas, naudojamas stulpeliuose, kuriuose naudojamos pilno teksto paieškos galimybės. Užklausose naudojančiose šį indekso tipą naudojami teiginiai MATCH() ir AGAINST(). Šis indeksas randa raktinius žodžius tekste, o ne lygina vertes tiesiogiai su indekso vertėmis. Šis indekso tipas turi savo niuansų – lauke, naudojančiame šį indekso tipą, negalima naudoti tam tikrų žodžių, laukas naudojantis šį indekso tipą, gali turėti kelis paieškos režimus ir kt.;
4. DESCENDING INDEX – šis indekso tipas įvestas aštuntoje MySQL versijoje. DESCENDING indekso tipas yra toks indekso tipas, kuriame visos reikšmės surūšiuotos atvirkštine tvarka;
5. PRIMARY KEY – unikalus indeksas. Nepriima NULL reikšmių ir nustačius jo reikšmes jų keisti negalima. Dažnai naudojamas kartu su AUTO_INCREMENT automatiškai didinant ID reikšmes.

Indeksas optimizuoja užklausos našumą išvengdamas skaitymo iš visos lentelės, tačiau indeksai yra tik vienas žaidėjas SELECT užklausų optimizavimo knygoje – yra ir daug kitų, pavyzdžiui, lentelių skaidymas (“partitioning”).

SELECT Užklausų Optimizavimas – Skaidymas

Skaidymas leidžia lenteles padalyti į kelias dalis, tačiau SQL lentelę vistiek traktuoja kaip vieną. Yra keli skaidymo tipai:

• “RANGE” tipo skaidymas – lentelė, suskirstyta pagal diapazoną (“RANGE”), apima įrašus, kurie patenka į diapazoną. Vertės yra nustatomos su užklausa VALUES LESS THAN;
• “LIST” (šąrašinis) skaidymas – lentelė, suskaidyta pagal sąrašą, leidžia paskirstyti duomenis pagal iš anksto apibrėžtą reikšmių sąrašą;
• “COLUMNS” (stulpelių) tipo skaidymas – lentelė, suskaidyta pagal stulpelius, leidžia programuotojams naudoti kelis stulpelius kaip skaidymo raktus;
• “HASH” (maišos) tipo skaidymas – taip suskaidyta lentelė leidžia programuotojams užtikrinti, kad duomenys būtų paskirstyti tolygiai per kelis skaidinius;
• “KEY” (rakto) tipo skaidymas – stulpeliai, skaidomi tokiu būdu, privalo būti pagrindinio rakto (“PRIMARY KEY”) dalimi.

Lentelių padalijimas į kelias dalis gali padaryti užklausas greitesnėmis, jei dirbama su dideliais duomenų kiekiais. Verta paminėti tai, kad SQL traktuoja skaidinius kaip lenteles, tad programuotojai gali atkurti atskirus skaidinius.

INSERT Užklausų Optimizavimas

Tam, kad optimizuotumėte INSERT užklausas, laikykitės atokiau nuo indeksų (venkite duomenų įterpimo į stulpelį, kuriame yra indeksas – kuo daugiau indeksų bus lentelėje, tuo lėtesnėmis pataps INSERT užklausos), naudokite išplėstines INSERT užklausas arba apsvarstykite galimybę įterpti duomenis į lentelę dalimis.

Reikėtų atkreipti dėmėsį į tai, kad dirbdami su dideliais duomenų kiekiais patartina vengti INSERT užklausų – vietoj jų turėtumėte naudoti masinį duomenų importavimą.

UPDATE Užklausų Optimizavimas

Vykdydami UPDATE užklausas turėkite omenyje, kad atnaujinamų įrašų skaičius turi gana didelę įtaką užklausos našumui.

Norėdami pagerinti UPDATE užklausos našumą, apsvarstykite galimybę į užklausą įtraukti WHERE sąlygą – tokiu būdu bus atnaujinami tik sąlygą atitinkantys įrašai. Atminkite, kad UPDATE užklausos su WHERE bus greitesnės, jei naudosite indeksą.

Taip pat apsvarstykite galimybę atidėti atnaujinimus ir po kurio laiko užrakinę lentelę atlikti kelis UPDATE vienu metu – tai padaryti yra daug greičiau nei vykdyti kiekvieną UPDATE užklausą po vieną.

DELETE Užklausų Optimizavimas

Tam, kad optimizuotumėte DELETE užklausas, naudokite LIMIT sąlygą – taip pasakysite MySQL kiek įrašų norite trinti vienu kartu. Kartokite užklausą ją šiek tiek pakeisdami tol, kol bus ištrinti visi įrašai. Šiame scenarijuje nepadarysite labai daug.

Santrauka

Trumpai tariant, norėdami optimizuoti užklausų našumą, įsitikinkite, kad duomenų bazės prašote tik tų dalykų, kurių jums reikia, neverskite duomenų bazės skenuoti nereikalingų įrašų, kur reikia, naudokite indeksus (atminkite, kad indeksai yra veiksmingi tik su WHERE sąlyga – indeksai sulėtina INSERT našumą), jei reikia, panaudokite skaidymą, naudokite EXPLAIN užklausas ir prireikus profiliuokite savo užklausas, taip pat apsvarstykite galimybę naudoti LIMIT sąlygą.