Većina problema s performansama baze podataka ne počinje očito lošim upitom. Počinju upitom koji je prihvatljiv na malom skupu podataka, a zatim postaje nepredvidiv kako se povećavaju promet, broj redaka i broj istodobnih zahtjeva.

Cilj nije učiniti svaki upit domišljatim. Cilj je rad s bazom podataka učiniti ograničenim, vidljivim i usklađenim s načinom na koji aplikacija stvarno čita i zapisuje podatke. Predvidive performanse svojstvo su dizajna: proizlaze iz odabira obrazaca upita čiji trošak ostaje razumljiv kako sustav raste.

Započnite s ograničenim radom

Zahtjev bi rijetko trebao tražiti od baze podataka da razmatra neograničenu količinu podataka. Upiti poput SELECT * FROM orders mogu biti bezopasni u razvojnom okruženju, ali stvaraju opasnu naviku. Produkcijski podaci imaju način da praktičnost pretvore u latenciju, pritisak na memoriju i preopterećene veze.

Postavite izričito ograničenje kad korisniku zaista nisu potrebni svi podudarajući zapisi. Uparite to ograničenje s redoslijedom koji je deterministički i podržan indeksom.

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

Ovaj upit jasno izražava namjeru: dohvatiti nedavni, upravljivi dio narudžbi jednog kupca. Odgovarajući indeks trebao bi odražavati obrazac pristupa, obično počevši s customer_id, a zatim stupcima za redoslijed gdje je to prikladno. Dizajn indeksa ne znači indeksirati svako polje; znači podržati filtre, spajanja i redoslijede sortiranja na koje se aplikacija oslanja.

Dajte prednost paginaciji s ključevima za duboke skupove rezultata

Paginaciju s pomakom lako je napisati:

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

Ali veliki pomaci traže od baze podataka da prođe pokraj redaka koje neće vratiti. Također mogu stvoriti nezgodno korisničko iskustvo kada se retci umeću ili brišu između zahtjeva za stranicama. Zapis se može pojaviti dvaput ili nestati iz niza.

Za sažetke, tokove aktivnosti, zapisnike revizije i druge uređene zbirke koristite paginaciju s ključevima. Proslijedite vrijednosti za redoslijed posljednjeg retka kao pokazivač.

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;

Točnu sintaksu i ponašanje indeksa treba provjeriti za korišteni mehanizam baze podataka, ali načelo je stabilno: nastavite s poznatog položaja umjesto da preskačete sve veći broj redaka. Vrijednosti pokazivača treba tretirati kao dio API ugovora, pažljivo ih validirati i temeljiti na stabilnom, jedinstvenom redoslijedu.

Uklonite N+1 upite na granici

Problem N+1 upita često se skriva iza aplikacijskog koda koji izgleda uredno. PHP petlja učitava popis zapisa, a zatim lijeno učitava povezani zapis za svaku stavku. Pedeset redaka neprimjetno postaje pedeset i jedno putovanje do baze podataka.

Najprije odlučite što krajnja točka treba. Zatim namjerno učitajte te podatke. Za popis narudžbi koji treba imena kupaca, spajanje može biti prikladno:

SELECT
    o.id,
    o.status,
    o.created_at,
    c.name AS customer_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= :from
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;

Za odnose jedan-prema-više, spajanje svega u jedan veliki rezultat može duplicirati nadređene retke i povećati korisni teret odgovora. U tom slučaju dva svrhovita upita mogu biti jasnija: dohvatite stranicu ID-ova nadređenih zapisa, zatim dohvatite svu potrebnu djecu ograničenim IN upitom. Grupirajte rezultate u aplikacijskom kodu. Važna razlika nije „jedan je upit uvijek bolji”; nego „broj i oblik upita ne bi trebali slučajno rasti s brojem prikazanih zapisa.”

Namjerno odabirite podatke

SELECT * je praktičan, ali povezuje upit sa svakim stupcem u tablici. To može prenositi velika tekstna polja ili blobove koje krajnja točka nikad ne koristi, otežati promišljanje promjena sheme i zamagliti o čemu aplikacija ovisi.

Odaberite polja potrebna za trenutačnu operaciju. Time se poboljšava čitljivost jednako kao što se može poboljšati učinkovitost. Kompaktan skup rezultata lakše je serijalizirati, predmemorirati, testirati i pregledati.

Ista disciplina primjenjuje se na agregate. Nadzorna ploča ne bi trebala učitavati tisuće redaka u PHP samo da ih prebroji ili zbroji. Neka baza podataka obavi rad temeljen na skupovima, uz izričito određen opseg agregacije.

SELECT status, COUNT(*) AS order_count
FROM orders
WHERE created_at >= :start
  AND created_at < :end
GROUP BY status;

Koristite poluotvoreni vremenski raspon, kao što je prikazano iznad, umjesto pokušaja stvaranja vremenske oznake za „kraj dana”. Time se izbjegavaju rubni slučajevi preciznosti i susjedni izvještajni prozori čisto se povezuju.

Neka zapisi budu mali, atomski i svjesni ponovnih pokušaja

Performanse čitanja privlače pozornost, ali nepredvidivi zapisi često su štetniji. Transakcije neka budu kratke: pokrenite ih blizu prvog zapisa, obavite samo rad s bazom podataka potreban za atomsku promjenu i odmah potvrdite transakciju. Nemojte držati transakciju otvorenom dok pozivate vanjsku HTTP uslugu, generirate odgovor ili čekate korisnički unos.

Kada više promjena mora zajedno uspjeti ili ne uspjeti, odredite tu granicu transakcijom. Primjerice, stvaranje narudžbe i smanjenje rezerviranih zaliha ne bi smjeli ostaviti potvrđenom samo jednu od tih promjena.

$pdo->beginTransaction();

try {
    $reserve = $pdo->prepare(
        'UPDATE inventory
         SET reserved = reserved + :quantity
         WHERE product_id = :product_id
           AND available - reserved >= :quantity'
    );
    $reserve->execute([
        'product_id' => $productId,
        'quantity' => $quantity,
    ]);

    if ($reserve->rowCount() !== 1) {
        throw new RuntimeException('Insufficient inventory.');
    }

    $order = $pdo->prepare(
        'INSERT INTO orders (customer_id, status)
         VALUES (:customer_id, :status)'
    );
    $order->execute([
        'customer_id' => $customerId,
        'status' => 'pending',
    ]);

    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $error;
}

U sustavu s istodobnim radom mogući su zastoji i prolazni kvarovi čak i uz razumne upite. Tamo gdje je operaciju sigurno ponoviti, dodajte ograničenu politiku ponovnih pokušaja oko cijele transakcije. Nemojte slijepo ponavljati svaku iznimku: neuspjele validacije, kršenja jedinstvenih ograničenja i trajni kvarovi povezivosti zahtijevaju drukčije postupanje. Ključevi idempotentnosti osobito su vrijedni za API-je za zapisivanje pokrenute izvana jer ponovni pokušaj klijenta ne bi trebao stvoriti drugu narudžbu.

Koristite ograničenja kao dio dizajna upita

Validacija aplikacije korisna je, ali baza podataka ostaje konačni autoritet za integritet podataka. Strani ključevi, jedinstvena ograničenja, stupci koji ne dopuštaju null vrijednosti i odgovarajuća ograničenja provjere pretvaraju pretpostavke u provediva pravila.

Jedinstveno ograničenje na vanjskoj referenci plaćanja, primjerice, pruža trajnu zaštitu od dvostruke obrade. Strani ključ otežava stvaranje nevaljanih odnosa. To nisu samo obrambene mjere; pojednostavnjuju logiku upita jer se nizvodni kod može osloniti na snažnije invarijante.

Pregledavajte planove, ne samo proteklo vrijeme

Upit koji je danas brz možda je brz samo zato što je tablica još mala ili je predmemorija zagrijana. Pri procjeni važnog upita pregledajte njegov plan izvršavanja alatima koje pruža baza podataka. Tražite potpuna skeniranja velikih tablica, skupa sortiranja, strategije spajanja koje ne odgovaraju očekivanjima i procjene koje se znatno razlikuju od stvarnog broja redaka gdje su te informacije dostupne.

Zatim testirajte s realističnim parametrima. Upit za jednog kupca nije dokaz da se isti upit dobro ponaša za kupca s višegodišnjom poviješću. Rad na performansama poboljšava se kada je povezan s poznatim oblikom zahtjeva, količinom redaka, indeksom i prihvatljivom veličinom rezultata.

Izrađujte API-je koji poštuju stvarnost baze podataka

Baza podataka nije pasivni sloj pohrane ispod neograničenog API-ja. Dizajn krajnjih točaka određuje ponašanje upita. Filtri trebaju ograničenja, pretraživa polja trebaju plan indeksiranja, izvozi trebaju asinkrone ili strujane radne tokove kada su skupovi rezultata veliki, a krajnje točke popisa trebaju stabilna pravila paginacije.

Predvidive performanse proizlaze iz odabira jednostavnih, izričitih obrazaca: uskih čitanja, stabilnih redoslijeda, podržanih indeksa, ograničenih stranica, promišljenog učitavanja odnosa, kratkih transakcija i provedivih ograničenja. Nijedan od tih izbora nije glamurozan. Zajedno čine sustav lakšim za upravljanje kada je to najvažnije: nakon što podaci i promet nagađanje učine skupim.