Kodėl jūsų užklausos lėtos ir kaip tai sužinoti
Kiekvienas, dirbęs su duomenų bazėmis, bent kartą yra susidūręs su situacija, kai užklausa veikia taip lėtai, kad norisi galvą į stalą daužyti. Atidari phpMyAdmin ar kokį kitą įrankį, paleidžianti SELECT užklausą ir… laukianti. Ir laukianti. O gal net kavos spėji nueiti. Problema ta, kad dažniausiai mes rašome SQL užklausas ir tiesiog tikimės, kad duomenų bazė viską padarys efektyviai. Bet realybė būna kitokia.
Čia ir ateina į pagalbą EXPLAIN komanda – tai tarsi rentgeno aparatas jūsų SQL užklausoms. Ji parodo, ką tiksliai duomenų bazė daro su jūsų užklausa, kokius indeksus naudoja (arba nenaudoja), kiek eilučių peržiūri ir kokiu keliu eina gaudama duomenis. Problema tik viena – EXPLAIN išvestis atrodo kaip kažkoks šifras, kurį reikia mokėti perskaityti.
Šiame straipsnyje išmoksime skaityti EXPLAIN planus kaip atvirą knygą. Ne teoriškai, o praktiškai – su realiais pavyzdžiais ir paaiškinimais, ką daryti, kai matote tam tikrus dalykus.
Pirmasis žingsnis: kaip gauti EXPLAIN planą
Pradėkime nuo paprasčiausio dalyko. Norint gauti EXPLAIN planą, tiesiog prirašote EXPLAIN prieš savo SELECT užklausą:
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
MySQL (arba MariaDB) grąžins lentelę su stulpeliais, kurie iš pirmo žvilgsnio gali atrodyti bauginančiai. Bet nesijaudinkite – dauguma jų yra intuityvi, kai suprantate logiką.
Jei naudojate PostgreSQL, galite naudoti:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
ANALYZE papildymas yra ypač naudingas, nes jis ne tik parodo planą, bet ir faktiškai įvykdo užklausą bei parodo realiuosius skaičius. MySQL nuo 8.0 versijos taip pat turi EXPLAIN ANALYZE.
Svarbu suprasti: paprastas EXPLAIN parodo, ką duomenų bazė *planuoja* daryti. EXPLAIN ANALYZE parodo, ką ji *iš tikrųjų padarė*. Kartais šie dalykai skiriasi, ypač kai statistika pasenusi.
Svarbiausi EXPLAIN plano stulpeliai ir jų reikšmės
Dabar pažiūrėkime į svarbiausius stulpelius, kuriuos matote EXPLAIN išvestyje. MySQL/MariaDB atveju tai bus:
select_type – parodo, kokio tipo SELECT užklausa. SIMPLE reiškia paprastą užklausą be subquery ar UNION. PRIMARY reiškia išorinę užklausą, kai yra subquery. SUBQUERY – akivaizdu. Dažniausiai matote SIMPLE, ir tai gerai.
table – iš kokios lentelės skaitomi duomenys. Jei turite JOIN, matysite kelias eilutes, po vieną kiekvienai lentelei.
type – tai vienas svarbiausių stulpelių. Jis parodo, kaip MySQL prisijungia prie lentelės. Čia yra hierarchija nuo geriausio iki blogiausio:
– system – lentelė turi vieną eilutę. Tobula situacija.
– const – lentelė turi maksimaliai vieną atitinkančią eilutę. Pavyzdžiui, paieška pagal PRIMARY KEY. Labai greita.
– eq_ref – viena eilutė skaitoma iš šios lentelės kiekvienai eilučių kombinacijai iš ankstesnių lentelių. Naudojama JOIN su PRIMARY KEY arba UNIQUE indeksu.
– ref – visos eilutės su atitinkančia indekso verte skaitomos. Gera, kai indeksas nėra unikalus.
– range – eilutės gaunamos naudojant indeksą su diapazonu (BETWEEN, >, < ir pan.).
- index – visas indeksas nuskaitomas. Geriau nei ALL, bet vis tiek ne idealu.
– ALL – pilnas lentelės skaitymas (full table scan). Blogiausia situacija, ypač didelėse lentelėse.
Jei matote ALL arba index didelėse lentelėse, tai raudona vėliavėlė. Reikia indeksų.
possible_keys – kokie indeksai galėtų būti panaudoti. Kartais MySQL parodo kelis variantus.
key – kuris indeksas iš tikrųjų buvo panaudotas. Jei čia NULL, vadinasi, indeksas nenaudojamas. Blogai.
rows – kiek eilučių MySQL tikisi peržiūrėti. Tai įvertinimas, bet labai svarbus. Jei matote milijonus, o lentelėje yra milijonai įrašų, vadinasi, daroma full table scan.
Extra – papildoma informacija. Čia gali būti įvairių dalykų: „Using index” (gerai, naudojamas tik indeksas), „Using where” (filtruojama su WHERE), „Using temporary” (kuriama laikina lentelė – gali būti lėta), „Using filesort” (rūšiuojama – gali būti lėta su dideliais duomenų kiekiais).
Praktiniai pavyzdžiai: kas blogai ir kaip taisyti
Teorija teorija, bet pažiūrėkime į konkrečius pavyzdžius. Tarkime, turime tokią užklausą:
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2024-01-01';
Gauname tokį planą:
type: ALL key: NULL rows: 2500000 Extra: Using where
Matome problemą? Type yra ALL, key yra NULL, o rows rodo, kad bus peržiūrėtos 2.5 milijono eilučių. Tai klasikinis full table scan. Sprendimas paprastas – sukurti indeksą:
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);
Po indekso sukūrimo EXPLAIN planas turėtų atrodyti taip:
type: range key: idx_customer_date rows: 150 Extra: Using index condition
Dabar type yra range (gerai!), naudojamas mūsų sukurtas indeksas, ir peržiūrima tik 150 eilučių vietoj 2.5 milijono. Užklausa pagreitės šimtus kartų.
Kitas pavyzdys su JOIN:
EXPLAIN SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';
Jei matote tokį planą:
table: users | type: ALL | rows: 100000 table: orders | type: ALL | rows: 2000000
Tai katastrofa. Abi lentelės skaitomos pilnai, o tai reiškia 100,000 * 2,000,000 = 200 milijardų operacijų teoriškai. Reikia indeksų:
CREATE INDEX idx_user_status ON users(status);
CREATE INDEX idx_order_user ON orders(user_id);
Subquery ir jų pavojai
Subquery yra vieta, kur dažnai slepiasi našumo problemos. Pavyzdžiui:
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE name LIKE 'Electronics%');
EXPLAIN gali parodyti, kad subquery vykdomas DEPENDENT SUBQUERY tipo, o tai reiškia, kad jis bus įvykdytas kiekvienai išorinės užklausos eilutei. Jei products lentelėje yra 100,000 įrašų, subquery bus įvykdytas 100,000 kartų.
Sprendimas – perrašyti į JOIN:
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name LIKE 'Electronics%';
Arba, jei subquery tikrai grąžina nedaug reikšmių, galima naudoti EXISTS vietoj IN:
SELECT * FROM products p WHERE EXISTS (
SELECT 1 FROM categories c
WHERE c.id = p.category_id AND c.name LIKE 'Electronics%'
);
EXISTS dažnai veikia greičiau, nes gali sustoti iškart radęs pirmą atitikmenį.
Indeksų strategijos ir dažniausios klaidos
Indeksai nėra magiškas sprendimas visoms problemoms. Kartais jie net gali pakenkti. Štai keletas praktinių patarimų:
Sudėtiniai indeksai ir jų tvarka – kai kuriate indeksą keliems stulpeliams, tvarka svarbi. Indeksas (a, b, c) veiks užklausoms su WHERE a=X, WHERE a=X AND b=Y, ir WHERE a=X AND b=Y AND c=Z. Bet jis NEVEIKS su WHERE b=Y arba WHERE c=Z. Pirmasis stulpelis turi būti naudojamas.
Taisyklė: į indeksą pirmus dėkite stulpelius, kurie naudojami lygybės palyginime (=), paskui tuos, kurie naudojami diapazonams (>, <, BETWEEN). Per daug indeksų – kiekvienas indeksas lėtina INSERT, UPDATE ir DELETE operacijas, nes indeksai turi būti atnaujinami. Neturite kurti indekso kiekvienam stulpeliui. Sukurkite tuos, kurie tikrai naudojami dažnose užklausose.
Indeksai ir LIKE – indeksas veiks su LIKE ‘tekstas%’, bet NEVEIKS su LIKE ‘%tekstas%’ arba LIKE ‘%tekstas’. Jei reikia ieškoti teksto viduryje, galbūt reikia full-text indekso arba specializuoto sprendimo kaip Elasticsearch.
Funkcijos ir indeksai – jei rašote WHERE LOWER(email) = ‘[email protected]’, indeksas ant email stulpelio neveiks, nes naudojate funkciją. Sprendimas: arba saugokite duomenis jau apdorotus (lowercase), arba naudokite funkcijų indeksus (MySQL 8.0+):
CREATE INDEX idx_email_lower ON users((LOWER(email)));
PostgreSQL specifika ir skirtumai
Nors pagrindiniai principai tokie patys, PostgreSQL turi savo ypatumų. EXPLAIN ANALYZE išvestis yra detalesne ir parodo daugiau informacijos:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = '[email protected]';
BUFFERS parametras parodo, kiek duomenų buvo skaityta iš cache, o kiek iš disko. Tai svarbu suprantant realų našumą.
PostgreSQL taip pat turi skirtingus indeksų tipus:
– B-tree (numatytasis, tinka daugumai atvejų)
– Hash (greitesnis lygybės palyginimams, bet negali būti naudojamas range užklausoms)
– GiST ir GIN (geografiniams duomenims, full-text paieškai, JSON)
– BRIN (labai didelėms lentelėms su natūralia tvarka)
PostgreSQL planuotojas yra sudėtingesnis ir dažnai priima geresnius sprendimus nei MySQL, bet tai nereiškia, kad galite ignoruoti optimizavimą.
Vienas svarbus dalykas – PostgreSQL statistika. Jei pastebite, kad planuotojas priima blogus sprendimus, pabandykite:
ANALYZE users;
Tai atnaujins lentelės statistiką ir gali kardinaliai pakeisti planus.
Kaip tai pritaikyti realiame projekte
Gerai, dabar žinote kaip skaityti EXPLAIN planus. Bet kaip tai integruoti į kasdienį darbą?
Slow query log – įjunkite lėtų užklausų registravimą. MySQL:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
Tai registruos visas užklausas, kurios užtrunka ilgiau nei 1 sekundę. Periodiškai peržiūrėkite šį logą ir optimizuokite problemingas užklausas.
Monitoring įrankiai – naudokite įrankius kaip pt-query-digest (Percona Toolkit), pgBadger (PostgreSQL), arba debesijos sprendimus kaip AWS RDS Performance Insights. Jie automatiškai identifikuoja problemingas užklausas.
Development aplinkoje – įjunkite užklausų analizę development metu. Daugelis framework’ų (Laravel, Django, Rails) turi debug toolbar’us, kurie rodo EXPLAIN planus tiesiog naršyklėje. Naudokite juos!
Testing – sukurkite testus su realistiniais duomenų kiekiais. Užklausa, kuri veikia greitai su 100 įrašų, gali būti katastrofiškai lėta su milijonu. Naudokite įrankius kaip Faker generuoti test duomenis.
Code review – įtraukite SQL optimizavimą į code review procesą. Kai kas nors prideda naują užklausą, paprašykite EXPLAIN plano. Tai užkirs kelią problemoms prieš jas patenkant į produkciją.
Kai EXPLAIN planas atrodo gerai, bet užklausa vis tiek lėta
Kartais susidursite su situacija, kai EXPLAIN planas atrodo puikiai – naudojami indeksai, nedaug eilučių peržiūrima, bet užklausa vis tiek lėta. Ką daryti?
Patikrinkite cache – galbūt duomenys nėra cache ir skaitomi iš disko. PostgreSQL EXPLAIN (ANALYZE, BUFFERS) tai parodys. Sprendimas gali būti paprastas – padidinti buffer pool dydį.
Lock’ai – galbūt užklausa laukia lock’o. Patikrinkite SHOW PROCESSLIST (MySQL) arba pg_stat_activity (PostgreSQL). Jei matote daug užklausų „Waiting for table lock” būsenoje, problema gali būti ilgai trunkančios transakcijos arba DDL operacijos.
Network latency – jei duomenų bazė yra kitame serveryje, network latency gali būti problema, ypač jei daug mažų užklausų. Sprendimas – connection pooling, užklausų grupavimas, arba duomenų bazės perkėlimas arčiau aplikacijos.
Returning per daug duomenų – SELECT * iš lentelės su 100 stulpelių gali būti lėtas tiesiog dėl duomenų kiekio. Rinkitės tik reikalingus stulpelius. Tai ne tik greičiau, bet ir naudoja mažiau atminties.
Pasenusi statistika – jei lentelė dažnai keičiasi, statistika gali būti neaktuali. MySQL automatiškai atnaujina statistiką, bet ne visada pakankamai dažnai. Rankiniu būdu:
ANALYZE TABLE orders;
PostgreSQL:
VACUUM ANALYZE orders;
Dar vienas dalykas – kartais problema ne SQL, o aplikacijos logika. Jei darote N+1 užklausų problemą (viena užklausa gauti sąrašui, po to po vieną užklausą kiekvienam elemento detalėms), net ir su puikiais indeksais bus lėta. Sprendimas – eager loading arba užklausų optimizavimas aplikacijos lygyje.
Ką daryti su tuo visu žinojimu
Skaitymas EXPLAIN planų nėra raketų mokslas, bet reikalauja praktikos. Pradėkite nuo paprastų užklausų, pažiūrėkite į jų planus, eksperimentuokite su indeksais. Sukurkite indeksą, palyginkite EXPLAIN prieš ir po. Ištrinkite indeksą, pažiūrėkite kas pasikeitė.
Svarbiausias dalykas – neoptimizuokite per anksti. Premature optimization is the root of all evil, kaip sakė Donald Knuth. Bet kai jau turite našumo problemą, EXPLAIN planas yra jūsų geriausias draugas. Jis parodo tiksliai, kur problema, ir dažniausiai sprendimas yra akivaizdus – trūksta indekso, blogai parašyta užklausa, arba pasenusi statistika.
Įsidėmėkite šiuos pagrindinius dalykus: type stulpelis turėtų būti kuo aukščiau hierarchijoje (const, eq_ref, ref – gerai; ALL – blogai). Key stulpelis neturėtų būti NULL. Rows skaičius turėtų būti kuo mažesnis. Extra stulpelyje „Using temporary” ir „Using filesort” su dideliais duomenų kiekiais yra įtartini.
Ir paskutinis patarimas – dokumentuokite savo optimizacijas. Kai sukuriate indeksą, parašykite komentarą kodėl. Ateityje, kai kas nors (galbūt jūs patys) matys tą indeksą ir galvos ar jis reikalingas, komentaras išgelbės. Duomenų bazių optimizavimas yra iteratyvus procesas, ir žinojimas kodėl tam tikri sprendimai buvo priimti yra neįkainojamas.
