u loše formulisani SQL upiti Ovo su jedni od najčešćih razloga zašto aplikacija radi sporo pri radu s velikim relacijskim bazama podataka poput MySQL-a, PostgreSQL-a, SQL Servera, Oracle-a ili DB2-a. Iako sada imamo moćne servere i elastične oblake, neefikasni upiti će vas na kraju skupo koštati. viši troškovi infrastrukture, veća latencija i lošije korisničko iskustvo.
Optimizacija SQL upita u velikim bazama podataka ide daleko dalje od pukog "dodavanja indeksa i to je to". To uključuje Razumijevanje načina razmišljanja optimizatora upitaKako se podaci pohranjuju, koje obrasce pristupa vaša aplikacija koristi i koje kombinovane tehnike vam omogućavaju da smanjite korištenje ulazno/izlaznih operacija, CPU-a i memorije. U sljedećim odjeljcima ćemo detaljno i s primjerima pregledati, Najefikasnije strategije za maksimalno iskorištavanje vaših relacijskih baza podataka.
Šta je zapravo optimizacija SQL upita i zašto je važna?
Optimizirajte SQL upit To znači prepisivanje (i prilagođavanje konteksta: indeksa, statistike, dizajna) tako da SQL vraća isti rezultat uz trošenje manje resursa i za kraće vrijeme. SQL sintaksa omogućava mnogo načina izražavanja iste stvari, ali se ne izvršavaju svi jednako brzo, posebno kada postoje milioni redova ili složenih spojeva.
Kada programer shvati kako nešto funkcioniše planer upita Pomoću vašeg engine-a (PostgreSQL, MySQL, SQL Server, Oracle, DB2, itd.), možete pisati upite koji bolje koriste indekse, smanjuju nepotrebna čitanja i minimiziraju skupe operacije poput sortiranja, sekvencijalnog skeniranja ili ponavljajućih koreliranih podupita.
Međutim, važno je biti jasno da Optimizacija upita nije jedini faktor performansiDizajn sheme (normalizacija, primarni i strani ključevi, tipovi podataka), arhitektura (replike, particije, keš memorije) i sama infrastruktura imaju značajan utjecaj. Ali čak i uz pristojnu arhitekturu, jedan loše optimiziran upit može biti veliki problem. brutalno usko grlo.
Među prednostima rada u konsultacijama, ističu se sljedeće: poboljšanje ukupnog učinka (više zahtjeva obrađeno u kraćem vremenu), smanjenje troškova u oblaku (manje CPU-a i diska, manje veličine instanci) i lakše korisničko iskustvo smanjenjem vremena čekanja u oglasima, pretragama i izvještajima. Nadalje, jasni i dobro strukturirani upiti su lakše održavanje i otklanjanje grešaka, nešto što se izuzetno cijeni kada projekat raste.
U aplikacijama koje zaista teže skaliranju, kontinuirana optimizacija upita postaje ponavljajući zadatak: pratiti, detektovati, mjeriti, prilagođavati i ponovo mjeritiTo nije jednokratna akcija, već proces.
Praktičan primjer: isti upit, vrlo različite performanse
Da biste svoje ideje smirili, zamislite sto narudžbe s više od 20 miliona zapisa Na e-trgovini želimo preuzeti završene narudžbe kupca iz posljednjih 30 dana i bez mnogo razmišljanja mogli bismo napisati nešto poput ovoga:
SELECT * FROM pedidos
WHERE cliente_id = 456
AND LOWER(estado) = 'completado'
AND fecha_creacion BETWEEN NOW() - INTERVAL '30 days' AND NOW();
Ovaj upit vraća ono što želimo, ali sa stanovišta performansi je malo kompliciran: koristi ODABIR *, primjenjuje funkciju (LOWER) na koloni filtera i kombinuje datume sa izrazima koji mogu ometati upotrebu indeksa. Ako, pored toga, odgovarajući indeksi ne postoje na client_id, status ili creation_date, motor će biti primoran skenirati veliki dio tabele.
Praktične posljedice su jasne: Preneseno je više podataka nego što je potrebnoViše posla za mapiranje neiskorištenih kolona u pozadini, puno čitanja s diska i vrijeme izvršavanja koje u vrlo velikim tabelama može naglo porasti na nekoliko sekundi, što utiče na cijeli sistem kada se pokreće više puta.
Isto pitanje, formulisano inteligentnije, moglo bi izgledati ovako:
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 456
AND estado = 'Completado'
AND fecha_creacion >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY fecha_creacion DESC
LIMIT 100;
Tu smo odabir samo potrebnih kolonaizbjegavanje funkcija na koloni statusa, pojednostavljenje uvjeta datuma i ograničavanje broja redova. S dobro dizajniranim indeksima (na primjer, INDEX(cliente_id, fecha_creacion) i jedan o estado (ako ima visoku kardinalnost), mehanizam može koristiti skeniranje indeksa i riješiti upit u milisekunde umjesto sekundi.
Ovaj kontrast ilustruje ključnu ideju: Nije dovoljno da upit "radi"Morate se brinuti o tome kako će se pokrenuti kada tabela više nema stotine redova, već milione.
Indeksi: glavna poluga za ubrzavanje pretraga
u Indeksi su najmoćniji alat za ubrzavanje upita u velikim bazama podataka. Umjesto pregledavanja cijele tabele red po red (sekvencijalno skeniranje ili Seq Scan), mehanizam koristi pomoćne strukture (obično B-stabla, R-stabla ili heševe, ovisno o tipu podataka i mehanizmu) koje omogućavaju direktan prelazak na kandidatske redove.
U MySQL-u, na primjer, najčešće strukture su drveće B za indekse tipova PRIMARY KEY, UNIQUE, INDEX y FULLTEXT, dok prostorni indeksi koriste R stabla a tabele u memoriji mogu povlačiti podatke iz indeksa na osnovu hashSvaki od njih je optimiziran za određeni obrazac pristupa.
Međutim, ne radi se o tome da se svemu doda indeks. Svaki dodatni indeks Zauzima prostor na disku i usporava umetanje, ažuriranje i brisanje.jer motor mora održavati strukturu sinhroniziranom. Trik je u pronalaženju ravnoteža između broja indeksa i vremena odziva, s fokusom na upite kritičkog čitanja.
Među najčešćim tipovima indeksa u relacijskim mehanizmima nalazimo one od primarni ključ (jedinstveno identificiraju svaki red i ne dozvoljavaju null vrijednosti), one od strani ključ (referenca PK druge tabele), the jedinstveni indeksi (garantuju jedinstvenost, ali dozvoljavaju null vrijednosti) i kompozitni indeksi na više kolona, što je veoma korisno prilikom filtriranja ili sortiranja po više polja istovremeno.
Također postoje scenariji u kojima je korisno koristiti indeksi sa ponovljenim vrijednostima (za ubrzanje pretrage u nejedinstvenim kolonama) ili indeksi punog teksta (FULLTEXT u MySQL-u, na primjer) za poboljšanje pretraga u dugim tekstualnim poljima. Od verzije MySQL 8.0.13, ova polja se mogu kreirati funkcionalni indeksiTo jest, na osnovu rezultata izraza ili funkcije (na primjer, YEAR(fecha_pago)), što otvara vrata naprednim optimizacijama.
Indekse u MySQL-u možemo kreirati različitim naredbama: CREATE INDEX, dodajući ih kasnije; ALTER TABLEza izmjenu postojeće tabele; ili direktno u definiciji sa CREATE TABLEU sva tri slučaja, dozvoljeni su jednostavni, složeni, jedinstveni i prefiksni indeksi (samo prvih N znakova indeksa VARCHAR) ili FULLTEXT, ovisno o dizajnu koji nam je potreban.
Korišćenje prefiksni indeksi Ovo je korisno kada imamo dugačke nizove znakova, ali relativno mali broj znakova je dovoljan da se razlikuju gotovo sve vrijednosti. Na ovaj način smanjujemo veličinu indeksa bez prevelikog gubitka selektivnosti, što je vrlo korisno u kolonama poput imena kupaca gdje možemo indeksirati, na primjer, prvih 25 znakova umjesto cijelog polja.
Odaberite samo kolone koje su vam potrebne
Zlostavljanje ODABIR * To je jedna od najčešćih loših navika u SQL-u. Praktična je tokom razvoja, ali u produkciji postaje teret: Svaka dodatna kolona podrazumijeva više bajtova koji putuju iz baze podataka ovisno o vašoj aplikaciji, više memorije na klijentu i više posla deserijalizacije.
Kada tabela sadrži velike kolone (BLOB-ove, velike JSON datoteke, ogromne tekstualne datoteke, binarne avatare itd.), njihovo nepotrebno uključivanje povećava korištenje I/O i RAM memorije. Nadalje, u mehanizmima poput PostgreSQL-a, ograničavanje broja kolona omogućava bolje performanse. Skeniranje samo indeksa, gdje baza podataka odgovara iz indeksa bez odlaska na heap, ali ovo funkcioniše samo ako se sve kolone koje tražite nalaze u indeksu.
Klasičan primjer: stol users sa kolonama kao što su ID, e-pošta, heš_lozinke, avatar, kreirano_na, zadnja_prijavaAko bacite SELECT * FROM users WHERE email = 'juan@example.com';Dobit ćete heš lozinke i binarni avatar čak i ako želite prikazati samo e-mail adresu i datum posljednje prijave. Mnogo je bolje da to jednostavno zatražite. id, email, last_login.
Uvijek radite sa eksplicitne liste kolona Čini vaše upite jasnijima, štiti vas od promjena sheme (dodavanje kolone ne kvari ništa) i dramatično smanjuje potrošnju resursa u velikim tabelama ili paginiranim listama, pomažući... upravljati velikim količinama podataka.
JOIN-ovi, podupiti i CTE-ovi: kako pravilno strukturirati složene upite
u korelirani podupiti (Oni koji se izvršavaju jednom za svaki red vanjskog upita) mogu izgledati elegantno na papiru, ali u praksi postaju usko grlo performansi kako tabele rastu. Svaki red u glavnoj tabeli pokreće dodatno izvršavanje podupita, što rezultira astronomskim brojem operacija.
Kad god je to moguće, poželjno je transformirati ove podupite u dobro indeksirani JOIN-ovi ili u CTE-ovi (Uobičajeni tabelarni izrazi) koji logiku razlažu na jasne korake. Optimizator obično mnogo bolje obrađuje kombinaciju tabela nego skup složenih podupita.
Na primjer, da biste dobili proizvode zajedno s nazivom njihove kategorije, umjesto izvršavanja podupita u SELECT Efikasnije je koristiti JOIN u odnosu na tabelu kategorija. Ako su stupci spajanja indeksirani (na primjer, productos.categoria_id y categorias.id), motor može riješiti spajanje uz vrlo niske troškove čak i na velikim tablicama.
u CTE-ovi (WITH ... AS (...)Ovo je posebno korisno kod upita za izvještavanje, složenih agregacija i postupne logike. Iako ne poboljšavaju uvijek performanse same po sebi, pomažu planeru i, prije svega, poboljšavaju čitljivost, olakšavajući daljnje optimizacije poput dodavanja specifičnih indeksa ili materijalizacije međurezultata.
Paginacija i LIMIT za upravljanje velikim količinama
U stvarnim aplikacijama, vraćanje hiljada redova odjednom gotovo nikada nema smisla sa stanovišta korisničkog iskustva. Popis proizvoda, historija narudžbi ili zapis događaja se obično čitaju stranicu po stranicu, tako da... ograniči broj vraćenih redova To je osnovni uslov za penjanje.
Klasični pristup koristi LIMIT y OFFSET (npr. LIMIT 10 OFFSET 20 da biste otišli na „treću“ stranicu). Lako ga je implementirati i razumjeti, ali ima ozbiljan problem: motor mora Pređite preko svih redova prije OFFSET-a na isti način.iako vraća samo posljednjih 10. U vrlo velikim tabelama, visoke vrijednosti OFFSET-a rezultiraju sve lošijim vremenima odziva.
Kada se radi sa stotinama hiljada ili milionima redova, obično je bolje Paginacija skupa ključeva ili paginacija zasnovana na pretraživanjuU ovom pristupu, umjesto da bazi podataka kažete "preskoči 1000 redova", kažete joj "vrati sljedećih N zapisa počevši od ove sortirane vrijednosti ključa", koristeći uslove tipa WHERE fecha_creacion < <última_fecha_vista> sa a ORDER BY dosljedan.
Ova tehnika omogućava motoru da iskoristi prednost direktnog indeksa na sortiranoj koloni (na primjer, fecha_creacion o id), izbjegavajući troškove prelaska preko međustranica. Nadalje, olakšava paginaciju stabilan na insercije ili delecije između stranica, nešto što OFFSET ne garantuje.
Zauzvrat, paginacija skupa ključeva ima nedostatak što Nije trivijalno skočiti na stranicu 37 Bez dodatnih informacija, budući da se radi unaprijed od logičkog kursora (posljednji preuzeti ID ili datum). Zato mnogi sistemi kombiniraju oba pristupa ovisno o funkcionalnim potrebama.
Izbjegavajte funkcije u filtriranim kolonama i dobro koristite klauzulu WHERE
Vrlo čest izvor gubitka performansi je primjena funkcije na kolonama koje učestvuju u filterimaIzrazi poput LOWER(nombre), DATE(fecha) o CAST(campo AS ...) unutar klauzule WHERE Obično sprečavaju optimizator da koristi indeks te kolone.
Umjesto toga, bolje je normalizujte podatke prilikom umetanja ili ažuriranja (na primjer, spremanje e-mailova malim slovima, statusi s homogenim kodiranjem) i transformirati ulazne vrijednosti kako bi odgovarale tom formatu, umjesto primjene funkcije na kolonu u svakom poređenju.
Također vrijedi obratiti pažnju na samu klauzulu. WHERE kako bi bio što selektivniji. Iako redoslijed uvjeta nema uvijek direktan utjecaj (optimizator ih obično preuređuje), korisno je imati dobro indeksirani predikati i jednostavna poređenja umjesto skupih uzoraka poput LIKE '%texto'što obično prisiljava na potpuno skeniranje.
Kada trebate ukloniti duplikate, razmislite o tome da li DISTINCT ili ako bi se upit mogao redizajnirati sa JOINs preciznija ili jedinstvena ograničenja u modelu. Oba DISTINCT como UNION obično uključuju operacije sortiranja ili grupiranjakoji su među najskupljima u planu implementacije.
Održavanje indeksa i statistike radi pomoći optimizatoru
Moderni mehanizmi baza podataka oslanjaju se na interna statistika Da se procijeni koliko redova ispunjava svaki uslov, koji su indeksi najprikladniji i kojim redoslijedom spojiti tabele. Ako su ove statistike zastarjele, planer može donositi vrlo loše odluke i generirati neefikasne planove izvršenja.
Zato je važno periodično izvršavati naredbe poput ANALYZE (ili njihove specifične varijante u svakom motoru) za Osvježi statistiku nakon velikih učitavanjamigracije ili velike količine INSERT, UPDATE y DELETEU PostgreSQL-u, na primjer, automatsko vakuumiranje se obično obrađuje automatski, ali nakon velikog uvoza može biti korisno pokrenuti ANALYZE priručnik.
U MySQL-u imamo naredbe poput ANALYZE TABLE, koji analizira i pohranjuje distribuciju ključeva kako bi pomogao optimizatoru da odluči o redoslijedu i upotrebi indeksa u JOINsOsim toga, OPTIMIZE TABLE dozvolite defragmentiranje tabela, preuređivanje i ažuriranje indeksa, nešto što se preporučuje u tabelama koje su pretrpjele mnoge promjene.
Da biste provjerili da li motor koristi indekse kako se očekuje, nema ništa bolje od povlačenja iz EXPLAIN o EXPLAIN ANALYZEOvi alati nam pokazuju procijenjeni plan (a u nekim pretraživačima i stvarni plan s vremenima i pročitanim redovima) i pokazuju da li se vrši sekvencijalno skeniranje (ALL u MySQL-u, na primjer) ili ako je Index Scankoliko se redova očekuje i koliko se zapravo odigra.
Učenje čitanja ovih planova je možda jedna od najvrijednijih vještina za svakoga ko želi optimizirati baze podataka: Omogućava vam otkrivanje uskih grla, beskorisnih indeksa, loše selektivnih filtera i loše uređenih spajanja. mnogo prije nego što problem dođe do proizvodnje.
Indeksi punog teksta, regularni izrazi i posebni scenariji
kada radite sa velika tekstualna polja (opisi, bogati HTML sadržaj, komentari itd.), pretrage sa LIKE '%palabra%' Ovo brzo postaje nepraktično za velike tabele. Za ove slučajeve, pretraživači poput MySQL-a nude indekse tipa FULLTEXT i operateri kao što su MATCH() AGAINST()što omogućava mnogo efikasnije i relevantnije pretrage.
con FULLTEXT Možete birati između različitih načina rada: prirodni jezik, boolean (s operatorima) +, -, *(navodnici za tačne fraze, itd.) ili proširenje upita proširiti povezane rezultate. Ovo vam omogućava da izgradite prilično moćne interne pretraživače bez napuštanja baze podataka.
Postoje napredniji scenariji gdje tekst uključuje, na primjer, ugrađene HTML oznake. U tom slučaju, možda će biti potrebno kombinirati indeks. FULLTEXT sa funkcijama poput REGEXP_REPLACE za čišćenje oznaka prilikom poređenja tačnih fraza. Tipična strategija je prvo filtrirajte koristeći indeks punog teksta a zatim primijenite regularni izraz u drugom uvjetu kako biste suzili rezultat na tačan iznos bez skeniranja cijele tabele.
Drugi motori, kao što je Oracle, omogućavaju korištenje regularni tabelarni izrazi Ove funkcije pomažu optimizatoru da ubacuje predikate unutar prikaza i što brže smanji količinu međupodataka. Ovaj pristup je veoma koristan pri radu sa mnogo ugniježđenih prikaza ili složenih definicija u kolaborativnim radnim okruženjima.
Dodatne najbolje prakse: parametri, materijalizovani prikazi i dijeljenje upita
Pored indeksa i planova implementacije, postoji niz dobre međusektorske prakse koji doprinose i performansama i sigurnosti. Jedan od najvažnijih je koristite parametrizirane upite Umjesto spajanja stringova za izgradnju dinamičkog SQL-a, ovo smanjuje rizik od SQL injekcije i omogućava bazi podataka da ponovo koristi planove izvršavanja za upite s istom strukturom.
U sistemima sa vrlo teški i ponavljajući upiti (kontrolne ploče, izvršni izvještaji, agregirani proračuni), materijalizovani pogledi Oni su odličan saveznik. Za razliku od običnog prikaza, oni fizički pohranjuju rezultat upita, postajući vrsta unaprijed izračunate tabele koja se može vrlo brzo indeksirati i upitati.
PostgreSQL, Oracle i SQL Server (sa svojim indeksiranim prikazima) izvorno podržavaju materijalizirane prikaze, s raznim opcijama osvježavanja (ručno, zakazano, pa čak i automatsko u nekim slučajevima). U MySQL-u, budući da ne postoji direktna podrška, ovo ponašanje se obično emulira s tablicama i procesima koji periodično regeneriraju podatke, često putem okidača ili zakazanih zadataka.
Kada upit spaja previše tabela ili se oslanja na složeni mozaik prikaza, druga valjana strategija je podijelite upit u nekoliko korakaOvo se prevodi kao pokretanje početnog upita za dobijanje manjeg skupa (npr. relevantnih ID-ova), a zatim pokretanje dodatnih upita za kompletiranje informacija. Ovaj pristup treba koristiti razumno, jer može povećati broj pristupa bazi podataka, ali u nekim slučajevima drastično smanjuje složenost plana i veličinu međuskupova.
Tokom ovog procesa, alati za praćenje kao što su pg_stat_statements, PgHero, PMM, Query Store, New Relic ili Datadog Mogu vam pomoći da brzo identifikujete koji su upiti sporiji ili se češće izvršavaju, tako da možete dati prioritet optimizaciji tamo gdje je to zaista važno.
Optimizirajte SQL upite uz pomoć umjetne inteligencije
Posljednjih godina pojavili su se alati zasnovani na vještačkoj inteligenciji koji analiziraju vaše upite i shemu baze podataka kako bi predložili poboljšanja: prijedloge indeksa, prepisivanje upita, promjene u strukturi tabela itd. Imena poput EverSQL, DBScoop, PGAnalyzer ili Redshift Advisor postala su popularna u profesionalnim okruženjima.
Ova rješenja mogu pregledati velike količine zapisa upita, uporediti ih sa statistikama, planovima izvršenja i metrikama performansi, i na osnovu toga... otkriti neefikasne obrasce ili uska grla što bi nam na prvi pogled promaklo. Oni također pomažu u procjeni hipotetičkog utjecaja stvaranja ili ukidanja određenih indeksa.
Međutim, važno ih je shvatiti kao podrška, a ne kao zamjena To zavisi od vašeg znanja SQL-a i razumijevanja vaše aplikacije. Možda ćete dobiti prijedlog indeksa koji, u teoriji, ubrzava određeni upit, ali značajno pogoršava pisanje u kritični modul. Bez poslovnog konteksta, alat ne zna šta je najvažnije.
Idealna kombinacija je tim koji savladava principe optimizacije (planove, indekse, normalizaciju, obrasce pristupa) i koristi vještačku inteligenciju za... ubrzati analizu i potvrditi hipotezeda ne donosimo slijepe odluke.
Kada internalizirate cijeli ovaj skup tehnika - pažljiv dizajn indeksa, minimalan odabir kolona, inteligentnu upotrebu JOIN-ova i CTE-ova, efikasnu paginaciju, redovno održavanje statistike, iskorištavanje materijaliziranih prikaza, pa čak i podršku od AI alata - Velike baze podataka više nisu nekontrolisano čudovište i postaju predvidljiva i skalabilna komponenta vaše arhitekture, sposobna da raste s vašim poslovanjem bez narušavanja korisničkog iskustva ili budžeta za infrastrukturu.
