Većina problema s performansama baze podataka ne počinje kao dramatičan prekid rada. Počinju kao razumni prečaci: upit koji radi na maloj tablici, fleksibilan stupac dodan „za sada”, krajnja točka koja učitava povezane zapise jedan po jedan zahtjev. Aplikacija se isporuči, promet raste i odjednom se od baze podataka traži da nadoknadi odluke koje nikada nisu bile osmišljene za rad u velikom opsegu.

Dobar dizajn baze podataka ne znači predvidjeti svaki budući zahtjev. To je nemoguće. Riječ je o prepoznavanju obrazaca koji postaju skupi kada se povećaju količina podataka, istodobnost i složenost značajki. Cilj je budući rad na performansama učiniti promišljenim, a ne reaktivnim.

Dizajnirajte prema obrascima pristupa, a ne samo prema entitetima

Dijagrami entiteta korisni su, ali predstavljaju samo polovicu dizajna. Shema može savršeno modelirati kupce, narudžbe, proizvode i račune, a da aplikacija i dalje bude spora ako uobičajena čitanja zahtijevaju nepotrebna spajanja, skeniranja ili sortiranja.

Prije finaliziranja tablice, zapitajte se kako će je aplikacija koristiti. Koji će je zasloni i API krajnje točke čitati? Koji su filtri uobičajeni? Koji se zapisi prikazuju zajedno? Koji se odnosi učitavaju pri svakom zahtjevu? Ta pitanja pretvaraju apstraktno modeliranje podataka u operativni dizajn.

Na primjer, krajnja točka povijesti narudžbi može često zahtijevati narudžbe kupca obrnutim kronološkim redoslijedom. Taj obrazac pristupa sugerira indeks koji počinje identifikatorom kupca i uključuje vremensku oznaku koja se koristi za sortiranje.

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at DESC);

Točna sintaksa indeksa i ponašanje optimizatora razlikuju se ovisno o sustavu za upravljanje bazom podataka, ali načelo je stabilno: indeksirajte uvjete i sortiranje koje vaši stvarni upiti koriste zajedno. Indeks samo na customer_id može pomoći pri filtriranju, dok složeni indeks može pomoći bazi podataka da izbjegne dodatno sortiranje.

Indeksi su ugovori s vašim upitima

Indeks nije općenita naljepnica za performanse. Poboljšava određene putanje pretraživanja, a košta prostor za pohranu, vrijeme pisanja i održavanje. Svaki umetnuti, ažurirani ili obrisani redak također može zahtijevati promjene indeksa. Nasumično dodavanje indeksa može problem čitanja pretvoriti u problem pisanja.

Praktičan pregled indeksiranja počinje upitima koji su najvažniji:

• Dohvaćanja prema primarnom ključu i spajanja prema stranim ključevima.
• Često korišteni filtri, osobito na velikim tablicama.
• Putanje sortiranja i paginacije.
• Pravila jedinstvenosti koja baza podataka mora provoditi.
• Administrativni upiti i upiti za izvještavanje koji se pokreću dovoljno često da utječu na produkciju.

Jedinstveni indeksi zaslužuju posebnu pozornost. Validacija na razini aplikacije korisna je za povratne informacije, ali ne može pouzdano spriječiti duplikate pri istodobnim zahtjevima. Ako adresa e-pošte ili vanjska referenca mora biti jedinstvena, neka to pravilo bude dio sheme.

CREATE UNIQUE INDEX users_email_unique
ON users (email);

Zatim neka aplikacija uredno obradi očekivani sukob. U PHP API-ju to obično znači uhvatiti iznimku baze podataka nastalu zbog kršenja ograničenja i vratiti odgovarajući odgovor o validaciji ili sukobu, umjesto pretpostaviti da je provjera prije umetanja bila dovoljna.

Poštujte kardinalnost prije nego što vas iznenadi

Performanse su često problem kardinalnosti prerušen u problem upita. Odnos koji djeluje bezazleno s deset redaka može se ponašati vrlo drukčije s deset milijuna.

Razmotrite odnos jedan-prema-više između narudžbi i stavki narudžbe. Dohvaćanje jedne narudžbe s njezinim stavkama jednostavno je. Dohvaćanje stotinu narudžbi, svake sa stavkama, zahtijeva više pažnje. Naivan ORM tijek rada može stvoriti klasičan obrazac upita N+1: jedan upit za narudžbe, a zatim jedan upit po narudžbi za njezine stavke.

Rješenje nije uvijek „pišite sirovi SQL”. Zrela upotreba ORM-a uključuje razumijevanje njegova ponašanja pri učitavanju. Koristite željno učitavanje, spajanja ili skupne upite gdje je prikladno te pregledajte generirani SQL za putanje s velikim prometom. Čist objektni model vrijedan je, ali ne oslobađa kod troškova baze podataka.

Paginacija ima sličnu zamku. Offset paginaciju lako je implementirati:

SELECT id, created_at, status
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

No veliki offseti mogu prisiliti bazu podataka da prođe kroz mnogo redaka prije vraćanja sljedeće stranice. Za feedove i velike povijesti paginacija temeljena na kursoru često je predvidljivija. Koristite stabilan ključ sortiranja, poput vremenske oznake uparene s identifikatorom, i zatražite retke nakon posljednjeg zapisa koji je klijent primio.

Držite integritet podataka blizu podataka

Ograničenja nisu birokracija. Ona su način sprječavanja nevaljanih stanja da uđu u sustav putem zaboravljene skripte, pozadinskog radnika, druge usluge ili uvjeta utrke.

Koristite primarne ključeve, strane ključeve tamo gdje odgovaraju operativnom modelu, ograničenja NOT NULL, razumne zadane vrijednosti, ograničenja provjere tamo gdje su podržana i prikladna te ograničenja jedinstvenosti za poslovne invarijante. Aplikacija i dalje validira ulaze, ali baza podataka ostaje konačni autoritet za to jesu li pohranjeni podaci koherentni.

To poboljšava održivost jednako kao i ispravnost. Kada pravila postoje samo u PHP kodu, svaki novi radnik, proces uvoza i API krajnja točka moraju ih ponovno otkriti i reproducirati. Kada kritična pravila žive u shemi, sustav ima trajnu granicu.

Pažljivo birajte fleksibilna polja

JSON stupci, tekstualni blobovi i generičke tablice ključ-vrijednost mogu biti korisni alati. Također su česta mjesta za skrivanje nedovršenog modela podataka. Ako se vrijednost redovito filtrira, spaja, sortira, validira ili uključuje u izvještaje, obično je snažan kandidat za prvoklasni stupac.

Fleksibilna pohrana ima smisla za istinski promjenjive metapodatke ili korisne terete koje aplikacija uglavnom dohvaća kao cjelinu. Postaje skupa kada su važna poslovna polja zakopana unutar nestrukturiranih podataka i svaki ih upit mora izdvajati. Rezultat su teže indeksiranje, slabija ograničenja i manje očito ponašanje upita.

Pragmatičan kompromis je čest: zadržite promjenjive metapodatke u fleksibilnom polju, a stabilne atribute visoke vrijednosti izdignite u tipizirane stupce. To čuva prilagodljivost bez zamagljivanja ključnih upita.

Izmjerite planove upita prije promjene sheme

Kada se upit uspori, izbjegavajte nagađanje. Zabilježite stvarni upit, realistične parametre, broj redaka i plan izvršavanja. Potražite potpuna skeniranja na neočekivano velikim tablicama, skupa sortiranja, loš redoslijed spajanja, ponovljene podupite i procjene koje se oštro razlikuju od stvarnosti.

Koristite mogućnost objašnjenja baze podataka tijekom razvoja i analize incidenata. Izlaz nije uvijek pregledan, ali govori vam što optimizator namjerava učiniti. To je daleko korisnije od dodavanja indeksa zato što njegovo ime zvuči povezano s problemom.

Također testirajte s količinom i distribucijom sličnima produkciji. Razvojna baza podataka s ravnomjerno raspoređenim oglednim recima ne može otkriti isto ponašanje kao tablica u kojoj jedan zakupac posjeduje većinu zapisa ili u kojoj vrijednost statusa odgovara gotovo svakom retku.

Učinite migracije sigurnim operativnim promjenama

Promjene sheme su implementacije, a ne samo izmjene koda. Dodavanje stupca može biti jednostavno; promjena velike indeksirane tablice može zaključati resurse, ponovno zapisati podatke ili stvoriti dugotrajnu operaciju, ovisno o sustavu i verziji.

Za promjene koje utječu na aktivne sustave, prednost dajte postupnim uvođenjima. Najprije dodajte stupac koji dopušta NULL, implementirajte kod koji može čitati i stari i novi oblik, popunite podatke u kontroliranim skupinama, provjerite rezultat, a zatim primijenite stroža ograničenja kada su podaci spremni. Takav pristup također pruža mogućnosti vraćanja koje jedna destruktivna migracija nema.

Dizajn baze podataka postaje održiv kada se tretira kao dio arhitekture aplikacije. Jasno modelirajte podatke, indeksirajte načine na koje im se stvarno pristupa, provodite invarijante tamo gdje pripadaju i mjerite prije optimizacije. Najbolje vrijeme za rješavanje performansi nije nakon što se oglasi dojavljivač. To je trenutak kada jednostavna odluka o shemi još uvijek može zadržati sutrašnji sustav jednostavnim.