Pomalý zoznam objednávok: EXPLAIN, indexy a stránkovanie

Merajte celú požiadavku, potom jej časti

Ilustračný portál zobrazí päťdesiat objednávok, ale stránka čaká niekoľko sekúnd. Príčina môže byť v dotaze, čakaní na databázové spojenie, načítavaní zákazníka pre každý riadok alebo odovzdávaní veľkého výsledku. Pred pridaním indexu zaznamenajte čas požiadavky, počet dotazov a ich trvanie. Päťdesiat je veľkosť ukážkovej stránky, nie odporúčaný limit pre každý systém.

Použite filter a objem údajov podobný prevádzke. Dotaz nad prázdnou vývojovou databázou neukáže správanie zákazníka s dlhou históriou. Rozlišujte studenú a zahriatu cache a porovnávajte rovnaké podmienky. Samotný priemer môže zakryť problém, ktorý sa prejaví iba pri veľkom klientovi alebo pri špičke.

EXPLAIN ANALYZE vykoná skúmaný dotaz

V PostgreSQL zobrazí EXPLAIN plán, ktorý databáza zamýšľa použiť. EXPLAIN ANALYZE dotaz aj vykoná a doplní skutočné počty riadkov a čas. Pri zápisových príkazoch preto vznikajú účinky; pri funkciách môže mať účinok aj zdanlivo čítací dotaz. Diagnostiku začnite v vhodnom testovacom prostredí a pri produkcii určte časový rozpočet aj záťaž, ktorú povoľujete.

V pláne porovnajte odhad riadkov so skutočnosťou, pozrite počet opakovaní uzlov a množstvo vyradených riadkov. Veľký rozdiel môže viesť k prevereniu štatistík alebo nerovnomerného rozdelenia dát. Sekvenčné čítanie nie je automaticky chyba: pri veľkej časti tabuľky môže byť vhodnejšie než množstvo náhodných čítaní cez index.

Index navrhujte podľa filtra a poradia

Pre zoznam jedného klienta zoradený podľa času vytvorenia môže byť kandidátom B-tree index nad tenant_id, created_at a id. Rovnosť klienta obmedzí rozsah a ďalšie stĺpce zodpovedajú poradiu. Použitie a prínos treba potvrdiť plánom aj meraním; závisia od distribúcie dát a dotazu. Ďalší filter, napríklad stav objednávky, môže viesť k odlišnému návrhu.

Každý index tiež zaberá miesto a pri zápise vyžaduje údržbu. Nevytvárajte všetky kombinácie filtrov iba podľa zoznamu polí vo formulári. Vyberte významné prevádzkové dotazy, porovnajte kandidátov a preverujte aj čas vkladania a aktualizácií. Spôsob vytvorenia indexu za prevádzky je samostatná zmena s pravidlami pre zámky a obnovu.

Kurzoru dajte jednoznačné a nemenné poradie

Veľký OFFSET môže byť drahý, pretože databáza musí spracovať aj preskočené riadky. Pri priebežnom prechádzaní zoznamu možno namiesto čísla stránky použiť poslednú zobrazenú dvojicu created_at a id. Príklad predpokladá neprázdne hodnoty, nemenný čas vytvorenia a jednoznačné id. Oba stĺpce sa porovnávajú aj radia rovnakým zostupným smerom.

Kurzoru patrí aj pôvodný filter. Server overí jeho formát a rozsah a klienta znovu určí z autorizovaného kontextu. Nové objednávky pred kurzorom sa pri pokračovaní nezobrazia; zmena filtra alebo poradia má začať nový priechod. Také čítanie nevytvára konzistentný historický snapshot. Export, ktorý ho potrebuje, vyžaduje osobitnú dohodu o izolácii a životnosti čítania.

SELECT id, created_at, status
FROM orders
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;

N+1 dotazy môže vytvoriť aj malý výsledok

Aplikácia načíta jednu stránku objednávok a potom samostatne zisťuje názov zákazníka pri každom riadku. Databázový index nezruší sieťové čakanie na desiatky ďalších dotazov. Podľa dátového modelu môže pomôcť JOIN alebo dávkové načítanie súvisiacich záznamov. Pri viacnásobných väzbách však JOIN môže zväčšiť výsledok; nestačí mechanicky spojiť všetky tabuľky.

Pre prehľad často netreba všetky položky, prílohy a dlhé texty objednávky. Vráťte polia, ktoré sa naozaj zobrazujú, a detaily čítajte samostatne. Po optimalizácii skontrolujte počet dotazov, veľkosť odpovede aj výsledné oprávnenia. Dávkové čítanie nesmie rozšíriť rozsah klienta, ktorý predtým kontroloval každý jednotlivý dotaz.

Porovnajte výsledok aj pri súbežných zmenách

Meranie výkonu spojte s kontrolou správnosti. Pripravte rovnaké časy vytvorenia pri viacerých objednávkach, veľkého klienta a vloženie novej objednávky medzi stránkami. Overte, že sa zachová zvolené poradie a že pokračovanie zodpovedá zdokumentovanému kontraktu. Používateľ musí rozumieť tomu, či prechádza živý zoznam alebo pevný export.

• Pri rovnakom created_at rozhoduje id, takže hranica stránky je jednoznačná.
• Zmena filtra zruší starý kurzor a nezmieša dve výsledkové množiny.
• Priechod pri veľkom klientovi má zmeranú latenciu a počet dotazov.
• Nový index nezvýši čas významných zápisov nad dohodnutý limit.

Od témy ku konkrétnemu riešeniu.