Kada se pojavi spor endpoint, prvi je instinkt često: „Dodaj indeks.” Ponekad je to upravo ispravno. Češće je to početak istrage, a ne njezin kraj.

Indeksi su jedan od najvrjednijih alata u dizajnu baza podataka, ali ne mogu popraviti nejasan model podataka, neučinkovit oblik upita, preopterećen skup veza ili API koji traži daleko više podataka nego što mu je potrebno. Stvarni rad na performansama počinje tretiranjem latencije kao svojstva sustava: aplikacijski kod, ponašanje baze podataka, infrastruktura i zahtjevi proizvoda svi sudjeluju.

Započnite sa stvarnim uskim grlom

Baza podataka može biti spora iz vrlo različitih razloga. Upit može skenirati previše redaka, ali može biti i brz izolirano, a spor pod istodobnim opterećenjem. Aplikacija može izvršiti isti upit stotine puta po zahtjevu. Spremnik može imati nedovoljno memorije, zbog čega korisne stranice baze podataka ispadaju iz predmemorije. ORM može neprimjetno dohvatiti velike grafove objekata koje odgovor nikada ne koristi.

Prije promjene sheme ili koda, učinite spor put mjerljivim. Zabilježite endpoint, njegov volumen zahtjeva, trajanje, broj upita i reprezentativne parametre upita. Zatim pregledajte plan upita za stvarni upit koji se izvršava. Važno pitanje nije „ima li ovaj stupac indeks?” Nego: „koji posao baza podataka obavlja i zašto?”

Plan može otkriti potpuna skeniranja, skupa sortiranja, loš redoslijed spajanja, velike skupove međurezultata ili procjene koje ne nalikuju stvarnosti. Ti nalazi upućuju na različite popravke. Dodavanje indeksa bez razumijevanja plana može povećati trošak pisanja i upotrebu pohrane, a pritom ostaviti problem vidljiv korisniku netaknutim.

Smanjite upit prije nego što ga ubrzate

Najjeftiniji redak je onaj koji nikada ne pročitate. Dizajn upita trebao bi odražavati podatke potrebne značajci, a ne praktičnost općenite metode modela.

Razmotrite nadzornu ploču koja prikazuje ime kupca, datum najnovije narudžbe i broj narudžbi. Dohvaćanje svake narudžbe i izračunavanje tih vrijednosti u PHP-u šalje nepotrebne podatke preko mreže i troši memoriju aplikacije. Usmjereni agregatni upit obično je bolja granica:

SELECT
    c.id,
    c.name,
    MAX(o.created_at) AS latest_order_at,
    COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.account_id = ?
GROUP BY c.id, c.name
ORDER BY latest_order_at DESC
LIMIT 50;

Ovo nije automatski savršeno; odgovarajući indeksi i semantika i dalje su važni. No izravno izražava značajku i sprječava aplikaciju da obavlja posao za koji je baza podataka osmišljena.

Budite jednako sumnjičavi prema SELECT *. Ono povezuje endpoint sa svakim sadašnjim i budućim stupcem, povećava troškove prijenosa i može slučajno povući velika tekstualna ili JSON polja u učestalo korištene putanje. Odaberite stupce koje odgovor treba. To poboljšava performanse, čini API ugovore jasnijima i smanjuje vjerojatnost da bezazleno dodavanje shemi postane skupo čitanje.

Paginacija je odluka o performansama

Offset paginaciju lako je objasniti, što je čini primamljivom:

SELECT id, created_at, title
FROM posts
WHERE author_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 5000;

Za plitke stranice može biti sasvim prihvatljiva. Za duboku navigaciju kroz velik skup podataka koji se mijenja, baza podataka i dalje mora proći pokraj ranijih redaka. Rezultat može postati sve skuplji, a umetanja mogu učiniti granice stranica nestabilnima.

Kursorska, odnosno keyset, paginacija daje bazi podataka korisnije mjesto za nastavak:

SELECT id, created_at, title
FROM posts
WHERE author_id = ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 25;

Kursor mora biti usklađen s determinističkim redoslijedom, a indeks bi trebao podržavati obrazac filtriranja i sortiranja. Važna je pouka šira od paginacije: tražite od baze podataka da nastavi s poznatog položaja umjesto da ga stalno iznova pronalazi.

Indeksi moraju odgovarati obrascima pristupa

Indeksi su najučinkovitiji kada podržavaju stvaran predikat, spajanje i obrazac sortiranja. Složeni indeks nije samo nekoliko indeksa nad pojedinačnim stupcima spojenih zajedno. Redoslijed stupaca je važan.

Pretpostavimo da API često traži nedavne plaćene račune za jedan račun. Vjerojatan obrazac pristupa je:

SELECT id, issued_at, total
FROM invoices
WHERE account_id = ?
  AND status = 'paid'
ORDER BY issued_at DESC
LIMIT 100;

Indeks oblikovan oko account_id, status i issued_at može dobro služiti ovom upitu, ovisno o pogonu baze podataka i distribuciji vrijednosti. No nemojte to opažanje pretvoriti u pravilo da svaki stupac filtra pripada svakom indeksu. Polja niske selektivnosti, tablice s mnogo pisanja i konkurentski obrasci upita zahtijevaju prosudbu.

Svaki indeks ima trošak održavanja pri umetanju, ažuriranju, brisanju, sigurnosnim kopijama i pohrani. Pregledavajte indekse kao dio dizajna sheme, a ne kao hitne ostatke ostavljene nakon incidenata.

Zaustavite slučajno umnažanje upita

Jedan spor upit je vidljiv. Sto umjerenih upita može biti gore.

Klasičan primjer je API koji učita popis narudžbi, a zatim učita kupca za svaku narudžbu. U PHP-u to može izgledati bezazleno jer je pristup relaciji sažet. Tijekom izvođenja postaje obrazac upita N+1: jedan upit za popis, zatim jedan dodatni upit po retku.

Popravite to namjerno. Upotrijebite odgovarajuće spajanje, skupno učitajte povezane zapise ili konfigurirajte unaprijed učitavanje s ograničenim popisom polja. Zatim provjerite broj upita na razini zahtjeva. Izbjegavajte zamijeniti N+1 golemim unaprijed učitavanjem koje dohvaća svaku ugniježđenu relaciju; učitajte samo relacije i stupce potrebne za taj odgovor.

Predmemoriranje koristite kao ugovor, a ne kao flaster

Predmemoriranje može zaštititi bazu podataka od ponovljenog rada, ali samo kada predmemorirana vrijednost ima jasno vlasništvo, istek i ponašanje poništavanja. „Spremi u predmemoriju na sat vremena” nije strategija ako korisnici očekuju da se promjene prikažu odmah.

Dobri kandidati za predmemoriranje obično su skupi, često čitani i tolerantni na ograničenu zastarjelost: snimke konfiguracije, dijelovi javnog kataloga, izvedeni sažeci ili podaci o dozvolama s pažljivo osmišljenim putem poništavanja. Ključevi predmemorije trebali bi uključivati svaki ulaz koji mijenja rezultat, kao što su zakupac, lokalizacija, filtri i opseg autorizacije.

Također planirajte promašaje. Istodobni istek popularnog ključa kod mnogih radnika može stvoriti nalet istovjetnog rada baze podataka. Objedinjavanje zahtjeva, pristupi stale-while-revalidate ili kontrolirana osvježavanja mogu smanjiti taj rizik kada sustav opravdava dodatnu složenost.

Performanse uključuju okruženje za implementaciju

Dobro napisan upit i dalje može patiti u loše konfiguriranom okruženju izvođenja. Veze s bazom podataka ograničeni su resursi. Ako PHP radnici, potrošači reda i zakazani poslovi svi otvaraju veze bez zajedničkog ograničenja, skokovi opterećenja mogu iscrpiti bazu podataka prije nego što se CPU učini zauzetim.

Postavite ograničenja konkurentnosti i skupova veza imajući na umu kapacitet baze podataka. Zajedno nadzirite upotrebu veza, spore upite, čekanja zaključavanja, ponašanje predmemorije, CPU, memoriju i latenciju diska. U okruženjima temeljenima na Dockeru također izričito postavite ograničenja resursa i osigurajte da je trajna pohrana baze podataka prikladna za radno opterećenje. Spremnici poboljšavaju dosljednost pakiranja; ne čine da latencija pohrane ili pritisak na memoriju nestanu.

Zaključavanje zaslužuje jednaku pozornost kao i performanse čitanja. Držite transakcije kratkima, izbjegavajte interaktivan rad unutar njih, ažurirajte retke dosljednim redoslijedom kada je uključeno više redaka i namjerno oblikujte ponašanje ponovnih pokušaja za prolazne sukobe. Ponovni pokušaj bez idempotentnosti može duplicirati nuspojave; ključ idempotentnosti ili trajno ograničenje jedinstvenosti često su dio ispravnog API dizajna.

Izgradite naviku mjerljivog poboljšavanja

Performanse baze podataka rijetko se rješavaju jednom herojskom optimizacijom. Poboljšavaju se kada timovi u svakodnevnom razvoju učine vidljivima oblik upita, razvoj sheme i operativno ponašanje. Izmjerite reprezentativno radno opterećenje, promijenite jednu značajnu stvar, pregledajte rezultat i zadržite poboljšanje samo kada pomaže stvarnom putu.

Indeksi i dalje ostaju ključni. Jednostavno najbolje rade kao dio šire discipline: dohvaćajte manje podataka, izbjegavajte ponovljene upite, svjesno birajte paginaciju, pošteno modelirajte ponašanje predmemorije i upravljajte bazom podataka kao ograničenom zajedničkom uslugom. Tako baza podataka prestaje biti tajanstveno usko grlo i postaje pouzdan dio dizajna sustava.