Ahojte! Som backend vývojár, píšem mikroslužby na Jave + Spring. Pracujem v jednom z tímov vyvíjajúcich interné produkty v spoločnosti Tinkoff.
V našom tíme sa často rieši otázka optimalizácie dopytov v DBMS. Vždy sa chceme dostať k rýchlejšiemu vykonaniu, ale nie vždy môžeme dosiahnuť, že premyslene vybudované indexy postačia — musíme hľadať nejaké obchádzajúce cesty. Počas jedného z takýchto potuliek po internete v snahe nájsť rozumné optimalizácie pri práci s databázami som našiel , autora knihy SQL Performance Explained. Toto je ten vzácny typ blogov, v ktorých sa dajú čítať všetky články jeden za druhým.
Chcem vám preložiť malý článok od Marcusa. Dá sa nazvať do určitej miery manifestom, ktorý sa snaží pritiahnuť pozornosť k starému, ale dodnes aktuálnemu problému výkonnosti operácie offset podľa štandardu SQL.
Na niektorých miestach budem autora dopĺňať vysvetleniami a poznámkami. Všetky takéto miesta budem označovať ako „prip.“ pre väčšiu jasnosť.
Krátke úvodné slovo
Myslím, že mnohí vedia, ako problematická a pomalá môže byť práca s stránkovými selectami cez offset. A viete, že sa to dá celkom jednoducho nahradiť výkonnejšou konštrukciou?
Takže, kľúčové slovo offset príkazu hovorí databáze, aby preskočila prvých n záznamov v dopyte. Avšak databáza stále musí prečítať týchto prvých n záznamov z disku, a to v určenom poradí (prip.: aplikujte triedenie, ak je zadané), a až potom bude možné vrátiť záznamy počnúc n+1 a ďalej. Najzaujímavejšie na tom je, že problém nie je v konkrétnej implementácii v DBMS, ale v pôvodnej definícii podľa štandardu:
…the rows are first sorted according to the and then limited by dropping the number of rows specified in the from the beginning…
-SQL:2016, Part 2, 4.15.3 Derived tables (prip.: teraz najpoužívanejší štandard)
Kľúčovým bodom je, že offset prijíma jediný parameter — počet záznamov, ktoré treba preskočiť, a to je všetko. Podľa takej definície môže DBMS len načítať všetky záznamy a potom odhodiť tie nepotrebné. Je jasné, že taká definícia offsetu núti vykonávať zbytočnú prácu. A tu nezáleží na tom, či to je SQL alebo NoSQL.
Ešte trochu bolesti
Problémy s offsetom sa týmto nekončia, a to z nasledujúcich dôvodov. Ak medzi čítaním dvoch stránok údajov z disku iná operácia vloží nový záznam, čo sa v takom prípade stane?
Keď sa používa offset na preskočenie záznamov z predchádzajúcich strán, v situácii, keď dochádza k pridaniu nového záznamu medzi operáciami čítania rôznych strán, pravdepodobne získate duplicity (poznámka: toto je možné, keď čítame stránkovane použitím konštrukcie order by, potom sa do našich výsledkov môže dostať nový záznam).
Obrázok názorne znázorňuje takúto situáciu. Databáza číta prvých 10 záznamov, po tom sa vloží nový záznam, ktorý posunie všetky prečítané záznamy o 1. Potom databáza vezme novú stránku z ďalších 10 záznamov a začína nie s 11. ako by mala, ale s 10., čím sa duplicitne zobrazuje tento záznam. Existujú aj ďalšie anomálie spojené s používaním tohto výrazu, ale táto je najbežnejšia.
Ako sme už zistili, to nie sú problémy konkrétneho DBMS alebo jeho implementácií. Problém je v definovaní paginácie podľa štandardu SQL. Hovoríme DBMS, ktorú stránku treba načítať alebo koľko záznamov preskočiť. Databáza jednoducho nie je schopná optimalizovať takýto dotaz, pretože má príliš málo informácií.
Stojí za zmienku, že toto nie je problém konkrétneho kľúčového slova, ale skôr sémantiky dotazu. Existuje niekoľko ďalších identických syntaktických problémov:
- Kľúčové slovo offset, ako sme už predtým spomínali.
- Konštrukcia z dvoch kľúčových slov limit [offset] (aj keď limit sám o sebe nie je až tak zlý).
- Filtrácia podľa dolných hraníc, založená na číslovaní riadkov (napríklad row_number(), rownum a podobne).
Všetky tieto výrazy len hovoria, koľko riadkov treba preskočiť, žiadne dodatočné informácie alebo kontext.
Ďalej v tomto článku sa kľúčové slovo offset používa ako zhrnutie všetkých týchto variantov.
Život bez OFFSET
A teraz si predstavme, aký by bol náš svet bez všetkých týchto problémov. Ukazuje sa, že život bez offsetu nie je taký zložitý: pomocou selectu môžeme vyberať len tie riadky, ktoré sme ešte nevideli (poznámka: to znamená, že tie, ktoré neboli na predchádzajúcej stránke), s pomocou podmienky vo where.
V tomto prípade vychádzame z faktu, že selekty sa vykonávajú nad usporiadanou množinou (staré dobré order by). Keďže máme usporiadanú množinu, môžeme použiť dosť jednoduchý filter na získanie iba tých údajov, ktoré sa nachádzajú za posledným záznamom predchádzajúcej stránky:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYA to je celý princíp takého prístupu. Samozrejme, pri radení podľa mnohých stĺpcov je to ešte zaujímavejšie, ale myšlienka je stále tá istá. Je dôležité poznamenať, že táto konštrukcia je aplikovateľná na mnohé -riešeniach.
Takýto prístup sa nazýva seek method alebo keyset pagination. Rieši problém s plávajúcim výsledkom (pozn.: situácia so záznamom medzi čítaním stránok, opísaná skôr) a, samozrejme, že všetci to máme radi, funguje rýchlejšie a stabilnejšie ako klasický offset. Stabilita spočíva v tom, že čas spracovania dopytu sa nezvyšuje proporcionálne s číslom požadovanej tabuľky (pozn.: ak sa chcete podrobnejšie dozvedieť o práci rôznych prístupov k paginácii, môžete . Tam nájdete aj porovnávacie benchmarky pre rôzne metódy).
Jeden zo snímkov , že paginácia podľa kľúčov, samozrejme, nie je všemohúca — má svoje obmedzenia. Najvýznamnejšie — nemá možnosť čítať náhodné stránky (pozn.: neporadí sa). Avšak v ére nekonečného rolovania (pozn.: na frontende) to nie je až taký problém. Uvádzanie čísla stránky pre kliknutie — v každom prípade zlé rozhodnutie pri vývoji UI (pozn.: názor autora článku).
A čo s nástrojmi?
Paginácia na kľúčoch často nie je vhodná z dôvodu nedostatku nástrojovej podpory tejto metódy. Väčšina vývojových nástrojov, vrátane rôznych rámcov, nedáva na výber, akým spôsobom sa bude vykonávať paginácia.
Situáciu zhoršuje to, že opísaná metóda vyžaduje komplexnú podporu v používaných technológiách — od DBMS až po vykonávanie AJAX dopytov v prehliadači pri nekonečnom rolovaní. Namiesto toho, aby sa uvádzalo iba číslo stránky, teraz bude treba uviesť súbor kľúčov pre všetky stránky naraz.
Avšak počet rámcov, ktoré podporujú stránkovanie na kľúčoch, postupne rastie. Toto je aktuálny zoznam:
- pre Java;
- pre Ruby;
- a pre Django;
- pre Python;
- — criteria API pre implementácie JPA;
- pre Perl;
- , мапер для Node.js .
(Poznámka: niektoré odkazy boli odstránené, pretože v čase prekladu niektoré knižnice neboli aktualizované od roku 2017—2018. Ak máte záujem, môžete sa pozrieť na pôvodný zdroj.)
Práve v tomto bode potrebujem vašu pomoc. Ak vyvíjate alebo podporujete rámec, ktorý nejako využíva stránkovanie, prosím, prosím, vyzývam vás, aby ste pridali natívnu podporu pre stránkovanie na kľúčoch. Ak máte otázky alebo potrebujete pomoc, budem rád, ak vám pomôžem (, , ) (pozn.: z mojej skúsenosti s Markusom môžem povedať, že naozaj s nadšením pristupuje k rozširovaniu tejto témy).
Ak používate hotové riešenia, ktoré si myslíte, že si zaslúžia mať podporu stránkovania na kľúčoch, — vytvorte požiadavku alebo navrhnite dokončené riešenie, ak je to možné. Môžete tiež pridať tento článok ako odkaz.
Záver
Dôvod, prečo je taký jednoduchý a užitočný prístup, ako stránkovanie na kľúčoch, málo rozšírený, nie je v tom, že by bol technicky zložitý alebo by vyžadoval nejaké veľké úsilie. Hlavným dôvodom je, že mnohí si zvykli vidieť a pracovať s offsetom — takýto prístup je daný samotným štandardom.
V dôsledku toho málokto premýšľa o zmene prístupu k stránkovaniu, a preto podpora zo strany rámcov a knižníc sa rozvíja len slabšie. Preto, ak sa vám páči myšlienka a cieľ bezoffsetového stránkovania, — pomôžte tento prístup šíriť!
Zdroj:
Autor: Markus Winand
Zdroj: habr.com
