SQL’da paginatsiya: OFFSET yoki keyset (kursorli) paginatsiya

Qisqa javob

• LIMIT/OFFSET — kichik jadvallar va sahifa raqamlari kerak bo‘lgan holatlar uchun oddiy variant.
• Keyset (kursorli) paginatsiya — katta jadvallar, cheksiz lenta va API uchun variant: tezlik foydalanuvchi qanchalik uzoq varaqlaganiga bog‘liq emas va yozuvlar takrorlanmaydi.

Nega OFFSET sekinlashadi

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

20 ta qatorni qaytarish uchun baza 100 000 ta qatorni topib, o‘tkazib yuborishi kerak. Indeks saralashga yordam beradi, lekin kerakli pozitsiyaga darhol «sakrash»ga imkon bermaydi. Sahifa qanchalik chuqur bo‘lsa, ish shunchalik ko‘p: birinchi sahifalar tez, uzoqdagilari sekin.

Nega OFFSET takrorlar va tushib qolishlar beradi

Foydalanuvchi birinchi sahifani ko‘rayotganda jadvalga yangi yozuv qo‘shiladi. Barcha qatorlar bir pozitsiyaga suriladi va birinchi sahifaning oxirgi yozuvi ikkinchi sahifa boshida yana paydo bo‘ladi. O‘chirishda esa aksincha — bitta yozuv sahifalar orasida yo‘qoladi.

Cheksiz lentada bu ayniqsa seziladi: foydalanuvchi bitta postni ikki marta ko‘radi.

Keyset paginatsiya qanday ishlaydi

«N ta qatorni o‘tkazib yubor» o‘rniga so‘rov «oxirgi ko‘rsatilgan yozuvdan keyingi yozuvlarni ber» deydi:

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Bu yerda $1 va $2 — oldingi sahifadagi oxirgi yozuvning created_at va id qiymatlari.

Uchta majburiy shart:

1. Barqaror saralash. Saralash qatorlarni bir ma’noda tartiblashi kerak. Faqat created_at yetarli emas: ikki yozuvning vaqti bir xil bo‘lishi mumkin. Shuning uchun tie-breaker sifatida noyob id qo‘shamiz.
2. WHERE sharti ORDER BY bilan mos. O‘sha maydonlar, o‘sha tartibda va o‘sha yo‘nalishda.
3. Saralash maydonlari bo‘yicha tarkibiy indeks:

CREATE INDEX posts_created_at_id_idx ON posts (created_at DESC, id DESC);

Bunday indeks bilan baza kursor bo‘yicha joyni darhol topadi va faqat 20 ta qatorni o‘qiydi — birinchi sahifada ham, minginchisida ham bir xil.

(a, b) < (x, y) kortejlarni solishtirish PostgreSQL va MySQL’da qo‘llab-quvvatlanadi. Agar ma’lumotlar bazangiz yoki uning rejalashtiruvchisi undan yomon foydalansa, shartni yoyib yozing:

WHERE created_at < $1 OR (created_at = $1 AND id < $2)

Buni API’da qanday qilish

Kursor mijozga shaffof bo‘lmagan satr sifatida beriladi, masalan saralash maydonlari bor JSON’ning base64 ko‘rinishi:

{
  "items": [ ... ],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTAxLTE1VDEwOjAwOjAwWiIsImlkIjo0Mn0="
}

Amaliy qoidalar:

• LIMIT + 1 so‘rang. 21 ta qator kelsa, keyingi sahifa bor: 20 tasini va yigirmanchisidan olingan kursorni qaytarasiz. Aks holda — nextCursor: null.
• Mijozga kursorni o‘zi yig‘ishiga yo‘l qo‘ymang. Shunda formatni mijozlarni buzmasdan o‘zgartirish mumkin.
• Kursorni serverda tekshiring. Bu foydalanuvchi kiritgan ma’lumot: uni soxtalashtirish yoki buzuq holda yuborish mumkin.
• Kursor saralashga bog‘langan. Foydalanuvchi saralash yoki filtrni o‘zgartirsa, paginatsiya qaytadan boshlanadi.

Lentalar, admin panellar va sahifa raqamlari

Keyset «37-sahifaga o‘tish»ni bilmaydi — buning uchun pozitsiyani bilish, ya’ni qatorlarni sanash kerak. Katta jadvalda «jami sahifalar» uchun COUNT(*) ham qimmat ekanini hisobga oling. Ko‘pincha «10 000 dan ortiq» yoki taxminiy qiymatni ko‘rsatish kifoya.

Orqaga o‘tish ko‘zgu kabi qilinadi: < o‘rniga > sharti, teskari saralash, olingan qismni esa mijozga berishdan oldin teskari aylantirasiz.

Ko‘p uchraydigan xatolar

• Faqat noyob bo‘lmagan maydon bo‘yicha saralash: bir xil qiymatli yozuvlar sahifalar orasida yo‘qoladi yoki takrorlanadi.
• Indeks bor, lekin maydonlar tartibi yoki yo‘nalishi ORDER BY bilan mos emas.
• Shartda va indeksda NULLni aniq ishlamasdan, NULL bo‘lishi mumkin bo‘lgan maydon bo‘yicha saralash.
• Sahifa raqamidan iborat kursor — bu boshqa nom ostidagi o‘sha OFFSET.