Не варто користуватися OFFSET та LIMIT у запитах з розбиттям на сторінки

Минули дні, коли треба було турбуватися про оптимізацію продуктивності баз даних. Час не стоїть на місці. Кожен новий бізнесмен зі сфери високих технологій хоче створити черговий Facebook, прагнучи збирати всі дані, до яких може дотягнутися. Ці дані потрібні бізнесу для якіснішого навчання моделей, які допомагають заробляти. У таких умовах програмістам необхідно створювати такі API, які дозволяють швидко та надійно працювати з величезними обсягами інформації.

Якщо ви вже деякий час займаєтеся проектуванням серверних частин програм або баз даних, то ви, ймовірно, писали код для виконання запитів із розбиттям на сторінки. Наприклад - такий:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Так воно і є?

Але якщо розбиття на сторінки ви виконували саме так, я з жалем можу відзначити, що ви робили це далеко не найефективнішим чином.

Хочете мені заперечити? можете НЕ витрачати час. Млявий, Shopify и Міксмакс вже застосовують прийоми, про які я хочу сьогодні розповісти.

Назвіть хоча б одного розробника бекендів, який ніколи не користувався OFFSET и LIMIT для виконання запитів із розбиттям на сторінки. У MVP (Minimum Viable Product, мінімальний життєздатний продукт) і проектах, де використовуються невеликі обсяги даних, цей підхід цілком застосовний. Він, так би мовити, просто працює.

Але якщо потрібно з нуля створювати надійні та ефективні системи, варто заздалегідь подбати про ефективність виконання запитів до баз даних, які використовуються у таких системах.

Сьогодні ми поговоримо про проблеми, що супроводжують широко використовувані (шкода, що це так) реалізації механізмів виконання запитів з розбиттям на сторінки, і про те, як досягти високої продуктивності при виконанні подібних запитів.

Що не так з OFFSET та LIMIT?

Як вже було сказано, OFFSET и LIMIT Добре показують себе у проектах, у яких не потрібно працювати з великими обсягами даних.

Проблема виникає в тому випадку, якщо база даних розростається до розмірів, що перестає поміщатися в пам'яті сервера. Але при цьому під час роботи з цією базою даних потрібно використовувати запити з розбиттям на сторінки.

Для того, щоб ця проблема себе проявила, потрібно, щоб виникла ситуація, в якій СУБД вдається до неефективної операції повного сканування таблиці (Full Table Scan) при виконанні кожного запиту з розбиттям на сторінки (в той же час можуть відбуватися операції зі вставлення та видалення даних) , І застарілі дані нам при цьому не потрібні!).

Що таке "повне сканування таблиці" (або "послідовний перегляд таблиці", Sequential Scan)? Це — операція, в ході якої СУБД послідовно зчитує кожен рядок таблиці, тобто дані, що містяться в ній, і перевіряє їх на відповідність заданій умові. Відомо, що цей тип сканування таблиць є найповільнішим. Річ у тім, що з його виконанні виконується багато операцій вводу/вывода, задіючих дискову підсистему сервера. Ситуацію погіршують затримки, що супроводжують роботу з даними, що зберігаються на дисках, і те, що передача даних з диска на згадку — це ресурсомістка операція.

Наприклад, у вас є записи про 100000000 XNUMX XNUMX користувачів, і ви виконуєте запит з конструкцією OFFSET 50000000. Це означає, що СУБД доведеться завантажити всі ці записи (адже вони нам навіть не потрібні!), помістити їх на згадку, а вже після цього взяти, припустимо, 20 результатів, про які повідомлено в LIMIT.

Скажімо, це може мати такий вигляд: «вибрати рядки від 50000 до 50020 з 100000». Тобто системі для виконання запиту потрібно буде спочатку завантажити 50000 XNUMX рядків. Бачите, як багато непотрібної роботи їй доведеться виконати?

Якщо не вірите, погляньте на приклад, який я створив, користуючись можливостями db-fiddle.com. 

Приклад на db-fiddle.com

Там, ліворуч, у полі Schema SQLє код, що виконує вставку в базу даних 100000 рядків, а праворуч, в полі Query SQLпоказано два запити. Перший, повільний, виглядає так:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

А другий, який є ефективним рішенням того ж завдання, так:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Щоб виконати ці запити, достатньо натиснути на кнопку Run у верхній частині сторінки. Зробивши це, порівняємо відомості про час виконання запитів. Виявляється, що на виконання неефективного запиту йде, як мінімум, у 30 разів більше часу, ніж на виконання другого (від запуску до запуску цей час відрізняється, наприклад, система може повідомити про те, що на виконання першого запиту пішло 37 мс, а на виконання другого - 1 мс).

А якщо даних буде більше, то все буде виглядати ще гірше (для того, щоб у цьому переконатися - погляньте на мій приклад з 10 мільйонами рядків).

Те, що ми щойно обговорили, має дати вам деяке розуміння того, як насправді обробляються запити до баз даних.

Враховуйте, що чим більше значення OFFSET — тим довше виконуватиметься запит.

Що варто використовувати замість комбінації OFFSET та LIMIT?

Замість комбінації OFFSET и LIMIT варто використовувати конструкцію, побудовану за такою схемою:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Це виконання запиту з розбиттям на сторінки, засноване на курсорі (Cursor based pagination).

Замість локально зберігати поточні OFFSET и LIMIT і передавати їх з кожним запитом, потрібно зберігати останній отриманий первинний ключ (зазвичай це ID) і LIMIT, в результаті і будуть виходити запити, що нагадують вищенаведений.

Чому? Справа в тому, що в явному вигляді вказуючи ідентифікатор останнього прочитаного рядка, ви повідомляєте свою СУБД про те, де їй потрібно розпочинати пошук потрібних даних. Причому пошук завдяки використанню ключа буде здійснюватися ефективно, системі не доведеться відволікатися на рядки, що знаходяться за межами зазначеного діапазону.

Погляньмо на таке порівняння продуктивності різних запитів. Ось неефективний запит.

Повільний запит

А ось оптимізована версія цього запиту.

Швидкий запит

Обидва запити повертають точно один і той же обсяг даних. Але виконання першого йде 12,80 секунд, але в другого — 0,01 секунда. Відчуваєте різницю?

Можливі проблеми

Для забезпечення ефективної роботи запропонованого методу виконання запитів потрібно, щоб у таблиці був присутній стовпець (або стовпці), що містить унікальні, послідовно розташовані індекси, на кшталт цілісного ідентифікатора. У деяких специфічних випадках це може визначати успіх застосування подібних запитів для підвищення швидкості роботи з базою даних.

Звичайно, конструюючи запити, необхідно враховувати особливості архітектури таблиць, і вибирати ті механізми, які найкраще покажуть себе на таблицях. Наприклад, якщо потрібно працювати в запитах з великими обсягами пов'язаних даних, вам може здатися цікавою ця стаття.

Якщо перед нами стоїть проблема відсутності первинного ключа, наприклад, якщо є таблиця з ставленням «багатьом до багатьох», то традиційний підхід, що передбачає застосування OFFSET и LIMIT, нам гарантовано підійде. Але його застосування може призвести до виконання потенційно повільних запитів. У таких випадках я б порекомендував використовувати первинний ключ з автоінкрементом, навіть якщо він потрібен тільки для організації виконання запитів з розбиттям на сторінки.

Якщо вам цікава ця тема ось, ось и ось - Кілька корисних матеріалів.

Підсумки

Головний висновок, який ми можемо зробити, полягають у тому, що завжди, про які б розміри баз даних не йшлося, потрібно аналізувати швидкість виконання запитів. В наш час вкрай важлива масштабованість рішень, і якщо від початку роботи над якоюсь системою спроектувати все правильно, це, в майбутньому, здатне позбавити розробника безлічі проблем.

Як ви аналізуєте та оптимізуєте запити до баз даних?

Джерело: habr.com