Časy, keď ste sa nemuseli starať o optimalizáciu výkonu databázy, sú preč. Čas nestojí. Každý nový technologický podnikateľ chce vytvoriť ďalší Facebook a zároveň sa snaží zhromaždiť všetky údaje, ktoré sa im dostanú do rúk. Firmy potrebujú tieto údaje na lepšie školenie modelov, ktoré im pomôžu zarobiť peniaze. V takýchto podmienkach musia programátori vytvoriť API, ktoré im umožní rýchlo a spoľahlivo pracovať s obrovským množstvom informácií.
Ak ste navrhovali aplikačné alebo databázové backendy akokoľvek dlho, pravdepodobne ste napísali kód na spúšťanie stránkovaných dotazov. Napríklad takto:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Ako to je?
Ale ak ste stránkovanie urobili takto, je mi ľúto, že ste to neurobili tým najefektívnejším spôsobom.
Chcete mi namietať? . , и Už používajú techniky, o ktorých chcem dnes hovoriť.
Uveďte aspoň jedného backendového vývojára, ktorý nikdy nepoužil OFFSET и LIMIT na vykonávanie stránkovaných dotazov. V MVP (Minimum Viable Product) a v projektoch, kde sa používajú malé množstvá údajov, je tento prístup celkom použiteľný. Takpovediac „jednoducho to funguje“.
Ak však potrebujete vytvoriť spoľahlivé a efektívne systémy od začiatku, mali by ste sa vopred postarať o efektívnosť dotazovania v databázach používaných v takýchto systémoch.
Dnes si povieme o problémoch s bežne používanými (škoda) implementáciami stránkovaných dotazovacích strojov a o tom, ako dosiahnuť vysoký výkon pri vykonávaní takýchto dotazov.
Čo je zlé na OFFSET a LIMIT?
Ako už bolo povedané, OFFSET и LIMIT Dobre fungujú v projektoch, ktoré nepotrebujú pracovať s veľkým množstvom údajov.
Problém nastáva, keď databáza narastie do takej veľkosti, že sa už nezmestí do pamäte servera. Pri práci s touto databázou však musíte používať stránkované dotazy.
Aby sa tento problém prejavil, musí nastať situácia, v ktorej sa DBMS pri každom stránkovanom dotaze uchýli k neefektívnej operácii Full Table Scan (zatiaľ čo môžu nastať operácie vloženia a vymazania a nepotrebujeme zastarané údaje!).
Čo je to „skenovanie celej tabuľky“ (alebo „sekvenčné skenovanie tabuľky“, sekvenčné skenovanie)? Ide o operáciu, počas ktorej DBMS postupne načítava každý riadok tabuľky, teda údaje v nej obsiahnuté, a kontroluje ich súlad s danou podmienkou. Tento typ skenovania tabuľky je známy ako najpomalší. Faktom je, že keď sa spustí, vykoná sa veľa vstupných/výstupných operácií, ktoré zahŕňajú diskový subsystém servera. Situáciu zhoršuje latencia spojená s prácou s dátami uloženými na diskoch a skutočnosť, že prenos dát z disku do pamäte je operácia náročná na zdroje.
Napríklad máte záznamy o 100000000 XNUMX XNUMX používateľoch a spustíte dotaz s konštruktom OFFSET 50000000. To znamená, že DBMS bude musieť načítať všetky tieto záznamy (a my ich ani nepotrebujeme!), uložiť ich do pamäte a potom vziať povedzme 20 výsledkov zaznamenaných v LIMIT.
Povedzme, že by to mohlo vyzerať takto: „vyberte riadky od 50000 50020 do 100000 50000 z XNUMX XNUMX“. To znamená, že systém bude musieť najprv načítať XNUMX XNUMX riadkov na dokončenie dotazu. Vidíte, koľko zbytočnej práce bude musieť urobiť?
Ak mi neveríte, pozrite sa na príklad, ktorý som vytvoril pomocou funkcií .
Príklad na db-fiddle.com
Tam, vľavo, v poli Schema SQL, existuje kód, ktorý vloží 100000 XNUMX riadkov do databázy a napravo do poľa Query SQL, zobrazia sa dva dotazy. Prvý, pomalý, vyzerá takto:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
A druhý, ktorý je efektívnym riešením toho istého problému, je takýto:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Na splnenie týchto požiadaviek stačí kliknúť na tlačidlo Run v hornej časti stránky. Potom porovnáme informácie o čase vykonania dotazu. Ukázalo sa, že vykonanie neúčinného dotazu trvá najmenej 30-krát dlhšie ako vykonanie druhého (tento čas sa líši od spustenia k spusteniu; systém môže napríklad hlásiť, že dokončenie prvého dotazu trvalo 37 ms, ale spustenie sekunda - 1 ms).
A ak bude viac údajov, všetko bude vyzerať ešte horšie (aby ste sa o tom presvedčili, pozrite sa na moje s 10 miliónmi riadkov).
To, o čom sme práve hovorili, by vám malo poskytnúť určitý prehľad o tom, ako sa v skutočnosti spracúvajú databázové dotazy.
Upozorňujeme, že čím vyššia je hodnota OFFSET — čím dlhšie bude vybavenie žiadosti trvať.
Čo by som mal použiť namiesto kombinácie OFFSET a LIMIT?
Namiesto kombinácie OFFSET и LIMIT Stojí za to použiť štruktúru postavenú podľa nasledujúcej schémy:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Toto je vykonávanie dotazu so stránkovaním na základe kurzora.
Namiesto lokálneho ukladania aktuálnych OFFSET и LIMIT a prenášať ich pri každej požiadavke, musíte si uložiť posledný prijatý primárny kľúč (zvyčajne to je ID) A LIMITv dôsledku toho budú získané dopyty podobné tým, ktoré sú uvedené vyššie.
prečo? Ide o to, že explicitným uvedením identifikátora posledného prečítaného riadku poviete svojmu DBMS, kde má začať hľadať potrebné údaje. Navyše, vyhľadávanie vďaka použitiu kľúča bude prebiehať efektívne, systém sa nebude musieť rozptyľovať linkami mimo určeného rozsahu.
Pozrime sa na nasledujúce porovnanie výkonnosti rôznych dopytov. Tu je neúčinný dotaz.
Pomalá požiadavka
A tu je optimalizovaná verzia tejto požiadavky.
Rýchla žiadosť
Oba dotazy vracajú presne rovnaké množstvo údajov. Ale dokončenie prvého trvá 12,80 sekundy a druhé 0,01 sekundy. Cítiš ten rozdiel?
Možné problémy
Aby navrhovaná metóda dotazu fungovala efektívne, tabuľka musí mať stĺpec (alebo stĺpce) obsahujúci jedinečné sekvenčné indexy, ako je celočíselný identifikátor. V niektorých špecifických prípadoch to môže rozhodovať o úspešnosti použitia takýchto dotazov na zvýšenie rýchlosti práce s databázou.
Prirodzene, pri konštrukcii dotazov musíte brať do úvahy špecifickú architektúru tabuliek a vybrať tie mechanizmy, ktoré budú najlepšie fungovať na existujúcich tabuľkách. Napríklad, ak potrebujete pracovať v dopytoch s veľkým objemom súvisiacich údajov, môže vás to zaujímať článok.
Ak sa stretávame s problémom chýbajúceho primárneho kľúča, napríklad ak máme tabuľku so vzťahom many-to-many, potom tradičný prístup používania OFFSET и LIMIT, nám zaručene vyhovuje. Jeho použitie však môže viesť k potenciálne pomalým dopytom. V takýchto prípadoch by som odporučil použiť automaticky sa zvyšujúci primárny kľúč, aj keď je potrebný iba na spracovanie stránkovaných dopytov.
Ak vás táto téma zaujíma - , и - niekoľko užitočných materiálov.
Výsledky
Hlavný záver, ktorý môžeme vyvodiť, je, že bez ohľadu na to, o akej veľkosti databáz hovoríme, vždy je potrebné analyzovať rýchlosť vykonávania dotazov. V dnešnej dobe je škálovateľnosť riešení mimoriadne dôležitá a ak je všetko správne navrhnuté už od začiatku práce na určitom systéme, môže to vývojára v budúcnosti zachrániť od mnohých problémov.
Ako analyzujete a optimalizujete databázové dotazy?
Zdroj: hab.com