OFFSET-dən istifadə edərək sətirləri ötürmək və səhifələmə qurmaq

Bir sual verim: internet mağazasında bir səhifədə çoxlu məhsul görəndə nə edirsən? Düzdür, növbəti səhifəni açırsan. Bəs pərdə arxasında nə baş verir? Orda SQL magiyası işləyir — OFFSET komandası sətirləri ötürmək üçün istifadə olunur. Bu gün sən öyrənəcəksən ki, OFFSET nədir, niyə lazımdır, necə istifadə olunur və niyə məlumatların səhifələnməsinin əsasıdır.

OFFSET — SQL-də açar sözdür, sorğunun nəticəsində müəyyən sayda sətiri ötürməyə imkan verir. Bu, kitab səhifəsini çevirmək kimidir: ilk 10 sətiri "ötürüb" 11-ci sətirdən baxmağa başlaya bilərsən.

Sintaksis

SELECT kolonka1, kolonka2
FROM cədvəl
OFFSET sətir_sayı;

• OFFSET — sətirləri ötürmək üçün açar söz.
• sətir_sayı — ötürmək istədiyin sətirlərin sayı.

OFFSET-dən istifadə üçün sadə nümunə

Tutaq ki, bizdə students cədvəli var və orda belə məlumatlar var:

Bütün tələbələri üçüncü sətirdən başlamaqla çıxarmaq istəyirik. Bunun üçün belə sorğu yazırıq:

SELECT *
FROM students
OFFSET 2;

Nəticə:

SQL ilk iki sətiri (Alisa və Bob) "ötürdü" və nəticəni üçüncü sətirdən göstərdi. Elə bil kitabda səhifə işarəsi qoyursan: "bu səhifəni oxumuşuq, növbətini açırıq".

OFFSET-i LIMIT ilə birləşdirmək

OFFSET adətən LIMIT ilə birlikdə istifadə olunur. Bu, həm sətirləri ötürməyə, həm də nəticəni istədiyin sayda yazı ilə məhdudlaşdırmağa imkan verir. Xüsusilə səhifələmə (page — səhifə) üçün çox rahatdır — məlumatları hissə-hissə, bir neçə sətirlik çıxarmaq üçün.

Tutaq ki, hər dəfə 2 yazı çıxarmaq və üçüncü sətirdən başlamaq istəyirik. Sorğu belə olacaq:

SELECT *
FROM students
LIMIT 2 
OFFSET 2;

Nəticə:

İş prinsipi:

1. OFFSET 2 ilk iki sətiri ötürür: Alisa və Bob.
2. LIMIT 2 qalanlardan yalnız iki sətir götürür.

Səhifələmə qurmaq

İndi ən maraqlı yerə gəldik — səhifələmə qurmağa. Təsəvvür elə ki, tələbə siyahısı olan web-app düzəldirsən. Bir səhifədə 2 yazı göstərmək istəyirsən. Necə edək?

Əsas prinsip:

• Birinci səhifə 0 sətir ötürür: OFFSET 0.
• İkinci səhifə 2 sətir ötürür: OFFSET 2.
• Üçüncü səhifə 4 sətir ötürür: OFFSET 4.
• Və s.

Nümunə: ikinci səhifəni çıxar

Birinci səhifədə 1 və 2-ci yazılar (Alisa və Bob) çıxır. İkinci səhifədə — 3 və 4-cü yazılar (Klara və Dan).

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

Nəticə:

Nümunə: üçüncü səhifəni çıxar

Üçüncü səhifədə — 5 və 6-cı yazılar (əgər varsa).

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

Nəticə:

OFFSET üçün formula

Səhifələmə sistemləri quranda bu formuladan istifadə edə bilərsən:

OFFSET = (səhifə_nömrəsi - 1) * səhifədə_yazı_sayı

Məsələn:

• Birinci səhifə üçün: (1 - 1) * 2 = 0.
• İkinci səhifə üçün: (2 - 1) * 2 = 2.
• Üçüncü səhifə üçün: (3 - 1) * 2 = 4.

Performans üçün vacib qeydlər

Böyük cədvəllərlə işləyəndə OFFSET-dən istifadə sonda effektli olmaya bilər, xüsusən gec səhifələrdə. Səbəb: PostgreSQL ötürülən sətirləri yenə də oxuyur. Məsələn, OFFSET 10000 olan sorğu SGBD-ni ilk 10 000 sətiri keçməyə məcbur edir. Belə hallarda alternativlərə baxmaq olar, məsələn, unikal identifikatoru səhifələmə marker kimi istifadə etmək.

Alternativ: cursor ilə səhifələmə

Optimallaşdırma üçün "cursor" yanaşmasından istifadə edə bilərsən. OFFSET ilə sətirləri ötürmək əvəzinə, əvvəlki səhifədə çıxan sonuncu sətirin id-sini yadda saxlayıb, növbəti sorğunu belə qurursan:

SELECT *
FROM students
WHERE id > sonuncu_çıxan_id
ORDER BY id
LIMIT 2;

Bu yanaşma böyük cədvəllərlə işi xeyli sürətləndirə bilər.

Səhifələmənin praktik tətbiqi

Səhifələmə əksər web-app-larda istifadə olunur: internet mağazalar, bloqlar, admin panellər. Məsələn:

• İnternet mağazada məhsul siyahısını göstərmək;
• CRM-də istifadəçi siyahısını çıxarmaq;
• Xəbər lentini səhifə-səhifə göstərmək.

Həmçinin səhifələmə böyük həcmli məlumatları analiz edəndə də faydalı olur, çünki hissə-hissə işləməyə imkan verir.

OFFSET-dən istifadə zamanı tipik səhvlər

OFFSET ilə işləyəndə bəzən çətinliklər olur, xüsusən öyrənəndə. Budur, ən yayılmış səhvlər:

Sıralama yoxdur. Əgər ORDER BY əlavə etməsən, OFFSET nəticəsində sətirlərin sırası gözlənilməz ola bilər.

Səhv sorğu:

SELECT * FROM students
OFFSET 5;

Düzgün sorğu:

SELECT * FROM students
ORDER BY id
OFFSET 5;

Yanlış OFFSET dəyəri. Çox böyük dəyər yazsan, nəticə boş olacaq.

Performans problemləri. Yuxarıda danışdığımız kimi, gec səhifələrdə böyük OFFSET istifadə etmək effektli deyil.

Filtrlər yoxdur. Əgər OFFSET və LIMIT filtrlərsiz (WHERE) istifadə etsən, lazımsız məlumat ala bilərsən və performans pisləşər.

Operatorların sırası. SQL-də operatorların sırası dəqiqdir: əvvəl LIMIT, sonra OFFSET gəlir. Operatorları buraxmaq olar, amma sırasını dəyişmək olmaz.