Защо е необходима инструментална поддръжка на пагинацията по ключове

Здравейте на всички! Аз съм бекенд разработчик, пиша микросервиси на Java + Spring. Работя в един от екипите за разработка на вътрешни продукти в компанията Тинькофф.

В нашия екип често се поставя въпросът за оптимизация на заявките в СУБД. Винаги искаме да бъдем малко по-бързи, но не винаги можем да разчитаме само на добре проектирани индекси — понякога трябва да търсим обходни решения. По време на едно от тези търсения в мрежата за разумни оптимизации при работа с БД, открих безкрайно полезния блог на Маркус Винанд, автор на книгата SQL Performance Explained. Това е онзи рядък тип блог, в който можеш да четеш всички статии една след друга.

Искам да преведа за вас една малка статия на Маркус. Може да се нарече в известен смисъл манифест, който цели да привлече вниманието към стар, но все още актуален проблем на производителността на операцията offset според SQL стандарта.

На някои места ще добавя пояснения и бележки от автора. Всички тези места ще обозначавам като „прим.“ за по-голяма яснота.

Небольшое введение

Мисля, че много хора знаят колко проблемна и бавна може да бъде работата с постранични селекти чрез offset. Но знаете ли, че може да бъде доста лесно заменена с по-производителна конструкция?

И така, ключовото слово offset указва на базата да пропусне първите n записа в заявката. Въпреки това базата все още трябва да прочете тези първи n записа от диска, в зададения ред (прим.: да приложи сортиране, ако е зададено), и само след това може да върне записите, започвайки от n+1 и нататък. Най-интересното е, че проблемът не е в конкретната реализация в СУБД, а в първоначалното определение по стандарта:

…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 (прим.: в момента най-използваният стандарт)

Ключовият момент тук е, че offset приема единствен параметър — броя на записите, които трябва да се пропуснат, и това е всичко. Следвайки това определение, СУБД може само да извлече всички записи и след това да отхвърли ненужните. Очевидно е, че такова определение на offset-а кара да се върши излишна работа. И тук дори не е важно дали е SQL или NoSQL.

Още малко болка

Проблемите с offset не се изчерпват тук, и ето защо. Ако между четене на две страници данни от диска бъде извършена друга операция, която да добави нов запис, какво ще се случи в този случай?

Когато се използва offset за пропускане на записи от предишните страници, в ситуация, в която нов запис бъде добавен между операциите на четене на различни страници, най-вероятно ще получите дубликати (например: това е възможно, когато четем странично, използвайки конструкцията order by, и нов запис може да влезе в средата на нашето изходно представяне).

Илюстрацията ясно показва такава ситуация. Базата данни чете първите 10 записа, след което се добавя нов запис, който измествя всички прочетени записи с 1. След това базата взима нова страница с 10 следващи записа и започва не от 11-ия, както е трябвало, а от 10-ия, дублирайки този запис. Съществуват и други аномалии, свързани с използването на това изразяване, но тази е най-често срещаната.

Както вече установихме, тези проблеми не са специфични за конкретна СУБД или техните реализации. Проблемът е в дефиницията на пагинацията по стандарта SQL. Ние казваме на СУБД коя страница трябва да извлече или колко записи да пропусне. Базата просто не може да оптимизира такава заявка, тъй като информацията е твърде малко.

Също така е уместно да уточним, че това не е проблем на конкретна ключова дума, а по-скоро семантика на заявката. Има още няколко идентични по проблемност синтаксиса:

• Ключовата дума offset, както беше посочено по-рано.
• Конструкция от две ключови думи limit [offset] (въпреки че самата limit не е толкова лоша).
• Филтриране по долни граници, основано на номерация на редовете (например, row_number(), rownum и т.н.).

Всички тези изрази просто казват колко реда трябва да се пропуснат, без допълнителна информация или контекст.

По-нататък в тази статия ключовата дума offset се използва като обобщение на всички тези варианти.

Живот без OFFSET

А сега си представете как щеше да изглежда нашият свят без тези проблеми. Оказва се, че животът без offset не е толкова сложен: с селект можем да избираме само онези редове, които все още не сме видели (например: т.е. тези, които не са били на предишната страница), с помощта на условие в where.

В този случай се основаваме на факта, че селектите се изпълняват върху подредено множество (старият добър order by). Тъй като имаме подредено множество, можем да използваме доста прост филтър, за да извлечем само данните, които са след последния запис от предишната страница:

    SELECT ...
    FROM ...
    WHERE ...
    AND id < ?last_seen_id
    ORDER BY id DESC
    FETCH FIRST 10 ROWS ONLY

И това е целият принцип на такъв подход. Разбира се, при сортиране по много колони всичко става по-весело, но идеята си остава същата. Важно е да се отбележи, че тази конструкция е приложима на много NoSQL-решения.

Този подход се нарича seek method или keyset pagination. Той решава проблема с плаващия резултат (бележка: ситуацията с запис между четенията на страниците, описана по-горе) и, разбира се, работи по-бързо и стабилно от класическия offset. Стабилността се състои в това, че времето за обработка на заявка не се увеличава пропорционално на номера на заявената таблица (бележка: ако искате да научите повече за работата на различните подходи към пагинацията, можете да прегледате презентацията на автора. Там също можете да намерите сравнителни бенчмарки по различните методи).

Един от слайдовете разказва за това, че пагинацията по ключове, разбира се, не е всемогъща — тя има свои ограничения. Най-значимото е, че няма възможност да се четат случайни страници (бележка: непоследователно). Въпреки това, в епохата на безкрайното скролиране (бележка: на фронтенда) това не е толкова голям проблем. Указването на номера на страницата за кликване — в крайна сметка лошо решение при разработката на UI (бележка: мнение на автора на статията).

А какво става с инструментите?

Пагинацията по ключове често не е подходяща поради липса на инструментална поддръжка на този метод. Повечето от инструментите за разработка, включително различни фреймуърк, не предлагат избор относно начина, по който ще се изпълнява пагинацията.

Ситуацията се усложнява от факта, че описаният метод изисква пълна поддръжка в използваните технологии — от СУБД до изпълнението на AJAX-заявки в браузера при безкрайно скролиране. Вместо да указвате само номера на страницата, сега ще трябва да посочите набор от ключове за всички страници наведнъж.

Обаче броят на фреймворковете, които поддържат пагинация на ключове, постепенно нараства. Ето какво имаме в момента:

• jOOQ за Java;
• order_query за Ruby;
• chunkator и Django Infinite Scroll Pagination за Django;
• SQL Alchemy sqlakeyset за Python;
• blaze-persistence — критерий API за реализации JPA;
• DBIx::Class::Wrapper за Perl;
• Massive.js, мапер для Node.js Keyset Documentation.

(Прим.: някои линкове бяха премахнати, тъй като по време на превода някои библиотеки не бяха обновявани от 2017—2018 година. Ако ви интересува, можете да се запознаете с първоизточника.)

Точно в този момент се нуждаем от вашата помощ. Ако разработвате или поддържате фреймворк, който по някакъв начин използва пагинация, моля, призовавам ви да направите св native поддръжка за пагинация на ключове. Ако имате въпроси или нужда от помощ, ще се радвам да помогна (форум, Twitter, форма за обратна връзка) (прим.: от личния ми опит в общуването с Маркус мога да кажа, че той наистина е ентусиазиран относно разпространението на тази тема).

Ако ползвате готови решения, които мислите, че заслужават поддръжка на пагинация по ключове, — създайте реквест или дори предложете готово решение, ако е възможно. Можете също да споменете тази статия в линка.

Заключение

Причината, поради която такъв прост и полезен подход като пагинация по ключове не е широразпространен, не е, защото е сложно технически или изисква някакви големи усилия. Основната причина е, че много хора свикнали да виждат и работят с offset — този подход е наложен от самия стандарт.

В следствие на това, малко се замислят за промяна на подхода към пагинацията, а поради това инструменталната поддръжка от фреймворковете и библиотеките се развива слабо. Затова, ако ви е близка идеята и целта на безофсетната пагинация, — помогнете да я разпространим!

Източник: https://use-the-index-luke.com/no-offset
Автор: Markus Winand

Източник: habr.com