Baza podataka može imati izvrsne indekse, a ipak djelovati sporo. To je neugodna istina iza mnogih istraga performansi: indeksiranje je važno, ali je samo jedan dio sustava koji premješta podatke kroz aplikacijski kod, mrežne veze, granice transakcija i konkurentna opterećenja.

Najbrži rad baze podataka često je onaj koji od baze nikada ne zatražite. Sljedeći najbrži je rad koji je namjerno oblikovan: mali skupovi rezultata, predvidivi upiti, kratke transakcije i shema koja odgovara stvarnom ponašanju proizvoda.

Razmišljanje izvan indeksa pretvara podešavanje performansi iz skupa hitnih popravaka u inženjersku praksu.

Započnite s oblikom posla

Prije dodavanja indeksa provjerite što aplikacija traži. Upit može koristiti indeks, a ipak biti skup jer vraća previše redaka, spaja velike međuskupove, sortira širok rezultat ili prisiljava poslužitelj da procjenjuje funkciju za svaki kandidatni redak.

Čest primjer je dohvaćanje cijelog zapisa kada krajnja točka treba samo ime i status. Odabir manjeg broja stupaca smanjuje rad baze podataka, mrežni prijenos, upotrebu memorije u PHP-u i trošak serijalizacije.

SELECT id, name, status
FROM customers
WHERE account_id = ?
ORDER BY created_at DESC
LIMIT 50;

To je obično poželjnije od SELECT *, posebno za tablice koje sadrže velika tekstualna polja, JSON dokumente ili binarne podatke. Također čini ovisnosti eksplicitnima: manje je vjerojatno da će buduće promjene sheme neprimjetno povećati trošak često pozivane krajnje točke.

Oblik upita uključuje i učestalost. Upit koji traje nekoliko milisekundi možda ne zaslužuje pažnju kada se izvršava jednom dnevno. Isti upit postaje važan kada se izvršava jednom za svaku stavku na popisu, za svaki zahtjev u prometnom API-ju ili pri svakoj iteraciji radnika.

Pronađite i uklonite ponovljene upite

Obrazac upita N+1 jedan je od najpouzdanijih načina da responzivnu aplikaciju pretvorite u usko grlo baze podataka. Pojavljuje se kada kod učita popis, a zatim izvrši jedan dodatni upit za svaki redak.

Zamislite API koji vraća 100 narudžbi i za svaku narudžbu traži kupca. Čak i ako je svako dohvaćanje kupca indeksirano, aplikacija je stvorila 101 povratno putovanje umjesto jednog ili dva dobro osmišljena upita.

U PHP-u učinite pristup podacima dovoljno vidljivim da uočite ovaj obrazac. Metode repozitorija, učitavanje ORM relacija i povratni pozivi serijalizatora mogu izdavati upite daleko od kontrolera koji ih je pokrenuo. Bilježenje upita u razvoju i broj upita po zahtjevu često otkrivaju više od jednog izvještaja o sporim upitima.

Popravak ovisi o kontekstu:

• Dohvatite povezane zapise spajanjem kada oblik rezultata ostaje upravljiv.
• Učitajte povezane entitete u ograničenoj skupnoj operaciji koristeći WHERE id IN (...).
• Za krajnju točku čitanja vratite namjenski izrađenu projekciju umjesto da je sastavljate iz mnogih generičkih učitavanja objekata.
• Postavite eksplicitna ograničenja kako skupna operacija ne bi mogla neograničeno rasti.

Nemojte pohlepno učitavanje smatrati automatskim lijekom. Spajanje nekoliko odnosa jedan-prema-više može drastično umnožiti retke. Cilj nije „jedan upit pod svaku cijenu”; cilj je mala, predvidiva količina rada baze podataka.

Čitajte planove izvršavanja, a ne namjere

Razvojni programeri često zaključuju na temelju značenja upita. Strojevi baza podataka moraju zaključivati kako ga izvršiti. To su različita pitanja.

Plan izvršavanja pomaže otkriti skenira li stroj tablicu, koristi li koristan put pristupa, sortira li neočekivano velik skup ili odabire li redoslijed spajanja koji upit čini skupim. Upotrijebite značajku plana koju pruža korištena baza podataka i usporedite njezine procjene s brojem stvarno vraćenih redaka kada je ta informacija dostupna.

Planovi su najkorisniji kada su povezani s konkretnim pitanjem: zašto je ova krajnja točka postala sporija kako je tablica rasla? Može li se sortiranje izbjeći? Je li predikat spajanja selektivan? Sprječava li uvjet bazu podataka da koristi učinkovit put pristupa?

Primjerice, obavijanje stupca funkcijom može promijeniti potreban rad:

SELECT id, email
FROM users
WHERE DATE(created_at) = ?;

Ako aplikacija isti zahtjev može izraziti kao raspon, bazi je često lakše suziti kandidatne retke:

SELECT id, email
FROM users
WHERE created_at >= ?
  AND created_at < ?;

Točni detalji ovise o bazi podataka, distribuciji podataka i postojećoj shemi. Načelo je stabilno: pišite predikate koji opisuju uzak, izravan put do željenih zapisa.

Transakcije neka budu kratke i svrhovite

Dizajn transakcija odluka je o performansama jednako kao i odluka o ispravnosti. Duga transakcija može držati zaključavanja, zadržavati stare verzije redaka, povećavati natjecanje i prisiljavati nepovezane zahtjeve na čekanje.

Transakcijski dio zadržite uskim. Provjerite unos, pozovite udaljene usluge, generirajte dokumente i obavite skupe izračune prije ili nakon transakcije baze podataka kada to ispravnost dopušta. Unutar transakcije obavite najmanji potrebni broj čitanja i pisanja, a zatim je odmah potvrdite.

To ne znači žrtvovanje dosljednosti. Znači precizno odrediti što mora biti atomsko. Ako stvaranje narudžbe zahtijeva istodobno rezerviranje zaliha i evidentiranje narudžbe, te promjene mogu pripadati jednoj transakciji. Slanje e-pošte s potvrdom ne mora držati tu transakciju otvorenom.

Kada su ponovni pokušaji potrebni zbog prolaznih neuspjeha transakcija, učinite operacije sigurnima za ponovni pokušaj. Ključevi idempotentnosti za pisanja pokrenuta izvana i jedinstvena ograničenja za poslovne identifikatore praktične su zaštite. Petlja ponovnih pokušaja bez strategije idempotentnosti može stvoriti duplicirani rad.

Paginacija je izbor dizajna baze podataka

Paginacija s pomakom je jednostavna:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

Ali duboki pomaci mogu zahtijevati da baza prođe pokraj mnogih redaka prije vraćanja tražene stranice. Također otežavaju dosljedno kretanje kroz skupove podataka koji se mijenjaju.

Za velike, uređene kolekcije paginacija po skupu ključeva često bolje odgovara. Umjesto traženja stranice 501, zatražite zapise nakon zadnje viđene stavke:

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Redoslijed mora biti deterministički, zbog čega je važan razrješivač izjednačenja poput id. Ovaj pristup posebno je koristan za feedove, zapisnike revizije i operativne nadzorne ploče.

Predmemoriranje koristite kao ugovor, a ne kao flaster

Predmemoriranje može zaštititi bazu podataka od ponovljenih čitanja, ali uvodi novo pitanje: kada su predmemorirani podaci valjani? Predmemorija bez strategije poništavanja ili isteka samo premješta rizik za ispravnost na drugi sloj.

Predmemorirajte stabilne, skupe i često tražene rezultate. Koristite ograničeno vrijeme isteka kada su neznatno zastarjeli podaci prihvatljivi. Poništite ciljane ključeve nakon pisanja kada aplikacija može pouzdano utvrditi što se promijenilo. Izbjegavajte predmemoriranje izrazito personaliziranih ili brzo promjenjivih podataka osim ako su vlasništvo i životni ciklus jasni.

Važno je i upravljanje vezama. Ponovna upotreba veza putem odgovarajuće aplikacijske ili infrastrukturne strategije može smanjiti trošak uspostavljanja, ali svaka veza troši resurse baze podataka. Ispravna veličina skupa nije „što više to bolje”; to je dovoljno istodobnosti za stvarnu potražnju bez preopterećivanja baze podataka.

Mjerite cijeli zahtjev

Spori API odgovor može provesti malo vremena u SQL-u. Možda čeka vezu, transformira velik rezultat u PHP-u, poziva drugu uslugu ili kodira prevelik JSON odgovor. Mjerite trajanje zahtjeva zajedno s brojem upita, trajanjem upita, vraćenim recima, stopom pogrešaka i vremenom čekanja na vezu.

Rad na performansama postaje mnogo lakši kada svaka optimizacija ima navedenu hipotezu i mjerenje prije i poslije. „Ova krajnja točka dohvaća previše podataka” može se primijeniti. „Baza podataka je spora” samo je početna točka.

Brzina je suzdržanost

Indeksi su moćni jer smanjuju nepotrebno pretraživanje. Šira je pouka ista: smanjite nepotreban rad posvuda. Tražite manje redaka, obavite manje povratnih putovanja, držite zaključavanja kraće, serijalizirajte manje podataka i predmemorirajte samo ono što možete objasniti.

Brz sustav koji se oslanja na bazu podataka rijetko je rezultat jednog domišljatog indeksa. Rezultat je mnogih uobičajenih odluka donesenih pažljivo, pri čemu svaka od njih čuva prostor za sljedeću fazu rasta.