Ahoj všichni! Jsem backendový vývojář a píšu mikroslužby v Java + Spring. Pracuji v jednom z interních týmů vývoje produktů ve společnosti Tinkoff.
V našem týmu často vyvstává otázka optimalizace dotazů v DBMS. Vždy chcete být o něco rychlejší, ale ne vždy si vystačíte s promyšleně sestavenými indexy – musíte hledat nějaká řešení. Při jednom z těchto toulek po webu při hledání rozumných optimalizací při práci s databázemi jsem našel , autor SQL Performance Explained. Toto je vzácný typ blogu, ve kterém si můžete přečíst všechny články za sebou.
Rád bych pro vás přeložil krátký článek od Marcuse. Dá se to do jisté míry nazvat manifestem, který se snaží upozornit na starý, ale stále aktuální problém provádění operace offsetu podle standardu SQL.
Na některých místech autora doplním vysvětlivkami a komentáři. Všechna taková místa budu označovat jako „cca. pro větší přehlednost
Malý úvod
Myslím, že mnoho lidí ví, jak problematická a pomalá je práce s výběrem stránek přes offset. Věděli jste, že jej lze poměrně snadno nahradit účinnějším designem?
Klíčové slovo offset tedy říká databázi, aby vynechala prvních n záznamů v požadavku. Databáze však ještě potřebuje načíst těchto prvních n záznamů z disku v daném pořadí (poznámka: aplikujte třídění, pokud je zadáno), a teprve poté bude možné vrátit záznamy od n+1 výše. Nejzajímavější je, že problém není v konkrétní implementaci v DBMS, ale v původní definici podle standardu:
…řádky jsou nejprve seřazeny podle a poté omezena vypuštěním počtu řádků specifikovaných v od začátku…
-SQL:2016, část 2, 4.15.3 Odvozené tabulky (poznámka: v současnosti nejpoužívanější standard)
Klíčovým bodem je, že offset má jediný parametr - počet záznamů, které se mají přeskočit, a to je vše. Podle této definice může DBMS pouze načíst všechny záznamy a poté vyřadit nepotřebné. Je zřejmé, že tato definice ofsetu nás nutí dělat práci navíc. A je jedno, jestli je to SQL nebo NoSQL.
Jen trochu víc bolesti
Problémy s offsetem tím nekončí a zde je důvod. Pokud mezi čtením dvou stránek dat z disku jiná operace vloží nový záznam, co se stane v tomto případě?
Když se offset používá k přeskakování záznamů z předchozích stránek, v situaci přidání nového záznamu mezi čteními různých stránek s největší pravděpodobností získáte duplikáty (poznámka: to je možné, když čteme stránku po stránce pomocí pořadí po konstrukci, pak uprostřed našeho výstupu může získat nový záznam).
Obrázek tuto situaci jasně znázorňuje. Báze přečte prvních 10 záznamů, poté se vloží nový záznam, který posune všechny přečtené záznamy o 1. Poté základ vezme novou stránku z dalších 10 záznamů a nezačne od 11, jak by měl, ale od 10., duplikuje tento záznam. S používáním tohoto výrazu jsou spojeny i další anomálie, ale toto je nejčastější.
Jak jsme již zjistili, nejedná se o problémy konkrétních DBMS nebo jejich implementací. Problém je v definování stránkování podle standardu SQL. Říkáme DBMS, kterou stránku má načíst nebo kolik záznamů má přeskočit. Databáze prostě není schopna takový požadavek optimalizovat, protože na to je příliš málo informací.
Je také vhodné upřesnit, že nejde o problém konkrétního klíčového slova, ale spíše sémantiky dotazu. Existuje několik dalších syntaxí, které jsou ve své problematické povaze totožné:
- Klíčové slovo offset je, jak bylo zmíněno dříve.
- Konstrukce dvou klíčových slov limit [offset] (i když limit sám o sobě není tak špatný).
- Filtrování podle spodních mezí na základě číslování řádků (například row_number(), rownum atd.).
Všechny tyto výrazy vám jednoduše říkají, kolik řádků se má přeskočit, žádné další informace nebo kontext.
Dále v tomto článku je klíčové slovo offset použito jako souhrn všech těchto možností.
Život bez OFFSETU
Nyní si představme, jaký by byl náš svět bez všech těchto problémů. Ukazuje se, že život bez offsetu není tak obtížný: pomocí select můžete vybrat pouze ty řádky, které jsme ještě neviděli (pozn.: tedy ty, které nebyly na předchozí stránce), pomocí podmínky kde.
V tomto případě vycházíme ze skutečnosti, že výběry se provádějí na uspořádané sadě (staré dobré pořadí). Vzhledem k tomu, že máme uspořádanou sadu, můžeme pomocí poměrně jednoduchého filtru získat pouze data, která jsou za posledním záznamem předchozí stránky:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYTo je celý princip tohoto přístupu. Při řazení podle mnoha sloupců jsou věci samozřejmě zábavnější, ale myšlenka je stále stejná. Je důležité si uvědomit, že tento design je použitelný pro mnoho -rozhodnutí.
Tento přístup se nazývá metoda vyhledávání nebo stránkování sady klíčů. Řeší problém s plovoucím výsledkem (pozn. výše popsaná situace s psaním mezi čteními stránek) a samozřejmě to, co všichni milujeme, funguje rychleji a stabilněji než klasický offset. Stabilita spočívá v tom, že doba zpracování požadavku se nezvyšuje úměrně počtu požadované tabulky (poznámka: pokud se chcete dozvědět více o práci různých přístupů k stránkování, můžete . Můžete zde také najít srovnávací benchmarky pro různé metody).
Jeden z diapozitivů že stránkování klíči samozřejmě není všemocné – má svá omezení. Nejvýznamnější je, že nemá schopnost číst náhodné stránky (pozn.: nekonzistentně). V době nekonečného rolování (pozn.: na frontendu) to však není takový problém. Určení čísla stránky pro kliknutí je každopádně špatné rozhodnutí v UI designu (pozn.: názor autora článku).
A co nástroje?
Stránkování na klávesách často není vhodné kvůli chybějící instrumentální podpoře této metody. Většina vývojových nástrojů, včetně různých frameworků, neumožňuje přesně zvolit, jak bude stránkování prováděno.
Situaci ztěžuje fakt, že popisovaný způsob vyžaduje end-to-end podporu v používaných technologiích – od DBMS až po provedení AJAX požadavku v prohlížeči s nekonečným rolováním. Místo zadávání pouze čísla stránky nyní musíte zadat sadu klíčů pro všechny stránky najednou.
Postupně však roste počet frameworků, které stránkování na klíčích podporují. V tuto chvíli máme následující:
- pro Javu;
- pro Ruby;
- и pro Djanga;
- pro Python;
- — API kritérií pro implementace SPS;
- pro Perl;
- , mapovač pro Node.js .
(Poznámka: některé odkazy byly odstraněny z důvodu, že v době překladu nebyly některé knihovny aktualizovány od roku 2017-2018. V případě zájmu se můžete podívat na původní zdroj.)
Právě v tuto chvíli je potřeba vaše pomoc. Pokud vyvíjíte nebo podporujete rámec, který využívá stránkování, pak vás žádám, naléhavě žádám, abyste poskytli nativní podporu pro stránkování na klíčích. Pokud máte dotazy nebo potřebujete pomoc, rád pomůžu (, , ) (poznámka: ze své zkušenosti s Marcusem mohu říci, že je opravdu nadšený ze šíření tohoto tématu).
Pokud používáte hotová řešení, o kterých si myslíte, že si zaslouží mít podporu pro stránkování pomocí klíčů, vytvořte požadavek nebo dokonce nabídněte hotové řešení, je-li to možné. Můžete také odkazovat na tento článek.
Závěr
Důvodem, proč tak jednoduchý a užitečný přístup, jako je stránkování pomocí kláves není rozšířený, není to, že by bylo obtížné jej technicky implementovat nebo by vyžadovalo velké úsilí. Hlavním důvodem je, že mnozí jsou zvyklí vidět a pracovat s offsetem - tento přístup je diktován samotnou normou.
Výsledkem je, že jen málo lidí přemýšlí o změně přístupu k stránkování, a proto se instrumentální podpora z rámců a knihoven rozvíjí špatně. Pokud je vám tedy myšlenka a cíl stránkování bez offsetu blízká, pomozte ji šířit!
Zdroj:
Autor: Markus Winand
Zdroj: www.habr.com