Всім привіт! Я бекенд-розробник, пишу мікросервіси на 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Ось і принцип такого підходу. Звичайно, при сортуванні по багатьох стовпцях все стає веселіше, але ідея та ж. Важливо зауважити, що ця конструкція застосовна для багатьох -Рішення.
Такий підхід називається seek method або keyset pagination. Він вирішує проблему з плаваючим результатом (прим.: ситуація із записом між читаннями сторінок, описана раніше) і, звичайно, що ми всі любимо, працює швидше та стабільніше, ніж класичний offset. Стабільність полягає в тому, що час обробки запиту не збільшується пропорційно номеру таблиці, що запитується (прим.: якщо хочеться докладніше дізнатися про роботу різних підходів до пагінації, можна . Там же можна знайти порівняльні бенчмарки з різних методів).
Один із слайдів , Що пагінація за ключами, звичайно ж, не всемогутня - вона має свої обмеження. Найбільш значуще - вона не має можливості читати випадкові сторінки (прим.: непослідовно). Проте в епоху нескінченного скролінгу (прим.: на фронтенді) це не така вже й проблема. Вказівка номера сторінки для клацання — у разі погане рішення під час розробки UI (прим.: думка автора статті).
А що з інструментами?
Пагінація на ключах часто не підходить через відсутність інструментальної підтримки цього методу. Більшість інструментів розробки, у тому числі різні фреймворки, не дають вибору, яким саме способом буде виконуватися пагінація.
Ситуацію посилює те, що описаний метод вимагає наскрізної підтримки у технологіях, що використовуються - починаючи від СУБД і закінчуючи виконанням AJAX-запиту в браузері при нескінченному скролінгу. Замість того, щоб вказувати лише номер сторінки, тепер доведеться вказувати набір ключів для всіх сторінок відразу.
Проте кількість фреймворків, які підтримують пагінацію на ключах, поступово зростає. Ось що є зараз:
- для Java;
- для Ruby;
- и для Django;
- для Python;
- - Criteria API для реалізацій JPA;
- для Perl;
- , мапер для Node.js .
(Прим.: деякі посилання були прибрані через те, що на момент перекладу деякі бібліотеки не оновлювалися з 2017-2018 року. Якщо цікаво, можна зазирнути в першоджерело.)
Саме на цьому моменті і потрібна ваша допомога. Якщо ви розробляєте чи підтримуєте фреймворк, який хоч якось використовує пагінацію, то я прошу, я закликаю, я благаю вас зробити нативну підтримку для пагінації на ключах. Якщо є питання або вам потрібна допомога, я буду рада допомогти (, , ) (прим.: на мій досвід спілкування з Маркусом можу сказати, що він справді ставиться з ентузіазмом до поширення цієї теми).
Якщо ж ви користуєтеся готовими рішеннями, які, як ви гадаєте, гідні підтримки пагінації по ключах, — створіть реквест або навіть запропонуйте готове рішення, якщо це можливо. Можна також вказати на посилання цю статтю.
Висновок
Причина чому такий простий і корисний підхід, як пагінація по ключах, мало поширений, не в тому, що це складно в технічній реалізації або вимагає якихось великих зусиль. Головна причина в тому, що багато хто звикли бачити і працювати з offset — такий підхід диктується самим стандартом.
Як наслідок, мало хто замислюється про зміну підходу до пагінації, а через це й інструментальна підтримка з боку фреймворків та бібліотек розвивається слабо. Тому, якщо вам близька ідея та мета безофсетної пагінації, допоможіть поширити її!
Джерело:
Автор: Markus Winand
Джерело: habr.com