Las loše formulirani SQL upiti Ovo su jedni od najčešćih razloga zašto aplikacija sporo radi pri radu s velikim relacijskim bazama podataka poput MySQL-a, PostgreSQL-a, SQL Servera, Oraclea ili DB2. Iako sada imamo moćne poslužitelje i elastične oblake, neučinkoviti upiti će vas u konačnici 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 kombinirane tehnike vam omogućuju smanjenje korištenja ulazno/izlaznih operacija, procesora i memorije. U sljedećim odjeljcima detaljno ćemo i s primjerima pregledati Najučinkovitije strategije za maksimalno iskorištavanje vaših relacijskih baza podataka.
Što 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 u kraćem vremenu. SQL sintaksa omogućuje mnogo načina izražavanja iste stvari, ali se ne izvršavaju svi jednako brzo, posebno kada postoje milijuni redaka ili složenih spojeva.
Kada programer shvati kako nešto funkcionira 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, predmemorije) i sama infrastruktura imaju značajan utjecaj. Ali čak i s pristojnom arhitekturom, jedan loše optimiziran upit može biti veliki problem. brutalno usko grlo.
Među prednostima rada u konzultacijama 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 glatko korisničko iskustvo smanjenjem vremena čekanja u popisima, pretragama i izvješćima. Nadalje, jasni i dobro strukturirani upiti su lakše održavanje i otklanjanje pogrešaka, nešto što se izuzetno cijeni kada projekt raste.
U aplikacijama koje zaista teže skaliranju, kontinuirana optimizacija upita postaje ponavljajući zadatak: pratiti, otkrivati, mjeriti, prilagođavati i ponovno mjeritiTo nije jednokratna akcija, već proces.
Praktičan primjer: isti upit, vrlo različite performanse
Da biste svoje ideje smirili, zamislite stol narudžbe s više od 20 milijuna zapisa Na web-mjestu za e-trgovinu želimo dohvatiti dovršene narudžbe kupca iz posljednjih 30 dana i bez puno 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 s gledišta performansi je malo kompliciran: koristi IZABERI *, primjenjuje funkciju (LOWER) na stupcu filtera i kombinira datume s izrazima koji mogu ometati korištenje indeksa. Ako, osim toga, ne postoje prikladni indeksi na client_id, status ili creation_date, motor će biti prisiljen skenirati veliki dio tablice.
Praktične posljedice su jasne: Preneseno je više podataka nego što je potrebnoViše posla za mapiranje neiskorištenih stupaca u pozadini, puno čitanja s diska i vrijeme izvršavanja koje u vrlo velikim tablicama može naglo porasti na nekoliko sekundi, što utječe na cijeli sustav kada se pokreće više puta.
Isto pitanje, formulirano 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 stupacaizbjegavanje funkcija na stupcu statusa, pojednostavljenje uvjeta datuma i ograničavanje broja redaka. S dobro osmišljenim 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 ilustrira ključnu ideju: Nije dovoljno da upit "radi"Morate se brinuti o tome kako će se pokrenuti kada tablica više nema stotine redaka, već milijune.
Indeksi: glavna poluga za ubrzavanje pretraživanja
The Indeksi su najmoćniji alat za ubrzavanje upita u velikim bazama podataka. Umjesto pregledavanja cijele tablice redak po redak (sekvencijalno skeniranje ili Skeniranje sekvenci), mehanizam koristi pomoćne strukture (obično B-stabla, R-stabla ili hashove, ovisno o vrsti podataka i mehanizmu) koje omogućuju izravno skakanje na retke kandidate.
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 tablice u memoriji mogu izvlačiti podatke iz indeksa na temelju smjesaSvaki je optimiziran za određeni obrazac pristupa.
Međutim, ne radi se o indeksiranju svega. Svaki dodatni indeks Zauzima prostor na disku i usporava umetanje, ažuriranje i brisanje.jer motor mora održavati strukturu sinkroniziranom. Trik je u pronalaženju ravnoteža između broja indeksa i vremena odziva, s naglaskom na upite kritičkog čitanja.
Među najčešćim vrstama indeksa u relacijskim programima nalazimo one od primarni ključ (jedinstveno identificiraju svaki redak i ne dopuštaju null vrijednosti), one od strani kljuc (referenca PK druge tablice), the jedinstveni indeksi (jamče jedinstvenost, ali dopuštaju null vrijednosti) i kompozitni indeksi na više stupaca, vrlo korisno pri filtriranju ili sortiranju po više polja istovremeno.
Također postoje scenariji u kojima je korisno koristiti indeksi s ponovljenim vrijednostima (za ubrzanje pretraživanja u stupcima koji nisu jedinstveni) ili indeksi punog teksta (FULLTEXT u MySQL-u, na primjer) za poboljšanje pretraživanja u dugim tekstualnim poljima. Od MySQL-a 8.0.13, oni se mogu kreirati funkcionalni indeksiTo jest, na rezultat 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 tablice; ili izravno u definiciji s CREATE TABLEU sva tri slučaja dopušteni su jednostavni, složeni, jedinstveni i prefiksni indeksi (samo prvih N znakova indeksa VARCHAR) Ili FULLTEXT, ovisno o dizajnu koji nam je potreban.
El uso prefiksni indeksi Ovo je korisno kada imamo duge nizove, ali relativno mali broj znakova dovoljan je za razlikovanje gotovo svih vrijednosti. Na taj način smanjujemo veličinu indeksa bez prevelikog gubitka selektivnosti, što je vrlo korisno u stupcima poput imena kupaca gdje možemo indeksirati, na primjer, prvih 25 znakova umjesto cijelog polja.
Odaberite samo stupce koji su vam potrebni
Zlostavljanje IZABERI * To je jedna od najčešćih loših navika u SQL-u. Praktična je tijekom razvoja, ali u produkciji postaje teret: Svaki dodatni stupac 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 tablica sadrži velike stupce (BLOB-ove, velike JSON datoteke, ogromne tekstualne datoteke, binarne avatare itd.), njihovo nepotrebno uključivanje povećava korištenje I/O i RAM-a. Nadalje, u tražilicama poput PostgreSQL-a, ograničavanje broja stupaca omogućuje bolje performanse. Skeniranje samo indeksagdje baza podataka odgovara iz indeksa bez odlaska na hrpu, ali to funkcionira samo ako su svi stupci koje tražite u indeksu.
Klasičan primjer: stol users sa stupcima poput ID, e-pošta, sažetak_lozinke, avatar, kreirano_na, zadnja_prijavaAko baciš SELECT * FROM users WHERE email = 'juan@example.com';Dobit ćete hash lozinke i binarni avatar čak i ako želite prikazati samo e-poštu i datum zadnje prijave. Mnogo je bolje da to jednostavno zatražite. id, email, last_login.
Uvijek radite s eksplicitne liste stupaca Čini vaše upite jasnijima, štiti vas od promjena sheme (dodavanje stupca ne prekida ništa) i dramatično smanjuje potrošnju resursa u velikim tablicama ili paginiranim popisima, pomažući upravljati velikim količinama podataka.
JOIN-ovi, podupiti i CTE-ovi: kako pravilno strukturirati složene upite
Las korelirani podupiti (Oni koji se izvršavaju jednom za svaki redak vanjskog upita) mogu se činiti elegantnima na papiru, ali u praksi postaju usko grlo performansi kako tablice rastu. Svaki redak u glavnoj tablici 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 tablični izrazi) koji logiku raščlanjuju na jasne korake. Optimizator obično puno bolje obrađuje kombinaciju tablica nego gnijezdo složenih podupita.
Na primjer, da biste dobili proizvode zajedno s nazivom njihove kategorije, umjesto izvršavanja podupita u SELECT Učinkovitije je koristiti JOIN u odnosu na tablicu 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.
Las CTE-ovi (WITH ... AS (...)Ovo je posebno korisno u upitima za izvještavanje, složenim agregacijama i detaljnoj logici. Iako sami po sebi ne poboljšavaju uvijek performanse, pomažu planeru i, prije svega, poboljšavaju čitljivost, olakšavajući daljnje optimizacije poput dodavanja specifičnih indeksa ili materijaliziranja međurezultata.
Paginacija i LIMIT za upravljanje velikim količinama
U stvarnim aplikacijama, vraćanje tisuća redaka odjednom gotovo nikad nema smisla s gledišta korisničkog iskustva. Popis proizvoda, povijest narudžbi ili zapisnik događaja obično se čita stranica po stranica, pa ograniči broj vraćenih redaka To je osnovni uvjet za penjanje.
Klasični pristup koristi LIMIT y OFFSET (na primjer, LIMIT 10 OFFSET 20 da biste otišli na „treću“ stranicu). Lako ga je implementirati i razumjeti, ali ima ozbiljan problem: motor mora Prođite kroz sve retke prije OFFSET-a na isti način.iako vraća samo posljednjih 10. U vrlo velikim tablicama, visoke vrijednosti OFFSET-a rezultiraju sve lošijim vremenima odziva.
Prilikom rada sa stotinama tisuća ili milijunima redaka, obično je bolje Paginacija skupa ključeva ili paginacija temeljena na pretraživanjuU ovom pristupu, umjesto da bazi podataka kažete "preskoči 1000 redaka", kažete joj "vrati sljedećih N zapisa počevši od ove sortirane vrijednosti ključa", koristeći uvjete tipa WHERE fecha_creacion < <última_fecha_vista> s ORDER BY dosljedan.
Ova tehnika omogućuje tražilici da iskoristi prednost izravnog indeksa na sortiranom stupcu (na primjer, fecha_creacion o id), izbjegavajući troškove pregledavanja međustranica. Nadalje, olakšava paginaciju stabilan na insercije ili delecije između stranica, nešto što OFFSET ne jamči.
Zauzvrat, paginacija skupa ključeva ima nedostatak u tome što Nije lako skočiti na stranicu 37 Bez dodatnih informacija, budući da radi naprijed od logičkog kursora (zadnji dohvaćeni ID ili datum). Zato mnogi sustavi kombiniraju oba pristupa ovisno o funkcionalnim potrebama.
Izbjegavajte funkcije u filtriranim stupcima i dobro koristite klauzulu WHERE
Vrlo čest izvor gubitka performansi je primjena funkcije na stupcima koji sudjeluju u filterimaIzrazi poput LOWER(nombre), DATE(fecha) o CAST(campo AS ...) unutar klauzule WHERE Obično sprječavaju optimizator da koristi indeks tog stupca.
Umjesto toga, bolje je normalizirati podatke prilikom umetanja ili ažuriranja (na primjer, spremanje e-poruka malim slovima, statusi s homogenim kodiranjem) i transformirati ulazne vrijednosti kako bi odgovarale tom formatu, umjesto primjene funkcije na stupac u svakoj usporedbi.
Također vrijedi obratiti pozornost na samu klauzulu. WHERE kako bi bio što selektivniji. Iako redoslijed uvjeta nema uvijek izravan utjecaj (optimizator ih obično preuređuje), korisno je imati dobro indeksirani predikati i jednostavne usporedbe umjesto skupih uzoraka poput LIKE '%texto'što obično prisiljava na potpuno skeniranje.
Kada trebate ukloniti duplikate, razmislite o tome je li DISTINCT ili ako bi se upit mogao redizajnirati s JOINs preciznija ili jedinstvena ograničenja u modelu. Oboje DISTINCT kao UNION obično uključuju operacije sortiranja ili grupiranjakoji su među najskupljima u planu provedbe.
Održavanje indeksa i statistike za pomoć optimizatoru
Moderni mehanizmi baza podataka oslanjaju se na interna statistika Procijeniti koliko redaka zadovoljava svaki uvjet, koji su indeksi najprikladniji i kojim redoslijedom spojiti tablice. Ako su te statistike zastarjele, planer može donositi vrlo loše odluke i generirati neučinkovite 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 korištenju indeksa u JOINsDodatno, OPTIMIZE TABLE dopustiti defragmentirati tablice, promijeniti redoslijed i ažurirati indekse, nešto što se preporučuje u tablicama koje su pretrpjele mnoge promjene.
Da biste provjerili koristi li motor 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 tražilicama i stvarni plan s vremenima i pročitanim retcima) i pokazuju provodi li se 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 možda je jedna od najvrjednijih vještina za svakoga tko želi optimizirati baze podataka: Omogućuje vam otkrivanje uskih grla, beskorisnih indeksa, loše selektivnih filtera i loše uređenih spojeva. mnogo prije nego što problem dođe do proizvodnje.
Indeksi punog teksta, regularni izrazi i posebni scenariji
kada radite s velika tekstualna polja (opisi, bogati HTML sadržaj, komentari itd.), pretraživanja s LIKE '%palabra%' To brzo postaje nepraktično za velike tablice. Za te slučajeve, tražilice poput MySQL-a nude indekse tipa FULLTEXT i operateri kao što su MATCH() AGAINST()što omogućuje puno učinkovitije i relevantnije pretrage.
s FULLTEXT Možete birati između različitih načina rada: prirodni jezik, boolean (s operatorima) +, -, *(navodnici za točne fraze itd.) ili proširenje upita proširiti povezane rezultate. To vam omogućuje izradu prilično moćnih internih tražilica bez napuštanja baze podataka.
Postoje napredniji scenariji u kojima tekst uključuje, na primjer, ugrađene HTML oznake. U tom slučaju možda će biti potrebno kombinirati indeks. FULLTEXT s funkcijama poput REGEXP_REPLACE za čišćenje oznaka prilikom usporedbe toč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 točan iznos bez skeniranja cijele tablice.
Drugi tražilice, poput Oraclea, omogućuju korištenje regularni tablični izrazi Ove značajke pomažu optimizatoru da umetne predikate unutar prikaza i što brže smanji volumen međupodataka. Ovaj pristup je vrlo koristan pri radu s mnogo ugniježđenih prikaza ili složenih definicija u okruženjima za suradnju.
Dodatne najbolje prakse: parametri, materijalizirani prikazi i dijeljenje upita
Osim indeksa i planova provedbe, postoji niz dobre međusektorske prakse koji doprinose i performansama i sigurnosti. Jedan od najvažnijih je koristiti parametrizirane upite Umjesto spajanja nizova znakova za izgradnju dinamičkog SQL-a, ovo smanjuje rizik SQL injekcije i omogućuje bazi podataka da ponovno koristi planove izvršavanja za upite s istom strukturom.
U sustavima sa vrlo teški i ponavljajući upiti (nadzorne ploče, izvršna izvješća, agregirani izračuni), materijalizirani pogledi Oni su odličan saveznik. Za razliku od normalnog prikaza, fizički pohranjuju rezultat upita, postajući svojevrsna unaprijed izračunata tablica 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 izravna podrška, ovo se ponašanje obično emulira tablicama i procesima koji periodički regeneriraju podatke, često putem okidača ili zakazanih zadataka.
Kada upit spaja previše tablica ili se oslanja na složeni mozaik prikaza, druga valjana strategija je podijelite upit u nekoliko korakaTo se prevodi u pokretanje početnog upita za dobivanje manjeg skupa (npr. relevantnih ID-ova), a zatim pokretanje dodatnih upita za dovršetak informacija. Ovaj pristup treba koristiti razborito 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.
Tijekom 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 prepoznate 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 temeljeni na umjetnoj inteligenciji koji analiziraju vaše upite i shemu baze podataka kako bi predložili poboljšanja: prijedloge indeksa, prepisivanje upita, promjene u strukturi tablica itd. Imena poput EverSQL, DBScoop, PGAnalyzer ili Redshift Advisor postala su popularna u profesionalnim okruženjima.
Ova rješenja mogu pregledati velike količine zapisnika upita, usporediti ih sa statistikama, planovima izvršenja i metrikama performansi, te odatle otkriti neučinkovite obrasce ili uska grla što bi nam na prvi pogled promaklo. Oni također pomažu u procjeni hipotetskog utjecaja stvaranja ili ukidanja određenih indeksa.
Međutim, važno ih je shvatiti kao podrška, a ne kao zamjena Ovisi o vašem znanju SQL-a i razumijevanju 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 što je najvažnije.
Idealna kombinacija je tim koji vlada principima optimizacije (planovi, indeksi, normalizacija, obrasci pristupa) i koristi umjetnu inteligenciju za... ubrzati analizu i potvrditi hipotezene donositi slijepe odluke.
Kada internalizirate cijeli ovaj skup tehnika - pažljiv dizajn indeksa, minimalan odabir stupaca, inteligentnu upotrebu JOIN-ova i CTE-ova, učinkovito paginiranje, redovito održavanje statistike, iskorištavanje materijaliziranih prikaza, pa čak i podršku AI alata - Velike baze podataka više nisu nekontrolirano čudovište i postaju predvidljiva i skalabilna komponenta vaše arhitekture, sposobna rasti s vašim poslovanjem bez narušavanja korisničkog iskustva ili proračuna za infrastrukturu.
