Kodėl jūsų duomenų bazė lėtėja ir kas su tuo daryti
Kiekvienas programuotojas bent kartą gyvenime susiduria su situacija, kai aplikacija veikia puikiai testuojant su keliais šimtais įrašų, bet pradėjus naudoti produkcinėje aplinkoje su milijonais įrašų, viskas tampa nepakenčiamai lėta. Vartotojai skundžiasi, serveriai dejuoja, o jūs žiūrite į savo, atrodytų, idealiai parašytą kodą ir nežinote, kas nutiko.
Dažniausiai problema slypi ne kode, o duomenų bazės optimizavime. Tiksliau – indeksų trūkume arba netinkamame jų naudojime. PostgreSQL indeksai yra tarsi knygos turinys – galite skaityti visą 500 puslapių knygą ieškodami konkretaus skyriaus, arba tiesiog pažvelgti į turinį ir iš karto atverti reikiamą puslapį. Skirtumas akivaizdus, ypač kai knyga stora.
Problema ta, kad daugelis kūrėjų apie indeksus galvoja kaip apie kažką, ką „reikia pridėti vėliau”, kai aplikacija jau veikia. Realybėje indeksų strategija turėtų būti planuojama kartu su duomenų bazės schema. Taip, žinau – skamba kaip dar vienas dalykas, apie kurį reikia galvoti, bet patikėkite, geriau praleisti valandą planuojant dabar, nei savaitę optimizuojant vėliau.
Kaip PostgreSQL ieško duomenų be indeksų
Įsivaizduokite, kad turite lentelę su milijonu vartotojų. Parašote užklausą: SELECT * FROM users WHERE email = '[email protected]'. Be indekso PostgreSQL turi atlikti tai, kas vadinama „sequential scan” arba nuosekliu skenavimu. Tai reiškia, kad duomenų bazė paeiliui tikrina KIEKVIENĄ įrašą lentelėje, kol randa tą, kurio ieško.
Milijonas įrašų – milijonas patikrinimų. Net jei jūsų Jonas yra pirmasis įrašas lentelėje, PostgreSQL vis tiek turi būti pasiruošusi patikrinti visus, nes ji iš anksto nežino, kur jis yra. Tai kaip ieškoti vieno konkretaus žmogaus stadione, pilname žmonių, klausinėjant kiekvieną: „Ar tu esi Jonas?”.
Kai kurie žmonės mano, kad šiuolaikiniai serveriai tokie galingi, jog tai neturėtų būti problema. Iš dalies tai tiesa – SSD diskai ir daug RAM atminties tikrai padeda. Bet kai jūsų aplikacija auga, kai vartotojų skaičius didėja, kai pradeda veikti sudėtingesnės užklausos su JOIN’ais – nuoseklus skenavimas tampa tikru našumo smaugiku.
Štai čia ir ateina į pagalbą indeksai. Jie sukuria atskirą duomenų struktūrą, kuri saugo nuorodas į duomenis, surūšiuotus pagal tam tikrą stulpelį ar stulpelius. Grįžtant prie stadiono analogijos – tai tarsi turėti sąrašą su visų žmonių vardais ir jų sėdimų vietų numeriais. Užuot klausę kiekvieno, tiesiog pažiūrite į sąrašą ir iš karto žinote, kur Jonas sėdi.
B-tree: jūsų pirmasis ir dažniausiai naudojamas indeksas
PostgreSQL palaiko kelis indeksų tipus, bet 95% atvejų jums reikės B-tree indekso. Tai numatytasis tipas, kurį gausite parašę CREATE INDEX idx_users_email ON users(email). B-tree struktūra veikia kaip subalansuotas medis, kuris leidžia labai greitai rasti reikiamus duomenis.
Kas įdomu – B-tree indeksai veikia ne tik su lygybės operatoriais (=), bet ir su palyginimais (<, >, <=, >=), BETWEEN užklausomis ir net su LIKE, jei paieškos šablonas prasideda konkrečiu tekstu (pvz., ‘Jonas%’, bet ne ‘%Jonas’).
Praktiškai tai reiškia, kad vienas indeksas ant datos stulpelio gali pagreitinti ir WHERE created_at = '2024-01-01', ir WHERE created_at > '2024-01-01', ir WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'. Universalu ir efektyvu.
Tačiau yra vienas dalykas, kurį būtina suprasti apie B-tree indeksus – jie užima vietą. Kiekvienas indeksas yra papildoma duomenų struktūra, kuri turi būti saugoma diske ir atmintyje. Be to, kiekvieną kartą įterpiant, atnaujinant ar ištrinant duomenis, PostgreSQL turi atnaujinti ne tik pagrindinę lentelę, bet ir visus jos indeksus. Tai reiškia, kad per daug indeksų gali sulėtinti INSERT, UPDATE ir DELETE operacijas.
Sudėtiniai indeksai: kai vieno stulpelio nepakanka
Realybėje dažnai reikia filtruoti duomenis pagal kelis kriterijus vienu metu. Pavyzdžiui: SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'. Galite sukurti atskirus indeksus ant user_id ir status stulpelių, bet tai nebus optimalu.
PostgreSQL gali naudoti kelis indeksus vienai užklausai (tai vadinama bitmap index scan), bet efektyviau yra sukurti vieną sudėtinį indeksą: CREATE INDEX idx_orders_user_status ON orders(user_id, status).
Čia svarbi stulpelių tvarka. Sudėtinis indeksas veikia kaip telefonų knyga – pirmiausia surūšiuota pagal pavardę, tada pagal vardą. Jei ieškote žmogaus žinodami tik vardą, telefonų knyga jums nepadės. Panašiai ir su indeksais – indeksas (user_id, status) puikiai veiks užklausoms su user_id arba su abiem stulpeliais, bet nepadės užklausai, kuri filtruoja tik pagal status.
Praktinis patarimas: sudėtiniame indekse pirmiausia dėkite stulpelius, kurie turi daugiau unikalių reikšmių (high cardinality), ir kurie dažniau naudojami užklausose. Pavyzdžiui, user_id paprastai turi daug daugiau unikalių reikšmių nei status, kuris gali turėti tik keletą reikšmių (pending, completed, cancelled).
Dar vienas niuansas – jei jūsų užklausa naudoja ORDER BY, indeksas gali padėti ir su rūšiavimu. CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC) ne tik pagreitins filtravimą pagal user_id, bet ir leis PostgreSQL praleisti rūšiavimo žingsnį, kai užklausa baigiasi ORDER BY created_at DESC.
Specialūs indeksų tipai sudėtingesnėms situacijoms
Nors B-tree indeksai tinka daugumai atvejų, PostgreSQL siūlo ir kitų indeksų tipus specifinėms situacijoms. GIN (Generalized Inverted Index) indeksai puikiai tinka pilno teksto paieškai ir JSONB duomenims. Jei turite stulpelį su JSONB duomenimis ir dažnai ieškote tam tikrų reikšmių jame, GIN indeksas gali suteikti milžinišką našumo šuolį.
Pavyzdys: CREATE INDEX idx_products_attributes ON products USING GIN (attributes), kur attributes yra JSONB stulpelis. Dabar užklausos kaip SELECT * FROM products WHERE attributes @> '{"color": "red"}' veiks žaibiškai net su milijonais įrašų.
GiST (Generalized Search Tree) indeksai naudojami geografiniams duomenims (PostGIS), pilno teksto paieškai ir kitoms sudėtingoms duomenų struktūroms. Jei kuriate aplikaciją, kuri ieško vietų pagal koordinates ar skaičiuoja atstumą, GiST indeksas yra būtinas.
BRIN (Block Range Index) indeksai yra įdomus pasirinkimas labai didelėms lentelėms, kurios natūraliai surūšiuotos pagal tam tikrą stulpelį (pavyzdžiui, laiko žymas). Jie užima labai mažai vietos, nes saugo tik santrauką apie duomenų blokus, o ne kiekvieno įrašo informaciją. Tai gali būti puikus pasirinkimas log lentelėms ar istoriniams duomenims.
Hash indeksai anksčiau buvo laikomi eksperimentiniais, bet nuo PostgreSQL 10 versijos jie tapo patikimi ir gali būti efektyvesni už B-tree, kai naudojate tik lygybės operatorių. Tačiau praktikoje jie retai naudojami, nes B-tree yra pakankamai greiti ir daug universalesni.
Partial ir expression indeksai: optimizavimas iki smulkmenų
Kartais jums reikia indekso ne visai lentelei, o tik jos daliai. Pavyzdžiui, jei 95% jūsų užsakymų yra užbaigti (status = ‘completed’) ir jus domina tik aktyvūs užsakymai, nėra prasmės indeksuoti užbaigtų įrašų. Čia praverčia partial indeksai:
CREATE INDEX idx_orders_active ON orders(user_id) WHERE status IN ('pending', 'processing')
Toks indeksas bus daug mažesnis, greitesnis atnaujinti ir vis tiek suteiks maksimalų našumą užklausoms, kurios ieško aktyvių užsakymų. Tai viena iš tų optimizacijų, kurios atrodo smulkmena, bet produkcinėje aplinkoje su milijonais įrašų gali sutaupyti gigabaitus vietos ir žymiai pagerinti našumą.
Expression indeksai leidžia indeksuoti ne tiesiog stulpelio reikšmę, o išraiškos rezultatą. Klasikinis pavyzdys – nepriklausoma nuo registro paieška:
CREATE INDEX idx_users_email_lower ON users(LOWER(email))
Dabar užklausa WHERE LOWER(email) = '[email protected]' galės naudoti indeksą. Be šio indekso PostgreSQL negalėtų naudoti paprasto indekso ant email stulpelio, nes funkcija LOWER() pakeičia reikšmę.
Kitas dažnas atvejis – datos dalių ekstrahavimas: CREATE INDEX idx_orders_year_month ON orders(EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)). Tai leidžia efektyviai grupuoti ar filtruoti duomenis pagal mėnesį ar metus be pilno lentelės skenavimo.
EXPLAIN ANALYZE: jūsų geriausias draugas optimizuojant
Teorija yra puiki, bet kaip žinoti, ar jūsų indeksai tikrai veikia? PostgreSQL turi nuostabų įrankį – EXPLAIN ANALYZE. Tiesiog pridėkite šiuos žodžius prieš savo užklausą, ir gausite detalų planą, kaip PostgreSQL vykdo užklausą, kiek laiko užtrunka kiekvienas žingsnis ir ar naudojami indeksai.
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]'
Rezultate ieškokite „Seq Scan” (nuoseklus skenavimas) – tai blogas ženklas didelėse lentelėse. Norite matyti „Index Scan” arba „Index Only Scan”. Taip pat atkreipkite dėmesį į „cost” ir „actual time” reikšmes – jos parodo, kiek užklausa tikrai kainuoja.
Vienas dalykas, kuris dažnai suklaidina – PostgreSQL kartais pasirenka naudoti Seq Scan net kai yra indeksas. Tai ne klaida – duomenų bazė yra protinga ir žino, kad jei užklausa grąžins didelę dalį lentelės įrašų, greičiau bus perskaityti visą lentelę iš eilės, nei šokinėti per indeksą. Indeksai efektyviausi, kai filtruoja nedidelę dalį duomenų.
Dar vienas svarbus dalykas – statistika. PostgreSQL saugo statistiką apie duomenų pasiskirstymą lentelėse ir naudoja ją planuojant užklausas. Jei statistika pasenusi, duomenų bazė gali priimti blogus sprendimus. Reguliariai vykdykite ANALYZE komandą, ypač po didelių duomenų pakeitimų. Dar geriau – įsitikinkite, kad autovacuum procesas veikia (jis turėtų būti įjungtas pagal nutylėjimą).
Indeksų priežiūra ir dažniausios klaidos
Sukurti indeksą – tai tik pusė darbo. Indeksai reikalauja priežiūros. PostgreSQL naudoja MVCC (Multi-Version Concurrency Control) sistemą, kuri reiškia, kad ištrynus ar atnaujinus įrašą, seni duomenys iš karto nėra pašalinami. Laikui bėgant indeksai gali „išsipūsti” su mirusiomis nuorodomis.
Čia į pagalbą ateina VACUUM ir REINDEX komandos. VACUUM valo mirusius įrašus, o REINDEX perkuria indeksą nuo nulio. Tačiau REINDEX užrakina lentelę, todėl produkcinėje aplinkoje geriau naudoti REINDEX CONCURRENTLY (nuo PostgreSQL 12 versijos).
Dažniausia klaida, kurią matau – per daug indeksų. Kiekvienas indeksas turi kainą: užima vietą, lėtina įrašymo operacijas, turi būti atnaujinamas. Jei lentelėje yra 10 indeksų, bet 8 iš jų niekada nenaudojami, jūs tiesiog švaistote resursus. PostgreSQL turi sisteminę lentelę pg_stat_user_indexes, kuri rodo, kiek kartų kiekvienas indeksas buvo naudotas. Periodiškai peržiūrėkite ją ir ištrinkite nenaudojamus indeksus.
Kita klaida – neatsižvelgimas į indeksų dydį. Jei jūsų indeksas yra didesnis už RAM atmintį, jo efektyvumas krenta, nes PostgreSQL turi skaityti duomenis iš disko. Kartais geriau turėti kelis mažesnius, tikslingus indeksus nei vieną milžinišką sudėtinį indeksą su 5-6 stulpeliais.
Ir galiausiai – neužmirškite unique indeksų. Jie ne tik užtikrina duomenų vientisumą, bet ir veikia kaip įprasti indeksai, pagreitindami užklausas. CREATE UNIQUE INDEX idx_users_email_unique ON users(email) ir užtikrina, kad nebūtų dublikatų, ir pagreitina paiešką pagal email.
Kai indeksai tampa strategija, o ne taktika
Optimizuojant duomenų bazę, svarbu suprasti, kad indeksai nėra magiškas sprendimas, kurį galima pritaikyti vienodai visur. Kiekviena aplikacija unikali, kiekviena lentelė turi savo naudojimo šablonus, kiekviena užklausa – savo poreikius.
Geriausias būdas valdyti indeksus – tai stebėti savo aplikacijos elgesį produkcinėje aplinkoje. Naudokite įrankius kaip pg_stat_statements, kurie rodo lėčiausias užklausas. Analizuokite, kokios užklausos vykdomos dažniausiai. Testuokite su realistiškais duomenų kiekiais – tai, kas veikia su 1000 įrašų, gali visiškai nevykti su milijonu.
Nepamirškite, kad indeksai – tai kompromisas tarp skaitymo ir rašymo greičio. Jei jūsų aplikacija daugiau rašo nei skaito (pvz., logging sistema), per daug indeksų gali pakenkti. Jei daugiau skaito (pvz., analitinė sistema), indeksai yra kritiškai svarbūs.
Ir paskutinis patarimas – dokumentuokite savo indeksus. Po metų niekas (įskaitant jus patį) neprisimins, kodėl buvo sukurtas konkretus sudėtinis indeksas su keturiais stulpeliais ir WHERE sąlyga. Komentaras SQL faile ar dokumentacijoje gali sutaupyti daug laiko ateityje: COMMENT ON INDEX idx_orders_complex IS 'Optimizuoja mėnesines ataskaitas pagal aktyvius vartotojus'.
PostgreSQL indeksai – tai galingas įrankis, bet kaip ir bet kuris įrankis, jis turi būti naudojamas protingai. Pradėkite nuo pagrindų, eksperimentuokite, matuokite rezultatus ir laipsniškai tobulinkite savo strategiją. Jūsų vartotojai pastebės skirtumą, net jei nežinos, kad už greitesnės aplikacijos slypi kruopščiai sukurti ir prižiūrimi indeksai.
