OFFSET Tuzağı: Sayfalama Derinleştikçe Neden Yavaşlar?

OFFSET tuzağı sayfalama yapan neredeyse her uygulamada bir gün ortaya çıkıyor ve genellikle şöyle bildiriliyor: "Liste ilk sayfalarda hızlı ama ileri sayfalarda kilitleniyor." Sorgu aynı, indeks yerinde, veri de o kadar büyük değil. Sebep, OFFSET'in ne yaptığına dair yaygın bir yanlış anlamada gizli.

OFFSET aslında ne yapıyor?

Yaygın beklenti, veritabanının 10.000. satıra doğrudan atlaması. Gerçekte olan bu değil: veritabanı sıralanmış sonucu baştan üretir, ilk 10.000 satırı okur, atar ve sonraki 20 satırı döndürür. Yani istemediğiniz satırların bedelini de ödersiniz.

-- 500. sayfa: 10.000 satır okunur, atılır, 20 satır döner
SELECT id, baslik, olusturma_tarihi
FROM yazilar
WHERE durum = 'yayinda'
ORDER BY olusturma_tarihi DESC
LIMIT 20 OFFSET 10000;

Maliyet, sayfa derinliğiyle doğrusal artar. İlk sayfa 20 satır okur, beş yüzüncü sayfa 10.020 satır. Sıralama alanında indeks olsa bile bu böyledir; indeks sıralamayı ücretsiz hâle getirir, atlamayı değil.

Sorunu ölçmek

Teoriye güvenmeden kendi verinizde doğrulayın. Aynı sorguyu iki farklı OFFSET ile çalıştırıp süreleri karşılaştırmak yeterli:

EXPLAIN ANALYZE SELECT ... LIMIT 20 OFFSET 0;
EXPLAIN ANALYZE SELECT ... LIMIT 20 OFFSET 50000;

İkinci sorgunun planında okunan satır sayısının (rows) uçuşa geçtiğini göreceksiniz. Plan okumayı tazelemek isterseniz indeks mantığı yazısı bu konuya giriş niteliğinde.

Çözüm: keyset (cursor) sayfalama

Fikir basit: "10.000 satır atla" demek yerine "son gördüğüm kayıttan sonrasını ver" deyin. Veritabanı indeks üzerinde doğrudan o noktaya konumlanır ve yalnızca isteyeceğiniz satırları okur.

-- İlk sayfa
SELECT id, baslik, olusturma_tarihi
FROM yazilar
WHERE durum = 'yayinda'
ORDER BY olusturma_tarihi DESC, id DESC
LIMIT 20;

-- Sonraki sayfa: son satırın değerleriyle
SELECT id, baslik, olusturma_tarihi
FROM yazilar
WHERE durum = 'yayinda'
  AND (olusturma_tarihi, id) < ($1, $2)   -- son satırın tarihi ve id'si
ORDER BY olusturma_tarihi DESC, id DESC
LIMIT 20;

Bu sorgunun maliyeti sayfa derinliğinden bağımsızdır: birinci sayfa ile beş bininci sayfa aynı süreyi alır.

İki ayrıntı gözden kaçmasın

Birincisi: sıralama benzersiz olmalı. Yalnızca olusturma_tarihi ile sıralarsanız ve aynı saniyede iki kayıt varsa sınır tam oraya düştüğünde bir kayıt atlanır veya tekrar eder. id'yi ikinci sıralama alanı olarak eklemek bunu tamamen çözer.

İkincisi: bileşik karşılaştırmayı destekleyen bir indeks gerekir. Yukarıdaki sorgu için (durum, olusturma_tarihi DESC, id DESC) üzerinde bir indeks doğru olanıdır; sütun sırası da tam bu olmalıdır.

Neyi kaybediyorsunuz?

Doğrudan sayfa numarasına atlama. Keyset sayfalamada "37. sayfa" diye bir şey yok, "bu kayıttan sonrası" var. Arayüz tarafında bu, klasik sayfa numaralarından "daha fazla yükle" düğmesine veya sonsuz kaydırmaya geçmek demek.

Pratikte bu genellikle kabul edilebilir bir değiş tokuş: kullanıcı davranışı verilerine baktığınızda ikinci sayfanın ötesine geçen ziyaretçi oranı çoğu listede yüzde beşin altında kalıyor. Yine de sayfa numaraları vazgeçilmezse melez bir yol var: ilk N sayfa için OFFSET, sonrası için keyset. Kullanıcıların gerçekten gittiği aralık hızlı kalır, derin sayfalar da çökmeden çalışır.

API tasarlıyorsanız

Dışarıya açtığınız listeleme uçlarında keyset sayfalama ayrıca bir tutarlılık kazancı sağlar. OFFSET ile çalışan bir API'de, siz 2. sayfayı isterken listeye yeni bir kayıt eklenirse bir kayıt iki kez görünür veya bir kayıt hiç görünmez. Keyset'te böyle bir kayma olmaz, çünkü konum bir sayıya değil bir kaydın kendisine bağlıdır. API tarafındaki diğer kararlar için REST API tasarımı yazısına bakabilirsiniz.

Toplam sayı sorunu

Sayfalamayı düzelttikten sonra genellikle ikinci darboğaz ortaya çıkar: "toplam 148.322 sonuç" yazan satır. Bu sayı için yapılan COUNT(*) de tabloyu baştan sona tarar ve tek başına sayfalama sorgusundan yavaş olabilir. Üç makul seçenek var: sayıyı periyodik olarak hesaplayıp önbellekte tutmak, yaklaşık göstermek ("1000+ sonuç"), ya da kaldırmak. Hangisinin uygun olduğunu, o alana gerçekten bakılıp bakılmadığını ölçerek belirleyin. Önbellek tarafı için önbellek stratejileri yazısı yardımcı olur.

Sonuç

OFFSET tuzağı sayfalama sorunlarının en sinsi olanı, çünkü geliştirme ortamında hiç görünmez: yerelde 200 kayıt vardır, ileri sayfa yoktur. Üretimde ise arama motoru botu listenin sonuna kadar gezer ve sunucuyu ilk fark eden o olur. Çözüm hazır: sıralamanızı benzersiz yapın, bileşik indeksi kurun, imleçle sayfalayın. Değişiklik genellikle tek bir sorgu ve tek bir arayüz düğmesiyle sınırlı kalıyor.

Konunun derinlemesine anlatımı için: Use The Index, Luke! — No Offset.