Використання OFFSET для пропуску рядків і побудови пагінації

Давай задам питання: що ти робиш, коли на сайті інтернет-магазину знаходиш забагато товарів на одній сторінці? Правильно, відкриваєш наступну сторінку. А що відбувається за лаштунками? Там працює магія SQL — використовується команда OFFSET для пропуску рядків. Сьогодні ти дізнаєшся, що таке OFFSET, навіщо він потрібен, як його використовувати і чому він є основою побудови пагінації даних.

OFFSET — це ключове слово в SQL, яке дозволяє пропустити задану кількість рядків у результатах запиту. Це як гортати сторінку книжки: ти можеш "пропустити" перші 10 рядків і почати перегляд з 11-го.

Синтаксис

SELECT колонка1, колонка2
FROM таблиця
OFFSET кількість_рядків;

• OFFSET — ключове слово для пропуску рядків.
• кількість_рядків — скільки рядків треба пропустити.

Простий приклад використання OFFSET

Припустимо, у нас є таблиця students з такими даними:

Ми хочемо вивести всіх студентів, починаючи з третього. Для цього можна використати запит:

SELECT *
FROM students
OFFSET 2;

Результат:

SQL "пропустив" перші два рядки (Аліса і Боб) і видав результат, починаючи з третього рядка. Прямо як із закладками: "цю сторінку вже читали, відкриваємо наступну".

Комбінування OFFSET з LIMIT

OFFSET зазвичай використовується разом з LIMIT. Це дозволяє одночасно пропускати рядки і обмежувати результат потрібною кількістю записів. Особливо це корисно для побудови пагінації (page — сторінка) — виводу даних порціями, по кілька рядків за раз.

Припустимо, ми хочемо виводити по 2 записи за раз і почати з третього рядка. Запит буде виглядати так:

SELECT *
FROM students
LIMIT 2 
OFFSET 2;

Результат:

Логіка роботи:

1. OFFSET 2 пропускає перші два рядки: Аліса і Боб.
2. LIMIT 2 бере тільки два рядки з тих, що залишились.

Побудова пагінації

Тепер ми дійшли до найцікавішого — побудови пагінації. Уяви, що ти робиш веб-додаток зі списком студентів. На одній сторінці хочеш показувати 2 записи. Як це зробити?

Основний принцип:

• Перша сторінка пропускає 0 рядків: OFFSET 0.
• Друга сторінка пропускає 2 рядки: OFFSET 2.
• Третя сторінка пропускає 4 рядки: OFFSET 4.
• І так далі...

Приклад: виведи другу сторінку

На першій сторінці виводяться записи 1 і 2 (Аліса і Боб). На другій сторінці — записи 3 і 4 (Клара і Дан).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 2;

Результат:

Приклад: виведи третю сторінку

На третій сторінці — записи 5 і 6 (якщо вони є).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 4;

Результат:

Формула для обчислення OFFSET

При побудові систем пагінації можна використовувати таку формулу для автоматизації:

OFFSET = (номер_сторінки - 1) * кількість_записів_на_сторінці

Наприклад:

• Для першої сторінки: (1 - 1) * 2 = 0.
• Для другої сторінки: (2 - 1) * 2 = 2.
• Для третьої сторінки: (3 - 1) * 2 = 4.

Важливі зауваження щодо продуктивності

Коли ти працюєш з великими таблицями, використання OFFSET може бути неефективним, особливо на пізніх сторінках. Причина в тому, що PostgreSQL все одно переглядає рядки, які пропускаються. Наприклад, запит з OFFSET 10000 змушує СУБД пройти всі перші 10 000 рядків, перш ніж віддати результати. В таких випадках можна розглянути альтернативи, наприклад, використання унікального ідентифікатора як маркера пагінації.

Альтернатива: пагінація за допомогою курсора

Для оптимізації можна використати підхід з "курсорoм". Замість того, щоб пропускати рядки через OFFSET, ти можеш запам’ятовувати ідентифікатори останнього рядка, отриманого на попередній сторінці, і використовувати їх для побудови наступного запиту:

SELECT *
FROM students
WHERE id > останній_виведений_id
ORDER BY id
LIMIT 2;

Такий підхід може суттєво прискорити роботу з великими таблицями.

Практичне застосування пагінації

Пагінація використовується у більшості веб-додатків: інтернет-магазини, блоги, адмін-панелі. Наприклад:

• Відображення списку товарів в інтернет-магазині;
• Вивід списку користувачів у CRM-системі;
• Посторінковий показ новинної стрічки.

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

Типові помилки при використанні OFFSET

Робота з OFFSET іноді викликає труднощі, особливо на етапі навчання. Ось деякі поширені помилки:

Відсутність сортування. Якщо ти не додаси ORDER BY, порядок рядків у результатах OFFSET може бути непередбачуваним.

Неправильний запит:

SELECT * FROM students
OFFSET 5;

Правильний запит:

SELECT * FROM students
ORDER BY id
OFFSET 5;

Неправильне значення OFFSET. Якщо вказати занадто велике значення, результатом буде порожній набір.

Проблеми з продуктивністю. Як ми обговорювали раніше, використання великих OFFSET на пізніх сторінках може бути неефективним.

Відсутність фільтрів. Якщо ти використовуєш OFFSET і LIMIT без фільтрації (WHERE), можеш отримати зайві дані, що може погіршити продуктивність.

Порядок операторів. Порядок операторів у SQL суворо фіксований: спочатку йде LIMIT, потім OFFSET. Пропускати (не вказувати) оператори можна, змінювати порядок — не можна.